准备工作[实体层]
此示例在Linux下演示没有问题,并且此处假设你已经安装了MySQL和Eclipse.
请使用以下代码登录你的数据库:
mysql -u USER -p ->ENTER
password:PASSWORD
//->表示按下回车,USER:用户名,PASSWORD:密码.
//如果是远程数据库请使用以下语句[IP表示数据库IP地址,若数据库不是默认的3306端口,请按照格式:IP:3306连接]:
mysql -h IP -p ->ENTER
password:PASSWORD
接着开始创建数据库和表:
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进行数据插入:
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.
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.
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.
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
String strSql = "select * from Student limit " + (page - 1)
* pagesize + "," + pagesize + "";
// 定义SQL查询语句
Statement pstmt = null;
ResultSet rs = null;
List
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页面搞定.
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="bean.PaginationUtil745,java.util.List,bean.Stu745"%>
<%
PaginationUtil745 pageUtil = new PaginationUtil745();
int pageNo = 0;
if (request.getParameter("No") == null) {
pageNo = 1; //当前页面为第一页
} else {
pageNo = Integer.parseInt(request.getParameter("No"));
}
List
%>
NAME | ADDRESS | AGE | SEX | ||||||
<%=stu.getName()%> | <%=stu.getAddress()%> | <%=stu.getAge()%> | <%=stu.getEmail()%> | <%=stu.getSex()%> | 共<%=all%>页,当前位于第<%=pageNo%>页 <% if (pageNo > 1) { //如果当前页码大于1 %> 上一页 | <% if (pageNo < all) { %> 下一页 |