How to use a list in JDBC

Question

I need to retrieve data from 3 different tables. Initially i used "joins" in my query to get the result. One of 3 tables consists of large data which is making my program to run very slow. So, i have divided the queries into two. 1)One of my query returns list of strings whereas another query should return integer.

Now my doubt is how to make use of this list in my query??

I have tried the following code but it throws exceptions.. Please suggest me on this

1.Method1: Which returns a list

public ArrayList<String> custList()
{
   ArrayList<String> customerList=new  ArrayList<String>();
 try {
        Class.forName("org.postgresql.Driver");
       conection=DriverManager.getConnection("","");
        Statment =coonection.createstatement;
       String CustomerInfo="select username, place from customer where customerID >=100 
              and customerID <=200";

       ResultSet result1=statement.executeQuery(CustomerInfo);
       while(result.next)
          {
           String cust=rs.getString(username);
           customerList.add(cust);
            }

        }
      //required catch exceptions
      //close resultset,connection,statement
     return customerList;

 }

2.Method:2 I need to use this customer username in other query.. I tried the following but not successful thou

     public int useCount()
        {
        int count=0;

         //Using a method of same class, assuming my class name is customer
         ArrayList<String> custtDetails=customer.custList();
     try {
        Class.forName("org.postgresql.Driver");
       conection=DriverManager.getConnection("","");
        Statment =coonection.createstatement;

       String C=custtDetails.get(count);
       String CustomerInfo="select count(*) login  from customerdetails 
               where custUsername=C"; // <-- I need to use my list of strings here       

       ResultSet result1=statement.executeQuery(CustomerInfo);
       while(result.next)
          {
           String cust=rs.getString(username);
           customerList.add(cust);
            count++; // <--- I think my mistake goes here ! How to use my list now?? 
            }

        }
      //required catch exceptions
      //close resultset,connection,statement
     return useCount;

 }

         }

Please suggest me !!

Answer 1

Solution

Step-1

Create a class with instance variables ( say EID and ENAME ) and write setter's and getter's ( Say the class name is Employee )

public class Employee implements java.io.Serializable {

private Integer eid;
private String ename;


public Integer getEid() {
    return eid;
}

public void setEid(Integer eid) {
    this.eid = eid;
}

public String getEname() {
    return ename;
}

public void setEname(String ename) {
    this.ename = ename;
}
}

Step-2

Now write a class (say RetriveRecords)

public class RetriveRecords {

 public ArrayList listMethod(){
  ArrayList list = new ArrayList();
 try {
        //Standard JDBC Code
    Statement stmt = con.createStatement();
    String query = "select * from emp"; // Your own Query

    ResultSet rs = stmt.executeQuery(query);
    while(rs.next()) {

            Employee e = new Employee();
            e.setEno(rs.getInt(eno);
            e.setEName(rs.getString("ename");
            list.add(e);
}
   con.close();      
}
catch(Exception e){
    e.printStackTrace();
}
  return list;
}
}

Step-3

public class DisplayRecords {
 public static void main(String[] args){

        RetriveRecords rr = new RetriveRecords();
        ArrayList a = rr.listMethod();
        Iterator i = a.iterator();

       while(i.hasNext()){

              Employee e =(Employee) i.next();
              System.out.println(e.getEno());
              System.out.println(e.getName());
}
}
}

Done...Actually, to overcome the limitation of ResultSet object (i.e ResultSet object cannot be transfered)...Hibernate ORM uses the same code internally.