hibernate关联查询方法

今天做了一个两张表的查询 要求查询文章分类及该分类文章的数目
hibernate刚学，我的解决方案如下，不知道大家有什么好的方法，请赐教

public List<Sort> getAllSort() {
//		String hql = "from Sort sort_ order by sort_.id desc";
//		return (List<Sort>)this.getHibernateTemplate().find(hql);
		
		Session session =  HibernateUtil.getSession();
		
		Transaction tx = session.beginTransaction();
		Connection conn = session.connection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT sort_.id_,sort_.name_,sort_.tag_,COUNT(sort_id) as counts FROM note_,sort_ WHERE sort_id=sort_.id_ GROUP BY sort_id ORDER BY counts desc");
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		} 		
		tx.commit();
		
		List<Sort> list = new ArrayList<Sort>();
		Sort sort = null;
		try {
			while(rs.next()){
				sort = new Sort();
				sort.setId(rs.getString(1));
				sort.setName(rs.getString(2));
				sort.setTag(rs.getInt(3));
				sort.setCounts(rs.getInt(4));
				list.add(sort);
				SysLog.loger(rs.getString(1));
				SysLog.loger(rs.getString(2));
				SysLog.loger(rs.getInt(3));
				SysLog.loger(rs.getInt(4));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

======================================================
table:note_ (表示文章列表)
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id_         | varchar(255) | NO   | PRI |         |       |
| title_      | varchar(50)  | YES  |     | NULL    |       |
| content_    | text         | YES  |     | NULL    |       |
| createTime_ | datetime     | YES  |     | NULL    |       |
| visitCount_ | int(11)      | YES  |     | NULL    |       |
| sort_id     | varchar(255) | YES  | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

table:sort_(表示文章的类别)
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id_   | varchar(255) | NO   | PRI |         |       |
| name_ | varchar(50)  | YES  |     | NULL    |       |
| tag_  | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Note.java
private String id;
private String title;
private Sort sort;
private String content;
private Date createTime;
private int visitCount;
...getter,setter

Sort.java
private String id;
private String name;
private Set Note;
private int tag;
private int counts;
...getter,setter

Note.hbm.xml
<id name="id" type="string" column="id_">
<generator class="uuid"></generator>
</id>
<property name="title" type="string" column="title_" length="50" />
<property name="content" type="string" column="content_" length="1024" />
<property name="createTime" type="java.util.Date" column="createTime_" length="50" />
<property name="visitCount" type="int" column="visitCount_" length="50" />
<many-to-one name="sort" column="sort_id"/>

Sort.hbm.xml
<id name="id" type="string" column="id_">
<generator class="uuid"></generator>
</id>
<property name="name" type="string" column="name_" length="50" />
<property name="tag" column="tag_" length="50" />
<set name="note" cascade="save-update">
<key column="sort_id"></key>
<one-to-many class="Note"/>
</set>

评论

2 楼 xiaoban0514 2009-08-06  

没有关系，，就是因为不会用oo的思想去查询所有旧出此下策了。。。

1 楼 zuzg 2009-08-06  

这个做法和hibernate有关系吗？