Java – How to write a proper class to connect to database in Java

databasejavajdbcMySQL

I'm currently writing an application and I'm struggling with the decision of how to correctly design a class to connect to a database. I came up with something like this:

public class DatabaseConnector {
    private Connection databaseConnection = null;

    public DatabaseConnector(String url, String user, String password) {
        databaseConnection = DriverManager.getConnection(url, user, password);
    }

    public void close() throws SQLException {
        databaseConnection.close();
    }
}

Additionally, in this class I have methods to pull something from database or insert and so on, and for each method a create a separate PrepareStatement and ResultSet and other objects.

My question is if this approach is correct, somehow wrong, or terribly wrong. I will be glad for every tip on designing a good communication class and how to correctly work with databases.

I use a MySQL database and JDBC for communication.

Best Answer

I prefer a two-classes design in connecting to a database. This approach is especially efficient in communicating with multiple databases within a single application:

  • The First class (CommDB) contains a generic code for connecting to DBs and manipulating with their data.
  • The Second class is a DB Proxy (MyDB) that carries a DB specific code. Obviously, in a case of multiple DBs, each DB must have its own proxy class.

To be more specific, for instance, the select method in CommDB looks something like this:

public class CommDB
{/** This class contains a generic code for data manipulation */

    public TreeMap<String,HashMap<String,String>> 
    select(Connection conn, String selectQuery) 
    { /** This is a generic method for the select operation */

        TreeMap<String,HashMap<String,String>> selectResult = 
                                    new TreeMap<String,HashMap<String,String>>();
        String[] keys = selectQuery.replace(" ","").replace("SELECT", "").split("FROM")[0].split(",");

        try {
            PreparedStatement stmt = conn.prepareStatement(selectQuery);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                HashMap<String,String> eachResult = new HashMap<String,String>();
                for (int i=1; i<keys.length; i++) { 
                    eachResult.put(keys[i],rs.getString(i+1));              
                } // for
                selectResult.put(rs.getString(1),eachResult);
            } // while

        } catch(SQLException sqlExc) {
            System.out.println(sqlExc.getMessage());
        } // try-catch

        return selectResult;
    } // select()

} // class CommDB

and a specific code for getting user info from MyDB may look like:

public class MyDB
{ /** This is MyDB Proxy Class */
    String myDbUrl = "jdbc:mysql://MyDB/mySchema";
    String myDbDriver = "com.mysql.jdbc.Driver";
    String myDbUser = "myName";
    String myDbPwd = "myPassword";
    CommDB db = new CommDB();

    public TreeMap<String,HashMap<String,String>> 
    getUsers(String namePattern) 
    { /** This method is specific for USERS data */     
        TreeMap<String,HashMap<String,String>> users = 
                new TreeMap<String,HashMap<String,String>>();           
        String selectUsers = 
         "SELECT userID, firstName, lastName, address, phone FROM USERS " + 
            "WHERE lastName like '%" + namePattern + "%'";

        Connection conn = null;
        try {           
            conn = db.connect(myDbUrl,myDbDriver,myDbUser,myDbPwd);
            users = db.select(conn, selectUsers);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (conn != null) { conn.close(); }
            } catch (SQLException sqlExc) {
                System.out.println(sqlExc.getMessage());
            } // try-catch
        } // try-catch-finally

        return users;
    } // getUsers()

} // class MyDB
Related Topic