What is the best way to take database connection for pollling programs in java

Question

I am creating a polling program in java using TimerTask for to send email and other notifications automatically. Now this program every second check the database for any new data available.

Now i am creating connection like below

Connector class its holds database details and return a connection.

public class Connector implements Serializable {

    private static final long serialVersionUID = 1L;
    private ResourceBundle prop = ResourceBundle.getBundle("dbdetails");

    public Connection getConnection() throws Exception {
        Connection con;
        Class.forName((String) prop.getString("DB_DRIVER"));
        con = DriverManager.getConnection((String) prop.getString("DB_URL"),
                (String) prop.getString("DB_USER"),
                (String) prop.getString("DB_PASS"));
        return con;

    }

}

My polling class

public class EmailPoller extends TimerTask {
    private static Logger logger = Logger.getLogger(EmailPoller.class);
    private Connector connector = new Connector();

    @Override
    public void run() {
        Connection con = null;
        PreparedStatement ps = null, ps1 = null;
        ResultSet rs = null;
        try {
            con = connector.getConnection();
            ps = con.prepareStatement("select to_addr,subject,content,id from email_notification where status='A'");
            ps1 = con
                    .prepareStatement("update email_notification set status='I' where id=?");
            rs = ps.executeQuery();
            while (rs.next()) {
                Boolean b = Mailer.sendMail(rs.getString(1), rs.getString(2),
                        rs.getString(3));

                if (b) {
                    ps1.setInt(1, rs.getInt(4));
                    ps1.executeUpdate();
                }
            }
        } catch (Exception ex) {
            logger.info("Email Poller Error : " + ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (ps1 != null) {
                    ps1.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (Exception ex) {
                logger.info("Email Poller Error : " + ex);
            }
        }
    }
}

after send a email i am updating a flag. Mailer is sending mail perfectly.

Whether this is a correct approach to check the database for data or any other best way is there to connect to the database?

Answer 1

Since you need to poll the database at a particular time period, - you should never create a connection every time you seek for something in the database. Create a connection once and reuse it, or let DataSource handle everything. You just request DataSource a connection and it gives you a connection.

You could slightly modify your Connector class as below to allow DataSource Implementations to handle connection pooling.

The below example uses the MySql Implementation of the DataSource interface. You could however change the implementations depending on what database you work on, by simply adding the corresponding jar to your classpath.

    class Connector implements Serializable {

    private static final long serialVersionUID = 1L;
    private static ResourceBundle prop = ResourceBundle.getBundle("dbdetails");
    private static MysqlDataSource dataSource = null;

    // Dont allow any instance of this class
    private Connector(){
    }

    private static void initDataSource()
    {
        try 
        {
            dataSource = new MysqlDataSource();
            dataSource.setURL(prop.getString("DB_URL"));
            dataSource.setUser(prop.getString("DB_USER"));
            dataSource.setPassword(prop.getString("DB_PASS"));
        } 
        catch (SQLException e) {
            // handle the Exception according to your application demand
        }
    } 
    /*
     * Return a connection from the datasource pool 
     */
    public static Connection getConnection() throws Exception {
       if(dataSource == null)
       {
           initDataSource();
       }
       return dataSource.getConnection();
    }

}

Although the above becomes more efficient, it is more suitable for web applications where a large number of requests need to be handled and connections to be pooled. Since your program is a standalone code, you could ignore connection pooling and simply go ahead just ensuring that your application has only one connection at any time. You could modify the Connector class as below:

class Connector implements Serializable {

private static final long serialVersionUID = 1L;
private static ResourceBundle prop = ResourceBundle.getBundle("dbdetails");;
private static Connection con = null;

// Dont allow any instance to be created for this class
private Connector(){
}

private static void initConnection() throws  Exception
{
    Class.forName((String) prop.getString("DB_DRIVER"));
    con = DriverManager.getConnection((String) prop.getString("DB_URL"),
            (String) prop.getString("DB_USER"),
            (String) prop.getString("DB_PASS"));
}

public static Connection getConnection() throws Exception {
    if(con == null)
    {
        initConnection();
    }
    return con;
}

}

get the connection as: Connector.getConnection(), following a singleton pattern.

Answer 2

I don't believe yours is the most efficient way of getting connections. Read about Connection pools and from your timer task, acquire a connection from the pool which would be more efficient and makes your job a lot easier in terms of managing the connections.

For starters you can check : this question