Insert variables, arrays into MySQM database using Java

Question

I've successfully inserted data into database by just writing in data that I need. Now I'm trying to insert variables and arrays that will hold the data. This was kind of shot in the dark because I had no idea how to do it, I just kind of guessed. I get no syntax errors, so I thought I was doing good but it doesn't compile... I just need to know the exact syntax to do that.

for(int i = 0; i < ReadingFile.altitudeList.size(); i++){
 for(int j = 0; j < ReadingFile.temperatureList.size(); j++){
  for( int k = 0; k < ReadingFile.velocityList.size(); k++){
   for( int x = 0; x < ReadingFile.latList.size(); x++){
    for(int y = 0; y < ReadingFile.longList.size();y++){
stat
    .execute("INSERT INTO TrailTracker VALUES(id,ReadingFile.date,ReadingFile.distance, ReadingFile.timeElapsed, ReadingFile.startTime,"
                + "ReadingFile.temperatureList[j], ReadingFile.velocityList[k], ReadingFile.altitudeList[i], ReadingFile.latList[x],"
                + "ReadingFile.longList[y])");
        }}}}}

Qwerky · Accepted Answer · 2011-01-20 11:45:02Z

You can't insert variables or arrays into a database. You can only insert data, ie the values of your variables or arrays.

A PreparedStatement is the way to go. It would look something like this;

int a = 1;
Date b = new Date();
String c = "hello world";

PreparedStatement stmt = conn.prepareStatement("INSERT INTO MyTable VALUES (?,?,?)");
stmt.setInt(1, a);
stmt.setDate(2, new java.sql.Date(b.getTime());
stmt.setString(3, c);
stmt.execute();

Note that it doesn't look like you have correctly designed your table to match your data. Your ReadingFile seems to have 5 Lists and you need to figure out how the values in these lists relate to each other. Your current logic with 5 nested loops is almost certainly not what you want. It results in a highly denormalised structure.

For example, say you had a ReadingFile object with an id of 1, date of 20/1/2011, distance of 10, time elapsed of 20 and start time of 30. Then each of the lists had two values;
- temperature 21, 23
- velocity 51, 52
- altitude 1000, 2000
- lat 45.1, 47.2
- long 52.3, 58.4

Then your nested loops would insert data into your table like this;

+--+---------+--------+-----------+---------+-----------+--------+--------+----+----+
|id|     date|distance|timeElapsed|startTime|temperature|velocity|altitude| lat|long|
+--+---------+--------+-----------+---------+-----------+--------+--------+----+----+
| 1|20.1.2011|      10|         20|       30|         21|      51|    1000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      51|    1000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      51|    1000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      51|    1000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      52|    1000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      52|    1000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      52|    1000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      52|    1000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      51|    1000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      51|    1000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      51|    1000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      51|    1000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      52|    1000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      52|    1000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      52|    1000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      52|    1000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      51|    2000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      51|    2000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      51|    2000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      51|    2000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      52|    2000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      52|    2000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         21|      52|    2000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         21|      52|    2000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      51|    2000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      51|    2000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      51|    2000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      51|    2000|47.2|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      52|    2000|45.1|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      52|    2000|45.1|58.4|
| 1|20.1.2011|      10|         20|       30|         23|      52|    2000|47.2|52.3|
| 1|20.1.2011|      10|         20|       30|         23|      52|    2000|47.2|58.4|
+--+---------+--------+-----------+---------+-----------+--------+--------+----+----+

So if I had an arrayList velocityList with [22,25] data in it. Can I do for(int i=0;velocityList.size();i++){ stmt.setInt(1,velocityList[i]); } ? It still doesn't seem right, I'm sure I missed something...
I sure don't want my database to look like your table :) and it makes total sense how you insert data from variables into the DB, but still not sure for my arrayLists..
So I tried using prepared statement and using just VARIABLES following your method and later using ResultSet, where I select all from database where id =3; I get an exception "Illegal operation on empty result set" I can't understand what I'm doing wrong.. Empty ResultSet, so that means that nothing gets imported into the database?
nevermind above.. forgot stmt.executeUpdate();.. still not sure how get data from arrays into the database, but I will try to figure it out...

Jigar Joshi · Accepted Answer · 2011-01-20 09:09:06Z

1

this would be invalid query.

You need to go for PreparedStatement.

answered Jan 20, 2011 at 9:09

Jigar Joshi

241k42 gold badges409 silver badges446 bronze badges

1 Comment

Freddie Over a year ago

This is a good idea - you can prepare the statement once and just change the values each time it is executed - quite efficient especially considering the depth of those loops...

t0x13 · Accepted Answer · 2011-01-21 04:39:14Z

0

So I figured out the easiest way to do what I needed using a while loop

while(!(sampleSize == temp)){
        conn.prepareStatement(insertStr);
        prstat.setInt(1, id);
        prstat.setInt(7, v.get(temp));
        temp++;
        prstat.executeUpdate();
        }

temp is initially set to zero, and increments while inserting elements from arrayList into database until its equal to the sampleSize (sampleSize = v.size();) so that it knows it reached the end of the list. Thanks for everyones help!

answered Jan 21, 2011 at 4:39

t0x13

3592 gold badges10 silver badges24 bronze badges

1 Comment

Qwerky Over a year ago

Note that you only need to prepare the statement once. You can repeatedly set stuff on it and execute it once it has been prepared.

Collectives™ on Stack Overflow

Insert variables, arrays into MySQM database using Java

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related