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

Question

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.

Answer 1

There are various ways to connect to a relational database to store and retrieve information. Depending on your needs you can go with a low level implementation or a higher one.

You could directly use JDBC. You need a driver that knows how to talk to your particular database, you open a connection, you prepare a statement with some SQL query, set the needed parameters for the statement (if any), execute the statement, get back a result set, iterate the result set to create objects out of the results, then close the resources you have used (result set, statement, connection).

That's the most low level way of doing it. But it has some disadvantages:

• You have a lot of boiler plate code: get connection, create statement, execute statement, loop through results, build objects, release used resources. You have to do these each time you want to run some SQL. Only the SQL is different each time, the rest you have to do again and again and again.
• you open a database connection each time you run a query. There is some overhead involved in that and depending on how many queries you run at a time, you could end up opening too many connections. Some database might have limitations per client so you can't go too high.

For limiting the connections you open you might use a connection pool like Apache DBCP, C3P0, HikariCP, etc. You still have the same boiler plate code, but now instead of creating and closing a connection, you borrow and return one to the pool. More efficient.

Now, since the boiler plate is the same each time, why not move it away in some framework or library that does it for you and you just concentrate on writing the SQL. That's what a data mapper like MyBatis does, for example. You configure it once, write the SQLs you need and map them to methods, tell MyBatis how to map the result from rows to objects, and all the boiler plate is handled by MyBatis for you. Run a method and get back the objects you want.

With MyBatis you only need to write the SQL. But some people don't even want to bother with that. You have boiler plate code and the connections handled by some library/framework for you, but why not get rid of SQL also?

That's what ORMs like Hibernate do. You map classes to tables and then Hibernate handles everything for you. It generates the needed SQL when you want to save or retrieve data from the database. Once you configure Hibernate, you can pretend that the database does not exist (at least for a while).

Each of these methods have advantages and disadvantages.

• with JDBC you need to write a lot of boiler plate code, manage transactions on your own, make sure you don't leak resources, etc. It's low level;
• with data mappers you need to write the SQLs. The data mapper does not pretend there is no database, you have to deal with it.
• with ORMs you can pretend there is no relational database involved. You deal with objects only. ORMs are great for CRUD applications but for others, an ORM might get you in some trouble. There is this thing called the object-relational impedance mismatch that shows it's ugly head. And when it does, performance is usually the first thing that goes down the drain.

These are your options. Look at your application and see which solution might be more appropriate. Considering your question, you might want to use something lightweight (not as low as direct JDBC, and not as high as Hibernate either).

But don't reinvent the wheel. Something like Commons DbUtils for example can be a good starting point. It takes JDBC boiler plate code away from you, without changing/adding to much to the way you interact with a database.

Answer 2

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

Answer 3

So to call the query you would do something like this:

    String Querry = "SELECT seatID,movieID,Tanda,seatNum,isBooked,BookedBy FROM ROOT.SEATS";
    Map<String, List< Object>> map = getListQuerry(Querry);
    map.forEach((key, value) -> System.out.println(key + ":" + value));

The methods will look something like this:

Map<String, List< Object>> getListQuerry(String query) {
    System.out.println("getListQuerry will run SQL Querry:\n\t\t" + query);

    Map<String, List< Object>> result = new HashMap<>();
    String[] keys = query.replace(" ", "").replace("SELECT", "").split("FROM")[0].split(",");
    for (String Key : keys) {
        result.put(Key, new ArrayList<>());
    }

    try (Connection Connection = DriverManager.getConnection(host, DBUsername, DBpassword)) {
        PreparedStatement stmt = Connection.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery();

        while (resultSet.next()) {
            for (int i = 1; i < keys.length; i++) {
                result.get(keys[i]).add(resultSet.getInt(keys[i]));
            }
        }
    } catch (SQLException ex) {
        Logger.getLogger(Stage1Bootup.class.getName()).log(Level.SEVERE, null, ex);
        System.out.println("Query failed to run");
    }
    return result;
}

Answer 4

For small tasks there is no need to bring monsters like Hibernate into picture. If you want to perform direct SQL queries without ORM layer your approach is good. Just do not forget to load driver class ahead.

public DatabaseConnector(String url, String user, String password) {

    Class.forName("com.mysql.jdbc.Driver");

    databaseConnection = DriverManager.getConnection(url, user, password);

}

For other aspects it is up to you how to manage connection, how long you'd like to keep it open, reuse it or not, and so on. Same is about preparedStatements...