Java uses JDBC to build a simple data access layer example

The purpose of this tutorial is to use a separate layer written in Java to access tables in the database, which is commonly referred to as the data access layer (DAL)

The biggest advantage of using DAL is that it simplifies the database access operation by directly using some methods like insert () and find (), rather than always making links first and then executing some queries.

This layer handles all database related calls and queries internally.

Create database

We want to create a simple table for users, which we can use these fields to create

id int name varchar(200) password varchar(200) age int

Data transmission object

This layer should contain a simple class called data transfer object (dto). This class is only a simple mapping corresponding to the table in the database, and each column in the table corresponds to a member variable of the class.

Our goal is to use simple Java objects instead of processing SQL statements and other database related commands to add, delete, modify and query the database.

If we want to map the table to Java code, we just need to create a class (bean) containing the same fields

In order to better encapsulate, in addition to the constructor, we should declare all field variables private and create accessors (getters and setters), one of which is the default constructor.

In order to map fields correctly, we should consider null values in the database. For the original default value of Java, such as int type, the default value is 0, so we should provide a new data type that can accommodate null values. We can replace int by using special types -- encapsulation classes, such as integer.

Finally, our class should look like this:

A good practice is to provide a default empty constructor, a full constructor, and a full constructor without an ID parameter.

Connect to database

We can use an intermediate class to connect to the database. In this class, we will provide database connection parameters such as database JDBC, URL, user name and password, and define these variables as final (it will be better to obtain these data from properties or XML configuration files)

Provide a method to return a connection object, or return a null when the connection fails, or throw a runtime exception.

We can also include a main method in the class to test the connection. The complete class looks like this:

Data access object

The Dao layer can perform CRUD operations. It can add, delete, modify and check our table.

Our Dao layer interface should look like this:

Find users

Users can query through any unique field such as ID, name or email. In this example, we use the ID to find the user. The first step is to create a connection through the connector class, and then execute the select statement to obtain the user with ID 7. We can use this statement to query the user:

SELECT * FROM user WHERE id=7

Here, we make a dynamic statement to get the ID from the parameter.

By executing this query, you get a result set in which users or null are saved. We can check whether there is a value through the next () method of resultset. If it returns true, we will continue to use data getters to get user data from the resultset. When we encapsulate all the data into user, we return it. If there is no user with this ID or any other exception occurs (such as invalid SQL statement), this method will return null.

It will be more convenient to use a separate method to extract data from the result set, because we will call it in many methods.

This new method will throw sqlexception and should be private in order to restrict its use only inside the class:

The above method should be modified to a new method:

Login method

The login operation is similar. We want to provide user and password substitution IDS, which will not affect the parameter list and query statements. If the user name and password are correct, this method will return a valid user, otherwise it will be null. Because there are many parameters, it is more useful to use Preparedstatement.

Method to query all users

This method will return all users, so we should store them in an array like container and return them. But because we don't know how many records there are. It would be better to use collections such as set or list:

Insertion method

The insert method takes the user as a parameter and uses the Preparedstatement object to execute the SQL UPDATE statement. The executeupdate method returns the number of rows affected. If we add a single line, it means that the method should return 1. If so, we return true. Otherwise, we return false

Update method

The update method is similar to the insert method. The only change is the SQL statement

Delete method

The method of deletion is to use a simple query like

DELETE FROM user WHERE ID = 7

Sending the query with the ID parameter will delete this record. If the deletion is successful, 1 will be returned

Thank you for reading, hope to help you, thank you for your support to this site!