Java thread on resultset oracle

Question

I am new to java. I am trying to create program to throw value to ResultSet in different thread.

     ResultSet rset = stmt
            .executeQuery("select  DISTINCT  substr(file_type,1,3) from DBM.file_table where process_time= '0015' ");

while (rset.next()){    
    r = rset.getString(1);
System.out.println(r);

Now suppose we get 5 different value from ResultSet. What i wanna do is to run below code for all value of ResultSet synchronized in different Thread.

    String spCall = "call DBM.SP_MS_" + r + "('0015', NULL, SYSDATE, 'DBM')";

    try {
        dbConnection = getDBConnection();
        stmt1 = dbConnection.createStatement();

        System.out.println(spCall);

        stmt1.executeUpdate(spCall);

        System.out.println("SUCCESS for File_Type " + r );
        System.out.println("-----------------------------");
    } catch (SQLException e) {

        System.out.println(spCall);

        stmt1.executeUpdate(spCall);

        System.out.println("SUCCESS for File_Type " + r );
        System.out.println("-----------------------------");
    } catch (SQLException e) {

Thanks

Why not using a [Thread](http://docs.oracle.com/javase/7/docs/api/java/lang/Thread.html)? — Kai, Mar 20 '13 at 16:03

Vishal K · Accepted Answer · 2013-03-20T16:54:07.560

You can proceed as follows:

public void executeQuery()
{
    ResultSet rset = stmt.executeQuery("select  DISTINCT  substr(file_type,1,3) from DBM.file_table where process_time= '0015' ");

    while (rset.next())
    {    
        final r = rset.getString(1);
        System.out.println(r);
        Thread th = new Thread ( new Runnable()
        {
            @Override
            public void run()
            {
                method(r);
            }
        });
        th.start();
    }
}
public synchronized void method(String r)
{
    String spCall = "call DBM.SP_MS_" + r + "('0015', NULL, SYSDATE, 'DBM')";

    try {
        dbConnection = getDBConnection();
        stmt1 = dbConnection.createStatement();

        System.out.println(spCall);

        stmt1.executeUpdate(spCall);

        System.out.println("SUCCESS for File_Type " + r );
        System.out.println("-----------------------------");
    } catch (SQLException e) {

        System.out.println(spCall);

        stmt1.executeUpdate(spCall);

        System.out.println("SUCCESS for File_Type " + r );
        System.out.println("-----------------------------");
    } catch (SQLException e) {}
}

score 0 · Answer 2 · answered Mar 20 '13 at 16:15

Are you sure that you are going in the right direction? You are using a stored procedure already, so why not have a stored procedure do all of the work? At that point it should be a single call to the database:

CALL DBM.SP_MS('0015', NULL, SYSDATE, 'DBM');

Internally to the Oracle Stored Procedure, you would then do whatever was actually being done individually by the _ + r versions; it receives the '0015' just the same as the original call anyway. This would simplify your Java side, and it would create a much smaller connection burden against the database (rather than going back and forth).

The stored Procedure could then return a list of File Types, r, that it affected, which you could use for logging or reporting purposes.

How this works internally depends on what the _ + r implementations are actually doing. I would suspect that it's the same.

Finally, doing so would mean that, if you actually wanted to still Thread off the call, then you would only need to create a single Thread for the overall invocation. This leads to less Threads in the system, which is generally a good thing.

Also, as you are new to Java, look into Executors and jtahlborn's link.

Java thread on resultset oracle

2 Answers2