package com.sf.quanrizhi.day03;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Comparator;
import java.util.Properties;

public class TE {

    /**
     * PreparedStatement
     */
    @Test
    public void t1() throws ClassNotFoundException, SQLException {
    //  加载数据库的驱动
        Class.forName("com.mysql.jdbc.Driver");
        //创建数据库的连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8", "root", "root");
        //创建PreparedStatement对象
        String  sql = "insert into user(user_name,price,create_time) values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //⑤绑定参数(参数的下标从1开始而不是像数组或者集合从0开始)
        preparedStatement.setDouble(2,123.12);
        preparedStatement.setString(1,"lisa");
        preparedStatement.setDate(3,new Date(System.currentTimeMillis()));
        int row = preparedStatement.executeUpdate();
        System.out.println(row);
    }


    /**
     * 张三给李四转账500元
     */

    @Test
    public void t2(){
        Connection connection = null;
        //加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
             connection = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8", "root", "root");
            //取消默认提交方式
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            String sql1 = "UPDATE account SET money = money+500 WHERE account_name='z3'";
            String sql2 = "UPDATE account SET money = money-500 WHERE account_name='l4'";
            statement.executeUpdate(sql1);
            //  演示存在异常
//            int a = 1/0;
            statement.executeUpdate(sql2);
            //手动提交
            connection.commit();
        } catch (ClassNotFoundException | SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        }

    }

    @Test
    public void t3() throws ClassNotFoundException,SQLException{
        //执行批量处理
        Class.forName("com.mysql.jdbc.Driver");
       Connection connection = DriverManager.getConnection("jdbc:mysql:///jdbc?characterEncoding=utf-8", "root", "root");
        //取消默认提交方式
        connection.setAutoCommit(false);
        Statement statement = connection.createStatement();
        String sql1 = "UPDATE account SET money = money+500 WHERE account_name='z3'";
        String sql2 = "UPDATE account SET money = money-500 WHERE account_name='l4'";
        statement.addBatch(sql1);
        statement.addBatch(sql2);
        // 批量执行
        statement.executeBatch();
        connection.commit();
    }

    @Test
    public void t4() throws SQLException {
        //创建Druid的核心连接池对象DruidDataSource
        DruidDataSource ds = new DruidDataSource();
        //设置初始化参数
        ds.setUrl("jdbc:mysql:///jdbc?characterEncoding=utf-8");
        ds.setUsername("root");
        ds.setPassword("root");
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        //  获取数据库的连接
        Connection connection = ds.getConnection();
        //预编译
        String sql = "INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //  动态绑定参数信息
        preparedStatement.setString(1,"litiantian");
        preparedStatement.setDouble(2,12.23);
        preparedStatement.setDate(3,new Date(System.currentTimeMillis()));
        int row = preparedStatement.executeUpdate();
        System.out.println(row);

    }

    /**
     * 通过获取文件信息的方式
     */
    @Test
    public void t5() throws Exception {
        InputStream inputStream = TE.class.getClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
//采用Druid提供的工厂类创建DruidDataSource对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //  获取数据库的连接
        Connection connection = dataSource.getConnection();
        String sql = "INSERT INTO user(user_name,price,create_time) VALUES(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //  动态赋值
        preparedStatement.setString(1,"llovecoding");
        preparedStatement.setDouble(2,12.23);
        preparedStatement.setDate(3,new Date(System.currentTimeMillis()));
        int i = preparedStatement.executeUpdate();
        System.out.println(i);
    }


}