Executing a SQL statement with JDBC

On the previous page, we looked at how to open a connection to a database with JDBC. We showed an example method, openConnection(), which returned a JDBC Connection object. Once we have obtained a Connection object, we can use this to access the database.

Executing SQL on the Connection involves two further objects:

• we create a Statement object, to which we pass the SQL to be executed and set any required options;
• we read the result of the query via a ResultSet object.

Creating a Statement object is simple once we have our Connection:

Connection c = createConnection();
Statement st = c.createStatement();

Then, to execute a simple SQL statement and get the corresponding result, we write something as follows¹:

int id = ... get ID from somewhere ...
String sql = "SELECT Name FROM Users WHERE Id = " + id;
ResultSet rs = st.executeQuery(sql);
// ... read from result set ...

The executeQuery() method returns a ResultSet object from which the result data can be read. For a simple SELECT query such as this, we would always expect a result set to be returned, which would be empty if no rows were returned.

The ResultSet object returned can then be queried to pull out the rows and colums of the result. On the next page, we look in more detail at reading from a ResultSet.

1. Note that it's actually very bad practice to take user input and construct a SQL, because poor validation of the user's input could lead to various types of attacks by an unscrupulous user. This example would only be acceptable if the contents of the id variable can be trusted by the program. User-supplied string input is a particular problem. In a real-world application with user-supplied data, we would need to use prepared statements.