伪列
rownum：伪列
需求：根据伪列实现查询tb_news表中的第一条到第五条记录
方案：将rownum伪列字段变成明列字段
代码： select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between 1 and 5;
回顾oracle中的伪列使用及其与分页相关知识的使用
rownum：伪列
需求：根据伪列实现查询tb_news表中的第一条到第五条记录
方案：将rownum伪列字段变成明列字段
代码： select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between 1 and 5;
回顾oracle中的伪列使用及其与分页相关知识的使用
--实现javaweb中的数据分页--依赖于伪列
select * from tb_news;
--根据伪列实现查询tb_news表中的第一条到第五条记录（不考虑排序）
--注意：如果一个select插叙语句中包含rownum字段，那么对于区间查询中的算术运算符(>)无效
--解决：将rownum伪列字段变成明列字段即可
select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between 1 and 5;
--按钮 点击 6-10
select count(*) from tb_news;
--假设：在某一个显示区域显示新闻 通过分页显示
--规定：可以显示多少条数据 假设：一次性只能显示5条数据 默认显示第一页
--规律：默认第一页 每一页显示5条数据
int pageIndex = 1;//页码 默认从第一页开始显示
int pageSize = 5;//每一页显示的新闻的条数
--当pageIndex = 1 pageSize = 5的情况下 显示第一页数据
select b.* from (
select a.*,rownum as rid from tb_news a
) b where b.rid between (pageIndex-1)*pageSize+1 and pageIndex * pageSize;--1-5
--按钮 点击 下一页 控制pageIndex+1 pageIndex = 2
--当pageIndex = 2 pageSize = 5的情况下 显示第一页数据
select b.* from (
select a.*,rownum as rid from tb_news a
) b where b.rid between (pageIndex-1)*pageSize+1 and pageIndex*pageSize;
--按钮 点击 下一页 pageIndex = 2 控制pageIndex+1 pageIndex = 3
--当pageIndex = 3 pageSize = 5的情况下 显示第一页数据
select b.* from (
select a.*,rownum as rid from tb_news a
) b where b.rid between (pageIndex-1)*pageSize+1 and pageIndex*pageSize;
select count(*) from tb_news where ntitle like '%红楼梦%'
--问题：实现带模糊查询的分页
--先模糊查询 再次分页 ？ 1
--先分页 再模糊查询？ 2
select b.* from (
select a.*,rownum as rid from (
select * from tb_news where ntitle like '%1%'
)a
)b where b.rid between 1 and 5;
select count(*) from tb_news where ntitle like '%1%'
以上效果在oracle数据库中使用
分页
实现javaweb中的数据分页将依赖于伪列
（1）分析每一页显示多少条数据
（2）找到每一页与条数之间的关系
（3）将找到的sql规律利用到javaweb中
（4）编写分页和显示条数的方法
简单的代码如下：
<%
//request请求对象设置编码
request.setCharacterEncoding("utf-8");
//实例化DAO
INewsDao ind = new NewsDaoImpl();
//pageIndex 页码 默认第一页
int pageIndex = 1;
//pageSize 每页显示的条数 5条
int pageSize = 5;
//当手动点击了下一页按钮 获取pageIndex 赋值给第75处的那个pageINdex
String pIndex = request.getParameter("pageIndex");
if (null != pIndex) {//说明点击了下一页
pageIndex = Integer.valueOf(pIndex);
}
//手动点击了搜索提交按钮 获取到模糊查询的关键字 否则没有点击的情况下 null 转换
String strName = request.getParameter("strName");
if (strName == null) {//没有手动点击搜索
strName = "";
} else {//手动点击了搜索
//编码和解码
//strName = new String(strName.getBytes("ISO-8859-1"),"utf-8");
}
System.out.println("strName = " + strName);
//不考虑不糊查询
int count = ind.getNewsCount(strName);//6
int pageMax = 0;
if (count % pageSize == 0) {
pageMax = count / pageSize;
} else {
pageMax = count / pageSize + 1;
}
System.out.println("count： " + count);
System.out.println("pageMax： " + pageMax);
//调用查看所有的新闻的方法
List<News> listNews = ind.queryNewsAll5(pageIndex, pageSize, strName);//第一个参数页码 第二个参数：条数
for (News news : listNews) {
%>
<!-- 填充新闻标题以及时间或者作者 -->
<li>
<!-- 新闻标题 --> <a href='#'><%=news.getNtitle()%></a> <span>
作者：<%=news.getNauthor()%>      <a href='#'>修改</a>
     <a href='javascript:void(0)'
οnclick='clickdel()'>删除</a>
</span>
</li>
<%
需要在src中创建好需要的类
提供代码如下：
package com.news.dao;
import java.util.List;
import com.news.entity.News;
/**
* 新闻管理的dao接口
*
* @author Administrator
*
*/
public interface INewsDao {
/**
* 方法功能：获取新闻信息表中所有的新闻记录
*
* @return 集合
*/
public List<News> queryNewsAll();
/**
* 分页版本1 方法功能：分页查询
*
*/
public List<News> queryNewsAll2();
/**
* 分页版本2 方法功能：分页
*
* @param pageIndex
* 页码 默认是第一页
* @param pageSize
* 每一页显示的新闻条数
*
*/
public List<News> queryNewsAll3(int pageIndex, int pageSize);
/**
* 方法功能：求新闻信息表中的总记录数
*/
public int getNewsCount();
/**
* 模糊查询
*/
public List<News> queryNewsAll4(String strName);
/**
* 带模糊查询的分页查询
*/
public List<News> queryNewsAll5(int pageIndex, int pageSize, String strName);
/**
* 方法功能：求新闻信息表中的总记录数(带模糊查询)
*/
public int getNewsCount(String strName);
}
package com.news.dao;
import com.news.entity.Theme;
/**
* 新闻分类管理接口
*
* @author Administrator
*
*/
public interface IThemeDao {
/**
* 方法功能：根据新闻分类编号查找对应的分类名称
*
* @param tid
* @return
*/
public Theme getThemeByTid(int tid);
}
package com.news.dao;
import com.news.entity.Users;
/**
* 用户模块的DAO接口
*
* @author Administrator
*
*/
public interface IUsersDao {
// 用户登录：返回类型--boolean 当前登录Users用户
/**
* 方法功能登录操作
*/
public Users adminUsersLogin(Users users);
/**
* 注册功能
*/
public int addUsersLogin(Users users);
/**
* 修改密码功能
*/
public int setUsersPassword(Users users, String newPassword);
}
package com.news.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.news.dao.INewsDao;
import com.news.entity.News;
import com.news.entity.Theme;
import com.news.untils.DBHelper;
public class NewsDaoImpl implements INewsDao {
@Override
public List<News> queryNewsAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<News> list = new ArrayList<News>();
try {
conn = DBHelper.getConn();
String sql = "select * from tb_news";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
// 获取分类编号rs.getInt("ntid");
Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
rs.getString("nsummary"), rs.getString("ncontent"), rs.getString("nimage"),
rs.getString("ndate"), rs.getInt("ncount")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return list;
}
@Override
public List<News> queryNewsAll2() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<News> list = new ArrayList<News>();
try {
conn = DBHelper.getConn();
String sql = "select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between 1 and 5";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
// 获取分类编号rs.getInt("ntid");
Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
rs.getString("nsummary"), rs.getString("ncontent"), rs.getString("nimage"),
rs.getString("ndate"), rs.getInt("ncount")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return list;
}
@Override
public List<News> queryNewsAll3(int pageIndex, int pageSize) {
// 1 5 第一页 5条数据 start = 1 end = 5;
// 2 5 第二页 5条数据 start = 6 end = 10
// 根据参数pageIndex和pageSize来计算区间查询的规律
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<News> list = new ArrayList<News>();
try {
conn = DBHelper.getConn();
String sql = "select b.* from ( select a.*,rownum as rid from tb_news a ) b where b.rid between " + start
+ " and " + end + "";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
// 获取分类编号rs.getInt("ntid");
Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
rs.getString("nsummary"), rs.getString("ncontent"), rs.getString("nimage"),
rs.getString("ndate"), rs.getInt("ncount")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return list;
}
@Override
public List<News> queryNewsAll4(String strName) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<News> list = new ArrayList<News>();
try {
conn = DBHelper.getConn();
String sql = "select * from tb_news where ntitle like '%" + strName + "%'";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
// 获取分类编号rs.getInt("ntid");
Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
rs.getString("nsummary"), rs.getString("ncontent"), rs.getString("nimage"),
rs.getString("ndate"), rs.getInt("ncount")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return list;
}
@Override
public List<News> queryNewsAll5(int pageIndex, int pageSize, String strName) {
// 1 5 第一页 5条数据 start = 1 end = 5;
// 2 5 第二页 5条数据 start = 6 end = 10
// 根据参数pageIndex和pageSize来计算区间查询的规律
int start = (pageIndex - 1) * pageSize + 1;
int end = pageIndex * pageSize;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List<News> list = new ArrayList<News>();
try {
conn = DBHelper.getConn();
String sql = "select b.* from (\r\n" + " select a.*,rownum as rid from (\r\n"
+ " select * from tb_news where ntitle like '%" + strName + "%'\r\n"
+ " )a\r\n" + ")b where b.rid between " + start + " and " + end + "";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
// 获取分类编号rs.getInt("ntid");
Theme theme = new ThemeDaoImpl().getThemeByTid(rs.getInt("ntid"));
list.add(new News(rs.getInt("nid"), theme, rs.getString("ntitle"), rs.getString("nauthor"),
rs.getString("nsummary"), rs.getString("ncontent"), rs.getString("nimage"),
rs.getString("ndate"), rs.getInt("ncount")));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return list;
}
@Override
public int getNewsCount() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;// 保存表的总记录数
try {
conn = DBHelper.getConn();
String sql = "select count(*) from tb_news";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return count;
}
@Override
public int getNewsCount(String strName) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;// 保存表的总记录数
try {
conn = DBHelper.getConn();
String sql = "select count(*) from tb_news where ntitle like '%" + strName + "%'";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return count;
}
public static void main(String[] args) {
// System.out.println(new NewsDaoImpl().getNewsCount());
// List<News> list = new NewsDaoImpl().queryNewsAll2();
// System.out.println(list.size());
// for (News news : list) {
// System.out.println(news);
// }
List<News> list = new NewsDaoImpl().queryNewsAll5(2, 3, "2");
for (News news : list) {
System.out.println(news.getNtitle());
}
}
}
package com.news.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.news.dao.IThemeDao;
import com.news.entity.Theme;
import com.news.untils.DBHelper;
public class ThemeDaoImpl implements IThemeDao {
@Override
public Theme getThemeByTid(int tid) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
Theme theme = null;
try {
conn = DBHelper.getConn();
String sql = "select * from tb_news_theme where tid = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, tid);
rs = ps.executeQuery();
if (rs.next()) {
theme = new Theme(rs.getInt(1), rs.getString(2));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, rs);
}
return theme;
}
public static void main(String[] args) {
System.out.println(new ThemeDaoImpl().getThemeByTid(1));
}
————————————————
版权声明：本文为CSDN博主「凡哈哈」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/l65656565/article/details/124458916
扫一扫
536
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
