mybatis 实现oracle主键自增机制的教程
mybatis 实现oracle主键自增的机制教程
首先我们看对于同一张student表，对于mysql，sql server，oracle中它们都是怎样创建主键的
在mysql中
[sql] view plain copy
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中
[sql] view plain copy
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中
[sql] view plain copy
create table student(
student_id number(6) not null primary key,
student_name varchar2(10) not null,
student_age number(2) not null
);
而oracle如果想设置主键自增长，则需要创建序列
[sql] view plain copy
create sequence student_sequence
increment by 1
nomaxvalue
nocycle
cache 10;
insert into student values(student_sequence.nextval,'aa',20);
如果使用了触发器的话，就更简单了
[sql] view plain copy
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
[sql] view plain copy
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文件
[html] view plain copy
username=go
password=go
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
driver=oracle.jdbc.driver.oracledriver
mybatis-config.xml文件
[html] view plain copy
<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数据库 主键自增
https://blog.csdn.net/thepeakofmountain/article/details/17173715
对应的student.xml文件
[html] view plain copy
<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>
测试类
[java] view plain copy
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
[java] view plain copy
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用的多吧，当然只是我一家之言，欢迎与各路朋友探讨相关问题。