/*
* 使用DbUtils框架对数据库的CRUD
* 批处理
*
* */
public class Test {
    @org.junit.Test
    public void add() throws SQLException {
        //创建出QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (id,name) VALUES(?,?)";
        //我们发现query()方法有的需要传入Connection对象，有的不需要传入
        //区别：你传入Connection对象是需要你来销毁该Connection，你不传入，由程序帮你把Connection放回到连接池中
        queryRunner.update(sql, new Object[]{"100", "zhongfucheng"});
    }
    @org.junit.Test
    public void query()throws SQLException {
        //创建出QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT * FROM student";
        List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));
        System.out.println(list.size());
    }
    @org.junit.Test
    public void delete() throws SQLException {
        //创建出QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "DELETE FROM student WHERE id='100'";
        queryRunner.update(sql);
    }
    @org.junit.Test
    public void update() throws SQLException {
        //创建出QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE student SET name=? WHERE id=?";
        queryRunner.update(sql, new Object[]{"zhongfuchengaaa", 1});
    }
    @org.junit.Test
    public void batch() throws SQLException {
        //创建出QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (name,id) VALUES(?,?)";
        Object[][] objects = new Object[10][];
        for (int i = 0; i < 10; i++) {
            objects[i] = new Object[]{"aaa", i + 300};
        }
        queryRunner.batch(sql, objects);
    }
}

 /*
      Oracle分页语法：
        @lineSize---每页显示数据行数
        @currentPage----当前所在页
    */
    SELECT *FROM (
        SELECT 列名,列名,ROWNUM rn
        FROM 表名
        WHERE ROWNUM<=(currentPage*lineSize)) temp
    WHERE temp.rn>(currentPage-1)*lineSize;

    /*
      Oracle分页：
        Oracle的分页依赖于ROWNUM这个伪列，ROWNUM主要作用就是产生行号。
      分页原理：
        1：子查询查出前n行数据，ROWNUM产生前N行的行号
        2：使用子查询产生ROWNUM的行号，通过外部的筛选出想要的数据
      例子：
        我现在规定每页显示5行数据【lineSize=5】，我要查询第2页的数据【currentPage=2】
        注：【对照着语法来看】
      实现：
        1：子查询查出前10条数据【ROWNUM<=10】
        2：外部筛选出后面5条数据【ROWNUM>5】
        3：这样我们就取到了后面5条的数据
    */

    /*
      Mysql分页语法：
      @start---偏移量，不设置就是从0开始【也就是(currentPage-1)*lineSize】
      @length---长度，取多少行数据
    */
    SELECT *
    FROM 表名
    LIMIT [START], length;
    /*
      例子：
        我现在规定每页显示5行数据，我要查询第2页的数据
      分析：
        1：第2页的数据其实就是从第6条数据开始，取5条
      实现：
        1：start为5【偏移量从0开始】
        2：length为5
*/

 //每页显示3条数据
        int lineSize = 3;
        //总记录数
        int totalRecord = getTotalRecord();
        //假设用户指定的是第2页
        int currentPage = 2;
        //一共有多少页
        int pageCount = getPageCount(totalRecord, lineSize);
        //使用什么数据库进行分页，记得要在JdbcUtils中改配置
        List<Person> list = getPageData2(currentPage, lineSize);
        for (Person person : list) {
            System.out.println(person);
        }
    }
    //使用JDBC连接Mysql数据库实现分页
    public static List<Person> getPageData(int currentPage, int lineSize) throws SQLException {
        //从哪个位置开始取数据
        int start = (currentPage - 1) * lineSize;
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT name,address  FROM person LIMIT ?,?";
        List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{start, lineSize});
        return persons;
    }
    //使用JDBC连接Oracle数据库实现分页
    public static List<Person> getPageData2(int currentPage, int lineSize) throws SQLException {
        //从哪个位置开始取数据
        int start = (currentPage - 1) * lineSize;
        //读取前N条数据
        int end = currentPage * lineSize;
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT " +
                "  name, " +
                "  address " +
                "FROM ( " +
                "  SELECT " +
                "    name, " +
                "    address , " +
                "    ROWNUM rn " +
                "  FROM person " +
                "  WHERE ROWNUM <= ? " +
                ")temp WHERE temp.rn>?";
        List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{end, start});
        return persons;
    }
    public static int getPageCount(int totalRecord, int lineSize) {
        //简单算法
        //return (totalRecord - 1) / lineSize + 1;
        //此算法比较好理解，把数据代代进去就知道了。
        return totalRecord % lineSize == 0 ? (totalRecord / lineSize) : (totalRecord / lineSize) + 1;
    }
    public static int  getTotalRecord() throws SQLException {
        //使用DbUtils框架查询数据库表中有多少条数据
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT COUNT(*) FROM person";
        Object o = queryRunner.query(sql, new ScalarHandler());
        String ss = o.toString();
        int  s = Integer.parseInt(ss);
        return s;
    }