package com.jdbc.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import org.apache.commons.dbutils.BeanProcessor;import org.apache.commons.dbutils.DbUtils;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.ResultSetHandler;import org.apache.commons.dbutils.handlers.ArrayHandler;import org.apache.commons.dbutils.handlers.ArrayListHandler;import org.apache.commons.dbutils.handlers.BeanHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import com.jdbc.db.DBConnection;import com.jdbc.entity.Book;import com.jdbc.entity.School;import com.jdbc.entity.Student;public class Test {public static void main(String[] args) {Test t = new Test(); // dbutils 自带的一个结果集处理类，只把查询结果的第一行封装成数组t.testArrayHandler(); // dbutils 自带的一个结果集处理类，把查询结果的每一个行都封装到数组再把数组装到集合中t.testArrayListHandler(); // dbutils 自带的一个结果集处理类，把查询结果封装在bean中t.testBeanHandler(); // dbutils 自带的一个结果集处理类，把查询结果封装在bean中再把bean装到集合中t.testBeanListHandler(); // 两个表查封装到一个bean中t.queryTwoTable(); // 把两个表联查的每行数据封装到bean中再装入集合t.queryListTwoTable(); // 3个表联查封装成级联beant.queryListThreeTable(); t.insert();t.upload();t.detele(); // 把数据插入两张表t.insertTwoTable();}public void testArrayHandler() {ResultSetHandler<Object[]> rsh = new ArrayHandler();QueryRunner qr = new QueryRunner(new DBConnection());String sql = "select * from student, book where student.book_id = book.book_id";try {Object[] arr = qr.query(sql, rsh);for (int i = 0; i < arr.length; i++)System.out.print(arr[i].toString() + "\t");System.out.println("");} catch (SQLException e) {e.printStackTrace();}}public void testArrayListHandler() {ResultSetHandler<List<Object[]>> rsh = new ArrayListHandler();QueryRunner qr = new QueryRunner(new DBConnection());String sql = "select * from student, book where student.book_id = book.book_id";try {List<Object[]> list = qr.query(sql, rsh);int size = list.size();for (int i = 0; i < size; i++) {Object[] arr = list.get(i);for (int j = 0; j < arr.length; j++)System.out.print(arr[j].toString() + "\t");System.out.println("");}System.out.println("");} catch (SQLException e) {e.printStackTrace();}}public void testBeanHandler() {ResultSetHandler<Student> rsh = new BeanHandler<Student>(Student.class);QueryRunner qr = new QueryRunner(new DBConnection());String sql = "select * from student, book where student.book_id = book.book_id";try {Student student = qr.query(sql, rsh);System.out.print(student.getStudent_id() + "\t");System.out.print(student.getStudent_name() + "\t");System.out.print(student.getStudent_age() + "\t");System.out.print(student.getStudent_email() + "\n");} catch (SQLException e) {e.printStackTrace();}}public void queryTwoTable() {QueryRunner qr = new QueryRunner(new DBConnection());String sql = "select * from student, book where student.book_id = book.book_id";try {Student student = qr.query(sql, new ResultSetHandler<Student>() {public Student handle(ResultSet rs) throws SQLException {Student student = null;if (rs.next()) {BeanProcessor bp = new BeanProcessor();student = bp.toBean(rs, Student.class);Book book = bp.toBean(rs, Book.class);student.setBook(book);}return student;}});System.out.print(student.getStudent_id() + "\t");System.out.print(student.getStudent_name() + "\t");System.out.print(student.getStudent_age() + "\t");System.out.print(student.getStudent_email() + "\t");System.out.print(student.getBook().getBook_id() + "\t");System.out.print(student.getBook().getBook_name() + "\t");System.out.print(student.getBook().getBook_price() + "\n");} catch (SQLException e) {e.printStackTrace();}}public void testBeanListHandler() {ResultSetHandler<List<Student>> rsh = new BeanListHandler<Student>(Student.class);QueryRunner qr = new QueryRunner(new DBConnection());String sql = "select * from student, book where student.book_id = book.book_id";try {List<Student> list = qr.query(sql, rsh);int size = list.size();for (int i = 0; i < size; i++) {Student student = list.get(i);System.out.print(student.getStudent_id() + "\t");System.out.print(student.getStudent_name() + "\t");System.out.print(student.getStudent_age() + "\t");System.out.print(student.getStudent_email() + "\n");}} catch (SQLException e) {e.printStackTrace();}}public void queryListTwoTable() {QueryRunner qr = new QueryRunner(new DBConnection());String sql = "select * from student, book where student.book_id = book.book_id";try {List<Student> list = qr.query(sql,new ResultSetHandler<List<Student>>() {public List<Student> handle(ResultSet rs) throws SQLException {List<Student> list = new ArrayList<Student>();BeanProcessor bp = new BeanProcessor();while (rs.next()) {Student student = bp.toBean(rs, Student.class);Book book = bp.toBean(rs, Book.class);student.setBook(book);list.add(student);}return list;}});if (list != null && !list.isEmpty()) {for (Student student : list) {System.out.print(student.getStudent_id() + "\t");System.out.print(student.getStudent_name() + "\t");System.out.print(student.getStudent_age() + "\t");System.out.print(student.getStudent_email() + "\t");System.out.print(student.getBook().getBook_id() + "\t");System.out.print(student.getBook().getBook_name() + "\t");System.out.print(student.getBook().getBook_price() + "\n");}}} catch (SQLException e) {e.printStackTrace();}}public void queryListThreeTable() {Connection conn = null;QueryRunner qr = new QueryRunner();List<School> schoolList = null;try {conn = new DBConnection().getConnection();String sql = "select * from school";schoolList = qr.query(conn, sql, new BeanListHandler<School>(School.class));if (schoolList != null && !schoolList.isEmpty()) {for (School school : schoolList) {sql = "select * from student, book where student.book_id = book.book_id and student.school_id = ?";ResultSetHandler<List<Student>> rsh = new ResultSetHandler<List<Student>>() {public List<Student> handle(ResultSet rs) throws SQLException {List<Student> list = new ArrayList<Student>();BeanProcessor bp = new BeanProcessor();while (rs.next()) {Student student = bp.toBean(rs, Student.class);Book book = bp.toBean(rs, Book.class);student.setBook(book);list.add(student);}return list;}};List<Student> studentList = qr.query(conn, sql, rsh, school.getSchool_id());school.setStudentList(studentList);}}} catch (SQLException e) {e.printStackTrace();} finally {DbUtils.closeQuietly(conn);}if (schoolList != null && !schoolList.isEmpty()) {for (School school : schoolList) {System.out.print(school.getSchool_id() + "\t");System.out.print(school.getSchool_name() + "\t");System.out.print(school.getSchool_address() + "\n");for (Student student : school.getStudentList()) {System.out.print("\t" + student.getStudent_id() + "\t");System.out.print(student.getStudent_name() + "\t");System.out.print(student.getStudent_age() + "\t");System.out.print(student.getStudent_email() + "\t");System.out.print(student.getBook().getBook_id() + "\t");System.out.print(student.getBook().getBook_name() + "\t");System.out.print(student.getBook().getBook_price() + "\n");}}}}public void insert() {QueryRunner qr = new QueryRunner(new DBConnection());String sql = "insert into student values(?, ?, ?, ?)";Object[] params = new Object[] { "seven", 23, "seven123@qq.com", 1 };try {System.out.println(qr.update(sql, params));} catch (SQLException e) {e.printStackTrace();}}public void insertTwoTable() {Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;try {conn = new DBConnection().getConnection();QueryRunner qr = new QueryRunner();conn.setAutoCommit(false);String sql = "insert into book values(?, ?)";Object[] params = new Object[] { "C#", 99.36 };pst = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);qr.fillStatement(pst, params);pst.execute();rs = pst.getGeneratedKeys();int book_id = rs.next() ? rs.getInt(1) : 0;sql = "insert into student values(?, ?, ?, ?)";params = new Object[] { "seven", 23, "seven123@qq.com", book_id };pst = conn.prepareStatement(sql);qr.fillStatement(pst, params);pst.execute();conn.commit();} catch (SQLException e) {try {DbUtils.rollback(conn);} catch (SQLException e1) {e1.printStackTrace();}e.printStackTrace();} finally {DbUtils.closeQuietly(conn, pst, rs);}}public void upload() {QueryRunner qr = new QueryRunner(new DBConnection());String sql = "update student set student_name = ? where student_id = ?";Object[] params = new Object[] { "seven+++", 11 };try {System.out.println(qr.update(sql, params));} catch (SQLException e) {e.printStackTrace();}}public void detele() {QueryRunner qr = new QueryRunner(new DBConnection());String sql = "delete from student where student_id = ?";Object[] params = new Object[] { 11 };try {System.out.println(qr.update(sql, params));} catch (SQLException e) {e.printStackTrace();}}}
