JDBC summary

What is JDBC?

Java Data Base Connectivity (JDBC) : Java database connection. Now widely used driver standards are ODBC (Open Data Base Connectivity) and JDBC, JDBC is SUN company on the basis of ODBC developed a set of Java application connection standard (interface), operating all relational database rules. Database vendors like MySQL, Oracle, SQL Server, etc. provide jar packages to implement these interfaces. We use this set of interfaces (JDBC) to program, and the real code to execute is the implementation class in the driver JAR package.

Simple JDBC operation

  1. Import the jar package
  2. Register the driver (initialize the driver)
  3. Connecting to a Database
  4. Writing SQL statements
  5. Gets an object that can execute a SQL statement
  6. Execute SQL statement
  7. Get the data, process the results
  8. Close the connection

There are two ways to register drivers

  1. Class.forName("com.mysql.jdbc.Driver");

  2. DriverManager.registDriver(new com.mysql.jdbc.Driver());

    The first method registers the driver only once, take a look at the source code

    public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver(a) throws SQLException {}static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!"); }}}Copy the code

    Static code block only once, when Class loading static blocks of code are already performed again DriverManager. RegisterDriver (new Driver (), so use Class. The class.forname () this will only be loaded once. The second method, however, causes the Driver to be registered twice, because the display registers once, and then when new com.mysql.jdbc.driver () is created, the class is loaded, and the static code block executes, so the Driver is registered again. Moreover, this registration method relies too much on the MySQL database API and cannot be compiled without the MySQL development package.

Each object

  1. DriverManager: Driver managed object

    Static void registerDriver(MySQL5): static void registerDriver(MySQL5): Register with the given driver DriverManager

    Function 2: Obtain a database connection

    Static Connection getConnection(String URL, String user, String Password) : Obtains the database Connection object

    * url: specify the connection path * grammar: JDBC: mysql: / / IP address (domain name) : port/database name * example: JDBC: mysql: / / localhost: 3306 / db3 * details: If you are connecting to a local mysql server and the default mysql service port is 3306, the URL can be shortened to: JDBC :mysql:/// database nameCopy the code
    • User: indicates the user name
    • Password: password

  2. Connection: database Connection object

    Function: Database connection object

    Common methods:

    • Execute SQL statement
      • Statement createStatement()
        • PreparedStatement prepareStatement(String sql)
      • Regulatory affairs
        • void setAutoCommit(boolean autoCommit)
        • void commit()
        • void rollback()

  3. Statement: Object to execute SQL

    Function: Execute SQL statement object to execute SQL statement

    • Boolean execute(String SQL) : can execute arbitrary SQL (not common)

    • Int executeUpdate(String SQL) : Execute DML (INSERT, UPDATE, delete) statements and DDL(CREATE, ALTER, drop) statements. Return value: number of affected rows. You can determine whether the DML statement is successfully executed based on the number of affected rows. If the return value is greater than 0, the DML statement is successfully executed.

    1. ResultSet executeQuery(String SQL) : Executes a DQL (SELECT) query statement

  4. PreparedStatement: An object to execute SQL (more secure and commonly used)

    • Fix SQL injection problems: Use PreparedStatement objects

      • Precompiled SQL: parameter use? As a placeholder

      • Steps:

        • Import the driver JAR package mysql-connector-java-x.x.xx-bin

        • Registration drive

        • Connect to the database and obtain the database Connection object

        • Define SQL (note: SQL parameters use? As a placeholder. Select * from user where username =? And password =?)

        • Access to execute SQL statements PreparedStatement object Connection. PrepareStatement (String SQL)

        • Give? Method: setXxx(parameter 1, parameter 2)

          * Parameter 1:? The position number of the

          * Parameter 2:? The value of theCopy the code

        • Execute the SQL and accept the results returned without passing the SQL statement

        • The processing results

        • Release resources

    • In the actual development, PreparedStatement will be used to complete all operations of adding, deleting, modifying, and checking, which has the following advantages:

      1. SQL injection can be prevented

      2. Be more efficient

  5. ResultSet: a ResultSet object that encapsulates query results

    Common methods:

    • Boolean next(): Moves the cursor down one line to determine if the current line is the end of the last line (if there is data), return false if so, and true if not

    • * Xxx: indicates the data type, for example, int getInt(), String getString() * 1. Int: indicates the column number, starting from 1, for example, getString(1) 2. String: Represents the column name. Such as: the getDouble (” balance “)

      • Note:
        • Use steps:
          1. The cursor moves down one row
          2. Determine if there is data
          3. To get the data

    Since next returns a Boolean value, we can use a loop to determine if the cursor is the end of the last line. = =

Create a simple tool class

Since we need to connect to close the database frequently, we put both the connect and close operations into a utility class.

/ * * *@ClassName: JDBCUtil
 * @Description: Database connection utility class *@Author: LinZeLiang
 * @Date: the 2020-09-01 21:03 *@Version: 1.0.0 * /
public class JDBCUtil {

    private static String url;
    private static String username;
    private static String password;
    private static String driver;

    // The connection message for the database is stored in the jdbc.properties configuration file in the SRC directory, so we import and fetch the information from this configuration file. The static code block is executed only once when the class is loaded into the JVM to avoid multiple reads from the configuration file
    static {
        try {
            // Get files from SRC: ClassLoader
            /*ClassLoader classLoader = JDBCUtil.class.getClassLoader(); URL res = classLoader.getResource("jdbc.properties"); String path = res.getPath(); * /
            InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");

            // Create the Properties collection class
            Properties pro = new Properties();
            // Load the file
            //pro.load(new FileReader(path));
            pro.load(inputStream);

            // Get data and assign
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch(ClassNotFoundException e) { e.printStackTrace(); }}/** * Get connection **@param: void
     * @return: Connection
     * @Author: LinZeLiang
     * @Date: the 2020-09-01 * /
    public static Connection getConnection(a) {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    /** * Release resource **@param: rs, stmt, conn
     * @return: void
     * @Author: LinZeLiang
     * @Date: the 2020-09-01 * /
    public static void close(ResultSet rs, Statement stmt, Connection conn) {

        if(rs ! =null) {
            try {
                rs.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}if(stmt ! =null) {
            try {
                stmt.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}if(conn ! =null) {
            try {
                conn.close();
            } catch(SQLException throwables) { throwables.printStackTrace(); }}}}Copy the code

ORM

ORM = Object Relationship Database Mapping: An Object encapsulates a record in a Database.

/** * @ClassName: ProductFind * @Description: TODO * @Author: LinZeLiang * @Date: 2020-08-31 22:14 * @Version: 1.0.0 */ public class ProductFind {public static void main(String[] args) {ProductFind test = new ProductFind(); List<Product> all = test.findAll(); System.out.println(all); ** @param: void * @return: List<Product> * @author: LinZeLiang * @date: 2020-08-31 22:34 **/ public List<Product> findAll() { Connection conn = null; Statement stmt = null; ResultSet rs = null; List<Product> list = null; Try {// Initialize Driver and register Driver class.forname (" com.mysql.jdbc.driver "); DriverManager.registerDriver(new com.mysql.jdbc.Driver()); / / connected to the database, access connection object conn = DriverManager. GetConnection (" JDBC: mysql: / / localhost: 3306 / shop? characterEncoding=UTF-8", "root", "root"); String SQL = "SELECT * FROM product"; STMT = conn.createstatement (); Rs = stmt.executeQuery(SQL); Product product = null; list = new ArrayList<>(); while (rs.next()) { product = new Product(); product.setProduct_id(rs.getString("product_id")); product.setProduct_name(rs.getString("product_name")); product.setProduct_type(rs.getString("product_type")); product.setSale_price(rs.getInt("sale_price")); product.setPurchase_price(rs.getInt("purchase_price")); product.setRegist_date(rs.getDate("regist_date")); list.add(product); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } finally { if (rs ! = null) { try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (stmt ! = null) { try { stmt.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn ! = null) { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } return list; } } class Product { private String product_id; private String product_name; private String product_type; private int sale_price; private int purchase_price; private Date regist_date; public String getProduct_id() { return product_id; } public void setProduct_id(String product_id) { this.product_id = product_id; } public String getProduct_name() { return product_name; } public void setProduct_name(String product_name) { this.product_name = product_name; } public String getProduct_type() { return product_type; } public void setProduct_type(String product_type) { this.product_type = product_type; } public int getSale_price() { return sale_price; } public void setSale_price(int sale_price) { this.sale_price = sale_price; } public int getPurchase_price() { return purchase_price; } public void setPurchase_price(int purchase_price) { this.purchase_price = purchase_price; } public Date getRegist_date() { return regist_date; } public void setRegist_date(Date regist_date) { this.regist_date = regist_date; } @Override public String toString() { return "Product{" + "product_id='" + product_id + '\'' + ", product_name='" + product_name + '\'' + ", product_type='" + product_type + '\'' + ", sale_price=" + sale_price + ", purchase_price=" + purchase_price + ", regist_date=" + regist_date + '}'; }}Copy the code

DAO

DAO=DataAccess Object: DataAccess Object. Using ORM ideas, the database related operations are encapsulated in this class, JDBC code can not be seen elsewhere.