CODEWITHSUNDEEP
javamysqlsqlexception
A. Masssey
A. Masssey

Reputation: 179

How to mix java loops with SQL conditional selects

So I would like to do a select from an SQL database, using Java, subject to a conditional statement (less than or equal to something) subject to some loop in Java. In other words, something like the following:

for (int i=0; i< 195; i++) {
            // Get the results of the SQL query
            resultSet = statement.executeQuery( 
                    "SELECT max( millisFromMid ) FROM stockInfo1 WHERE ( millisFromMid <=  34200000 + (120000)*i ) GROUP BY stockID" 
                    );

Now Java is returning an exception here, because it doesn't want me to use "i" in this condition; however, without being able to use it here, I'm not sure how I can change this condition. The problem is this: I'm looking to retrieve data from one database, with the intent of doing some manipulation and putting the results into a new database. These manipulations depend on taking the most recent data available, which is why I'm wanting to increase the number bounding millisFromMid. Does that make sense?

Does anyone have a suggestion on how someone might do something like this? This seems like a fundamental skill to have when using Java and SQL together, so I'd very much like to know it.

Upvotes: 0

Views: 913

Answers (3)

Jayan
Jayan

Reputation: 18459

You can generate an sql string and call it via jdbc, using bind variable is more correct from performance, security. It also saves you from escaping any special characters.

Since you are learning about using sql with jdbc, please read about bind variables. An example is here

Upvotes: 0

Idan Arye
Idan Arye

Reputation: 12603

The SQL statement is parsed and run in a different environment than your Java code - different language, different scope, and unless you use SQLite they run in different processes or even different machines. Because of that, you can't just refer to the Java variable i from your SQL code - you have to either inject it or use special API.

The first option - injection - is to simply put the value of i inside the string: 

"SELECT max( millisFromMid ) FROM stockInfo1 WHERE ( millisFromMid <=  34200000 + (120000)*"+i+" ) GROUP BY stockID"

Personally, I prefer to do it using String.format - but that's just me.

String.format("SELECT max( millisFromMid ) FROM stockInfo1 WHERE ( millisFromMid <=  34200000 + (120000)*%d ) GROUP BY stockID",i)

The second option - via API - is more complex but also faster(especially if you combine it with transactions) - using SQL parameters. You need to create a prepared statement:

PreparedStatement preparedStatement = connection.prepareStatement("SELECT max( millisFromMid ) FROM stockInfo1 WHERE ( millisFromMid <=  34200000 + (120000)*? ) GROUP BY stockID")

Notice the ? that is replacing the i - this is your parameter. You create prepareStatement once - before the loop, and inside the loop you set the parameter every time and execute it:

for (int i=0; i< 195; i++) {
    preparedStatement.setInt(1,i); //Set the paramater to the current value of i.
    resultSet = preparedStatement.executeQuery(); //Execute the statement - each time with different value of the parameter.
}

Upvotes: 5

Thorbj&#248;rn Ravn Andersen
Thorbj&#248;rn Ravn Andersen

Reputation: 75356

The problem os that the database is not told about the value of i. Instead make Java put the value into the string and then submit it to the database.

string sql = "select * from foo where bar=" + i;

You need to remember at all times that the database only knows what you explicitly tell it, and that you are essentially writing code at runtime that will execute on another host.

Upvotes: 1

Related Questions