Home > Database > Mysql Tutorial > mybatis操作oracle数据库主键自增(触发器)

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

WBOY
Release: 2016-06-07 15:15:07
Original
1909 people have browsed it

首先我们看对于同一张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);ins

首先我们看对于同一张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);
Copy after login

在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);
Copy after login

在oracle中
create table Student(
 Student_ID  number(6) NOT NULL PRIMARY KEY,
 Student_Name varchar2(10) NOT NULL,
 Student_Age number(2) NOT NULL
);
Copy after login

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

CREATE SEQUENCE student_sequence 
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 10;

insert into Student values(student_sequence.nextval,'aa',20);
Copy after login
如果使用了触发器的话,就更简单了
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;
/
Copy after login
此时插入的时候触发器会帮你插入id
insert into student(student_name,student_age) values('wangwu',20);
Copy after login

至此,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
Copy after login

mybatis-config.xml文件

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

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

http://blog.csdn.net/thepeakofmountain/article/details/17173715

对应的Student.xml文件

<mapper namespace="com.bean.Student">
	<insert id="add" parametertype="Student">
    </insert></mapper>
Copy after login
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template