DButils的使用

dbutils能极大的减少程序代码的数量，但不利于程序员思维逻辑的培养

1. package com.test.dbutils.dao;  
2.  //dbutils 的运用
3. import java.sql.SQLException;  
4. import java.util.List;  
5. import org.apache.commons.dbutils.QueryRunner;  
6. import org.apache.commons.dbutils.handlers.BeanHandler;  
7. import org.apache.commons.dbutils.handlers.BeanListHandler;  
8. import org.apache.commons.dbutils.handlers.ScalarHandler;  
9. import com.xiaohui.cusSys.domain.Customer;  
10. import com.xiaohui.cusSys.util.JdbcUtil;  
11.   
12. public class customerDao {  
13.     private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());  
14.   
15.     // 根据id返回 Customer 对象  
16.     public Customer getCustomerById(int id) throws SQLException {  
17.         String sql = "select * from customer where id = ?";  
18.         Customer cus = (Customer) qr.query(sql,  
19.                 new BeanHandler(Customer.class), id);  
20.         return cus;  
21.     }  
22.   
23. 
    
24.     // 返回记录的总数目  
25.     public int getAllCustomer() throws SQLException {  
26.         String sql = "select count(*) from customer";  
27.         Long temp = qr.query(sql, new ScalarHandler());  
28.         return temp.intValue();  
29.     }  
30.   
31.     // 根据ID删除指定的记录  
32.     public void deleteCustomerById(int id) throws SQLException {  
33.         String sql = "delete from customer where id = ?";  
34.         qr.update(sql, id);  
35.     }  
36.   
37.     // 根据id更新记录信息  
38.     public void updateCustomerById(Customer newCus) throws SQLException {  
39.         String sql = "update customer set name= ?,address= ?,tel= ?,mail= ?,birthday= ? where id= ?";  
40.         qr.update(  
41.                 sql,  
42.                 new Object[] { newCus.getName(), newCus.getAddress(),  
43.                         newCus.getTel(), newCus.getMail(),  
44.                         newCus.getBirthday(), newCus.getId() });  
45.     }  
46.   
47.     // 添加记录  
48.     public void addCustomer(Customer newCus) throws SQLException {  
49.         String sql = "insert into customer(name,address,tel,mail,birthday) values(?,?,?,?,?)";  
50.         qr.update(sql, new Object[] { newCus.getName(), newCus.getAddress(),  
51.                 newCus.getTel(), newCus.getMail(),  
52.                 // //将java.util.Date 转换为 java.sql.Date  
53.                 // new java.sql.Date( newCus.getBirthday().getTime())  
54.                 newCus.getBirthday() });  
55.     } 
56. } 

1. 下面是用逻辑思维一步步编写

1. package com.test.Customerdao;

   
   

   import java.sql.Connection;

   import java.sql.PreparedStatement;

   import java.sql.ResultSet;

   import java.sql.Statement;

   import java.util.ArrayList;

   import java.util.List;

   
   

   import com.test.entity.Customer;

   import com.test.utils.C3P0Utils;

   import com.test.utils.J1602DBUtils;

   import com.test.utils.SQLXMLParse;

   
   

   public class CustomerDAO {

   /**根据ID查询客户信息

   * @param id View传递的ID

   * @return 客户信息

   * @throws Exception

   */

   public Customer getCustomerById(int id) throws Exception {

   String sql = SQLXMLParse.getSQLByKey("getCustomerById"); //解析XML文件中的K键，V值——[select * from customer where id=? ]

   try (Connection conn = C3P0Utils.getC3P0Connection();       //c3p0连接池

   PreparedStatement ps = conn.prepareStatement(sql);) {

   ps.setInt(1, id);     //将id 的值传入SQL语句中的第一个“？”中                                     

   try (ResultSet rs = ps.executeQuery();) {

   Customer cust = new Customer();   //new 一个新的对象容器来接受SQL查询出来的对象

   if (rs.next()) {

   // cust_name,cust_ticket,cust_mobile,cust_address

   cust.setId(id);

   cust.setCustName(rs.getString(1));

   cust.setTicket(rs.getInt(2));

   cust.setCustMobile(rs.getString(3));

   cust.setCustAddress(rs.getString(4));

   }

   return cust;

   }

   }

   }

   /**

   * 插入一条记录

   * @param cust

   * @return 受影响行数

   * @throws Exception

   */

   public int saveCustomer(Customer cust) throws Exception{

   String sql = SQLXMLParse.getSQLByKey("saveCustomer");

   try(

   Connection conn = C3P0Utils.getC3P0Connection();

   PreparedStatement ps = conn.prepareStatement(sql);

   ){

   ps.setString(1,cust.getCustName());

   ps.setInt(2,cust.getTicket());

   ps.setString(3,cust.getCustMobile());

   ps.setString(4,cust.getCustAddress());

   return ps.executeUpdate();

   }

   }

   /**

   * 根据ID删除对应的Customer

   * @param id 浏览器传入的客户ID

   * @return 删除成功受影响的行数

   * @throws Exception

   */

   public int deleteCustomerById(int id)throws Exception{

   String sql = SQLXMLParse.getSQLByKey("deleteCustomerById");

   try(

   Connection conn = C3P0Utils.getC3P0Connection();

   PreparedStatement ps = conn.prepareStatement(sql);

   ){

   ps.setInt(1, id);

   return ps.executeUpdate();

   }

   }

   public List<Customer> getAllCustomerTwo()throws Exception{

   String sql = SQLXMLParse.getSQLByKey("getAllCustomer");

   try(

   Connection conn =C3P0Utils.getC3P0Connection();

   ){

   return J1802DBUtils.getAll(Customer.class, sql);

   }

   }

   public List<Customer> getAllCustomer()throws Exception{

   String sql = SQLXMLParse.getSQLByKey("getAllCustomer");

   try(

   //Connection conn = JDBCUtils.getConn();

   //Connection conn = DBCPUtil.getDBCPConnection();

   Connection conn =C3P0Utils.getC3P0Connection();

   Statement st = conn.createStatement();

   ResultSet rs = st.executeQuery(sql);

   ){

   List<Customer> list = new ArrayList<>();

   while(rs.next()){

   Customer cust = new Customer();

   //id,cust_name,cust_ticket,cust_mobile,cust_address 

   cust.setId(rs.getInt(1));

   cust.setCustName(rs.getString(2));

   cust.setTicket(rs.getInt(3));

   cust.setCustMobile(rs.getString(4));

   cust.setCustAddress(rs.getString(5));

   list.add(cust);

   }

   return list;

   }

   }

   }