JDBC related summary

What is what is JDBC?

JDBC (Java Database Connectivity) is a specification (standard) for Java connection databases. You can use the Java language to connect databases to complete CRUD operations.

1, Seven steps of native JDBC

1. register driver
2. get the database connection object
3. prepare SQL statements
4. execute SQL statement
5. operation database
6. get the return value
7. release resources

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

//jdbc operation steps
public class JdbcDemo {
    public static void main(String[] args) {

        Statement stmt = null ;
        Connection conn = null ;
        try {
            //1) Import package, register driver--
            //Class.forName("com.mysql.jdbc.Driver") ;

            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
            //2) Get the connection object of the database
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/myee_2203_02",
                    "root",
                    "123456"
            );
            //3)sql
            String sql = "update account set name = 'You Ting Zhao' where id = 2  " ;
            
            //4) Connect object to get execution object Statement
            stmt = conn.createStatement();
            
            //5) Perform update operation
            int count = stmt.executeUpdate(sql);
           
            //6) Output results
            System.out.println("Affected"+count+"line");


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           
            //7) Free resources
            try {
                if(stmt!=null){
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if(conn!=null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2, JDBC Statement and PreparedStatement are two different operation processes

Statement operation process
Send the written SQL statement to the database

java.sql.Statement:Execute static sql sentence,in the light of DDL sentence(Build table),DML sentence(insert into,delete ,update)
                insert into Table name values(Value 1,Value 2,Value 3...) ;   sql Statement directly write dead
                
                General method:
                        int executeUpdate(String sql):Common update operations for DDL,DML sentence
                        ResultSet executeQuery(String sql):Actions on query statements DQL sentence

PreparedStatement inherits the Statement interface and executes SQL statements in the same way.
effect:
Precompiled SQL statements are efficient.
Safe to avoid SQL injection.
It can dynamically fill data and execute multiple isomorphic SQL statements.

PreparedStatemen operation process

//1. precompiled SQL statements
PreparedStatement pstmt = conn.prepareStatement("select * from user where username=? and password=?");
//2. assign values to parameter Subscripts
pstmt.setString(1,username);
pstmt.setString(2,password);

Difference between Statement object and PreparedStatement object: interview question

1) Differences in sql execution efficiency

Every time the Statement needs to send the static sql to the database, write an sql and send it once. Compared with the PreparedStatement precompiled object, the PreparedStatement will compile the parameterized sql (placeholder?) Send it to the database. The database will verify its column index value and corresponding type. Save the sql to the precompiled object. You can assign different parameters to execute the corresponding sql. The execution efficiency is much higher than that of the Statement!

2) Whether it will cause "SQL Injection"

The sql sent by the Statement object is a static sql Statement. There is "hard coding" and string splicing, which will cause sql injection. It is a very unsafe behavior! The sql sent by the PreparedStatement object is parameterized sq1. There is no string splicing, which will not cause sql injection. It is a safe behavior relative to the Statement!

matters needing attention:

PreparedStatement is used to encapsulate the underlying object-oriented jdbc of the late framework (semi-finished products: a pile of general code + a pile of configuration files)

3, Join connection pool operation of JDBC (prevent high concurrency)

Background: in the case of high energy, many people need to access the server. Each time, they need to use jdbc to frequently obtain the connection object and complete the user login operation. In memory, you need to create linked objects constantly, which consumes a lot of memory and may lead to memory overflow

Solution: join the database connection pool in JDBC
An interface provided by sun company: java sql. Datasource: datasource
Implementation class of the corresponding data source provided by the database manufacturer - >jar package ---------- Druid (Druid)
DruidDataSource: many connection pool parameters are stored in the Druid data source
Configuration file: druid properties
initialSize maximum connections
maxActive maximum presence
maxWait wait a certain time (MS) after the maximum number of connections is exceeded
Basic information about connecting to the database
Operation process:
1) Import package
druid-1.1.10.jar
2) Get the database connection object - configure the configuration file of the database connection pool in advance
druid.properties
The content key of the configuration file is the parameter information in the DruidDataSource
Configure the basic information of the database
Configure other connection information
Initialization quantity
initialSize
Maximum number of activations
Minimum free quantity: minIdle
Maximum idle quantity: maxIdle
activeCount
Maximum wait time (wait time exceeding the maximum number of connections: millisecond value)
maxWait
maxActive: maximum activation quantity
3) Read configuration file
4) Get the connection object of the database

public class DruidDemo {
    public static void main(String[] args) throws Exception {

        //1) Read configuration file
        InputStream inputStream = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");

        //2) Create datasource data source object: java sql. Datasource interface
        //DruidDataSourceFactory is the data source factory of Druids
        //There is one method: protected datasource createdatasourceinternal (properties)
        //Create an attribute collection list class object
        Properties prop = new Properties() ;
        //Load stream object
        prop.load(inputStream);

        //DruidDataSourceFactory data source factory
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
            //Get connection object

            for(int i = 1; i <=11; i++){
                Connection connection = dataSource.getConnection();
                if(i==3){
                    //Release the third connection object
                    connection.close() ; //om.mysql.jdbc.JDBC4Connection@17ed40e0
                }
                System.out.println(connection);
            }

//Benefits of database connection pooling:
//     1) The number of connections can be initialized. When a thread uses a connection object, the connection object will be held by the thread
//Yes, when this thread ends, release the connection object and return the connection object to the database connection pool for next utilization!

    }
}

4, Use of Commons dbutils

In the DAO layer, there is code redundancy in the operations of adding, deleting, modifying and querying database tables, which can be extracted and encapsulated. The DaoUtils tool class implements duplication
Use.
commonsUpdate

/**
* Methods for adding, deleting and modifying public processing
* sql Statement, parameter list
*
* @param sql Executed sql statements
* @param args Parameter list. Assign values to placeholders
* @return
*/
public int commonsUpdate(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(null, preparedStatement, null);
}
return 0;
}

commonsSelect

/**
* Public query method (you can query a single object or multiple objects, and you can query any table)
*
* @param sql
 18, Druid connection pool
 When the program is initialized, a specified number of database connection objects are created in advance and stored in the pool. When you need to connect to the database, take it out of the connection pool
 Existing connections; After use, it will not be closed, but put back into the pool to realize reuse and save resources.
18.1 Druid Connection pool usage steps
* @param args
* @return
*/
// select * from t_account
// select * from t_student
//The tool doesn't know what the query is. The caller knows
//The encapsulated object and object assignment are clear to the caller
public List<T> commonsSelect(String sql, RowMapper<T> rowMapper, Object... args) {
List<T> elements = new ArrayList<T>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DBUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
if(args !=null){
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
//How to create and assign objects when ORM is completed according to the query results?
T t = rowMapper.getRow(resultSet);//Callback ----- > an encapsulation method ORM provided by the caller
elements.add(t);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils.closeAll(null, preparedStatement, resultSet);
}
return elements;
}

5, jdbc management transaction

In JDBC, get the Connection object to start the transaction – commit or rollback – close the Connection. Its transaction strategy is
conn.setAutoCommit(false);//true is equivalent to 1, false is equivalent to 0
conn.commit();// Manually commit transactions
conn.rollback();// Manually rollback transactions

package com.qf.day44.accounts;
import java.sql.Connection;
import java.sql.SQLException;
public class T_AccountServiceImpl {
/**
* Transfer business
*
* @param fromNo Transfer card No
* @param pwd Transfer card number password
* @param toNo Charge card No
* @param money Transfer amount
*/
public String transfer(String fromNo, String pwd, String toNo, double money) {//Collect parameters
String result = "Transfer failed!";
//2. organization business functions
T_AccountDaoImpl accountDao = new T_AccountDaoImpl();
//Get a connection
Connection connection = null;
try {
//Established a database connection
connection = DBUtils.getConnection();
//Open transaction! And turn off automatic submission of transactions
connection.setAutoCommit(false);
//2.1 verify whether fromNo exists
T_Account fromAcc = accountDao.select(fromNo);
if (fromAcc == null) {
throw new RuntimeException("----Card number does not exist-----");
}
//2.2 verify whether the password of fromNo is correct
if (!fromAcc.getPassword().equals(pwd)) {
throw new RuntimeException("----Password error----");
}
//2.3 verify whether the balance is sufficient
if (fromAcc.getBalance() < money) {
throw new RuntimeException("----Sorry, your credit is running low----");
}
//2.4 verify whether toNo exists
T_Account toAcc = accountDao.select(toNo);
if (toAcc == null) {
throw new RuntimeException("----Opposite card number does not exist----");
}
//2.5 decrease the balance of fromNo
//Modify your own amount and replace the original attribute of balance transfer amount
fromAcc.setBalance(fromAcc.getBalance() - money);
accountDao.update(fromAcc);
int a = 10/0;//The transfer of simulation program is abnormal!
//2.6 add toNo balance
toAcc.setBalance(toAcc.getBalance() + money);
accountDao.update(toAcc);
result = "Transfer succeeded!";
//If there is no exception after execution, the transaction will be committed!
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
//An exception occurred. Rollback!
System.out.println("An exception has occurred! Rollback the entire transaction!");
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
DBUtils.closeAll(connection,null,null);
}
return result;
}
}

Encapsulate the start, commit and rollback of transactions in the tool class, and the business layer can call them

//Open transaction
public static void begin(){
Connection connection = getConnection();
try {
connection.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
}
//Commit transaction
public static void commit(){
Connection connection = getConnection();
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,null,null);
}
}
//Rollback transaction
public static void rollback(){
Connection connection = getConnection();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(connection,null,null);
}
}

Tags: Database SQL Java

Posted by bmw57 on Wed, 01 Jun 2022 06:10:20 +0530