package common;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* 数据库连接工厂
* @author sally
*
*/
public class ConnectionFactory {
private static Properties prop=new Properties();
static{
try {
prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
} catch (IOException e) {
e.printStackTrace();
System.out.println("在classpath下没有找到jdbc.properties文件");
}
}
/**
* 根据数据库的默认连接参数获取数据库的Connection对象
* @return 成功,返回Connection对象,否则返回null
*/
public static Connection getConnection(){
Connection conn=null;
try {
Class.forName(prop.getProperty("driver"));
conn=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 = -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=new Properties();
static{
try {
prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("dao.properties"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("在classpath下没有找到Dao工厂的配置文件dao.properties");
}
}
private DaoFactory() {}
/**
* 从dao.properties获取对象
* @param daoName dao的标识名
* @return 对应的实现类的实例
*/
public static Object getInstance(String daoName){
Object obj=null;
String className=prop.getProperty(daoName);
try {
obj=Class.forName(className).newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return obj;
}
}
package dao;
import java.util.List;
import entity.PageModel;
import entity.Product;
public interface ProductDao {
/**
* 新增产品
* @param pro 产品实例
* @throws DaoException 如果操作失败,会抛出DaoException这个非受检异常
* */
public void insert(Product pro) throws DaoException;
/** 更新产品信息*/
public void update(Product pro) throws DaoException;
/** 根据ID删除产品*/
public void delete(int id) throws DaoException;
/** 查询所有产品,以List方式返回
* @return 符合条件的产品实例的列表
* @throws DaoException 如果操作失败,会抛出DaoException这个非受检的异常
* */
public List<Product> getProductList() throws DaoException;
/** 根据ID查询产品
* @param id 产品id
* @return 符合条件的产品实例,如果不存在,返回null
* @throws DaoException 如果操作失败,会抛出DaoException这个非受检异常
* */
public Product getProduct(int id) throws DaoException;
/** 分页查询数据
* @param pageSize是每页显示的记录数
* @param pageNo要查询的页号
* @return 符合条件的分布数据模型PageModel实例
* @throws DaoException
* */
public PageModel<Product> getProListByPage(int pageNo,int pageSize) throws DaoException;
/** 根据姓名模糊查询员工,以分页数据方式返回*/
public PageModel<Product> getProListByPage(String name,int pageNo,int pageSize) throws DaoException;
}
package entity;
import java.io.Serializable;
import java.util.List;
public class PageModel<T> implements Serializable{
/**
*
*/
private static final long serialVersionUID = 8650004680285850659L;
private Long recordCount;
private List<T> data;
public Long getRecordCount() {
return recordCount;
}
public void setRecordCount(Long recordCount) {
this.recordCount = recordCount;
}
public List<T> getData() {
return data;
}
public void setData(List<T> data) {
this.data = 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 = id;
this.name = name;
this.price = price;
this.date = date;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
@Override
public String toString() {
DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str=this.date==null?"":df.format(date);
return "Product [id=" + id + ", name=" + name + ", price=" + price
+ ", date=" + str + "]";
}
}
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操作实现类
* @author sally
*
*/
public class ProductDaoImpl implements ProductDao {
private QueryRunner qr=new QueryRunner();//commons-dbutils包带的:SQL的执行器,线程安全的
@Override
public void insert(Product pro) throws DaoException {
Connection conn=ConnectionFactory.getConnection();
String sql="insert into product(name,price,date)values(?,?,now())";
Object[] params={pro.getName(),pro.getPrice()};
try {
qr.update(conn, sql, params);
} catch (SQLException e) {
throw new DaoException("新增产品时出现异常",e);
}finally{
DbUtils.closeQuietly(conn);
}
}
@Override
public void update(Product pro) throws DaoException {
Connection conn=ConnectionFactory.getConnection();
String sql="update product set name=?,price=?,date=? where id=?";
Object[] params={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);
}
}
@Override
public void delete(int id) throws DaoException {
Connection conn=ConnectionFactory.getConnection();
String sql="delete from product where id=?";
try {
qr.update(conn, sql, id);
} catch (SQLException e) {
throw new DaoException("产品删除时出现异常",e);
}finally{
DbUtils.closeQuietly(conn);
}
}
@Override
public List<Product> getProductList() throws DaoException {
List<Product> list=null;
Connection conn=ConnectionFactory.getConnection();
String sql="select name,price,date from product";
try {
list=qr.query(conn, sql, new BeanListHandler<Product>(Product.class));
} catch (SQLException e) {
throw new DaoException("根据id查找产品时出现异常",e);
}finally{
DbUtils.closeQuietly(conn);
}
return list;
}
@Override
public Product getProduct(int id) throws DaoException {
Connection conn=ConnectionFactory.getConnection();
String sql="select name,price,date from product where id=?";
Product pro=null;
try {
pro=qr.query(conn, sql, new BeanHandler<Product>(Product.class),id);
} catch (SQLException e) {
throw new DaoException("根据id查找产品时出现异常",e);
}finally{
DbUtils.closeQuietly(conn);
}
return pro;
}
@Override
public PageModel<Product> getProListByPage(int pageNo, int pageSize)
throws DaoException {
PageModel<Product> pm=new PageModel<Product>();
Connection conn=ConnectionFactory.getConnection();
String sqlCount="select count(id) from product";
String sql="select id,name,price,date from product limit ?,?";
try {
Long count=(Long)qr.query(conn, sqlCount, new ScalarHandler());
if(count>0){
pm.setRecordCount(count);
pm.setData(qr.query(conn, sql, new BeanListHandler<Product>(Product.class),(pageNo-1)*pageSize,pageSize));
}
} catch (SQLException e) {
throw new DaoException("根据id查找产品时出现异常",e);
}finally{
DbUtils.closeQuietly(conn);
}
return pm;
}
@Override
public PageModel<Product> getProListByPage(String name, int pageNo,
int pageSize) throws DaoException {
// TODO Auto-generated method stub
return null;
}
}
package testdao;
import dao.DaoFactory;
import dao.ProductDao;
import entity.PageModel;
import entity.Product;
/**
* Product的Dao测试类
* @author sally
*
*/
public class ProductDaoTest {
public static void main(String[] args) {
//testInsert();
testGetListByPage();
}
public static void testInsert(){
ProductDao dao=(ProductDao)DaoFactory.getInstance("productDao");
Product prod=new Product();
prod.setName("电脑");
prod.setPrice(8000.00);
dao.insert(prod);
}
public static void testGetListByPage(){
ProductDao dao=(ProductDao)DaoFactory.getInstance("productDao");
PageModel<Product> pm=dao.getProListByPage(2, 5);
System.out.println("总记录数"+pm.getRecordCount());
for(Product pro:pm.getData()){
System.out.println(pro);
}
}
}
productDao=impl.ProductDaoImpl
#MySQL Connection Parameters
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8
user=root
password=root