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 follows1:
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.