JDBC操作(基础篇)

重点prepareStatement进行了预编译在执行，就是通过了jdkJava虚拟机编译再执行的，执行效率非常高

事务回滚(重)

package cn.com.zzn;


import java.sql.*;

/**
 * 1.加载驱动
 * 2.获取连接对象
 * 3.语句对象执行sql
 * 4.处理返回结果
 * 5.释放资源
 * **/

public class DBTest {
    private static String URL="jdbc:mysql://localhost:3306/db01?useUnicode=true&characterEncoding=utf-8";
    private static String DriverClass="com.mysql.cj.jdbc.Driver";
    private static String USERNAME = "db01";
    private static String PASSWORD = "db01";
    private static Connection connection = null;
    private static PreparedStatement ps = null;
    private static ResultSet resultSet = null;

    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection(){
//        捕获异常
        try {
            Class.forName(DriverClass);
            connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            System.out.println("11111");
//            处理异常
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    /**
     * 释放资源
     */
    public static void closeReouse(){
       if (connection!=null){
           try {
               System.out.println("22222");
               connection.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
    }

    /**创建表order2*/
    public static void createTable(){
        connection = getConnection();
        try {
           ps= connection.prepareStatement(""+
                    "create table order2("+"o_id int not null primary key auto_increment,"+
                    "o_name varchar(20),"+
                    "o_pirce varchar(20),"+
                    "o_date date "+
                    ")");
            int result = ps.executeUpdate();
            System.out.println(result);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**创建表account**/
    public static void createAccount(){
        connection = getConnection();
        try {
            ps = connection.prepareStatement(""+"create table account("+"id int primary key not null auto_increment,"+
                        "name varchar(20),"+
                        "money double(10,2) "+
                    ") ");
            int result = ps.executeUpdate();
            System.out.println(result);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**新增account数据*/
    public static void insertAccount() throws SQLException {
        connection = getConnection();
        try {
            ps = connection.prepareStatement("insert into account(name,money) values (?,?),(?,?)");
            ps.setString(1,"张三");
            ps.setInt(2,10000);
            ps.setString(3,"李四");
            ps.setInt(4,10000);
            int update = ps.executeUpdate();
            System.out.println(update);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /***修改account数据
     *  * 模拟一次转账操作
     * */
    public static void UpdateAccount() throws SQLException {
        connection = getConnection();
        connection.setAutoCommit(false); //设置不自动提交
        Savepoint savepoint = null;
            //支出
        try {
            ps= connection.prepareStatement("update account set money= money-5000 where name ='张三'");
            ps.executeUpdate();
            //回滚点以上的继续执行,回滚点以下的不执行
            savepoint = connection.setSavepoint();//回滚点
            int i = 200/0; //产生异常
            //收入
            ps =connection.prepareStatement("update account set money= money+5000 where name ='李四'");
            ps.executeUpdate();
        } catch (Exception e) {
            connection.rollback(savepoint); //回滚
        }finally {
            connection.commit();
        }
    }

    /*新增数据**/
    public static void insertOrder2(){
        connection = getConnection();
        try {
            ps = connection.prepareStatement("insert into order2(o_name,o_pirce,o_date) values (?,?,?)");
            ps.setString(1,"小周");
            ps.setString(2,"hhh");
            ps.setDate(3,new Date(20210908));
            int update = ps.executeUpdate();
            System.out.println(update);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询数据
     * **/
    public static void getStudents(){
        try {
            getConnection();
            ps =connection.prepareStatement("select * from student");
            resultSet = ps.executeQuery();
             while (resultSet.next()){
                 System.out.println(resultSet.getInt("s_id")+"/"+resultSet.getString("s_name"));
             }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) throws SQLException {
//         connection = DBTest.getConnection();
//         if (connection !=null){
//             System.out.println("连接成功");
//         }else {
//             System.out.println("连接失败");
//         }
//        getStudents();
//        createTable();
//        insertOrder2();
//        insertAccount();
        UpdateAccount();
         closeReouse();
    }
}