package com.sf.jdbctest;

import org.junit.Test;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Test01 {

    /**
     * 添加操作
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void t1() throws ClassNotFoundException, SQLException {
//①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
//②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8", "root", "root");
//③创建Statement对象
        Statement statement = conn.createStatement();
//④创建添加SQL语句
        String sql = "INSERT INTO user(user_name,price,create_time) VALUES('admin',100.12,now())";
//⑤执行sql语句,返回影响行数,如果需要获取主键,要配置Statement.RETURN_GENERATED_KEYS参数
        int row = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
//⑥打印影响行数
        System.out.println("row:" + row);

//如果想获取插入用户的主键可以使用statement.getGeneratedKeys()获取

        ResultSet resultSet = statement.getGeneratedKeys();
        if (resultSet.next()) {
            int id = resultSet.getInt(1);
            System.out.println(id);
        }
    }


    /**
     * 更新操作
     */
    @Test
    public void t2() throws ClassNotFoundException, SQLException {
        //①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
//②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");
//③创建Statement对象
        Statement statement = conn.createStatement();
//④创建更新SQL语句
        String sql="UPDATE user SET user_name='admin1',price=12.12,create_time=now() WHERE user_id=1000";
//⑤执行sql语句,返回影响行数
        int row = statement.executeUpdate(sql);
//⑥打印影响行数
        System.out.println("row:"+row);
    }

    /**
     * 更新操作
     */
    @Test
    public void t3() throws ClassNotFoundException, SQLException {
//①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
//②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");
//③创建Statement对象
        Statement statement = conn.createStatement();
//④创建更新SQL语句
        String sql="DELETE FROM user WHERE user_id=1000";
//⑤执行sql语句,返回影响行数
        int row = statement.executeUpdate(sql);
//⑥打印影响行数
        System.out.println("row:"+row);
    }

    /**
     * 查询操作
     */
    @Test
    public void t4() throws ClassNotFoundException, SQLException {
//创建一个集合对象,将从数据库查询出来的数据保存到集合中
        List<User> users = new ArrayList<>();
//①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
//②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");
//③创建Statement对象
        Statement statement = conn.createStatement();
//④创建SQL语句
        String sql="SELECT * FROM user";
//⑤执行sql语句,返回结果集对象
        ResultSet result = statement.executeQuery(sql);
        while(result.next()){
            User user = new User();
            int userId = result.getInt("user_id");
            user.setUserId(userId);
            String userName = result.getString("user_name");
            user.setUserName(userName);
            double price = result.getDouble("price");
            user.setPrice(price);
            Date createTime = result.getDate("create_time");
            user.setCreateTime(createTime);
            users.add(user);
        }
//打印从数据库查询出来的对象
        System.out.println(users);
    }

    /**
     * 查询一个用户(因为JDBC没有提供一个查询单个对象的结果集,所以查询单个对象时也是使用ResultSet对象)
     */
    @Test
    public void t5() throws ClassNotFoundException, SQLException {
//创建一个集合对象,将从数据库查询出来的数据保存到集合中
        List<User> users = new ArrayList<>();
//①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
//②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");
//③创建Statement对象
        Statement statement = conn.createStatement();
//④创建SQL语句
        String sql="SELECT * FROM user";
//⑤执行sql语句,返回结果集对象
        ResultSet result = statement.executeQuery(sql);
        if(result.next()){
            User user = new User();
            int userId = result.getInt("user_id");
            user.setUserId(userId);
            String userName = result.getString("user_name");
            user.setUserName(userName);
            double price = result.getDouble("price");
            user.setPrice(price);
            Date createTime = result.getDate("create_time");
            user.setCreateTime(createTime);
            users.add(user);
        }
//打印从数据库查询出来的对象
        System.out.println(users);
    }


    /**
     * 查询数据库的总记录数
     */

    @Test
    public void t6() throws ClassNotFoundException, SQLException {
        //①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
//②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");
//③创建Statement对象
        Statement statement = conn.createStatement();
//④创建SQL语句
        String sql="SELECT COUNT(*) AS count FROM user";
//⑤执行sql语句,返回结果集对象
        ResultSet result = statement.executeQuery(sql);
        if(result.next()){
            int count = result.getInt("count");
            System.out.println(count);
        }
    }


    /**
     * 分页查询
     */
    @Test
    public void t7() throws ClassNotFoundException, SQLException {
        //创建一个集合对象,将从数据库查询出来的数据保存到集合中
        List<User> users = new ArrayList<>();
        //①加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        //②创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8","root","root");
        //③创建Statement对象
        Statement statement = conn.createStatement();
        //④创建SQL语句
        String sql="SELECT * FROM user limit 0,1";
        //⑤执行sql语句,返回结果集对象
        ResultSet result = statement.executeQuery(sql);
        while(result.next()){
            User user = new User();
            int userId = result.getInt("user_id");
            user.setUserId(userId);
            String userName = result.getString("user_name");
            user.setUserName(userName);
            double price = result.getDouble("price");
            user.setPrice(price);
            Date createTime = result.getDate("create_time");
            user.setCreateTime(createTime);
            users.add(user);
        }
//打印从数据库查询出来的对象
        System.out.println(users);
    }


    /**
     * PreparedStatement接口的使用
     * 添加操作
     */
    @Test
    public void t8() throws ClassNotFoundException, SQLException {
        //加载数据库的驱动
        Class.forName("com.mysql.jdbc.Driver");
//        创建数据库的连接
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf-8", "root", "root");
        String sql = "INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";
//        预编译
        PreparedStatement pre = conn.prepareStatement(sql);
        pre.setString(1,"admin");
        pre.setDouble(2,100.21);
        pre.setDate(3,new Date(new java.util.Date().getTime()));
//        执行进行绑定参数之后的sql语句
        int i = pre.executeUpdate();
        System.out.println(i);
    }


    /**
     * 转账业务
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void t9() throws ClassNotFoundException, SQLException {
        Connection connection = null;
        //执行
        int row1=0;
        int row2=0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
             connection = DriverManager.getConnection("jdbc:mysql://localhost/jdbc?characterEncoding=utf-8&useUnicode=true", "root", "root");
            //取消默认提交
            connection.setAutoCommit(false);
            //创建Statement
            Statement statement = connection.createStatement();
            //准备sql语句
            String sql1 = "UPDATE account SET money=money-500 WHERE account_name='z3'";
            String sql2 = "UPDATE account SET money=money+500 WHERE account_name='l4'";
            row1 = statement.executeUpdate(sql1);
            //异常
            int i = 1 / 0;
            row2 = statement.executeUpdate(sql2);
            System.out.println(row1);
            System.out.println(row2);
            //手动提交
            connection.commit();
        }catch (ClassNotFoundException | SQLException e){
            //事物或滚
            if(connection != null){
                try {
                    connection.rollback();
                }catch (SQLException e1){
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }



}
