hibernate操作视图

三张表的SQL：

CREATE TABLE TB_COURSE
(
    ID INTEGER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE TB_STUDENT
(
    ID INTEGER PRIMARY KEY,
    NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE TB_STUDENT_COURSE
(
    STUDENT_ID INTEGER NOT NULL REFERENCES TB_STUDENT(ID),
    COURSE_ID INTEGER NOT NULL REFERENCES TB_COURSE(ID),
    PRIMARY KEY(STUDENT_ID, COURSE_ID)
);

创建视图的SQL：

CREATE OR REPLACE VIEW VW_STUDENT_COURSE(STUDENT_ID, STUDENT_NAME, COURSE_ID, COURSE_NAME) AS SELECT T1.ID, T1.NAME, T2.ID, T2.NAME
FROM TB_STUDENT T1, TB_COURSE T2, TB_STUDENT_COURSE T3
WHERE T1.ID = T3.STUDENT_ID AND T2.ID = T3.COURSE_ID;

如果用户没有权限，则用DBA登陆后，给用户授权：

SQL> CONN SYSTEM/MANAGER
已连接。
SQL> GRANT CREATE VIEW TO HIBERNATE;

授权成功。

java代码。先要创建一个对应视图字段的javabean，然后创建组合的javabean，而且都要实现序列化：

package com.view.pojo;

import java.io.Serializable;

public class StudentCourseId implements Serializable {
	private static final long serialVersionUID = 1L;
	private int studentId;
	private String studentName;
	private int courseId;
	private String courseName;

	public int getStudentId() {
		return studentId;
	}

	public void setStudentId(int studentId) {
		this.studentId = studentId;
	}

	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

	public int getCourseId() {
		return courseId;
	}

	public void setCourseId(int courseId) {
		this.courseId = courseId;
	}

	public String getCourseName() {
		return courseName;
	}

	public void setCourseName(String courseName) {
		this.courseName = courseName;
	}

	@Override
	public String toString() {
		return "StudentCourseId [studentId=" + studentId + ", studentName=" + studentName + ", courseId=" + courseId + ", courseName=" + courseName + "]";
	}
}

package com.view.pojo;

import java.io.Serializable;

public class StudentCourseView implements Serializable {
	private static final long serialVersionUID = 1L;
	private StudentCourseId id;

	public StudentCourseId getId() {
		return id;
	}

	public void setId(StudentCourseId id) {
		this.id = id;
	}

}

StudentCourseView.hbm.xml：

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
      "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
          "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.view.pojo">

	<class name="StudentCourseView" table="vw_student_course">
		<composite-id name="id" class="StudentCourseId">
			<key-property name="studentId" column="student_id" type="integer" />
			<key-property name="courseId" column="course_id" type="integer" />
			<key-property name="studentName" column="student_name" type="string" />
			<key-property name="courseName" column="course_name" type="string" />
		</composite-id>
	</class>

</hibernate-mapping>

测试查询：

package com.view.test;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.view.pojo.StudentCourseView;

import junit.framework.TestCase;

public class TestView extends TestCase {
	private Session session = null;

	private static final String cfgName = "/oracle.hibernate.cfg.xml";

	private static final SessionFactory factory = new Configuration().configure(cfgName).buildSessionFactory();

	private Transaction tran = null;

	@Override
	protected void setUp() throws Exception {
		session = factory.openSession();
		tran = session.beginTransaction();
	}

	public void testSelect() {
		String hql = "from StudentCourseView v where v.id.studentId = ? and v.id.courseId = ?";
		Query query = session.createQuery(hql);
		query.setInteger(0, 55);
		query.setInteger(1, 41);
		List<StudentCourseView> scList = query.list();
		for (StudentCourseView sc : scList) {
			System.out.print(sc.getId());
		}
	}

	@Override
	protected void tearDown() throws Exception {
		tran = null;
		session.close();
		session = null;
	}

}

oracle.hibernate.cfg.xml：

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

	<session-factory>

		<!-- Database connection settings -->
		<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
		<property name="connection.url">jdbc:oracle:thin:@localhost:1521:ORCL</property>
		<property name="connection.username">HIBERNATE</property>
		<property name="connection.password">HIBERNATE</property>

		<property name="connection.pool_size">1</property>

		<property name="dialect">org.hibernate.dialect.Oracle10gDialect</property>

		<property name="show_sql">true</property>

		<property name="hibernate.show_sql">true </property>
		
		<property name="jdbc.fetch_size">50</property>
		
		<mapping resource="com/view/pojo/StudentCourseView.hbm.xml"/>

	</session-factory>

</hibernate-configuration>

测试结果：

Hibernate: select studentcou0_.student_id as student1_0_, studentcou0_.course_id as course2_0_, studentcou0_.student_name as student3_0_, studentcou0_.course_name as course4_0_ from vw_student_course studentcou0_ where studentcou0_.student_id=? and studentcou0_.course_id=?
StudentCourseId [studentId=55, studentName=张飞, courseId=41, courseName=物理]