package com.qf.tx;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc 以转账为案例演示
 * --------------------
 * Statement语句和PreparedStatement语句
 * 与事务操作没有影响
 */
public class Demo6_TX {

    // 张三转账给李四
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");

            // 【1 开启事务】
            conn.setAutoCommit(false);

            // 张三的钱减少100
            String sql1 = "update account set money = money - 100 where id = 1";
            ps1 = conn.prepareStatement(sql1);
            int num = ps1.executeUpdate( );
            if (num > 0) {
                System.out.println("张三转账(-100)完成!");
            }

            System.out.println(1/0 ); // 模拟在转账中,出现异常,后续不执行

            // 李四的钱要增加100
            String sql2 = "update account set money = money + 100 where id = 2";
            ps2 = conn.prepareStatement(sql2);
            int num2 = ps2.executeUpdate( );
            if (num2 > 0) {
                System.out.println("李四转账(+100)完成!");
            }

            // 【2 一切顺利,提交事务】
            conn.commit();

        } catch (Exception e) {
            try{
                // 【3 不顺利,中间有异常,回滚事务】
                conn.rollback();
            }catch (Exception e2) {
                System.out.println("回滚事务异常!!" );
                e2.printStackTrace();
            }
            System.out.println("SQL异常!!!");
            e.printStackTrace( );
        } finally {
            try {
                ps1.close( );
                ps2.close( );
                conn.close( );
            } catch (Exception e) {
                System.out.println("关流时有异常!!");
                e.printStackTrace( );
            }
        }
    }
}

package com.qf.model; // 包
public class User {   // 实体类,是表名
	// 属性是字段名
    private int id;
    private String username;
    private String password;
    private String phone;
    private Date createTime;
    private double money;
    private int sex;
    
    // setter getter...
}

package com.qf.orm;

import com.qf.model.User;

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

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc 登录完成,查询到数据,将数据封装到User对象
 * ---------------------------------------------
 * 使用ORM封装数据,将查询得到结果集封装到User对象中
 * 即登录成功  User对象中有值
 *  登录不成功 User对象不存在
 */
public class Demo7_Login_ORM {


    public static void main(String[] args) throws Exception{

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名:" );
        String username = scanner.nextLine( );

        System.out.println("请输入密码:" );
        String password = scanner.nextLine( );

        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");

        String sql = "select * from tb_user where username = ? and password = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,username);
        ps.setString(2,password);

        ResultSet rs = ps.executeQuery( );

        // 声明用于封装数据表数据的实体类
        User user = null;

        if (rs.next()) {
            // 从结果集取值
            int id = rs.getInt("id");
            String uname = rs.getString("username");
            String pwd = rs.getString("password");
            String phone = rs.getString("phone");
            Date createTime = rs.getDate("createTime");
            double money = rs.getDouble("money");
            int sex = rs.getInt("sex");

            // 创建封装数据用的实体类对象
            user = new User();
            // 开始封装
            user.setId(id);
            user.setUsername(uname);
            user.setPassword(pwd);
            user.setPhone(phone);
            user.setCreateTime(createTime); // java.sql.Date是java.util.Date的子类
            user.setMoney(money);
            user.setSex(sex);
        }

        if (user != null) {
            System.out.println("登录成功!" );
            System.out.println("个人信息为:" + user );
        } else {
            System.out.println("登录失败!" );
        }
    }
}

package com.qf.orm;

import com.qf.model.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
public class Demo8_Insert_ORM {

    public static void main(String[] args) {
        User user = new User( );
        user.setId(6);
        user.setUsername("李冰");
        user.setPassword("123456" );
        user.setCreateTime(new Date());
        user.setMoney(4000.1);
        user.setSex(2);
        user.setPhone("222333");

        insert(user);
    }

    public static void insert(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2217?useSSL=false", "root", "123456");

            String sql = "insert into tb_user (id,username,password,phone,createTime,money,sex) values (?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2,user.getUsername());
            ps.setString(3,user.getPassword());
            ps.setString(4,user.getPhone());
            ps.setDate(5,new java.sql.Date(user.getCreateTime().getTime()));
            ps.setDouble(6,user.getMoney());
            ps.setInt(7,user.getSex());

            int num = ps.executeUpdate( );
            if (num > 0) {
                System.out.println("注册成功!" );
            }
        } catch (Exception e) {
            System.out.println("SQL异常!!!" );
            e.printStackTrace();
        } finally {
            try{
                ps.close();
                conn.close();
            }catch (Exception e) {
                System.out.println("关流异常!!" );
                e.printStackTrace();
            }
       }
    }
}

package com.qf.dao;

import com.qf.model.User;
import com.qf.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
public class UserDao {

    /**
     * 根据id查用户
     *
     * @param id
     * @return
     */
    public User findUserById(int id) {
        Connection conn = DBUtil.getConnection( );
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            ps = conn.prepareStatement("select * from tb_user where id = ?");
            ps.setInt(1, id);

            rs = ps.executeQuery( );

            if (rs.next( )) {
                user = new User( );
                user.setId(rs.getInt("id"));
                user.setPhone(rs.getString("phone"));
                user.setUsername(rs.getString("username"));
                user.setSex(rs.getInt("sex"));
                user.setMoney(rs.getDouble("money"));
                user.setPassword(rs.getString("password"));
                user.setCreateTime(rs.getDate("createTime"));
            }
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            DBUtil.closeAll(conn, ps, rs);
        }
        return user;
    }

    /**
     * 查询全部数据,返回值是List集合,集合中是全部用户数据
     */
    public List<User> findAll() {
        Connection conn = DBUtil.getConnection( );
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        ArrayList<User> list = new ArrayList<>( );

        try {
            ps = conn.prepareStatement("select * from tb_user");

            rs = ps.executeQuery( );

            while (rs.next( )) {
                // 每一行数据都需要创建新对象
                user = new User( );
                user.setId(rs.getInt("id"));
                user.setPhone(rs.getString("phone"));
                user.setUsername(rs.getString("username"));
                user.setSex(rs.getInt("sex"));
                user.setMoney(rs.getDouble("money"));
                user.setPassword(rs.getString("password"));
                user.setCreateTime(rs.getDate("createTime"));

                // 将每个对象装入集合
                list.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            DBUtil.closeAll(conn, ps, rs);
        }
        return list;
    }

    /**
     * 插入数据
     */
    public boolean addUser(User user) {
        Connection conn = DBUtil.getConnection( );
        PreparedStatement ps = null;
        int num = 0;
        try {
            String sql = "insert into tb_user (id,username,password,phone,createTime,money,sex) values (?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId( ));
            ps.setString(2, user.getUsername( ));
            ps.setString(3, user.getPassword( ));
            ps.setString(4, user.getPhone( ));
            ps.setDate(5, new java.sql.Date(user.getCreateTime( ).getTime( )));
            ps.setDouble(6, user.getMoney( ));
            ps.setInt(7, user.getSex( ));

            num = ps.executeUpdate( );
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            DBUtil.closeAll(conn, ps);
        }
        return num > 0 ? true : false;
    }

    /**
     * 删除数据
     */
    public boolean deleteById(int id) {
        Connection conn = DBUtil.getConnection( );
        PreparedStatement ps = null;
        int num = 0;
        try {
            String sql = "delete from tb_user where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);

            num = ps.executeUpdate( );
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            DBUtil.closeAll(conn, ps);
        }
        return num > 0 ? true : false;
    }

    /**
     * 根据id更新用户
     * 更新user表的数据,根据User对象的属性更新,
     * 如果属性值是null则不更新,不为null就更新对应的字段
     * ---------------------------------------
     * update tb_user set username= ?,password = ?,phone = ? ,,,,
     * -------------------------------------------
     * 因为值为空不为空情况不一样,那么每次更新的sql语句也不太一样,
     * 解决方案
     * 1) 方案一: (更新全部字段)
     * 在传给updateById()的user对象中,属性全部赋值,没有null
     * 属性值跟原表一样的,更新了也跟原表没变化
     * 属性值跟原表不一样的,就会更新成新值
     * 2) 方案二: (更新部分字段)
     * 根据传给updateById()的user对象中的属性值判断
     * 属性有值,就更新,属性没有值就不更新
     * 即SQL语句要动态拼接
     */
    // 方案一
    public boolean updateById1(User user) {
        Connection conn = DBUtil.getConnection( );
        PreparedStatement ps = null;
        int num = 0;
        try {
            String sql = "update tb_user set username = ?,password = ?," +
                    "phone = ?,createTime = ?,money = ? ," +
                    "sex = ? where id = ?";
            ps = conn.prepareStatement(sql);

            ps.setString(1, user.getUsername( ));
            ps.setString(2, user.getPassword( ));
            ps.setString(3, user.getPhone( ));
            ps.setDate(4, new java.sql.Date(user.getCreateTime( ).getTime( )));
            ps.setDouble(5, user.getMoney( ));
            ps.setInt(6, user.getSex( ));
            ps.setInt(7, user.getId( ));

            num = ps.executeUpdate( );
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            DBUtil.closeAll(conn, ps);
        }
        return num > 0 ? true : false;
    }

    // 方案二
    public boolean updateById2(User user) {
        Connection conn = DBUtil.getConnection( );
        Statement statement = null;
        int num = 0;
        try {
            String sql = "update tb_user set ";
            if (user.getUsername( ) != null) {
                sql += "username = '" + user.getUsername( ) + "',";
            }
            if (user.getPassword( ) != null) {
                sql += "password = '" + user.getPassword( ) + "' ,";
            }
            if (user.getPhone( ) != null) {
                sql += "phone = '" + user.getPhone( ) + "' ,";
            }
            // ...继续拼接其他字段,我在此省略
            System.out.println("字段拼接完的sql " + sql);
            // update tb_user set username= 'aaa',password = '111',
            // 截取最后一个,
            int index = sql.lastIndexOf(",");
            String newSql = sql.substring(0, index);
            System.out.println("截取,后的sql  " + newSql);

            // 最后拼接条件
            newSql += " where id = " + user.getId( );
            System.out.println("最终的sql  " + newSql);

            statement = conn.createStatement( );
            num = statement.executeUpdate(newSql);

        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            DBUtil.closeAll(conn,statement);
        }
        return num > 0 ? true : false;
    }
}

package com.qf.test;

import com.qf.dao.UserDao;
import com.qf.model.User;

import java.util.Date;
import java.util.List;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
public class TestUserDao {

    public static void main(String[] args) {
        UserDao userDao = new UserDao( );
        // User user = userDao.findUserById(2);
        // System.out.println(user );

        // List<User> list = userDao.findAll( );
        // list.forEach(e -> System.out.println(e ));

        // User user = new User( );
        // user.setId(8);
        // user.setUsername("博文");
        // user.setPassword("123456");
        // user.setCreateTime(new Date());
        // boolean isOk = userDao.addUser(user);
        // System.out.println("插入是否成功?" + isOk );


        // boolean isOk = userDao.deleteById(4);
        // System.out.println("删除是否成功 " + isOk );

        // User user = new User( );
        // user.setId(1);
        // user.setUsername("aaaa");
        // user.setPassword("123456");
        // user.setPhone("11110000");
        // user.setMoney(1000);
        // user.setSex(1);
        // user.setCreateTime(new Date(122,10,21));
        // boolean isOk = userDao.updateById1(user);
        // System.out.println(isOk );

        User user = new User( );
        user.setId(2);
        user.setUsername("ROOT");
        user.setPassword("654321");
        boolean isOk = userDao.updateById2(user);
        System.out.println(isOk );
    }
}

/**
  * 演示获取字节码文件
  */
 public static void getClassFile() throws ClassNotFoundException {
     // 方式1
     Class<?> clazz = Class.forName("com.qf.model.User");

     // 方式2
     Class<User> clazz2 = User.class;

     // 方式3
     User user = new User( );
     Class<? extends User> clazz3 = user.getClass( );

     if (clazz.equals(clazz2) && clazz2.equals(clazz3)) {
         System.out.println("是同一个字节码文件" );
     } else {
         System.out.println("不是" );
     }
 }

/**
 * 获得字节码中的属性并操作
 */
public static void getAndSetFields() throws Exception {
    Class<User> clazz = User.class;
    /**
     * Filed getField(String name)
     *       通过属性名获得属性对象(只能获得public修饰的属性的属性对象)
     * Filed getDeclaredField()
     *       通过属性名获得属性对象(获得任意修饰符修饰的属性对象)
     */
    // Field id = clazz.getField("id");
    // System.out.println(id );

    Field id1 = clazz.getDeclaredField("id");
    System.out.println(id1 );

    /**
     * Filed[]  getFields( ) 获得public修饰所有属性对象,返回数组
     * Filed[] getDeclaredFields( )  获得所有属性对象,返回数组
     */
    Field[] fields = clazz.getFields( );
    System.out.println(fields.length );
    Field[] declaredFields = clazz.getDeclaredFields( );
    System.out.println(declaredFields.length );

    // =============================================
    // 获得属性名
    String name = id1.getName( );
    System.out.println("name = "+ name );

    // 获得属性访问修饰符
    int modifiers = id1.getModifiers( );
    System.out.println("访问修饰符: " + modifiers );

    // 获得属性值
    // 获得哪个对象的该属性值
    // 但是不能获得私有属性的值
    // 可以通过设置,就可以访问,
    id1.setAccessible(true);
    User user = new User( );
    int value = id1.getInt(user);
    System.out.println("id = " + value );

    // 设置属性值
    // 设置哪个对象的该属性值
    id1.setInt( user,11);

    System.out.println(user );

}

/**
 *  获得字节码中的方法
 */
public static void getAndSeMethod() throws Exception {
   Class<User> clazz = User.class;
   // 方法有重载,需要通过参数来确定获得哪个方法
   Method m1 = clazz.getMethod("m1"); // 获得无参的m1方法
   Method m1_ = clazz.getMethod("m1",int.class); // 获得有参的m1(int)方法

    // 获得关于方法的所有信息
    int count = m1.getParameterCount( );// 参数个数
    int count_ = m1_.getParameterCount( );// 参数个数

    // 操作方法,让方法执行
    // 参数1: 哪个对象的该方法执行
    // 参数2: 该方法执行时的参数
    Object ret = m1.invoke(new User( ));
    System.out.println("m1()执行后的返回值:" + ret );
    m1_.invoke(new User(),222);

}

/**
 *  获得字节码中的构造方法
 */
public static void getAndSeConstructor() throws Exception {
    Class<User> clazz = User.class;

    // 通过参数来获得有参还是无参构造
    Constructor<User> constructor = clazz.getConstructor( );

    // 构造方法执行,创建对象
    User user = constructor.newInstance( );

    System.out.println(user );

    // 创建字节码的对象,还有另外方法
    // 可以通过字节码,直接创建
    User user1 = clazz.newInstance( );
}

package com.qf.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * --- 天道酬勤 ---
 *
 * @author QiuShiju
 * @desc
 */
public class DBUtil {

    // 创建Properties类对象,专用于操作properties文件
    private static final Properties properties = new Properties( );
    // 声明Druid连接池的连接池对象
    // 数据连接,一般称作数据源 dataSource
    private static DruidDataSource dataSource;


    static {

        try {
            InputStream inputStream = DBUtil.class.getResourceAsStream("/jdbc.properties");
            properties.load(inputStream);
            // 不需要由我们加载驱动
            // 需要给dataSource赋值
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            System.out.println("加载驱动异常!!");
            e.printStackTrace( );
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            // 不需要我们获得连接
            // 而是通过Druid获得
            conn = dataSource.getConnection( );
        } catch (Exception e) {
            System.out.println("获得连接出异常!!!");
            e.printStackTrace( );
        }
        return conn;
    }


    /**
     * 关闭所有流
     */
    public static void closeAll(Connection conn, Statement s) {
        if (conn != null) {
            try {
                conn.close( );
            } catch (SQLException throwables) {
                throwables.printStackTrace( );
            }
        }

        if (s != null) {
            try {
                s.close( );
            } catch (SQLException throwables) {
                throwables.printStackTrace( );
            }
        }
    }

    public static void closeAll(Connection conn, Statement s, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close( );
            } catch (SQLException throwables) {
                throwables.printStackTrace( );
            }
        }

        if (s != null) {
            try {
                s.close( );
            } catch (SQLException throwables) {
                throwables.printStackTrace( );
            }
        }

        if (rs != null) {
            try {
                rs.close( );
            } catch (SQLException throwables) {
                throwables.printStackTrace( );
            }
        }
    }


    /**
     * 封装查询方法,返回一个对象
     * 参数1 执行查询的SQL,预处理的,条件用?占位
     * select * from tb_user where id = ? and username = ? and password = ?
     * 参数2 结果要封装的类
     * 参数3 给?赋值,不定长参数,是数组
     * 1,admin,123456
     */
    public static <T> T selectOne(String sql, Class<T> t, Object... args) {
        Connection conn = getConnection( );
        PreparedStatement ps = null;
        ResultSet rs = null;
        T target = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; args != null && i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery( );
            /**
             * 创建对象
             * 从数据库取出数据,并设置对象属性
             */
            while (rs.next( )) {
                target = t.newInstance( );
                Field[] declaredFields = t.getDeclaredFields( );
                for (int i = 0; i < declaredFields.length; i++) {
                    Field field = declaredFields[i];
                    Object value = rs.getObject(field.getName( ));

                    // 破解私有
                    field.setAccessible(true);

                    // 给对象的该字段赋值
                    field.set(target, value);
                }

            }
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            closeAll(conn, ps, rs);
        }
        return target;
    }

    public static <T> List<T> selectAll(String sql, Class<T> t, Object... args) {
        Connection conn = getConnection( );
        PreparedStatement ps = null;
        ResultSet rs = null;
        T target = null;
        ArrayList<T> list = new ArrayList<>( );
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; args != null && i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }

            rs = ps.executeQuery( );
            /**
             * 创建对象
             * 从数据库取出数据,并设置对象属性
             */
            while (rs.next( )) {
                target = t.newInstance( );
                Field[] declaredFields = t.getDeclaredFields( );
                for (int i = 0; i < declaredFields.length; i++) {
                    Field field = declaredFields[i];
                    Object value = rs.getObject(field.getName( ));

                    // 破解私有
                    field.setAccessible(true);

                    // 给对象的该字段赋值
                    field.set(target, value);
                }
                list.add(target);

            }
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            closeAll(conn, ps, rs);
        }
        return list;
    }

    /**
     * 增删改方法一样
     */
    public static boolean update(String sql, Object... args) {
        Connection conn = getConnection( );
        PreparedStatement ps = null;
        int num = 0;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; args != null && i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            num = ps.executeUpdate( );
        } catch (Exception e) {
            e.printStackTrace( );
        } finally {
            closeAll(conn, ps);
        }
        return num > 0 ? true : false;
    }
}