4

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.

13
  • 1
    I can't be sure from the small code sample given, but so far it looks like "reinventing the wheel" by wrapping an existing wheel.
    – Uueerdo
    Jan 6 '17 at 17:52
  • 2
    For a class with a method called close() is always a good idea to implement Closable. Jan 6 '17 at 17:53
  • There are multiple ways of doing it 'properly'. My personal preference goes out to JPA - Hibernate. It uses specific objects to store and retrieve information in a database. Here's a short tutorial: tutorialspoint.com/jpa/index.htm
    – Roel Strolenberg
    Jan 6 '17 at 17:53
  • x2 for JPA, there are several great libraries and it does 90% of the database work for you, even down to creating and managing the schema. JPA handles complex queries for you as well, all you have to do is work with Java objects.
    – Alex R
    Jan 6 '17 at 17:56
  • I would suggest javalite.io/activejdbc, lightweight ORM framework. Info on JDBC terms: stackoverflow.com/questions/1039419/…
    – dkb
    Jan 6 '17 at 17:58
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
6
  • It looks really cool. When i got a lot of queries (selects, inserts. .etc) where shoud i save them ? For now i got separetly methods for every query.
    – Piter _OS
    Jan 12 '17 at 17:57
  • Hello Piter_OS. Thank you for your positive feedback. In my example, I use a select query (getUsers) in the ProxyDB class and, using this style, you have to keep all your queries in the ProxyDB; but, you can minimize code duplication in your queries by arranging generic codes into CommDB methods. Theoretically, you can design CommDB methods in such a way that you will not need to write any code for your queries as well as do not need to have a ProxyDB class at all, In this case, you just call generic CommDB methods directly right from your application module.
    – Noviff
    Jan 13 '17 at 8:58
  • For now my Class is 300 lines long and it's becoming hard to maintain. So i'm looking for some tips how to write good quality code with database. This is my first project. Is it good to have code for connecting to database in many classes and have global connection or to let one class make all actions on database ?
    – Piter _OS
    Jan 13 '17 at 16:07
  • Piter_OS, it is always a good coding practice to consolidate your common code into generic methods as it improves a structure of your application. Connecting to SQL databases is a generic code with three parameters: your credential, DB URL, and DB Driver type. In my example, I left a connection logic inside the ProxyDB method for the demonstration purpose, in order to easier understand the concept. Honestly, this is not a good practice :-) It is always better to move this code to the CommDB class.
    – Noviff
    Jan 13 '17 at 18:31
  • And now i see a lot of code that i could make more generic but i always have the same problem. Handle with results. And making PreparedStatements because i mostly use them. Every query generate diffrent result and sometimes doesn't generate at all. So i'm thinking that i can't bypass creating new method for every single query. Or maybe i'm wrong ?
    – Piter _OS
    Jan 14 '17 at 3:26
2

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.

0

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;
}
-1

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...

3
  • 2
    JDBC 4 drivers on the classpath are autoloaded so Class.forName() shouldn't be necessary.
    – Glenn
    Jan 6 '17 at 18:23
  • 1
    OK. That is better now than when I used it last time. :-)
    – Vadim
    Jan 6 '17 at 18:25
  • 2
    Yes, @Glenn is correct Class.forName() hasn't been needed since JDK 4, which was released in 2002. That's 14 years ago!
    – Bohemian
    Jan 6 '17 at 18:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.