oracle数据库实现分页（使用Oracle和hibernate实现分类）
oracle数据库实现分页（使用Oracle和hibernate实现分类）由于在Oracle中的sql没有limit 所以不能使用limit对数据进行分割。那么oracle有个字段叫做rownum一、分页原理 username VARCHAR2(20) AGE NUMBER)
首先建立个表：
create table Users
(
USERID VARCHAR2(20)
username VARCHAR2(20)
AGE NUMBER
)
一、分页原理
由于在Oracle中的sql没有limit 所以不能使用limit对数据进行分割。那么oracle有个字段叫做rownum
select rownum t.* from users t，我们利用rownum进行数据分割
比如我们每页3条记录 pagesize=3，设当前页数的变量为currPage
例如第2页的数据那么分页切割的sql语句为：
select * from (select t.* rownum rn from (select * from users) t where rownum<=6) where rn>=4
开始：(currPage-1)*pagesize 1 =4
结束： currPage*pagesize =6
二、在Oracle中使用hibernate查询出所有的记录
在使用hibernate的时候，注意给表加主键，不然生成的实体文件和映射文件会出问题
public static void main(String[] args) {
// TODO Auto-generated method stub
Session session = HibernatesessionFactory.getSession();
String sql="select * from users";
SQLQuery query = session.createSQLQuery(sql);
List list = query.list();
for(Object obj_arr:list)
{
Object obj[]=(Object[])obj_arr;
System.out.println(obj[0] " " obj[1] " " obj[2]);
}
}
三、分页程序
public static void main(String[] args) {
// TODO Auto-generated method stub
//1、每页3条
int pagesize=3;
//设置当前为第3页
int currPage=3;
int v_begin=0;//开始
int v_end=0;//结束
v_begin=(currPage-1)*pagesize 1 ;
v_end=currPage*pagesize;
Session session = HibernateSessionFactory.getSession();
String sql=" select * from (select t.* rownum rn "
"from (select * from users) t where rownum<=" v_end ") where rn>=" v_begin;
SQLQuery query = session.createSQLQuery(sql);
List list = query.list();
for(Object obj_arr:list)
{
Object obj[]=(Object[])obj_arr;
System.out.println(obj[0] " " obj[1] " " obj[2]);
}
}
下面给出所有参考源代码：
1、实体文件Users.java
package com.po;
import java.math.BigDecimal;
/**
* Users entity. @author MyEclipse Persistence Tools
*/
public class Users implements java.io.Serializable {
// Fields
private String userid;
private String username;
private BigDecimal age;
// Constructors
/** default constructor */
public Users() {
}
/** minimal constructor */
public Users(String userid) {
this.userid = userid;
}
/** full constructor */
public Users(String userid String username BigDecimal age) {
this.userid = userid;
this.username = username;
this.age = age;
}
// Property accessors
public String getUserid() {
return this.userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getUsername() {
return this.username;
}
public void setUsername(String username) {
this.username = username;
}
public BigDecimal getAge() {
return this.age;
}
public void setAge(BigDecimal age) {
this.age = age;
}
}
2、映射文件Users.hbm.xml
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.po.Users" table="USERS" schema="SYSTEM">
<id name="userid" type="java.lang.String">
<column name="USERID" length="20" />
<generator class="assigned" />
</id>
<property name="username" type="java.lang.String">
<column name="USERNAME" length="20" />
</property>
<property name="age" type="java.math.BigDecimal">
<column name="AGE" precision="22" scale="0" />
</property>
</class>
</hibernate-mapping>
3、配置文件
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>
<session-factory>
<property name="dialect">
org.hibernate.dialect.Oracle9Dialect
</property>
<property name="connection.url">
jdbc:oracle:thin:@127.0.0.1:1521:ORCL
</property>
<property name="connection.username">system</property>
<property name="connection.password">lily001</property>
<property name="connection.driver_class">
oracle.jdbc.driver.OracleDriver
</property>
<property name="myeclipse.connection.profile">
oracle.jdbc.driver.OracleDriver
</property>
<mapping resource="com/po/Users.hbm.xml" />
</session-factory>
</hibernate-configuration>
4、hibernate自动生成的工具类HibernateSessionFactory.java
package com.test;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
/**
* Configures and provides access to Hibernate sessions tied to the
* current thread of execution. Follows the Thread Local Session
* pattern see {@link http://hibernate.org/42.html }.
*/
public class HibernateSessionFactory {
/**
* Location of hibernate.cfg.xml file.
* Location should be on the classpath as Hibernate uses
* #resourceAsStream style lookup for its configuration file.
* The default classpath location of the hibernate config file is
* in the default package. Use #setConfigFile() to update
* the location of the configuration file for the current session.
*/
private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";
private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
private static Configuration configuration = new Configuration();
private static org.hibernate.SessionFactory sessionFactory;
private static String configFile = CONFIG_FILE_LOCATION;
static {
try {
configuration.configure(configFile);
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
System.err
.println("%%%% Error Creating SessionFactory %%%%");
e.printStackTrace();
}
}
private HibernateSessionFactory() {
}
/**
* Returns the ThreadLocal Session instance. Lazy initialize
* the <code>SessionFactory</code> if needed.
*
* @return Session
* @throws HibernateException
*/
public static Session getSession() throws HibernateException {
Session session = (Session) threadLocal.get();
if (session == null || !session.isOpen()) {
if (sessionFactory == null) {
rebuildSessionFactory();
}
session = (sessionFactory != null) ? sessionFactory.openSession()
: null;
threadLocal.set(session);
}
return session;
}
/**
* Rebuild hibernate session factory
*
*/
public static void rebuildSessionFactory() {
try {
configuration.configure(configFile);
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
System.err
.println("%%%% Error Creating SessionFactory %%%%");
e.printStackTrace();
}
}
/**
* Close the single hibernate session instance.
*
* @throws HibernateException
*/
public static void closeSession() throws HibernateException {
Session session = (Session) threadLocal.get();
threadLocal.set(null);
if (session != null) {
session.close();
}
}
/**
* return session factory
*
*/
public static org.hibernate.SessionFactory getSessionFactory() {
return sessionFactory;
}
/**
* return session factory
*
* session factory will be rebuilded in the next call
*/
public static void setConfigFile(String configFile) {
HibernateSessionFactory.configFile = configFile;
sessionFactory = null;
}
/**
* return hibernate configuration
*
*/
public static Configuration getConfiguration() {
return configuration;
}
}
5、分页的测试文件
package com.test;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
//1、每页3条
int pagesize=3;
//设置当前为第3页
int currPage=3;
int v_begin=0;//开始
int v_end=0;//结束
v_begin=(currPage-1)*pagesize 1 ;
v_end=currPage*pagesize;
Session session = HibernateSessionFactory.getSession();
String sql=" select * from (select t.* rownum rn "
"from (select * from users) t where rownum<=" v_end ") where rn>=" v_begin;
SQLQuery query = session.createSQLQuery(sql);
List list = query.list();
for(Object obj_arr:list)
{
Object obj[]=(Object[])obj_arr;
System.out.println(obj[0] " " obj[1] " " obj[2]);
}
}
}
