Database operation steps :
1: The driver to import the database jar package ----> Import implementation class
2: The load driver -----> Tell him which database I want to connect to
3: Obtain a connection ------> Let me have a relationship with the database
4: Get the preprocessing statement object -> Let me send out sql Instruction to database
5: perform SQL command ;---> Preprocessing statement objects .execute("SQL Command line ");
6: Release resources -------> Cut me off from the database
How to find the dataset library you want to operate ：
Write dead jdbc: Vendor names for different databases ://ip Address : Port number / The name of the database
operation mysql The way 1：jdbc:mysql://localhost:3306/ The name of the database
operation mysql The way 2：jdbc:mysql://127.0.0.1:3306/ The name of the database
If The operating database is on the local computer , And the port number of the database is 3306
It can be abbreviated The way ：jdbc:mysql:/// The name of the database
Extract tool class ：DAO Standard design
standard DAO Class name and package name of the component . Reverse the domain name . Module name . Component name ----> cn.wolfcode.smis
1. Definition domain Package and class name ( operation DAO All to operate a table , The table should correspond to a javabean, Take a special name domain)
2. Definition DAO Packages and DAO Interface .
IxxxDAO---> XXX Express domain.
3. Definition DAO The package of the implementation class and the implementation class
4. Test class
5. Tool class
Standardized development steps --- Finish saving :
1. Create database tables .
2. Create the corresponding... According to the database table domain Baohe class .
Be careful : The name and type of the column of the table should match the attribute name and type of the class one by one .
3. adopt domain To create DAO Package and interface .
4. Generate the implementation class according to the interface .
5. According to the interface / Implementation class generates test class . Code to complete the test .
6. Implement a method , Test a method , The test passed , Write another method .
Precompiled statement object -PreparedStatement:
Precompiled statement object -PreparedStatement:
PreparedStatement Is a subclass of a static statement object . therefore , All methods in the parent class can be called to . But usually we don't call . Call your own method .
Precompiled statement objects are created by using SQL Templates to create objects .
SQL Templates : With placeholders ? Of SQL. The same operation ,SQL The statement is actually the same , It's just that the parameters are different .
The parameters cannot be determined , Use placeholders ? To express .
1. Get precompiled statement object .
Use database connection objects :
PreparedStatement prepareStatement(String sql); The relay will be SQL Templates , There are placeholders
2. In execution SQL Before , Be sure to set a value for the placeholder .
void setObject(int parameterIndex,Object x):
parameterIndex: Parameter index , stay JDBC in , The index is from 1 At the beginning .
3. perform SQL command :
Be careful , Don't call methods with parameters in the parent class .
DQL operation ： Additions and deletions
stay JDK in , How to parse the result set , Encapsulated in an object .ResultSet.
1. Inquire about id by 10 Student information
2. Query all student information .
ResultSet object :
The process of how to parse the result set is encapsulated into an object .
boolean next(): Move one line down , If you return true, It means there is data , Otherwise there is no data . To get data , Be sure to call next Method .
Object getObject(int columnIndex): Get data according to the position of the column . Index from 1 Start .
Object getObject(String columnLabel): Get data by column name
1. Connect the four elements of the database and write them repeatedly many times ( Extract to a public location , Just write it once ).
2.Class.forName Load bytecode , Bytecode will only be loaded once ( Use static code blocks to load registered drivers ).
3. Hard encoding .( Connect the four elements of the database ).
4. Duplicate code (DML operation , DQL operation ).
5. Every time you get the database connection object , It's closed directly after use . waste .
The configuration file ：
Symbol javaBean Of Student class ：
DAO Definition of interface ：
Interface test class ：
JDBC Tool class of ：
Implementation class of interface ：
The above code is still repeated ： The template method can be further optimized .
Injection problem ： Classic case login security issues ;
Business ： Classic case , Transfer security issues .
Expected results : If the transfer is successful , Then the data changes . If the transfer fails , Then the account balance should not change , It should still be the status before the transfer .
The above operation , Is the transaction operation in the database .
Business (Transaction, Shorthand for tx):
In the database , A transaction is a set of logical operation units , To change data from one state to another .
To ensure the consistency of data in the database , The manipulation of data should be discrete and grouped logical units :
When each logical operation unit is completed , Data consistency can be maintained ,
And when part of the unit fails , The whole business should be treated as a mistake , All operations after the starting point shall be fallback to the starting state .
Operation of transaction :
Start a transaction by defining , Then modify the data , If you submit (commit), These changes are permanently saved , If you go back (rollback), The database management system discards all your changes and returns to the state when the transaction started .
Business ： Refers to the set of operations that make up a single logical unit of work
Transaction processing ： Ensure that all transactions are executed as a unit of work , Even if something goes wrong , Can't change this way of execution . When multiple operations are performed in a transaction , Either all transactions are committed (commit), Or the whole transaction is rolled back (rollback) To the initial state
To put it bluntly :
1. Multiple operations need to be put together as a whole .
2. All success , Success is success , Otherwise a failure , All failed .
The transaction ACID attribute :
1. Atomicity （Atomicity）: Atoms in Chemistry , Is the smallest unit , No more Division .
Atomicity means that a transaction is an indivisible unit of work , Either the operations in the transaction occur , Or none at all .
2. Uniformity （Consistency): Ensure data integrity .
Transactions must transform the database from one consistency state to another .( Data is not broken )
3. Isolation, （Isolation）:
Transaction isolation means that the execution of one transaction cannot be interfered by other transactions , That is, the operations and data used within a transaction are isolated from other concurrent transactions , Transactions that execute concurrently cannot interfere with each other .
4. persistence （Durability）:
Persistence means that once a transaction is committed , It changes the data in the database permanently , Other subsequent operations and database failures should not have any impact on it
Transaction related details :
1): By default , The transaction is finished DML The operation is automatically submitted .
2): Query operation , In fact, there is no need for affairs . however , General , In our development, we put queries into transactions .
3): In development , The code is completely correct , No abnormal , But the data in the database remains unchanged .
consciousness : Transaction may not be committed .
4): stay MySQL in , Only InnoDB Storage engine support transactions , Support foreign keys ,MyISAM Unsupported transaction .
5): In the future, we should not be in DAO Layer handles , belong service Layer control ( Put forward ).
6): The business will be discussed again when explaining the framework and project .
The transaction operations , Multiple operations in one connection . You need to put multiple operations into a whole , You need to use transactions .
Operating templates :
Be careful :
Only in the call commit After method , The database will really change . All success , call commit Method . If it fails, be sure to call rollback Method .
reason : When you start a transaction , There is a transaction lock . Must call commit perhaps rollback To release .
Get the automatically generated primary key ： Get post registration information
1. Set a marker , Tell the database , I want to automatically generate the primary key .
When getting the precompiled statement object , Pass the tag to get .
2. Get the auto generated primary key .
Database connection pool ：
At present, the way we operate the database :
1. Get database connection object .
2. perform SQL command
3. Parse the result set .
4. close resource ( Result set object , Precompiled statement object , Database connection object )
After a SQL After the command , The database connection object will be closed . Here's the problem .
Why do I have to use a database connection pool :
ordinary JDBC Database connection (Connection object ) Use DriverManager To get , Every time you establish a connection to the database, you need to Connection Load into memory , Then verify the user name and password ( It costs 0.05s～1s Time for ), Database connection is expensive ( The cost of creation is relatively large ).
When you need a database connection , Just ask the database for a , Disconnect after execution . This way will consume a lot of resources and time .
The connection resources of the database are not well reused . If there are hundreds or even thousands of people online at the same time , Frequent database connection operations will take up a lot of system resources , Serious even can cause the server to crash .
For every database connection , They have to be disconnected after use . otherwise , If the program fails to close due to an exception , Will cause memory leaks in the database system , It will eventually lead to a restart of the database .
This development does not control the number of connection objects created , System resources will be allocated without consideration , If there are too many connections , It can also cause memory leaks , Server crash .
To put it bluntly :
The current way to manipulate the database is , It takes a lot of resources and time to create very expensive database connection objects , Once used, it turns off . The main problem is , Insufficient use of database connection objects . Use connection pooling to solve this problem .
Pool technology : Manage resource objects , Achieve maximum efficiency .
Pooling technology can reduce the number of resource objects created , Improve the performance of the program , Especially in the high and send this kind of improvement is more obvious . Resource objects cached using pooling technology have the following common characteristics ：
1, Object creation takes a long time ;
2, Object creation requires a lot of resources ;3, Objects can be reused after they are created . such as thread,connection And other objects have the above common characteristics .
stay Java in , Connection pool use javax.sql.DataSource Interface to represent the connection pool .
DataSource( data source ) And connection pool (Connection Pool) Is the same .
Be careful :DataSource Just an interface , By the major server manufacturers to achieve (Tomcat,JBoss).
frequently-used DataSource The implementation of the :
DBCP: Spring The framework recommends
druid: Alibaba's connection pool ( It's called Java The best performance connection pool in the language ) Druid .
What's the difference between using a connection pool and not using a connection pool ?
How to get Connection object :
Connection pool not used :
Connection pool is used :
Just get Connection object , The following operations are as like as two peas. .
The key lies in : How to create DataSource object .
How to release Connection object (Connection object .close()):
Connection pool not used : Yes, disconnect from the database server .
Connection pool is used : Return to the connection pool .
Use database connection pool to create JDBC Tool class of ：
The way 1：
The way 2：
Recommended reading ：
java Introduction of development JDBC Method of driver
Java Development technology JDBC Driver loading and registration steps
Java Development -JDBC Database connection pool configuration file sharing in technical fundamentals
java Development of Java Virtual machine stack