hibernate连oracle

原创

feier7501 2023-04-26 19:36:36 博主文章分类：hibernate ©著作权

文章标签 hibernate xml List 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者feier7501的原创作品，请联系作者获取转载授权，否则将追究法律责任

创建表：

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

Animal：

package com.animal.pojo;

public class Animal {
	private Integer id;
	
	private String name;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}

主键要自增长，方式一：

创建序列：

CREATE SEQUENCE SQ_ANIMAL
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

Animal.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.animal.pojo">

	<class name="Animal" table="tb_animal">
		<id name="id" column="id">
			<generator class="sequence">
				<param name="sequence">SQ_ANIMAL</param>
			</generator>
		</id>
		<property name="name" column="name" type="string" />

	</class>

	<query name="getAnimalById">
		from Animal where id=?
	</query>

	<sql-query name="getAnimalByName">
		select * from tb_animal where name=?
	</sql-query>

</hibernate-mapping>

hbm中的sequence要和SQL语句中的一样。

不需要创建触发器。

第二种方式：

创建名为HIBERNATE_SEQUENCE的序列：

CREATE SEQUENCE HIBERNATE_SEQUENCE
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

创建触发器：

CREATE OR REPLACE TRIGGER TR_ANIMAL BEFORE INSERT ON TB_ANIMAL FOR EACH ROW
BEGIN
    SELECT HIBERNATE_SEQUENCE.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

Animal.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.animal.pojo">

	<class name="Animal" table="tb_animal">
		<id name="id" column="id">
			<generator class="native" />
		</id>
		<property name="name" column="name" type="string" />

	</class>

	<query name="getAnimalById">
		from Animal where id=?
	</query>

	<sql-query name="getAnimalByName">
		select * from tb_animal where name=?
	</sql-query>

</hibernate-mapping>

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">ANIMAL</property>
		<property name="connection.password">ANIMAL</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/animal/pojo/Animal.hbm.xml"/>

	</session-factory>

</hibernate-configuration>

注意：

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

就是因为上面这个地方没改成Oracle10gDialect，我第一次测试就一直报错，比如：

Caused by: java.sql.SQLException: ORA-08002: 序列 HIBERNATE_SEQUENCE.CURRVAL 尚未在此会话中定义

测试代码：

package com.animal.test;

import java.util.List;
import java.util.Random;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;

import com.animal.pojo.Animal;

import junit.framework.TestCase;

public class TestAnimal extends TestCase {
	private Session session = null;
	
//	private static final String mysqlCfgName = "/mysql.hibernate.cfg.xml";
	
	private static final String oracleCfgName = "/oracle.hibernate.cfg.xml";
	
	private static final SessionFactory factory = new Configuration().configure(oracleCfgName).buildSessionFactory();

	private Transaction tran = null;

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

	public void testInsertAnimal() {
		for (int i = 0; i < 10; i++) {
			Animal animal = new Animal();
			animal.setName("animal_" + i);
			session.save(animal);
		}
		tran.commit();
	}

	public void testHQL() {
		// from后面是对象，不是表名
		String hql = "from Animal where id=?";
		Query query = session.createQuery(hql);
		query.setInteger(0, 1);
		List<Animal> list = query.list();
		out(list);
	}

	public void testCriteria() {
		Criteria criteria = session.createCriteria(Animal.class);
		criteria.add(Restrictions.eq("id", 1));
		List<Animal> list = criteria.list();
		out(list);
	}

	public void testDetachedCriteria() {
		DetachedCriteria dc = DetachedCriteria.forClass(Animal.class);
		dc.add(Restrictions.eq("id", 1));
		Criteria criteria = dc.getExecutableCriteria(session);
		List<Animal> list = criteria.list();
		out(list);
	}

	public void testSQLQuery() {
		// from后面是表名，不是对象
		String queryString = "select * from tb_animal where id=?";
		SQLQuery query = session.createSQLQuery(queryString);
		query.addEntity(Animal.class);
		query.setInteger(0, 1);
		List<Animal> list = query.list();
		out(list);
	}

	public void testNamedQuery() {
		Query query = session.getNamedQuery("getAnimalById");
		query.setInteger(0, 1);
		List<Animal> list = query.list();
		out(list);
	}

	public void testNamedSQLQuery() {
		Query query = session.getNamedQuery("getAnimalByName");
		query.setString(0, "animal_1");
		List<?> list = query.list();
		for (int i = 0; i < list.size(); i++) {
			Object[] objs = (Object[]) list.get(i);
			System.out.println(objs[0] + ", " + objs[1]);
		}
	}
	
	public void testUpdate() {
		Random random = new Random();
		Animal animal = new Animal();
		animal.setId(1);
		animal.setName("u_animal_" + random.nextInt(10));
		session.saveOrUpdate(animal);
		tran.commit();
	}
	
	public void testDelete() {
		Animal animal = new Animal();
		animal.setId(10);
		session.delete(animal);
		tran.commit();
	}

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

	private void out(List<Animal> list) {
		if (list == null) {
			return;
		}
		for (Animal animal : list) {
			System.out.println(animal.getId() + ", " + animal.getName());
		}
	}

}

package com.animal.test;

import junit.framework.Test;
import junit.framework.TestSuite;

public class TestAll {
	public static Test suite() {
		TestSuite testSuite = new TestSuite();
		testSuite.addTestSuite(TestAnimal.class);
		return testSuite;
	}
}