The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Spring

JDBC

How does Java code send SQL to the database?

A database is a program that works separately from the Java program. (A database such as MySQL is a completely different program. H2, used in this lab, runs inside the Java program, but what the code does is the same.) For Java code to have the database do work, it does three things, following the convention called JDBC. First, it makes a connection (Connection) to the database. Second, it sends SQL as text over that connection. Third, it reads the returned result (ResultSet) one row at a time. Without a connection, SQL cannot be sent, and if the SQL text is wrong, the database answers with an error, which becomes a SQLException in Java (an exception, a signal that an error occurred). In this lab you add a conn.close() line to code that reads the name of the member whose id is 1 from the member table, or change the SQL text, and see the SQL the database received, the value Java printed, and the exception. (The code on the screen is treated as being inside the main method, so the import lines and the main declaration are left out. JdbcTemplate and JPA, which write this repeated code for you, are covered in the in-depth labs.)

Checked against real execution · Compiled and ran all six combinations in new Java processes on JDK 21 and H2 2.4.240, and checked the rules against the Java SE 21 API documentation

Concepts in this lab

JDBC

The most basic standard API that a Java program uses to send SQL to a database and receive the results. You get a connection, pass the SQL statement to it as a string, fill in the values at the question marks and run it, and then take the column values one by one from the returned result (a ResultSet). Both writing the SQL and moving the values you took into the fields of an object are the developer's job. JPA and MyBatis also send SQL through JDBC internally. So even if you do not use JDBC directly, it helps to know what happens underneath.

Connection and DriverManager

A Connection is an object that stands for a connection (a session) made with a particular database. SQL statements run inside this connection, and the results come back over it too. Java code gets a connection with DriverManager.getConnection(url). The url has the form jdbc:subprotocol:name (for example jdbc:h2:mem:shop), and DriverManager picks a fitting one from the registered JDBC drivers and makes the connection. close() releases the database and JDBC resources that the connection holds right away, and calling createStatement on a closed connection gives a SQLException. Without a connection, SQL cannot be sent.

Statement and executeQuery

A Statement is an object used to send an SQL statement that has no values put into it to the database and run it, and it is made with conn.createStatement(). executeQuery(sql) sends a statement such as a SELECT and returns the result as one ResultSet, and the value it returns is never null. It gives a SQLException if a database access error occurs, if it is called on a closed Statement, or if the SQL it sends does not produce a single ResultSet. To Java, SQL is just a string, so a wrong text still compiles and is found out as an error at run time, when the database reads it. That making a Statement alone does not send SQL to the database and that SQL is sent when executeQuery is called was checked by running it on H2.

ResultSet and SQLException

A ResultSet is the table of rows that a query returned, and it has a cursor that points to the current row. The cursor is at first in front of the first row, and next() moves it one step forward and returns true if there is a row at that place and false if there are no more. A method that takes the value of a column of the current row, such as getString("name"), needs a current row, so calling it after next() has returned false gives a SQLException. Having no row that matches the condition, so that the result is empty, is not an error, and it is signaled by next() returning false. A SQLException is an exception that reports a database access error or another error (a subclass of Exception, so it is a checked exception), and it carries a description string, an SQLState, and an error code that differs for each database product.

Try these

• Default settings — It makes a connection, sends SQL, and reads the result one row at a time. The database receives one SQL statement, runs it, and returns the value kim.
• Sending after closing the connection — SQL cannot reach the database over a closed connection, and a SQLException occurs.
• A table name that does not exist — While reading the SQL, the database answers with an error saying the table does not exist, and in Java it becomes a SQLException.
• No matching row — It is not an error, and an empty result comes back. The call rs.next() gives the value false, and if you try to read a value anyway, an exception occurs.
• Closed connection and a wrong table name — The closed-connection error comes first. The database never got to read the wrong SQL.

Running with the default settings

1. DriverManager.getConnection("jdbc:h2:mem:shop"): the Java program makes a connection to the database. The jdbc in the URL means the convention for Java to connect to a database (JDBC), h2 is the kind of database, and mem:shop is a database named shop that lives in memory. When the connection is made, one place that handles this connection (a session) appears on the database side, and an object conn that points to the connection appears in Java. The member table holds one row, (1, kim). Not a single line of SQL has been sent yet.
2. conn.createStatement(): makes a tool (Statement) that sends SQL over the connection conn, and puts it in st. Only the tool was made, so no SQL has gone to the database yet.
3. st.executeQuery("SELECT name FROM member WHERE id = 1"): st sends the SQL text to the database. The database reads the text it received and looks in the member table for the row whose id is 1, and makes the result. The result is a table with 1 row. Java receives this result as a ResultSet object rs. A ResultSet starts standing in front of the first row, so it has not read any row yet.
4. rs.next(): moves one step to the next row in the result, and returns true if there is a row at the place it moved to, and false if not. It moves to the first row, and the value true goes into the variable found. If you try to read a value before calling the next() method, a SQLException occurs saying there is no row to read.
5. System.out.println(found): prints the value true held in the variable found to the screen.
6. rs.getString("name"): takes the value of the name column from the current row as text and prints the value kim. The program ran to the last line of main and ended. The database received one SQL statement, and there was no exception.

Result of this run

• Java code reads a value from the database in this order: making a connection, making a tool to send with, sending SQL, and reading the result one row at a time. The database received and ran one SQL statement, and Java read the value kim.
• The Java side has no code that runs SQL. SQL is made as text and sent to the database, the database runs it, and Java only receives the returned result as a ResultSet and reads it.

Code

// The member table holds one row: (id 1, name kim)
// Connect: make a connection to the database that the URL points to
Connection conn = DriverManager.getConnection("jdbc:h2:mem:shop");
// Tool: the object that sends SQL over this connection
Statement st = conn.createStatement();
// Send: send the SQL text to the database and receive the result
ResultSet rs = st.executeQuery("SELECT name FROM member WHERE id = 1");
// Read: move to the next row. true if there is a row
boolean found = rs.next();
System.out.println(found);
System.out.println(rs.getString("name"));

Assumptions of this model

Checked against real execution on JDK 21.0.12.1 and H2 2.4.240 (an in-memory database). It was not checked against execution on MySQL. All six combinations were checked by putting the code on the screen into a Main.java that has a main method (only import java.sql.* and the main declaration were added), compiling it with javax.tools, and running it in a new Java process for each combination. Before the run, the same process made the member table (id INT as the primary key, name VARCHAR(20)) and one row, (1, kim), and this preparation code is left out on the screen. The database is H2 running inside the Java process, so in reality it is in the same process, and it is not a separate program like MySQL. The order of connecting, sending SQL, and reading the result is the same, but that what travels between them is network communication was not checked in this lab. What was checked is the three items of the result table, that is, the SQL the database received, the value printed, and the exception. The SQL the database received was judged by whether that SELECT remained in INFORMATION_SCHEMA.QUERY_STATISTICS of H2 (the execution log), or the exception came from the parser of the database engine (org.h2.command.Parser) and the error message quoted that SQL. With the closed connection, neither was the case, and the exception came from JdbcConnection.checkClosed. The kind of exception is the SQLState and the error code of H2 (42102 table not found, 2000 no data to read, 90007 already closed object) put into the wording on the screen, and these codes are those of H2, so they may be different on MySQL. All three exceptions are subclasses of SQLException. The facts that the step sentences rely on were also checked by execution: that making a connection adds one session on the database side and closing it removes one, that createStatement alone leaves no SELECT in the execution log of the database and a SELECT is left after executeQuery, that calling getString before calling the next() method gives a SQLException, and that calling the next() method one more time gives false. The session count includes the one connection that the preparation code opened before the run. The execution log (QUERY_STATISTICS) did not show new records after it was read once (the cause was not checked), so a separate database was made for each point in time and read only once. To keep the code short, the code on the screen does not write the lines that close the connection and the ResultSet or the code that handles exceptions. Because the exception is not handled, the program stops at the line where the exception occurred and the lines below it do not run, and this was checked by the fact that the values of the later lines are not in the output. The Java SE 21 API documentation was opened and checked directly. The lab follows the java.sql package description (that DriverManager makes a connection, Statement sends SQL, and ResultSet receives the result), the description of Connection (that SQL is run inside a connection and the result comes back, and that createStatement throws a SQLException on a closed connection), Statement.executeQuery (that it returns a ResultSet and throws a SQLException on a closed Statement), the description of ResultSet (that the cursor is at first in front of the first row, that next moves one step and returns false if there is no row, and that a method that needs a current row throws a SQLException then), the description of SQLException (that it is an exception that carries the SQLState and an error code), and DriverManager.getConnection (that the URL has the form jdbc:subprotocol:name). From the H2 documentation (the features page), it was checked that jdbc:h2:mem:name points to one in-memory database with that name inside the same JVM, and that the in-memory database disappears when the last connection is closed. That H2 gives an error when a Statement made from a closed connection is used is not defined by the documentation but is a behavior observed on this H2 (the Statement documentation says only that it throws an exception on a closed Statement). This lab does not cover PreparedStatement and how to put values into SQL, SQL that changes data (executeUpdate), transactions, or the right way to close a connection (try-with-resources). JdbcTemplate, which reduces the repeated code, is covered in the in-depth lab "JdbcTemplate and exception translation", borrowing a connection instead of making a new one every time is covered in "Connection pool and OSIV", and JPA, which connects objects and tables, is covered in "What JPA solves".

Previous lab: Configuration files · Next lab: @Transactional