How to insert Java ArrayList into a MySQL table?

Question

Could someone kindly tell me how can I insert the values of Arraylist into MySQL table as a single string?

E.g, insert "crime, drama, Action" into column "genres" of a table "featuredfilms_INFO".

This is a part of my code:

int i = 0;
List<String> genre = new ArrayList<String>();
.
.
.
Elements elms1 = doc.select("div.infobar"); 
Elements links1 = elms1.select("a[href]");
for (Element link1 : links1){
    if (link1.attr("href").contains("/genre/")) {
        genre.add(links1.get(i).text());
        i++;
    }
}
System.out.println("movie genres:" + genre); 
.
.
try {
     String query = "INSERT into featuredfilms_INFO (movieId, genres)" + "VALUES (?, ?)";
     PreparedStatement preparedStmt = conn.prepareStatement(query);
     preparedStmt.setString (1, ID);
     preparedStmt.setString (2, genre);
   .
   .
   .
}

My problem is that I cannot use setString for genre since it is not of type string. I'm a bit confused since at the beginning I defined genre as string but after some search I found that in Java for dynamic arrays I have to use ArrayList.

Can someone explain me in detail what should I do and why?

@JosefE. I get this ID from another table in my datbase where I stored movies. — mOna
– mOna, Commented Jul 2, 2014 at 15:08
Are you using JDBC, JPA, JDO, etc.? More information needed here — hd1
– hd1, Commented Jul 2, 2014 at 15:09
Do you want one row per genre or do you want one row with a long string containing all genres (e.g. comma separated)? — Erich Kitzmueller
– Erich Kitzmueller, Commented Jul 2, 2014 at 15:09
@ammoQ: I need all genres of each movie to be stored in one record, ex: movie1: crime, drama, then next recod: movie2: action, fantasy, history, next record: movie3: .... — mOna
– mOna, Commented Jul 2, 2014 at 15:12

Erich Kitzmueller · Accepted Answer · 2014-07-02 15:19:22Z

5

Just join all the strings before inserting.

 String comma="";
 StringBuilder allGenres = new StringBuilder();
 for (String g: genre) {
    allGenres.append(comma);
    allGenres.append(g);
    comma = ", ";
 }

 String query = "INSERT into featuredfilms_INFO (movieId, genres)" + "VALUES (?, ?)";
 PreparedStatement preparedStmt = conn.prepareStatement(query);
 preparedStmt.setString (1, ID);
 preparedStmt.setString (2, allGenres.toString());

Maybe even

 preparedStmt.setString (2, genre.toString());

would even be good enough, but the above solution gives you more freedom how to join the genres.

answered Jul 2, 2014 at 15:19

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mOna Over a year ago

Thanks a lot for your help :), it worked by only adding .toString() to the last line as u said

Luiggi Mendoza · Accepted Answer · 2014-07-02 15:09:15Z

2

I guess you're confused because your List<String> is called genre and you try to insert it as is. This won't work. You need to insert every element from the List in your table.

Assuming you have declared and initialized ID variable and that List<String> genre is filled with the right values, then you should only traverse the list and insert every genre:

String query = "INSERT into featuredfilms_INFO (movieId, genres)" + "VALUES (?, ?)";
PreparedStatement preparedStmt = conn.prepareStatement(query);
preparedStmt.setString (1, ID);
for (String realGenre : genre) {
    preparedStmt.setString (2, realGenre);
    preparedStmt.executeUpdate();
}

answered Jul 2, 2014 at 15:09

Luiggi Mendoza

85.9k16 gold badges159 silver badges354 bronze badges

3 Comments

mOna Over a year ago

actually I summarized the insert in my question, I mean it is: INSERT into featuredfilms_INFO (movieId, genre, country)" + "VALUES (?, ?, ?)"; so now when I write your answer I get an error that "No values specified for parameter 3", and when I omit the executeUpdate line in for loop, it onnly insert the last genre for each movie !! :( PreparedStatement preparedStmt = conn.prepareStatement(query);

Luiggi Mendoza Over a year ago

@monamona that part is outside the question, and the error is very specific: you're declaring you need to send 3 parameters but only providing 2. Provide the third parameter or remove it at all.

mOna Over a year ago

I provided the 3rd on, I just didnt write here. but I got the error, however it solved by only adding toString() that is: preparedStmt.setString (2, genre.toString()); tnx anyway for your help ;)

Collectives™ on Stack Overflow

How to insert Java ArrayList into a MySQL table?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related