Proper database connections with JDBC driver

Hi guys,

our first article will be dedicated to technical stuff especially to use of JDBC in Java SE applications since we spotted some commons mistakes managing the JDBC connection. Below we describe the correct way of getting database connection using JDBC in Java/J2EE applications.

JDBC ( = Java DataBase Connectivity) is now a pretty old feature of Java SE that allows Java/J2EE applications to communicate with databases using drivers. Since every SGBD is more or less specific each of them requires a different a different driver so we have a MySql driver, an Oracle driver, etc… JDBC is extensively used because it is really simple to set up, uses SQL queries, is flexible and doesn’t mix Object mindsets with Relational mindsets. So it separates clearly what is data and what is business logic.

However being so versatile can bring to misuse of it. Assume we have a Java application with a MySql back-end listening on 3306 port, let’s see how it works. First we need to obtain the appropriate driver (jar file) corresponding to our database which can be found on Sun website and place it in the correct libfolder (probably the “WEB-INF/lib/” folder)
Then we add the code below :

public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String databaseUrl="jdbc:mysql//db.domaine.com:3306/mydatabase";
String username = "toto";
String password = "tata";
 
try {
Connection connection = DriverManager.getConnection(databaseUrl,
username,password);
// implements business logic
// plenty of examples can be found on the web
...
// close the connection
connection.close();
} catch (SQLException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

Retrieving of data is not detailed here since it can be found in many other place, however I would like to insist on some what some consider as minor :

  1. catching exceptions
  2. closure of the connection

In many exemples I went through I was very surprised to find out that many bloggers weren’t catching exceptions and therefore couldn’t identify origin of issues : classpath issue, bad login for database, unreachable database server, bad SQL request ? Using such approach allows to debug much more easily the application since we can customise error messages according to the exceptions raised.

Second point is regarding the closure of connection. Most of the time connections are not closed by the developer probably saying “I have my data I don’t care about the connection, it will be garbage collected”. Unfortunately such behaviour is not to be encouraged because it is not consequence free and many guys are complaining about “the too many connection open” error message.
This is because the connection open like this will be pending and waiting for another requests that will never come because next time we will use another connection.

At the end, since MySql server as a maximum amount of connections allowed (default 100) we will discover this message : Too many connections and we won’t be able to connect anymore to MySql until we restart MySql… Related Java exceptions is the sadly famous : User ‘toto’ already has more than ‘max_user_connections’ active connections.

To prevent such issues in a shared environment (as it is for hosting providers) most providers limit the number of connections that every user can open on MySql side. So developers should rework their code in order to avoid such message.

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Server connection failure during transaction. Due to underlying exception:
‘com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: User ‘toto’ already has more than ‘max_user_connections’ active connections’.

**BEGIN NESTED EXCEPTION**
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException
MESSAGE: User ‘toto’ already has more than ‘max_user_connections’ active connections

STACKTRACE:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: User ‘toto’ already has more than ‘max_user_connections’ active connections
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2924)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:171)

This message is only due to limitation on the number of concurrent connections allowed by the database adiminstrator for the user ‘toto’. This is easily done thanks to the MySql command:

.. limit simultaneous connections to 15 for user ‘toto’

GRANT USAGE ON * . * TO ‘toto’@’databaseserver’ WITH MAX_USER_CONNECTIONS 15 ;

To be honest, 15 connections should be sufficient for many Java applications : indeed, to exceed this amount you should have, in about a tenth of second, 15 requests sent to the database server with this user… but this can happen on larger application.

On the Java code side all you can do to fix it is to ensure you properly close your MySql connections, if not enough, maybe the limit is to low for your application. Alternatively you can try to use a connection pool or Java Persistance API to better manage your connection but it’s much more tricky so this will the topic of the next post and moreover it definitely won’t solve limitations on the MySql server…

Cheers,

Matthieu

About Matthieu

Hello, I joined Java-Hoster in 2009. After my engineering studies in France, I worked for several companies including some well known Natixis , Airbus and Air France . Now I am more focused and involved on Java open-source projects and of course in Java-Hoster which makes me learn a lot and feed me as well :-D I am also responsible for this blog, so if you have something to say about it feel free to contact me. Cheers.
This entry was posted in Database, Java course and tagged , , , , , , . Bookmark the permalink.

One Response to Proper database connections with JDBC driver

  1. inCitizen says:

    public static void Rule1()
    {
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    int updateQuery=0;
    String url = “jdbc:mysql://localhost:3306/test”;
    String user = “root”;
    String password = “aiman”;
    /***************/
    try
    {
    Class.forName(“com.mysql.jdbc.Driver”).newInstance(); // Load JBBC driver “com.mysql.jdbc.Driver”.
    con = DriverManager.getConnection(url, “root”, “aiman”);/*Create a connection*/
    st = con.createStatement();
    /***count rows**/
    int count=0;
    ResultSet res = st.executeQuery(“SELECT COUNT(*) FROM feature”);
    while (res.next())
    {
    count = res.getInt(1);
    }
    System.out.println(“Number of rows:”+count);
    /*****************************Inserting Values**************************/
    String w1,w2;
    String nsubj=”nsubj”;
    String query[] ={“SELECT * FROM Dependency WHERE header like ‘nsubj'”};
    System.out.println(“before printing”);
    for(String q : query)
    {
    res = st.executeQuery(q);
    //System.out.println(“Names for query “+ q +” are”);
    while (res.next())
    {
    String header = res.getString(“header”);
    String wi = res.getString(“wi”);
    String wj = res.getString(“wj”);
    System.out.println(header+”|”+wi+”|”+wj);
    ResultSet res1 = null;
    Statement st1=null;
    st1=con.createStatement();
    String query1[] ={“SELECT * FROM tagtable WHERE word like ‘wi’ AND tag like ‘%NN%'”};
    for(String q1 : query1)
    {
    res1 = st1.executeQuery(q1);
    while (res1.next())
    {
    System.out.println(“in the loop”);
    System.out.println(“*******************”);
    String w = res1.getString(“word”);
    String tag = res1.getString(“tag”);
    System.out.println(w+”|”+tag);
    }
    }
    res1.close();
    }//end while
    System.out.println(“done”);
    }//end for
    res.close();
    st.close();
    con.close();

    /************************************************************************/
    }//try ends
    catch(Exception e)
    {
    System.out.println(“Exception is = “+e);
    }
    }//rule 1 ends here
    }//feature class ends
    =====================================
    the inner while loop which is :
    while (res1.next())
    {
    System.out.println(“in the loop”);
    System.out.println(“*******************”);
    String w = res1.getString(“word”);
    String tag = res1.getString(“tag”);
    System.out.println(w+”|”+tag);
    }
    doesn’t get executed … ! help !

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>