Preface

CRUD operation on database has a certain history
① JDBC - > ② dbutils - > ③ jdbctemplate - > ④ - > hibernate - > ⑤ mybatis - > ⑥ JPA+SpringData
So many dao layers are used to realize their own advantages and disadvantages, and to seek the best dao scheme of the project.

But the framework will work, and the foundation is the most important

Java background, from learning to participating in and doing projects, has been more than a year.
Recently engaged in other interesting directions in IT industry, such as artificial intelligence, big data, reptile, etc

After joining in, I think it's necessary to summarize the Java background first
It can also be used as a reference for Mengxin. This time, it's JDBC

Code

talk is cheap show me the code

package com.cun.jdbc;

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

import org.junit.Test;

/**
 * General CRUD
 * @author linhongcun
 *
 */
public class CRUD {

    /**
     * Connect
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                // ? useunicode = true & characterencoding = UTF-8 to solve Chinese disorder
                "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "123");
        return conn;
    }

    /**
     * To break off
     * @param stat
     * @param conn
     * @throws SQLException
     */
    void close(PreparedStatement stat, Connection conn) throws SQLException {
        stat.close();
        conn.close();
    }

    /**
     * 1,check
     * Simulated quantitative query
     * @throws ClassNotFoundException 
     * @throws SQLException 
     */
    @Test
    public void select() throws ClassNotFoundException, SQLException {
        String sql = "select * from t_user limit ?,?";
        Connection conn = getConnection();
        PreparedStatement stat = conn.prepareStatement(sql);
        // Query 3 consecutive records from record
        stat.setInt(1, 2);
        stat.setInt(2, 3);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("id") + "-" + rs.getString("user_name") + "-" + rs.getString("password"));
        }
        // Release resources
        close(stat, conn);
    }

    /**
     * 2,change
     * Simulate reset password
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void update() throws ClassNotFoundException, SQLException {
        String sql = "update t_user set password=? where user_name=?";
        Connection conn = getConnection();
        PreparedStatement stat = conn.prepareStatement(sql);
        stat.setString(1, "777");
        stat.setString(2, "Zhang Wuji");
        int rs = stat.executeUpdate();
        if (rs != 0) {
            System.out.println("Update success");
        } else {
            System.out.println("Update failed");
        }
        // Release resources
        close(stat, conn);
    }

    /**
     * 3,increase
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void add() throws ClassNotFoundException, SQLException {
        String sql = "insert into t_user(user_name,password) values(?,?)";
        Connection conn = getConnection();
        PreparedStatement stat = conn.prepareStatement(sql);
        stat.setString(1, "Zhang Wuji");
        stat.setString(2, "456");
        int rs = stat.executeUpdate();
        if (rs == 1) {
            System.out.println("Insert success");
        } else {
            System.out.println("Insert failure");
        }
        // Release resources
        close(stat, conn);
    }

    /**
     * 4,Delete
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void delete() throws ClassNotFoundException, SQLException {
        String sql = "delete from t_user where id=2";
        Connection conn = getConnection();
        PreparedStatement stat = conn.prepareStatement(sql);
        int rs = stat.executeUpdate();
        if (rs == 1) {
            System.out.println("Delete successful");
        } else {
            System.out.println("Delete failed");
        }
        // Release resources
        close(stat, conn);
    }

    /**
     * 5,Batch insertion
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void adds() throws ClassNotFoundException, SQLException {
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        String sql = "insert into t_user(user_name,password) values(?,?)";
        PreparedStatement stat = conn.prepareStatement(sql);
        for (int i = 1; i < 10; i++) {
            stat.setString(1, "Linghu Chong" + i);
            stat.setInt(2, i);
            stat.addBatch();
        }
        stat.executeBatch();
        conn.commit();
        // Release resources
        close(stat, conn);
    }

    /**
     * 6,Call stored procedure
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void pro() throws ClassNotFoundException, SQLException {
        Connection conn = getConnection();
        String sql = "CALL `prc_test3`;";
        PreparedStatement stat = conn.prepareStatement(sql);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString("id") + "-" + rs.getString("user_name") + "-" + rs.getString("password"));
        }
        // Release resources
        close(stat, conn);
    }

}

Other

I don't like to talk about all kinds of knowledge and its usage in long articles.
See code and notes for details

Stored procedures involved (if Oracle/sqlserver is used, the syntax is different)

Delete

DROP PROCEDURE IF EXISTS `prc_test3`;

Second, create

CREATE  PROCEDURE `prc_test3`()
BEGIN
    select * from t_user;
END;

Execution

CALL `prc_test3`;