创建的类以及方法使用
BaseDao<T>{
//回调函数(内部类r)
public interface ClassBack<T>{
public List<T> forecah(ResultSet rs);
}
写一个公共的方法,这个方法专门负责增删改
public int executeUpdate(String sql){
int n =0;
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
con=DBAccess.getConnection();
ps=con.prepareStatement(sql);
n=ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBAccess.close(con, ps, rs);
}
return n;}
查询全部的公共方法
public List<T> executeQuery(String sql,PageBean pageBean,CallBack<T> callback ){
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
con=DBAccess.getConnection();//判断用户是否需要分页
if(null !=pageBean&&pageBean.isPagination()){
//获取满足用户的总条目数,将sql语句进行更换
String countHql=this.countHql(sql);
ps=con.prepareStatement(countHql);//拿到总条目数
rs=ps.executeQuery();
if(rs.next()){
pageBean.setTotal(rs.getInt(1));//设置PageBean的总条目数
String pageSql=this.pageHql(sql,pageBean);//将用户传递过来的sql语句进行修改,修改为可分页的语句
ps=con.prepareStatement(pageSql);//重新放入
rs=ps.executeQuery();
}
}else{//当不需要分页的时候
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
}
return callback.foreach(rs);//返回的时候必须要去调用foreach
}
将用户传递过来的sql语句进行添加修改和转为可分页的语句
public String pageHql(String sql,PageBean pageBean){
String pageHql=sql+" limit "+pageBean.startIndex()+" ,"+pageBean+getRows();// limit(从多少个开始查,查多少个);
return pageHql;
}
查询满足用户条件的sql语句总条目数
public String countHql(String sql){
String countHql=" select count(*) from ("+sql+") a";return countHql;
}
}
StudentDao extends BaseDao<Student>{
专门放查询语句的
例:新增
public int add(Student student){
String sql="insert into t_student(sname,sname_pinyin,age,remark) values('"+student.getSname()+"','"+student.getSnamePinyin()+"',"+student.getAge()+",'"+student.getRemark()+"')";
return this.executeUpdate(sql);
}
修改语句
String sql="update t_student set sname='"+student.getSname()+"',sname_pinyin='"+student.getSnamePinyin()+"',age="+student.getAge()+",remark='"+student.getRemark()+"' where sid="+student.getSid()+"";
删除语句
String sql="delete from t_student where sid="+student.getSid()+" ";
查询全部的分页功能
public List<Student> getAll(Student student,PageBean pageBean){
String sql="select * from t_student where 1=1";
if(StringUtils.isNotBlank(student.getSname())) {
sql+=" and where sname like '%"+student.getSname()+"%'";
}
else if(StringUtils.isNotBlank(student.getSnamePinyin())) {
sql+="and sname_pinyin like '%"+student.getSnamePinyin()+"%'";
}return this.executeQuery(sql,pageBean,new CallBack<Student>(){
实现方法赋值
public List<Student> foreach(ResultSet rs){
List<Student> list=new ArrayList<Student>();
Student s=null;
while(rs.next()) {
s=new Student();
s.setSid(rs.getInt("sid"));
s.setSname(rs.getString("sname"));
s.setAge(rs.getInt("age"));
s.setRemark(rs.getString("remark"));
list.add(s);
}
return list;
}});
}
StudentDaoTest{ //单元测试类 JUnit Test Case
private StudentDao stuDao=new StudentDao();
private Student s=null;
setUp(){//在单元测试最前运行
s=new Student();//实例化
}
tearDoen(){ //在单元测试最后运行
}
方法演示
查询所有
@Test
public void testList() throws Exception {
PageBean pageBean=new PageBean();
List<Student> students = stuDao.getAll(s, pageBean);
for (Student student : students) {
System.out.println(student);
}
}
}
实体类
Student{
private Integer sid;
private String sname;
private String snamePinyin;
private Integer age;
private String remark;
}
StudentServlet extends HttpServlet{
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PageBean pageBean=new PageBean();
pageBean.setRequest(req);//只要请求里面带有page就能修改
Student student=new Student();
String sname = req.getParameter("sname");
student.setSname(sname);
try {
List<Student> students = stuDao.getAll(student, pageBean);
//将结果集封装到session作用域中然后进行页面传递
req.getSession().setAttribute("stu", students);
//把pageBean也传过去标签需要用到
req.getSession().setAttribute("pageBean", pageBean);
//页面跳转
resp.sendRedirect("index.jsp");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
PaginationTag;分页标签
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
PageBean pageBean=new PageBean();
pageBean.setRequest(req);//只要请求里面带有page就能修改
Student student=new Student();
String sname = req.getParameter("sname");
student.setSname(sname);
try {
List<Student> students = stuDao.getAll(student, pageBean);
//将结果集封装到session作用域中然后进行页面传递
req.getSession().setAttribute("stu", students);
//把pageBean也传过去标签需要用到
req.getSession().setAttribute("pageBean", pageBean);
//页面跳转
resp.sendRedirect("index.jsp");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
帮助类
DBAccess{}连接数据库的方法
PageBean{}分页的方法
PinyinUtil{}汉字转缩写的方法
StringUtils{}判空帮助类