1,总体架构
index是进去的页面,
可以跳转Insert的增加页面,operatePerson是根据传进来的URI来判断增删改查的页面,
DbManager.java是封装的数据库操作类, Pagination.java是页脚的信息栏
2,index页面
final int pageSize = 5;//一页显示多少纪录
int pageNum = 1;//当前页数
int pageCount = 1;//总页数
int recordCount = 0;//总记录数
for(; resultSet.next();){
out.println("
");Stringname=resultSet.getString("name");out.println("
" +name+ "");out.println("
" +resultSet.getString("age")+ "");out.println("
" +resultSet.getString("sex")+ "");out.println("
删除");out.println("
修改");out.println("
");}%>
, recordCount, request.getRequestURI())%>
画出表,包括页脚,(上面的form其实没有什么用
分页技术使用数据库的 select * from person limit ?,? ----意思是从哪条开始(不包括这一条)的 n条记录
问号使用preparedStatement语句来实现
代码:
String basePath= request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
My JSP 'index.jsp' starting pagefinal int pageSize = 5;//一页显示多少纪录
int pageNum = 1;//当前页数
int pageCount = 1;//总页数
int recordCount = 0;try{//当前页数
pageNum = Integer.parseInt(request.getParameter("pageNum"));
System.out.println("0000:" +pageNum);
}catch(Exception e){}
String sql= null;
Connection con= null;
PreparedStatement preparedStatement= null;
ResultSet resultSet= null;try{
sql= "select count(*) from person";
recordCount=DbManager.getCount(sql);
pageCount= (recordCount + pageSize - 1) /pageSize;int startRecord = (pageNum - 1) *pageSize;
sql= "select * from person limit ?,?";
con=DbManager.getConnection();
preparedStatement=con.prepareStatement(sql);
DbManager.setParams(preparedStatement, startRecord, pageSize);
resultSet=preparedStatement.executeQuery();%>
for(; resultSet.next();){
out.println("
");String name= resultSet.getString("name");
out.println("
" + name + "");out.println("
" + resultSet.getString("age") + "");out.println("
" + resultSet.getString("sex") + "");out.println("
删除");out.println("
修改");out.println("
");}%>
, recordCount, request.getRequestURI())%>
out.println("0001" +e1.getMessage());
}finally{if (resultSet != null)
resultSet.close();if (preparedStatement != null)
preparedStatement.close();if (con != null)
con.close();
}%>
View Code
2,operatePerson.jsp页面
我写的时候没有使用DbManager类,在里面的增删改都用了原始的操作
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");//一定要
String action = request.getParameter("action");//先接受是什么操作
增:request.getPararmeter("name...")通过这个方法来获得表传进来的数据
删:request.getPararmeter("name")通过这个方法来获得uri传进来的paramer
改:request.getPararmeter("name")通过这个方法来获得uri传进来的paramer,(我设计的时候没有完善好,默认他们不能有同名,所以不能改名字
request.setAttribute("name", resultSet.getString("name")//把传进来的参数穿进去request里面,用forward转向到insert的页面在,那样insert可以从request里面提取数据(同时也把"save"信息传过去),和真正的插入有了区别
在insert会判断是save的传递,把数据传回来operate页面,做真正的修改
3,insert页面
会根据传过来的action判断是插入还是修改,以此来修改uri,form表给的提交方式要改为post ,因为这样表格的内容就不能形象uri的传递,
value="${name}"的时候,在修改的时候会显示值,注意:el表达式,${name}或者${requestScope.name}而不是${request.name}
4,Pagination.java
页脚,用StringBuffer类,append字符串
5,DbManager.java
数据库管理类
public static Connection getConnection(String dbName, String userName,String password)
public static Connection getConnection() //获得con对象
public static void setParams(PreparedStatement preparedStatement,Object... params) //把preparedStatement放进去,发若干个参数,preparedStatement.setInt.....之类的
public static int executeUpdate(String sql)
public static int executeUpdate(String sql, Object... params) //对上面方法的封装,穿进去sql的字符串就行了
源码:
DbManager
packagetestPackage;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;importjava.sql.Types;public classDbManager {public static Connection getConnection() throwsSQLException,
ClassNotFoundException {return getConnection("test", "kooing", "");
}public staticConnection getConnection(String dbName, String userName,
String password)throwsSQLException, ClassNotFoundException {
String url= "jdbc:mysql://localhost:3306/" +dbName;
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(url);returncon;
}public static voidsetParams(PreparedStatement preparedStatement,
Object... params)throwsSQLException {for (int i = 1; i <= params.length; i++) {
Object param= params[i - 1];if (param == null) {
preparedStatement.setNull(i, Types.NULL);
}else if (param instanceofInteger) {
preparedStatement.setInt(i, (Integer) param);
}else if (param instanceofString) {
preparedStatement.setString(i, (String) param);
}
}
}public static int executeUpdate(String sql) throwsSQLException,
ClassNotFoundException {return executeUpdate(sql, newObject[] {});
}public static intexecuteUpdate(String sql, Object... params)throwsSQLException, ClassNotFoundException {
Connection con= null;
PreparedStatement preparedStatement= null;try{
con=getConnection();
preparedStatement=con.prepareStatement(sql);
setParams(preparedStatement, params);returnpreparedStatement.executeUpdate();
}finally{if (preparedStatement != null)
preparedStatement.close();if (con != null)
con.close();
}
}public static int getCount(String sql) throwsSQLException,
ClassNotFoundException {
Connection con= null;
Statement statement= null;
ResultSet rs= null;try{
con=getConnection();
statement=con.createStatement();
rs=statement.executeQuery(sql);
rs.next();return rs.getInt(1);
}finally{if (rs != null)
rs.close();if (statement != null)
statement.close();if (con != null)
con.close();
}
}
}
View Code
Pagination
packagetestPackage;public classPagination {public static String getPagination(int pageNum, intpageCount,intrecordCount, String pageUrl) {
String url= pageUrl.contains("?") ? pageUrl : pageUrl + "?";if (!url.endsWith("?") && !url.endsWith("&")) {
url+= "&";
}
StringBuffer buffer= newStringBuffer();
buffer.append("第 " + pageNum + "/" + pageCount + "页,共" +recordCount+ "纪录。");
buffer.append(pageNum== 1 ? "第一页" : "第一页");//buffer.append(pageNum == 1 ? "第一页" : "第一页");
buffer.append(pageNum== 1 ? "上一页" : "上一页");
buffer.append(pageNum== pageCount ? "下一页" : "下一页");
buffer.append(pageNum== pageCount ? "最后一页" : "最后一页");returnbuffer.toString();
}
}
View Code
index
String basePath= request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
My JSP 'index.jsp' starting pagefinal int pageSize = 5;//一页显示多少纪录
int pageNum = 1;//当前页数
int pageCount = 1;//总页数
int recordCount = 0;try{//当前页数
pageNum = Integer.parseInt(request.getParameter("pageNum"));
System.out.println("0000:" +pageNum);
}catch(Exception e){}
String sql= null;
Connection con= null;
PreparedStatement preparedStatement= null;
ResultSet resultSet= null;try{
sql= "select count(*) from person";
recordCount=DbManager.getCount(sql);
pageCount= (recordCount + pageSize - 1) /pageSize;int startRecord = (pageNum - 1) *pageSize;
sql= "select * from person limit ?,?";
con=DbManager.getConnection();
preparedStatement=con.prepareStatement(sql);
DbManager.setParams(preparedStatement, startRecord, pageSize);
resultSet=preparedStatement.executeQuery();%>
for(; resultSet.next();){
out.println("
");String name= resultSet.getString("name");
out.println("
" + name + "");out.println("
" + resultSet.getString("age") + "");out.println("
" + resultSet.getString("sex") + "");out.println("
删除");out.println("
修改");out.println("
");}%>
, recordCount, request.getRequestURI())%>
out.println("0001" +e1.getMessage());
}finally{if (resultSet != null)
resultSet.close();if (preparedStatement != null)
preparedStatement.close();if (con != null)
con.close();
}%>
View Code
operate
String basePath= request.getScheme() + "://"
+ request.getServerName() + ":" +request.getServerPort()+ path + "/";%>
response.setCharacterEncoding("UTF-8");
String action= request.getParameter("action");
String sql= null;if ("add".equals(action)) {
String name= request.getParameter("name");int age = Integer.parseInt(request.getParameter("age"));
String sex= request.getParameter("sex");
sql= "insert into person values('" + name + "','" +age+ "','" + sex + "')";try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "kooing", "");
Statement sm=con.createStatement();int resultSet =sm.executeUpdate(sql);
out.println(sql);
}catch(Exception e) {
}
}if ("delete".equals(action)) {
String name= request.getParameter("name");
System.out.println(name);
sql= "delete from person where name = '" + name + "'";try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "kooing", "");
Statement sm=con.createStatement();int resultSet =sm.executeUpdate(sql);
out.println(sql);
}catch(Exception e) {
}
}if ("edit".equals(action)) {try{
String name= request.getParameter("name");
sql= "select * from person where name ='" + name + "'";
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "kooing", "");
Statement sm=con.createStatement();
ResultSet resultSet=sm.executeQuery(sql);
out.println(sql);
resultSet.next();
request.setAttribute("name", resultSet.getString("name"));
request.setAttribute("age", resultSet.getString("age"));
request.setAttribute("sex", resultSet.getString("sex"));
request.setAttribute("action", action);
request.getRequestDispatcher("Insert.jsp").forward(request,
response);
}catch(Exception e) {
}
}if("save".equals(action)){
String name= request.getParameter("name");int age = Integer.parseInt(request.getParameter("age"));
String sex= request.getParameter("sex");
sql= "update person set "
+ "name='" +name+ "',age='" +age+ "',sex='" + sex + "' where name ='" + name +"'";
System.out.println(sql);
Class.forName("com.mysql.jdbc.Driver");
Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3306/test","kooing", "");
Statement sm=con.createStatement();int result =sm.executeUpdate(sql);
}%>
My JSP 'operatePerson.jsp' starting pageView Code
insert
String basePath= request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
My JSP 'Insert.jsp' starting pagemyAction= myAction == null? "add" : "save";
System.out.println(myAction);
System.out.println(request.getAttribute("name"));
System.out.println(request.getAttribute("age"));
System.out.println(request.getAttribute("sex"));%>
姓名年龄
性别
View Code