VIP23/02_MySql/jdbc-day01/src/com/lovecoding/jdbc/TestJdbc03.java

package com.lovecoding.jdbc;

import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

public class TestJdbc03 {


    //连接对象
    Connection connection = null;

    Statement statement = null;

    @Before
    public void before() throws ClassNotFoundException, SQLException {
        //连接mysql
        //1 加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //8.0 需要加cj
        //2 创建连接  需要指定 端口 地址  用户名 密码
        //public static Connection getConnection(String url,String user, String password)
        //localhost 数据库地址 3306 端口 lovecoding 数据库 8.0之后serverTimezone=UTC
        String url = "jdbc:mysql://localhost:3306/lovecoding?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&rewriteBatchedStatements=true&useSSL=false";
        //网络编程
        connection = DriverManager.getConnection(url, "root", "root");

        statement = connection.createStatement();

    }

    // sql注入问题 万能密码
    // 批处理
    // 事务
    @Test
    public void test01() throws Exception {
        // sql拼接
        String ename = "李四";
        double salary = 15000;//15000
        String birthday = "1990-1-1";//1990-1-1
        String tel = "13578595685";//13578595685
        String email = "'zhangsan,','1'";//'zhangsan,','1'
        //sql执行对象
        //4 编写sql 添加
        // 原因1 需要加单引号  2 hiredate 非空 需要给值
        String sql = " INSERT INTO `lovecoding`.`t_employee` ( ename, salary, birthday, tel, email) VALUES ( '"+ename+"' , '"+salary+"' , '"+birthday+"' , '"+tel+"' ,'"+email+"' ); ";
        // VALUES ( '李四' , 15000 , '1990-1-1' , '13578595685' ,''zhangsan,','1'' );
        int i = statement.executeUpdate(sql);
        //6 判断
        if (i > 0) {
            System.out.println("执行成功");
        }else{
            System.out.println("执行失败");
        }
        //关闭资源
        statement.close();
        connection.close();

    }

    //SQL拼接
    @Test
    public void test02() throws Exception {
        // sql拼接
        String ename = "李四";
        double salary = 15000;//15000
        String birthday = "1990-1-1";//1990-1-1
        String tel = "13578595685";//13578595685
        String email = "'zhangsan,','1'";//'zhangsan,','1'
        //sql执行对象
        //4 编写sql 添加
        // 原因1 需要加单引号  2 hiredate 非空 需要给值
        String sql = " INSERT INTO `lovecoding`.`t_employee` " +
                " ( ename, salary, birthday, tel, email) " +
                " VALUES ( ? , ? , ? , ? , ? ); ";

        //PreparedStatement 可解决sql拼接问题
        //'zhangsan,','1'
        // Statement 拼接 sql
        // PreparedStatement 解析?  setString参数字符串去处理。
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //注入参数 ?
        // 索引从 1 开始
        preparedStatement.setString(1,ename);
        preparedStatement.setDouble(2,salary);
        preparedStatement.setString(3,birthday);
        preparedStatement.setString(4,tel);
        preparedStatement.setString(5,email);

        int i = preparedStatement.executeUpdate();
        //6 判断
        if (i > 0) {
            System.out.println("执行成功");
        }else{
            System.out.println("执行失败");
        }
        //关闭资源
        preparedStatement.close();
        connection.close();

    }

    // 解决
    // PreparedStatement
    // 设置 参数 替换 拼接的sql
    // 参数 用 ? 索引  设置 ? 的值。

    //sql注入
    //select * from t_employee where eid = 值;
    @Test
    public void test03() throws Exception {
        String eid = "35 or 1=1"; // 35 or 1=1 万能密码  select where name=？ ，password = ？  or 1=1
        // " select * from t_employee where eid = 35 or 1=1 "
        String sql = " select * from t_employee where eid =  "+ eid;
        //执行sql
        ResultSet rs = statement.executeQuery(sql);
        //遍历
        while (rs.next()){
            String id = rs.getString(1);
            String ename = rs.getString(2);

            System.out.println(id + "-" +ename);
        }

        rs.close();
        statement.close();
        connection.close();

    }

    @Test
    public void test04() throws Exception {
        String eid = "a34or 1=1";
        // " select * from t_employee where eid = '35 or 1=1' "
        String sql = " select * from t_employee where eid = ? "; //34or 1=1 转换数字 数字直接转 34
        //执行sql
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //preparedStatement设置值

        preparedStatement.setString(1,eid);
        //执行查询
        ResultSet rs = preparedStatement.executeQuery();
        //遍历
        while (rs.next()){
            String id = rs.getString(1);
            String ename = rs.getString(2);

            System.out.println(id + "-" +ename);
        }

        rs.close();
        statement.close();
        connection.close();

    }

    //获取自增长键值
    @Test
    public void test05() throws Exception {
        // sql拼接
        String ename = "李四1";
        double salary = 15000;//15000
        String birthday = "1990-1-1";//1990-1-1
        String tel = "13578595685";//13578595685
        String email = "'zhangsan,','1'";//'zhangsan,','1'
        //sql执行对象
        //4 编写sql 添加
        // 原因1 需要加单引号  2 hiredate 非空 需要给值
        String sql = " INSERT INTO `lovecoding`.`t_employee` " +
                " ( ename, salary, birthday, tel, email) " +
                " VALUES ( ? , ? , ? , ? , ? ); ";
        //PreparedStatement
        //获取自增主键参数 Statement.RETURN_GENERATED_KEYS
        //getGeneratedKeys();
        PreparedStatement preparedStatement = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        //注入参数
        preparedStatement.setString(1,ename);
        preparedStatement.setDouble(2,salary);
        preparedStatement.setString(3,birthday);
        preparedStatement.setString(4,tel);
        preparedStatement.setString(5,email);

        int i = preparedStatement.executeUpdate();
        //自增主键
        ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
        while (generatedKeys.next()){
            Object id = generatedKeys.getObject(1);
            System.out.println(id);
        }

        //6 判断
        if (i > 0) {
            System.out.println("执行成功");
        }else{
            System.out.println("执行失败");
        }
        //关闭资源
        preparedStatement.close();
        connection.close();

    }

    //通过 preparedStatement 完成 添加或者修改。

    //批处理(了解)
    @Test
    public void test06() throws Exception {
        //添加1000记录
        long l1 = System.currentTimeMillis();
        // sql拼接
        String ename = "测试用户";
        double salary = 15000;//15000
        String birthday = "1990-1-1";//1990-1-1
        String tel = "13578595685";//13578595685
        String email = "'zhangsan,','1'";//'zhangsan,','1'
        //sql执行对象
        //4 编写sql 添加
        // 原因1 需要加单引号  2 hiredate 非空 需要给值
        String sql = " INSERT INTO `lovecoding`.`t_employee` " +
                " ( ename, salary, birthday, tel, email) " +
                " VALUES ( ? , ? , ? , ? , ? ); ";

        PreparedStatement preparedStatement = null;
        for (int i = 0; i < 1000; i++) {

            preparedStatement = connection.prepareStatement(sql);
            //注入参数
            preparedStatement.setString(1,ename+i);
            preparedStatement.setDouble(2,salary);
            preparedStatement.setString(3,birthday);
            preparedStatement.setString(4,tel);
            preparedStatement.setString(5,email);
            //执行
            preparedStatement.executeUpdate();
        }

        long l2 = System.currentTimeMillis();
        System.out.println(l2-l1); //736
        //关闭资源
        preparedStatement.close();
        connection.close();
    }

    //连接参数 在url后面再加一个参数 rewriteBatchedStatements=true
    //添加到批处理 addBatch()
    // INSERT INTO 表名 values(值0,值1)
    // 分号 去掉;
    @Test
    public void test07() throws Exception {
        //添加1000记录
        long l1 = System.currentTimeMillis();
        // sql拼接
        String dname = "批处理部门";
        String desc = "部门介绍";
        //sql执行对象
        //4 编写sql 添加
        String sql = " INSERT INTO `lovecoding`.`t_department`  VALUES ( null , ? , ?  ) ";

        PreparedStatement  preparedStatement = connection.prepareStatement(sql);

        for (int i = 0; i < 1000; i++) {
            //注入参数
            preparedStatement.setObject(1,dname+i);
            preparedStatement.setObject(2,desc);
            //添加
            preparedStatement.addBatch();
        }
        //整体执行
        preparedStatement.executeBatch();

        long l2 = System.currentTimeMillis();
        System.out.println(l2-l1); // 31
        //关闭资源
        preparedStatement.close();
        connection.close();
    }


    //事务
    /*
    update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' where did = 3;

	故意把其中一条sql语句写错。

    update t_department set description = 'xx' where did = 2;
	update t_department set description = 'yy' what did = 3;  #what是错误的
     */
    @Test
    public void test08() throws Exception {
        //sql对象
        Statement statement = connection.createStatement();
        //转账
        String sql1 = " update t_department set dname = 'aa' where did = 2 ";
        String sql2 = " update t_department set dname = 'aa' where did = 3 ";

        //手动事务
        connection.setAutoCommit(false);
        try {
            statement.executeUpdate(sql1);
            statement.executeUpdate(sql2);
            //正常提交代码
            connection.commit();
        } catch (SQLException e) {
            //回滚
            connection.rollback();
            e.printStackTrace();
        }
        //释放资源
        statement.close();
        connection.close();
    }


}