Lesson 3 - Databases in Java JDBC - INSERT, UPDATE, DELETE, and COUNT

In the previous lesson of our Java database course, Databases in Java JDBC - Printing data and parameters, we programmed a simple dictionary using JDBC and learned how to defend against the SQL injection attack. Today, we're going to continue with JDBC.

Inserting, deleting, and editing values

Basic database operations are often referred to as CRUD. These are Create, Read, Update and Delete. Last time we tried the Read operation. We already know that we read data from the database using the SELECT statement and the Java ResultSet collection.

INSERT

Let's ask the user to enter a word in Spanish and English. We'll then put the new word into the database.

Scanner scanner = new Scanner(System.in);
System.out.println("Enter a new Spanish word:");
String spanish = scanner.nextLine();
System.out.println("Enter the word in English:");
String english = scanner.nextLine();
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("INSERT INTO word (spanish, english) VALUES (?, ?)");) {
    statement.setString(1, spanish);
    statement.setString(2, english);
    int rows = statement.executeUpdate();
    System.out.println(rows);
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

The SQL statement for inserting a new row into a table starts with the INSERT INTO statement, followed by the table name. We provide the column names we want to insert the values into in parentheses. After the VALUES clause, we write the values also in parentheses. You shouldn't be surprised we use the parameters again.

We'll run the query by the executeUpdate() method. We use this method when we change data in the database. The method returns the number of affected rows, which allows us to verify that the insertion has been completed, we print this number to the console.

Console application
Enter a new Spanish word:
teclado
Enter the word in English:
keyboard

executeUpdate() returned 1. We can run the original code of our application that prints all the words or use NetBeans to display the data. We can see that the keyboard is now among the words:

DELETE

Let's move to deleting entries. There's basically no difference compared to inserting, but I just wanted you to see all the CRUD queries for completeness.

Scanner scanner = new Scanner(System.in);
System.out.println("Enter the Spanish word you want to delete:");
String spanish = scanner.nextLine();
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("DELETE FROM word WHERE spanish=?");) {
    statement.setString(1, spanish);
    int rows = statement.executeUpdate();
    System.out.println(rows);
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

The Java code hasn't changed at all. The SQL statement starts with DELETE FROM and continues with the table name. This is followed by the WHERE condition, which we already know well. Never forget the condition in the DELETE statement, otherwise all the rows in the table will be deleted! We'll try the application again:

Console application
Enter the Spanish word you want to delete:
teclado
1

You can make sure the keyboard is no longer among the words. We can also try to delete a word which doesn't exist, the number of affected rows will be 0.

Console application
Enter the Spanish word you want to delete:
zorro
0

UPDATE

We'll end our examples with the UPDATE statement, which changes the data. Let's teach the application to change the English translation of a Spanish word:

Scanner scanner = new Scanner(System.in);
System.out.println("Enter the Spanish word to change the meaning of:");
String spanish = scanner.nextLine();
System.out.println("Enter the new meaning:");
String english = scanner.nextLine();
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("UPDATE word SET english=? WHERE spanish=?");) {
    statement.setString(1, english);
    statement.setString(2, spanish);
    int rows = statement.executeUpdate();
    System.out.println(rows);
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

The Java is the same again (we just added a parameter). The SQL statement starts with UPDATE, followed by the table name and then by the SET clause. After that, the column name, the equals sign, and the new value follow. If we need to update multiple columns, we can write it as follows:

UPDATE word SET english=?, spanish=? WHERE id=?

As with DELETE we must never forget the WHERE clause, otherwise all rows in the table will be updated.

Let's try the app again

Console application
Enter the Spanish word to change the meaning of:
dog
Enter the new meaning:
espiar
1

Check that the translation has changed.

Number of rows

In our applications, we'll be often interested in the number of rows in a table. To get that value, we use the SQL COUNT clause. Let's show how to use it:

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM word");) {
    ResultSet result = statement.executeQuery();
    result.next();
    System.out.println(result.getInt(1));
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

We use the SELECT statement to query the number of rows. The COUNT clause returns an aggregate value. Aggregate means that it's based on multiple columns. Such values are e.g. the count, sum, and average. The asterisk in the parentheses indicates that we are interested in all columns. If we provided a column name instead, only the rows with this column filled in (not with the NULL value in it) would be counted. Of course, we could add a WHERE clause and count only those rows that meet some condition (for example, the Spanish word starting with "A").

We'll simply store the result into a ResultSet, move to the first row using the next() method and print the first column.

The result:

Console application
6

Without knowing about the COUNT clause, you might think of writing the following code:

// This code is ineffective

try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/dictionary_db?user=root&password=");
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM word");) {
    ResultSet result = statement.executeQuery();
    result.last();
    System.out.println(result.getRow());
} catch (SQLException ex) {
    System.out.println("Error while communicating with the database");
}

We select all the words from the database that will be transferred to our application. The last() method moves to the last row in ResultSet and then returns its number. This necessarily gets us the number of rows in the table.

Although the result returned is the same and it might seem that everything is fine, it's not. The data transfer takes some time and also some memory. In the end, we just count all these rows and throw the data away. Imagine that there are a million words in the dictionary. This really isn't an effective solution.

We haven't mentioned using backticks in queries. If a column name conflicts with an SQL keyword (for example, the column name is where), we put it in backticks (`). We type this character using the key under the escape key. You might often encounter a convention where all identifiers are wrapped in backticks:

UPDATE `word` SET `english`=?, `spanish`=? WHERE `id`=?

Today's project with source code is attached to be downloaded below as always.

Did you have a problem with anything? Download the sample application below and compare it with your project, you will find the error easily.