思路
基于MySql数据库的通用分页 limit
通用分页核心思路:将上一次查询请求再发一次,只不过页码变了
实现步骤:
1)先查询全部数据
Junit测试
baseDao<T>、CallBack<K>
2)通用分页实现
pagebean
1. PageBean
分页三要素
page=1 页码 视图层传递过来
rows=10 页大小 视图层传递过来
total=0 总记录数 后台查出来pagination=true 是否分页 视图层传递过来
getStartIndex() 基于MySql数据库分页,获取分页开始标记
(page-1)*rows
2. 后台
2.1 entity
2.2 dao
BaseDao<T>
1)匿名内部接口
public static interface CallBack<E>{
//只做一件事情,遍历ResultSet结果集,返回List<E>
public List<E> forEach(ResultSet rs) throws SQLException;
}
2)分页查询方法,接口方法传参
List<T> executeQuery(String sql,PageBean pageBean,CallBack<T> callBack)
if(如果分页)
(返回:总记录数+指定页码并满足条件的记录集)
else
直接查询sql语句return callBack.forEach(rs);
3)二次查询的条件要一致
getCountSql()/getPagerSql()ResultSet rs=super.executeQuery(sql,pageBean);
CommonUtils.toList(rs,Book.class);
3. junit
java单元测试/白盒测试
setUp
tearDown
测试用例
Servlet中的init和destory方法只会运行一次
Junit中的setUp和tearDown方法是根据方法数量来决定的
例题:
Book.java
package com.aike.entity; import java.io.Serializable; public class Book implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private Integer bid; private String bname; private double price; public Integer getBid() { return bid; } public void setBid(Integer bid) { this.bid = bid; } public String getBname() { return bname; } public void setBname(String bname) { this.bname = bname; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public Book() { // TODO Auto-generated constructor stub } public Book(Integer bid, String bname, double price) { this.bid = bid; this.bname = bname; this.price = price; } @Override public String toString() { return "Book [bid=" + bid + ", bname=" + bname + ", price=" + price + "]"; } }
DBHelper.java
package com.aike.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; public class DBHelper { private static final String url="jdbc:sqlserver://localhost:1433;DatabaseName=Zy"; private static final String cname="com.microsoft.sqlserver.jdbc.SQLServerDriver"; static { try { Class.forName(cname); } catch (Exception e) { e.printStackTrace(); } } public static Connection getsCon() { Connection con = null; try { con = DriverManager.getConnection(url,"sa","zkingedu"); } catch (Exception e) { e.printStackTrace(); } return con; } public static void Close(Connection con,PreparedStatement ps,ResultSet rs) { try { if(rs!=null) { rs.close(); } if(ps!=null) { ps.close(); } if(con!=null&&!con.isClosed()) { con.close(); } } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { System.out.println(getsCon()); } }
StringUtils.java
package com.aike.util; public class StringUtils { // 私有的构造方法,保护此类不能在外部实例化 private StringUtils() { } /** * 如果字符串等于null或去空格后等于"",则返回true,否则返回false * * @param s * @return */ public static boolean isBlank(String s) { boolean b = false; if (null == s || s.trim().equals("")) { b = true; } return b; } /** * 如果字符串不等于null或去空格后不等于"",则返回true,否则返回false * * @param s * @return */ public static boolean isNotBlank(String s) { return !isBlank(s); } }
BookDao.java
package com.aike.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.aike.entity.Book; import com.aike.util.DBHelper; import com.aike.util.StringUtils; public class BookDao { private Connection con = null; private PreparedStatement ps = null; private ResultSet rs = null; /** * 增加书本 * @param b */ public void addBook(Book b) { try { //获得连接 con=DBHelper.getsCon(); //定义sql语句 String sql="INSERT INTO BOOK(bname,price) VALUES(?,?)"; //获得执行对象 ps=con.prepareStatement(sql); //给占位符赋值 ps.setString(1, b.getBname()); ps.setDouble(2, b.getPrice()); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } } /** * 删除书本 * @param b */ public void delBook(Book b) { try { //获得连接 con=DBHelper.getsCon(); //定义sql语句 String sql="DELETE FROM BOOK WHERE bid=? "; //获得执行对象 ps=con.prepareStatement(sql); //给占位符赋值 ps.setInt(1, b.getBid()); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } } /** * 修改书本 * @param b */ public void updateBook(Book b) { try { //获得连接 con=DBHelper.getsCon(); //定义sql语句 String sql="UPDATE BOOK SET bname=?,price=? WHERE bid=? "; //获得执行对象 ps=con.prepareStatement(sql); //给占位符赋值 ps.setString(1, b.getBname()); ps.setDouble(2, b.getPrice()); ps.setInt(3, b.getBid()); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } } /** * 单个查询 * @param b */ public Book loadBook(Book b) { try { //获得连接 con=DBHelper.getsCon(); //定义sql语句 String sql="SELECT bid,bname,price FROM BOOK WHERE bid=? "; //获得执行对象 ps=con.prepareStatement(sql); //给占位符赋值 ps.setString(1, b.getBname()); ps.setDouble(2, b.getPrice()); ps.setInt(3, b.getBid()); rs=ps.executeQuery(); Book bb=null; if(rs.next()) { bb=new Book(); bb.setBid(rs.getInt("bid")); bb.setBname(rs.getString("bname")); bb.setPrice(rs.getDouble("price")); } return bb; } catch (Exception e) { throw new RuntimeException(e); } } /** * 查询所有 * @param b */ public List<Book> listBook(Book b) { try { //获得连接 con=DBHelper.getsCon(); //定义sql语句 String sql="SELECT bid,bname,price FROM BOOK WHERE 1=1 "; //模糊查询 if(StringUtils.isNotBlank(b.getBname())) { sql+=" AND bname LIKE '%"+b.getBname()+"%'"; } //获得执行对象 ps=con.prepareStatement(sql); //给占位符赋值 ps.setString(1, b.getBname()); ps.setDouble(2, b.getPrice()); ps.setInt(3, b.getBid()); rs=ps.executeQuery(); Book bb=null; List<Book> ls=new ArrayList<Book>(); while(rs.next()) { bb=new Book(); bb.setBid(rs.getInt("bid")); bb.setBname(rs.getString("bname")); bb.setPrice(rs.getDouble("price")); ls.add(bb); } return ls; } catch (Exception e) { throw new RuntimeException(e); } } }
BookDaoText.java
增加
package com.aike.dao; import static org.junit.Assert.*; import org.junit.After; import org.junit.Before; import org.junit.Test; import com.aike.entity.Book; public class BookDaoTest { private BookDao bookdao; private Book b; @Before public void setUp() throws Exception { bookdao=new BookDao(); b=new Book(); } @After public void tearDown() throws Exception { } @Test public void testAddBook() { for (int i = 1; i < 101; i++) { b.setBname("菜菜三打果果第"+i+"回"); b.setPrice(66f); bookdao.addBook(b); } } }
效果图:
删除
package com.aike.dao;
import static org.junit.Assert.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.aike.entity.Book;
public class BookDaoTest {
private BookDao bookdao;
private Book b;
@Before
public void setUp() throws Exception {
bookdao=new BookDao();
b=new Book();
}
@After
public void tearDown() throws Exception {
}
@Test
public void testDelBook() {
b.setBid(1);
bookdao.delBook(b);
}
}
效果图:删除bid为1的数据
修改
package com.aike.dao;
import static org.junit.Assert.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.aike.entity.Book;
public class BookDaoTest {
private BookDao bookdao;
private Book b;
@Before
public void setUp() throws Exception {
bookdao=new BookDao();
b=new Book();
}
@After
public void tearDown() throws Exception {
}
@Test
public void testUpdateBook() {
b.setBid(2);
b.setBname("菜菜喜欢果果");
b.setPrice(520f);
bookdao.updateBook(b);
}
}
效果图:修改bid为2的数据
查询单个
package com.aike.dao;
import static org.junit.Assert.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.aike.entity.Book;
public class BookDaoTest {
private BookDao bookdao;
private Book b;
@Before
public void setUp() throws Exception {
bookdao=new BookDao();
b=new Book();
}
@After
public void tearDown() throws Exception {
}
@Test
public void testLoadBook() {
b.setBid(2);
Book loadBook = bookdao.loadBook(b);
System.out.println(loadBook);
}
}
效果图:
查询全部
package com.aike.dao;
import static org.junit.Assert.*;
import java.util.List;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.aike.entity.Book;
public class BookDaoTest {
private BookDao bookdao;
private Book b;
@Before
public void setUp() throws Exception {
bookdao=new BookDao();
b=new Book();
}
@After
public void tearDown() throws Exception {
}
@Test
public void testListBook() {
List<Book> listBook = bookdao.listBook(b);
for (Book book : listBook) {
System.out.println(book);
}
}
}
效果图:
模糊查询
package com.aike.dao;
import static org.junit.Assert.*;
import java.util.List;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.aike.entity.Book;
public class BookDaoTest {
private BookDao bookdao;
private Book b;
@Before
public void setUp() throws Exception {
bookdao=new BookDao();
b=new Book();
}
@After
public void tearDown() throws Exception {
}
@Test
public void testListBook() {
b.setBname("1");
List<Book> listBook = bookdao.listBook(b);
for (Book book : listBook) {
System.out.println(book);
}
}
}
效果图:bid带1的数据