package com.zking.mvcPlus.dao;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.zking.mvcPlus.util.DBHelper;
import com.zking.mvcPlus.util.PageBean;
public class BaseDao<T> {
/**
* 匿名内部接口
* @author zjjt
*
* @param <k>
*/
public static interface CallBack<K>{
//循环遍历ResultSet结果集
public List<K> forEachRs(ResultSet rs) throws SQLException;
}
/**
* 增删改通用
* @param sql
* @param params
*/
public static void executeUpdate(String sql,Object[] params) {
Connection con=null;
PreparedStatement ps=null;
try {
//获取连接
con=DBHelper.getConnection();
//创建执行对象
ps=con.prepareStatement(sql);
//获取增删改语句的执行参数
ParameterMetaData matadata = ps.getParameterMetaData();
//循环赋值
for (int i = 0; i < matadata.getParameterCount(); i++) {
ps.setObject(i+1,params[i]);
}
//执行sql
int i = ps.executeUpdate();
System.out.println(i);
if(i<1)
throw new RuntimeException("执行失败，影响行数为0！！");
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(con, ps, null);
}
}
/**
* 通用查询方法（支持分页）
* @param <T>
* @param sql
* @param pagebean
* @return
*/
public List<T> executeQuery(String sql,PageBean pagebean,CallBack<T> callBack){
//思路
//分页
//1)根据满足条件查询总记录数，例如：select count(0) from t_xxx
//2)根据满足条件查询分页结果集
//不分页
//1)根据满足条件查询结果集
PreparedStatement ps=null;
ResultSet rs=null;
Connection con=null;
try {
//1.获取Connection连接
con=DBHelper.getConnection();
//2.通过PageBean判断是否分页
if (null!=pagebean&&pagebean.isPagination()) {
//3)根据满足条件查询总记录数
String countSQL = this.getCountSQL(sql);
ps=con.prepareStatement(countSQL);
rs=ps.executeQuery();
if(rs.next()) {
//获取总记录数并将其赋值到pageBean对象的total属性中
int total=rs.getInt(1);
pagebean.setTotal(total);
}
//4)根据满足条件查询结果集
sql=this.getPagerSQL(sql, pagebean);
}
//不管是分页，还是不分页都是需要查询遍历结果集ResultSet
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
return callBack.forEachRs(rs);
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.close(con, ps, rs);
}
return null;
}
/**
* 将普通的sql语句转换成查询总记录数的sql语句
* 例如
* select * from t_book
* select * from t_book where ....
* select book_name from t_book
* ----------->
* select count(0) from t_book
* select count(0) from t_book where....
* @param sql
* @return
*/
public String getCountSQL(String sql) {
return "select count(0) from("+sql+") temp";
}
/**
* 将普通的SQL语句转换成查询分页结果集的SQL语句
* mysql数据库分页关键字limit 参数1 参数2
* 参数1：从第几条数据开始返回
* 参数2：每次返回多少条数据rows
* 例如
* select * from t_book
* select * from t_book where ....
* select book_name from t_book
* ----------->
*
* @param sql
* @param pagebean
* @return
*/
private String getPagerSQL(String sql,PageBean pagebean) {
return sql+" limit "+pagebean.getStartIndex()+","+pagebean.getRows();
}
}
package com.zking.mvcPlus.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.zking.mvcPlus.entity.Book;
import com.zking.mvcPlus.util.CommonUtils;
import com.zking.mvcPlus.util.PageBean;
import com.zking.mvcPlus.util.PinYinUtil;
import com.zking.mvcPlus.util.StringUtils;
public class BookDao extends BaseDao<Book> {
//1.书本新增
public void addBook(Book book) {
String sql="insert into t_book(book_name,book_name_pinyin,book_price,book_type) values(?,?,?,?)";
super.executeUpdate(sql, new Object[] {
book.getBook_name(),
PinYinUtil.toPinyin(book.getBook_name()).toLowerCase(),
book.getBook_price(),
book.getBook_type()
});
}
//2.书本删除
public void delBook(Book book) {
String sql="delete from t_book where book_id=?";
super.executeUpdate(sql, new Object[] {
book.getBook_id()
});
}
//3.书本编辑
public void editBook(Book book) {
String sql="update t_book set book_name=?,book_price=?,book_type=? where book_id=? ";
super.executeUpdate(sql, new Object[] {
book.getBook_name(),
book.getBook_price(),
book.getBook_type(),
book.getBook_id()
});
}
//4.书本分页查询
public List<Book> query(Book book,PageBean pagebean){
String sql="select book_id,book_name,book_name_pinyin,book_price,book_type from t_book where 1=1";
System.out.println(sql);
//按照书本名称模糊查询
if(StringUtils.isNotBlank(book.getBook_name()))
sql+=" and book_name like '%"+book.getBook_name()+"%'";
//按照书本编号降序排序
sql+=" order by book_id desc";
return super.executeQuery(sql, pagebean, new CallBack<Book>() {
@Override
public List<Book> forEachRs(ResultSet rs) throws SQLException {
return CommonUtils.toList(rs, Book.class);
}
});
}
//5.根据书本ID查询单个书本信息
public Book querySingle(Book book){
String sql="select book_id,book_name,book_name_pinyin,book_price,book_type from t_book where book_id="+book.getBook_id();
System.out.println(sql);
List<Book> lst = super.executeQuery(sql, null, new CallBack<Book>() {
@Override
public List<Book> forEachRs(ResultSet rs) throws SQLException {
return CommonUtils.toList(rs, Book.class);
}
});
if(null!=lst&&lst.size()!=0)
return lst.get(0);
else
return null;
}
}
package com.zking.mvcPlus.action;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.zking.mvc.framework.DispatcherAction;
import com.zking.mvc.framework.DriverModel;
import com.zking.mvcPlus.dao.BookDao;
import com.zking.mvcPlus.entity.Book;
import com.zking.mvcPlus.util.PageBean;
public class BookAction extends DispatcherAction implements DriverModel<Book>{
private Book book=new Book();
private BookDao bookdao=new BookDao();
@Override
public Book getModel() {
return book;
}
/**
* 书本新增
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String addBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
bookdao.addBook(book);
return "list";
}
/**
* 书本修改
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String editBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
bookdao.editBook(book);
return "list";
}
/**
* 书本删除
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String delBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
bookdao.delBook(book);
return "list";
}
/**
* 书本分页
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String queryBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
PageBean pagebean=new PageBean();
pagebean.setRequest(req);
//查询结果集
List<Book> books = bookdao.query(book, pagebean);
//将查询结果集和pagebean对象保存到request对象中
req.setAttribute("books",books);
req.setAttribute("pagebean",pagebean);
return "success";
}
/**
* 查询单个书本信息，并跳转页面
* @param req
* @param resp
* @return
* @throws ServletException
* @throws IOException
*/
public String querySingleBook(HttpServletRequest req,HttpServletResponse resp) throws ServletException,IOException{
Book b = bookdao.querySingle(book);
//将查询出来的书本对象存入request中
req.setAttribute("book",b);
//根据type属性判断跳转页面
String type=req.getParameter("type");
if("edit".equals(type))
return "edit";
else
return "detail";
}
}
package com.zking.mvcPlus.util;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class CommonUtils {
/**
* 反射封装ResultSet结果集，并转换成List<T>（通用）
* @param rs 结果集
* @param cla 类对象
* @return
* @throws SQLException
*/
public static <T> List<T> toList(ResultSet rs,Class cla) {
//定义返回集合
List<T> lst=new ArrayList<T>();
try {
//获取ResultSet的列信息MetaData
ResultSetMetaData metaData = rs.getMetaData();
//获取对象的所有属性数组
Field[] fields = cla.getDeclaredFields();
//定义返回对象
T obj=null;
//循环遍历结果集
while(rs.next()) {
//反射机制实例化对象
obj=(T) cla.newInstance();
//遍历列信息(获取rs结果集中的所有列的数量)
for (int i = 0; i < metaData.getColumnCount(); i++) {
//获取列名
String colName = metaData.getColumnLabel(i+1);
//循环遍历Field属性数组
for (Field field : fields) {
//获取属性名
String fieldName=field.getName();
//将列名与属性名进行对比，注意：请转换成大写对比
if(fieldName.toUpperCase().equals(colName.toUpperCase())) {
//拼接set方法
//set+属性名的首字母大写 setId setName setXxxx
//set+I+d
//set+N+ame
//...
String methodName="set"+fieldName.substring(0,1).toUpperCase()+
fieldName.substring(1);
//反射调用方法
//field.getType() java.lang.String
Method method=cla.getDeclaredMethod(methodName, field.getType());
//设置访问权限
method.setAccessible(true);
//执行方法(1:实例化对象,2:被设置的值)
method.invoke(obj, rs.getObject(colName));
break;
}
}
}
lst.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return lst;
}
}
2527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
