准备工作[实体层]
此示例在Linux下演示没有问题,并且此处假设你已经安装了MySQL和Eclipse.
请使用以下代码登录你的数据库:
1 2 3 4 5 6 | mysql -u USER -p ->ENTER password:PASSWORD //->表示按下回车,USER:用户名,PASSWORD:密码. //如果是远程数据库请使用以下语句[IP表示数据库IP地址,若数据库不是默认的3306端口,请按照格式:IP:3306连接]: mysql -h IP -p ->ENTER password:PASSWORD |
接着开始创建数据库和表:
1 2 3 4 5 6 7 8 9 | CREATE DATABASE Test; CREATE TABLE Student ( NUM int (4) primary key not null auto_increment, NAME varchar(20), ADDRESS varchar(200), EMAIL varchar(50) SEX varchar(2); AGE int ); |
关于插入数据:可以使用Java进行数据插入:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * 连接MySQL范本. * @author Yefeng. * @version 2013.2.19 */ public class MySQLDemo { private static final String DRIVE ="com.mysql.jdbc.Driver";//设置驱动 private static final String URL = "jdbc:mysql://localhost/";//设置连接 private static final String USER = "root";//设置用户名 private static final String PASSWORD ="password";//设置密码 public static void main (String []args) throws Exception{ //仅在测试时才将异常信息交给main()方法处理. Class.forName(DRIVE);//加载驱动 Connection conn = null; int result = null; try{ conn=DriverManager.getConnection(URL,USER,PASSWORD);//获得连接. for (int i=0;i<100;i++){ StringBuffer sql = new StringBuffer(); sql.append(" INSERT INTO Student "); sql.append(" (NAME,ADDRESS,EMAIL,SEX,AGE) "); sql.append(" VALUES ( "); sql.append(" test , "); sql.append(" dslakfjdslakfjlksda , "); sql.append(" fdsafdas@000.com ,"); sql.append(" NV ,"); sql.append(i); sql.append(" ); "); result=conn.createStatement().executeUpdate("");// Receive the } System.out.println(result); }catch(SQLException e){ e.printStackTrace(); }finally{ conn.close();//关闭连接. } } } |
逻辑处理部分
然后,就按照下面的步骤来了:
1.负责存放属性的Bean.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | package bean; /** * 7.4.5 * * @author puruidong * @version 2013.7.3 */ public class Stu745 { private String name; public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } private int age; private String address; private String email; private String sex; } |
2.连接数据库的操作Bean.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | package bean; import java.sql.Connection; import java.sql.DriverManager; /** * 7.4.5 * * @author puruidong * @version 2013.7.3 */ public class GetConn745 { private static final String DRIVER = "com.mysql.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost:3306/Test"; private static final String USER = "USERID"; private static final String PASSWORD = "PASSWORD"; /** * 获取连接. * * @return */ public static Connection getConn() { Connection conn = null; try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL, USER, PASSWORD); } catch (Exception e) { e.printStackTrace(); conn = null; } return conn; } } |
3.处理数据的Bean.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | package bean; import java.util.List; import java.util.ArrayList; import java.sql.SQLException; import java.sql.Statement; import java.sql.Connection; import java.sql.ResultSet; /** * 在该类中定义根据当前分页页数和每页显示的记录数定义分页方法 * * @author puruidong * @version 2013.7.4 */ public class PaginationUtil745 { /** * 获取查询结果 * * @param page * @param pagesize * @return */ public List<Stu745> findGrade(int page, int pagesize) { String strSql = "select * from Student limit " + (page - 1) * pagesize + "," + pagesize + ""; // 定义SQL查询语句 Statement pstmt = null; ResultSet rs = null; List<Stu745> lstList = new ArrayList<Stu745>(); try { if (GetConn745.getConn() != null) { pstmt = (GetConn745.getConn()).createStatement(); rs = pstmt.executeQuery(strSql); while (rs.next()) { Stu745 orderBean = new Stu745(); orderBean.setName(rs.getString("NAME")); orderBean.setAddress(rs.getString("ADDRESS")); orderBean.setAge(rs.getInt("AGE")); orderBean.setEmail(rs.getString("EMAIL")); orderBean.setSex(rs.getString("SEX")); lstList.add(orderBean); } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (pstmt != null) { rs.close(); pstmt.close(); } } catch (Exception e) { e.printStackTrace(); } } return lstList; } /** * 总行数字 * * @param row * @return */ public int allPage(int row) { int allp = 0; try { if ((GetConn745.getConn()) != null) { Statement pstmt = (GetConn745.getConn()).createStatement(); pstmt.execute("select count(*) from Student "); ResultSet rs = pstmt.getResultSet(); rs.next(); int all = rs.getInt(1); allp = (all - 1) / row + 1; } } catch (SQLException e) { e.printStackTrace(); } return allp; } } |
视图部分
一个JSP页面搞定.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <?xml version="1.0" encoding="UTF-8" ?> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="bean.PaginationUtil745,java.util.List,bean.Stu745"%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>实现数据分页</title> </head> <body> <% PaginationUtil745 pageUtil = new PaginationUtil745(); int pageNo = 0; if (request.getParameter("No") == null) { pageNo = 1; //当前页面为第一页 } else { pageNo = Integer.parseInt(request.getParameter("No")); } List<Stu745> list = pageUtil.findGrade(pageNo, 5);//获取分页数据 %> <table align="center" border="1"> <tr> <td>NAME</td> <td>ADDRESS</td> <td>AGE</td> <td>EMAIL</td> <td>SEX</td> </tr> <% for (int i = 0; i < list.size(); i++) { Stu745 stu = list.get(i); %> <tr> <td><div align="center"><%=stu.getName()%></div></td> <td><div align="center"><%=stu.getAddress()%></div></td> <td><div align="center"><%=stu.getAge()%></div></td> <td><div align="center"><%=stu.getEmail()%></div></td> <td><div align="center"><%=stu.getSex()%></div></td> <% } %> <% int all = pageUtil.allPage(3); //获取总页码 %> <td colspan="2"> 共<%=all%>页,当前位于第<%=pageNo%>页 <% if (pageNo > 1) { //如果当前页码大于1 %> <a href="7.4.5.index.jsp?No=<%=pageNo - 1%>">上一页</a> </td> <% } %> <td colspan="3"> <% if (pageNo < all) { %> <a href="7.4.5.index.jsp?No=<%=pageNo + 1%>">下一页</a> </td> <% } %> </tr> </table> </body> </html> |