JDBC learning and sharing of java development

It Xiaoshang 2021-11-25 19:14:04
jdbc learning sharing java development

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

import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. Statement;
import org. junit. Test;


public class JDBCTest {

@ Test
public void testJDBC() throws Exception {
// 1. The load driver
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class. forName( "com.mysql.jdbc.Driver"); // Use this method directly : Because the bottom layer has implemented the above steps
// 2: Obtain a connection -----> Give Way java Program and database connection . Let the program relate to the database
Connection connection = DriverManager. getConnection( "jdbc:mysql://IP Address : Port number ( Default 3306)/ Database name ", "root", "root");
// 3: Get the preprocessing statement object --> Give Way java send out SQL Statement to database
Statement statement = connection. createStatement();
// 4. Do what needs to be done SQL sentence
statement. execute( "create table girl(id int , name varchar(20),age int);");
// 4: Release resources
statement. close();
connection. close();
}
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.

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

Java Development of JDBC Learning sharing _ Connection pool

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)

cn.wolfcode.smis.domain

Student.java

2. Definition DAO Packages and DAO Interface .

cn.wolfcode.smis.dao

IxxxDAO---> XXX Express domain.

IStudentDAO.java

3. Definition DAO The package of the implementation class and the implementation class

cn.wolfcode.smis.dao.impl

StudentDAOImpl.java

4. Test class

cn.wolfcode.smis.test

StudentDAOImplTest.java

5. Tool class

cn.wolfcode.smis.util

JDBCUtil.java

Java Development of JDBC Learning sharing _ Connection pool _02

Java Development of JDBC Learning sharing _ database _03

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:

Java Development of JDBC Learning sharing _java_04

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 .

common API:

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 .

Java Development of JDBC Learning sharing _ data _05

DQL operation : Additions and deletions

stay JDK in , How to parse the result set , Encapsulated in an object .ResultSet.

demand :

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

Java Development of JDBC Learning sharing _sql_06

Optimize :

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 .

Code :

The configuration file :

Java Development of JDBC Learning sharing _ Connection pool _07

Symbol javaBean Of Student class :

Java Development of JDBC Learning sharing _java_08

DAO Definition of interface :

Java Development of JDBC Learning sharing _java_09

Interface test class :

Java Development of JDBC Learning sharing _ data _10

JDBC Tool class of :

package cn. wolfcode. smis. util;


import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. ResultSet;
import java. sql. SQLException;
import java. sql. Statement;
import java. util. Properties;


// Tool class
public class MyJDBCUtil {
// Privatized constructor
private MyJDBCUtil() {
}


static Properties p = new Properties();
// Static code block
static {
try {
// Load profile
p. load( Thread. currentThread(). getContextClassLoader(). getResourceAsStream( "pd.properties"));
// Load drive
Class. forName( p. getProperty( "driverClassName"));
} catch ( Exception e) {
e. printStackTrace();
}
}


// Get the connection object
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager. getConnection( p. getProperty( "url"), p. getProperty( "username"),
p. getProperty( "password"));
} catch ( Exception e) {
e. printStackTrace();
}
return connection;
}


// close resource
public static void close( Connection connection, Statement statement) {
try {
if ( statement != null) {
statement. close();
}
} catch ( SQLException e) {
e. printStackTrace();
}
try {
if ( connection != null) {
connection. close();
}
} catch ( SQLException e) {
e. printStackTrace();
}


}


// Overload close resource
public static void close( Connection connection, Statement statement, ResultSet resultSet) {
try {
if ( statement != null) {
statement. close();
}
} catch ( SQLException e) {
e. printStackTrace();
}
try {
if ( connection != null) {
connection. close();
}
} catch ( SQLException e) {
e. printStackTrace();
}
try {
if ( resultSet != null) {
resultSet. close();
}
} catch ( SQLException e) {
e. printStackTrace();
}


}


}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.
  • 94.
  • 95.

Implementation class of interface :

package cn. wolfcode. smis. dao. impl;


import java. sql. Connection;
import java. sql. PreparedStatement;
import java. sql. ResultSet;
import java. util. ArrayList;
import java. util. List;


import cn. wolfcode. smis. dao. IStudentDAO;
import cn. wolfcode. smis. domain. Student;
import cn. wolfcode. smis. util. MyJDBCUtil;


public class StudentDAOImpl implements IStudentDAO {


@ Override
public void insert( Student student) {
// Get the connection object
Connection connection = MyJDBCUtil. getConnection();
// Get precompiled object
PreparedStatement statement = null;
try {
statement = connection. prepareStatement( "insert into student(id,name,age) values(?,?,?)");
// Set station character
statement. setObject( 1, student. getId());
statement. setObject( 2, student. getName());
statement. setObject( 3, student. getAge());
// perform
statement. execute();
} catch ( Exception e) {
e. printStackTrace();
} finally {
// close resource
MyJDBCUtil. close( connection, statement);
}
}


@ Override
public void delete( Long id) {
// Get the connection object
Connection connection = MyJDBCUtil. getConnection();
// Get precompiled object
PreparedStatement statement = null;
try {
statement = connection. prepareStatement( "delete from student where id = ?");
// Set station character
statement. setObject( 1, id);
// perform
statement. execute();
} catch ( Exception e) {
e. printStackTrace();
} finally {
// close resource
MyJDBCUtil. close( connection, statement);
}
}


@ Override
public void update( Student student) {
// Get the connection object
Connection connection = MyJDBCUtil. getConnection();
// Get precompiled object
PreparedStatement statement = null;
try {
statement = connection. prepareStatement( "update student set name = ? ,age = ? where id = ?");
// Set station character
statement. setObject( 1, student. getName());
statement. setObject( 2, student. getAge());
statement. setObject( 3, student. getId());
// Execute update method
statement. executeUpdate();
} catch ( Exception e) {
e. printStackTrace();
} finally {
// close resource
MyJDBCUtil. close( connection, statement);
}
}


@ Override
public Student selectOne( Long id) {
// Get the connection object
Connection connection = MyJDBCUtil. getConnection();
// Get precompiled object
PreparedStatement statement = null;
Student student = null;
try {
statement = connection. prepareStatement( "select * from student where id = ?");
// Set station character
statement. setObject( 1, id);
// Execute the query , Return result set
ResultSet resultSet = statement. executeQuery();
if ( resultSet. next()) { // There are elements in the result set
// Get the elements in the result set ----> The result set is the value corresponding to each field , Heard of the field name to get the value , Then create an object to return
Object name = resultSet. getObject( "name");
Object age = resultSet. getObject( "age");
// Create objects
student = new Student( id, ( String) name, ( Integer) age);
}
} catch ( Exception e) {
e. printStackTrace();
} finally {
// close resource
MyJDBCUtil. close( connection, statement);
}
return student;
}


@ Override
public List < Student > selectAll() {
// establish List
List < Student > list = new ArrayList < >();
// Get the connection object
Connection connection = MyJDBCUtil. getConnection();
// Get precompiled object
PreparedStatement statement = null;
try {
statement = connection. prepareStatement( "select * from student");
// Execute the query , Return result set
ResultSet resultSet = statement. executeQuery();
// if (resultSet.next()) {// There are elements in the result set
// use while The loop takes out all the elements in the result set
while ( resultSet. next()) {
// Get the elements in the result set ----> The result set is the value corresponding to each field , Heard of the field name to get the value , Then create an object to return
Object id = resultSet. getObject( "id");
Object name = resultSet. getObject( "name");
Object age = resultSet. getObject( "age");
// Create an object and add it to list in
list. add( new Student(( Long) id, ( String) name, ( Integer) age));
}
} catch ( Exception e) {
e. printStackTrace();
} finally {
// close resource
MyJDBCUtil. close( connection, statement);
}
return list;


}


}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.
  • 94.
  • 95.
  • 96.
  • 97.
  • 98.
  • 99.
  • 100.
  • 101.
  • 102.
  • 103.
  • 104.
  • 105.
  • 106.
  • 107.
  • 108.
  • 109.
  • 110.
  • 111.
  • 112.
  • 113.
  • 114.
  • 115.
  • 116.
  • 117.
  • 118.
  • 119.
  • 120.
  • 121.
  • 122.
  • 123.
  • 124.
  • 125.
  • 126.
  • 127.
  • 128.
  • 129.
  • 130.
  • 131.
  • 132.
  • 133.
  • 134.
  • 135.
  • 136.
  • 137.
  • 138.
  • 139.
  • 140.
  • 141.
  • 142.
  • 143.
  • 144.
  • 145.
  • 146.
  • 147.
  • 148.
  • 149.
  • 150.

The above code is still repeated : The template method can be further optimized .

Injection problem : Classic case login security issues ;

Java Development of JDBC Learning sharing _ Connection pool _11

@ Test
public void testLogin() throws Exception {
// Simulate account login , understand statement and preparestatement The difference between
String username = "will";
// String password = "123456";
// Injection problem
String password = "' or '1'='1";
// loginByStatement(username, password);
// loginByPrepareStatement(username, password);
loginByPrepareStatement1( username, password);
}


// No preprocessing statement object is used : unsafe String password = "' or '1'='1"; It can shield SQL The semantics of the statement . Result in successful login
private void loginByStatement( String username, String password) throws Exception {
// verification username and password Tables in the database Whether this data exists
Connection connection = JDBCUtil. getConnection();
Statement statement = connection. createStatement();
String sql = "select * from user where username = '" + username + "' and password = '" + password + "'";
ResultSet resultSet = statement. executeQuery( sql);
if ( resultSet. next()) {
System. out. println( " Login successful ");
} else {
System. out. println( " Wrong account or password , Login failed ");
}
JDBCUtil. close( connection, statement, resultSet);
}


// Simulated Login case ---> Use preprocessing objects : Security
private void loginByPrepareStatement1( String username, String password) throws Exception {
// Compare the parameters with the customer information in the database , You can log in successfully only if you are the same
// 1 Connect to database
Connection connection = JDBCUtil. getConnection();
// 2 Get the preprocessing statement object
PreparedStatement statement = connection
. prepareStatement( "select * from user where username = ? and password = ?");
// 2.1 Set up a place holder
statement. setObject( 1, username);
statement. setObject( 2, password);
// 3 Query results
ResultSet resultSet = statement. executeQuery();
// Judge whether there is this user data in the database
if ( resultSet. next()) {
System. out. println( " Login successful !");
} else {
System. out. println( " Account does not exist ! Login failed !!");
}
// close resource
JDBCUtil. close( connection, statement, resultSet);
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.

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 .

Java Development of JDBC Learning sharing _java_12

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 :

try{
// Start a transaction manually
conn. setAutoCommit( false);
operation 1 ...
operation 2 ...
operation N ...
// All success , Commit transaction
conn. commit();
} catch( Exception e){
// Exception code handling
// If you fail , Need to roll back
conn. rollback();
} finally{
// close resource
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

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 .

@ Test
public void testTransaction() {
// Simulate the effect of transfer Simon blow snow Transfer to Pineapple blows snow 1000 money . If there is an exception in the transfer process , Then the amount should not change .// 1: First judge Simon blow snow Is there any money
Connection connection = JDBCUtil. getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
// Cancel auto submit , Let complete things be submitted after execution
connection. setAutoCommit( false);
statement = connection. prepareStatement( "select * from account where name = ' Simon blow snow ' and balance >= 1000");
resultSet = statement. executeQuery();
// Only if the amount is enough can you transfer
if ( resultSet. next()) {
System. out. println( " Start transferring money ");
// Simon blowing snow money Reduce 1000
statement. executeUpdate( "update account set balance = balance - 1000 where name = ' Simon blow snow '");
// int ret = 10 / 0;// Make an exception ----> If there is an exception in the transfer process , Then the amount should not change .// Pineapple blowing snow Golden Lotus money increase 1000
statement. executeUpdate( "update account set balance = balance + 1000 where name = ' Pineapple blows snow '");


// Commit transactions manually
connection. commit();
} else {
System. out. println( " Don't have the money !!!, My second uncle is the director of public security ");
}
} catch ( Exception e) {
System. out. println( " Abnormal transfer interruption !");
e. printStackTrace();
// Back off : When something unusual happens , Fallback the transaction to the initial state .----> Fallback is required only when an abnormal interrupt occurs , So put it in catch In the sentence
try {
connection. rollback();
} catch ( SQLException e1) {
e1. printStackTrace();
}
} finally {
// close resource : In any case, close the resource in the end
JDBCUtil. close( connection, statement, resultSet);
}
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.

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 .

Statement.RETURN_GENERATED_KEYS .

2. Get the auto generated primary key .

// After simulating registration, display id And the information at the time of registration
@ Test
public void testInsert() throws Exception {
// Information entered during registration
String username = " Tianhe's richest man ";
String password = "lh0030";
// Get the connection object
Connection connection = JDBCUtil. getConnection();
String sql = "insert into user (username,password) values(?,?)";
// Get the preprocessing statement object
PreparedStatement statement = connection. prepareStatement( sql, Statement. RETURN_GENERATED_KEYS); // Get the automatically generated primary key
// Set the value of the placeholder
statement. setObject( 1, username);
statement. setObject( 2, password);
// Perform an update operation
statement. executeUpdate();
// Put the automatically generated primary key into a result set object
ResultSet keys = statement. getGeneratedKeys();
// If the insertion is successful, the primary key and the inserted information are returned
if ( keys. next()) {
// Get the auto generated primary key
long id = ( long) keys. getObject( 1);
System. out. println( id + " " + username + " " + password);
}
// close resource
JDBCUtil. close( connection, statement, keys);
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.

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 conn = DriverManager. getConnection( url, username, password);
  • 1.

Connection pool is used :

datasource object . getConnection(); // The returned connection object is already an enhanced connection object .
  • 1.

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 .

Java Development of JDBC Learning sharing _ database _13

Use database connection pool to create JDBC Tool class of :

The way 1:

import java. io. IOException;
import java. sql. Connection;
import java. sql. DriverManager;
import java. sql. ResultSet;
import java. sql. SQLException;
import java. sql. Statement;
import java. util. Properties;


//JDBC Tool class
public class JDBCUtil {
// Privatized constructor
private JDBCUtil() {
}


static Properties p = new Properties();
static {
try {
p. load( Thread. currentThread(). getContextClassLoader(). getResourceAsStream( "db.properties"));
Class. forName( p. getProperty( "driverClassName"));


} catch ( IOException e) {
System. err. println( " Your configuration file could not be loaded ");
e. printStackTrace();
} catch ( ClassNotFoundException e) {
e. printStackTrace();
}


}


// Get the connection object of the database
public static Connection getConnection() {
// get In the Resources folder db.properties Database connection information in the file
Connection connection = null;
try {
connection = DriverManager. getConnection( p. getProperty( "url"), p. getProperty( "username"),
p. getProperty( "password"));
} catch ( SQLException e) {
e. printStackTrace();
}
return connection;
}
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.

The way 2:

import java. io. IOException;
import java. sql. Connection;
import java. sql. ResultSet;
import java. sql. SQLException;
import java. sql. Statement;
import java. util. Properties;
import javax. sql. DataSource;


import com. alibaba. druid. pool. DruidDataSourceFactory; // Druids jar package


//JDBC Tool class
public class JDBCUtil {
// Privatized constructor
private JDBCUtil() {
}


static DataSource source = null;
static {
try {
Properties p = new Properties();
p. load( Thread. currentThread(). getContextClassLoader(). getResourceAsStream( "db.properties"));
// Create objects this way
source = DruidDataSourceFactory. createDataSource( p);
} catch ( IOException e) {
e. printStackTrace();
} catch ( Exception e) {
e. printStackTrace();
}
}


// Get the connection object of the database
public static Connection getConnection() {
Connection connection = null;
try {
connection = source. getConnection();
} catch ( SQLException e) {
// TODO Auto-generated catch block
e. printStackTrace();
}
return connection;
}
// Omit the closing operation , The flow has not changed
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.

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

版权声明
本文为[It Xiaoshang]所创,转载请带上原文链接,感谢
https://javamana.com/2021/11/20211109104304616y.html

  1. Fonction de flèche JavaScript
  2. JavaScript - déconstruction assignations
  3. Building CentOS 7.6 with Linux
  4. JavaScript - type d'emballage
  5. linux deepin/ubuntu安装flameshot火焰截图
  6. JavaScript - encapsulation et héritage (deux)
  7. JavaScript JS method for writing 99 multiplication table
  8. 從零開始學java - 第二十五天
  9. Apprendre Java à partir de zéro - jour 25
  10. Les voitures d'hiver, les voitures électriques et les voitures à essence ne sont pas les mêmes?
  11. JavaScript - ceci pointe vers le problème
  12. Copie JavaScript
  13. Spring boot quickly integrates swagger
  14. linux deepin/ubuntu安裝flameshot火焰截圖
  15. Capture d'écran de flamme de l'installateur de flamme Linux deepin / Ubuntu
  16. Jquery DOM et jquery, fonctions d'entrée (bases)
  17. Méthode d'instance jquery
  18. Méthode et démonstration de code dans l'interface de liste en Java
  19. 【错误记录】Java 中 ArrayList 排序 ( 使用 Comparator 接口时注意 compare 返回值是 -1 和 +1 )
  20. Démarrage du Zookeeper
  21. Java oom Cognition
  22. Java 开发者最困惑的四件事,值得一看!,BAT面试文档
  23. Java 将两个对象list里面的 某个字段值抽取到一个list里,java选择排序原理
  24. Java 多线程 —— 生产者消费者问题,从基础到深入案例
  25. Java 中设计模式 之 工厂模式,java反射机制的底层原理
  26. 【錯誤記錄】Java 中 ArrayList 排序 ( 使用 Comparator 接口時注意 compare 返回值是 -1 和 +1 )
  27. 【 enregistrement des erreurs 】 tri ArrayList en Java (Notez que les valeurs de retour de comparaison sont - 1 et + 1 lors de l'utilisation de l'interface de comparaison)
  28. Pourquoi Xiaopeng P5 est - il devenu un grand succès dans le cercle? Le Salon de l'automobile de Guangzhou montre encore des compétences polyvalentes
  29. Java 開發者最困惑的四件事,值得一看!,BAT面試文檔
  30. Les quatre choses les plus déroutantes pour les développeurs Java valent la peine d'être regardées! Document d'entrevue sur les MTD
  31. k8s-Pod污点与容忍
  32. k8s-Pod污点与容忍
  33. K8s POD Contamination and Tolerance
  34. K8s POD Contamination and Tolerance
  35. mysql常用语句——GROUP BY和HAVING
  36. Spring boot quickly integrates swagger
  37. mysql常用語句——GROUP BY和HAVING
  38. MySQL Common statements - group by and having
  39. Le défi du pinceau leetcode - Javascript: 110. Arbre binaire équilibré
  40. [notes d'apprentissage de première ligne] day44: XMLHttpRequest
  41. java调用RFC延长调用时间
  42. java調用RFC延長調用時間
  43. Java call RFC prolonge le temps d'appel
  44. Le modèle d'usine du modèle de conception en Java, le principe sous - jacent du mécanisme de réflexion Java
  45. Java Multithreading - producer Consumer issues, From Basic to Deep case
  46. OushuDB 安装与升级之安装 HDFS
  47. OushuDB 安装与升级之安装 Zookeeper
  48. Java-String-对象,你真的了解了吗?,mysql教程入门到精通
  49. Linux引导过程与服务控制
  50. Java-8新特性:学习如何使用Lambda表达式(一,线程池的实现原理
  51. Java 读写锁 ReentrantReadWriteLock 源码分析,headfirstjavapdf百度云
  52. JAVA 获取系统日期时间,初级java开发常见的面试题
  53. MySQL judges the salary changes based on the existing data
  54. k8s-Pod污點與容忍
  55. Java extrait une valeur de champ de deux listes d'objets dans une liste, et Java sélectionne le principe de tri
  56. java中ArrayList应用实例,阿里巴巴java面试流程
  57. Java个人技术知识点总结(框架篇),kafka聊天服务器架构
  58. JavaWeb快速入门--Servlet(2),java程序设计精编教程第三版上机实践
  59. JavaWeb - 文件的上传,核心API的使用,文件下载,附学习笔记 面试整理 进阶书籍
  60. Netty原理:pipeline