I have a text file containing multiple rows that each represent either an artist or artwork object, structures in a way similar as below (ARTIST is only the artist's name in the text file), where the object type order is random:
A| NAME| DOB| SEX| COUNTRY
W| ARTIST| TITLE| YEAR| COUNTRY
A| NAME| DOB| SEX| COUNTRY
W| ARTIST| TITLE| YEAR| COUNTRY
A| NAME| DOB| SEX| COUNTRY
A| NAME| DOB| SEX| COUNTRY
A| NAME| DOB| SEX| COUNTRY
W| ARTIST| TITLE| YEAR| COUNTRY
I have a means of parsing them and creating individual object from each row that works, and I have implemented each class as follows: Artist:
@Entity
@Table(name = "artist", uniqueConstraints = @UniqueConstraint(columnNames = "name"))
public class Artist implements Manageable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column(name = "name")
private String name;
@Column(name = "sex")
private String sex;
@Column(name = "country")
private String country;
@Column(name = "dob")
private int dob;
Artwork:
@Entity
@Table(name = "artwork", uniqueConstraints = @UniqueConstraint(columnNames = "title"))
public class Artwork implements Manageable{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column(name = "title")
private String title;
@Column(name = "country")
private String country;
@Column(name = "year")
private int year;
@Column(name = "artist")
private String artist;
As you can see in the Artwork class I have the artist currently set as a string. I'm struggling to come up with a solution that would enable me to have these tables joined as I load in the data from the text file so the relationships can be established (e.g. artist can have multiple artwork)
The data loader looks as below:
public class DataLoader {
public static void main(String[] args) throws Exception {
DataLoader dataL = new DataLoader();
DBConnection.createTables();
File file = new File("data\\data.txt");
dataL.separateDataFromTextFile(file);
}
public void separateDataFromTextFile(File file) throws Exception {
Scanner input = new Scanner(file);
// Separate input by line.
input.useDelimiter("\n");
while (input.hasNext()) {
String row = input.next().trim();
char lineStartChar = row.charAt(0);
// If row is an artwork
if (Character.toString(lineStartChar).equals("W")) {
mapArtwork(row);
// If row is an artist
} else if (Character.toString(lineStartChar).equals("A")) {
mapArtist(row);
}
}
System.out.print("Complete!");
input.close();
}
public void mapArtwork(String artworkRow) throws Exception {
String[] params = artworkRow.trim().split("\\|");
String artist = params[1].trim();
String title = params[2].trim();
int year = Integer.parseInt(params[3].trim());
String country = params[4].trim();
Artwork artwork = new Artwork(title, country, year, artist);
DBConnection.addArtwork(artwork);
}
public void mapArtist(String artistRow) throws Exception {
String[] params = artistRow.trim().split("\\|");
String name = params[1].trim();
int dob = Integer.parseInt(params[2].trim());
String sex = params[3].trim();
String country = params[4].trim();
Artist artist = new Artist(name, country, dob, sex);
DBConnection.addArtist(artist);
}
}
The DB Connection class that's used to update the database is as follows:
public class DBConnection {
private static Connection conn;
@SuppressWarnings("finally")
public static Connection getDBConnection() {
try {
conn = DriverManager.getConnection(
dbhost, username, password);
} catch (SQLException e) {
System.out.println("Cannot create database connection");
e.printStackTrace();
} finally {
return conn;
}
}
public static void createTables() throws Exception{
try{
Connection conn = getDBConnection();
// Artist Table
PreparedStatement createArtistTable = conn.prepareStatement(
"CREATE TABLE IF NOT EXISTS artist(" +
"id int NOT NULL AUTO_INCREMENT," +
" name varchar(255)," +
" dob int," +
" sex varchar(255)," +
" country varchar(255)," +
" date date," +
" PRIMARY KEY (id)," +
" UNIQUE (name))"
);
createArtistTable.executeUpdate();
//Artwork Table
PreparedStatement createArtworkTable = conn.prepareStatement(
"CREATE TABLE IF NOT EXISTS artwork(" +
"id int NOT NULL AUTO_INCREMENT," +
" title varchar(255)," +
" year int," +
" artist varchar(255)," +
" country varchar(255)," +
" PRIMARY KEY (id)," +
" UNIQUE (title))"
);
createArtworkTable.executeUpdate();
}
catch(Exception e){
System.out.println(e);
}
finally{
System.out.println("Function completed");
}
}
public static void addArtwork(Artwork art) throws Exception {
try{
Connection conn = getDBConnection();
String sql = "INSERT INTO artwork(title, year, artist, country) VALUES (?, ?, ?, ?)";
PreparedStatement posted = conn.prepareStatement(sql);
posted.setString(1, art.getName());
posted.setInt(2, art.getYear());
posted.setString(3, art.getArtist());
posted.setString(4, srt.getCountry());
posted.executeUpdate();
}
catch(Exception e) {
System.out.println(e);
}
finally{System.out.println("Insert completed");}
}
public static void addArtist(Artist artist) throws Exception {
try{
Connection conn = getDBConnection();
String sql = "INSERT INTO artist(name, sex, country, dob, date) VALUES (?, ?, ?, ?, ?)";
PreparedStatement posted = conn.prepareStatement(sql);
posted.setString(1, artist.getName());
posted.setString(2, artist.getSex());
posted.setString(3, artist.getCountry());
posted.setInt(4, artist.getDob());
posted.setDate(5, new Date(artist.getDate().getTime()));
posted.executeUpdate();
}
catch(Exception e) {
System.out.println(e);
}
finally{System.out.println("Insert completed");}
}
}
So what I'm trying to achieve is during the dataloading that each artwork is assigned an artist ID, where an artist can have multiple artworks, but artwork only the one artist. I have tried doing the dataloading so it allows for all of the artists to be created first, then trying associate the artworks with them, however I then struggled to introduce a relationship between them.
So to reiterate the main issue I'm having is establishing relationships during the loading of data from the text file, currently I've reverted the classes so there is no relationship as I made a total mess of them when trying to get that working. How would I best go about achieving this?