dbutils能极大的减少程序代码的数量,但不利于程序员思维逻辑的培养
- package com.test.dbutils.dao;
- //dbutils 的运用
- import java.sql.SQLException;
- import java.util.List;
- import org.apache.commons.dbutils.QueryRunner;
- import org.apache.commons.dbutils.handlers.BeanHandler;
- import org.apache.commons.dbutils.handlers.BeanListHandler;
- import org.apache.commons.dbutils.handlers.ScalarHandler;
- import com.xiaohui.cusSys.domain.Customer;
- import com.xiaohui.cusSys.util.JdbcUtil;
- public class customerDao {
- private QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
- // 根据id返回 Customer 对象
- public Customer getCustomerById(int id) throws SQLException {
- String sql = "select * from customer where id = ?";
- Customer cus = (Customer) qr.query(sql,
- new BeanHandler(Customer.class), id);
- return cus;
- }
- // 返回记录的总数目
- public int getAllCustomer() throws SQLException {
- String sql = "select count(*) from customer";
- Long temp = qr.query(sql, new ScalarHandler());
- return temp.intValue();
- }
- // 根据ID删除指定的记录
- public void deleteCustomerById(int id) throws SQLException {
- String sql = "delete from customer where id = ?";
- qr.update(sql, id);
- }
- // 根据id更新记录信息
- public void updateCustomerById(Customer newCus) throws SQLException {
- String sql = "update customer set name= ?,address= ?,tel= ?,mail= ?,birthday= ? where id= ?";
- qr.update(
- sql,
- new Object[] { newCus.getName(), newCus.getAddress(),
- newCus.getTel(), newCus.getMail(),
- newCus.getBirthday(), newCus.getId() });
- }
- // 添加记录
- public void addCustomer(Customer newCus) throws SQLException {
- String sql = "insert into customer(name,address,tel,mail,birthday) values(?,?,?,?,?)";
- qr.update(sql, new Object[] { newCus.getName(), newCus.getAddress(),
- newCus.getTel(), newCus.getMail(),
- // //将java.util.Date 转换为 java.sql.Date
- // new java.sql.Date( newCus.getBirthday().getTime())
- newCus.getBirthday() });
- }
- }
- 下面是用逻辑思维一步步编写
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;
}
}
}