How to code SQL statements?

Question

I am trying to run a new SQL command for each value in my list. I have the following that just repeats the same value over and over for the amount in my list.

Note that I need to loop around SQL for the number of values ​​in my list of arrays and insert each value as a name in SQL, as shown below.

What is the best way to do this, as it is definitely not.

int listSize = al.size(); for(int i = 0; i < listSize; i++) { ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+al.listIterator().next()+"'"); al1.add(rs1.getString(1)); rs1.close(); } System.out.println(al1); 

Result:

[70, 70, 70, 70, 70, 70, 70, 70, 70, 70]

Expected Result:

[70, 80, 110, 60, 35, 10, 15, 10, 0, 25]

Answer 1

The problem is in

 al.listIterator().next() 

which returns the same value all the time. Therefore, the query returns the same row of the result set, so your result is 70 all the time.

try using this code:

 int listSize = al.size(); for(int i = 0; i < listSize; i++) { ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+al.get(i)+"'"); while(rs1.next()){ al1.add(rs1.getString(1)); } rs1.close(); } System.out.println(al1); 

Answer 2

Every time you create a new Itrator, every time you return the same value. Try under the code

 int listSize = al.size(); for(int i = 0; i < listSize; i++) { ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+al.get(i)+"'"); al1.add(rs1.getString(1)); rs1.close(); } System.out.println(al1); 

Answer 3

Please, use

 while(rs1.next()){ System.out.println(rs1.getString(1)); } 

Answer 4

Set up the for loop as follows to use each entry in the list:

  for(String name : al) { ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+name+"'"); al1.add(rs1.getString(1)); rs1.close(); } System.out.println(al1); 

Answer 5

you can replace the for loop while

 Interator i = al.listIterator(); while(i.hasNext()){ String name = i.next(); ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE ="+date+"' AND name = '"+name+"'"); while(rs1.next()){ System.out.println(rs1.getString(1)); } } 

Answer 6

The simple answer is: you need al.get(i) and call rs1.next() before reading from the result set. You should also check that the rs1.next() call returns true before trying to read from the result set.

But this does not take into account the fact that the query execution in the loop sucks - the performance will be terrible, and you clog the database. Each time you call name.executeQuery , a remote call is made with all the overhead that entails, and you do this for every item in the collection that you iterate. If the collection has 1000 items, this is 1000 deleted calls. In addition, using string concatenation to build a query means that the database must compute a query plan every time a query is executed.

To do this correctly, you have to execute one query using a prepared statement, and then iterate over it according to the result set - 1 remote call - something like:

 PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { preparedStatement = connection.prepareStatement("SELECT sum(hours) FROM project_time WHERE date = ? AND name IN ?"); preparedStatement.setDate(1, date); preparedStatement.setArray(2, connection.createArrayOf("VARCHAR", al.toArray()); resultSet = preparedStatement.executeQuery(); List<String> hours = new ArrayList<String>(); while (resultSet.next()) { hours.add(resultSet.getString(1)); } System.out.println(hours); } finally { if (resultSet != null) resultSet.close(); if (preparedStatement != null) preparedStatement.close(); } 

Not knowing which database you are using, it is hard to see if the syntax is 100% consistent, but you have to get the gist.

Answer 7

When you use an iterator for List, it gives you the very first arrangement of items in the list, and when Iterator.next () is called, Iterator moves to the location of the socket.

`int listSize = al.size ();

 for(int i = 0; i < listSize; i++) { ResultSet rs1 = name.executeQuery("SELECT sum(hours) FROM PROJECT_TIME WHERE DATE = '"+date+"' AND name = '"+al.get(i)+"'"); al1.add(rs1.getString(1)); rs1.close(); } System.out.println(al1);`