使用commonsdbutils

2019独角兽企业重金招聘Python工程师标准>>>

package common;import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties;/*** 数据库连接工厂* &＃64;author sally**/ public class ConnectionFactory { private static Properties prop&＃61;new Properties();static{try {prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));} catch (IOException e) {e.printStackTrace();System.out.println("在classpath下没有找到jdbc.properties文件");}}/*** 根据数据库的默认连接参数获取数据库的Connection对象* &＃64;return 成功&＃xff0c;返回Connection对象&＃xff0c;否则返回null*/public static Connection getConnection(){Connection conn&＃61;null;try {Class.forName(prop.getProperty("driver"));conn&＃61;DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("user"), prop.getProperty("password"));} catch (ClassNotFoundException e) {e.printStackTrace();}catch (SQLException e) {e.printStackTrace();}return conn;} }

package dao;public class DaoException extends RuntimeException {/*** */private static final long serialVersionUID &＃61; -4554375587092492837L;public DaoException(){// TODO Auto-generated constructor stub}public DaoException(String arg0) {super(arg0);// TODO Auto-generated constructor stub}public DaoException(Throwable arg0) {super(arg0);// TODO Auto-generated constructor stub}public DaoException(String arg0, Throwable arg1) {super(arg0, arg1);// TODO Auto-generated constructor stub}public DaoException(String arg0, Throwable arg1, boolean arg2, boolean arg3) {super(arg0, arg1, arg2, arg3);// TODO Auto-generated constructor stub}}

package dao;import java.io.IOException; import java.util.Properties;public class DaoFactory {private static Properties prop&＃61;new Properties();static{try {prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("dao.properties"));} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();System.out.println("在classpath下没有找到Dao工厂的配置文件dao.properties");}}private DaoFactory() {}/*** 从dao.properties获取对象* &＃64;param daoName dao的标识名* &＃64;return 对应的实现类的实例*/public static Object getInstance(String daoName){Object obj&＃61;null;String className&＃61;prop.getProperty(daoName);try {obj&＃61;Class.forName(className).newInstance();} catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}return obj;} }

package dao;import java.util.List;import entity.PageModel; import entity.Product;public interface ProductDao {/** * 新增产品* &＃64;param pro 产品实例* &＃64;throws DaoException 如果操作失败&＃xff0c;会抛出DaoException这个非受检异常* */public void insert(Product pro) throws DaoException;/** 更新产品信息*/public void update(Product pro) throws DaoException;/** 根据ID删除产品*/public void delete(int id) throws DaoException;/** 查询所有产品&＃xff0c;以List方式返回* &＃64;return 符合条件的产品实例的列表* &＃64;throws DaoException 如果操作失败&＃xff0c;会抛出DaoException这个非受检的异常* */public List getProductList() throws DaoException;/** 根据ID查询产品* &＃64;param id 产品id* &＃64;return 符合条件的产品实例&＃xff0c;如果不存在&＃xff0c;返回null* &＃64;throws DaoException 如果操作失败&＃xff0c;会抛出DaoException这个非受检异常* */public Product getProduct(int id) throws DaoException;/** 分页查询数据* &＃64;param pageSize是每页显示的记录数* &＃64;param pageNo要查询的页号* &＃64;return 符合条件的分布数据模型PageModel实例* &＃64;throws DaoException* */public PageModel getProListByPage(int pageNo,int pageSize) throws DaoException;/** 根据姓名模糊查询员工&＃xff0c;以分页数据方式返回*/public PageModel getProListByPage(String name,int pageNo,int pageSize) throws DaoException;}

package entity;import java.io.Serializable; import java.util.List;public class PageModel implements Serializable{/*** */private static final long serialVersionUID &＃61; 8650004680285850659L;private Long recordCount;private List data;public Long getRecordCount() {return recordCount;}public void setRecordCount(Long recordCount) {this.recordCount &＃61; recordCount;}public List getData() {return data;}public void setData(List data) {this.data &＃61; data;}}

package entity;import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Date;public class Product {private int id;private String name;private double price;private Date date;public Product() {super();}public Product(int id, String name, double price, Date date) {super();this.id &＃61; id;this.name &＃61; name;this.price &＃61; price;this.date &＃61; date;}public int getId() {return id;}public void setId(int id) {this.id &＃61; id;}public String getName() {return name;}public void setName(String name) {this.name &＃61; name;}public double getPrice() {return price;}public void setPrice(double price) {this.price &＃61; price;}public Date getDate() {return date;}public void setDate(Date date) {this.date &＃61; date;}&＃64;Overridepublic String toString() {DateFormat df&＃61;new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String str&＃61;this.date&＃61;&＃61;null?"":df.format(date);return "Product [id&＃61;" &＃43; id &＃43; ", name&＃61;" &＃43; name &＃43; ", price&＃61;" &＃43; price&＃43; ", date&＃61;" &＃43; str &＃43; "]";}}

package impl;import java.sql.Connection; import java.sql.SQLException; import java.util.List;import org.apache.commons.dbutils.DbUtils; 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 common.ConnectionFactory; import dao.DaoException; import dao.ProductDao; import entity.PageModel; import entity.Product;/*** 产品的Dao操作实现类* &＃64;author sally**/public class ProductDaoImpl implements ProductDao {private QueryRunner qr&＃61;new QueryRunner();//commons-dbutils包带的&＃xff1a;SQL的执行器&＃xff0c;线程安全的&＃64;Overridepublic void insert(Product pro) throws DaoException {Connection conn&＃61;ConnectionFactory.getConnection();String sql&＃61;"insert into product(name,price,date)values(?,?,now())";Object[] params&＃61;{pro.getName(),pro.getPrice()};try {qr.update(conn, sql, params);} catch (SQLException e) {throw new DaoException("新增产品时出现异常",e);}finally{DbUtils.closeQuietly(conn);}}&＃64;Overridepublic void update(Product pro) throws DaoException {Connection conn&＃61;ConnectionFactory.getConnection();String sql&＃61;"update product set name&＃61;?,price&＃61;?,date&＃61;? where id&＃61;?";Object[] params&＃61;{pro.getName(),pro.getPrice(),pro.getDate(),pro.getId()};try {qr.update(conn, sql, params);} catch (SQLException e) {throw new DaoException("产品更新时出现异常",e);}finally{DbUtils.closeQuietly(conn);}}&＃64;Overridepublic void delete(int id) throws DaoException {Connection conn&＃61;ConnectionFactory.getConnection();String sql&＃61;"delete from product where id&＃61;?";try {qr.update(conn, sql, id);} catch (SQLException e) {throw new DaoException("产品删除时出现异常",e);}finally{DbUtils.closeQuietly(conn);}}&＃64;Overridepublic List getProductList() throws DaoException {List list&＃61;null;Connection conn&＃61;ConnectionFactory.getConnection();String sql&＃61;"select name,price,date from product";try {list&＃61;qr.query(conn, sql, new BeanListHandler(Product.class));} catch (SQLException e) {throw new DaoException("根据id查找产品时出现异常",e);}finally{DbUtils.closeQuietly(conn);}return list;}&＃64;Overridepublic Product getProduct(int id) throws DaoException {Connection conn&＃61;ConnectionFactory.getConnection();String sql&＃61;"select name,price,date from product where id&＃61;?";Product pro&＃61;null;try {pro&＃61;qr.query(conn, sql, new BeanHandler(Product.class),id);} catch (SQLException e) {throw new DaoException("根据id查找产品时出现异常",e);}finally{DbUtils.closeQuietly(conn);}return pro;}&＃64;Overridepublic PageModel getProListByPage(int pageNo, int pageSize)throws DaoException {PageModel pm&＃61;new PageModel();Connection conn&＃61;ConnectionFactory.getConnection();String sqlCount&＃61;"select count(id) from product";String sql&＃61;"select id,name,price,date from product limit ?,?";try {Long count&＃61;(Long)qr.query(conn, sqlCount, new ScalarHandler());if(count>0){pm.setRecordCount(count);pm.setData(qr.query(conn, sql, new BeanListHandler(Product.class),(pageNo-1)*pageSize,pageSize));}} catch (SQLException e) {throw new DaoException("根据id查找产品时出现异常",e);}finally{DbUtils.closeQuietly(conn);}return pm;}&＃64;Overridepublic PageModel getProListByPage(String name, int pageNo,int pageSize) throws DaoException {// TODO Auto-generated method stubreturn null;}}

package testdao;import dao.DaoFactory; import dao.ProductDao; import entity.PageModel; import entity.Product;/*** Product的Dao测试类* &＃64;author sally**/ public class ProductDaoTest {public static void main(String[] args) {//testInsert();testGetListByPage();}public static void testInsert(){ProductDao dao&＃61;(ProductDao)DaoFactory.getInstance("productDao");Product prod&＃61;new Product();prod.setName("电脑");prod.setPrice(8000.00);dao.insert(prod);}public static void testGetListByPage(){ProductDao dao&＃61;(ProductDao)DaoFactory.getInstance("productDao");PageModel pm&＃61;dao.getProListByPage(2, 5);System.out.println("总记录数"&＃43;pm.getRecordCount());for(Product pro:pm.getData()){System.out.println(pro);}}}

productDao&＃61;impl.ProductDaoImpl

#MySQL Connection Parameters driver&＃61;com.mysql.jdbc.Driver url&＃61;jdbc:mysql://localhost:3306/test?useUnicode&＃61;true&characterEncoding&＃61;utf8 user&＃61;root password&＃61;root