Using Spring Data JPA with an adjacency matrix stored in a MySQL database

user_7310414 0
Viewed 207

I am new to using Spring Data Jpa but I understand one normally creates a 'Model'/ 'Entity' class in Java that represents a row of data in your database table. This makes sense to me when I have a small table with 3 columns/ class attributes, but what about the case of a table with 100 columns? One hardly creates a 'Model' class with 100 attributes? The reason I ask this is because I am storing a large adjacency matrix of train stations in a MySQL database table. I want to interact with this data in my Java Spring application but cannot get my head around how to do this.

I am open to the fact that I may be storing the data incorrectly or should not be using Spring Data JPA at all in this situation. Any advice would be greatly appreciated!

Thanks.

Screenshot of part of my adjacency matrix table in MySQL

EDIT:

I'll try to explain what I'm trying to do more clearly. I would like to essentially import a copy of this 'Stations' (adjacency matrix) database table into my Java Spring application. I don't know how best to perform this 'import', or what Java data structure to store the table in. I would like to be able to run algorithms like BFS on the data once in Java.

The data in the table is an adjacency matrix showing a graph of a train network. A '1' shows the stations are connected by a track and a '0' shows no connection.

adjacency-matrix spring-data-jpa spring mysql java
4 Answers

If your model is truly represented with a hundred attributes, then yes, you can create an entity that has a hundred properties. But I think your model needs reworked because the way I look at this, inserting a new record would require a schema change.

Since I don't know what those columns represent, I'm going to assume they're maybe next and previous stations? This would be a simple model to represent this.

@Entity
@Table(name = "station")
@Getter
@Setter
public class Station {

   @Id
   private Long id;

   @Column(name = "name")
   private String name;

   @OneToOne
   @JoinColumn(name = "previous_station_id")
   private Station previous;

   @OneToOne
   @JoinColumn(name = "next_station_id")
   private Station next;
}

Your DDL would look like this:

create table station
(
    id                  bigint auto_increment,
    name                varchar(255) null,
    previous_station_id bigint       null,
    next_station_id     bigint       null,
    constraint station_id_uindex
        unique (id),
    constraint station_station_id_fk
        foreign key (previous_station_id) references station (id),
    constraint station_station_id_fk_2
        foreign key (next_station_id) references station (id)
);

create index station_next_station_id_index
    on station (next_station_id);

create index station_previous_station_id_index
    on station (previous_station_id);

alter table station
    add primary key (id);

Because this table actually has keys back to itself, you would first insert a station with no previous or next set, then you would update the record to set these values. If I'm off base on what those values are, update your question to better describe what you're trying to model for more accurate answers.

user_8885800

you can use two classes like this :

@Entity
public class TestClass {    
   @Id
   private long id;

   @OneToMany(mappedBy="testClass")
   private List<MatrixRow> matrix; 
}

and one MatrixLine class:

@Entity
public class MatrixRow {
  @Id
  private long id;

  @ManyToOne
  private TestClass testClass;

  @CollectionOfElements
  private List<Value> row;
}
user_172547770

I think when you're doing BFS you need a previous station name, so either you have to create a model class with 100 names and use the @Getter and @Setter annotation or create a List with 100 elements and store the index representation in some other files as an enum in this case you've to change the database schema to stations and array as blob/text.

user_121077980

Representing it as a many to many relationship could work well in this case. I'm not very familiar with JPA but I think it would look something like this:

@Getter
@Setter
@Entity
public class Station {

    @Id    
    private Long id;

    private String name;

    @ManyToMany       
    private Set<Station> adjacentStations;
}

The stations are interconnected, such that when station B is adjacent to station A (i.e. B is in A's set of adjacent stations), then station A is also adjacent to station B (i.e. A is also in B's set of adjacent stations).

In the database, the relationship between stations would then be represented through a mapping table that has two columns (station_id_1, station_id_2) and one row for each connection.

user_10070080
Related