commons-dbutils的操作方法

最新推荐文章于 2022-05-15 18:11:56 发布
javaerSir
最新推荐文章于 2022-05-15 18:11:56 发布
阅读量1k
点赞数
这张是数据库表，一共三张，每个表的ID都是自动增长列
这个是项目结构
测试代码
     Java代码   
     
   
 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个表联查封装成级联bean  
         t.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();  
         }  
   
     }  
   
 }  