Insert variables, arrays into MySQM database using Java

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开发者_JS百科 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])");
        }}}}}

---

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|
+--+---------+--------+-----------+---------+-----------+--------+--------+----+----+

---

this would be invalid query.

You need to go for PreparedStatement.

---

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!