JDBC 测试连接数据库 实现数据的CRUD 模拟转账异常功能

编写代码

package com.xiang.lesson01;

import java.sql.*;
import java.text.ParseException;


public class DBTest {
    /*
    **建立连接的五大步骤：**

1. 加载（注册）数据库

2. 建立链接

3. 语句对象来执行SQL语句

4. 处理结果集 、返回结果

5. 关闭数据库、释放资源
     */
//    private static  String URL = "jdbc:mysql://localhost:3307/webapp1?serverTimezone=utf8mb4";
//    useSSL=false 安全连接；
    private static String URL = "jdbc:mysql://localhost:3307/webapp2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private static String DriverClass = "com.mysql.cj.jdbc.Driver";
    private static String UserName = "webapp1";
    private static String PassWord = "webapp1";
    private static Connection connection = null;
    private static PreparedStatement statement = null;
    private static ResultSet resultSet = null;
    private static Savepoint savepoint = null;


    public static Connection getConnection() {
        try {
            Class.forName(DriverClass);
            connection = DriverManager.getConnection(URL, UserName, PassWord);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

//    public  void  closeRerource(Connection connection){
//        try {
//            connection.close();
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }
//    }

    public static void closeRerource() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 查询
     *
     * @param
     */

    public static void getStudent() {
        connection = getConnection();
        try {
            statement = connection.prepareStatement("select  * from student");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(
                        resultSet.getInt("s_id") + "\t" +
                                resultSet.getString("s_name") + "\t" +
                                resultSet.getString("s_sex") + "\t" +
                                resultSet.getDate("s_birthday") + "\t" +
                                resultSet.getInt("s_professional"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 新增
     *
     * @param
     */
    public static void insertStudent() throws SQLException, ParseException {
        connection = getConnection();
        statement = connection.prepareStatement("insert into student(s_name,s_sex,s_birthday,s_professional) values (?,?,?,?)");
        statement.setString(1, "小小");
        statement.setString(2, "女");
//        SimpleDateFormat format = new SimpleDateFormat("YYYY-MM-dd");
        statement.setDate(3, new Date(20210908));
        statement.setInt(4, 4);

        int update = statement.executeUpdate();

        System.out.println(update + "insert into ----->ok");
    }

    /**
     * 创建表
     *
     * @param
     * @throws SQLException
     */
    public static void createTable() throws SQLException {
        connection = getConnection();
        statement = connection.prepareStatement("drop table if exists stu");
        statement.executeUpdate();
        statement = connection.prepareStatement("create table stu\n" +
                "(\n" +
                "    s_no int(8) not null primary key auto_increment,\n" +
                "    s_name varchar(12),\n" +
                "    s_sex varchar(4),\n" +
                "    s_score double(6,1)\n" +
                ")");

        int res = statement.executeUpdate();
        System.out.println(res + "创建表ok");
    }

    /**
     * 创建表 account
     *
     * @param args
     * @throws SQLException
     * @throws ParseException
     */
    /*
   create table account(
	id int not null primary key auto_increment,
	name varchar(20),
	money DOUBLE(10,2)
);
insert into account(name,money)
values
('张三',10000),
('李四',10000);
     */
    public static void account() throws SQLException {
        connection = getConnection();
        statement = connection.prepareStatement("drop table if exists account");
        statement.executeUpdate();
        statement = connection.prepareStatement("create table account(\n" +
                "\tid int not null primary key auto_increment,\n" +
                "\tname varchar(20),\n" +
                "\tmoney DOUBLE(10,2)\n" +
                ")");
        int res = statement.executeUpdate();
        System.out.println(res + "创建 account 表ok");
    }

    /**
     * insert into
     *
     * @param args
     * @throws SQLException
     * @throws ParseException
     */
    public static void insertaccount() throws SQLException {
        connection = getConnection();
        statement = connection.prepareStatement("insert into account(name,money)\n" +
                "values\n" +
                "('张三',10000),\n" +
                "('李四',10000)");
        int update = statement.executeUpdate();

        System.out.println(update + "insert into  account ----->ok");
    }

    /**
     * 更新，实现转账 功能
     *
     * @param args
     * @throws SQLException
     * @throws ParseException
     */
    public static void task() throws SQLException {
        connection = getConnection();
        statement = connection.prepareStatement("update account set money = money - 5000 where  name = '张三'");
        statement.executeUpdate();
        statement = connection.prepareStatement("update account set money = money + 5000 where  name = '李四'");
        statement.executeUpdate();
        int update = statement.executeUpdate();

        System.out.println(update + "update  account task ----->ok");
    }


    /**
     * 更新，实现转账 功能
     * 更加异常；
     *
     * @param args
     * @throws SQLException
     * @throws ParseException
     */
    public static void task2() throws SQLException {
        connection = getConnection();
        connection.setAutoCommit(false);

        //        回滚点；   两条 sql 都不执行；
        savepoint = connection.setSavepoint();
        //支出

        statement = connection.prepareStatement("update account set money = money - ? where  name = ?");
        statement.setInt(1, 5000);
        statement.setString(2, "张三");
        statement.executeUpdate();

//        回滚点； 上边个 sql ( set money = money - ?) 执行；下边个不执行；
//        savepoint = connection.setSavepoint();
        //产生异常；
        try {
            int i = 100/0;
        } catch (Exception e) {
            connection.rollback(savepoint);
//            e.printStackTrace();
        }

//收入
        statement = connection.prepareStatement("update account set money = money + ? where  name = ?");
        statement.setInt(1, 5000);
        statement.setString(2, "李四");
        statement.executeUpdate();
        int update = statement.executeUpdate();

//        回滚；
        connection.rollback(savepoint);
//        提交；
        connection.commit();

        System.out.println(update + "update  account task2 ----->ok");
    }


    public static void main(String[] args) throws SQLException, ParseException {
//        connection = DBTest.getConnection();
//        if (connection != null){
//            System.out.println("连接成功");
//        }else {
//            System.out.println("连接失败");
//        }
        account();
        insertaccount();
        task2();
//        task();
        createTable();
        insertStudent();
        getStudent();
        closeRerource();
    }

}

运行结果