DAO Layer for applications

package com.anshul.projects.drfirst.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import com.anshul.projects.drfirst.commons.Constants;

public class LocalDBConnection implements Constants{

                private static Connection conn = null;

                private static Statement stmt = null;

                private static PreparedStatement pstmt = null;

                private static String url = "";

               

                public static Connection getConnection(String dbName) {

                                try {

                                                if (conn != null && !conn.isClosed()) {

                                                                return conn;

                                                }

                                } catch (SQLException e) {

                                                System.out.println("Unable to estalish Connection because of "+e.getLocalizedMessage());

                                }

                                conn = makeAConnection(dbName);

                                return conn;

                }

               

                private static Connection makeAConnection(String dbName){

                                try {

                                                String username = null;

                                                String password = null;

                                                Class.forName(Drivers.MY_SQL);

                                                if (dbName.trim().equalsIgnoreCase(Databases.LOCAL_DB_NAME)) {

                                                                url = URLs.LOCAL_DB_URL;

                                                                username = Users.LOCAL_DB_USERNAME;

                                                                password = Passwords.LOCAL_DB_PASSWORD;                                               

                                                }

                                                conn = DriverManager.getConnection(url, username, password);

                                                System.out.println("Connection successful");

                                                return conn;

                                } catch (Exception e) {

                                                e.printStackTrace();

                                }

                                return null;

                }

               

                public static void closeConnection() {

                                if (conn != null) {

                                                try {

                                                                conn.close();

                                                                System.out.println("Connection closed.");

                                                                conn = null;

                                                } catch (SQLException e) {

                                                                e.printStackTrace();

                                                }

                                }

                                if(stmt !=null){

                                                try {

                                                                stmt.close();

                                                                System.out.println("Connection stmt closed.");

                                                                stmt = null;

                                                } catch (SQLException e) {

                                                                e.printStackTrace();

                                                }

                                }

                }

                public static Statement getStatement(String dbName) {

                                if (stmt != null) {

                                                return stmt;

                                }

                                try {

                                                stmt = LocalDBConnection.getConnection(dbName).createStatement();

                                                return stmt;

                                } catch (SQLException e) {

                                                e.printStackTrace();

                                }

                                return null;

                }

               

                public static PreparedStatement getPreparedStatement(String dbName, String query) {

                                if (pstmt != null) {

                                                return pstmt;

                                }

                                try {

                                                pstmt = (PreparedStatement) LocalDBConnection.getConnection(dbName).prepareStatement(query);

                                                return pstmt;

                                } catch (SQLException e) {

                                                e.printStackTrace();

                                }

                                return null;

                }

               

                public static void main(String args[]){

                                String query = "select * from update_commands";

                                try {

                                                ResultSet st = LocalDBConnection.getStatement(Databases.LOCAL_DB_NAME).executeQuery(query);

                                } catch (SQLException e) {

                                                e.printStackTrace();

                                }

                }

               

}

package com.anshul.projects.drfirst.test;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.anshul.projects.drfirst.commons.Constants.Databases;

import com.anshul.projects.drfirst.dao.LocalDBConnection;

public class TestMe {

      public static void main (String args[]){

            String query = "insert into login (username, password) values (?,?)";

            PreparedStatement pstmt = LocalDBConnection.getPreparedStatement(Databases.LOCAL_DB_NAME, query);

            try {

                  pstmt.setString(1, "A'nshul");

                  pstmt.setString(2, "S\"ood");

                  int j = pstmt.executeUpdate();

                  System.out.println(j);

                  LocalDBConnection.closeConnection();

            } catch (SQLException e) {

                  e.printStackTrace();

            }

      }

}