Creating and executing queries

In the last lesson you typed SQL at the mysql> prompt and wrote a DBConnection class that opens a connection from Java. This lesson puts the two together behind five buttons. Each button sends one SQL statement to MySQL: create a table, insert a student, update, delete, and list everyone. The five handlers are short and nearly identical, so once you can read one you can read them all, and the few places where they differ are what this lesson is about.

The example: five buttons and a text area#

The example is a single window: five buttons stacked in a VBox, and a TextArea called output underneath, where every handler writes its result. The complete program, with its imports, start method and main, is assembled in the next lesson. Here the handlers come one at a time.

Every handler follows the same six steps: the JDBC steps from the last lesson, squeezed into one click.

1. 1Open a connection with DBConnection.getConnection(), inside a try-with-resources header.
2. 2Make a statement in the same header: conn.createStatement() for fixed SQL, or conn.prepareStatement(sql) when values go into ? placeholders.
3. 3Run the SQL: executeUpdate if it changes the table, executeQuery if it reads rows.
4. 4Show the result in the text area with output.setText(...).
5. 5Close everything automatically: when the try block ends, however it ends, the statement and then the connection are closed.
6. 6On failure, catch (SQLException ex) puts ex.getMessage() in the text area instead.

The handlers assume the improved DBConnection from the last lesson, the one that declares throws SQLException. With it, a stopped server lands in the same catch block, and the text area says Communications link failure. With the first version, which returns null, the click fails silently and a NullPointerException appears in the console.

executeUpdate or executeQuery#

Every handler runs its SQL through one of two methods. The choice depends on what the statement gives back, as in the table at the end of the last lesson's SQL section.

A third method, execute(sql), is for SQL whose kind you do not know in advance. It returns true when the result is a ResultSet, which you then fetch with getResultSet(), and false when it is a row count, fetched with getUpdateCount().

Create Table#

The first button creates the table that the other four work on.

Table created successfully!

Here is what each part does, in order:

• The handler. createBtn.setOnAction(e -> { ... }) sets the code that runs each time the button is pressed. The lambda is the EventHandler<ActionEvent> from event handling, and e is the ActionEvent, unused here.
• Connection and statement. DBConnection.getConnection() opens a connection, and conn.createStatement() makes a Statement, the object that carries SQL to MySQL. Both live in the try header. A plain Statement is enough because this SQL contains no values from outside the program.
• The SQL. The string is built from three pieces joined with +, only to keep the lines short. Joined, it reads CREATE TABLE IF NOT EXISTS students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT), which asks MySQL for a table named students with three columns. The table below takes it apart.
• Running it. stmt.executeUpdate(sql) sends the command. executeUpdate is the method for SQL that changes the database. For CREATE TABLE it returns 0, since no rows were affected, and the handler ignores the number. Then Table created successfully! goes into the text area, or, if anything threw, the catch block puts the error message there.
• Closing. try-with-resources closes the statement and the connection when the block ends, whether it finished or threw. Without it they would leak: connections left open on the server. MySQL allows 151 at a time by default, so a program that leaked one per click would eventually be refused with Too many connections.

IF NOT EXISTS is there for the second click. Without it, a second click puts Table 'students' already exists in the text area. With it, MySQL notes the fact quietly and carries on, so the second click also reports Table created successfully!, although nothing was created. The message overstates what happened, but the table exists either way, which is all the other buttons need.

Check the result at the prompt:

id says NOunder Null because a primary key can never be empty. The Extra column, empty for the last lesson's JavaCourse, now says auto_increment.

Insert#

Record inserted!

• A PreparedStatement. After DBConnection.getConnection() opens a connection, conn.prepareStatement(sql) makes a PreparedStatement. It takes the SQL straight away, with question marks where the values will go.
• The SQL. INSERT INTO students (name, age) VALUES (?, ?) names the two columns it fills and leaves out id, so AUTO_INCREMENT supplies it. This is the column-list form of INSERT from the last lesson; the JavaCourse inserts gave a value for every column instead.
• The placeholders. Each question mark is a placeholder, a slot for a value that is filled in before the statement runs. They are numbered from 1, left to right: ps.setString(1, "John") fills the first and ps.setInt(2, 20) the second. Choose the setter that matches the column: setString for VARCHAR, setInt for INT, setDouble for DOUBLE. Numbering from 0 fails with Parameter index out of range (0 < 1 )., and leaving one unset fails with No value specified for parameter 2.
• Running it. ps.executeUpdate() runs the INSERT and adds the row. It takes no argument, because the SQL went to prepareStatement. It returns 1, the number of rows added. The handler ignores that, which is safe here: an INSERT either adds its row or throws.
• Success, failure, closing. On success the text area shows Record inserted!. A SQLException, for example from a lost connection or a typo in the SQL, puts its message there instead, and try-with-resources closes the prepared statement and the connection.

After one click the table holds one row:

Click Insert again and you get a second John, with id 2. Nothing in the table forbids two students with the same name: only id has to be unique, and MySQL supplies a new one each time.

Keep both Johns in mind. The Update and Delete buttons find their rows by name.

Why PreparedStatement, and not +#

The insert could have been written by gluing the values into the SQL with +, the way the Create Table string is built. With a name typed by the user, it would look like this:

For John the string comes out as INSERT INTO students (name, age) VALUES ('John', 20), and it works. Two problems appear as soon as the name is not one you chose.

A quote in the data#

SQL marks the start and end of text with single quotes. For the name O'Brien, the string becomes the SQL below. The quote inside the name ends the text after the O, and MySQL cannot make sense of what follows.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Brien', 20)' at line 1

An ordinary surname, and the insert fails.

SQL injection#

The second problem is worse: whoever types into the field gets to write part of your SQL. Take the Delete handler, built the same way:

Someone types x' OR '1'='1 into the name field. This is the SQL that reaches MySQL:

Every row matches, so every student is deleted.

The first quote in the input closes the name early, and the rest becomes part of the command. '1'='1' is true for every row, and OR makes the whole condition true whenever either side is, so the WHERE matches the entire table. The user typed code, not data. This is SQL injection, one of the oldest and most common security holes in programs that talk to databases.

With a placeholder the same input is harmless. The Delete handler below uses DELETE FROM students WHERE name=?. Given x' OR '1'='1 through setString, MySQL looks for a student whose name is literally that string of characters, finds none, and executeUpdatereturns 0. O'Brien inserts correctly for the same reason. That is the main reason to use a PreparedStatement: a value can only ever be a value.

Key idea

Never build SQL by joining user input with +. Put a ? where each value goes, and hand the value over with setString or setInt.

Update#

Record updated!

• The SQL. A PreparedStatement runs UPDATE students SET age=? WHERE name=?. The placeholders are numbered by their position in the SQL, not by meaning. The age's ? comes first, so it is ps.setInt(1, 21), and the name's comes second, ps.setString(2, "John").
• Which rows.It is easy to read this as “change John's age to 21”. It changes the age of every record whose name matches. After two clicks on Insert there are two Johns, and both become 21. A name is not a key; to change exactly one student, match on the key with WHERE id=?.
• Errors and closing. If anything fails, say the connection drops or the table does not exist, the catch block shows the SQLException's message instead, such as Table 'studentdb.students' doesn't exist. try-with-resources closes both resources, as before.

Use the number executeUpdate returns#

The handler prints Record updated! whatever happened. Click Update on a table with no John in it: executeUpdate returns 0, nothing throws, the try block runs to its end, and the text area announces an update that never happened. The returned number is how you tell the difference.

With two Johns in the table:  2 record(s) updated.
With no John in the table:    No student named John was found.

Delete#

Record deleted!

• When deleteBtn is clicked, the handler opens a connection inside a try-with-resources block, so the connection is closed again when the block ends.
• It prepares DELETE FROM students WHERE name=? as a PreparedStatement, with the protection against injection shown above. ps.setString(1, "John") fills the one placeholder.
• ps.executeUpdate() deletes every matching record, not just one. Both Johns go, and it returns 2. Like Update, the handler shows Record deleted! even when it returns 0.
• On success the text area shows Record deleted!; if a SQLException is thrown, it shows the error message instead.

The table is empty. Insert again and the new John gets id 3, not 1: AUTO_INCREMENT never reuses a number, even when its row is gone.

Select#

If you clicked the buttons in order, Delete has just emptied the table, and Select would show only its first line, Students:. To give the loop something to do, start again with a fresh table: run drop table students; at the prompt, click Create Table, Insert and Update once each, and add two more students at the prompt.

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

The table now holds John (id 1, age 21 after the update), Asha (id 2, age 19) and Ravi (id 3, age 22). Click Select:

Students:
1 - John - 21
2 - Asha - 19
3 - Ravi - 22

• The Select button's handler reads every student from the database and shows them in the text area.
• The try header declares three resources, so all three are closed when the block ends. They close in reverse order: rs first, then stmt, then conn. Reverse is the safe order, because each was made from the one before it: the result set belongs to the statement, and the statement to the connection.
• A Statement runs SELECT * FROM students, which fetches every row of the table. A plain Statement is fine, since the query has no values in it. It runs through executeQuery because it returns rows, and the rows come back in a ResultSet.
• A StringBuilder called data collects the text, starting with the heading line.
• The while (rs.next()) loop visits each row in turn, reads its id, name and age, and appends them to data. After the loop, output.setText(data.toString()) shows the lot.
• A SQLException, from a lost connection or a bad query, shows its message instead. Before Create Table has ever been clicked, that message is Table 'studentdb.students' doesn't exist.

The ResultSet is a cursor#

A ResultSet is not a list you can index. It is a cursor: a marker that starts just before the first row. Each call to rs.next() moves it down one row and returns true if it landed on a row, or false once it has gone past the last one.

That is why the loop is while (rs.next()). One call both moves the cursor and answers whether there is a row to read, so the body runs exactly once per row and never before the first. Reading a column before the first next() fails with Before start of result set.

Key idea

A ResultSet starts before the first row. Each next() steps onto the next row and says whether there was one, so while (rs.next()) visits every row exactly once.

Inside the loop, the getters read a column of the row the cursor is on. rs.getInt("id") reads by column name, and the name is not case-sensitive. rs.getInt(1) reads by position: with SELECT *, column 1 is id, 2 is name and 3 is age, counted from 1 like the placeholders. Names are easier to read and survive a change in the order of the columns, so prefer them. Match the getter to the column's type, getInt for INT and getString for VARCHAR. A name that does not exist fails with Column 'age2' not found..

Each append adds to the end of the builder and returns the same builder, which is why the calls chain: data.append(rs.getInt("id")).append(" - ")appends the id, then the separator, to one object. For John's row the chain adds 1 - John - 21 and a newline. Building with += instead would create a new String on every pass, as strings showed; the builder grows one object in place.

A ResultSet cannot run in the browser, but its loop can. This model keeps the three rows in arrays, uses row = 0 for “before the first row”, and does the two jobs of rs.next() by hand: check that another row exists, then step onto it. A String stands in for the StringBuilder. Step through it and watch row and data.

Real input instead of John and 20#

Every handler so far uses fixed values: John and 20 in the insert, 21 and John in the update. A real form reads them from text fields. Here is the Insert handler reading two TextFields, nameField and ageField, added to the window above the buttons.

Name Asha, age 19          ->  Record inserted!
Name Asha, age nineteen    ->  Age must be a whole number, for example 20.
Name Asha, age left empty  ->  Age must be a whole number, for example 20.

• getText() returns a String. setString takes it as it is, but setInt needs an int, so Integer.parseInt converts the age first. trim() removes spaces at either end, because Integer.parseInt(" 19") fails on the leading space.
• parseInt throws NumberFormatException for text that is not a whole number, including an empty field. That is an unchecked exception and not a SQLException, so without its own catch it would escape the handler the way the NullPointerException did in the last lesson: nothing in the window, a stack trace in the console.
• The two catch blocks can go in either order, because neither exception class is a subclass of the other: SQLException extends Exception, and NumberFormatException extends IllegalArgumentException. Order matters only when one catch type is a subclass of another, as in the Armstrong program in event handling, where NumberFormatException must come before IllegalArgumentException.
• The age is parsed after the connection has been opened, so a bad age costs a connection that is opened and closed for nothing. Checking the fields before any database work is tidier.
• An empty name still goes in, as a student with no name. A real form checks that too.