Java JDBC Statement
The Java JDBC Statement interface (java.sql.Statement) sends SQL from your program to the database and gives you back the result: rows for a query, or a count of changed rows for an update. It is the simplest way to run SQL in JDBC and is ideal for fixed commands such as creating tables or running reports.
In this lesson you will create statements, use executeQuery(), executeUpdate() and execute(), read auto-generated keys, limit results with setMaxRows() and setQueryTimeout(), group commands into a batch, and see why a plain Statement must never be built from user input. The lesson ends with a small SQL script runner.
How to Create a Statement in Java
A Statement is created from an open Connection with createStatement(). One statement can run many SQL commands, one after another.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateStatement {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
System.out.println("Statement created: " + !statement.isClosed());
System.out.println("Same connection? " + (statement.getConnection() == connection));
}
}
}
Output:
Statement created: true
Same connection? true
How it works:
createStatement()returns a newStatementbound to this connection.getConnection()returns the connection that created it.- Both objects are declared in the try-with-resources header, so the statement is closed first and the connection second.
The Three Kinds of Statement
| Interface | Created with | Use it for |
|---|---|---|
Statement | createStatement() | Fixed SQL with no values from variables or users |
PreparedStatement | prepareStatement(sql) | SQL with ? parameters: safe, and faster when repeated |
CallableStatement | prepareCall(sql) | Calling stored procedures in the database |
Java JDBC Statement Methods
| Method | Returns | Use for |
|---|---|---|
executeQuery(sql) | ResultSet | SELECT |
executeUpdate(sql) | int rows affected | INSERT, UPDATE, DELETE, DDL such as CREATE TABLE |
execute(sql) | boolean | Any SQL, when you do not know the type in advance |
getResultSet() / getUpdateCount() | ResultSet / int | Reading the result after execute() |
getGeneratedKeys() | ResultSet | Keys created by the database during an insert |
addBatch(sql) / executeBatch() | int[] | Sending many updates in one round trip |
setMaxRows(n) | void | Return at most n rows from a query |
setQueryTimeout(seconds) | void | Cancel a query that runs too long |
close() / isClosed() | void / boolean | Free the statement and its open ResultSet |
Changing Data with executeUpdate()
executeUpdate() runs SQL that changes the database and returns the number of rows affected. Statements that change the structure, such as CREATE TABLE, return 0.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class ExecuteUpdateExample {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
int create = statement.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");
int insert = statement.executeUpdate("INSERT INTO students VALUES (1, 'Asha', 88), (2, 'Ravi', 72), (3, 'Meena', 92)");
int update = statement.executeUpdate("UPDATE students SET marks = marks + 5 WHERE marks < 90");
int delete = statement.executeUpdate("DELETE FROM students WHERE marks < 50");
System.out.println("CREATE TABLE: " + create);
System.out.println("INSERT: " + insert);
System.out.println("UPDATE: " + update);
System.out.println("DELETE: " + delete);
}
}
}
Output:
CREATE TABLE: 0
INSERT: 3
UPDATE: 2
DELETE: 0
How it works:
CREATE TABLEdefines a table but changes no rows, so it returns0.- The
INSERTadds three rows. TheUPDATEmatches Asha (88) and Ravi (72), so it returns2. - The
DELETEmatches no student, so it returns0. A count of0is not an error; it simply means nothing matched. Checking this number is how you detect "record not found" in an update.
Reading Data with executeQuery()
executeQuery() runs a SELECT and returns a ResultSet. Calling it with SQL that does not return rows, such as an INSERT, throws an SQLException.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ExecuteQueryExample {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");
statement.executeUpdate("INSERT INTO students VALUES (1, 'Asha', 88), (2, 'Ravi', 72), (3, 'Meena', 92)");
try (ResultSet rows = statement.executeQuery("SELECT name, marks FROM students WHERE marks >= 80 ORDER BY marks DESC")) {
while (rows.next()) {
System.out.println(rows.getString("name") + " scored " + rows.getInt("marks"));
}
}
try {
statement.executeQuery("DELETE FROM students WHERE id = 1");
} catch (SQLException e) {
System.out.println("executeQuery() with DELETE: SQLException");
}
}
}
}
Output:
Meena scored 92
Asha scored 88
executeQuery() with DELETE: SQLException
How it works:
- The query filters and sorts in SQL, and the loop prints each row it receives.
executeQuery()must produce aResultSet. ADELETEdoes not, so the driver throwsSQLExceptioninstead of deleting the row. UseexecuteUpdate()for that.
Note: A
Statementcan have only one openResultSet. Running another query on the same statement automatically closes the previousResultSet. If you need two result sets at the same time, use two statements.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class OneResultSetPerStatement {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
ResultSet first = statement.executeQuery("SELECT 1");
ResultSet second = statement.executeQuery("SELECT 2");
System.out.println("First ResultSet closed? " + first.isClosed());
System.out.println("Second ResultSet closed? " + second.isClosed());
second.close();
}
}
}
Output:
First ResultSet closed? true
Second ResultSet closed? false
How it works:
- The second
executeQuery()closed the firstResultSet, as the JDBC specification requires. - Reading from
firstnow would throwSQLException. This is a common surprise when a loop over one result set runs another query on the same statement.
Running Any SQL with execute()
execute() accepts any SQL. It returns true when the result is a ResultSet and false when it is an update count. This is what SQL consoles and admin tools use, because they cannot know in advance what the user will type.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ExecuteAnySql {
static void run(Statement statement, String sql) throws SQLException {
boolean returnsRows = statement.execute(sql);
if (returnsRows) {
try (ResultSet rows = statement.getResultSet()) {
int count = 0;
while (rows.next()) {
count++;
}
System.out.println("Query returned " + count + " row(s)");
}
} else {
System.out.println("Update changed " + statement.getUpdateCount() + " row(s)");
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
run(statement, "CREATE TABLE subjects (code VARCHAR(10), title VARCHAR(40))");
run(statement, "INSERT INTO subjects VALUES ('CS101', 'Java'), ('CS102', 'Databases')");
run(statement, "SELECT * FROM subjects");
run(statement, "UPDATE subjects SET title = 'Java Programming' WHERE code = 'CS101'");
}
}
}
Output:
Update changed 0 row(s)
Update changed 2 row(s)
Query returned 2 row(s)
Update changed 1 row(s)
How it works:
run()does not know what kind of SQL it receives. It callsexecute()and checks the returnedboolean.- For the
SELECT,getResultSet()returns the rows. For the other commands,getUpdateCount()returns the number of changed rows.
Getting Auto-Generated Keys
When the database creates the primary key (an identity or auto-increment column), you usually need that new key, for example to insert related rows. Pass Statement.RETURN_GENERATED_KEYS to executeUpdate() and read the keys with getGeneratedKeys().
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class GeneratedKeys {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE courses (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title VARCHAR(40))");
String[] titles = {"Java Basics", "SQL Fundamentals", "Spring Boot"};
for (String title : titles) {
statement.executeUpdate("INSERT INTO courses (title) VALUES ('" + title + "')",
Statement.RETURN_GENERATED_KEYS);
try (ResultSet keys = statement.getGeneratedKeys()) {
keys.next();
System.out.println("Inserted '" + title + "' with id " + keys.getInt(1));
}
}
}
}
}
Output:
Inserted 'Java Basics' with id 1
Inserted 'SQL Fundamentals' with id 2
Inserted 'Spring Boot' with id 3
How it works:
GENERATED BY DEFAULT AS IDENTITYmakes the database number new rows automatically (MySQL usesAUTO_INCREMENTfor the same purpose).RETURN_GENERATED_KEYSasks the driver to collect the new key.getGeneratedKeys()returns a smallResultSetholding the key, read here withgetInt(1).- The titles are fixed strings from the program, which is the only case where gluing values into SQL is acceptable. For user input, use a
PreparedStatement, as shown below.
Limiting Results: setMaxRows() and setQueryTimeout()
Two settings protect your application from queries that return too much or run too long.
| Setting | Default | Effect |
|---|---|---|
setMaxRows(n) | 0 (no limit) | Extra rows beyond n are silently dropped |
setQueryTimeout(seconds) | 0 (no limit) | The driver cancels the query and throws SQLTimeoutException after this time |
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class LimitResults {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE scores (student VARCHAR(20), points INT)");
statement.executeUpdate("INSERT INTO scores VALUES ('Asha', 88), ('Ravi', 72), ('Meena', 92), ('Kavin', 81), ('Divya', 95)");
System.out.println("Default max rows: " + statement.getMaxRows());
statement.setMaxRows(3); // top 3 only
statement.setQueryTimeout(10); // never wait more than 10 seconds
try (ResultSet rows = statement.executeQuery("SELECT student, points FROM scores ORDER BY points DESC")) {
int rank = 1;
while (rows.next()) {
System.out.println(rank++ + ". " + rows.getString("student") + " (" + rows.getInt("points") + ")");
}
}
System.out.println("Query timeout: " + statement.getQueryTimeout() + " s");
}
}
}
Output:
Default max rows: 0
1. Divya (95)
2. Meena (92)
3. Asha (88)
Query timeout: 10 s
How it works:
- The table has five rows, but
setMaxRows(3)makes theResultSetstop after three. Combined withORDER BY points DESC, that gives a top-three list. setQueryTimeout(10)applies to every later query on this statement. A slow query would be cancelled with anSQLTimeoutException.
Note:
setMaxRows()limits what the driver hands to Java; it does not change the SQL. When you only ever need a few rows, also add a limit in SQL (FETCH FIRST 3 ROWS ONLY, orLIMIT 3in MySQL and PostgreSQL) so the database does less work.
Running Many Updates with a Batch
Each executeUpdate() is a round trip to the database. A batch collects several commands with addBatch() and sends them together with executeBatch(), which returns one update count per command.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
public class StatementBatch {
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
Statement statement = connection.createStatement()) {
statement.executeUpdate("CREATE TABLE attendance (student VARCHAR(20), present BOOLEAN)");
statement.executeUpdate("INSERT INTO attendance VALUES ('Asha', FALSE), ('Ravi', FALSE), ('Meena', FALSE)");
statement.addBatch("UPDATE attendance SET present = TRUE WHERE student = 'Asha'");
statement.addBatch("UPDATE attendance SET present = TRUE WHERE student = 'Meena'");
statement.addBatch("INSERT INTO attendance VALUES ('Kavin', TRUE)");
statement.addBatch("DELETE FROM attendance WHERE student = 'Nobody'");
int[] counts = statement.executeBatch();
System.out.println("Update counts: " + Arrays.toString(counts));
System.out.println("Total changed: " + Arrays.stream(counts).sum());
}
}
}
Output:
Update counts: [1, 1, 1, 0]
Total changed: 3
How it works:
addBatch()only stores the SQL; nothing runs untilexecuteBatch().- The returned array has one entry per command, in the same order. The last
DELETEmatched nothing, so its count is0. - Batches are most powerful with
PreparedStatement, where one SQL command is repeated with different values.
SQL Injection: Why Statement Is Dangerous with User Input
If you build SQL by joining strings with user input, a user can type SQL code instead of a value. This attack is called SQL injection, and it is one of the most common security holes in web applications.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInjectionDemo {
static int unsafeLogin(Connection connection, String user, String password) throws SQLException {
String sql = "SELECT COUNT(*) FROM users WHERE username = '" + user + "' AND password = '" + password + "'";
System.out.println(" SQL sent: " + sql);
try (Statement statement = connection.createStatement();
ResultSet rows = statement.executeQuery(sql)) {
rows.next();
return rows.getInt(1);
}
}
static int safeLogin(Connection connection, String user, String password) throws SQLException {
String sql = "SELECT COUNT(*) FROM users WHERE username = ? AND password = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, user); // sent as data, never as SQL
statement.setString(2, password);
try (ResultSet rows = statement.executeQuery()) {
rows.next();
return rows.getInt(1);
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:app", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE users (username VARCHAR(20), password VARCHAR(20))");
setup.executeUpdate("INSERT INTO users VALUES ('admin', 'x9#Lq2'), ('asha', 'p@ss')");
String attackerUser = "admin";
String attackerPassword = "' OR '1'='1";
System.out.println("Statement:");
System.out.println(" Matching users: " + unsafeLogin(connection, attackerUser, attackerPassword));
System.out.println("PreparedStatement:");
System.out.println(" Matching users: " + safeLogin(connection, attackerUser, attackerPassword));
}
}
}
Output:
Statement:
SQL sent: SELECT COUNT(*) FROM users WHERE username = 'admin' AND password = '' OR '1'='1'
Matching users: 2
PreparedStatement:
Matching users: 0
How it works:
- The attacker's "password" closes the quote and adds
OR '1'='1'. BecauseANDbinds tighter thanOR, the condition becomes(username = 'admin' AND password = '') OR '1'='1', which is true for every row. The unsafe login matches both users without knowing any password. safeLogin()uses?placeholders. The driver sends the password as plain data, so the database looks for a user whose password is literally' OR '1'='1and finds none.- Rule: use
Statementonly for SQL that is completely fixed in your code. Whenever a value comes from a variable, a form, a URL or a file, usePreparedStatement.
Note: Real applications never store plain-text passwords. They store a slow, salted hash, which is covered in the Java Security section of this tutorial.
Real-World Example: A Simple SQL Script Runner
Developers often keep setup and report SQL in a script file. This runner splits a script into commands, runs each with execute(), and prints either a neat table of rows or the number of changed rows, much like a database console.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlScriptRunner {
static final String SCRIPT = """
CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT);
INSERT INTO students VALUES (1, 'Asha', 88), (2, 'Ravi', 72), (3, 'Meena', 92);
UPDATE students SET marks = marks + 5 WHERE marks < 75;
SELECT name, marks FROM students ORDER BY marks DESC;
SELECT COUNT(*) AS total, MAX(marks) AS best FROM students;
DELETE FROM students WHERE marks < 80;
SELECT nme FROM students;
""";
static void printTable(ResultSet rows) throws SQLException {
ResultSetMetaData meta = rows.getMetaData();
int columns = meta.getColumnCount();
StringBuilder header = new StringBuilder();
for (int i = 1; i <= columns; i++) {
header.append(String.format("%-8s", meta.getColumnLabel(i)));
}
System.out.println(" " + header.toString().trim());
int count = 0;
while (rows.next()) {
StringBuilder line = new StringBuilder();
for (int i = 1; i <= columns; i++) {
line.append(String.format("%-8s", rows.getString(i)));
}
System.out.println(" " + line.toString().trim());
count++;
}
System.out.println(" (" + count + " row(s))");
}
static void runScript(Connection connection, String script) {
String[] commands = script.split(";");
try (Statement statement = connection.createStatement()) {
statement.setQueryTimeout(30);
for (String command : commands) {
String sql = command.strip();
if (sql.isEmpty()) {
continue;
}
System.out.println("> " + sql);
try {
if (statement.execute(sql)) {
try (ResultSet rows = statement.getResultSet()) {
printTable(rows);
}
} else {
System.out.println(" OK, " + statement.getUpdateCount() + " row(s) affected");
}
} catch (SQLException e) {
System.out.println(" ERROR (SQLState " + e.getSQLState().substring(0, 2) + "...): command skipped");
}
}
} catch (SQLException e) {
System.out.println("Could not create statement: " + e.getMessage());
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:scripts", "sa", "")) {
runScript(connection, SCRIPT);
}
}
}
Output:
> CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)
OK, 0 row(s) affected
> INSERT INTO students VALUES (1, 'Asha', 88), (2, 'Ravi', 72), (3, 'Meena', 92)
OK, 3 row(s) affected
> UPDATE students SET marks = marks + 5 WHERE marks < 75
OK, 1 row(s) affected
> SELECT name, marks FROM students ORDER BY marks DESC
NAME MARKS
Meena 92
Asha 88
Ravi 77
(3 row(s))
> SELECT COUNT(*) AS total, MAX(marks) AS best FROM students
TOTAL BEST
3 92
(1 row(s))
> DELETE FROM students WHERE marks < 80
OK, 1 row(s) affected
> SELECT nme FROM students
ERROR (SQLState 42...): command skipped
How it works:
- Splitting the script.
split(";")turns the text block into separate commands, andstrip()removes the line breaks around them. Empty pieces, such as the text after the last semicolon, are skipped. - One statement for everything. A single
Statementruns every command, with a 30-second query timeout as a safety net. execute()decides the output. When it returnstrue, the runner prints a table; otherwise it prints the update count. Ravi's marks rise from 72 to 77, so theUPDATEchanges one row and the laterDELETEremoves him.- Generic table printing.
ResultSetMetaDataprovides the number of columns and their labels, soprintTable()works for any query.getString()converts every value to text for display. H2 reports unquoted labels in upper case, which is why the headers readNAMEandTOTAL. - Errors do not stop the script. The misspelled column
nmecauses anSQLExceptionwith SQLState class42(syntax error or unknown name). The runner reports it and moves on.
Note: Splitting on
;is a simplification: it breaks if a string value contains a semicolon. Real tools such as Flyway and Liquibase use a proper SQL parser.
For any SQL that includes values from users, move on to PreparedStatement, the safe and efficient way to pass parameters. To revisit how statements come from a connection and take part in transactions, see Java JDBC Connection, and for the overall workflow see Java JDBC Basics.