Java: connecting to db to fetch data

Use Spring, with MyBatis or spring-jdbc for data access instead of raw JDBC.

spring-jdbc is a library wrapping basic JDBC code where you can provide callbacks to specify how you want resultsets mapped to objects and such. Mybatis is a little higher-level, you specify your queries in an xml file.

With Spring the big win is you get declarative transactions so you have no code starting and committing transactions, you also get templates for data access objects, and setting up a connection pool is easy. And there are plenty of examples for how to put the pieces together.

At some point you're better off writing your own DAO which handles all the plumbing for you.

I would do different things depending on whether I am in a single threaded batch job or inside a container.

For single threaded batch job:

• create just one connection and reuse it
• create just one prepared statement and reuse it
• optimize commit calls

For J2EE

• use the container managed data source pool

Most of the times when you write a program working with a database you do not open a connection every time you want to do something with it. Instead you open a connection at the beggining of the program and then use it every time when accessing a database.

Take a look at this example (pseudocode!):

class Database {

    private Connection conn;

    public Database() {
        connect();
    }

    private void connect() {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(dbUrl);
    }

    public void close() {
        conn.close();
    }

    private ResultSet readSth() {
        statement = conn.createStatement();
        return statement.executeQuery("select * from FEEDBACK.COMMENTS");
    }

    private void doSth() {
        // do sth else with conn
    }

}

You can create a class having static method there, that returns you an instance of connection like below:

import java.sql.*;
import java.util.Properties;

public class Getconnection{
    private static final String dbClassName = "com.mysql.jdbc.Driver";
    private static final String CONNECTION ="jdbc:mysql://127.0.0.1/dbUserData";
    private static Properties p = new Properties();
    public static Connection getConnection(){
        p.put("user","root");
        p.put("password","library");
        try{
            Class.forName(dbClassName);
            Connection con=DriverManager.getConnection(CONNECTION,p);
            return con;
        }
        catch(Exception ie){
            ie.printStackTrace();
            return null;
        }
    }
}

So that you do not need to create different instances of connection, and change only at one place if you want to...

I think you are suffering from basic code organization. For example, you should only create the connection once and then pass it around to whatever methods need it. Typically, people use connection pools so that only a certain number of connections ever exist to the db (because they are expensive) and a connection pool manager will loan them out as needed and keep track of their states. There are a few connection pool libraries in Java (Apache has one) but I have had good luck using: http://sourceforge.net/projects/c3p0/

I dont really like heavy ORMs such as Hibernate but I do I like MyBatis. It lets me wright SQL and doesnt inject itself all over my domain models: http://www.mybatis.org/java.html I think you would benefit greatly by having a Data Access Object layer (DAO). This layer abstracts out communication with your data so the layers above can just fetch collections of data without worrying about the underlying SQL it took to generate that list.

I've moved away from direct JDBC access since I started using ORM (eg. Hibernate). Or why dont you have some publicly available static method to read from the db:

public ResultSet readDataBase(String query) throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager
                    .getConnection("jdbc:mysql://localhost/feedback?"
                            + "user=sqluser&password=sqluserpw");

            statement = connect.createStatement();
            return statement.executeQuery(query);

        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }

    }

and you can have similar methods when you do update/delete, etc