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.
Download
By downloading the following file, you agree to the license terms
Downloaded 18x (23.29 kB)
Application includes source codes in language java