数据库操作
使用JDBC连接数据库
(JDBC是Java操作数据库的技术规范。他实际上定义了一组标准的操作数据库的接口。为了能让Java操作数据库,必须要有实现了JDBC这些接口的类,不同的数据库厂商为了让Java语言能操作自己的数据库,都提供了对JDBC接口的实现--这些实现了JDBC接口的类打成一个jar包,就是我们平时看到的数据库驱动。)
public class DaoJDBCImpl implements Dao {
private Connection con ;
DaoJDBCImpl dbc;
String dbUrl = "jdbc:mysql://127.0.0.1:3306/persons";//后期修改为通过配置文件参数设置,便于管理
String user = "root";
String passwordmysql = "password";
public DaoJDBCImpl() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
this.con = DriverManager.getConnection(dbUrl, user, passwordmysql);
}
public Connection getConnection(){
return this.con ;
}
public void close() throws Exception {
if(this.con != null){
try{
this.con.close() ;
}catch(Exception e){
throw e ;
}
}
}
//统计人员数量
public int count() throws Exception {
int count=0;
dbc = new DaoJDBCImpl();
Statement st = dbc.getConnection().createStatement();
ResultSet rs=null;
rs = st.executeQuery("select count(*) as cnt from person");
while (rs.next()) {
count=rs.getInt(1);
}
rs.close();
st.close();
dbc.close();
return count;
}
//分页显示
public List<Person> topFind1(int pageSize,int pageNow) throws Exception {
List<Person> list = new ArrayList<Person>();
dbc = new DaoJDBCImpl();
Statement st = dbc.getConnection().createStatement();
ResultSet rs = st.executeQuery("select * from person where role='0' limit "+pageSize*(pageNow-1)+","+pageSize);
while (rs.next()) {
Person person = new Person(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11));
list.add(person);
}
rs.close();
st.close();
dbc.close();
return list;
}
控制器Servlet类:public void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//System.out.println("-----");
String url="index.jsp";
response.setContentType("text/html");
response.setCharacterEncoding("utf-8");
int pageSize=10;
int pageCount=0;//该值是通过pageSize和rowCount
int pageNow=1;//默认显示第一页
int rowCount=0;//该值从数据库中查询
String s_pageNow=request.getParameter("pageNow");
if(s_pageNow!=null){
//接收到了pageNow
pageNow=Integer.parseInt(s_pageNow);
}
try {
Dao dao = new DaoJDBCImpl();//获得dao对象
//int cou =dao.count();//查询所有数据
if(session.getAttribute("role").equals("user")){
rowCount =dao.count();
if(rowCount%pageSize==0){
pageCount=rowCount/pageSize;
}else{
pageCount=rowCount/pageSize+1;
}
List <Person> person1 =dao.topFind1(pageSize,pageNow);
request.setAttribute("jsp", person1);
request.setAttribute("pageNow", pageNow);
request.setAttribute("pageCount", pageCount);
}else{
rowCount =dao.count();
if(rowCount%pageSize==0){
pageCount=rowCount/pageSize;
}else{
pageCount=rowCount/pageSize+1;
}
<span style="white-space:pre"> </span>request.setAttribute("pageNow", pageNow);
<span style="white-space:pre"> </span>request.setAttribute("pageCount", pageCount);
显示JSP页面:<%
int pageCount = ( (Integer)request.getAttribute("pageCount")).intValue();
int pageNow = ( (Integer)request.getAttribute("pageNow")).intValue();
%>
第<%=pageNow %>页(共<%=pageCount %>页)
<br>
<a href="ShowServlet?pageNow=1">首页</a>
<%if(pageNow > 1){%>
<a href="ShowServlet?pageNow=<%=pageNow-1%>">上一页</a>
<%}%>
<% //根据pageCount的值显示每一页的数字并附加上相应的超链接
for(int i=1;i<=pageCount;i++){
%>
<a href="ShowServlet?pageNow=<%=i%>"><%=i%></a>
<% }
%>
<%if(pageNow < pageCount){%>
<a href="ShowServlet?pageNow=<%=pageNow+1%>">下一页</a>
<%}%>
<a href="ShowServlet?pageNow=<%=pageCount%>">末页</a>
<!-- 通过表单提交用户想要显示的页数 -->
<form action="" method="get">
跳转到第<select name="pageNow" style="overflow:hidden" >
<%
for(int i=1;i<=pageCount;i++){
%>
<option ><%=i %></option>
<% }%>
</select>页
<input type="submit" name="submit" value="跳转" >
</form>
效果图展示: