Need advice on fixing my JAVA query statement?

Question

My JAVA script consists of 2 JAVA classes: RMS, queryRMS In the RMS class I call the method in the queryRMS class

RMS Java Class (I left out the start execution part, below is just the method)

    for (int i = 1; i <= itemCount; i++) {
        GlobalVariables.numberRow = i;
        JavaDatapool.settings();

        String item = queryRPM.connectDB_Multi(configFile,"SELECT ITEM FROM ORDSKU WHERE ORDER_NO  = '" + orderNo + "' ORDER BY ITEM ASC",i);
        JavaDatapool.writeXLS("item",item,GlobalVariables.sheetXLS);
        sleep(1);

    }

queryRMS JAVA class

public static String connectDB_Multi(String configFile, String query, int i) throws FileNotFoundException, IOException, SQLException, ClassNotFoundException{
    Properties p = new Properties();
    p.load(new FileInputStream(configFile));

    String serverName = (p.getProperty("RMS_DBServerName"));
    String portNumber = (p.getProperty("RMS_PortNumber"));
    String sid = (p.getProperty("RMS_SID"));
    String url = "jdbc:oracle:thin:@//" + serverName + ":" + portNumber + "/" + sid;
    String username = (p.getProperty("RMS_Username"));
    String password = (p.getProperty("RMS_Password"));
    //  jdbc:oracle:thin:@//localhost:1521/orcl

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection connection = DriverManager.getConnection(url,username,password);     
    String setr = null;
    try {      
        Statement stmt = connection.createStatement();

        try {ResultSet rset = stmt.executeQuery(query);
            try {
                while(rset.absolute(i))   
                    setr = rset.getString(1);
                    return setr;  
            }        
            finally {
                try { rset.close(); 
                } 
                catch (Exception ignore) {}

            }
        } 
        finally {
            try { stmt.close(); 
            } 
            catch (Exception ignore) {}
        }
    } 
    finally {
        try { connection.close(); 
        } 
        catch (Exception ignore) {}

    }
}

So what it does is call the connectDB_multi class and then returns the String where the next part is saving it inside an Excel worksheet.

The loop should return all rows, one at a time and then save it inside the Excel worksheet.

In the second time in loop the query is faulted, eventhough the query should return 1 column consisting of 2 rows.

the original contained the part while(rset.next()) instead of while(rset.absolute(i)) but next only return the first row everytime. so the script works when only one column and row is retrieved from the Database.

`GlobalVariables.numberRow`? `JavaDatapool.settings()`? What sort of library is that? — cherouvim, Nov 25 '11 at 08:03
Those are for resetting the rownumber of the and the excel sheetname — Eve, Nov 25 '11 at 08:05
The code is build this way that other users without JAVA experience can use the code. The only thing they need to do is call the different methods. That's why it's very inefficient, if I build the code my way it would be much easier. — Eve, Nov 25 '11 at 09:41

score 5 · Accepted Answer · answered Nov 25 '11 at 08:16

5

Your logic looks a bit messed up.

Look at the first loop you posted. You are, effectivly, executing:

SELECT ITEM FROM ORDSKU WHERE ORDER_NO = '" + orderNo + "' ORDER BY ITEM ASC

itemCount number of times. Each time you execute it, you are attempting to access the n:th row, n being loop counter. Do you see a problem there? How do you know that the query will return itemCount number of rows? Because if it doesn't, it will fail since you are attempting to access a row that doesn't exist.

What I suspect you WANT to do is something like this

Statement stmt = connection.createStatement();
ResultSet rset = stmt.executeQuery(query);
while(rset.next()) {
    JavaDatapool.writeXLS("item",rset.getString(1),GlobalVariables.sheetXLS);
}

You should also seriously consider using some form of connection pooling to avoid having to re-open new connections all the time as that is a pretty time-consuming operation.

answered Nov 25 '11 at 08:16

pap

27,064
6
41
46

I tried what you wrote now i'm at the beginning again where it only returns 1 single itemnumber (1 row) instead of 2 two. – Eve Nov 25 '11 at 08:34
Are you sure the query actually returns more than one row? – pap Nov 25 '11 at 09:15
yes i am sure when I run the query it returns two itemnumbers of the column 'item' – Eve Nov 25 '11 at 09:38
Then perhaps it's your JavaDatapool.writeXLS method this is not working properly and overwriting the first value with the second. – pap Nov 25 '11 at 11:58

score 1 · Answer 2 · answered Nov 25 '11 at 08:34

This code seems very inefficient, for each row you want to fetch from the database you read a property file, create a connection, select all matching rows, skip ahead to the row you want and return just that row. (Or at least I think that is what you are trying to do).

Your code

while(rset.absolute(i))   
    setr = rset.getString(1);

is probably an infinite loop as it will continue to go to the same row as long as it is ok to go to that row, so either that row does not exist (and the while exists) or the row does exist (and while continues forever).

You should probably restructure your program to only do one select and read all rows that you want and store them in your excel file. While doing this, you can debug to see if you actually are getting the data you expect.

Apart from the inefficient code of creating new connections and querying once for each row, how do you know how many rows you want?

I think in the end you want something like this

....
while(rset.next()) {
    JavaDatapool.writeXLS("item",item,GlobalVariables.sheetXLS);
}

And what is the sleep(1) support to accomplish?

score 0 · Answer 3 · answered Nov 25 '11 at 09:01

FYI: if you open and close statement too often as your logic or pap's solution, you can get the " java.sql.SQLException: ORA-01000: maximum open cursors exceeded" error message.

I suggest you to not do 'too much generalize'. I saw a lot of OOP programmers overdid generalization and that is painful. You should design by a goal and the goal should not be 'just alignment' nor 'code look beautiful', it has to have a purpose for designing.

Need advice on fixing my JAVA query statement?

3 Answers3