JDBC--database management system

1-JDBC Overview

Preface: Data Persistence

Persistence: Save data to a power-down storage device for later use use. In most cases, especially for enterprise-level applications, data persistence means saving the data in memory to the hard disk and "solidifying" it, and the persistence process is mostly accomplished through various relational databases.
The main application of persistence is to store data in memory in a relational database. Of course, it can also be stored in disk files and XML data files.

Data storage technology in Java

In Java, database access technology can be divided into the following categories:

JDBC directly accesses the database
JDO technology
Third-party O/R tools, such as Hibernate, ibatis, etc.

JDBC It is the cornerstone of Java's access to databases. JDO, Hibernate, etc. just better encapsulate JDBC.

JDBC Basics

JDBC (Java Database Connectivity) is a public interface for universal SQL database access and operation that is independent of a specific database management system ( A set of APIs) that defines standard Java class libraries (java.sql, javax.sql) used to access databases. Using this class library, you can easily access database resources in a standard way. JDBC provides access to different databases. A unified approach shields some details from developers.
The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides a JDBC driver, so that programmers do not need to have too much knowledge about the characteristics of a specific database system. This greatly simplifies and speeds up the development process.

JDBC Architecture

The JDBC interface (API) includes two levels:

Oriented Application API: Java API, abstract interface, used by application developers (connect to the database, execute SQL statements, and obtain results).
Database-oriented API: Java Driver API, for developers to develop database drivers.

#JDBC is a set of interfaces provided by Sun for database operations. Java programmers only need to program for this set of interfaces. Different database vendors need to provide different implementations for this set of interfaces. A collection of different implementations are drivers for different databases. ————Interface-oriented programming

JDBC driver classification.

JDBC driver: A class library of JDBC implementation classes produced by various database vendors according to JDBC specifications
There are four types of JDBC drivers in total:

The first category: JDBC-ODBC bridge.
The second category: some local APIs and some Java drivers.
The third category: JDBC network pure Java driver.
Category 4: Pure Java driver for local protocols.

The third and fourth categories are pure Java drivers. Therefore, for Java developers, they are in terms of performance and portability. It has advantages in terms of performance and function.

The first category: ODBC

Early access to the database was based on calling the proprietary API provided by the database manufacturer. In order to provide a unified access method under the Windows platform, Microsoft launched ODBC (Open Database Connectivity) and provided an ODBC API. Users only need to call the ODBC API in the program, and the ODBC driver will convert the call. It becomes a call request to a specific database. An ODBC-based application does not rely on any DBMS (database manager system) for database operations and does not deal directly with the DBMS. All database operations are completed by the ODBC driver of the corresponding DBMS. In other words, whether it is FoxPro, Access , MYSQL or Oracle database can be accessed by ODBC API. It can be seen that the biggest advantage of ODBC is that it can handle all databases in a unified way.
JDBC-ODBC Bridge
The JDBC-ODBC bridge itself is also a driver. Using this driver, you can use JDBC-API to access through ODBC database. This mechanism actually converts standard JDBC calls into corresponding ODBC calls, and accesses the database through ODBC. Because it requires multiple layers of calls, it is inefficient to use the JDBC-ODBC bridge to access the database. In the JDK, JDBC-ODBC is provided. The implementation class of ODBC bridge (sun.jdbc.odbc.JdbcOdbcDriver).

The second category: Partial local API and partial Java driver

This type of JDBC driver is written in Java, and it calls the local API provided by the database manufacturer through this type The JDBC driver's access to the database reduces the number of ODBC calls and improves the efficiency of database access. In this way, a local JDBC driver and a specific vendor's local API need to be installed on the customer's machine.

The third category: JDBC network pure Java driver

This driver uses the middleware application server to access the database. The application server acts as a gateway to multiple databases, through which clients can connect to different database servers.
The application server usually has its own network protocol. The Java user program sends JDBC calls to the application server through the JDBC driver. The application server uses the local program driver to access the database to complete the request.

Category 4: Pure Java driver for local protocol

Most database vendors already support allowing client programs to communicate directly with Network protocol for database communication.
This type of driver is written entirely in Java. Through the Socket connection established with the database, it uses the manufacturer's specific network protocol to convert JDBC calls into directly connected network calls to the JDBC API.

#JDBC API is a series of interfaces that enable applications to connect to databases, execute SQL statements, and get returned results.

2-Get database connection

Driver interface

java.sql. Driver interface is the interface that all JDBC drivers need to implement. This interface is provided to database vendors. Different database vendors provide different implementations. There is no need to directly access the classes that implement the Driver interface in the program. Instead, the driver manager class (java.sql.DriverManager) calls these. Driver implementation.

Oracle driver: oracle.jdbc.driver.OracleDriver
mySql driver: com.mysql.jdbc.Driver

Loading and registering the JDBC driver

Method 1: To load the JDBC driver, you need to call the static method forName() of the Class class and pass it the JDBC driver to be loaded. Class name

Class.forName(“com.mysql.jdbc.Driver”);

Method 2: DriverManager class Is the driver manager class, responsible for managing the driver

DriverManager.registerDriver(com.mysql.jdbc.Driver);

Usually there is no need to explicitly call the registerDriver() method of the DriverManager class to register an instance of the driver class, because the driver classes of the Driver interface all contain static code blocks. In this static code block, the DriverManager.registerDriver() method will be called. to register an instance of itself.

Establish a connection (Connection)

You can call the getConnection() method of the DriverManager class to establish a connection to the database.
User, password can be told to the database using "property name = property value";
JDBC URL is used to identify a registered driver, driver manager Select the correct driver via this URL to establish a connection to the database.
The JDBC URL standard consists of three parts, separated by colons.
jdbc:Subprotocol:Subname
Protocol: The protocol in the JDBC URL is always jdbc.
Subprotocol: Subprotocol is used to identify a database driver.
Subname: A way to identify the database. The subname can change according to different subprotocols. The purpose of using the subname is to provide sufficient information to locate the database. Contains the host name (corresponding to the IP address of the server), port number, and database name.

JDBC URLs of several commonly used databases

##jdbc:mysql://localhost:3306/test
Protocol sub-protocol Sub-name
For Oracle database connection, use the following form:
jdbc:oracle:thin:@localhost:1521:atguigu
For SQLServer database connection, use the following form:
jdbc:microsoft:sqlserver//localhost:1433; DatabaseName=sid
For MYSQL database connection, use the following form ：
jdbc:mysql://localhost:3306/atguigu

Statement

Once the connection object Connection is obtained, It is not possible to execute SQL yet. You must obtain the execution object Statement from the Connection object to execute SQL.

Connection connection = getConnection();
Statement state = connection.createStatement();
int n = state.executeUpdate(“insert,update,delete…”);

Where n is the number of records that will be affected by adding, deleting, or modifying the table. If a query is executed, a ResultSet result set object is returned.

SQL injection attack

SQL injection is the use of certain systems that do not fully check the data entered by the user. Inject illegal SQL statement segments or commands (such as: SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1'), thereby using the system's SQL engine to complete malicious behavior For Java, to prevent SQL injection, just use PreparedStatement (extended from Statement) Just replace Statement .

3-Use PreparedStatement

data type conversion table

#You can call the Connection object The preparedStatement() method obtains the PreparedStatement object.
The PreparedStatement interface is a sub-interface of Statement, which represents a precompiled SQL statement.
The parameters in the SQL statement represented by the PreparedStatement object are represented by question marks (?). Call the setXxx() method of the PreparedStatement object to set these parameters. The setXxx() method has two parameters, the first One parameter is the index (starting from 1) of the parameter in the SQL statement to be set, and the second is the value of the parameter in the SQL statement to be set.
PreparedStatement vs Statement
##Code readability and maintainability.PreparedStatement can maximize performance:
DBServer will provide performance optimization for prepared statements. Because precompiled statements may be called repeatedly, the execution code of the statement after being compiled by the DBServer compiler is cached. Then the next time the statement is called, as long as it is the same precompiled statement, there is no need to compile it, as long as the parameters are passed in directly. Compiled statements will be executed in the execution code.
In the statement statement, even if it is the same operation but the data content is different, the entire statement itself cannot match, and there is no meaning of caching the statement. The fact is that no database will cache the compiled execution code of ordinary statements. In this way, the incoming statement must be compiled once every time it is executed. (Syntax checking, semantic checking, translation into binary commands, caching).
PreparedStatement
Can prevent SQL injection Steps to connect to the database and operate the table:

Register the driver (only do this once)

Establish a connection (Connection)

Create a statement to execute SQL (PreparedStatement)

Execute the statement

Manage the execution result (ResultSet)

Release resources

Connection conn = null;
		PreparedStatement st=null;
		ResultSet rs = null;
		try {       
			//获得Connection      
			//创建PreparedStatement      
			//处理查询结果ResultSet
		}catch(Exception e){
		     	e.printStackTrance();
		} finally {     
			//释放资源ResultSet,        
			// PreparedStatement ,    
			//Connection		

释放ResultSet, PreparedStatement ,Connection。
数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

4-使用ResultSet、ResultSetMetaData操作数据表：SELECT

ORM:Object Relation Mapping
表 与 类 对应
表的一行数据 与 类的一个对象对应
表的一列 与类的一个属性对应

ResultSet

通过调用 PreparedStatement 对象的 excuteQuery() 方法创建该对象。
ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集，ResultSet 接口由数据库厂商实现。
ResultSet 对象维护了一个指向当前数据行的游标，初始的时候，游标在第一行之前，可以通过 ResultSet 对象的 next() 方法移动到下一行。
ResultSet 接口的常用方法：

boolean next()
getString()
…

处理执行结果(ResultSet)
读取（查询）对应SQL的SELECT，返回查询结果

st = conn.createStatement();
String sql = "select id, name, age,birth from user";
rs = st.executeQuery(sql);
while (rs.next()) {		
	System.out.print(rs.getInt("id") + " \t ");		
	System.out.print(rs.getString("name") + " \t");		
	System.out.print(rs.getInt("age") + " \t");		
	System.out.print(rs.getDate(“birth") + " \t ");		
	System.out.println();}
｝

关于Result的说明

1. 查询需要调用 Statement 的 executeQuery(sql) 方法，查询结果是一个 ResultSet 对象
2. 关于 ResultSet：代表结果集
ResultSet: 结果集. 封装了使用 JDBC 进行查询的结果. 调用 Statement 对象的 executeQuery(sql) 可以得到结果集.
ResultSet 返回的实际上就是一张数据表. 有一个指针指向数据表的第一条记录的前面.
3.可以调用 next() 方法检测下一行是否有效. 若有效该方法返回 true, 且指针下移. 相当于Iterator 对象的 hasNext() 和 next() 方法的结合体
4.当指针指向一行时, 可以通过调用 getXxx(int index) 或 getXxx(int columnName) 获取每一列的值. 
例如: getInt(1), getString("name")
5.ResultSet 当然也需要进行关闭. 

MySQL BLOB 类型 

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。
MySQL的四种BLOB类型(除了在存储的最大信息量上不同外，他们是等同的)。

实际使用中根据需要存入的数据大小定义不同的BLOB类型。
需要注意的是：如果存储的文件过大，数据库的性能会下降。

ResultSetMetaData 类

可用于获取关于 ResultSet 对象中列的类型和属性信息的对象。

ResultSetMetaData meta = rs.getMetaData();
getColumnName(int column)：获取指定列的名称
getColumnLabel(int column)：获取指定列的别名
getColumnCount()：返回当前 ResultSet 对象中的列数。 
getColumnTypeName(int column)：检索指定列的数据库特定的类型名称。 
getColumnDisplaySize(int column)：指示指定列的最大标准宽度，以字符为单位。 
isNullable(int column)：指示指定列中的值是否可以为 null。  
isAutoIncrement(int column)：指示是否自动为指定列进行编号，这样这些列仍然是只读的。 

JDBC API 小结

java.sql.DriverManager is used to load the driver and obtain the database connection.
java.sql.Connection completes the connection to a specified database.
java.sql.Statement serves as a container for SQL execution statements in a given connection. It contains two important subtypes.
Java.sql.PreparedSatement is used to execute precompiled sql statements.
Java.sql.CallableStatement is used to execute calls to stored procedures in the database.
java.sql.ResultSet The way to obtain the result for a given statement.

Two Thoughts

The idea of ​​interface-oriented programming;

ORM idea: SQL needs to be written by combining column names and table attribute names, and pay attention to aliases.

Two technologies

JDBC metadata: ResultSetMetaData;

PropertyUtils: Create an object through Class.newInstance(), and assemble the queried column value to the created object through this class.

5-Batch processing

Batch processing of JDBC statements Improve processing speed.
When you need to insert or update records in batches. Java's batch update mechanism can be used, which allows multiple statements to be submitted to the database for batch processing at one time. It is usually more efficient than individual submission processing
JDBC batch processing statements include the following two methods:

addBatch(String): Add SQL statements or parameters that require batch processing;
executeBatch(): execute batch processing statements; clearBatch(): clear cached data;

Usually we encounter two situations of batch execution of SQL statements:

Batch processing of multiple SQL statements;
Batch parameter transfer of one SQL statement;

6-Database Connection Pool

The necessity of JDBC database connection pool
##When using and developing database-based web programs, the traditional model basically follows the following steps:

Establish a database connection in the main program (such as servlet, beans)
Perform sql operations
Disconnect the database connection

This model of development has problems:
Ordinary JDBC database connections are obtained using DriverManager. Every time you establish a connection to the database, you must load the Connection into memory and then verify the user name and password (it takes 0.05s to 1s). When a database connection is needed, request one from the database, and then disconnect after the execution is completed. This approach will consume a lot of resources and time. The database connection resources are not well reused. If there are hundreds or even thousands of people online at the same time, frequent database connection operations will occupy a lot of system resources, and even cause the server to crash. Every database connection must be disconnected after use. Otherwise, if the program fails to close due to an exception, it will cause a memory leak in the database system and eventually cause the database to be restarted. This kind of development cannot control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also cause memory leaks and server crashes.
Database connection pool (connection pool)
To solve the database connection problems in traditional development, database connection pool technology can be used.
The basic idea of ​​the database connection pool is to establish a "buffer pool" for database connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, you only need to take one out of the "buffer pool" and put it back after use.
The database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re-establishing one.
The database connection pool will create a certain number of database connections and put them into the connection pool during initialization. The number of these database connections is set by the minimum number of database connections. Regardless of whether these database connections are used or not, the connection pool will always be guaranteed to have at least this many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can occupy. When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue.

How database connection pool works

##Advantages of database connection pool technology

Resource reuse
Because the database connection can be reused, a large amount of performance overhead caused by frequent creation and release of connections is avoided. On the basis of reducing system consumption, on the other hand, it also increases the stability of the system operating environment.
Faster system response speed
# During the initialization process of the database connection pool, several databases have often been created. The connection is placed in the connection pool for later use. At this point, the initialization of the connection has been completed. For business request processing, existing available connections are directly used to avoid the time overhead of database connection initialization and release process, thus reducing the system response time.
New resource allocation method
#For systems where multiple applications share the same database, you can Through the configuration of the database connection pool, the maximum number of available database connections for a certain application is limited to prevent a certain application from monopolizing all database resources.
Unified connection management to avoid database connection leakage
In a relatively complete database connection pool implementation, you can According to the pre-occupancy timeout setting, the occupied connection is forcibly recycled, thus avoiding resource leakage that may occur in regular database connection operations. Two open source database connection pools
:
JDBC’s database connection pool is represented by javax.sql.DataSource. DataSource is just an interface, which is usually used by the server. (Weblogic, WebSphere, Tomcat) provides implementation, and some open source organizations provide implementation:

DBCP database connection Pool

C3P0 database connection pool

DataSource is usually called a data source. It contains two parts: connection pool and connection pool management. It is customary to call DataSource a connection pool.
DataSource is used to replace DriverManager to obtain Connection, which is fast and can greatly improve the database access speed.
DBCP data source
DBCP is an open source connection pool implementation under the Apache Software Foundation. The connection pool relies on another open source system under the organization: Common-pool . If you want to use this connection pool implementation, you should add the following two jar files to the system:

Commons-dbcp.jar: Connection pool implementation

Commons-pool.jar: Dependent library for connection pool implementation

Tomcat’s connection pool is implemented using this connection pool . The database connection pool can be integrated with the application server or used independently by the application.
DBCP data source usage example
Data sources are different from database connections. There is no need to create multiple data sources. It is a factory that generates database connections, so the entire application only needs Just one data source is enough.
When the database access ends, the program still closes the database connection as before: conn.close(); But the above code does not close the physical connection of the database, it only releases and returns the database connection A database connection pool is given.

7-Database Transaction

Transaction: a set of logical operation units that transform data from one state to another state.
Transaction processing (transaction operation): Ensure that all transactions are executed as a unit of work. Even if a failure occurs, this execution method cannot be changed. When multiple operations are performed in a transaction, either all transactions are committed and the modifications are permanently saved; or the database management system abandons all modifications and the entire transaction is rolled back to initial state.
To ensure the consistency of data in the database, data manipulation should be done in discrete logical units: when it is all completed, the consistency of the data can be maintained, and when the operations in this unit If part of the operation fails, the entire transaction should be regarded as an error, and all operations from the starting point should be rolled back to the starting state.

ACID (acid) attribute of transaction
1. Atomicity
Atomicity means that a transaction is an indivisible unit of work, and operations in a transaction either all occur or none occur.
2. Consistency
Transactions must change the database from one consistency state to another consistency state.
3. Isolation
The isolation of a transaction means that the execution of a transaction cannot be interfered with by other transactions, that is, the operations and uses within a transaction The data is isolated from other concurrent transactions, and transactions executed concurrently cannot interfere with each other.
4. Durability

Durability means that once a transaction is submitted, its changes to the data in the database are permanent. Subsequent operations and database failures should not have any impact on it
JDBC transaction processing
When a When the connection object is created, the transaction is automatically committed by default: every time a SQL statement is executed, if the execution is successful, it will be automatically submitted to the database and cannot be rolled back.
In order to execute multiple SQL statements as one transaction:

Call setAutoCommit(false) of the Connection object; To cancel the automatic submission of transactions

After all SQL statements are successfully executed, call the commit(); method to commit the transaction

When an exception occurs When, call the rollback(); method to roll back the transaction

If the Connection is not closed at this time, you need to restore its automatic submission state

Data status after submission

The data changes have been saved to the database.

The data before the change has been lost.

All users can see the results.

The lock is released and other users can operate the involved data.

8-DBUtils tool class

A collection of commonly used JDBC classes and methods for operating databases Together, it is DBUtils.

BeanHandler: Convert the result set to a JavaBean

BeanBeanListHandler: Convert the result set Convert to a collection of Beans

MapHandler: Convert the result set to a Map

MapMapListHandler: Convert the result set to a List of Map

ScalarHandler: Convert the result set to a Map Returns a type of data, which usually refers to String or other 8 basic data types.

The above is the JDBC--database management system Content, please pay attention to the PHP Chinese website (www.php.cn) for more related content!