[2００分]使用hibernate如何实现多表关联的查询和分页！最好给个实例！！

[zhangsq]

使用hibernate如何实现多表关联的查询和分页！
最好给个实例！！
先谢谢大侠！！！！！！

[mofeir]

//ＨＱＬ 0-20
Query query = session.createQuery("from tableName t order by t.name asc");
query.setFirstResult(0);
query.setMaxResults(20);
list result = query.list();

[fashchina]

UP

[bluelily22]

HQL 也是可以像SQL一样写多表联接查询的呀，但是如果你多表之间建立关联的话，你根本不需要写联接查询，在相关类会生成关联表对象的set集合，就像属性一样使用，很方便的.

这有一个参考
http://www.7880.com/Print.php?PID=8a69f5c0

有时间买一本精通hibernate看看，这书讲的不错，看完后你的hibernate水平就应该比较高了:)

[OnError]

我用过没问题的,你把hibernate生成的类替换一下就好了.
祝你成功.
HibernateUtil.java
获得连结
<----------------------------------------------------->

import net.sf.hibernate.*;
import net.sf.hibernate.cfg.*;
import org.apache.log4j.Logger;


public class HibernateUtil {

private static Logger log = Logger.getLogger(HibernateUtil.class);

private static final SessionFactory sessionFactory;

static{
try{
//Create the SessionFactory
sessionFactory = new Configuration().configure().buildSessionFactory();
}catch(Throwable ex){
ex.printStackTrace();
log.error("Initial SessionFactory creation failed.",ex);
throw new ExceptionInInitializerError(ex);
}
}

public static final ThreadLocal session = new ThreadLocal();
public static Session currentSession() throws HibernateException{
Session s = (Session) session.get();

//Open a new Session,if this Thread has none yet
if(s == null){
s = sessionFactory.openSession();
session.set(s);
}
return s;
}
public static void closeSession() throws HibernateException{
Session s = (Session) session.get();
session.set(null);
if(s != null)
s.close();
}
}

<------------------------------------------------------->
Page.java
分页
<--------------------------------->
mport net.sf.hibernate.*;
import java.util.List;
import java.util.Collection;
import java.util.ArrayList;


public class Page {

public List Total_List;

public Collection Result_coll;

public int Row_Num;

public int Page_Num;

private int pageSize;

//initailzation
public Page(Query query, int pageSize) {

this.pageSize = pageSize;

this.Result_coll = new ArrayList();

try {

this.Total_List = query.list();

this.Row_Num = this.Total_List.size();

this.Page_Num = (this.Row_Num % this.pageSize > 0) ? (this.Row_Num/ this.pageSize + 1) : (this.Row_Num / this.pageSize);

} catch (Exception e) {
System.err.println("Page.class initialization failed");
}
}

/**
*
* @param page 需要显示的页号码
* @return Collection
*/
public Collection ChangePage(int page) {

int BigenNum = page * this.pageSize;
int EndNum = (page + 1) * this.pageSize - 1;

for (int i = BigenNum; i <= EndNum; i++) {
this.Result_coll.add(this.Total_List.get(i));
}

return this.Result_coll;
}

//返回 结果总数
public int getTotalNum(){
return this.Row_Num;
}

//返回 总页数
public int getPageNum(){
return this.Page_Num;
}

}
<------------------------------------------------------>
test.java
测试
<------------------------------------------------------>
import net.sf.hibernate.*;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Collection;
import java.util.List;

public class test {

public static void main(String[] args) {
try{
Session session = HibernateUtil.currentSession();


StockIn Si = new StockIn();
OrgInfo Oi = new OrgInfo();
MainYjsj MY = new MainYjsj();
Object[] ob = null;



net.sf.hibernate.Query query = session.createQuery("from OrgInfo as a,StockIn as b where b.OrgId= a.OrgId and b.OrgId like '001%' and b.InDate>='2005-1-8' and b.InDate<='2005-7-8'");


Page p = new Page(query,10); //参数2为页面显示记录行数

System.out.println("Row Total :" + p.getTotalNum());
System.out.println("Page Total :" + p.getPageNum());

Collection pp = new ArrayList();
pp = p.ChangePage(1); //参数为要显示的页数
Iterator si_iter = pp.iterator();

while(si_iter.hasNext()){
Si = (StockIn)si_iter.next();
System.out.println(Si.getCode());
MY = (MainYjsj)si_iter.next();
System.out.println(MY.getSpflmc());
}
HibernateUtil.closeSession();

}
catch(Exception e){
e.printStackTrace();
}
}
}

[tk1984]

多表查询不用遵循HIBERNATE的规定可以直接在createQuery(）方法里直接写多表关联的SQL查询语句就行了，不过更新，删除，插入的时候还要按HIBERNATE的语句规则。查询随便！我做的用友的项目就这样的！

[mythvivi]

帮顶一下！

[zhangsq]

OnError(刚毅之醉)
先谢谢你；能否把StockIn、OrgInfo这两个类对应的类文件、表结构和*.hbm.xml发给我吗！我是刚学！对于多表关联的我还没作成功过！
大侠最好给个能运行了例子，小弟好好看看！！
如果可以马上结贴！！！！！！！！！

[zhangsq]

OnError(刚毅之醉)
先谢谢你；能否把StockIn、OrgInfo这两个类对应的类文件、表结构和*.hbm.xml发给我吗！我是刚学！对于多表关联的我还没作成功过！
大侠最好给个能运行了例子，小弟好好看看！！
如果可以马上结贴！！！！！！！！！
我的email是[e-mail]

[zsq666]

顶

[zhangsq]

我是按你的情况去实现的，但还是不行！报错信息如下：
能否给个能运行的实例！
分不够另开贴再加！！
13:51:44,156 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)

13:51:44,437 INFO SettingsFactory:89 - Use scrollable result sets: true

13:51:44,453 INFO SettingsFactory:96 - echoing all SQL to stdout

13:51:44,453 INFO SettingsFactory:99 - Query language substitutions: {no='N', true=1, yes='Y', false=0}

13:51:44,453 INFO SettingsFactory:110 - cache provider: net.sf.hibernate.cache.HashtableCacheProvider

13:51:44,468 INFO Configuration:1044 - instantiating and configuring caches

13:51:44,640 INFO SessionFactoryImpl:118 - building session factory

13:51:45,187 INFO SessionFactoryObjectFactory:82 - no JNDI name configured

13:51:45,203 INFO UpdateTimestampsCache:35 - starting update timestamps cache at region: net.sf.hibernate.cache.UpdateTimestampsCache

13:51:45,203 INFO QueryCache:39 - starting query cache at region: net.sf.hibernate.cache.QueryCache

StandardWrapperValve[debugjsp]: Servlet.service() for servlet debugjsp threw exception

javax.servlet.ServletException: in expected: A [select A.uid,A.uname,A.passwd,B.name from userinfo A,test_hibernate B where A.eid=B.id]

javax.servlet.ServletException: in expected: A [select A.uid,A.uname,A.passwd,B.name from userinfo A,test_hibernate B where A.eid=B.id]

at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:471)

at org.apache.jsp.testMulti$jsp._jspService(testMulti$jsp.java:115)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServlet.java:201)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:381)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:473)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:243)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:190)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2347)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:170)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:564)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)

at org.apache.catalina.core.StandardPipeline.invokeNext(StandardPipeline.java:566)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:472)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:943)

at org.apache.catalina.connector.http.HttpProcessor.process(HttpProcessor.java:1027)

at org.apache.catalina.connector.http.HttpProcessor.run(HttpProcessor.java:1125)

at java.lang.Thread.run(Thread.java:534)

[zhangsq]

运行的代码如下：
Configuration cfg = new Configuration().configure();
SessionFactory sessions = cfg.buildSessionFactory();
Session session1 = sessions.openSession();
Query q = session1.createQuery("select A.uid,A.uname,A.passwd,B.name from userinfo A,test_hibernate B where A.eid=B.id");
ScrollableResults sc=q.scroll();
while(sc.next()){
System.err.println(sc.getInteger(0).intValue());
}
session1.flush();
// List list=q.list();
session1.close();

[programdolt]

关注

[programdolt]

学习

[tk1984]

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import com.pms.sys.dao.ComDAO;
import com.pms.util.db.dblinkpool;
import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSetMetaData;
import com.pms.util.factory.ContextConfig;
import org.apache.log4j.Logger;
import java.sql.Statement;

/**
* 此类的主要功能是配合struts的完成分页输出页面的功能 构造函数有两种 输入参数包含ArrayList对象 这个ArrayList
* 通常是由数据底层访问得到的列表，里面填充的一条记录就是一个VO实体 这种构造只能适合单表的查询列表输出。
*
*
* 这两种构造能满足不同的需要，都能通过Struts的logic:iterate输出，所以比较通用
* 建议在使用的过程中单表查询输出不要使用Sql语句，这样不破坏对象型设计思路 在多表复杂关联查询输出列表的时候才利用Sql语句。
*
* 注意：对象型设计思路不建议直接在业务逻辑中使用Sql语句，这个控件只是单纯用在列表关联查询
* 页面输出中，返回的实体只做显示输出，并不参与业务，所以并不破坏对象型设计模式。
*
* 使用范例1: action中： tablelist tblist = new tablelist("select
* id,name,age,password,bak from student ",
* httpServletRequest.getParameter("paged"), "paged"); ArrayList rows =
* tblist.getRows(); httpServletRequest.setAttribute("rows",rows);
* httpServletRequest.setAttribute("HtmlPageCtl",tblist.getHtmlPageCtl());
*
* 前台JSP中: <table border="1"> <bean:write name="HtmlPageCtl" filter="html"/>
* <logic:iterate id="row" name="rows">
* <tr>
* <td><bean:write name="row" property="id"/></td>
* <td><bean:write name="row" property="name"/></td>
* <td><bean:write name="row" property="age"/></td>
* <td><bean:write name="row" property="password"/></td>
* <td><bean:write name="row" property="bak"/></td>
* </tr>
* </logic:iterate> </table>
*
*
*
*
* <p>
* Title:TableList
* </p>
* <p>
* Description: 页面分页输出控件，建议结合Struts来使用
* </p>
* <p>
* Copyright: Copyright (c) 2004
* </p>
* <p>
* Company:
* </p>
*
* @author Li Kan
* @version 1.0
*/

public class TableList implements Serializable {
private int pagesize = 10;
public final static String PAGE_ROWS = "PAGEROWS";
public final static String HTML_CTR = "HTML_CTR";
public final static String PAGE_TXT = "PAGE_TXT";
public final static String PAGE_TXT_1 = "PAGE_TXT_1";
public final static String PAGE_TXT_2 = "PAGE_TXT_2";
public final static String PAGE_ROWS_1 = "PAGEROWS1";
public final static String PAGE_ROWS_2 = "PAGEROWS2";
private int pageCount;
private int page;
private int rowcount;
private ArrayList rows;
//private dblinkpool db = dblinkpool.newInstance();
final ComDAO dao = (ComDAO) ContextConfig.ctx.getBean("ComDAOImplProxy");
private String HtmlPageController;
private String HtmlCtrlName; //Html下拉页面控制器的名字id
private Logger log = Logger.getLogger(TableList.class);
public int getPageCount() {
return this.pageCount;
}
public int getPage() {
return this.page;
}
public int getRowcount() {
return this.getRowcount();
}
public void setPage(int page) {
this.page = page;
}
public ArrayList getRows() {
return rows;
}
/* 计算 pageCount */
private void __pageCount() {
if (this.pagesize == 0)
return;
pageCount = rowcount / pagesize;
if (rowcount % pagesize != 0)
pageCount = pageCount + 1;

if (this.rowcount == 0) {
pageCount = 0;
page = 0;
}
}
private void __tablelist(ArrayList rslt, int page) {
this.page = page;
page--;
rowcount = rslt.size();
__pageCount();
//System.out.println(page + ":" +pageCount);
if (rslt.size() == 0) {
rows = new ArrayList();
this.__HtmlPageController();
return;
}
if (page < pageCount - 1)
rows = new ArrayList(rslt.subList(pagesize * page, pagesize * page
+ pagesize));
else
rows = new ArrayList(rslt.subList(pagesize * page, rowcount));
this.__HtmlPageController();
}

[tk1984]

private void __tablelist(String sql, int page, Connection conn) {
dblinkpool db = dblinkpool.newInstance();
this.page = page;
page--;
rows = new ArrayList();
try {
//Connection conn = db.getConnection();
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(sql);
if (rs.last())
this.rowcount = rs.getRow();
else
this.rowcount = 0;

__pageCount();
rs.beforeFirst();
ArrayList ColumNames = getResultSetColumnNames(rs);
//System.out.println(ColumNames);
// if((curPage-1)*pageCtl.rowsPerPage>0)
// rs.absolute((curPage-1)*pageCtl.rowsPerPage);
int position = page * pagesize;
if (position > 0)
rs.absolute(position);
//System.out.println("+++" + position);
int j = 0;
if (pagesize == 0) {
pagesize = this.rowcount + 1;
}
while (rs.next() && j < pagesize) {
j++;
HashMap row = new HashMap();
int colcount = ColumNames.size();
for (int i = 0; i < colcount; i++) {
String colname = ColumNames.get(i).toString().toLowerCase();//????????????????
String coltype = columnTypes.get(i).toString();
//对于日期类型的不能用STRING取出来，否则Bean:write 标签的 formatkey功能就没效果了
if (coltype.indexOf("DATE") >= 0)
row.put(colname, rs.getDate(colname));
else
row.put(colname, rs.getString(colname));
}
this.rows.add(row);
}
// db.release();
//dao.closeConnection();
} catch (SQLException ex) {
ex.printStackTrace();
//throw new SystemException(ex.getMessage(), ex);
}
this.__HtmlPageController();
}
private void __HtmlPageController() {
//System.out.println(this.page + " :::: " + this.pageCount);

String goBefore = this.page == 1 ? "\" "
: "if("
+ this.HtmlCtrlName
+ ".value >1)"
+ this.HtmlCtrlName
+ ".value--;opt.value='doList';doActionBfpage();submit(); \" "
+ " onmouseover=\"style:this.style.cursor='hand';this.style.textDecorationUnderline=true\" "
+ " onmouseout = \"this.style.textDecorationUnderline = false\" ";
if (this.rowcount == 0)
goBefore = "\" ";

String goNext = this.page == this.pageCount ? "\" "
: "if("
+ this.HtmlCtrlName
+ ".value < "
+ this.pageCount
+ ")"
+ this.HtmlCtrlName
+ ".value++;opt.value='doList';doActionBfpage();submit(); \" "
+ " onmouseover=\"style:this.style.cursor='hand';this.style.textDecorationUnderline=true \" "
+ " onmouseout = \"this.style.textDecorationUnderline = false\"";

String validpage = "if(parseInt(" + this.HtmlCtrlName + ".value)!="
+ this.HtmlCtrlName + ".value){ alert('请输入数字'); "
+ this.HtmlCtrlName + ".focus(); return;} if("
+ this.HtmlCtrlName + ".value>" + this.pageCount + " ) "
+ this.HtmlCtrlName + ".value = " + this.pageCount + ";"
+ "if(" + this.HtmlCtrlName + ".value < 1)" + this.HtmlCtrlName
+ ".value = 1";

this.HtmlPageController = "P." + this.page + "/" + this.pageCount
+ " 共" + this.rowcount + "条" + " <a onclick=\"javascript:"
+ validpage + ";" + goBefore + ">上页</a> \n"
+ " <a onclick=\"javascript:" + validpage + ";" + goNext
+ ">下页</a> \n";
// String selectHtml = "<select name = \"" + this.HtmlCtrlName + "\"
// id=\"" + this.HtmlCtrlName + "\" onchange =
// \"opt.value='doList';submit(); \"> \n" ;
// String options = "";
// for(int i = 1; i < this.pageCount + 1; i ++) {
// String selected = this.page == i ? " selected=\"selected\" ":" ";
// options += "<option value =\"" + i + "\" " + selected + ">" + i +
// "</option> \n";
// }
// selectHtml += options;
// selectHtml += "</select>\n";
String texthtml = "<script>function doActionBfpage(){}</script><input type=text onblur=\""
+ validpage
+ "\" size=3 name="
+ this.HtmlCtrlName
+ " value="
+ this.page
+ ">"
+ "<input type='button' value='go' onclick=doActionBfpage();submit()>";

this.HtmlPageController += texthtml;
}

public String getHtmlPageCtl() {
return this.HtmlPageController;
}

public void setPageSize(int pagesize) {
//log.debug("------------------------" + pagesize);
this.pagesize = pagesize;
}

/**
* 页面分页控制器构造函数
*
* @param rslt
* ArrayList
* @param requestpage
* 页面提交过来的page 的值
* @param CtrlName
* String 页面page下拉控制器的名称
*/
public TableList(String sql, String requestpage, int pagesize,
String CtrlName, Connection conn) {

this.pagesize = pagesize;
//log.debug("~~~~~~~~~~~~~~~~~~~~~~~" + pagesize);
this.HtmlCtrlName = CtrlName;
int page;
try {
page = requestpage == null ? 1 : Integer.parseInt(requestpage);
} catch (Exception e) {
page = 1;
}
__tablelist(sql, page, conn);
}
private ArrayList columnTypes = new ArrayList();
private ArrayList getResultSetColumnNames(ResultSet rs) {
ArrayList columnNames = null;
try {
//get a reference to the ResultSet's meta data
ResultSetMetaData md = rs.getMetaData();
int count = md.getColumnCount();
columnNames = new ArrayList();
for (int x = 0; x < count; x++) {
columnTypes.add(md.getColumnTypeName(x + 1));
// log.debug(md.getColumnTypeName(x+1));
columnNames.add(md.getColumnName(x + 1));
}
} catch (SQLException sqlex) {
log.error("求列名表出错：" + sqlex.getMessage());
}
return columnNames;
}

private static void showmap(HashMap mp) {
Iterator it = mp.keySet().iterator();
while (it.hasNext()) {
String key = (String) it.next();
System.out.print(key + "=" + mp.get(key) + " ");
}
System.out.println();
}
//public static void main(String[] args) {
// final CommDAO studdao = (CommDAO)
// ContextConfig.ctx.getBean("CommDAOProxy");
// TableList tb = studdao.getTableList("select * from student",
// "1", 2, TableList.PAGE_TXT);
//
// ArrayList rows = tb.getRows();
//
// for(int i =0;i < rows.size(); i ++) {
// showmap((HashMap) rows.get(i));
// }
// System.out.println(tb.getHtmlPageCtl());
//
//}

}

[tk1984]

自己研究下！不懂在问！

[cdwei80]

顶一下， 以前自己看了一段时间的hibernate, 现在这个公司都不用， 郁闷呀。。。

[xinyaoxp]

顶一下，我也是现在还没有搞清楚

[zhaogz2008]

不会吧？怎么这么多代码呀？
为什么要写这么多代码呢？
10行代码就足够了呀！

[javaprogramlover]

有没有不用写代码，而可以直接配置的呢？？