SodhanaLibrary

I built many web applications with java and tomcat server. I came across so many problems in handling the flow of programs. Atlast I figured out this below standard way.

Some Standards

1. Catch exception when you can continue with remaining program  ( Closing statement wont break the process, so you can continue with remaining program)
2. Throw exception when you can't continue with remaining program  ( Failing to get Database connection will break the process, so you can not continue with remaining program)
3. Its better to catch exceptions at Servlet, so that it is possible to give appropriate message to user
4. To avoid SQL Injection attack, Use PreparedStatement to execute queries
5. Use one Java Class to creating and closing Connections and Statements.
6. Use one Java Class to execute all queries
7. Its better to use Pojo classes to carry out data.

Download Sample Java Web Application

Generate Java DAO for relational data table - Online

DBConn.java

This Java Class is to create Connections and Closing connections and statements.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author srinivas
 * This file is to create Connections and Closing connections and statements.
 */
public class DBConn {
    Connection con = null;
    static String className = "class sodhana.sdb.connection.DBConnection";

    /**
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     * To Create Connections
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = null;
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/world", "root", "srinivas");
        return connection;
    }

    /**
     * @param con
     * @param stmt
     * @param rs
     * To close statements, result sets and Connection 
     */
    public static void close(Connection con, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param con
     * @param stmt
     * To close statements and Connection
     */
    public static void close(Connection con, Statement stmt) {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param con
     * @param pstmt
     * @param rs
     * To Close PreparedStatement and Connection
     */
    public static void close(Connection con, PreparedStatement pstmt,
            ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param con
     * To close connection
     */
    public static void closeConnection(Connection con) {
        try {
            if (con != null)
                con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param conn
     * @throws SQLException
     * To Commit and Close the connection
     */
    public static void commitAndClose(Connection conn) throws SQLException {
        conn.commit();
        conn.close();
    }

}

Exception Class for Database Exceptions

Whenever we need to throw an exception, throw the this exception.

public class DBException extends Exception {

    private static final long serialVersionUID = 1L;

    private String message;

    public DBException(String string) {
        message = string;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }
    
}

Example

Lets take an example of Employee Table which consists of ID and Name.

EmployeeDAO.java

This java class is for executing all type of  SQL Queries. This is Data Access Object for above Employee Table

public class EmployeeDAO {

    /**
     * @throws DBException 
     * @throws ClassNotFoundException
     * @throws SQLException
     * To Create Table
     */
    public static void createTable() throws DBException {
        Connection conn = null;
        Statement s = null;
        try {
            conn = DBConn.getConnection();
            s = conn.createStatement();
            s.executeUpdate("create table EMP(id varchar(30),name varchar(200),primary key(id))");
            DBConn.close(conn, s);
        } catch (SQLException | ClassNotFoundException e) {
            DBConn.close(conn, s);
            throw new DBException("Excepion while accessing database");
        }

    }

    /**
     * To Drop Table
     * @throws DBException 
     */
    public static void dropTable() {
        Connection conn = null;
        Statement s = null;
            try {
                conn = DBConn.getConnection();
                s = conn.createStatement();
                s.executeUpdate("drop table EMP");
                DBConn.close(conn, s);
            } catch (ClassNotFoundException | SQLException e) {
                DBConn.close(conn, s);
            }
    }

    /**
     * @param emp
     * @throws DBException 
     * @throws SQLException
     * @throws ClassNotFoundException
     * To Insert Into Data into Table
     */
    public static void insertRow(EmployeePojo emp) throws DBException{
        Connection conn = null;
        PreparedStatement psInsert = null;
        try {
            conn = DBConn.getConnection();
            psInsert = conn.prepareStatement("insert into "
                    + "EMP(id,name) values (?,?)");
            psInsert.setString(1, emp.getId());
            psInsert.setString(2, emp.getName());
            psInsert.executeUpdate();
            DBConn.close(conn, psInsert);
        } catch (ClassNotFoundException | SQLException e) {
            DBConn.close(conn, psInsert);
            throw new DBException("Excepion while accessing database");
        }
    }

    /**
     * @param emp
     * @throws DBException 
     * @throws SQLException
     * @throws ClassNotFoundException
     * Update data in EMP table
     */
    public static void updateRow(EmployeePojo emp) throws DBException{
        Connection conn = null;
        PreparedStatement psInsert = null;
        try {
            conn = DBConn.getConnection();
            psInsert = conn
                    .prepareStatement("update EMP set name = ? where id = ?");
            psInsert.setString(2, emp.getId());
            psInsert.setString(1, emp.getName());
            psInsert.executeUpdate();
            DBConn.close(conn, psInsert);
        } catch (ClassNotFoundException | SQLException e) {
            DBConn.close(conn, psInsert);
            throw new DBException("Excepion while accessing database");
        }
    }

    /**
     * @param emp
     * @throws DBException 
     * @throws SQLException
     * @throws ClassNotFoundException
     * To Insert or Update Table
     */
    public static void insertOUpdateRow(EmployeePojo emp) throws DBException{
        if(searchEmployee(emp.getId())){
            updateRow(emp);
        } else {
            insertRow(emp);
        }
    }

    /**
     * @param conn
     * @param id
     * @throws DBException 
     * @throws SQLException
     * To delete data from Table
     */
    public static void deleteRow(String id){
        Connection conn = null;
        PreparedStatement psInsert = null;
        try {
            conn = DBConn.getConnection();
            psInsert = conn
                    .prepareStatement("delete from EMP where id=?");
            psInsert.setString(1, id);
            psInsert.executeUpdate();
            psInsert.close();
            DBConn.close(conn, psInsert);
        } catch (SQLException | ClassNotFoundException e) {
            DBConn.close(conn, psInsert);
        }
    }

    /**
     * @param id
     * @return
     * @throws DBException 
     * @throws SQLException
     * @throws ClassNotFoundException
     * Read data from Table
     */
    public static EmployeePojo selectEmployee(String id) throws DBException {
        Connection conn = null;
        PreparedStatement psInsert = null;
        ResultSet res=null;
        EmployeePojo emp = new EmployeePojo();
        try {
            conn = DBConn.getConnection();
            psInsert = conn
                    .prepareStatement("select * from EMP where id = ?");
            psInsert.setString(1, id);
            res = psInsert.executeQuery();
            if (res != null) {
                while (res.next()) {
                    emp.setId(res.getString(1));
                    emp.setName(res.getString(2));
                }
            }
            DBConn.close(conn, psInsert, res);
        } catch (ClassNotFoundException | SQLException e) {
            DBConn.close(conn, psInsert, res);
            throw new DBException("Excepion while accessing database");
        }
        return emp;
    }

    /**
     * @param id
     * @return
     * @throws DBException 
     * @throws SQLException
     * @throws ClassNotFoundException
     * To search data
     */
    public static boolean searchEmployee(String id) throws DBException{
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet res = null;
        boolean b = false;
        try {
            conn = DBConn.getConnection();
            ps = conn
                    .prepareStatement("select * from EMP where id = ?");
            ps.setString(1, id);
            res = ps.executeQuery();
            if (res != null) {
                while (res.next()) {
                    b = true;
                }
            }
            DBConn.close(conn, ps, res);
        } catch (ClassNotFoundException | SQLException e) {
            DBConn.close(conn, ps, res);
            throw new DBException("Excepion while accessing database");
        }
        return b;
    }

    /**
     * Droping and Creating Table
     * @throws DBException 
     */
    public static void resetEMP() throws DBException {
            EmployeeDAO.dropTable();
            EmployeeDAO.createTable();
    }

    /**
     * @param args
     * @throws DBException 
     */
    public static void main(String args[]) throws DBException {
        resetEMP();
    }

}