Location>code7788 >text

JDBC, SQL injection, transactions, C3P0 in the Druid connection pool (the most detailed analysis)

Popularity：384 ℃/2024-09-07 03:53:04

JDBC

JDBC (Java DataBase Connectivty, Java Database Connectivity) API, is a Java API for the execution of Sql statements , you can provide a unified access to relational databases , which consists of a set of Java classes and interfaces .

JDBC Driver

At first, SUN introduced the JDBC API hope that it can be applied to all databases, but in practice it is not possible to realize.The differences in the databases offered by the various vendors were so great that SUN, in coordination with the database vendors, decided that.SUN provides a set of access to the database API, each vendor according to the specification to provide a set of access to their own database API interface.The canonical API provided by SUN is calledJDBCThe vendor-provided API interface to its own database is called thedrive (vehicle wheel)

JDBC Principle

  • After SUN's collaboration with various database vendors, the structure of JDBC is shown below.

Principles of JDBC Driver

We know the principle of JDBC operation, then the JDBC driver is how to run it, we began to explore

  • Types of JDBC Drivers

    According to access the database database technology is different, JDBC driver is accessed in four categories

    • Type1: JDBC-ODBCBridge Driver
      • This type of driver consists of a JDBC-ODBC bridge amount an ODBC driver.
    • Type2:Part of the Java native JDBC API driver
      • This type of driver must be installed on the local computer before it can be used.
    • Type3: pure Java database middleware driver (the current mainstream)
      • Pure Java Driver for Database MiddlewareWhen using this type of driver, there is no need to install any additional software on the local computer, but middleware must be installed on the server where the database management system is installed.(Middleware) This middleware takes care of all the necessary transformations for accessing the database.Middleware works as follows.The driver converts the JDBC access to a database-independent standard network protocol (usually HTTP or HTTPS) and sends it out to the middleware server, which then converts it into database-specific access commands to complete the operation of the database. Middleware can support a variety of database access.
    • Type4: pure Java JDBC driver (the ideal driver)
      • Direct-toDatabasePureJavaDriver These drivers are database-oriented Java drivers, the so-called "thin" driver. The use of this driver does not require the installation of additional software (including the local computer or database server), all access to the database operations are done directly by the JDBC driver.

  • JDBC Driver Job Action

    • For the third type of Type3 driver, which is developed by the pure Java language, this type of driver is the smallest size
      • The structure of the Type3 driver is given below

    - As can be seen , Type 3, JDBC driver for the two-tier structure , respectively, for the driver client and driver server , the client directly interacts with the user , ** which provides users with the database in line with the JDBC specification of the Unified Programming Interface , ** the client will be the data request through ** a specific network request ** to send the value of the server . Server as the role of middleware , which is responsible for receiving and processing user requests , JDBC driver itself does not directly interact with the database , but with the help of other drivers that have been implemented , known as "employment". Of course, the more the number of "hired", the more the number of databases can support ." The number of "hires" and the members can be dynamically changed to meet the business expansion, which is the Type3JDBC powerful performance reasons

Importing a JDBC driver into idea (using Mysql as an example)

  1. The first step is definitely to download the JDBC driver, each vendor to the specific driver packaged into a .jar released on its official website you can download to the official website!

    • Commonly used major manufacturers to download the address of the driver

      1. Mysql database

        /downloads/connector/

      2. Oracle database

        /database/technologies/appdev/

      3. SQL Server Database

        /zh-cn/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017

    • Mysql Driver Download

      • By selecting Connector/J→Options select Platform Independent→Download according to your computer configuration.

    Once downloaded, unzip the file into a folder.
  1. Open idea, create a new folder named lib in the project where you want to import the driver (suggested name).

  1. Unzip the downloaded zip file, find the mysql-connector-java-8.0.package, and then unzip it.Copy it

  1. Copy the mysql-connector-java-8.0.file, right-click on the lib folder, and paste it into the new lib folder you just created in IDEA.

Enabling imported drivers

  1. In idea click File→Project Structure

  1. Select Denpendencies in Modules

  1. Click on the + sign on the left and select JARS or directories.

  1. In the pop-up window, select the driver you just imported into the lib folder and click Ok.

  1. You can see that in the Module module, there is an additional mysql driver, select it and click Apply, then Ok

Classes in JDBC and their applications

JDBC API contains four commonly used interfaces and a class, respectivelyConnectionInterfaces,StatementInterfaces,PreparedStatementInterfaces,ResultSetInterfaces,DriverManagerclass, the jar package already contains the implementation of these interfaces directly to use the class

Statement interface

Statement interface is an important interface for Java programs to perform database operations , used to have established a database connection on the basis of the database to send to the database to perform the Sql statements

  • corresponds English -ity, -ism, -ization:Execute simple Sql statements without parameters
  • Primary methodology
    • void addBatch*(* String sql *)* throws SQLException : This method is used to add the Sql statement to the current list of commands in the Statement object.For batch processing of Sql statements
    • void clearBatch() throws SQLException:: Immediatelyliberate (a *er)List of commands in the Statement object
    • boolean excute(String sql) throws SQLException :Execute the specified Sql statement,Successful returntrueOtherwise, returnfalse
    • int[] excuteBatch() throws SQLException:Commit the sql commands in the command list for execution, return an int array indicating the number of rows affected by each sql statement
    • ResultSet excuteQuery(String sql) throws SQLException: This method is used forExecutes a Sql statement of query type (Select type).The result set obtained by the returned queryResultSetboyfriend
    • void close() throws SQLException:: Database and JDBC resources for immediate release of this Statement object

Connection interface

Connection interface is located in the package, is used to connect to the database object, only after obtaining the object connected to the database, you can access the database for operations

  • corresponds English -ity, -ism, -ization:: Connection to the database

  • Primary methodology

    • Statement createStatement*()* throws SQLException:: Used to create a Statement object, used to execute the Sql statement

    • PreparedStatement prepareStatement*(*String sql*)* throws SQLException: Create a PreparedStatement object for executing precompiled Sql statements.

    • CallableStatement prepareCall*(*String sql*)* throws SQLException: Create a CallableStatement object for executing a procedure or function.

    • void commit*()* throws SQLException:: Commit the current transaction

    • void rollback*()* throws SQLException:: Rollback transactions

    • void close*()* throws SQLException:: Close connection

      When making a database connection, you also need to use the DriverManager class'sgetConnection(url,username,password)methodologies

    :

            String url = "jdbc:mysql://localhost:3306/demo";
        String username = "root";
        String password = "root";
        //establish a connection
        Connection connection = (url, username, password);
        String sql = "SELECT * FROM dept";
        //establishStatementObjects perform query operations
        Statement statement = ();
        ResultSet resultSet = (sql);
        //Processing of search results
        while (()) {
            String dId = ("d_id");
            String dName = ("d_name");
            String loc = ("loc");
            (dId + " " + dName + " " + loc);
        }
        ();
        ();
        ();
    }

DriverManager class

DriverManager class is the core of the JDBC API, the class contains methods for interacting with the database, the methods in the class are provided by the database vendor.

  • Role.Managing and coordinating different JDBC drivers
  • Primary methodology
    • public static Connection getConnection(String url, String user, String password)throws SQLException: Establish a database connection based on the specified database url, user name and password.
    • public static Connection getConnection(String url,Properties info): Establish a database connection based on the specified database url and connection properties.
    • public static synchronized void deregisterDriver(Driver driver) throws SQLException: Delete a driver from the DriverManager management list, the driver parameter is the driver object to be deleted.

PreparedStatement interface

PreparedStatement interface is located in the package, which inherits the Statement interface

  • Difference with Statement.
    • Faster execution.PreparedStatement objects are pre-compiled and execute faster than Statements.Therefore, if you want to execute a large number of Sql statements, use PreparedStatement to increase efficiency.
  • Primary methodology

    • setXXX()

      These methods set the type of the parameter passed in the sql statement.

      • void setBinaryStream(int parameterIndex,InputStream x) throws SQLException: binary stream as a parameter passed to the sql statement, binary streams can be used to efficiently process images, audio, video, and other media.parameterIndex is the parameter position index
      • void setBoolean(int parameterIndex,boolean x) throws SQLException: boolen as the type of parameter passed to the sql, parameterIndex for the parameter position index
      • void setByte(int parameterIndex,byte x) throws SQLException: byte as the type of parameter passed in by sql
      • void setDate(int parameterIndex,Date x) throws SQLException: Make the value x a parameter value in the SQL statement
      • void setDouble(int parameterIndex,double x) :Make the double value x the value of the parameter in the SQL statement
      • void setInt(int parameterIndex,int x) throws SQLException :Make the int value x a parameter value in the SQL statement
      • void setObject(int parameterIndex,Object x) throws SQLException :Make object object x the value of the parameter in the SQL statement
      • void setString(int parameterIndex,String x) throws SQLException: String value x as a parameter value of a SQL statement
      • void setTimestamp(int parameterIndex,Timestamp x) throws SQLException: Make the value x a parameter value in the SQL statement

    • int executeUpdate() throws SQLException:executeUpdate() method returned by theint The value indicates the number of rows affected. If the return value is 0, it may indicate that no eligible rows were modified; execute theINSERT、UPDATE maybeDELETEThe same applies to these DML statements

ResultSet interface

is used to receive the results of the query, is the result of the collection, when you execute a SELECT statement DBMS will return a query containing the results of the data table, ResultSet to receive the performance of this data table object

  • Role.Indicates the return value of the query
  • Primary methodology
    • boolean next() **throws SQLException: Moves the cursor to the next line in the result set and returns a boolen value, ending with false.
    • getXXX(String columnLabel): Get the value of the specified column name , XXX that the Java data class to return XXX type , such as getString ().columnLabelIndicates the name of the column
    • getXXX(int columnIndex): Gets the value of the specified column index, starting at 1.

SQL injection

The so-called SQL injection, is the value of the malicious SQL statement inserted into the Web form submission or page request query string, and ultimately achieve the result of spoofing the server.

SQL Injection Example

  • For a simple login function, the key functions are as follows.
 static boolean noProtectLogin(String username, String password, Statement statement) throws SQLException {
        //username="abc";
        //password = "or '1'='1'";
        String sql = "SELECT *FROM  user WHERE username= '" + username + "'AND password=+''";
        ResultSet resultSet = (sql);
        return ();
    }
  • The username and password in the method are not processed, directly accepting the incoming data from the front-end, so that the spliced SQL statement will be sent to the injection vulnerabilities
  • If you change the password parameter to"or '1'='1'", username is any value, then the result of this statement isSELECT *FROM user WHERE username= 'abc' AND password= 'or '1'='1''Obviously, this statement is always true, so you can query all the user information in the user table, you can successfully realize the passwordless login

SQL pre-compilation

Also known as SQL preprocessing is a technique that can improve the security and performance of sql statements.

  • Pre-compiled SQL statements allow the structure of the SQL statement to be defined before it is run, using placeholders (usually question marks).?) to dynamically represent the data part

  • In Java you can use thePreparedStatementto create and execute pre-compiled SQL statements, just logged in using thePreparedStatementThe code for the operation is as follows

    static boolean noProtectLogin(String username, String password, Connection connection) throws SQLException {
//      username="abc";
//      password = "or '1'='1'";
        String sql = "SELECT * FROM  user WHERE username= ? AND password = ? ";
        PreparedStatement statement = (sql);
        (1, username);
        (2, password);
        ResultSet resultSet = ();
        return ();
    }
    • This way we can perform a malicious SQL injection, such as defining the password as"or '1'='1'" The result of the execution returns false directly

(political, economic etc) affairs

What is a transaction? The official term: transaction is a sequence of operations to access the database, the database application system through the execution of the business set to complete the access to the database, in short: the transaction is a sequence of operations to access the database, the database application system through the execution of the business set to complete the access to the database, in short.A transaction is the smallest indivisible unit of work in the execution of a work operation.Usually a business corresponds to a transaction, multiple operations at the same time either succeed at the same time, or fail at the same time, this is a transaction.

Understanding of affairs

Characterization of transactions

  • Atomicity: i.e., indivisibility, a transaction is either executed in its entirety or not executed at all.. If all transactions are committed successfully, then the database operation is committed and the database state is changed, theIf a sub-transaction fails, the database operations of the remaining transactions are rolled back.That is, the database state returns to the state before the transaction is executed, keeping the state unchanged.
  • Consistency: The execution of a transaction causes the database to transition from one correct state to another.
  • Isolation: until the transaction is correctly committed, the transaction's changes to the data are not allowed to be committed to other transactions, i.e., the possible results should not be used for other transactions until they are correctly committed.
  • persistence: that is, after the transaction is correctly submitted, the results will always be saved in the database, that is, after the transaction is submitted to other failures, the results of the transaction will also be preserved

Common examples of transactions

  • Suppose that Zhang San wants to transfer money to Li Si, to complete this operation, two transactions should be executed, one is: debit Zhang San's account balance; the other is: Li Si's account balance increaseThe two services are inseparable.

Role of transactions

  • The main role: to ensure that the user's every operation is reliable, even if there is an abnormal access situation, but also will not destroy the integrity of the data backstage, take the ATM machine as an example, if the ATM in the process of operation suddenly appeared failure, at this time, the transaction must ensure that before the failure of the account number of the operation does not come into effect, to ensure that the user in the interests of the bank will not be damaged!

Managing things in JDBC

In JDBC, the Connection interface defines several methods of transaction operations, we explain one by one

  • void setAutoCommit*(*boolean autoCommit*)* throws SQLException: By default, JDBC connections are in autocommit mode, which means that every SQL statement will be committed immediately after execution, which is obviously not in line with the characteristics of the transaction, we have to explicitly close, that is, theautoCommitSet to false
  • void commit(): If all operations are successful, call this method to commit the transaction.
  • void rollback(): If any operation in the transaction fails or an abnormal error occurs then you need to call therollback()Rolling back transactions

Examples of bank deposits/withdrawals

Take the example of Zhang San and Li Si just now to illustrate that

try {
            String sql1 = "UPDATE bank SET balance = balance - ? WHERE b_id=?";
            String sql2 = "UPDATE bank SET balance = balance + ? WHERE b_id=?";

            stmt1 = (sql1);
            stmt2 = (sql2);
            //(political, economic etc) affairs1fulfillment

            (1, 500);
            (2, "01");
            int r1 = ();
            //(political, economic etc) affairs2fulfillment

            (1, 500);
            (2, "02");
            int r2 = ();

            if (r1 == 1 && r2 == 1) {
                ("业务fulfillment成功");
                ();
            } else {
                ("业务fulfillment失败");
                ();
            }
        } catch (SQLException e) {
            ();
            //有异常回滚(political, economic etc) affairs
            ();
        } finally {
            (stmt1).close();
            (stmt2).close();
            ();
        }

connection pool

Connection pooling is the technique of creating and managing database connections that are ready to be used by any thread that needs them.

Principles of Connection Pooling

  • The basic idea of connection pooling is to connect to the database during system initialization.Stored as an object in running memory,When a user needs to access a database, instead of creating a new connection, the user takes a free connection from the connection pool. When finished, the user does not close the connection, but puts it back into the connection pool for the next request.The connection pool is managed by the connection pool itself, and the connection pool is created and disconnected. At the same time, it is also possible toBy setting the connection pooling parameters to control the initial number of connections in the pool, the upper and lower limits of the number of connections, as well as the maximum number of times each connection can be used, the maximum idle time and so on.
  • Connection Pool Parameter Role
    • Minimum number of connections: is the number of connections the connection pool keeps to the database at all times.Therefore, if the application program does not use a large number of database connections and still sets a large minimum number of connections, it will result in a large amount of wasted connection resources.
    • Maximum number of connections: is the maximum number of connections that the connection pool can apply for, if the database connection request exceeds the maximum number of connections, the subsequent database connection request will be added to the waiting queue.
    • If the difference between the min connection and the max connection is very large, then the first connection request will be profitable, and then more than the min connection connection request is equivalent to create a new database connection, but these greater than the min connection will not be released immediately after the use of the database connection will be put into the connection pool waiting for reuse.

C3P0

C3P0 is an open source JDBC connection pool , including the jdbc3 and jdbc2 extended specification of the Connection and Statement pool DataSources objects

Configuration of C3P0

  • The two necessary jar packages for C3P0 are as follows: you can go to the official website and download the zip file directly.c3p0:JDBC DataSources/Resource Pools download |

The import method is similar to importing the JDBC-Mysql jar package and will not be repeated here.

  • Then to configure the file (here choose xml configuration) → directly configured to the src folder, otherwise it will report a configuration error

  • General template for the document (can be copied and used directly).

    <c3p0-config>
    <default-config>
        <! -- database-driver-name -->
        <property name="driverClass"> </property>

        <! -- url of the database -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/demo</property>

        <! --username. default: null -->!
        <property name="user">root</property>

        <! --password. default: null --> <property name="user">root</property> <!
        < <property name="password"> root</property>

        <! --Get three connections at initialization, value should be between minPoolSize and maxPoolSize.Default: 3 -->!
        <property name="initialPoolSize">3</property>.

        <! --Maximum number of connections retained in the connection pool.Default: 15 -->.
        < property name="maxPoolSize">5</property>
        <! -- The number of simultaneous connections that c3p0 will fetch at one time when the connection pool is depleted.Default: 3 --> </property>!

        < property name="acquireIncrement"> 3</property>
        <! --Maximum idle time, if not used within 60 seconds the connection is dropped. If 0 then it is never dropped.Default: 0 --> <
        <property name="maxIdleTime"> 60</property>

        <! -- When the connection pool runs out the client calls getConnection() and waits to get a new connection, after the timeout will throw a SQLException, if set to 0 then wait indefinitely. Default: 0 --> <property name="SQLException"; <property name="SQLException".
        <property name="checkoutTimeout">0</property>
    </default-config>
</c3p0-config>

How to use C3P0

C3P0There is only one class, obviously this class is the most important, which contains all the C3P0 operations on the database connection, then we start to explain the

ComboPooledDataSource class

  • Primary methodology

    • Connection getConnection(): Get a connection to the database from the connection pool, if there is no free connection, then create a new connection until the maximum number of connections is reached. It returns aConnectionboyfriend

    In addition to using the xml file configuration can also be used directly in the codeComboPooledDataSource method configuration in the class, but generally use the xml file, to avoid redundancy

    • void setXXX() :: Set the various types of attributes in C3P0, XXX means attributes, for examplesetUser(),setMinPoolSize(int min) wait a minute!

  • Testing Update Statements with Connection Pooling

    public static void main(String[] args) throws SQLException {
        //Create a connection pool,Get Connection
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection conn = ();
        //Testing Update Statements with Connection Pooling
        String sql = "SELECT * FROM user WHERE username= ? AND password = ? ";
        PreparedStatement stmt = (sql);

        (1, 11111);
        (2, "11111111");
        ResultSet resultSet = ();
        if (()) {
            int username = ("username");
            String password = ("password");
            ("username:" + username + "password:" + password);
        }
        ();
        ();
    }

Druid (the best Java connection pool)

Druid is the best database connection pool available, in terms of features. Performance. Scalability are hanging other connection pools , includingDBCP,C3P0,BoneCP,Proxoolwait a minute!

Druid Configuration

  • Druid implementation requires a jar package you can go to the official website at./maven2/com/alibaba/druid/ download the required jar package, in the import can be

  • List of parameters for Druid

    Properties (Parameter) Default Description
    username **** User name to connect to the database
    password **** Password for connecting to the database
    jdbcUrl **** Same as the jdbcUrl attribute in C3P0
    driverClassName Auto-recognition based on url This item can be configured with or without, if you do not configure the druid will automatically identify the dbType according to the url, and then select the appropriate driverClassName
    initialSize 0 *The number of physical connections established during initialization. Initialization occurs when the display calls the init method, or the first getConnectionSee the initialSize property in the DBCP
    maxActive 8 Maximum number of connection pools(Maximum number of Connections a pool will maintain at any given time.
    maxIdle 8 It's no longer in use, and configuring it didn't work
    minIdle **** Minimum number of connection pools
    maxWait **** The maximum wait time in milliseconds to get a connection. After maxWait is configured, fair locking is enabled by default and the concurrency efficiency will be reduced, if needed you can use non-fair locking by configuring the useUnfairLock property to true.
    poolPreparedState- ments false Whether or not to cache preparedStatement, also known as PSCache. pscache is a huge performance boost for databases that support cursors, such as oracle.
    maxOpenPrepared- Statements -1 To enable PSCache, it must be configured to be greater than 0. When it is greater than 0, poolPreparedStatements is automatically triggered to be modified to true. In Druid, there won't be the problem of PSCache taking up too much memory under Oracle, so you can configure this value to be larger, say 100
    testOnBorrow true Execute validationQuery when requesting a connection to check if the connection is valid, doing this configuration will reduce performance.
    testOnReturn false Execute validationQuery when returning a connection to check if the connection is valid, doing this configuration will reduce performance
    testWhileIdle false It is recommended to configure it as true, which does not affect performance and ensures security. If the idle time is longer than timeBetweenEvictionRunsMillis, execute validationQuery to check whether the connection is valid.
    validationQuery **** The sql used to test if a connection is valid, requires a query. If validationQuery is null, testOnBorrow, testOnReturn, testWhileIdle will not work. In mysql, it is usually select 'x', in oracle, it is usually select 1 from dual.
    timeBetweenEviction-RunsMillis **** 1) Destroy thread detects the interval between connections 2) The basis of the testWhileIdle judgment
    minEvictableIdle- TimeMillis **** The Destory thread closes the current connection if it detects that the difference between the last active time and the current time of the current connection is greater than minEvictableIdleTimeMillis.
    removeAbandoned **** Force closure of connections whose establishment time exceeds removeAbandonedTimeout
    removeAbandoned-Timeout **** Specifies how long a connection should be established before it is forced to close.
    logAbandoned false Specifies whether to record the current thread's stack information in the log when removeabandoned occurs.
    filters **** attribute type is a string , through the alias of the way to configure the extension plug-ins , commonly used plug-ins are : 1) monitoring statistics with filter:stat 2) logging with filter:log4j 3) defense of sql injection filter:wall
    • Attributes in red are required configuration attributes
    • Definitions (names can be taken in passing but must be properties), can be placed in any directory

    • The general template for the document is as follows
    driverClassName=
url=jdbc:mysql://localhost:3306/test?userSSL=false&serverTimezone=Asia/Shanghai
username=root
password=123456
initialSize=3
maxActive=5
maxWait=1000

How to use Druid

Druid is also just one core classDruidDataSourceIt realizes connector

  • Main Classes and Methods

    • DruidDataSource() constructor: Construct a default instance of DruidDataSource This instance is mainly used to explicitly configure the Druid property
      • setXXX(): Set the value of the attribute, e.g.setUrl(String url),setInitialSize(int initialSize)wait a minute!
    • DruidDataSourceFactoryclass:is a factory class that is used to create a factory based on the provided configuration informationDruidDataSource instance, this class simplifies the process of loading configuration information from a configuration file and creating theDruidDataSource course of action
      • createDataSource(Properties properties): One of the most commonly used methods, which accepts a Properties object as a parameter, reads configuration information from that object, and creates aDruidDataSource

  • Wrapping Druid as a JdbcUtils class

    public class JdbcUtil {
    private static DataSource dataSource;

    static {
        //Create the configuration first,connection pool
        try {
            InputStream inputStream = ("resource/");
            Properties props = new Properties();
            (inputStream);
            dataSource = (props);
        } catch (Exception e) {
            ();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = ();
        } catch (SQLException e) {
            ();
        }
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
        try {
            if (rs != null) {
                ();
            }
            if (stmt != null) {
                ();
            }
            if (conn != null) {
                ();
            }
        } catch (SQLException e) {
            ();
        }
    }
}

  • This article draws on the work of many authors on the same platform such as@Shaoping's Blogging Life,@chy_18883701161,@abusivegoodman

<Previous Next>