Java database connectivity, Java database connection, Java language operation database The essence of Jdbc: In fact, it is a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Various data The library manufacturer implements this set of interfaces and provides database driven jar packages. We can use this set of interfaces (JDBC) to compile The actual code to execute is to drive the implementation classes in the jar package.

Steps:

1. Import the driver jar package mysql-connector-java-5.1.37-bin.jar

1. Copy mysql-connector-java-5.1.37-bin.jar to the libs directory of the project

2. Right click -- > Add as library

2. Register driver

3. Get the database Connection object Connection

4. Define sql

5. Get the object Statement that executes the sql Statement

6. Execute sql and accept the returned results

7. Treatment results

8. Release resources

Example (lazy, the annotation has been explained in detail):

import com.mysql.jdbc.PreparedStatement;

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

public class TestJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, SQLException {
                    /* 1: Register driver
                    After mysql5.0, there is no need to register the driver, and the driver has been brought with it
                    But we can write forward compatibility
                    */
                    /*The reason why the driver can be registered:
                    public class Driver extends NonRegisteringDriver implements java.sql.Driver {
                    public Driver() throws SQLException {} static {
                    try {
                    DriverManager.registerDriver(new Driver());
                    } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");}}}
                    */
        Class.forName("com.mysql.jdbc.Driver");
                   /* 2:Get database connection object
                   DriverManger Class function:
                   1)Manage and register drivers
                   2) Create a connection to the database
                   <1>:Connection getConnection (String url, String user, String password) through
                   The connection object of the database is obtained through the connection string, user name and password
                   <2>:Connection getConnection (String url, Properties info->Write your own configuration document
                   The connection object is obtained through the connection string and attribute object
                   Writing configuration file format: url = "jdbc:mysql://localhost:3306/ Connection database name ";
                   <3>:Solution to garbled code (specify database encoding method):
                   jdbc:mysql://localhost:3306/database characterEncoding=utf8
                   <4>:Short url premise: it must be a local server and the port number is 3306
                   Abbreviation: jdbc:mysql: / / / database name
                   */
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/nanzi", "root", "root");
                  /* 3 :Gets the object that executes the sql statement
                  Connection Class:
                  1)Gets the object that executes the sql statement
                  <1>:Get the ordinary performer object: Statement createStatement();
                  Statement Object is not recommended because of unsafe sql injection
                  sql Injection problem: it is to make database errors through special splicing sql statements
                  Example: select * from table name where name = 'Zhang San' and pass ='123 'or' a '='a';
                  In this way, the data in the database will be confused and recognized
                  <2>:Get the precompiled performer object: PreparedStatement prepareStatement(String sql);
                  Precompiled object solves the problem of sql injection. Precompile first through question marks as placeholders, and then through precompiling
                  The translation object assigns a value to the placeholder
                  Assignment method: PreparedStatement object. setXxx(? Sequence number, value - > pay attention to string and number);
                  Note: the assignment starts from 1, not 0
                  2)Transaction processing (sequence)
                  Start transaction: setAutoCommit(boolean autoCommit); If the parameter is false, the transaction is started.
                  Commit transaction: commit();
                  Rollback transaction: rollback();
                  Special note: the transaction operation should be implemented in the service layer, and the dao layer generally only does simple database logic
                  */
        PreparedStatement preparedStatement = (PreparedStatement)
                connection.prepareStatement("select * from nanfengmessage where name=? and pass=? ?");
       preparedStatement.setString(1, "name");
       preparedStatement.setInt(2, 123);
                   /*4: Execute the sql statement and receive the returned object:
                   1)Execute dml: modify, etc
                   int i = preparedStatement.executeUpdate();
                   The return value indicates the number of affected rows: it is generally used to judge the execution result of sql statements. If it is greater than 0, it indicates execution
                   The line is successful, otherwise the execution fails
                   2)Execute dql: query
                   ResultSet resultSet = preparedStatement.executeQuery();
                   ResultSet Execution result encapsulation class:
                   Common methods:
                   next() boolean; Used to obtain the next set of data in the query result data table (equivalent to
                   The table contains an arrow pointing to the next row each time. If there is data
                   Returns true, no number false (similar to iterator), and traverses common
                   getXXX("Column name "") XXX type; specify column name to get data results
                   getXXX((column number) XXX type; formulate method column number to obtain data results
                   */
        ResultSet resultSet = preparedStatement.executeQuery();
            //Traverse the results
        while (((ResultSet) resultSet).next()) {
            String name = resultSet.getString("name");
            System.out.println(name);
            //Specific treatment
        }
        /*5: Finally, release resources: */
        connection.close();
        preparedStatement.close();
        resultSet.close();
    }
}

Comparison table of data acquisition:

Extraction of tool classes:

public class JDBCUtils {
    private static String driverClass;
    private static String databaseName;
    private static String databasePass;
    private static String databaseURL;

       //Initialize data through static code blocks
    static {
    //Get the bytecode file of the current class, and get the class loader through the bytecode file
        ClassLoader classLoader = JDBCUtils.class.getClassLoader();
      //Load the specified file through the classloader to obtain the input stream object (the file path must be in the src directory or absolute path)
        InputStream resourceAsStream =
                classLoader.getResourceAsStream("db.properties");
      //Create the Properties collection and load the data in the stream through the load method
        Properties properties = new Properties();
        try {
            properties.load(resourceAsStream);
       //Get the data in the file (according to the key name in the file, don't write it wrong)
            databaseURL = properties.getProperty("URL");
            driverClass = properties.getProperty("driverClass");
            databaseName = properties.getProperty("name");
            databasePass = properties.getProperty("password");
        //Register driver
            Class.forName(driverClass);
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }

    
    private JDBCUtils() {
    }

   
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(databaseURL, databaseName, databasePass);
        return connection;
    }

    
    public static void close(Connection connection, Statement statement) {
   //This parameter adopts statement. Preparedstatement is a subclass of statement (polymorphic)
    //Make non empty judgment
        if (statement != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

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

Several configuration files for database connection:

driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///mybatismussary
username=root
password=root 

Concept:

It's actually a container( aggregate ), the container 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.

advantage:

Save resources and improve user access efficiency

(3) Create the core object database connection pool object ComboPooledDataSource

(4) Get connection: getConnection

code:

//1. Create database connection pool object

DataSource ds = new ComboPooledDataSource();

//2. Get the connection object

Connection conn = ds.getConnection();

(5) Note: in the objects using connection pool, the close() method returns the connection object to the database connection

Pooled

Tools:

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3p0PoolUtils {
    private static DataSource ds;

    static {
        //Get database connection pool object
        ds = new ComboPooledDataSource();
    }

    private C3p0PoolUtils() {
    }
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

four   Druid: database connection pool implementation technology, provided by Alibaba

Standard interface: under DataSource javax.sql package

Get connection: getConnection()

Return Connection: Connection.close(). If the Connection object Connection is obtained from the Connection pool, then

Call the Connection.close() method to return the connection instead of closing it

Class enhancements are inevitable: