W7 Java Database Access

A model in any form is a simplification of reality. Model is the art of leaving out details that are not relevant. Not relevant for the purpose of the model. Modeling in this sense also implies that there can be more models of the same reality. Each of these models can then highlight different aspects of interest for what you want to communicate. You want to express these aspects to communicate a meaning, relation, dependency, composition etcetera. A model is always about abstraction because it never IS the real thing but only describe some of it’s aspects.

Models can have different form. Formulas in math, screenplay or scripts for a movie, descriptive text, and diagrams. In UML we have many model types. The trick is to choose the correct model type for the aspects you want to discover. Drawing can be done with a pencil and paper is willing, so you can easily become confused about syntax and semantics of the modeling language, in particular on what to use when.

A natural order of modeling, diagram wise, is something like this:

Modeling serves to highlight the important aspects. Choose the proper kind of diagram and try to closely stick to the syntax and semantics of the diagram and its purpose, so it helps the understanding of the details you DO want to communicate.

Modeling is always about leaving out details. Making things simpler.
Do not add details to a diagram that make no sense.

To paraphrase someone famous Everything should be made as simple as possible, but no simpler.

The above implies that the situation is not black or white, but some shade of gray.
That grayness is the reason of the confusion, because there is not one answer that fits in all situations.
It also means that you do not have to draw all diagrams all of the time, but only the diagrams that are needed. The question of course is: How do you know that it is needed? The answer is: as soon as you want to discover or explain something. Discovering is explaining to yourself. In a course where you need to learn the syntax and semantics of a language you must of course practice it and use the "words" (icons, lines) to form proper sentences (models, diagrams).

A model is a kind of plan and help to keep oversight, both overall and in the details, where required.

Let me explain:

First you should consider the purpose of the diagram.

Then

Let us use metaphores. Sausage and Sausage Machines.

Transformation of the domain model into the system class diagram sounds reasonable, but is in fact a fallacy._
It would be as if you could turn sausage into a sausage machine. I’m pretty sure your Butcher will tell you different when you would propose such a solution.

Sometimes it helps to go from the meta level to metaphors, to explain things, because \(\text{meta} = \text{abstract}^2\).
Do so if you have to explain something, but once the abstraction has been understood by the reader, you can stop simplifying.

It is easier to model sausages than dogs, because the former sit still.

That concludes this intermezzo on currywurst.

Pieter van den Hombergh, March 2021.

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 see that the properties file supports two environments, development and production.

There are some traditional ways to obtain a database connection. We use a DataSource, which itself can be seen as a resource, similar to a service in the architecture diagram of the testable design. 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.

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.

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.

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:

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.

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

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

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.

We combine this with the ideas presented in part 3 about testable design, part 4, generics, and part 6, reflection. We are quite sure that the programming style from week 5, with lambda expressions and streams will also come handy.

We actually want to focus on testing the business code, with that business code to be oblivious of the actual persistence layer or service it uses to get the data, and avoid to write a lot of boiler plate code. As a replacement for the later we will introduce the DAO concept.

Similarly you can also get the information of the check constraints with another, a bit more involved query.

which, because only one column in this schema actually declares a check constraint, results in:

You can use the check constraints in your business code too, but actually deferring those checks as final checks to the database is just fine. The database layer with throw an appropriate exception when a value is not to the liking of a constraint.

You may want to use the check information in the user interface layer, to warn the user that a transaction will not succeed with a illegal or missing value. Parsing the check clause is of course a bit complex, but with a regex you can do a lot, and also know that most check constraint are relatively simple.

We will now take a look at how the update method is implemented in the PGDAO.

E update( E t );, We get an object E and should update the correct row in the database, based on the primary key.

The SQL statement for that is basically update tablename set (col1, col2) = (val1, val2) where idcolumn=?. But we want more control, because we want to get column values in the order of the entity so we can create an entity instance of it using the mapper. We can ask the same mapper for the field order.

So in terms of Student: update students set (firstname,…​ active,), with all defined field names in between.

The DAO code, from top (near the user of the method) to the bottom (implementation details).

So what does the update helper method do?

We will have a look at the updateQueryText method in the exercise of this week, so for now let us focus on the doUpdate.

Not lets have a quick look at how to convert a record from the ResultSet to an instance of the entity type.

Now we know how the PGDAO update works, except for the updateQueryText. This part will be discussed in the following exercise.

Often the interaction with the database involves the update of or insert into more then one record in one or more tables. Each of these operations may cause a constraint violation, meaning that the operation is not accepted. This will cause an exception.

When you have such a scenario, we call that a transaction. A transaction in databases means: It either completes successfully or not at all.

The setup typically is

Objects is the approach you use when the design in the programming language is leading. You can use all the features of object orientation, and may have to bend the OO-rules a bit to fit stuff into the database. As an example for the less than perfect match: There is no real (usable) inheritance in the database, so the best you can do is join tables to go from person to teacher-attributes to teacher. The teacher is then a view. (How exactly is left as an exercise). The example below shows some design and implementation details for Students.

You define your data in tables and views first and use 'reflection' on it’s metadate to derive your entity types. Generating entities and entity mappers, which you still need, can be very easy, certainly with either project lombok, or the modern java record like in Java 14+.

At the time of writing, one must be a bit carefull. Not everything works with Java 16. It is particularly annoying that the maven surefire plugin refuses to work with java 16 byte-codes. That should not be an excuse for dropping tests.

Rule one
In all cases, keep the objects you transfer stupid. Apply the KISS principle.

In all cases, you win, because when you have one definition you can transform it into the other. That works.

In the picture above you can see that you can go to quite some lengths to keep secret data private.
For instance with the postgresql roles, rules, and row level security (\(R^3\)), you could design a system in which no unneeded data is exposed to someone who does not need it, and you could expose the secret data to its rightful owner like the real person, that is identified by the personid. But that is a red tape area for other users.

It takes some work to get it done, but you see that can build to grade secure systems, only by cooking with water and sufficient expertise.

For those that do not stop at a TL;DR signs: An interesting read might be on PostgreSQL row level security

Study the JDBC Introduction.