How to avoid repetition of Database Connectivity code?

[b1izzard]

Hi everyone,
I had designed an application with Java as front end and mysql as back end. The client application uses both local database as well as Remote mysql server's database for login and other retrievals.

I don't know how to generalize the database connectivity code?, I need to access a remote database for more than couple of times,for each time I am creating a new connection(copy paste the same code) and just changing the query, my program size increases and retrievals take more time(>1min).

Connection connect1 = null;
    Statement statement1 = null;
    ResultSet rs1 = null;
    float available_qty;
    public purchase() {
        initComponents();
        try{
            int i;
            String username = "root";
            String password = "root";
             String url = "jdbc:mysql://172.16.36.55:3306/shop";
            Class.forName("com.mysql.jdbc.Driver");
            connect1 = DriverManager.getConnection(url, username, password);
            statement1 = connect1.createStatement();
            rs1 = statement1.executeQuery("select * from product");
            
            int flag=0;
            while(rs1.next())
            {
                int size=jComboBox1.getItemCount();
                int tbl_p_id=rs1.getInt("p_id");
                String tbl_pname=rs1.getString("pname");                
                int tbl_p_no_qty=rs1.getInt("p_no_qty");
                float tbl_p_cost=rs1.getFloat("p_cost");                
                for(i=0;i<size;i++)
                {
                     String temp=jComboBox1.getItemAt(i).toString();                     
                  if(temp.equals(tbl_pname))
                  {
                     flag=1;
                     break;
                  }

                }
                if(flag==0)
                    jComboBox1.addItem(tbl_pname);               
            }
        }catch(Exception e)
        {
            System.out.println("Database connectivity Error ");
        }

For simple Login the client takes more than 30 seconds,how to speed up the login process?
Any idea?

[peter_budo]

The simplest thing you may want to do is create getConnection() and putConnection() methods as showed here in JSP connectivity example. These will help you handle opening and closing of connection, where while opening provide DB URL and credentials as per your need and same code can provide you with access to either local or remote DB. Also you may want to look into use of PreparedStatemnt instead of simple Statement
You have time to learn something more advanced? Try Hibernate

[mKorbel]

that's very layz process open/close DB connections repeatedly, for intensive ... just open Db connection on Applications StartUp, and close that with Apps Exit

sure there are exists option put conn to the Timer and close that on some time is app is unused

[cretaros]

what if you put all ur connection codes jst below ur global variables, then every time acoonection is reuired u call the conection object.

[javaAddict]

If you take a closer look at the code:

for(i=0;i<size;i++)
                {
                     String temp=jComboBox1.getItemAt(i).toString();                     
                  if(temp.equals(tbl_pname))
                  {
                     flag=1;
                     break;
                  }

                }
                if(flag==0)
                    jComboBox1.addItem(tbl_pname);

No wonder it takes that long. You run the query and then inside the while loop you have another for loop. In other words, you use java code to do what can be done with sql.

From what I see you want to add all the tbl_pname from the database that are not already at the combo box?
Then maybe your query should be:
select tbl_pname from product.
Also you can try to get all the elements of the combo box before running the query, concatenate them all in a single String and do:
select tbl_pname from product where tbl_pname not in ('a','b','c',...)

And about the connection, try to have a single method that returns a new Connection every time and call that method:

Connection conn1 = null;
try{
            int i;
            conn1 = getConnection();
            statement1 = conn1.createStatement();
            rs1 = statement1.executeQuery("select * from product");
} catch (Exception e) {

} finally {
  // CLOSE THEM
  if (rs1!=null) rs1.close();
  if (statement1!=null) statement1.close();
  if (conn1!=null) conn1.close();
}

Try to have those variables locally. You don't need them visible through the entire class, since every time you run a query, you create new instances and then you close them.

Also in general
Don't mix the presentation with the queries:
jComboBox1.addItem(tbl_pname);
Have a method that returns the data from the database in a separate method in another class and call that method

[b1izzard]

@JavaAddict:I forgot the 'distinct' keyword also, will make use of it.

@all:thanks for the suggestion, Soon I will implement the code without repetition and
post the results