

Some Standards Catch exception when you can continue with remaining program ( Closing statement wont break the process, so you can continue with remaining program) 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) Its better to catch exceptions at Servlet, so that it is possible to give appropriate message to user To avoid SQL Injection attack, Use PreparedStatement to execute queries Use one Java Class to creating and closing Connections and Statements. Use one Java Class to execute all queries 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. 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. 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 isfor above Employee Table