JDBC连接建立及操作

首先要根据在本机所安装的MySQL版本来下载所依赖的包

https://mvnrepository.com/artifact/mysql/mysql-connector-java

    try {
                // 加载驱动类
                Class.forName("com.mysql.jdbc.Driver");
                // 建立连接
                Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306", "root", "123456abc");
                System.out.println(conn);
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }

---

对SQL语句的设置

使用PreparedStatement避免sql注入的问题，因为其使用?作为占位符，使用setObject()对其进行编辑

String sql = "SELECT * FROM user WHERE login=? AND pass=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, name);
ps.setObject(2, pass);

查询框架

try {
            // 加载驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pw);

            PreparedStatement ps = conn.prepareStatement("call sql_invoicing.get_invoices_with_balance()");

            ResultSet rs = ps.executeQuery(); // 查询使用Query

            while(rs.next()){
                double invoice_total = rs.getDouble("invoice_total"); // 相对应的数据类型的get方法对其进行转换获取
                System.out.println(invoice_total);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

更新操作

try {
    // 加载驱动类
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 建立连接
    Connection conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pw);

    PreparedStatement ps = conn.prepareStatement("update sql_store.customers set points = points + 50 where birth_date < '1990-01-01';");

    int rs = ps.executeUpdate(); // 更新，插入使用executeUpdate，返回更新的行数

    System.out.println(rs);

} catch (ClassNotFoundException | SQLException e) {
    e.printStackTrace();
}

插入

import java.sql.*;

public class Insert_SQL extends SQL_info{
    void Insert_customers(){
        try {
            // 加载驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pw);

            PreparedStatement ps = conn.prepareStatement(
                    "insert into " +
                            "sql_store.customers " +
                            "values " +
                            "(" +
                            "default," +
                            "'John'," +
                            "'Smith'," +
                            "'1990-01-01'," +
                            "null," +
                            "'address'," +
                            "'city'," +
                            "'CA'," +
                            "default)");


            int rs = ps.executeUpdate(); // 更新，插入使用executeUpdate，返回更新的行数

            System.out.println(rs);

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        Insert_SQL is = new Insert_SQL();
        is.Insert_customers();
    }
}

获取主键

try {
    // 加载驱动类
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 建立连接
    Connection conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pw);

    PreparedStatement ps = conn.prepareStatement(
        "insert into " +
        "sql_store.customers " +
        "values " +
        "(" +
        "default," +
        "'John'," +
        "'Smith'," +
        "'1990-01-01'," +
        "null," +
        "'address'," +
        "'city'," +
        "'CA'," +
        "default)", Statement.RETURN_GENERATED_KEYS); // 添加一个参数来保存主键


    int n = ps.executeUpdate(); // 更新，插入使用executeUpdate，返回更新的行数

    System.out.println(n);

    ResultSet rs = ps.getGeneratedKeys();

    if(rs.next()){
        int id = rs.getInt(1);
        System.out.println(id);
    }

} catch (ClassNotFoundException | SQLException e) {
    e.printStackTrace();
}

批量执行

try {
    // 加载驱动类
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 建立连接
    Connection conn = DriverManager.getConnection(jdbc_url, jdbc_user, jdbc_pw);

    PreparedStatement ps = conn.prepareStatement(
        "insert into " +
        "sql_store.customers " +
        "values " +
        "(" +
        "default," +
        "'John'," +
        "'Smith'," +
        "'1990-01-01'," +
        "null," +
        "'address'," +
        "'city'," +
        "'CA'," +
        "？)", Statement.RETURN_GENERATED_KEYS); // 添加一个参数来保存主键

    for (Student s : students) {
        ps.setString(1, s.name);
        ps.setBoolean(2, s.gender);
        ps.setInt(3, s.grade);
        ps.setInt(4, s.score);
        ps.addBatch(); // 添加到batch
    }
    // 执行batch:
    int[] ns = ps.executeBatch();
    for (int n : ns) {
        System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
        // 循环int[]数组即可获取每组参数执行后影响的结果数量。
    }

} catch (ClassNotFoundException | SQLException e) {
    e.printStackTrace();
}

事务

Connection conn = openConnection();
try {
    // 关闭自动提交:
    conn.setAutoCommit(false);
    // 执行多条SQL语句:
    insert(); update(); delete();
    // 提交事务:
    conn.commit();
} catch (SQLException e) {
    // 回滚事务:
    conn.rollback();
} finally {
    conn.setAutoCommit(true);
    conn.close();
}

实际上，默认情况下，我们获取到Connection连接后，总是处于“自动提交”模式，也就是每执行一条SQL都是作为事务自动执行的，这也是为什么前面几节我们的更新操作总能成功的原因：因为默认有这种“隐式事务”。只要关闭了Connection的autoCommit，那么就可以在一个事务中执行多条语句，事务以commit()方法结束。