preface

Hello, ladies and gentlemen, little tu started learning JDBC today, so I'd like to send you a "fresh" learning blog

As we all know, Java application and database are two different systems, so how do we combine them? At this time, our "middleman" - JDBC, through this blog, we can let readers know how to realize the CRUD operation of JDBC on the database.

If you think the blogger's writing is good ~ give the blogger a praise!

JDBC overview

 

Data storage technology in Java

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

• JDBC direct access to database
• JDO (Java Data Object) technology
• Third party O/R tools, such as Hibernate, Mybatis, etc

2. JDBC is the cornerstone of java accessing database. JDO, Hibernate and MyBatis just better encapsulate JDBC.

 

JDBC introduction

JDBC(Java Database Connectivity) is a common interface (a set of API s) independent of a specific database management system and general SQL database access and operation. It defines the standard Java class libraries used to access the database (java.sql,javax.sql). These class libraries can be used to access database resources in a standard and convenient way.

• JDBC provides developers with a unified way to access the database without knowing too many details

• The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides JDBC drivers, so that programmers do not need to know too much about the characteristics of a specific database system, which greatly simplifies and speeds up the development process.

• If there is no JDBC, the Java program accesses the database as follows:

  

• With JDBC, the Java program accesses the database as follows:

 

JDBC architecture

JDBC interface (API) includes two levels:

• Application oriented API: Java API, abstract interface, for application developers to use (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 company for database operation. java programmers only need to program for this set of interfaces.
 
Different database manufacturers need to provide different implementations for this set of interfaces. The collection of different implementations is the driver of different databases.
                                                                                                                                         ———— Interface oriented programming

 

JDBC programming steps

Step 1: import implementation java.sql The driver package implemented by each manufacturer of the interface in this package

Step 2: load and register the driver( java.sql.Driver Interface implementation class)

Step 3: get java.sql.Connection Implementation class object (database connection object)

Step 4: write Statement/PreparedStatement

Step 5: Execute sql sentence

Step 6: if it is a query statement, you will get ResultSet Object, right ResultSet Object, and then close it ResultSet Object; If you are adding, deleting, or modifying, omit this step

Step 7: close Statement object

Step 8: close Connection object

 
 

Get database connection

 

Element 1: Driver interface implementation class

Driver interface introduction

• The java.sql.Driver interface is the interface that all JDBC drivers need to implement. This interface is provided for database manufacturers. Different database manufacturers provide different implementations.
• In the program, you do not need to directly access the classes that implement the Driver interface, but the Driver manager class (java.sql.DriverManager) calls these Driver implementations.
  • Oracle driver: oracle.jdbc.driver.OracleDriver
  • mySql driver: com.mysql.jdbc.Driver

How to install the drive:

(1) Open IDEA and click File – > project structure

(2) Then operate as shown in Figure 1. In the third step, click the '+' sign, then click JARs or directories, find the file as shown in Figure 2 in the file, and finally click apply – > OK in the lower right corner

Loading and registering JDBC drivers

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

   Class.forName("com.mysql.jdbc.Driver");

2. Register driver: DriverManager class is the driver manager class, which is responsible for managing drivers

   DriverManager.registerDriver(com.mysql.jdbc.Driver)
   //Use this to register drivers

be careful:

Usually, it is not necessary 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 contain static code blocks. In this static code block, the DriverManager.registerDriver() method will be called to register an instance of itself.

The following is the source code of the Driver implementation class of MySQL:

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

 

Element 2: URL

1. Definition:
JDBC URL is used to identify a registered driver. The driver manager selects the correct driver through this URL to establish a connection to the database.

2. Structure:
The standard JDBC URL consists of three parts separated by colons.

jdbc:Sub agreement:Sub name

• Protocol: the protocol in the JDBC URL is always jdbc

• Sub protocol: the sub protocol is used to identify a database driver

• Subname: a method of identifying a database. The sub name can be changed according to different sub protocols. The purpose of using the sub name is to provide sufficient information for locating the database. Including host name (corresponding to the ip address of the server), port number and database name

  An example is shown below:
  

3. JDBC URL s of several common databases

(1) MySQL connection URL writing method:

  jdbc:mysql://Host name: mysql service port number / database name? Parameter = value & parameter = value

(2) How to write the connection URL of Oracle 9i:

  jdbc:oracle:thin:@Host name:oracle Service port number:Database name

(3) SQL Server connection URL is written as follows:

jdbc:sqlserver://Host name: sqlserver service port number: DatabaseName = database name

 

Element 3: user name and password

• User and password can tell the database by "attribute name = attribute value"
• You can call the getConnection() method of the DriverManager class to establish a connection to the database

 

Database connection mode

During the learning process, the connection method from the first edition to the final edition of the fifth edition has been continuously modified and optimized. Here, only an example of the database connection method of the final edition is presented to readers:

    @Test
    public  void testConnection5() throws Exception {
    	//1. Load configuration file
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        
        //2. Read configuration information
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");

        //3. Load drive
        Class.forName(driverClass);

        //4. Get connection
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }

The configuration file is declared in the src directory of the project: [jdbc.properties]

The configuration information of the configuration file is:

user=root
Password = database password set by yourself
url=jdbc:mysql://localhost:3306/ Select the database name to use
driverClass=com.mysql.jdbc.Driver

Benefits of using profiles:

① It realizes the separation of code and data. If you need to modify the configuration information, you can modify it directly in the configuration file without going deep into the code
② If the configuration information is modified, the recompilation process is omitted.

 
 

Implement CRUD operation using PreparedStatement

 

Operating and accessing databases

• The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server. In fact, a database connection is a Socket connection.

• In the java.sql package, there are three interfaces that define different ways to call the database:

  • Statement: an object used to execute a static SQL statement and return the results it generates.
  • PrepatedStatement: the SQL statement is precompiled and stored in this object, which can be used multiple times to execute the statement efficiently.
  • CallableStatement: used to execute SQL stored procedures

 

Disadvantages of using Statement to manipulate data table

Create a statement object by calling the createStatement() method of the Connection object. This object is used to execute static SQL statements and return execution results.

The following methods are defined in the Statement interface to execute SQL statements:

int excuteUpdate(String sql): perform UPDATE operations INSERT, UPDATE, DELETE
ResultSet executeQuery(String sql): execute query operation SELECT

However, using Statement to manipulate the data table has disadvantages:

• Problem 1: there is string splicing operation, which is cumbersome
• Problem 2: SQL injection problem

SQL injection:
It is a method of using some systems to inject illegal SQL statement segments or commands into the user input data without sufficient inspection of the user input data, so as to use the SQL Engine of the system to complete malicious behavior.

 

Use of PreparedStatement

PreparedStatement introduction

• The PreparedStatement object can be obtained by calling the preparedStatement(String sql) method of the Connection object

• 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 a question mark (?). Call the setXxx() method of the PreparedStatement object to set these parameters. The setXxx() method has two parameters. The first parameter is the index of the parameter in the SQL statement to be set (starting from 1), and the second is the value of the parameter in the SQL statement to be set

PreparedStatement vs Statement

• Code readability and maintainability.

• PreparedStatement maximizes performance:

  (1) DBServer provides performance optimization for precompiled statements. Because the precompiled statement may be called repeatedly, the execution code of the statement compiled by the DBServer compiler is cached. As long as it is the same precompiled statement in the next call, it does not need to be compiled. As long as the parameters are directly passed into the compiled statement execution code, it will be executed.

  (2) In the statement statement statement, even if it is the same operation, because the data content is different, the whole statement itself cannot match, and there is no meaning of caching the statement. In fact, no database will cache the execution code after the compilation of ordinary statements. In this way, the incoming statement will be compiled once every execution.

  (3) Syntax check, semantic check, translation into binary commands, cache

• PreparedStatement prevents SQL injection

 

Data type conversion table corresponding to Java and SQL

General method for adding, deleting and modifying

    public void update(String sql,Object ... args){
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1. Get database connection
			conn = JDBCUtils.getConnection();
			
			//2. Get the instance of PreparedStatement (or: precompiled sql statement)
			ps = conn.prepareStatement(sql);
			//3. Fill placeholder
			for(int i = 0;i < args.length;i++){
				ps.setObject(i + 1, args[i]);
			}
			
			//4. Execute sql statement
			ps.execute();
		} catch (Exception e) {
			
			e.printStackTrace();
		}finally{
			//5. Close resources
			JDBCUtils.closeResource(conn, ps);
			
		}
	}

 

General method for realizing query operation

    public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 1. Get database connection
			conn = JDBCUtils.getConnection();

			// 2. Precompile the sql statement to obtain the PreparedStatement object
			ps = conn.prepareStatement(sql);

			// 3. Fill placeholder
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			// 4. Execute executeQuery() to get the result set: ResultSet
			rs = ps.executeQuery();

			// 5. Get metadata of result set: ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();

			// 6.1 get columncount and columnlabel through ResultSetMetaData; Get column value through ResultSet
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {// Traverse each column

					// Get column value
					Object columnVal = rs.getObject(i + 1);
					// Gets the alias of the column: the alias of the column, using the property name of the class as the alias
					String columnLabel = rsmd.getColumnLabel(i + 1);
					// 6.2 using reflection, assign values to the corresponding attributes of the object
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columnVal);

				}

				return t;

			}
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			// 7. Close resources
			JDBCUtils.closeResource(conn, ps, rs);
		}

		return null;

	}

Note: the query operation implemented by PreparedStatement can replace the query operation implemented by Statement, and solve the problems of Statement concatenation and SQL injection.

 

ResultSet and ResultSetMetaData

ResultSet

• The query needs to call the executeQuery() method of PreparedStatement, and the query result is a ResultSet object

• The ResultSet object encapsulates the result set of database operation in the form of logical table, and the ResultSet interface is provided and implemented by the database manufacturer

• What the ResultSet returns is actually a data table. There is a pointer to the front of the first record in the data table.

• The ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object. Call the next () method to check whether the next line is valid. If valid, the method returns true and the pointer moves down. Equivalent to a combination of hasNext() and next () methods of the Iterator object.

• When the pointer points to a row, you can get the value of each column by calling getXxx(int index) or getXxx(int columnName).

  • For example: getInt(1), getString("name")
  • Note: the indexes in the relevant Java API s involved in the interaction between Java and database start from 1.

 

ResultSetMetaData

• An object that can be used to get information about the types and properties of columns in a ResultSet object

• ResultSetMetaData meta = rs.getMetaData();

  • getColumnName(int column): gets the name of the specified column

  • getColumnLabel(int column): gets the alias of the specified column

  • getColumnCount(): returns the number of columns in the current ResultSet object.

  • getColumnTypeName(int column): retrieves the database specific type name of the specified column.

  • getColumnDisplaySize(int column): indicates the maximum standard width of the specified column, in characters.

  • isNullable(int column): indicates whether the value in the specified column can be null.

  • isAutoIncrement(int column): indicates whether the specified columns are automatically numbered so that they are still read-only.

After understanding some methods of ResultSetMetaData, you can solve the following problems:

Question 1: after getting the result set, how do you know which columns are in the result set? What is the column name?

A description is required ResultSet Object, i.e ResultSetMetaData

Question 2: about ResultSetMetaData

How to get ResultSetMetaData:  call ResultSet of getMetaData() Method can
 obtain ResultSet How many columns are in: call ResultSetMetaData of getColumnCount() method
 obtain ResultSet What is the alias of each column: call ResultSetMetaData of getColumnLabel() method

 

Release of resources

• Release ResultSet, Statement,Connection.
• Database Connection is a very rare resource. It must be released immediately after use. If the Connection cannot be closed in time and correctly, it will lead to system downtime. The use principle of Connection is to create as late as possible and release as early as possible.
• It can be closed in finally to ensure that resources can be closed in case of exceptions in other codes.

 

JDBC API summary

• Two thoughts

  • The idea of interface oriented programming

  • ORM (object relational mapping)

    • A data table corresponds to a java class
    • A record in the table corresponds to an object of the java class
    • A field in the table corresponds to an attribute of the java class

• Two technologies

  • Metadata of JDBC result set: ResultSetMetaData
    • Get the number of columns: getColumnCount()
    • Get alias of column: getColumnLabel()
  • Through reflection, create the object of the specified class, obtain the specified attribute and assign a value

Note: sql needs to be written in combination with column names and table attribute names. Note the alias.
 
 

Operation BLOB type field

 

MySQL BLOB overview

• In MySQL, BLOB is a large binary object. It is a container that can store a large amount of data. It can hold data of different sizes.

• PreparedStatement must be used for inserting data of BLOB type, because data of BLOB type cannot be spliced with strings.

• Four BLOB types of MySQL (they are the same except that they are different in the maximum amount of information stored)
  

• In actual use, different BLOB types are defined according to the data size to be stored.

• It should be noted that if the stored file is too large, the performance of the database will decline.

• If the error xxx too large is reported after specifying the relevant Blob type, find the my.ini file in the mysql installation directory and add the following configuration parameters: max_allowed_packet=16M. Also note: after modifying the my.ini file, you need to restart the mysql service.

 

Insert big data type into data table

//Get connection
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// Fill placeholder
ps.setString(1, "Xu Haiqiang");
ps.setString(2, "xhq@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// Manipulate variables of Blob type
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
//implement
ps.execute();
		
fis.close();
JDBCUtils.closeResource(conn, ps);

 

Modify Blob type field in data table

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);

// Fill placeholder
// Manipulate variables of Blob type
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);

ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);

 

Read big data type from data table

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
	Integer id = rs.getInt(1);
    String name = rs.getString(2);
	String email = rs.getString(3);
    Date birth = rs.getDate(4);
	Customer cust = new Customer(id, name, email, birth);
    System.out.println(cust); 
    //Read Blob type fields
	Blob photo = rs.getBlob(5);
	InputStream is = photo.getBinaryStream();
	OutputStream os = new FileOutputStream("c.jpg");
	byte [] buffer = new byte[1024];
	int len = 0;
	while((len = is.read(buffer)) != -1){
		os.write(buffer, 0, len);
	}
    JDBCUtils.closeResource(conn, ps, rs);
		
	if(is != null){
		is.close();
	}
		
	if(os !=  null){
		os.close();
	}
    
}

 
 

Batch insert

 

Batch execution of SQL statements

When you need to insert or update records in batches, you can use the batch update mechanism of Java, which allows multiple statements to be submitted to the database for batch processing at one time. Generally, it is more efficient than submitting processing alone

JDBC batch processing statements include the following three methods:

• addBatch(String): add SQL statements or parameters that need batch processing;
• executeBatch(): execute batch processing statements;
• clearBatch(): clear cached data

Generally, we will encounter two cases of batch execution of SQL statements:

• Batch processing of multiple SQL statements;
• Batch parameter transfer of an SQL statement;

 

Efficient batch insertion

Example: insert 20000 pieces of data into the data table

• Provide a goods table in the database. Create the following:

  CREATE TABLE goods(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
  );
  

Implementation level 1: use Statement

Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
for(int i = 1;i <= 20000;i++){
	String sql = "insert into goods(name) values('name_' + "+ i +")";
	st.executeUpdate(sql);
}

 

Implementation level 2: use PreparedStatement

long start = System.currentTimeMillis();
		
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1;i <= 20000;i++){
	ps.setString(1, "name_" + i);
	ps.executeUpdate();
}
		
long end = System.currentTimeMillis();
System.out.println("The time spent is:" + (end - start));//82340
		
		
JDBCUtils.closeResource(conn, ps);

 

Implementation level 3

@Test
public void testInsert1() throws Exception{
	long start = System.currentTimeMillis();
		
	Connection conn = JDBCUtils.getConnection();
		
	String sql = "insert into goods(name)values(?)";
	PreparedStatement ps = conn.prepareStatement(sql);
		
	for(int i = 1;i <= 1000000;i++){
		ps.setString(1, "name_" + i);
			
		//1. Save sql
		ps.addBatch();
		if(i % 500 == 0){
			//2. Implementation
			ps.executeBatch();
			//3. Empty
			ps.clearBatch();
		}
	}
		
	long end = System.currentTimeMillis();
	System.out.println("The time spent is:" + (end - start));//20000: 625 / / 1000000: 14733  
		
	JDBCUtils.closeResource(conn, ps);
}

Modification 1: use addBatch() / executeBatch() / clearBatch()
 
Modify 2: the mysql server turns off batch processing by default. We need to use a parameter to enable mysql to turn on batch processing support.
? Rewritebackedstatements = true is written after the url of the configuration file
 
Modification 3: use the updated MySQL driver: mysql-connector-java-5.1.37-bin.jar

 

Implementation level 4

@Test
public void testInsert2() throws Exception{
	long start = System.currentTimeMillis();
		
	Connection conn = JDBCUtils.getConnection();
		
	//1. Set not to submit data automatically
	conn.setAutoCommit(false);
		
	String sql = "insert into goods(name)values(?)";
	PreparedStatement ps = conn.prepareStatement(sql);
		
	for(int i = 1;i <= 1000000;i++){
		ps.setString(1, "name_" + i);
			
		//1. Save sql
		ps.addBatch();
			
		if(i % 500 == 0){
			//2. Implementation
			ps.executeBatch();
			//3. Empty
			ps.clearBatch();
		}
	}
		
	//2. Submission of data
	conn.commit();
		
	long end = System.currentTimeMillis();
	System.out.println("The time spent is:" + (end - start));//1000000: 4978 
		
	JDBCUtils.closeResource(conn, ps);
}

Level 4: operate on the basis of level 3
 
setAutoCommit(false) / commit() using Connection