java数据库操作问题(上一条数据、下一条数据)
发布网友
发布时间：2022-04-09 01:39
共3个回答
热心网友
时间：2022-04-09 03:09
给你个思路吧：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib prefix="s" uri="/struts-tags" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'list.jsp' starting page</title>
<meta http-equiv="content-type" content="text/html;charset=utf-8">
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<style>
.row0{
background: #AABBFF;
}
.row1{
background: #FFAABB;
}
</style>
</head>
<body>
<div><span style="color:red;">请用list.action访问该页面</span></div>
<table width="100%" border="1px solid black" cellpadding="0" cellspacing="0">
<caption>用户列表及操作</caption>
<thead>
<tr>
<th width="20%">ID</th>
<th width="20%">用户名</th>
<th width="20%">密码</th>
<th width="20%">是否可用</th>
<th width="20%">操作</th>
</tr>
</thead>
<tbody>
<s:iterator value="%{users}" status="s">
<tr class="row${s.index%2 }">
<td>${id }</td>
<td>${username }</td>
<td>${password }</td>
<td>${valid }</td>
<td>
<a href="delete.action?id=${id }&p.page=${p.page }" onclick="return confirm('确定删除？')">删除</a>
<a href="load.action?id=${id }&p.page=${p.page }">修改</a>
</td>
</tr>
</s:iterator>
</tbody>
</table>
<div>
<form action="list.action">
<s:if test="%{p.page != 1}">
<a href="list.action?p.page=1">首页</a>
</s:if>
<s:else>
<a style="color:#ccc;">首页</a>
</s:else>
<s:if test="%{p.page != 1}">
<a href="list.action?p.page=${p.page-1 }">上一页</a>
</s:if>
<s:if test="%{p.page != p.totalPage}">
<a href="list.action?p.page=${p.page+1 }">下一页</a>
</s:if>
<s:if test="%{p.page != p.totalPage}">
<a href="list.action?p.page=${p.totalPage }">末页</a>
</s:if>
<s:else>
<a style="color:#ccc;">末页</a>
</s:else>
<span>第${p.page }页/共${p.totalPage }页</span>
<span>跳<input id="page" name="p.page" style="width:30px;">页</span>
<input type="submit" value="go">
</form>
</div>
<div>
<input type="button" value="添加用户" onclick="window.location='add.jsp'"/>
</div>
</body>
</html>
package com.action;
import java.util.ArrayList;
import java.util.List;
import com..UserDao;
import com..UserDaoImpl;
import com.opensymphony.xwork2.ActionSupport;
import com.pojo.User;
import com.util.Pagination;
public class UserAction extends ActionSupport{
private UserDao = new UserDaoImpl();
private List<User> users = new ArrayList<User>();
private User user;
private int id;
private Pagination p = new Pagination();
public String list(){
System.out.println("======================list.action");
try {
System.out.println("list:"+p.getPage()+p.getPageSize());
.getMaxPage(p);
users = .list(p);
return "success";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "failed";
}
public String add(){
System.out.println("======================add.action");
try {
.add(user);
return "success";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "failed";
}
public String delete(){
System.out.println("======================delete.action");
try {
.delete(id);
return "success";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "failed";
}
public String load(){
System.out.println("======================load.action");
try {
user = .findUserById(id);
return "success";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "failed";
}
public String modify(){
System.out.println("======================modify.action");
try {
user.setId(id);//设置user的id为所要修改的id
.update(user);
return "success";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "failed";
}
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Pagination getP() {
return p;
}
public void setP(Pagination p) {
this.p = p;
}
}
package com.util;
/**
* 分页
* @author yun
*
*/
public class Pagination {
private int pageSize = 3;//每页显示几条数据
private int totalPage = Integer.MAX_VALUE;//共多少页
private int page = 1;//第几页
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
if(pageSize <= 0){
pageSize = 3;
}
this.pageSize = pageSize;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
if(totalPage <=0){
totalPage = 1;
}
this.totalPage = totalPage;
//下面的setPage(page)一定要有，因为totalPage是查询出来的，这会影响page的值。
//如：原来有12页数据，现在查询出来的只有2页，那么page大于2的页应该就不存在了
setPage(page);
}
public int getPage() {
return page;
}
public void setPage(int page) {
System.out.println("=========totalPage:"+totalPage);
if(page <= 0){
page = 1;
}
if(page > totalPage){
page = totalPage;
}
this.page = page;
}
}
package com.;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.pojo.User;
import com.util.DBUtil;
import com.util.Pagination;
public class UserDaoImpl implements UserDao {
public void add(User u) throws Exception {
int id = searchMaxId();//获得id值，相当于id = seq.nextval()
System.out.println("获得id值=============="+id);
u.setId(id);
String sql = "insert into s_user(id,username,password,valid) " +
" values(?,?,?,?)";
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, u.getId());
pstmt.setString(2, u.getUsername());
pstmt.setString(3, u.getPassword());
pstmt.setString(4, u.getValid());
System.out.println("打印sql："+sql+"\t参数:["+u.getId()+","+u.getUsername()+","+u.getPassword()+","+u.getValid()+"]");
pstmt.executeUpdate();
DBUtil.close(pstmt);
DBUtil.close(conn);
}
/**
* 查询记录中最大的id，如果没有就默认为1，有就+1返回
* @return
*/
private int searchMaxId() {
int id = 1;
String sql = "select max(id) mid from s_user";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
stmt = conn.createStatement();
System.out.println("打印sql："+sql);
rs = stmt.executeQuery(sql);
if(rs.next()){
id = rs.getInt("mid")+1;//有结果，就+1
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return id;
}
public void delete(int id) throws Exception {
String sql = "delete from s_user where id = ?";
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
System.out.println("打印sql："+sql+"\t参数：["+id+"]");
pstmt.executeUpdate();
DBUtil.close(pstmt);
DBUtil.close(conn);
}
public User findUserById(int id) throws Exception {
User u = null;
String sql = "select * from s_user where id = ?";
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
System.out.println("打印sql："+sql+"\t参数：["+id+"]");
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
u = new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassword(rs.getString("password"));
u.setValid(rs.getString("valid"));
}
DBUtil.close(rs);
DBUtil.close(pstmt);
DBUtil.close(conn);
return u;
}
public List<User> list() throws Exception {
List<User> list = new ArrayList<User>();
String sql = "select id,username,password,valid from s_user order by id";
Connection conn = DBUtil.getConnection();
Statement stmt = conn.createStatement();
System.out.println("打印sql："+sql);
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String valid = rs.getString("valid");
User u = new User();
u.setId(id);
u.setUsername(username);
u.setPassword(password);
u.setValid(valid);
list.add(u);
}
DBUtil.close(rs);
DBUtil.close(stmt);
DBUtil.close(conn);
return list;
}
public List<User> list(Pagination p) throws Exception {
List<User> list = new ArrayList<User>();
String sql = "select * from (select ini.*,rownum rn from " +
"(select id,username,password,valid from s_user order by id) ini " +
"where rownum <= ?) where rn >= ? ";
int begin = p.getPageSize()*(p.getPage()-1)+1;//10*(3-1)+1=21 1-10 11-20 21-30
int end = p.getPageSize()*p.getPage();//10*3=30
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, end);
pstmt.setInt(2, begin);
System.out.println("打印sql："+sql+"\t参数：["+end+","+begin+"]");
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String valid = rs.getString("valid");
User u = new User();
u.setId(id);
u.setUsername(username);
u.setPassword(password);
u.setValid(valid);
list.add(u);
}
DBUtil.close(rs);
DBUtil.close(pstmt);
DBUtil.close(conn);
return list;
}
public void update(User u) throws Exception {
String sql = "update s_user set username=?,password=?,valid=? where id=?";
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, u.getUsername());
pstmt.setString(2, u.getPassword());
pstmt.setString(3, u.getValid());
pstmt.setInt(4, u.getId());
System.out.println("打印sql："+sql+"\t参数:["+u.getUsername()+","+u.getPassword()+","+u.getValid()+","+u.getId()+"]");
pstmt.executeUpdate();
DBUtil.close(pstmt);
DBUtil.close(conn);
}
/**
* 设置最大页
* @param p
* @throws SQLException
*/
public void getMaxPage(Pagination p) throws SQLException{
String sql = "select count(*) from s_user";
Connection conn = DBUtil.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
int s = rs.getInt(1);//一共有多少条数据
int n = p.getPageSize();//每页大小
int t = (s+n-1)/n;
p.setTotalPage(t);//共有多少页
}
DBUtil.close(rs);
DBUtil.close(stmt);
DBUtil.close(conn);
}
/**
* 测试UserDaoImpl
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
UserDaoImpl impl = new UserDaoImpl();
int size = impl.list().size();
System.out.println("size======"+size);//size======0
}
}
总体来说，这是一个翻页查询问题。
上一条，下一条如果是两个按钮的话，就要写javascript去异步发送请求了，请求时来带上参数，参数为当前的页数减一（上一条），或加一（下一条）。然后就是写查询语句了。
如：
public List<User> list(Pagination p) throws Exception {
List<User> list = new ArrayList<User>();
String sql = "select * from (select ini.*,rownum rn from " +
"(select id,username,password,valid from s_user order by id) ini " +
"where rownum <= ?) where rn >= ? ";
int begin = p.getPageSize()*(p.getPage()-1)+1;//10*(3-1)+1=21 1-10 11-20 21-30
int end = p.getPageSize()*p.getPage();//10*3=30
Connection conn = DBUtil.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, end);
pstmt.setInt(2, begin);
System.out.println("打印sql："+sql+"\t参数：["+end+","+begin+"]");
ResultSet rs = pstmt.executeQuery();
while(rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String valid = rs.getString("valid");
User u = new User();
u.setId(id);
u.setUsername(username);
u.setPassword(password);
u.setValid(valid);
list.add(u);
}
DBUtil.close(rs);
DBUtil.close(pstmt);
DBUtil.close(conn);
return list;
}
同理，还是要去查询，然后得到两条记录，还是要传参数的。
热心网友
时间：2022-04-09 04:27
热心网友
时间：2022-04-09 06:01
