JDBC foundation, connection pool, Spring JDBC

catalogue

  JDBC related API s

DriverManager class: drive management object

Connection: database connection object

Gets the object that executes SQL

Management services

Statement: object to execute sql

ResultSet: result set object that encapsulates query results

  PreparedStatement: object to execute sql

Database connection pool

C3P0 database connection pool

Druid database connection pool

Spring JDBC

JDBC essence: it is actually a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Each database manufacturer implements this interface and provides database driver jar package. We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package.

  JDBC related API s

Registered driver: Class.forName("com.mysql.jdbc.Driver");

Code analysis: use reflection mechanism to load bytecode file into memory and return a Class object. A static code block is defined in this Class to register the driver, so there is no need to return an object. (can be omitted after mysql5)

DriverManager class: drive management object

static Connection getConnection(String url, String user, String password)     Get connection to database

If the local database is connected and the port number is 3306, the url can be abbreviated as: jdbc:mysql: / / / database name

Connection: database connection object

Gets the object that executes SQL

  • Statement createStatement()
  • PreparedStatement prepareStatement(String sql) 

Management services

  • Start transaction: setAutoCommit(boolean autoCommit)   If the parameter is false, the transaction is started
  • Commit transaction: commit()
  • Rollback transaction: rollback()

Statement: object to execute sql

  • int executeUpdate(String sql): when executing DML and DDL statements, the return value is the number of affected rows
  • ResultSet executeQuery(String sql)  : Execute DQL (select) statement

ResultSet: result set object that encapsulates query results

  • boolean next(): move the cursor down one line to determine whether the current line is the end of the last line (whether there is data)
  • Getxxx (parameter): get data     The parameter can be the number of the column (starting from 1) or the name of the column

  PreparedStatement: object to execute sql

SQL injection problem

When splicing sql, some special keywords of sql participate in string splicing, which will cause security problems

                1. Enter the user's password: a 'or' a '='a
                2. sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a' 

Precompiled SQL: the parameter uses? As a placeholder, which solves the problem of SQL injection

  1. PreparedStatement Connection.prepareStatement(String sql)     Gets the object that executes the SQL statement
  2. Assign a value to the placeholder using setXXX (parameter 1, parameter 2), parameter 1: position number, parameter 2: data
  3. Execute sql statements and return query results   excuteQuery() or excuteUpdate()

Example code: SQL injection

  public static void main(String[] args) throws ClassNotFoundException, SQLException {
        System.out.println("Please enter user name:");
        Scanner in = new Scanner(System.in);
        String name = in.nextLine();
        System.out.println("Please input a password:");
        String password = in.nextLine();

        Class.forName("com.mysql.jdbc.Driver");//Register driver
        Connection connection = DriverManager.getConnection("jdbc:mysql:///Db_6 "," root "," 110120 "); / / get the connection object
        String sql="select* from user2 where username='"+name+"'and password='"+password+"'";
        System.out.println(sql);//Select * from user2 where username ='asd'and password ='a 'or' a '='a' SQL injection problem, the condition judgment is always true, and the whole table is queried
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()){//Judge whether there is data in the next row
            System.out.println("Login succeeded");
        }else{
            System.out.println("Login failed");
        }
        statement.close();//Release resources
        connection.close();//Release resources

    }

//PreparedStatement addresses SQL injection
 public static void main(String[] args) throws ClassNotFoundException, SQLException {
        System.out.println("Please enter user name:");
        Scanner in = new Scanner(System.in);
        String name = in.nextLine();
        System.out.println("Please input a password:");
        String password = in.nextLine();
        Class.forName("com.mysql.jdbc.Driver");//Register driver
        Connection connection = DriverManager.getConnection("jdbc:mysql:///Db_6 "," root "," 110120 "); / / get the connection object
        String sql="select * from user2 where username=? and password=?";//sql statement with placeholder
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,name);//Assign values to placeholders
        statement.setString(2,password);
        ResultSet resultSet = statement.executeQuery();
        if(resultSet.next()){
            System.out.println("Login succeeded");
        }else{
            System.out.println("Login failed");
        }
        statement.close();
        connection.close();
    }

Case: using transactions

public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//Register driver
        Connection connection = DriverManager.getConnection("jdbc:mysql:///Db_6 "," root "," 110120 "); / / get the connection object
        connection.setAutoCommit(false);//Open transaction
        String sql1="update account set balance= balance-500 where id=1";
        String sql2="update account set balance= balance+500 where id=2";
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql1);
        int i=1/0;//Manufacturing error
        statement.executeUpdate(sql2);
        connection.commit();//Commit transaction
        connection.rollback();//Transaction rollback
        statement.close();
        connection.close();
    }

Database connection pool

Connection pool: in fact, it is a container (Collection) for storing database connections. After the system is initialized, the container is created, and some connection objects will be applied in the container. When the user accesses the database, the connection objects will be obtained from the container. After the user accesses, the connection objects will be returned to the container.

Benefits:

  • save resources
  • Efficient user access

Standard interface DataSource for implementing database connection pool

Relevant methods:

  • getConnection(): get connection
  • close(): return the connection

C3P0 database connection pool

Steps:

  • Import jar package (two) c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar   And the database driver jar package
  • Definition configuration file: c3p0-config.xml    Just put the file in the src directory
  • Create the core object database connection pool object ComboPooledDataSource
  • Get connection: getConnection

Sample code

Configuration file: multiple databases can be configured (generally using the default)

<c3p0-config>
  <!-- Read the connection pool object using the default configuration -->
  <default-config>
  	<!--  Connection parameters -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db_1</property>
    <property name="user">********</property>
    <property name="password">********</property>
    
    <!-- Connection pool parameters -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">10</property>
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0"> 
    <!--  Connection parameters -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db_1</property>
    <property name="user">********</property>
    <property name="password">********</property>
    
    <!-- Connection pool parameters -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>

Test code

 public static void main(String[] args) throws SQLException {
        DataSource dataSource = new ComboPooledDataSource();//Create objects and use the default configuration
        DataSource otherc3p0 = new ComboPooledDataSource("otherc3p0");//Create an object using a configuration named otherc3p0
        Connection connection = dataSource.getConnection();//Get connection
        connection.close();//Return connection
    }

Druid database connection pool

step

  • Import the jar package druid-1.0.9.jar
  • Definition configuration file: in the form of properties, can be called any name, and can be placed in any directory
  • Load profile
  • Get database connection pool object: through the factory   DruidDataSourceFactory
  • Get connection: getConnection

Sample code

configuration file

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db_1
username=********
password=********
initialSize=5
maxActive=10
maxWait=3000

Test code

public static void main(String[] args) throws Exception {
        Properties properties = new Properties();//Connect Properties object
        properties.load(test22.class.getResourceAsStream("druid.properties"));//load file
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);//Obtain the DataSource object through the factory class
        Connection connection = dataSource.getConnection();//Get connection
        connection.close();//Return connection
    }

To create a tool class:

  • Create a database connection pool object in a static code block   
  • Member methods: obtain connections, release resources, and obtain connection pool objects

Spring JDBC

Spring framework is a simple encapsulation of JDBC. It provides a JDBC template object to simplify JDBC development

Create a JdbcTemplate object. Depends on the data source.

Member method

  • update(sql,args...): execute DML statements. Add, delete and change statements
  • queryForObject: the query result is encapsulated as an object (multiple results)
  • query(): query results, which are encapsulated into objects (single results)
  • queryForList(): the query result encapsulates the result set into a list set
    •   Encapsulate each record into a Map set, and then load the Map set into the List set

Sample code

public static void main(String[] args) throws Exception {
        Properties properties = new Properties();//Connect Properties object
        properties.load(test22.class.getClassLoader().getResourceAsStream("druid.properties"));//load file
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);//Obtain the DataSource object through the factory class
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql1="insert into user2 values(?,?)";
        jdbcTemplate.update(sql1,"Zhang San","123456789");//Add data
        String sql2="select * from user2";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql2);//Encapsulate the collection into a list line by line reading {username=tom, password=123}
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
        List<user> query = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(user.class));//Encapsulate multiple results into objects
        for (user user : query) {
            System.out.println(user);
        }
        String sql3="select * from user2 where username='Zhang San'";
        user user = jdbcTemplate.queryForObject(sql3, new BeanPropertyRowMapper<>(user.class));//Encapsulate a single result into an object
        System.out.println(user);
        
    }

Tags: Database

Posted on Wed, 15 Sep 2021 16:43:02 -0400 by philgh