This article has participated in the activity of “New person creation Ceremony”, and started the road of digging gold creation together.

The introduction

You dig friendly, this time to summarize the JDBC related knowledge. This paper mainly describes the principle of JDBC, interspersed with some database related knowledge, in addition to the SSM framework of POJO layer,DAO layer understanding and summary.

By reading this article, you will be able to quickly understand and master the theoretical knowledge of JDBC, and make a small foundation for the systematic learning framework in the future.

The origin of the JDBC

Early on, SUN tried to write an API that could connect to all databases, but found problems right from the start, with database servers from different vendors being vastly different. Later, SUN company discussed with database vendors, and finally, SUN was responsible for providing a set of database access specifications and protocol standards for connecting to the database, while each database vendor would follow SUN’s specifications to provide a set of API for accessing their own database servers. The specification provided by SUN is JDBC, and the apis provided by various vendors to access their databases are called drivers.

One thing to remember here is that JDBC is the interface, the JDBC driver implements the interface, and the database connection cannot be implemented without the driver.

The paper

Before going into the details, we should know what a complete database operation looks like. namely

  1. Connecting to a Database
  2. Operating on the database (CRUD)
  3. Closing a database connection

Connecting to a Database

Needed to connect to the databaseinformation:

  • Database URL JDBC: Database :// Server IP address

Address: port number/database name? Time zone Settings…

  • User name Of the database The default user name is root
  • Database password
  • The database driver can be searched and downloaded from this website. Note that the downloaded version must be the same as your own

In MySql, for example URL: JDBC: MySql: / / 127.0.0.1: port/dbname? . Note: 127.0.0.1: IP address of the database server localhost: indicates that the database server is local. Port: indicates the port number. The default is 3306 dbname: 8.0 the name of the Driver to access database: com. Mysql. Cj). The JDBC Driver 5. X version of the database Driver: com. Mysql.. JDBC Driver

The Connnection object is an object used in Java to represent a database connection

Method to connect to a database

Procedure: 1. Install the driver (loading the driver Class) class.forname (driver) 2. Access to the database Connection DriverManager. GetConnection (url, username, password) returns the Connection type

Operating database

Related Java objects and common methods

Statement object (not recommended because SQL injection may occur):

SQL statement object

Common methods:

  • Execute (SQL): Executes an SQL statement and returns whether the statement is successfully executed. The value is true on success and false on failure
  • ExecuteUpdate (SQL): Execute SQL statements (DML operations) and return the number of successes
  • ExecuteQuery (SQL): Executes an SQL statement (query) that returns a set of data (result set) queried from the database
  1. When database data is updated (add, modify, delete). Use executeUpdate()
  2. When performing queries, use executeQuery();

Use steps:

Connection =... ; / / get the Statement object Statement Statement = connection. The createStatement (); String SQL ="......." ; ExecuteUpdate (SQL)/executeQuery(SQL)... ;Copy the code

PreparedStatement object (recommended):

Precompiled SQL statement objects (to avoid SQL injection problems)

Common methods:

  1. SetString (parameter position, parameter content) setInt(parameter position, parameter content)…
  2. ExecuteUpdate () returns an integer that refers to the number of rows affected
  3. ExecuteQuery () returns a ResultSet object that holds the query results of the database response

The usage methods of 2 and 3 are similar to those in Statement, except that there are no SQL parameters

Use steps:

Connection =... ; String SQL ="........" ; / / get the statement object PreparedStatement PreparedStatement = connection. The prepareStatement (SQL); / / set parameters preparedStatement. SetString (... ,...). ; / / statement execution preparedStatement. ExecuteUpdate ()/executeQuery ()...Copy the code

The ResultSet object

The result set returned after a data query using executeUpdate() is the object that stores the query results. The result set not only has the function of storage, but also has the function of manipulating data and updating data.

The result set reads the data using getXXX(), such as getString(argument). The argument can be an integer representing the column number (starting at 1) or a column name that returns the value of the corresponding data type. Return null if XXX is an object, 0 if XXX is a numeric type, such as Float, and Boolean false.

XXX can represent the following types: Basic data types such as integer (int), Boolean (Boolean), Float (Double), etc. Special data types such as Date (java.sql.date), Time (java.sql.time), etc. Timestamp type (java.sql.Timestamp), etc.

  1. Basic ResultSet

The function of this ResultSet is to store the query results. It can only be read once and cannot scroll back and forth to read. The result set is created as follows:

Statement st = connection.CreateStatement 
ResultSet rs = st.excuteQuery(sql); 
Copy the code

If you get such a result set, you can only call its next() method and read the data one by one.

  1. Scrollable ResultSet

This type supports scrolling back and forth to retrieve records next (), previous(), back to the first row first(), absolute (int n), relative(int n), and relative(int n) of rows in the ResultSet to be accessed. To implement such a ResultSet, use the following methods when creating a Statement.

Statement st = connection. createStatement (int resultSetType, int resultSetConcurrency) 
ResultSet rs = st.executeQuery(sql) 
Copy the code

Where resultSetType sets the type of the ResultSet object to scrollable or unscrollable:

  • Resultset.type_forward_only Can only scroll forward
  • ResultSet.TYPE_SCROLL_INSENSITIVE
  • ResultSet.TYPE_SCROLL_SENSITIVE

The latter two methods can realize arbitrary forward and backward scrolling and support backforward, random, last, first and other operations on the result set. The difference between the two methods is that the former is insensitive to the modification of data in the database, while the latter is sensitive to the modification. (Session for the moment)

ResultSetConcurency sets whether the ResultSet object can be modified. The values are as follows:

  • ResultSet.CONCUR_READ_ONLY Parameter that is set to read-only.
  • ResultSet.CONCUR_UPDATABLE Set this parameter to a modifiable type.

Such as:

Statement st = conn.createStatement(Result.TYPE_SCROLL_INSENITIVE,ResultSet.CONCUR_READ_ONLY); ResultSet rs = st.excuteQuery(sqlStr);Copy the code

Queries executed with this Statement result in scrollable ResultSets.

POJO layer

I decided to cover POJOs in this article because I wanted to give you a broader perspective, beyond JDBC, on the entire backend development framework. My pen power is limited, please pass by the big guy give advice. If you have any questions, please discuss them in the comments section.

What is a POJO

Plain Ordinary Java Objects are simple Java objects, and the inherent meaning of POJOs is Java objects that do not inherit from any class, do not implement any interface, and have not been invaded by other frameworks.

In short, POJOs can be thought of as Java objects that map to tables in a database.

A POJO is a Persistent Object (PO). A record in the database can be treated as a PO object. Multiple records can be represented by a set of pos.

The meaning of the POJO

Enables developers to focus on business logic and off-frame unit testing. Because of its simplicity and flexibility, POJOs can be arbitrarily extended to cover multiple scenarios, allowing a model to span multiple layers.

I may not be able to appreciate the benefits of digger until now, but I will post some articles to help you understand.

POJO is different from PO

Pojos are created by new and collected by GC. However, PO objects are created by insert database and deleted by database DELETE, and the persistent object life cycle is closely related to the database. A persistent object can only exist in a Connection. After Connection is closed, the persistent object does not exist

How to write

POJO has been said, we return to JDBC, with the above paving, the specific operation should believe that you have the answer in mind.

Each table in the database corresponds to a Java Entity class (POJO), and the attributes of the entity class correspond to the fields of the table, so that the data in the database table can be temporarily stored in the entity-class objects.

In this way, you don’t have to go to the database every time to get the data you want, you can get it directly from the object

Dao layer

Data Access Object (Dao) is the Data access layer. Encapsulates some of the database operations, specific to a table, an entity to add, delete, change, search, mapping to a Java object (POJO).

Why the DAO layer

A large project must be layered, such as the MVC pattern. The purpose of this is to reduce the coupling of the layers, to make the responsibility boundaries of each layer clear, to facilitate the maintenance and extension of the subsequent code, and to improve the development efficiency of the entire team.

How to use

In practice, we can write an interface that handles the interaction between the program and the database, putting the specific operations in the implementation class of the interface. This interface can be called for data processing, regardless of the specific implementation of the interface class is which class, the structure is clear.

Three implementations

Here are three JDBC implementations for your reference.

1.0

The principles of JDBC and some object methods that need to be used have been finished, and the combination of them is our JDBC version 1.0.

2.0

Based on the 1.0 release, we made some improvements. You can place the parameter information for connecting to the database in the Properties configuration file as key=value pairs. At the same time, the static code block is introduced to initialize the parameters via Properties and file flow. This way, you can improve the utilization of your code by simply changing the corresponding parameters for each change. Note the properties file format:

  1. No Spaces allowed
  2. You are not allowed to enter until you finish writing

3.0

With version 3.0, we can go one step further. Third-party tools such as DBCP2 developed by Apache, C3P0 maintained by Hibernate working group, and Druid from Alibaba help us automatically manage database connections and releases. Note that when using third-party tools, strictly follow their specified formats. For example, with Druid, the properties file parameter name key is fixed

Benefits of connection pooling

  1. Database resources are reused
  2. Reduce the resource cost of establishing and releasing database connections, improve system response speed, and reduce I/O overhead
  3. Unified database management reduces JVM garbage and reduces database overloads

An advanced operation of a database

Transaction processing

Before learning about transaction processing, you need to understand the ACID properties of your database

  • Atomicity: A transaction can be viewed as an indivisible whole in which all operations occur or none occur
  • Consistency: Once a transaction completes, all data needs to be consistent
  • Isolation: When multiple users concurrently access a database, the transactions initiated by the database for each user cannot be disturbed by the operation data of other transactions. Multiple concurrent transactions must be isolated from each other.
  • Persistence: The operation of a transaction is eventually persisted to the database

As service scenarios become increasingly complex, a single operation cannot meet our requirements. Multiple operations or commands need to be executed together. This requires that all commands are successfully executed to mean the success of the transaction, and the failure of any command means the failure of the transaction (for example: transfer).

So how do you do that? Please read on

  1. Set transaction to commit connection.setautoCommit (false) before SQL statement execution; At this point, the data is not persisted using statements such as executeUpdate()
  2. Connection.mit (); If the transaction is abnormal, the data needs to be rolled back connection.rollback();
  3. After this operation is complete, set the transaction commit mode to automatic commit connection.setautocommit (true) to prevent subsequent operations.

Our predecessors have already written the method, so we just need to know how to use it.

Batch to Batch

When we need to perform a large number of DML operations, we can use batch processing. In this way, we can reduce the waste of resources, execute multiple SQL statements at a time, improve efficiency, and shorten the EXECUTION time of SQL statements

Don’t forget to flush the batch every time it finishes executing

Step \ statement Statement PreparedStatement
Add (do not add too much to prevent memory overflow) statement.addBatch( sql ) preparedStatement.addBatch()
Execute (after batch execution, int[] is returned) statement.executeBatch() preparedStatement.executeBatch()
empty statement.clearBatch() preparedStatement.clearBatch()

END

Hope you dig friends after reading this article, there is a harvest. If you have any questions, leave them in the comments section. If you think the article is good, give a praise bai, you move your finger is a great encouragement to me!