Lesson 2 - Databases in Java JDBC - Printing data and parameters

In the previous lesson of our Java database course, Introduction to databases in Java, we prepared a database with test data in it. In today's lesson, we're going to connect from Java to it and read these values.

Creating the project

We'll create a new Java application named Dictionary. In the Projects window, we'll right-click the project and choose Properties.

In the categories on the left, we'll select Libraries and click Add Library:

In the dialog, we'll select MySQL JDBC Driver and confirm. If you want to work with any other database using JDBC, it's always necessary to add the appropriate Connector in this way. The connector can be downloaded as a .jar file from the database manufacturer's website and added in the same way using the Add JAR button.

Loading the driver

In the past, the JDBC driver had to be loaded before use. It was done this way:

try {
    Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException ex) {
    System.out.println("Error while loading the database driver");
}

Java now loads the driver automatically, so this code is no longer needed. I only mention this in case you saw it in older projects or outdated tutorials.

Connection, PreparedStatement, and ResultSet

We'll work with the database using the trinity of the Connection, PreparedStatement and ResultSet classes. They are all in the java.sql package.

Connection

Connection is a database connection. It must be created before we ask the database anything. When creating it, we provide the connection string. This is a string containing the name of the database driver, the URL of the server the database is running on, the database name, user name, and password.

Creating a new connection instance will look like this:

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=")

PreparedStatement

PreparedStatement is a database query. When creating an instance, we enter the SQL code that we want to be executed on the database. Java also provides the Statement class that differs from PreparedStatement in that it cannot contain parameters (see below).

In our case, we'll create the following statement instance:

PreparedStatement statement = connection.prepareStatement("SELECT * FROM word")

Note that the statement is created using the connection instance.

We should also explain the SQL query itself. The SELECT statement says that we want to select data from a database table. The asterisk indicates that we want the result to have values from all the columns. The FROM keyword says that we are selecting from the word table. So the query selects all values for all words.

ResultSet

ResultSet is a collection of results returned by an SQL query. We'll use the executeQuery() method on the statement instance to obtain a ResultSet populated with the results of the SELECT SQL statement.

ResultSet results = statement.executeQuery();

Closing the connection

If you have already worked with files in Java, it will be no surprise that the database connection must be closed as well. However, a small surprise may be that we have to ensure that all 3 database objects are closed properly. If we didn't do this, the connection would remain open and the server be overloaded after a while.

The easiest way is to create the objects in a try-with-resources (TWR) block. When Java leaves this block, it takes care of closing the instances that are created inside automatically.

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM word");
    ResultSet results = statement.executeQuery();) {

} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

If something goes wrong, we inform the user with an error message. When debugging, comment out the catch block to be able to respond to the errors and fix the code.

Printing the results

We already have the retrieved words from the database in the results variable. All we have to do is to print them. The ResultSet contains the next() method. It moves the current position in the collection to the next item or returns false if we've reached the end of the results. It's necessary to call next() at least once if we want to read something from the results.

We use the methods starting with get to read values of the current resulting row. We can find here getInt(), getString(), getDate(), and others. We can pass either a column name or a numeric index as a parameter to these methods. Beware that the first column has the numeric index of 1.

while (results.next()) {
    int id = results.getInt(1);
    String english = results.getString("english");
    String spanish = results.getString("spanish");
    System.out.println("Id: " + id + ", English: " + english + ", Spanish: " + spanish);
}

The code above iterates over the results, retrieving their parameters, and then prints them to the console. Let's show the complete application code:

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=a");
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM word");
    ResultSet results = statement.executeQuery();) {

    while (results.next()) {
        int id = results.getInt(1);
        String english = results.getString("english");
        String spanish = results.getString("spanish");
        System.out.println("Id: " + id + ", English: " + english + ", Spanish: " + spanish);
    }

} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

You can try that the application really prints all the words from the database:

Console application
Id: 1, English: computer, Spanish: ordenador
Id: 2, English: ball, Spanish: pelota
Id: 3, English: dog, Spanish: perro
Id: 4, English: I, Spanish: yo
Id: 5, English: love, Spanish: amor
Id: 6, English: ICT, Spanish: ICT

Passing parameters

Let's make the application to really work like a dictionary. We'll let the user to enter a word in Spanish and then translate it into English.

SQL injection

The SQL query should now select only a specific row, so it's necessary to add a condition to it. We do this using the WHERE clause. Naively, we could insert the word the user has entered directly into the SQL query string:

// This code is dangerous
Scanner scanner = new Scanner(System.in);
System.out.println("Enter a Spanish word to be translated:");
String spanish = scanner.nextLine();
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("SELECT english FROM word WHERE spanish=\"" + spanish + "\"");
    ResultSet results = statement.executeQuery();) {

    results.next();
    String english = results.getString("english");
    System.out.println("Translating " + spanish + ": " + english);
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

The result:

Console application
Enter a Spanish word to be translated:
ordenador
Translating ordenador: computer

The code hasn't changed much. In the SQL query, we no longer select all columns, but only the column english. In addition, we provided a WHERE condition. We no longer retrieve results in a while loop, as we are only interested in a single one.

Although the app seems to work perfectly, the opposite is true. Whatever the user enters is inserted into the SQL query directly. What happens if, for example, the user enters the following string?

"; DROP TABLE word --

The database will execute a command to clear the table and all our data is lost. And this isn't even the worst case scenario, a more skilled user might be able to extract user passwords from another table through our dictionary. And that would be a problem. Believe it or not, users really enter such inputs from time to time, and your apps must resist them. This attack technique is called SQL injection because it inserts alien SQL code into our query.

Passing parameters

The whole problem is, of course, that we insert user inputs directly into the SQL query. Since we can never be sure if a variable constains something which possibly came from the user, let's simplify our problem to: inserting any variable into an SQL query is a huge security risk. In the past, variables were sanitized with a special function, which escaped the dangerous characters (especially quotation marks). However, it is safest to use prepared statements.

A prepared statement is a statement that contains wildcards instead of parameters, most often question marks. The actual values are inserted into the query separately and the database itself ensures they are inserted safely.

Let's rewrite our application to use parameterized queries:

Scanner scanner = new Scanner(System.in);
System.out.println("Enter a Spanish word to be translated:");
String spanish = scanner.nextLine();
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("SELECT english FROM word WHERE spanish=?");) {
    statement.setString(1, spanish);
    try (ResultSet results = statement.executeQuery()) {
        results.next();
        String english = results.getString("english");
        System.out.println("Translating " + spanish + ": " + english);
    }
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

Notice the question mark in the query and calling the setString() method, which sets the first parameter in the query to the given string. Of course, there are also methods for other data types. Our app is now secure.

Next time, in the lesson Databases in Java JDBC - INSERT, UPDATE, DELETE, and COUNT, we'll show how to edit the entries in the database. Today's project with the source code is attached to be downloaded below as always.

 

Did you have a problem with anything? Download the sample application below and compare it with your project, you will find the error easily.