CODEWITHSUNDEEP
javamultithreadingjdbc
ArrchanaMohan
ArrchanaMohan

Reputation: 2566

How to access the resultset values using multi threads

I have created below JDBC program to get the record from database

Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@db-user-rw-a.qa.amazon.com:1100/MONEY";
String username = "amazon212313";
String password = "XXXXX";

System.out.println("Connecting database...");
Connection connection = null;
if (connection == null) 
{
    try {
        connection = DriverManager.getConnection(url, username,password);
        System.out.println("Database connected!");
        } 
    catch (Exception ex)
        {
        System.out.println("Database Connection Failed...!!!");
        System.out.println(ex);
        }
}

Statement statement=connection.createStatement();

ResultSet rs1=statement.executeQuery("select account_number,flag,flag2,flag3,flag4,flag5,flag6,flag7,amount from transaction_p2");

 ResultSetMetaData metadata = rs1.getMetaData();
 int columnCount = metadata.getColumnCount(); 
 System.out.println(columnCount);

 while(rs1.next())
 {
      // account_number in if condition

     if(rs1.getString(1).contains("2195281819521610731"))
     {
     System.out.println(rs1.getString(1));
     }
 }


connection.close();

I have more than 99000 records in data base and the above program is working fine but its taking huge time to retrieve the specific account number values. It's taking more than 20 minutes (Some times beyond that) to retrieve the value.

Is there any other way to speed up the search value in result set. Like creating 100 thread to search the specific account number if it found then It should return the value.

Also, there is possibly a duplicate account number in the database. All I want that I need multithreads to access the resultset and search the specific account number and return all the found account number as per if condition.

Share yours idea to achieve this task.

Updated the program with WHERE clause and observed below in console:

Updated Code:

  ResultSet rs1=statement.executeQuery("select account_number,flag,flag2,flag3,flag4,flag5,flag6,flag7,amount from transaction_p2 where account_number=2195281819521610731");
     ResultSetMetaData metadata = rs1.getMetaData();
     int columnCount = metadata.getColumnCount(); 
     System.out.println(columnCount);
     System.out.println(rs1.next()+"\t boolean value");

     while(rs1.next())
     {
          // account_number in if condition
         System.out.println(rs1.getString(1));
     }

Output:

Connecting database...
Database connected!
12
false    boolean value

If I used execute() instead of executeQuery() which is returning "TRUE" but not sure how can i get resultset details.

The above account number queried in db and got result.

enter image description here

Upvotes: 1

Views: 872

Answers (1)

Lorenzo Notaro
Lorenzo Notaro

Reputation: 89

Why not just add the condition to your query?

instead of

   select account_number,flag,flag2,flag3,flag4,flag5,flag6,flag7,amount from transaction_p2

do

  select account_number,flag,flag2,flag3,flag4,flag5,flag6,flag7,amount from transaction_p2 where account_number like '%2195281819521610731%'

the last part (where account_number like '%...%') will tell SQL to only include the results whose account_number contains the given string.

Upvotes: 2

Related Questions