Dealing with bugs

Tears and pain

Errors may occur while running a Java program. When working with the database, errors will occur. It's all about which of them you can predict and offer an adequate solution.

The first large group of errors will be waiting for you when executing this line:

Connection connection  = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",  "root", "secret");

What can you expect here?

Error 1. Driver not found .

If you get the error “No suitable driver found for …” , it means that the DriverManager could not understand what type of DBMS is behind your URL. For example, you wrote jdbc_mysql: instead of jdbc:mysql:

Error 2. Connection error .

If you make a mistake in the host name, you will most likely get a message like “No such host is known” or “Communications link failure”.

Error 3. The database name is incorrect .

If you misspelled the database name or connect to another server where it doesn't exist, you'll get a message like “Unknown database 'supershop3'” .

Error 4. Wrong login or password .

If you forgot the database password or entered it incorrectly, then most likely you will receive a message like “Access denied for user 'root'@'localhost' (using password: YES)” .

SQL Exception

If the connection to the base is nevertheless established, then it will be more interesting further. In case of errors when working with the database, JDBC has a special exception - java.sql.SQLException . As well as several of its varieties.

This exception has only one additional method (compared to the Exception class) - the getSQLState() method, which returns the status code (string) that the SQL server returned to it. Error handling looks like this:

Connection connection  = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",  "root", "secret");
try {
   int rowsCount = statement.executeUpdate("DELETE FROM ‘Unemployees’");
} catch (SQLException ex) {
  // If table doesn't exist
   if (ex.getSQLState().compareTo("X0Y32") != 0) {
  	throw ex;
  }
 } finally {
  connection.close();
 }

There are hundreds of error codes. You can see the full list here .

But sometimes error codes help JDBC understand the error better and then it throws not just SQLException, but specialized SQLException:

Error examples

If an error occurred at the stage of the server application, then usually it can only be logged and then dealt with in more detail. But if it happened while the application was running on the programmer's computer, then you need to carefully investigate the reason for this.

There are two biggest classes of errors when working with a database:

• Request text error
• Error while working with ResultSet

An error in the request text can occur very often. The query can be very long, contain several tables (joined via JOIN) and subqueries (SUBSELECT). It is not easy to find an error in such a request. In addition, requests are often glued together from parts, data is added there.

The very first error you should be aware of is SQLSyntaxErrorException . Such an error usually means that you have a typo in the request body.

Let's take our favorite example:

ResultSet results = statement.executeQuery("SELECT Count(*) FROM user");
results.next();
    int count = results.getInt(1);

And “accidentally” delete the asterisk in the request body:

ResultSet results = statement.executeQuery("SELECT Count() FROM user");
results.next();
    int count = results.getInt(1);

Then we will get an exception:

Exception in thread "main" java.sql.SQLSyntaxErrorException:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM task' at line 1

           	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

SQL server tells us that there was a syntax error before FROM.

We carefully look at the request and think. If nothing comes to mind, and this happens very often, then you need to ask someone to look what is wrong.

Another popular mistake is incorrect work with ResultSet - the result of the request. Suppose you forgot that before reading the data, the “current row” of the ResultSet object comes before the first row, then you will have the following code:

ResultSet results = statement.executeQuery("SELECT Count(*) FROM user");
//    results.next();
    int count = results.getInt(1);

You will get this error:

2012 12:55:48 AM jButton5ActionPerformed
SEVERE: null
java.sql.SQLException: Before start of result set
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
    at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5650)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5570)

We carefully look at the error and think. Then we google and find a couple of examples and try to understand the solution.