Afternoon of the fourth day of javaweb training-JDBC in-depth understanding

JDBC in-depth understanding

1. Course introduction

1.PreparedStatement introduction; (understand)
2.PreparedStatement use; (master) dynamic statement object executes sql statement is dynamic
3. login function
4. login function realization method
5. transaction introduction; (understand)
6. transaction opening/committing / Rollback; (Master)
7. Transaction use; (Master) Bank transfer business
8. Get the primary key; (Understand) (Only for self-increment)
9. Connection pool; (Understand) Configure the connection parameters in the framework Can be integrated (guide package)
10. Optimization tool class (understand)

2. PreparedStatement query

2.1 Review jdbc

Complete an addition, deletion, and modification check through jdbc
① Create a table student
field username, password, age, id, intro, sex
Create a domain object based on the table, domain object
② write a dao layer, write an interface
IStudentDAO
③ write a dao layer Implementation class of the interface

Insert picture description here

④ Extract code JDBCUtil-tool class

Insert picture description here

Perfect query all:

Insert picture description here

2.2 Introduction of PreparedStatement

When using JDBC, when defining sql, there is a very tangled problem
. It is not easy to splice strings , especially insertion and modification;

How can we not concatenate strings-through PreparedStatement

2.2.1 Introduction to PreparedStatement

Statement: Represents a static SQL statement object.
PreparedStatement: Sub-interface of Statement, == represents a precompiled SQL statement object. ==

What is a pre-compiled SQL statement

PreparedStatement is an interface in java.sql, which is a subinterface of Statement. When the SQL statement is executed through the Statement object, the SQL statement needs to be sent to the DBMS, which is first compiled and then executed. The prepared statement is different from the Statement. The SQL statement is specified when the PreparedStatement object is created, and the statement is immediately sent to the DBMS for compilation. When the compiled statement is executed, the DBMS directly runs the compiled SQL statement, without the need to first compile it and execute it like other SQL statements.

Api example:

Insert picture description here

Note:? Don't add quotation marks above, don't write'?' like this;
you will know what to do later? What type is represented;

  比如:pstm.setInt(2,110592) 这里可以设置int值,它会自动知道;

2.3 Use of PreparedStatement

Jia Lian wants to be a deacon:
But the desire in it—getting the object to be processed

Insert picture description here

Note:
mysql has a variable called SQL_SAFE_UPDATES, for the security of database update operations,
execute it directly: SET SQL_SAFE_UPDATES=0;

①The steps of Jia Lian are the same, and PreparedStatement is a sub-interface of Statement ②Question
mark (?)-equivalent to a placeholder (a question in SQL), do we distinguish between types when we use it?-No-strings do not need to be special Add quotation marks
③Use SQL when you get the statement object

   Statement st = conn.createStatement();
  PreparedStatement st = conn.prepareStatement(sql);//预编译sql

④ No need to transfer sql during execution;

⑤All placeholders need to add corresponding values ​​to it –

 PreparedStatement对象.setXxxx(位置(从1开始),相应的值)

⑥? Can only be placed in a specific place (cannot replace the keyword table name)-can not be placed in select * from?

Error-prone areas of PreparedStatement:

1. Do not add quotation marks to the placeholder string;
2. When adding the corresponding value, the position, type, and number must be exactly the same;
3. When executing, do not put SQL in;
st.executeUpdate( ) The statement executed is the compiled statement. If you put sql in, it is the sql before compilation, there will be problems in it;


Summary: The difference between Statement and PreparedStatement Statement is to create a statement object, no need to pass in sql; ==statement = connection.createStatement();== PreparedStatement is to create a prepared statement object, when it is created, you need to pass in sql; = =ps = connection.prepareStatement(sql);== When executing sql statement, Statement needs to be passed in sql; PreparedStatement needs to pass in sql when executing sql statement;

2.4 Perfect function

Just set the value through st.setString/st.setInt/setBoolean, etc., and there is also a method
st.setObject(index, value) to set;
as shown below:

Insert picture description here

Complete the insert function:

Insert picture description here

Perfect delete function

Insert picture description here
Insert picture description here

Complete query function

Insert picture description here

Which way is better to use Statement and PrepareStatement? Will
be used in the futurePrepareStatement;

2.5 Why Choose PreparedStatement

PreparedStatement will be used in the future, it has the following advantages;

2.5.1 Clear thinking and easy to use

We don't need to concatenate strings,
especially when there are many fields, if you use Statement to spell strings. Who is who knows;

2.5.2 Speed, higher efficiency and faster

PreparedStatemnt is faster than Statement PreparedStatement
is a prepared statement

After I send a piece of sql to the database, what will the database do?

Completed by two parts: Java program and database part

The Java program will send sql to the database:

Statement sends sql
to the cache to check whether sql' exists. If it exists, use the sql in the cache to execute it. If it does not exist, from ① to ⑤, these steps are time-consuming and performance-consuming;

①Check the security of
sql;
②parse sql; ③compile sql into binary;
④execute sql;
⑤(some databases will put this sql in the cache, that is, open up a cache space in the database)

PreparedStatement occurs sql

Insert picture description here

Mysql does not support caching

2.5.3 Prevent SQL injection

Directly use the login function to demonstrate the prevention of this sql injection, how to do the login function in general?

Insert picture description here

Code:

Insert picture description here

Test code:

Insert picture description here

This is SQL injection;
analysis:

String name = " ' or 1=1 or ' ";
当我们的name是上面的内容的时候,用Statement我们会去拚接字符串,就会出现下面一句sql:
select * from student where name='' 
	or 1=1 
	or ' ' and password='werwer'
那么就肯定能查询到数据

PrepareStatement is not a concatenated string, it is a template - the format is fixed
But using PrepareStatement, its statement is quite a template. The basic structure of this template cannot be changed (the string cannot be spliced), so,It solves the problem of sql injection very well

2.6 Summary

1. What does PreparedStatement
mean when the prepared statement object is created and the object is passed into SQL for compilation. It does not need to pass in SQL to run directly
2. PreparedStatement realizes
3. The difference between
PreparedStatement and Statement ① PreparedStatement does not need to splice strings, the structure is clear
② PreparedStatement efficiency A little higher than Statement
③PreparedStatement can prevent sql injection

3. Complete the login function

3.1 Comparison of login methods

Complete the simple login function:

Incoming user name and password, and then splicing query user SQL
Select * from user table where name =? And password =?
If the user name and password exist at the same time, the data can be queried, and then we encapsulate the data into a corresponding object, and then return this object. If the user name and password are wrong and there is no data, directly return null

Comparison of login methods:

Insert picture description here

3.2 Realization of Login Method Two

Insert picture description here

test:

Insert picture description here

3.3 Summary

1. Realization of login
mode①select * from user where username =? and password=?
②select * from user where username=? It is recommended to use
③select count(*) from user where username=? and password=?

4. Details function

4.1 Affairs

4.1.1 Transaction use

What is a transaction?
Transaction: in the programA set of operations, Either all succeed or all fail;
ACID

Transaction example:
Bank transfer function: bank / money
Guoer and Aunt:
Guoer: 10,000 yuan Aunt: 0 yuan

Transfer: I have to transfer 1,000 yuan to my aunt later.
Analysis: To transfer money, two SQL statements are required, but the programmer will make mistakes and the comparison code is wrong.

①update bank set money = money +1000 where name ='Aunt' ②Code
error
③update bank set money = money -1000 where name ='过儿'

Complete example:

Cases of things:
analysis: ①build the table

Insert picture description here


②simulate the

Insert picture description here


problem, how to complete the transaction-commit at the same time or fail at the same time
③ setAutoCommit(boolean autoCommint) Set the auto-commit mode of this link to a given state, (set auto-commit transaction , When true, it starts automatically, when it is false, automatic start is prohibited)
In our JDBC, there is always a transaction, but this transaction is automatically committed by default (conn.getAutoCommit() is equal to true),When it is auto-committed, every time a sql is executed, the transaction is committed, And just two sql, indicating that it is not in a transaction, so there is a problem;

How to solve? -Generally speaking, our transaction should be set to execute several SQLs (to complete an overall function and commit), so we need to automatically commit the transaction to modify false, (do not let it execute a SQL, then automatically commit) conn.setAutoCommint(false) - Close the automatic submission, set manual submission

When we turn off automatic submission, sql is not submitted for execution, so we must remember that when our overall function is completed, we must manually submit ourselves; - conn.commit but after failure, remember to roll back the data
conn.rollback()

Insert picture description here


Insert picture description here

4.1.2 Awareness of affairs

Transaction (Transaction, abbreviated as tx):
In the database, > The so-called transaction refers to a set of logical operation units that transform data from one state to another.

In order to ensure the consistency of the data in the database, the manipulation of the data should be discrete groups of logical units:
when each logical operation unit is completed, the consistency of the data can be maintained,
and when part of the operation in this unit fails, the entire All transactions shall be regarded as errors, and all operations after the starting point shall be rolled back to the starting state.

Transaction operation: First define the start of a transaction, and then modify the data. At this time, if you commit (commit), these changes will be permanently saved. If you rollback (rollback), the database management system will abandon all the changes you made And back to the state when the transaction was started.
Transaction: A group of operations in the transaction space, either all succeed or all fail;


==ACID property of transaction ==:. . . . ==Atomicity==: Refers to the entire transaction as an indivisible unit of work. Only when all operations in the transaction are executed successfully, the entire transaction is considered to be successful. If any SQL statement in the transaction fails, the SQL statement that has been successfully executed must also be cancelled, and the database state should return to the state before the transaction was executed.

consistency: Means that database transactions cannot destroy the integrity of relational data and the consistency of business logic. For example, for a bank transfer transaction, regardless of the success or failure of the transaction, it should be ensured that the total amount of deposits in the two transfer accounts after the transaction is the same as before the transfer.

Isolation: Refers to in a concurrent environment, when different transactions manipulate the same data at the same time, each transaction has its own complete data space. (Hibernate is talking) JPA framework

Persistence: It means that as long as the transaction ends successfully, the updates it made to the database must be saved permanently. Even if a system crash occurs, after restarting the database system, the database can be restored to the state it was in when the transaction ended successfully.


Transaction: A set of user-defined operations. This group of operations are either done (all successful) or not done;
transaction processing: ensure that all transactions are executed as a unit of work, even if there is a failure, this way of execution cannot be changed. When multiple operations are performed in a transaction, either all transactions are committed (commit), or the entire transaction is rolled back (rollback) to the original state
. Two actions for processing transactions:
commit: commit: When the entire transaction, all All logical units are executed successfully. ---->Submit the transaction.—The data has been submitted and cannot be changed.
Rollbackrollback: When a logical unit fails in the entire transaction, ---->roll back the transaction.
Cancel all operations in the transaction -> restore to the original state.


How to handle transactions in code:
1. In JDBC, transactions are committed by default. You must first set the transaction to be manually committed.
connection object.setAutoCommit(false);//Set the transaction to be manually committed.
2. Manually commit the transaction .
connection object.commit();
3. If an exception occurs, the transaction must be rolled back: if the transaction is
not rolled back, the total balance is still correct. If the transaction is not rolled back, the database resources will not be released.
connection object.rollback();


1. In JDBC, the transaction is committed by default, when is it committed.
When a DML/DDL operation is executed, the transaction has been committed.
2. For CRUD operations. Only DML operations have transactions, query There is no transaction in the operation.
However, we generally put the query in the transaction. —> Spring's transaction management will talk about it.
3. In the future, if you find that the code you wrote is correct, the test also passes, but it is the database table The data in the database does not change----->The problem of the transaction not being submitted. When we learn MyBatis, we will encounter this problem.
4. In MySQL, InnoDB supports foreign keys. Support transactions, MyISAM does not support foreign keys, and does not support Affairs.


As for transaction management, I will talk about it in Hibernate, MyBatis, and Spring.
Transaction manager: TransactionManager.

4.2 Get the primary key (master)

4.2.1 Why do I need to get an Id

Now we insert a piece of data, but we don't know the id of the data, and we sometimes need this id for operations.

For example, if we insert a piece of data into the product table, its quantity is 200, but the product table does not have a field indicating the quantity, and the product_store table contains storeNum, so we should insert a piece of data into the product_store table at the same time after inserting the data. ;-At this time we need to get the newly inserted id in the product table;

How to get the Id value?
By finding the maximum value of Id in the table, it is obtained;-but there are still problems; for example, when multiple people visit at the same time, the obtained Id may also have problems;

Insert picture description here


Insert picture description here


Statement.RETURN_GENERATED_KEYS

4.2.2 How the Statement gets the primary key

For adding, the language object is obtained through the connection object and obtained through conn.createStatement();
when it needs to be executed, it is executed through the executeUpdate method. In addition to executeUpdate has a parameter, there is also
a method executeUpdate(String sql,int autoGeneratedKeys );

Whether the automatically generated key can be obtained; (that is, the automatically generated Id key, we can get it)

Insert picture description here


Insert picture description here

Get all automatically generated keys created as a result of executing this Statement object

Insert picture description here

note:

Insert picture description here

No column is specified here, but there is only one column, all can be obtained by rs.getLong(1)

Insert picture description here

4.2.3 How to get the primary key of PreparedStatement

Insert picture description here

Case:
// Write sql statement

String sql = "insert into bank (money,name) values (?,?)";
// RETURN_GENERATED_KEYS 该常量指示生成的键应该可用于获取。
ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setInt(1, 600);
ps.setString(2, "tom");
ps.executeUpdate();
// getGeneratedKeys() 获取由于执行此 Statement 对象而创建的所有自动生成的键。
ResultSet rs = ps.getGeneratedKeys();
while(rs.next()){
System.out.println("主键为:"+rs.getInt(1));
}

How to get the automatically generated primary key when saving data in JDBC?


Statement:
int executeUpdate(String sql, int autoGeneratedKeys):

Execute SQL:
Parameter: autoGeneratedKeys, whether you need to return the automatically generated primary key. Constant value: Statement.RETURN_GENERATED_KEYS
ResultSet getGeneratedKeys(): Get the automatically generated primary key

PreparedStatement:
PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)  :创建PreparedStatement对象,并指定是否需要返回生成的主键. 参数的常量值:Statement.RETURN_GENERATED_KEYS

Documents and document details:


Hibernate can automatically return, (the bottom layer has been encapsulated OK).

4.3 Summary

Summary:
Transaction: refers to a group of operations that either succeed or fail at the same time;
the four properties of the transaction ACID: atomicity, consistency, isolation, and durability;

5. Connection pool

5.1 Connection pool idea

For the connection pool, yesPartial theoretical knowledge, You need to master the ideas, and you don’t need to write code to achieve it;
connection pool: a container used to hold connection objects;

Why do I need to use a connection pool?

A piece of Java code operating a database requires a connection, and each time a database is operated, a connection needs to be obtained, but why can't a connection be used? -Use the same connection, there will be thread safety issues;
Every time you get a connection, you need to verify the user name and password;
Question: Does it take time to verify the user name and password?

Suppose it takes 0.01s for each verification, but how many seconds does it take for a website to be visited by a thousand people at the same time?
A waste of 10 seconds; for
example, Sina homepage, query sports news, you need a sql, query entertainment news, you need a sql, maybe a home page, there will be more than 100 requests (to the database query), then how many seconds
wasted ? A waste of 1000 second

Conclusion: a lot of time will be wasted

Each request will create a connection, so it will waste resources (memory). When 1000 people visit at the same time, it will take up a lot of resources, so it is a waste of time and the container operating system crashes;

How to solve it? Connection pool

Insert picture description here

Connection pool: a container, pool, used to hold connection objects;
connection poolWhere do the connections inside come from, and come from the database;

Getting the connection from the connection pool also requires a user name and password, and it takes time, but the Java code to get the connection only needs to be obtained from the connection pool, without the user name and password. After using it, it will return to the connection pool;

Insert picture description here

This is a bit like instant noodles. When you are hungry, you don’t need to go out to get it every time.

But there will be problems here?
① Is there a connection as soon as the connection pool comes out?
Of course there is,The number of connections needs to be set in advance;
Some people will return it after using it, and the next one of me will use it;

②If I take 10 connections, you only have 5, what should I do?
When it's not enough, I get it from the database.
But now, if it's not enough, I get it from the database. Why do I need the connection pool?
It will still waste time and resources, and there is an additional layer in the middle;

③If there are too many connections in the connection pool, it will waste and occupy resources, right?

④What if 5 people have not paid back?
If you need to solve the above problem, (need to study the problem of the railway station)
Regardless of whether there are people, you need to leave one or two windows,-there is an initial window
1. Initial capacity (5) How
many windows are needed at the train station at the beginning
? 2 What if there are not enough windows when there are more people-need to increase the window , But this window does not increase randomly.
When the number of people is large, add a window; the
maximum number (10)

3. If there are fewer people, there are only a few windows-at this time, the smallest window is required. The
smallest number (2)

4. What should I do if someone occupies the window?
If a person is not doing business, such as occupying 3 minutes, do not occupy the window.
Maximum connection time (3)
5. When the tickets are sold out, what if there are people waiting?
If the wait is too long, the connection will be disconnected and the request will time out;

This isUnderstanding of the connection pool;

Connection pool overview

In Java, the connection pool uses the javax.sql.DataSource interface to represent the connection pool. The DataSource here is the connection pool. Connection pool is DataSource

DataSource is an interface, like JDBC, it is a set of interfaces developed by Sun, which needs to be implemented by major manufacturers; you
need to import the corresponding package-the guide package...
So to use the connection pool, you need to first need the guide package;

There are two ways to implement commonly used DataSource:

DBCP: Spring recommended (Spring framework has integrated DBCP)
C3P0: Hibernate recommended (early) (Hibernate framework has integrated C3P0)

boneCP: Eliminated
Hikari: Very leathery connection pool, because of efficient Springboot integration
Druid: Ali’s, good overall performance, many functions



What is the difference between using connection pooling and not using connection pooling?

From the code:
Do not use connection pool: The Conenction object is obtained by DriverManager.
Connection conn = DriverManager.(url,username,password);

Use connection pool:(There is only one difference, the current connection needs to be taken from the DataSource, and the DataSource needs to be taken from the database,
so we need to give the user name and password to the connection pool, and the connection pool helps us to get the connection from the database)
How to create a DataSource object, how to in DataSource Set the url, account number, and password.
Connection conn = DataSource object.getConnection();


Finally, when using the connection pool, you need to release resources: Release resources: Connection object. close(): Put the Connection back to the connection pool instead of disconnecting from the database. Just put the connection back to the connection pool

5.2 DBCP connection pool implementation

As mentioned above, the way to realize the connection pool is through DBCP and C3P0; bone hakiray
Its effect is the same as that of JDBC, and no effect can be seen;

The following uses the DBCP method to realize the connection pool. The
first step is to prepare the jar package:
copy the jar:
commons-dbcp-1.3.jar commons-pool-1.5.6.jar.

After importing the package, it will not be used-
check the documentation: commons-dbcp-1.3-src\doc\BasicDataSourceExample.java (example)

Insert picture description here

The only difference: the way to get the connection is different
BasicDataSourceExample.java to get the connection

Insert picture description here

Solve the hard-coded problem of DBCP:
the connection information should be placed in the configuration file: the noun of the configuration file can be arbitrary.
But the key in the configuration file must be the attribute of the BasicDataSource object (the attribute determined by the setXxx method);

dbcp.properties
#连接字符串
url=jdbc:mysql://localhost:3306/jdbcdemo
#用户名
username=root
#密码
password=admin
#驱动的类路径
driverClassName=com.mysql.jdbc.Driver
#连接池启动时的初始值
initialSize=1
#连接池的最大值
maxActive=50
#连接池的最大空闲数

maxIdle=20


How to set the configuration information in dbcp.properties to the program: ![insert picture description here](https://img-blog.csdnimg.cn/20210602102428898.png?x-oss-process=image/watermark ,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlYXJRaUhhbw==,size_16,color_FFFFFF,t_70)! [insert picture description here](https://imgs-cn/png202,t_70)! [insert picture description here](https://imgs-cn/png202. type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RlYXJRaUhhbw==,size_16,color_FFFFFF,t_70)

The way to read the resource file directly:

Insert picture description here

Note: The property name in db.properties must correspond to the
test method here :

Insert picture description here

Method 2:
Get the connection through the BasicDataSourceFactory factory through the BasicDataSourceFactory factory:

Insert picture description here

Note: The property name in db.properties must correspond to this side, the first letter of ds.setUsername is lowercase;

Insert picture description here

note

Insert picture description here

5.3 Extract (understand)

5.4.1 Extraction (addition, deletion, modification method)

Extraction method one:

① Write a class BaseDAOImpl to
write a public method-complete addition, deletion, modification

Insert picture description here

①A class BaseDAOImpl ②Rewrite,
add, delete, modify

Insert picture description here

5.5 Summary

1. What is the connection pool to
install the connection container
2. Why use the connection pool to
improve the connection efficiency of the database to save memory resources and avoid the existence of a large number of connections in the system
3. The idea
of the connection pool does not need to get the connection from the connection pool every time Get the connection from the database, combined with the example of the train station
4. The realization of the connection pool (understanding)
DBCP (Spring) C3PO (hibernate)
5. Extract the method of adding, deleting and modifying a public method

6. Course summary

6.1 Focus

1. Recognizes PreparedStatement (precompiled statement objects)
2.PreparedStatement use
objects when created need to pass sql, implementation of the time do not need to pass sql
3.PreparedStatement and Statement differences
do not concatenate strings clear structure efficiency this anti-HA sql Injection
4. Comparison of login methods
Method one select * from user where username='' and password='';
Method two select * from user where username='' >
Method three select count(*) from user where username ='' and password='' (not considered)
5. Transaction characteristics ACID
transaction: a set of operations either succeed or fail.
Atomicity consistency, isolation, persistence
6. Connection pool thinking, master
connection pool: install connected containers combined with train ticket sales Example What is
the difference between using connection pooling and not using connection pooling? The difference between the
usage and the efficiency of
using the connection pool-understand

6.2 Difficulties

1. Understand and use the connection pool idea
2. Difficulty 2: sql injection

6.3 How to master

1. Knock on the teacher's code more (knock a vote)
2. Combine theory with realization to master the knowledge points

6.4 Troubleshooting skills

There is an error:

1. After the exception occurs, you can copy the exception information to Baidu or API to search;
2. After the problem occurs, report the error message, how to see if there is any problem with the code you wrote
3. Solve the problem according to the information in Baidu or API;

No error reported:
1. Will check the problem step by step according to the problem-solving ideas
2. System.out.println();
3. Debug mode F8 directly run the next breakpoint F6 directly run a sentence of code F5 into the method

7. Practice after class

1. Question 1: Complete the login of two login methods
2. Question 2: Simulate the function of completing a transfer (practice transaction)
3. Question 3: PrepareStatement to return the primary key (optional)
4. Question 4: Use DBCP Complete the crud of a table (optional)
5. Question 5 (optional): If there are multiple entity classes in a project (there are multiple classes under the domian package, such as students, teachers, users, departments, etc.), then There will also be multiple corresponding interfaces and multiple implementation classes under the corresponding dao. Please write at least 2 entity classes first, and then consider how to optimize the extraction of the corresponding interfaces and implementation classes of the dao layer

8. Interview questions

1. What are the characteristics of the first question?
2. The second question is the difference between PreparedStatement and Statement
3. The third question is the difference between using connection pooling and not using connection pooling

9. Recommendations for extended knowledge or extracurricular reading