Chapter 10: Managing Data in a Database

This chapter explains and demonstrates how to manage – that is, insert, read, update, and delete – data in a database using a Java application. It also provides a short introduction to Structured Query Language (SQL) and basic database operations, including how to connect to a database, how to create a database structure, how to write a database expression using SQL, and how to execute these expressions.

The following topics will be covered in this chapter:

  • Creating a database
  • Creating a database structure
  • Connecting to a database
  • Releasing the connection
  • Create, read, update, and delete (CRUD) operations on data
  • Using a shared library JAR file to access a database

By the end of the chapter, you will be able to create and use a database for storing, updating, and retrieving data as well as create and use a shared library.

Technical requirements

To be able to execute the code examples provided in this chapter, you will need the following:

  • A computer with either a Microsoft Windows, Apple macOS, or Linux operating system
  • Java SE version 17 or later
  • An IDE or code editor you prefer

The instructions for how to set up a Java SE and IntelliJ IDEA editor were provided in Chapter 1, Getting Started with Java 17. The files with the code examples for this chapter are available on GitHub (https://github.com/PacktPublishing/Learn-Java-17-Programming.git) in the examples/src/main/java/com/packt/learnjava/ch10_database folder, and in the database folder, as a separate project of the shared library.

Creating a database

Java Database Connectivity (JDBC) is a Java functionality that allows you to access and modify data in a database. It is supported by the JDBC API (which includes the java.sql, javax.sql, and java.transaction.xa packages) and the database-specific class that implements an interface for database access (called a database driver), which is provided by each database vendor.

Using JDBC means writing Java code that manages data in a database using the interfaces and classes of the JDBC API and a database-specific driver, which knows how to establish a connection with the particular database. Using this connection, an application can then issue requests written in SQL.

Naturally, we are only referring to the databases that understand SQL here. They are called relational or tabular database management systems (DBMSs) and make up the vast majority of the currently used DBMSs – although some alternatives (for example, a navigational database and NoSQL) are used too.

The java.sql and javax.sql packages are included in the Java Platform Standard Edition (Java SE). The javax.sql package contains the DataSource interface that supports the statement’s pooling, distributed transactions, and rowsets.

Creating a database involves the following eight steps:

  1. Install the database by following the vendor instructions.
  2. Open the PL/SQL terminal and create a database user, a database, a schema, tables, views, stored procedures, and anything else that is necessary to support the data model of the application.
  3. Add to this application the dependency on a .jar file with the database-specific driver.
  4. Connect to the database from the application.
  5. Construct the SQL statement.
  6. Execute the SQL statement.
  7. Use the result of the execution as your application requires.
  8. Release (that is, close) the database connection and any other resources that were opened in the process.

Steps 1 to 3 are performed only once during the database setup and before the application is run. Steps 4 to 8 are performed by the application repeatedly as needed. In fact, Steps 5 to 7 can be repeated multiple times with the same database connection.

For our example, we are going to use the PostgreSQL database. You will first need to perform Steps 1 to 3 by yourself using the database-specific instructions. To create the database for our demonstration, we use the following PL/SQL commands:

create user student SUPERUSER;

create database learnjava owner student;

These commands create a student user that can manage all aspects of the SUPERUSER database, and make the student user an owner of the learnjava database. We will use the student user to access and manage data from the Java code. In practice, for security considerations, an application is not allowed to create or change database tables and other aspects of the database structure.

Additionally, it is a good practice to create another logical layer, called a schema, which can have its own set of users and permissions. This way, several schemas in the same database can be isolated, and each user (one of them is your application) can only access certain schemas. On an enterprise level, the common practice is to create synonyms for the database schema so that no application can access the original structure directly. However, we do not do this in this book for the sake of simplicity.

Creating a database structure

After the database is created, the following three SQL statements will allow you to create and change the database structure. This is done through database entities, such as a table, function, or constraint:

  • The CREATE statement creates the database entity.
  • The ALTER statement changes the database entity.
  • The DROP statement deletes the database entity.

There are also various SQL statements that allow you to inquire about each database entity. Such statements are database-specific and, typically, they are only used in a database console. For example, in the PostgreSQL console, d <table> can be used to describe a table, while dt lists all the tables. Refer to your database documentation for more details.

To create a table, you can execute the following SQL statement:

CREATE TABLE tablename ( column1 type1, column2 type2, ... );

The limitations for a table name, column names, and types of values that can be used depend on the particular database. Here is an example of a command that creates the person table in PostgreSQL:

CREATE table person ( 
   id SERIAL PRIMARY KEY, 
   first_name VARCHAR NOT NULL, 
   last_name VARCHAR NOT NULL, 
   dob DATE NOT NULL );

The SERIAL keyword indicates that this field is a sequential integer number that is generated by the database every time a new record is created. Additional options for generating sequential integers are SMALLSERIAL and BIGSERIAL; they differ by size and the range of possible values:

SMALLSERIAL: 2 bytes, range from 1 to 32,767
SERIAL: 4 bytes, range from 1 to 2,147,483,647
BIGSERIAL: 8 bytes, range from 1 to 922,337,2036,854,775,807

The PRIMARY_KEY keyword indicates that this is going to be the unique identifier of the record and will most probably be used in a search. The database creates an index for each primary key to make the search process faster. An index is a data structure that helps to accelerate data search in the table without having to check every table record. An index can include one or more columns of a table. If you request the description of the table, you will see all the existing indices.

Alternatively, we can make a composite PRIMARY KEY keyword using a combination of first_name, last_name, and dob:

CREATE table person ( 
   first_name VARCHAR NOT NULL, 
   last_name VARCHAR NOT NULL, 
   dob DATE NOT NULL,
   PRIMARY KEY (first_name, last_name, dob) );

However, there is a chance that there are two people who will have the same name and were born on the same day, so such a composite prim is not a good idea.

The NOT NULL keyword imposes a constraint on the field: it cannot be empty. The database will raise an error for every attempt to create a new record with an empty field or delete the value from the existing record. We did not set the size of the columns of type VARCHAR, thus allowing these columns to store string values of any length.

The Java object that matches such a record may be represented by the following Person class:

public class Person {
  private int id;
  private LocalDate dob;
  private String firstName, lastName;
  public Person(String firstName, String lastName, 
                                                LocalDate dob){
    if (dob == null) {
      throw new RuntimeException
                              ("Date of birth cannot be null");
    }
    this.dob = dob;
    this.firstName = firstName == null ? "" : firstName;
    this.lastName = lastName == null ? "" : lastName;
  }
  public Person(int id, String firstName,
                  String lastName, LocalDate dob) {
    this(firstName, lastName, dob);
    this.id = id;
  }
  public int getId() { return id; }
  public LocalDate getDob() { return dob; }
  public String getFirstName() { return firstName;}
  public String getLastName() { return lastName; }
}

As you may have noticed, there are two constructors in the Person class: with and without id. We will use the constructor that accepts id to construct an object based on the existing record, while the other constructor will be used to create an object before inserting a new record.

Once created, the table can be deleted using the DROP command:

DROP table person;

The existing table can also be changed using the ALTER SQL command; for example, we can add a column address:

ALTER table person add column address VARCHAR;

If you are not sure whether such a column exists already, you can add IF EXISTS or IF NOT EXISTS:

ALTER table person add column IF NOT EXISTS address VARCHAR;

However, this possibility exists only with PostgreSQL 9.6 and later versions.

Another important consideration to take note of during database table creation is whether another index (in addition to PRIMARY KEY) has to be added. For example, we can allow a case-insensitive search of first and last names by adding the following index:

CREATE index idx_names on person ((lower(first_name), lower(last_name));

If the search speed improves, we leave the index in place; if not, it can be removed, as follows:

 DROP index idx_names;

We remove it because an index has an overhead of additional writes and storage space.

We also can remove a column from a table if we need to, as follows:

ALTER table person DROP column address;

In our examples, we follow the naming convention of PostgreSQL. If you use a different database, we suggest that you look up its naming convention and follow it, so that the names you create align with those that are created automatically.

Connecting to a database

So far, we have used a console to execute SQL statements. The same statements can be executed from Java code using the JDBC API too. But, tables are created only once, so there is not much sense in writing a program for a one-time execution.

Data management, however, is another matter. So, from now on, we will use Java code to manipulate data in a database. In order to do this, we first need to add the following dependency to the pom.xml file in the database project:

<dependency> 
    <groupId>org.postgresql</groupId> 
    <artifactId>postgresql</artifactId> 
    <version>42.3.2</version> 
</dependency>

The example project also gets access to this dependency because, in the pom.xml file of the example project, we have the following dependency on the database .jar file:

<dependency> 
    <groupId>com.packt.learnjava</groupId>
    <artifactId>database</artifactId>
    <version>1.0-SNAPSHOT</version> 
</dependency>

Make sure to install the database project by executing the "mvn clean install" command in the database folder before running any of the examples.

Now, we can create a database connection from the Java code, as follows:

String URL = "jdbc:postgresql://localhost/learnjava";
Properties prop = new Properties();
prop.put( "user", "student" );
// prop.put( "password", "secretPass123" );
try {
 Connection conn = DriverManager.getConnection(URL, prop);
} catch (SQLException ex) {
    ex.printStackTrace();
}

The preceding code is just an example of how to create a connection using the java.sql.DriverManger class. The prop.put( "password", "secretPass123" ) statement demonstrates how to provide a password for the connection using the java.util.Properties class. However, we did not set a password when we created the student user, so we do not need it.

Many other values can be passed to DriverManager that configure the connection behavior. The name of the keys for the passed-in properties are the same for all major databases, but some of them are database-specific. So, read your database vendor documentation for more details.

Alternatively, for passing user and password only, we could use an overloaded DriverManager.getConnection(String url, String user, String password) version. It is a good practice to keep the password encrypted. We are not going to demonstrate how to do it, but there are plenty of guides available on the internet that you can refer to.

Another way of connecting to a database is to use the javax.sql.DataSource interface. Its implementation is included in the same .jar file as the database driver. In the case of PostgreSQL, there are two classes that implement the DataSource interface:

  • org.postgresql.ds.PGSimpleDataSource
  • org.postgresql.ds.PGConnectionPoolDataSource

We can use these classes instead of DriverManager. The following code is an example of creating a database connection using the PGSimpleDataSource class:

PGSimpleDataSource source = new PGSimpleDataSource();
source.setServerName("localhost");
source.setDatabaseName("learnjava");
source.setUser("student");
//source.setPassword("password");
source.setLoginTimeout(10);
try {
    Connection conn = source.getConnection();
} catch (SQLException ex) {
    ex.printStackTrace();
}

Using the PGConnectionPoolDataSource class allows you to create a pool of Connection objects in memory, as follows:

PGConnectionPoolDataSource source = new PGConnectionPoolDataSource();
source.setServerName("localhost");
source.setDatabaseName("learnjava");
source.setUser("student");
//source.setPassword("password");
source.setLoginTimeout(10);
try {
    PooledConnection conn = source.getPooledConnection();
    Set<Connection> pool = new HashSet<>();
    for(int i = 0; i < 10; i++){
        pool.add(conn.getConnection())
    }
} catch (SQLException ex) {
    ex.printStackTrace();
}

This is a preferred method because creating a Connection object takes time. Pooling allows you to do it upfront and then reuse the created objects when they are needed. After the connection is no longer required, it can be returned to the pool and reused. The pool size and other parameters can be set in a configuration file (such as postgresql.conf for PostgreSQL).

However, you do not need to manage the connection pool yourself. There are several mature frameworks that can do it for you, such as HikariCP (https://github.com/brettwooldridge/HikariCP), Vibur (http://www.vibur.org), and Commons DBCP (https://commons.apache.org/proper/commons-dbcp) – these are reliable and easy to use.

Whatever method of creating a database connection we choose, we are going to hide it inside the getConnection() method and use it in all our code examples in the same way. With the object of the Connection class acquired, we can now access the database to add, read, delete, or modify the stored data.

Releasing the connection

Keeping the database connection alive requires a significant number of resources, such as memory and CPU, so it is a good idea to close the connection and release the allocated resources as soon as you no longer need them. In the case of pooling, the Connection object, when closed, is returned to the pool and consumes fewer resources.

Before Java 7, a connection was closed by invoking the close() method in a finally block:

try {
    Connection conn = getConnection();
    //use object conn here
} finally { 
    if(conn != null){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    } 
}

The code inside the finally block is always executed, whether the exception inside the try block is thrown or not. However, since Java 7, the try-with-resources construct also does the job on any object that implements the java.lang.AutoCloseable or java.io.Closeable interface. Since the java.sql.Connection object does implement the AutoCloseable interface, we can rewrite the previous code snippet, as follows:

try (Connection conn = getConnection()) {
    //use object conn here
} catch(SQLException ex) {
    ex.printStackTrace();
}    

The catch clause is necessary because the AutoCloseable resource throws java.sql.SQLException.

CRUD data

There are four kinds of SQL statements that read or manipulate data in a database:

  • The INSERT statement adds data to a database.
  • The SELECT statement reads data from a database.
  • The UPDATE statement changes data in a database.
  • The DELETE statement deletes data from a database.

Either one or several different clauses can be added to the preceding statements to identify the data that is requested (such as the WHERE clause) and the order in which the results have to be returned (such as the ORDER clause).

The JDBC connection is represented by java.sql.Connection. This, among others, has the methods required to create three types of objects that allow you to execute SQL statements that provide different functionality to the database side:

  • java.sql.Statement: This simply sends the statement to the database server for execution.
  • java.sql.PreparedStatement: This caches the statement with a certain execution path on the database server by allowing it to be executed multiple times with different parameters in an efficient manner.
  • java.sql.CallableStatement: This executes the stored procedure in the database.

In this section, we are going to review how to do it in Java code. The best practice is to test the SQL statement in the database console before using it programmatically.

The INSERT statement

The INSERT statement creates (populates) data in the database and has the following format:

INSERT into table_name (column1, column2, column3,...) 
                values (value1, value2, value3,...);

Alternatively, when several records need to be added, you can use the following format:

INSERT into table_name (column1, column2, column3,...) 
                values (value1, value2, value3,... ), 
                       (value21, value22, value23,...),
                       ...;

The SELECT statement

The SELECT statement has the following format:

SELECT column_name, column_name FROM table_name 
                        WHERE some_column = some_value;

Alternatively, when all the columns need to be selected, you can use the following format:

SELECT * from table_name WHERE some_column=some_value;

A more general definition of the WHERE clause is as follows:

WHERE column_name operator value 
Operator: 
= Equal 
<> Not equal. In some versions of SQL, != 
> Greater than 
< Less than 
>= Greater than or equal 
<= Less than or equal IN Specifies multiple possible values for a column 
LIKE Specifies the search pattern 
BETWEEN Specifies the inclusive range of values in a column

The construct’s column_name operator value can be combined using the AND and OR logical operators, and grouped by brackets, ( ).

For example, the following method brings all the first name values (separated by a whitespace character) from the person table:

String selectAllFirstNames() {
    String result = "";
    Connection conn = getConnection();
    try (conn; Statement st = conn.createStatement()) {
      ResultSet rs = 
        st.executeQuery("select first_name from person");
      while (rs.next()) {
          result += rs.getString(1) + " ";
      }
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
    return result;
}

The getString(int position) method of the ResultSet interface extracts the String value from position 1 (the first in the list of columns in the SELECT statement). There are similar getters for all primitive types: getInt(int position), getByte(int position), and more.

It is also possible to extract the value from the ResultSet object using the column name. In our case, it will be getString("first_name"). This method of getting values is especially useful when the SELECT statement is as follows:

select * from person;

However, bear in mind that extracting values from the ResultSet object using the column name is less efficient. The difference in performance, though, is very small and only becomes important when the operation takes place many times. Only the actual measuring and testing processes can tell whether the difference is significant to your application or not. Extracting values by column name is especially attractive because it provides better code readability, which pays off in the long run during application maintenance.

There are many other useful methods in the ResultSet interface. If your application reads data from a database, we highly recommend that you read the official documentation (www.postgresql.org/docs) of the SELECT statement and the ResultSet interface for the database version you are using.

The UPDATE statement

The data can be changed by the UPDATE statement, as follows:

UPDATE table_name SET column1=value1,column2=value2,... WHERE clause;

We can use this statement to change the first name in one of the records from the original value, John, to a new value, Jim:

update person set first_name = 'Jim' where last_name = 'Adams';

Without the WHERE clause, all the records of the table will be affected.

The DELETE statement

To remove records from a table, use the DELETE statement, as follows:

DELETE FROM table_name WHERE clause;

Without the WHERE clause, all the records of the table are deleted. In the case of the person table, we can delete all the records using the following SQL statement:

delete from person;

Additionally, this statement only deletes the records that have a first name of Jim:

delete from person where first_name = 'Jim';

Using statements

The java.sql.Statement interface offers the following methods for executing SQL statements:

  • boolean execute(String sql): This returns true if the executed statement returns data (inside the java.sql.ResultSet object) that can be retrieved using the ResultSet getResultSet() method of the java.sql.Statement interface. Alternatively, it returns false if the executed statement does not return data (for the INSERT statement or the UPDATE statement) and the subsequent call to the int getUpdateCount() method of the java.sql.Statement interface returns the number of affected rows.
  • ResultSet executeQuery(String sql): This returns data as a java.sql.ResultSet object (the SQL statement used with this method is usually a SELECT statement). The ResultSet getResultSet() method of the java.sql.Statement interface does not return data, while the int getUpdateCount() method of the java.sql.Statement interface returns -1.
  • int executeUpdate(String sql): This returns the number of affected rows (the executed SQL statement is expected to be the UPDATE statement or the DELETE statement). The same number is returned by the int getUpdateCount() method of the java.sql.Statement interface; the subsequent call to the ResultSet getResultSet() method of the java.sql.Statement interface returns null.

We will demonstrate how these three methods work on each of the statements: INSERT, SELECT, UPDATE, and DELETE.

The execute(String sql) method

Let’s try executing each of the statements; we’ll start with the INSERT statement:

String sql = 
   "insert into person (first_name, last_name, dob) " +
                "values ('Bill', 'Grey', '1980-01-27')";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    System.out.println(st.execute(sql)); //prints: false
    System.out.println(st.getResultSet() == null); 
                                                 //prints: true
    System.out.println(st.getUpdateCount()); //prints: 1
} catch (SQLException ex) {
    ex.printStackTrace();
}
System.out.println(selectAllFirstNames()); //prints: Bill

The preceding code adds a new record to the person table. The returned false value indicates that there is no data returned by the executed statement; this is why the getResultSet() method returns null. But, the getUpdateCount() method returns 1 because one record was affected (added). The selectAllFirstNames() method proves that the expected record was inserted.

Now, let’s execute the SELECT statement, as follows:

String sql = "select first_name from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    System.out.println(st.execute(sql));    //prints: true
    ResultSet rs = st.getResultSet();
    System.out.println(rs == null);             //prints: false
    System.out.println(st.getUpdateCount());    //prints: -1
    while (rs.next()) {
        System.out.println(rs.getString(1) + " "); 
                                                 //prints: Bill
    }
} catch (SQLException ex) {
    ex.printStackTrace();
}

The preceding code selects all the first names from the person table. The returned true value indicates that there is data returned by the executed statement. That is why the getResultSet() method does not return null but a ResultSet object instead. The getUpdateCount() method returns -1 because no record was affected (changed). Since there was only one record in the person table, the ResultSet object contains only one result, and rs.getString(1) returns Bill.

The following code uses the UPDATE statement to change the first name in all the records of the person table to Adam:

String sql = "update person set first_name = 'Adam'";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    System.out.println(st.execute(sql));  //prints: false
    System.out.println(st.getResultSet() == null); 
                                           //prints: true
    System.out.println(st.getUpdateCount());  //prints: 1
} catch (SQLException ex) {
    ex.printStackTrace();
}
System.out.println(selectAllFirstNames()); //prints: Adam

In the preceding code, the returned false value indicates that there is no data returned by the executed statement. This is why the getResultSet() method returns null. But, the getUpdateCount() method returns 1 because one record was affected (changed) since there was only one record in the person table. The selectAllFirstNames() method proves that the expected change was made to the record.

The following DELETE statement execution deletes all records from the person table:

String sql = "delete from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    System.out.println(st.execute(sql));  //prints: false
    System.out.println(st.getResultSet() == null); 
                                           //prints: true
    System.out.println(st.getUpdateCount());  //prints: 1
} catch (SQLException ex) {
    ex.printStackTrace();
}
System.out.println(selectAllFirstNames());  //prints:

In the preceding code, the returned false value indicates that there is no data returned by the executed statement. That is why the getResultSet() method returns null. But, the getUpdateCount() method returns 1 because one record was affected (deleted) since there was only one record in the person table. The selectAllFirstNames() method proves that there are no records in the person table.

The executeQuery(String sql) method

In this section, we will try to execute the same statements (as a query) that we used when demonstrating the execute() method in the The execute(String sql) method section. We’ll start with the INSERT statement, as follows:

String sql = 
"insert into person (first_name, last_name, dob) " +
              "values ('Bill', 'Grey', '1980-01-27')";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeQuery(sql);         //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();         //prints: stack trace 
}
System.out.println(selectAllFirstNames()); //prints: Bill

The preceding code generates an exception with the No results were returned by the query message because the executeQuery() method expects to execute the SELECT statement. Nevertheless, the selectAllFirstNames() method proves that the expected record was inserted.

Now, let’s execute the SELECT statement, as follows:

String sql = "select first_name from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    ResultSet rs1 = st.executeQuery(sql);
    System.out.println(rs1 == null);     //prints: false
    ResultSet rs2 = st.getResultSet();
    System.out.println(rs2 == null);     //prints: false
    System.out.println(st.getUpdateCount()); //prints: -1
    while (rs1.next()) {
        System.out.println(rs1.getString(1)); //prints: Bill
    }
    while (rs2.next()) {
        System.out.println(rs2.getString(1)); //prints:
    }
} catch (SQLException ex) {
    ex.printStackTrace();
}

The preceding code selects all the first names from the person table. The returned false value indicates that executeQuery() always returns the ResultSet object, even when no record exists in the person table. As you can see, there appear to be two ways of getting a result from the executed statement. However, the rs2 object has no data, so, while using the executeQuery() method, make sure that you get the data from the ResultSet object.

Now, let’s try to execute an UPDATE statement, as follows:

String sql = "update person set first_name = 'Adam'";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeQuery(sql);           //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();           //prints: stack trace
}
System.out.println(selectAllFirstNames()); //prints: Adam

The preceding code generates an exception with the No results were returned by the query message because the executeQuery() method expects to execute the SELECT statement. Nevertheless, the selectAllFirstNames() method proves that the expected change was made to the record.

We are going to get the same exception while executing the DELETE statement:

String sql = "delete from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeQuery(sql);           //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();           //prints: stack trace
}
System.out.println(selectAllFirstNames()); //prints:

Nevertheless, the selectAllFirstNames() method proves that all the records of the person table were deleted.

Our demonstration shows that executeQuery() should be used for SELECT statements only. The advantage of the executeQuery() method is that, when used for SELECT statements, it returns a not-null ResultSet object even when there is no data selected, which simplifies the code since there is no need to check the returned value for null.

The executeUpdate(String sql) method

We’ll start demonstrating the executeUpdate() method with the INSERT statement:

String sql = 
"insert into person (first_name, last_name, dob) " +
               "values ('Bill', 'Grey', '1980-01-27')";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
   System.out.println(st.executeUpdate(sql)); //prints: 1
   System.out.println(st.getResultSet());  //prints: null
   System.out.println(st.getUpdateCount());  //prints: 1
} catch (SQLException ex) {
    ex.printStackTrace();
}
System.out.println(selectAllFirstNames()); //prints: Bill

As you can see, the executeUpdate() method returns the number of affected (inserted, in this case) rows. The same number returns the int getUpdateCount() method, while the ResultSet getResultSet() method returns null. The selectAllFirstNames() method proves that the expected record was inserted.

The executeUpdate() method can’t be used for executing the SELECT statement:

String sql = "select first_name from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    st.executeUpdate(sql);    //PSQLException
} catch (SQLException ex) {
    ex.printStackTrace();     //prints: stack trace
}

The message of the exception is A result was returned when none was expected.

The UPDATE statement, on the other hand, is executed by the executeUpdate() method just fine:

String sql = "update person set first_name = 'Adam'";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
  System.out.println(st.executeUpdate(sql));  //prints: 1
  System.out.println(st.getResultSet());   //prints: null
    System.out.println(st.getUpdateCount());    //prints: 1
} catch (SQLException ex) {
    ex.printStackTrace();
}
System.out.println(selectAllFirstNames());    //prints: Adam

The executeUpdate() method returns the number of affected (updated, in this case) rows. The same number returns the int getUpdateCount() method, while the ResultSet getResultSet() method returns null. The selectAllFirstNames() method proves that the expected record was updated.

The DELETE statement produces similar results:

String sql = "delete from person";
Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
    System.out.println(st.executeUpdate(sql));  //prints: 1
    System.out.println(st.getResultSet());      //prints: null
    System.out.println(st.getUpdateCount());    //prints: 1
} catch (SQLException ex) {
    ex.printStackTrace();
}
System.out.println(selectAllFirstNames());      //prints:

By now, you have probably realized that the executeUpdate() method is better suited for INSERT, UPDATE, and DELETE statements.

Using PreparedStatement

PreparedStatement is a subinterface of the Statement interface. This means that it can be used anywhere that the Statement interface is used. The advantage of PreparedStatement is that it is cached in the database instead of being compiled every time it is invoked. This way, it is efficiently executed multiple times for different input values. It can be created by the prepareStatement() method using the same Connection object.

Since the same SQL statement can be used for creating Statement and PreparedStatement, it is a good idea to use PreparedStatement for any SQL statement that is called multiple times because it performs better than the Statement interface on the database side. To do this, all we need to change are these two lines from the preceding code example:

try (conn; Statement st = conn.createStatement()) { 
     ResultSet rs = st.executeQuery(sql);

Instead, we can use the PreparedStatement class, as follows:

try (conn; PreparedStatement st = conn.prepareStatement(sql)) { 
     ResultSet rs = st.executeQuery();

To create the PreparedStatement object with parameters, you can substitute the input values with the question mark symbol (?); for example, we can create the following method (see the Person class in the database project):

private static final String SELECT_BY_FIRST_NAME = 
            "select * from person where first_name = ?";
static List<Person> selectByFirstName(Connection conn, 
                                     String searchName) {
    List<Person> list = new ArrayList<>();
    try (PreparedStatement st = 
         conn.prepareStatement(SELECT_BY_FIRST_NAME)) {
       st.setString(1, searchName);
       ResultSet rs = st.executeQuery();
       while (rs.next()) {
           list.add(new Person(rs.getInt("id"),
                    rs.getString("first_name"),
                    rs.getString("last_name"),
                    rs.getDate("dob").toLocalDate()));
       }
   } catch (SQLException ex) {
        ex.printStackTrace();
   }
   return list;
}

When it is used the first time, the database compiles the PreparedStatement object as a template and stores it. Then, when it is later used by the application again, the parameter value is passed to the template, and the statement is executed immediately without the overhead of compilation since it has been done already.

Another advantage of a prepared statement is that it is better protected from a SQL injection attack because values are passed in using a different protocol and the template is not based on the external input.

If a prepared statement is used only once, it may be slower than a regular statement, but the difference may be negligible. If in doubt, test the performance and see whether it is acceptable for your application – the increased security could be worth it.

Using CallableStatement

The CallableStatement interface (which extends the PreparedStatement interface) can be used to execute a stored procedure, although some databases allow you to call a stored procedure using either a Statement or PreparedStatement interface. A CallableStatement object is created by the prepareCall() method and can have parameters of three types:

  • IN for an input value
  • OUT for the result
  • IN OUT for either an input or an output value

The IN parameter can be set the same way as the parameters of PreparedStatement, while the OUT parameter must be registered by the registerOutParameter() method of CallableStatement.

It is worth noting that executing a stored procedure from Java programmatically is one of the least standardized areas. PostgreSQL, for example, does not support stored procedures directly, but they can be invoked as functions that have been modified for this purpose by interpreting the OUT parameters as return values. Oracle, on the other hand, allows the OUT parameters as functions too.

This is why the following differences between database functions and stored procedures can serve only as general guidelines and not as formal definitions:

  • A function has a return value, but it does not allow OUT parameters (except for some databases) and can be used in a SQL statement.
  • A stored procedure does not have a return value (except for some databases); it allows OUT parameters (for most databases) and can be executed using the JDBC CallableStatement interface.

You can refer to the database documentation to learn how to execute a stored procedure.

Since stored procedures are compiled and stored on the database server, the execute() method of CallableStatement performs better for the same SQL statement than the corresponding method of the Statement or PreparedStatement interface. This is one of the reasons why a lot of Java code is sometimes replaced by one or several stored procedures that even include business logic. However, there is no one right answer for every case and problem, so we will refrain from making specific recommendations, except to repeat the familiar mantra about the value of testing and the clarity of the code you are writing:

String replace(String origText, String substr1, String substr2) {
    String result = "";
    String sql = "{ ? = call replace(?, ?, ? ) }";
    Connection conn = getConnection();
    try (conn; CallableStatement st = conn.prepareCall(sql)) {
        st.registerOutParameter(1, Types.VARCHAR);
        st.setString(2, origText);
        st.setString(3, substr1);
        st.setString(4, substr2);
        st.execute();
        result = st.getString(1);
    } catch (Exception ex){
        ex.printStackTrace();
    }
    return result;
}

Now, we can call this method, as follows:

String result = replace("That is original text",
                         "original text", "the result");
System.out.println(result);  //prints: That is the result

A stored procedure can be without any parameters at all, with IN parameters only, with OUT parameters only, or with both. The result may be one or multiple values, or a ResultSet object. You can find the syntax of the SQL for function creation in your database documentation.

Using a shared library JAR file to access a database

In fact, we have already started using the database project JAR file to access the database driver, set as a dependency in the pom.xml file of the database project. Now, we are going to demonstrate how to use a JAR file of the database project JAR file to manipulate data in the database. An example of such usage is presented in the UseDatabaseJar class.

To support CRUD operations, a database table often represents a class of objects. Each row of such a table contains properties of one object of a class. In the Creating a database structure section, we demonstrated an example of such mapping between the Person class and the person table. To illustrate how to use a JAR file for data manipulation, we have created a separate database project that has only one Person class. In addition to the properties shown in the Creating a database structure section, it has static methods for all CRUD operations. The following is the insert() method: 

static final String INSERT = "insert into person " +
  "(first_name, last_name, dob) values (?, ?, ?::date)";
static void insert(Connection conn, Person person) {
   try (PreparedStatement st = 
                       conn.prepareStatement(INSERT)) {
            st.setString(1, person.getFirstName());
            st.setString(2, person.getLastName());
            st.setString(3, person.getDob().toString());
            st.execute();
   } catch (SQLException ex) {
            ex.printStackTrace();
   }
}

The following is the selectByFirstName() method:

private static final String SELECT = 
          "select * from person where first_name = ?";
static List<Person> selectByFirstName(Connection conn, 
                                    String firstName) {
   List<Person> list = new ArrayList<>();
   try (PreparedStatement st = conn.prepareStatement(SELECT)) {
        st.setString(1, firstName);
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            list.add(new Person(rs.getInt("id"),
                    rs.getString("first_name"),
                    rs.getString("last_name"),
                    rs.getDate("dob").toLocalDate()));
        }
   } catch (SQLException ex) {
            ex.printStackTrace();
   }
   return list;
}

The following is the updateFirstNameById() method:

private static final String UPDATE = 
      "update person set first_name = ? where id = ?";
public static void updateFirstNameById(Connection conn, 
                           int id, String newFirstName) {
   try (PreparedStatement st = conn.prepareStatement(UPDATE)) {
            st.setString(1, newFirstName);
            st.setInt(2, id);
            st.execute();
   } catch (SQLException ex) {
            ex.printStackTrace();
   }
}

The following is the deleteById() method:

private static final String DELETE = 
                       "delete from person where id = ?";
public static void deleteById(Connection conn, int id) {
   try (PreparedStatement st = conn.prepareStatement(DELETE)) {
            st.setInt(1, id);
            st.execute();
   } catch (SQLException ex) {
            ex.printStackTrace();
   }
}

As you can see, all the preceding methods accept the Connection object as a parameter, instead of creating and destroying it inside each method. We decided to do it like so because it allows several operations to associate with each Connection object in case we would like them to be committed to the database together or to be rolled back if one of them fails (read about transaction management in the documentation of the database of your choice). Besides, the JAR file (generated by the database project) can be used by different applications, so database connection parameters are going to be application-specific, and that is why the Connection object has to be created in the application that uses the JAR file. The following code demonstrates such a usage (see the UseDatabaseJar class).

Make sure you have executed the mvn clean install command in the database folder before running the following examples:

1 try(Connection conn = getConnection()){
2    cleanTablePerson(conn);
3    Person mike = new Person("Mike", "Brown", 
                             LocalDate.of(2002, 8, 14));
4    Person jane = new Person("Jane", "McDonald", 
                             LocalDate.of(2000, 3, 21));
5    Person jill = new Person("Jill", "Grey", 
                             LocalDate.of(2001, 4, 1));
6    Person.insert(conn, mike);
7    Person.insert(conn, jane);
8    Person.insert(conn, jane);
9    List<Person> persons = 
           Person.selectByFirstName(conn, jill.getFirstName());
10   System.out.println(persons.size());      //prints: 0
11   persons = Person.selectByFirstName(conn, 
                                          jane.getFirstName());
12   System.out.println(persons.size());      //prints: 2
13   Person person = persons.get(0);
14   Person.updateFirstNameById(conn, person.getId(),
                                          jill.getFirstName());
15   persons = Person.selectByFirstName(conn, 
                                          jane.getFirstName());
16   System.out.println(persons.size());      //prints: 1 
17   persons = Person.selectByFirstName(conn, 
                                          jill.getFirstName());
18   System.out.println(persons.size());      //prints: 1
19   persons = Person.selectByFirstName(conn, 
                                          mike.getFirstName());
20   System.out.println(persons.size());      //prints: 1
21   for(Person p: persons){
22      Person.deleteById(conn, p.getId());
23   }
24   persons = Person.selectByFirstName(conn, 
                                          mike.getFirstName());
25   System.out.println(persons.size());      //prints: 0
26 } catch (SQLException ex){
27       ex.printStackTrace();
28 }

Let’s walk through the preceding code snippet. Lines 1 and 26 to 28 compose the try–catch block that disposes of the Connection object and catches all the exceptions that may happen inside this block during its execution.

Line 2 was included just to clean up the data from the person table before running the demo code. The following is the implementation of the cleanTablePerson() method: 

void cleanTablePerson(Connection conn) {
   try (Statement st = conn.createStatement()) {
       st.execute("delete from person");
   } catch (SQLException ex) {
       ex.printStackTrace();
   }
}

In lines 3, 4, and 5, we create three objects of the Person class, then in lines 6, 7, and 8, we use them to insert records in the person table.

In line 9, we query the database for a record that has the first name taken from the jill object, and in line 10, we print out the result count, which is 0 (because we did not insert such a record).

In line 11, we query the database for a record that has the first name set to Jane, and in line 12, we print out the result count, which is 2 (because we did insert two records with such a value).

In line 13, we extract the first of the two objects returned by the previous query, and in line 14, we update the corresponding record with a different value for the first name (taken from the jill object).

In line 15, we repeat the query for a record with the first name set to Jane, and in line 16, we print out the result count, which is 1 this time (as expected, because we have changed the first name to Jill on one of the two records).

In line 17, we select all the records with the first name set to Jill, and in line 18, we print out the result count, which is 1 this time (as expected, because we have changed the first name to Jill on one of the two records that used to have the first name value Jane).

In line 19, we select all the records with the name set to Mike, and in line 20, we print out the result count, which is 1 (as expected, because we have created only one such record).

In lines 21 to 23, we delete all the retrieved records in a loop.

That is why when we select all the records with the first name Mike in line 24 again, we get a result count equal to 0 in line 25 (as expected, because there is no such record anymore).

At this point, when this code snippet is executed and the main() method of the UseDatabseJar class is completed, all the changes in the database are saved automatically.

That is how a JAR file (which allows modifying data in a database) can be used by any application that has this file as a dependency.

Summary

In this chapter, we discussed and demonstrated how the data in a database can be populated, read, updated, and deleted from a Java application. A short introduction to the SQL language described how to create a database and its structure, how to modify it, and how to execute SQL statements, using Statement, PreparedStatement, and CallableStatement.

Now, you can create and use a database for storing, updating, and retrieving data, and create and use a shared library.

In the next chapter, we will describe and discuss the most popular network protocols, demonstrate how to use them, and how to implement client-server communication using the latest Java HTTP Client API. The protocols reviewed include the Java implementation of a communication protocol based on TCP, UDP, and URLs.

Quiz

  1. Select all the correct statements:
    1. JDBC stands for Java Database Communication.
    2. The JDBC API includes the java.db package.
    3. The JDBC API comes with Java installation.
    4. The JDBC API includes the drivers for all major DBMSs.
  2. Select all the correct statements:
    1. A database table can be created using the CREATE statement.
    2. A database table can be changed using the UPDATE statement.
    3. A database table can be removed using the DELETE statement.
    4. Each database column can have an index.
  3. Select all the correct statements:
    1. To connect to a database, you can use the Connect class.
    2. Every database connection must be closed.
    3. The same database connection may be used for many operations.
    4. Database connections can be pooled.
  4. Select all the correct statements:
    1. A database connection can be closed automatically using the try-with-resources construct.
    2. A database connection can be closed using the finally block construct.
    3. A database connection can be closed using the catch block.
    4. A database connection can be closed without a try block.
  5. Select all the correct statements:
    1. The INSERT statement includes a table name.
    2. The INSERT statement includes column names.
    3. The INSERT statement includes values.
    4. The INSERT statement includes constraints.
  6. Select all the correct statements:
    1. The SELECT statement must include a table name.
    2. The SELECT statement must include a column name.
    3. The SELECT statement must include the WHERE clause.
    4. The SELECT statement may include the ORDER clause.
  7. Select all the correct statements:
    1. The UPDATE statement must include a table name.
    2. The UPDATE statement must include a column name.
    3. The UPDATE statement may include the WHERE clause.
    4. The UPDATE statement may include the ORDER clause.
  8. Select all the correct statements:
    1. The DELETE statement must include a table name.
    2. The DELETE statement must include a column name.
    3. The DELETE statement may include the WHERE clause.
    4. The DELETE statement may include the ORDER clause.
  9. Select all the correct statements about the execute() method of the Statement interface:
    1. It receives a SQL statement.
    2. It returns a ResultSet object.
    3. The Statement object may return data after execute() is called.
    4. The Statement object may return the number of affected records after execute() is called.
  10. Select all the correct statements about the executeQuery() method of the Statement interface:
    1. It receives a SQL statement.
    2. It returns a ResultSet object.
    3. The Statement object may return data after executeQuery() is called.
    4. The Statement object may return the number of affected records after executeQuery() is called.
  11. Select all the correct statements about the executeUpdate() method of the Statement interface:
    1. It receives a SQL statement.
    2. It returns a ResultSet object.
    3. The Statement object may return data after executeUpdate() is called.
    4. The Statement object returns the number of affected records after executeUpdate() is called.
  12. Select all the correct statements about the PreparedStatement interface:
    1. It extends Statement.
    2. An object of type PreparedStatement is created by the prepareStatement() method.
    3. It is always more efficient than Statement.
    4. It results in a template in the database being created only once.
  13. Select all the correct statements about the CallableStatement interface:
    1. It extends PreparedStatement.
    2. An object of type CallableStatement is created by the prepareCall() method.
    3. It is always more efficient than PreparedStatement.
    4. It results in a template in the database being created only once.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.135.197.201