The database in the layered architecture

Database in layered architecture.

As you can see in the image above, to connect to a database managment system from your code you need a driver, which is a piece of software that implements the JDBC API and translates the calls to the database specific protocol. The driver is provided by the database vendor, or by a third party. You need to include the driver in your project as a dependency, so that it is available at runtime.

When you are developing in a team, you will typically have different environments for development, testing and production. Each environment will have its own database instance, with its own credentials. You need to configure your application to use the correct database for each environment. In the development environment, each developer would like to use his own database instance, so that they can work independently and not interfere with each other. In the testing environment, database contents should be predictable, so that tests can be reliable. In the production environment, the database should be secure and performant.

To avoid situations in which a database functionality does work for one developer ("Works-for-me!"), but not for another, it is good practice to work with a (Postgres) Docker container to make sure that all developers use the same database version and configuration.

Sometimes it is handy to directly connect to your database using the PostgreSQL Command Line Client. You can do this on the terminal of your host machine (or on the terminal of your Docker container) by simply entering the command "psql". By default, psql will try to connect to a database with the same name as your username, using the same username and no password. If you have set up your database differently, you can specify the connection parameters in the command line. For example, if you want to connect with a specific user to a specific database, e.g. user 'Alex' with database 'studentDB', you can use the following command: psql -U Alex -d studentDB. You will be prompted to enter the password for user 'Alex'. If you run psql on the terminal of your docker container, it will by default use root as user, which will not work since that is not a default user in postgres. Therefore, in that case you should write 'psql -U postgres' to login with the postgres user (remember, this is a very powerfull user, so be careful with it!).

Within the command line client, you can easily get insights into your database, for example by listing all tables with the command "\dt", or by describing a table with the command "\d tablename". with "\q" you can leave the client application again. Valuable is also "\h" to get an overview of all possible commands, or "\h DELETE" to learn everything about the syntax of the DELETE command, or "\h <command>" more general to get help.

So far about the command line client. Dont forget the "Never evers" that we discussed in class:

DataBase connection credentials and Java Properties

Some things do NOT belong in source code. In particular do not put credentials of any kind inside files that are committed to a version control system, such as source code. Make sure you configure your version control system such that such files are excluded from commits.

Instead put credentials in separate files, that are easily understood by both a human that uses it to configure access to a resource, and also by the programming language.
In java the tradition is to use so called properties files, which, also traditionally, have a file extension .properties. It also helps to give such files well known names, so the program can refer to them by that name.

For the demonstrations in this part we will use the following properties file.

# You can add comments here.

# hosts and ports are plural
# to be able to connect to multiple hosts and ports

# Make sure that you don't push this file to a repository, as it contains sensitive information such as database credentials.

db.dbhost=localhost
db.port=5432
db.username=aisuser
db.dbname=aisdb
db.password=aispassword
db.schema=public

public class DBProvider {

    static Map<String, DataSource> cache = new HashMap<>();

    static DataSource getDataSource(final String sourceName) {

        return cache.computeIfAbsent(sourceName,
                (s) -> {
                    Properties props = getProperties("db.properties");

                    PGSimpleDataSource dataSource = new PGSimpleDataSource();

                    String prefix = sourceName + ".";

                    String[] serverNames = {props.getProperty(prefix + "dbhost")};
                    dataSource.setServerNames(serverNames);

                    int[] portNumbers = {Integer.parseInt(props.getProperty(prefix + "port"))};
                    dataSource.setPortNumbers(portNumbers);

                    dataSource.setUser(props.getProperty(prefix + "username"));
                    dataSource.setDatabaseName(props.getProperty(prefix + "dbname"));
                    dataSource.setPassword(props.getProperty(prefix + "password"));
                    dataSource.setCurrentSchema(props.getProperty(prefix + "schema"));

                    return dataSource;
                }
        );
    }

    static Properties getProperties(String propertiesFileName){

        // Usage of resource file is preferred way. No issues with working dir.
        // Uses the default location of resources (in src/main/java/resources dir)

        Properties properties = new Properties();
        try (InputStream dbProperties = DBProvider.class.getClassLoader().getResourceAsStream(propertiesFileName);) {
            properties.load(dbProperties);
        } catch (IOException ex) {
            Logger.getLogger(DBProvider.class.getName()).log(Level.SEVERE, null, ex);
        }
        return properties;
    }
}

Using a Data source

There are some traditional ways to obtain a database connection. We use a DataSource, which itself can be seen as a resource. The data source can then be used to obtain a connection. In the example you see a class that needs a DataSource that is provided at construction time of the class, so it is available when the instance is created. A connection is AutoClosable so candidate for try-with-resources.

package simplejdbc;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;

/**
 *
 * @author hom
 */
public class DataSourceDemo {

    final DataSource datasource; 1

    public DataSourceDemo( DataSource datasource ) { 2
        this.datasource = datasource;
    }

    void demo() throws SQLException {  3
        String query
            = """
            select s.name as state
              ,p.name as president
              ,s.year_entered
            from president p
            join state s on(p.state_id_born=s.id)
                where s.name like 'N%'
            """;
            doQuery( query, System.out );
    }
}

The doQuery(…​) method uses the supplied connection to create a statement which is then executed to produce a ResultSet. You will see some similarities in what you have seen in project 1, using php PDO.

void doQuery( String query,
                PrintStream out ) throws SQLException {
    try ( Connection con = datasource.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery( query ) ) {

        new ResultSetPrinter( rs ).printTable( out );

    }
}

The ResultSetPrinter tries to make a nice looking table of the result of the query. You can imagine that this is a bit of code, but that is not relevant for this demo of JDBC.

ResultSet

For all queries that return a tabular result, the first JDBC class you will use is the ResultSet. The ResultSet also provides so called Meta Information that describes the types of the values in the columns, the number of columns, display size etc.
This can be used to:

Anatomy of a prepared statement

In the example earlier, the sql text used to create the statement is constant, because it needs no user input. If a statement does, you should always use a PreparedStatement.

In the JDBC standard you fill in the parameters from the user in multiple ways, but the simplest is to just use question marks (?) as placeholders and specify which columns and column values you want to insert or update, or you want to use in your select or delete query.

insert into students (student_id , lastname, firstname, dob, gender) 1
            values   (?        ,          ?,         ?,   ?,      ?) 2
returning *                                                          3

Now assume the SQL text contains the above. Also assume that we have an array of student data, simply as an array of objects.

 Object[] studentData = {123513, "Klaassen", "Jan", "1993-05-12" , "M"}; 1

Then creating the prepared statement and filling it with values is a simple loop:

try (
    Connection con = datasource.getConnection();
    PreparedStatement stmt = con.prepareStatement( query ); ) {

    int count = 0;
    for ( Object param : studentData ) {
        stmt.setObject( ++count, param ); 1
    }
    return stmt.executeUpdate();
}

You see that this is quite simple and other than what is specified in the query, there is no extra need for data conversion or named column use.

This approach can be used to make database access even simpler, so you only have to provide the data in an array and the rest can be packed into utility methods.

The holy grail is to find a way to do all kind of queries against tables, and the only thing you need to know is the table name and what entities as Java objects can be expected to be read from or written to the table. However, start simple first! At some point you’ll find duplicated code and you’ll find a way to optimize your code. Typically, you’ll use Generics, Lambda’s and streams and a bit of reflection at some point. We think it’s good to first face the issue of code that is difficult to maintain and afterwards find a solution for that, instead of providing you with a very generic and maybe complex solution without knowing which underlying problems it solves. //'