JDBC Queries ​

Earlier, we discussed that Java programs use the JDBC interface to query databases. JDBC is a set of interface specifications, but where is it located? It resides in the Java standard library java.sql, where most of it consists of interfaces. Interfaces cannot be instantiated directly; instead, you must instantiate the corresponding implementation classes and reference these instances through the interfaces. This raises the question: where are the implementation classes for the JDBC interfaces?

Since the JDBC interfaces do not know which database you intend to use, you must use the "implementation classes" provided by the specific database you are using. We refer to the JAR files that implement the JDBC interfaces for a particular database as JDBC drivers.

Because we have chosen MySQL 5.x as our database, we first need to find a JDBC driver for MySQL. A JDBC driver is essentially a third-party JAR file, which we can add directly as a Maven dependency:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

Notice that the scope of the dependency is set to runtime because compiling the Java program does not require the MySQL JAR file; it is only needed at runtime. If you set the scope to compile, it will compile correctly, but your IDE will display a lot of classes like com.mysql.jdbc.Connection, which can easily be confused with the JDBC interfaces in the Java standard library. Therefore, do not set the scope to compile.

With the driver in place, we must also ensure that MySQL is running correctly on the local machine and prepare some data. Here, we use a script to create the database and tables, then insert some data:

-- Create database learnjdbc:
DROP DATABASE IF EXISTS learnjdbc;
CREATE DATABASE learnjdbc;

-- Create user learn with password learnpassword
CREATE USER IF NOT EXISTS 'learn'@'%' IDENTIFIED BY 'learnpassword';
GRANT ALL PRIVILEGES ON learnjdbc.* TO 'learn'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- Create table students:
USE learnjdbc;
CREATE TABLE students (
  id BIGINT AUTO_INCREMENT NOT NULL,
  name VARCHAR(50) NOT NULL,
  gender TINYINT(1) NOT NULL,
  grade INT NOT NULL,
  score INT NOT NULL,
  PRIMARY KEY(id)
) Engine=INNODB DEFAULT CHARSET=UTF8;

-- Insert initial data:
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Ming', 1, 1, 88);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Hong', 1, 1, 95);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Jun', 0, 1, 93);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Bai', 0, 1, 100);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Niu', 1, 2, 96);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Bing', 1, 2, 99);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Qiang', 0, 2, 86);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Qiao', 0, 2, 79);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Qing', 1, 3, 85);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Wang', 1, 3, 90);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Lin', 0, 3, 91);
INSERT INTO students (name, gender, grade, score) VALUES ('Xiao Bei', 0, 3, 77);

In the console, enter mysql -u root -p, input the root password, and execute the above SQL statements. If you are running the latest version of MySQL 8.x, you may need to adjust the CREATE USER statement accordingly.

JDBC Connection ​

When using JDBC, it is important to understand what a Connection is. A Connection represents a JDBC connection, which is equivalent to the connection between the Java program and the database (typically a TCP connection). To open a Connection, you need to provide the URL, username, and password to successfully connect to the database.

The URL follows a format specified by the database vendor. For MySQL, the URL format is:

jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2

Assuming the database is running on the local machine (localhost), using the standard port 3306, and the database name is learnjdbc, the URL would be:

jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8

The last two parameters indicate not to use SSL encryption and to use UTF-8 as the character encoding (note that MySQL's UTF-8 is utf8).

To obtain a database connection, use the following code:

// JDBC connection URL, different databases have different formats:
String JDBC_URL = "jdbc:mysql://localhost:3306/test";
String JDBC_USER = "root";
String JDBC_PASSWORD = "password";
// Get connection:
Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
// TODO: Access the database...
// Close connection:
conn.close();

The core code is the getConnection() static method provided by DriverManager. DriverManager automatically scans the classpath to find all JDBC drivers and then selects an appropriate driver based on the URL you provide.

Since a JDBC connection is an expensive resource, it should be released promptly after use. Using try (resource) to automatically release the JDBC connection is a good practice:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    // ...
}

Performing JDBC Queries ​

Once you have obtained a JDBC connection, the next step is to query the database. Querying the database using JDBC involves the following steps:

1. Create a Statement Object: Use the createStatement() method provided by the Connection to create a Statement object, which is used to execute a query.

2. Execute the Query: Use the executeQuery("SELECT * FROM students") method provided by the Statement object, passing in the SQL statement. This executes the query and obtains the result set, which is referenced using a ResultSet object.

3. Process the Result Set: Repeatedly call the next() method on the ResultSet and read each row of the result.

Here is the complete query code:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
            while (rs.next()) {
                long id = rs.getLong(1); // Note: Index starts at 1
                long grade = rs.getLong(2);
                String name = rs.getString(3);
                int gender = rs.getInt(4);
            }
        }
    }
}

Key Points to Note:

• Both Statement and ResultSet are resources that need to be closed. Using nested try (resource) blocks ensures they are closed promptly.

• The rs.next() method is used to check if there is another row in the result set. If there is, it automatically moves the current row to the next one (the current row is not the first row when the ResultSet is initially obtained).

• When retrieving columns from the ResultSet, indexes start at 1, not 0.

• You must call methods like getLong(1) or getString(2) based on the corresponding position of the columns in the SELECT statement. Otherwise, mismatched data types will result in errors.

SQL Injection ​

Using Statement to concatenate strings makes it very easy to introduce SQL injection vulnerabilities because SQL parameters often come from method parameters.

Let's look at an example: Suppose the user login validation method is as follows:

User login(String name, String pass) {
    ...
    stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'");
    ...
}

Here, the parameters name and pass are typically received by the program from user input on a web page.

If the user's input is as expected, the correct SQL can be constructed. For example: name = "bob", pass = "1234":

SELECT * FROM user WHERE login='bob' AND pass='1234'

However, if the user's input is a carefully crafted string, it can create unexpected SQL that is still valid but changes the query conditions. For example: name = "bob' OR pass=", pass = " OR pass='":

SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''

When this SQL statement is executed, it bypasses the password check entirely, making the login ineffective.

To prevent SQL injection attacks, one way is to escape all string parameters, but escaping is cumbersome and requires adding escape code wherever SQL is used.

Another way is to use PreparedStatement. Using PreparedStatement can completely avoid SQL injection issues because PreparedStatement always uses ? as placeholders and sends the data along with the SQL statement to the database. This ensures that the SQL statement sent to the database is the same each time, with only the placeholder data differing. Additionally, it can efficiently utilize the database's query caching. The above login SQL can be rewritten using PreparedStatement as follows:

User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

Therefore, PreparedStatement is safer and faster than Statement.

Let's convert the earlier Statement example to use PreparedStatement:

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
        ps.setObject(1, "M"); // Note: Index starts at 1
        ps.setObject(2, 3);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                long id = rs.getLong("id");
                long grade = rs.getLong("grade");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
            }
        }
    }
}

When using PreparedStatement and Statement, there are slight differences. With PreparedStatement, you must first call setObject() to set the value for each ? placeholder, and the result is still a ResultSet object.

Additionally, when reading columns from the result set, using the column name as a String is more readable and less error-prone than using an index.

Note that JDBC queries always return a ResultSet, even for aggregate queries like SELECT SUM(score) FROM .... You still need to read the results from the ResultSet:

ResultSet rs = ...
if (rs.next()) {
    double sum = rs.getDouble(1);
}

Data Types ​

Some students might have noticed that when using JDBC, we need to convert between Java data types and SQL data types. JDBC defines a set of constants in java.sql.Types to represent how SQL data types are mapped, but typically, we use the following types:

Note: Only the latest JDBC drivers support LocalDate and LocalTime.

Practice Exercise ​

Use JDBC to query the database.

Summary ​

• The Connection interface in JDBC represents a JDBC connection.
• When performing JDBC queries, always use PreparedStatement instead of Statement.
• Query results are always returned as a ResultSet, even when using aggregate queries.