投稿
  1. 首页
  2. sql

java中获取数据库中最近的下一条记录

浪里白条 • 2023-3-27 • sql • 阅读 64

java中获取数据库中最近的下一条记录,第1张

最近的下一条记录——没明白。

是最近的记录，还是当前记录的下一条记录。

最近的记录：

select

*

from

(select

*

from

表名

order

by

时间字段

desc)

where

rownum=1

当前记录的下一条记录：

((ResultSet)rs).next()

给你个思路吧：

&lt%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%&gt

&lt%@ taglib prefix="s" uri="/struts-tags" %&gt

&lt%

String path = request.getContextPath()

String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"

%&gt

&lt!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"&gt

&lthtml&gt

&lthead&gt

&ltbase href="&lt%=basePath%&gt"&gt

&lttitle&gtMy JSP 'list.jsp' starting page&lt/title&gt

&ltmeta http-equiv="content-type" content="text/htmlcharset=utf-8"&gt

&ltmeta http-equiv="pragma" content="no-cache"&gt

&ltmeta http-equiv="cache-control" content="no-cache"&gt

&ltmeta http-equiv="expires" content="0"&gt

&ltmeta http-equiv="keywords" content="keyword1,keyword2,keyword3"&gt

&ltmeta http-equiv="description" content="This is my page"&gt

&lt!--

&ltlink rel="stylesheet" type="text/css" href="styles.css"&gt

--&gt

&ltstyle&gt

.row0{

background: #AABBFF

}

.row1{

background: #FFAABB

}

&lt/style&gt

&lt/head&gt

&ltbody&gt

&ltdiv&gt&ltspan style="color:red"&gt请用list.action访问该页面&lt/span&gt&lt/div&gt

&lttable width="100%" border="1px solid black" cellpadding="0" cellspacing="0"&gt

&ltcaption&gt用户列表及 *** 作&lt/caption&gt

&ltthead&gt

&lttr&gt

&ltth width="20%"&gtID&lt/th&gt

&ltth width="20%"&gt用户名&lt/th&gt

&ltth width="20%"&gt密码&lt/th&gt

&ltth width="20%"&gt是否可用&lt/th&gt

&ltth width="20%"&gt *** 作&lt/th&gt

&lt/tr&gt

&lt/thead&gt

&lttbody&gt

&lts:iterator value="%{users}" status="s"&gt

&lttr class="row${s.index%2 }"&gt

&lttd&gt${id }&lt/td&gt

&lttd&gt${username }&lt/td&gt

&lttd&gt${password }&lt/td&gt

&lttd&gt${valid }&lt/td&gt

&lttd&gt

&lta href="delete.action?id=${id }&ampp.page=${p.page }" onclick="return confirm('确定删除？')"&gt删除&lt/a&gt

&lta href="load.action?id=${id }&ampp.page=${p.page }"&gt修改&lt/a&gt

&lt/td&gt

&lt/tr&gt

&lt/s:iterator&gt

&lt/tbody&gt

&lt/table&gt

&ltdiv&gt

&ltform action="list.action"&gt

&lts:if test="%{p.page != 1}"&gt

&lta href="list.action?p.page=1"&gt首页&lt/a&gt

&lt/s:if&gt

&lts:else&gt

&lta style="color:#ccc"&gt首页&lt/a&gt

&lt/s:else&gt

&lts:if test="%{p.page != 1}"&gt

&lta href="list.action?p.page=${p.page-1 }"&gt上一页&lt/a&gt

&lt/s:if&gt

&lts:if test="%{p.page != p.totalPage}"&gt

&lta href="list.action?p.page=${p.page+1 }"&gt下一页&lt/a&gt

&lt/s:if&gt

&lts:if test="%{p.page != p.totalPage}"&gt

&lta href="list.action?p.page=${p.totalPage }"&gt末页&lt/a&gt

&lt/s:if&gt

&lts:else&gt

&lta style="color:#ccc"&gt末页&lt/a&gt

&lt/s:else&gt

&ltspan&gt第${p.page }页/共${p.totalPage }页&lt/span&gt

&ltspan&gt跳&ltinput id="page" name="p.page" style="width:30px"&gt页&lt/span&gt

&ltinput type="submit" value="go"&gt

&lt/form&gt

&lt/div&gt

&ltdiv&gt

&ltinput type="button" value="添加用户" onclick="window.location='add.jsp'"/&gt

&lt/div&gt

&lt/body&gt

&lt/html&gt

package com.action

import java.util.ArrayList

import java.util.List

import com.dao.UserDao

import com.dao.UserDaoImpl

import com.opensymphony.xwork2.ActionSupport

import com.pojo.User

import com.util.Pagination

public class UserAction extends ActionSupport{

private UserDao dao = new UserDaoImpl()

private List&ltUser&gtusers = new ArrayList&ltUser&gt()

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())

dao.getMaxPage(p)

users = dao.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 {

dao.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 {

dao.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 = dao.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

dao.update(user)

return "success"

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace()

}

return "failed"

}

public List&ltUser&gtgetUsers() {

return users

}

public void setUsers(List&ltUser&gtusers) {

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 &lt= 0){

pageSize = 3

}

this.pageSize = pageSize

}

public int getTotalPage() {

return totalPage

}

public void setTotalPage(int totalPage) {

if(totalPage &lt=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 &lt= 0){

page = 1

}

if(page &gttotalPage){

page = totalPage

}

this.page = page

}

}

package com.dao

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&ltUser&gtlist() throws Exception {

List&ltUser&gtlist = new ArrayList&ltUser&gt()

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&ltUser&gtlist(Pagination p) throws Exception {

List&ltUser&gtlist = new ArrayList&ltUser&gt()

String sql = "select * from (select ini.*,rownum rn from " +

"(select id,username,password,valid from s_user order by id) ini " +

"where rownum &lt= ?) where rn &gt= ? "

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

}

同理，还是要去查询，然后得到两条记录，还是要传参数的。

1.可以用离线查询，就是先把数据都取出来，再用list *** 作。

2.实时查询，每次查询5条记录，即第一条,下一条,当前记录，前一条,最后一条。这样就有了每条记录的id号，再提交按钮的是后直接用java查询，重复即可。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/6751310.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
浪里白条 浪里白条 一级用户组
0 0
上一篇 2023-03-27
下一篇 2023-03-27

发表评论

登录后才能评论

评论列表（0条）

保存