Retrieving data from jdbc

Question

I have a string array which holds the name of 500 stocks. Now i have a table in my mysql database which holds the symbol for more than 1000 stocks alonwith their name. What i would like to do is retrieve the symbol of those 500 stocks from the table i have. I have tried the following code

Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql//localhost/mydatabase","user","pass");
PreparedStatement stmt = conn.prepareStatement("SELECT (NAME) FROM stocks1 WHERE FULLNAME =?"); 
for(int i1=0;i1<i;i1++)
{   
  stmt.setString(1, name[i1]); 


  stmt.addBatch(); 
} 
ResultSet t=stmt.executeQuery(); 
while(t.next())
  System.out.println(t.getString("NAME"));

But it doesnt work. Nothing is printed. I think i am making a mistake in stmt.addBatch(). Also if i wanted the name[i1] to be follwed by a wildcard charcter(%) how would i do this.

Batching is for DML statemnts, not for queries. You need to run the SELECT for each ID you want to retrieve or use a `WHERE IN (...)` (which cannot be used in a prepared statement) — , Mar 27 '12 at 11:33
see http://stackoverflow.com/questions/7899543/in-java-can-preparedstatement-addbatch-be-used-for-select-queries — Riddhish.Chaudhari, Mar 27 '12 at 11:41
@Riddhish.Chaudhari- The link in the answer to the above question seems to have some error.@ahorsewithnoname,- So do i have to put the select statement into a for loop and run it. — user1092042, Mar 27 '12 at 11:45

score 1 · Accepted Answer · answered Mar 27 '12 at 11:52

1

You could create a table for the stocks in your string array and do a join with your stocks1 table.

Let's say the table for your arraystocks is named stocksperuser with one column FULLNAME the code looks like this:

Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql//localhost/mydatabase","user","pass");
PreparedStatement stmt = conn.prepareStatement("SELECT NAME FROM stocks1 WHERE FULLNAME is in (SELECT FULLNAME FROM arraystocks)"); 
ResultSet t=stmt.executeQuery(); 
while(t.next()) {
    System.out.println(t.getString("NAME"));
}

answered Mar 27 '12 at 11:52

Andreas

1,183
1
11
24

That would mean creating a new table. Is there any other way of doing this. – user1092042 Mar 27 '12 at 11:56
You could use temp tables, which are automatically dropped after closing an transaction. But i'm not sure if mysql supports this. But with no Tables you have to loop, like you mentioned yourself. If your query is performance critical you must use 'where in (..)'. But the length of the statements is normally limited, so you have to process chunks of stocknames, e.g. 100 Stocks in one query. – Andreas Mar 27 '12 at 12:01
I used you answer but it throws an syntax exception at is in(SELECT FULLNAME FROM ...). – user1092042 Mar 27 '12 at 12:08
I hadn't a db to test. But the error must be the 'is in' only use in. SELECT NAME FROM stocks1 WHERE FULLNAME in (SELECT FULLNAME FROM arraystocks) – Andreas Mar 27 '12 at 12:41
It prints nothing for some weird reason. But no problem. I iterated through each select query. I posted a question but no harm in asking anyway. How do i perform a case insensitve select – user1092042 Mar 27 '12 at 12:53
If you use like it should 'normaly' case-insensitive. It depends on the collation and character set. see http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html To be shure there are no whitespaces at the end you could use like '%%' or use sql functions upper() and trim(). http://dev.mysql.com/doc/refman/5.0/en/string-functions.html – Andreas Mar 27 '12 at 13:02

score 1 · Answer 2 · answered Mar 27 '12 at 12:44

For including wildcard, you can do as shown below.

In your select statement remove = and add LIKE

PreparedStatement stmt = conn.prepareStatement("SELECT (NAME) FROM stocks1 WHERE FULLNAME LIKE ?");

add % in the setString method.

ps.setString(1, name[i1]+ "%");

Also , for empty rows issue , please check if both the comparison column's case are the same,either they should be in upper or in lower. If its not , then you can convert while comparing as shown below.

SELECT (NAME) FROM stocks1 WHERE upper(FULLNAME) LIKE ?   

ps.setString(1, upper(name[i1])+ "%");

Retrieving data from jdbc

2 Answers2