mybatis操作oracle数据库主键自增(触发器)

上一篇文章介绍了hibernate 实现oracle主机自增的机制，本篇文章将研究mybatis 实现oracle主键自增的机制

首先我们看对于同一张student表，对于mysql，sql server，oracle中它们都是怎样创建主键的

在mysql中

create table Student(
 Student_ID  int(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
 Student_Name varchar(10) NOT NULL,
 Student_Age int(2) NOT NULL
);

insert into student(student_name,student_age) values('zhangsan',20);

在sql server中

create table Student(
 Student_ID  int primary key identity(1,1),
 Student_Name varchar2(10) NOT NULL,
 Student_Age number(2) NOT NULL
);
insert into student(student_name,student_age) values('zhangsan',20);

在oracle中

create table Student(
 Student_ID  number(6) NOT NULL PRIMARY KEY,
 Student_Name varchar2(10) NOT NULL,
 Student_Age number(2) NOT NULL
);

而oracle如果想设置主键自增长，则需要创建序列

CREATE SEQUENCE student_sequence 
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 10;

insert into Student values(student_sequence.nextval,'aa',20);

如果使用了触发器的话，就更简单了

create or replace trigger student_trigger
  before insert on student  
  for each row
begin
  select student_sequence.nextval into :new.student_id from dual;
end student_trigger;
/

此时插入的时候触发器会帮你插入id

insert into student(student_name,student_age) values('wangwu',20);

至此，mysql，sql server，oracle中怎样创建表中的自增长主键都已完成。看一看出oracle的主键自增较mysql和sql sever要复杂些，mysql，sqlserver配置好主键之后，插入时，字段和值一一对应即可，数据库就会完成你想做的，但是在oracle由于多了序列的概念，那么oracle怎样实现主键自增呢？且看下文

首先是mybatis框架的配置文件

jdbc.properties文件

username=go
password=go
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
driver=oracle.jdbc.driver.OracleDriver

mybatis-config.xml文件

<configuration>
	<properties resource="jdbc.properties"/>
	<typeAliases>
		<package name="com.bean"/>
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	<!-- 将mapper文件加入到配置文件中 -->
	<mappers>
		<mapper resource="com/bean/Student.xml" />
	</mappers>
</configuration>

对应的实体类Student无变化，参考hibernate操作oracle数据库 主键自增

http://blog.youkuaiyun.com/thepeakofmountain/article/details/17173715

对应的Student.xml文件

<mapper namespace="com.bean.Student">
	<insert id="add" parameterType="Student">
    <!--
<selectKey keyProperty="student_id" resultType="int" order="BEFORE">
      select student_sequence.nextval from dual
    </selectKey>
如果未使用触发器，请保留该注释
--!>
     insert into student(student_id,student_name,student_age) values(#{student_id},#{student_name},#{student_age})
    </insert>
	
	<select id="load" parameterType="int" resultType="Student">
		select * from student where student_id=#{student_id}
	</select>

	<select id="delete" parameterType="int" resultType="int">
		delete from student where student_id=#{student_id}
	</select>
	
	<update id="update" parameterType="Student">
		update student set student_name=#{student_name},student_age=#{student_age} where student_id=#{student_id}
	</update>
	
	<select id="list" resultType="Student">
		select * from student
	</select>
</mapper>

测试类

public class TestMybatis {
	
	@Test
	public  void testAdd() {   //为原始的获取配置文件，自己创建session，一步一步走的
		try {
			InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
			SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);

			SqlSession session = factory.openSession();
			Student u = new Student();
			u.setStudent_name("sunwukong");
			u.setStudent_age(50);
			session.insert("com.bean.Student.add", u);
			session.commit();
			session.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void testUpdate(){
		SqlSession session = null;
		try {
			session = MyBatisUtil.createSession();
			Student stu = new Student();
			stu.setStudent_id(11);
			stu.setStudent_name("bajie");
			stu.setStudent_age(20);
			session.update(Student.class.getName()+".update", stu);
			session.commit();
			session.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	
	/*@Test
	public void testDelete() {
		SqlSession session= null;
		try {
			session = MyBatisUtil.createSession();
			session.delete(Student.class.getName()+".delete",105);
			session.commit();
			session.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}*/
	
	@Test
	public void testLoad() {
		SqlSession session = null;
		try{
			session = MyBatisUtil.createSession();
			Student u = (Student)session.selectOne(Student.class.getName()+".load", 11);
			System.out.println(u.getStudent_name());
		} finally {
			MyBatisUtil.closeSession(session);
		}
	}
	
	@Test
	public void testList() {
		SqlSession session = null;
		try{
			session = MyBatisUtil.createSession();
			List<Student> us = session.selectList(Student.class.getName()+".list", null);
			System.out.println(us.size());
		} finally {
			MyBatisUtil.closeSession(session);
		}
	}
}

工厂类MyBatisUtil

public class MyBatisUtil {
	public static SqlSessionFactory factory;
	static {
		try {
			InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
			factory = new SqlSessionFactoryBuilder().build(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static SqlSession createSession() {
		return factory.openSession();
	}
	
	public static void closeSession(SqlSession session) {
		if(session!=null) session.close();
	}
}

小结：mybatis+oracle主键自增实现的核心，就插入来说，就是先从序列中查找一个序列值，然后插入到对应的表中，也就是分两步走

先select student_sequence.nextval from dual

后insert into student(student_id,student_name,student_age) values(#{student_id},#{student_name},#{student_age})

比较hibernate和mybatis，实现oracle主键自增都是需要两步，而在hibernate中无论是注解版还是非注解版，都需要将id字段映射到创建的序列名上。

补充：mybatis框架导入的jar包为mybatis-3.3.2.jar版本，junit为junit-4.5.jar，连接oracle的jar包ojdbc14.jar，其中MyBatisUtil.java文件其实是一个创建简单工厂模式，如果有兴趣，可以看看设计模式方面的书

对于我来说，还是喜欢用sql语句，感觉更原始，更清楚的知道自己在干什么，当然越底层，效率的话，肯定是mybatis高一些，但是现在还是hibernate用的多吧，当然只是我一家之言，欢迎与各路朋友探讨相关问题。

如果文章有什么错误或者有什么建议，欢迎提出，大家共同交流，一起进步

文章转载请注明出处，请尊重知识产权