Mybatis operation oracle Database primary key auto-increment (trigger)

For the same student table, for mysql, sqlserver, and oracle, how do they create primary keys? in mysql, createtableStudent (Student_IDint (6) NOTNULLPRIMARYKEYAUTO_INCREMENT, Student_Namevarchar (10) NOTNULL, student_Ageint (2) NOTNULL); ins

First, let's look at how to create a primary key for the same student table, mysql, SQL server, and oracle. create table Student (Student_ID int (6) in mysql) not null primary key AUTO_INCREMENT, Student_Name varchar (10) not null, Student_Age int (2) not null); ins

First, let's look at how to create primary keys for the same student table, mysql, SQL server, and oracle.

In 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);

In 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);

In oracle

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

If oracle wants to set primary key auto-increment, it needs to create a sequence

CREATE SEQUENCE student_sequence INCREMENT BY 1NOMAXVALUENOCYCLECACHE 10;insert into Student values(student_sequence.nextval,'aa',20);

If a trigger is used, it is simpler.

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

At this time, the trigger will help you insert the id when inserting

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

At this point, how to create an auto-increment primary key in a table in mysql, SQL server, and oracle has been completed. It can be seen that the auto-increment of the primary key of oracle is more complicated than that of mysql and SQL Server. After configuring the primary key of mysql and SQL Server, the fields and values can correspond One to One during insertion, the database will complete what you want to do, but since there is more sequence in oracle, How Does oracle implement auto-increment of primary keys? See the following

The first is the configuration file of the mybatis framework.

Jdbc. properties File

username=gopassword=gourl=jdbc:oracle:thin:@127.0.0.1:1521:orcldriver=oracle.jdbc.driver.OracleDriver

Mybatis-config.xml files

The corresponding entity class Student does not change. Refer to the hibernate operation for oracle Database primary key auto-increment.

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

Corresponding Student. xml file

 
      
   Insert into student (student_id, student_name, student_age) values (# {student_id}, # {student_name}, # {student_age })
  
  Select * from student where student_id =#{ student_id}
  Delete from student where student_id =#{ student_id}
  
   
Update student set student_name =#{ student_name}, student_age =#{ student_age} where student_id =#{ student_id}
  
  Select * from student
 

Test class

Public class TestMybatis {@ Testpublic void testAdd () {// create a session for the original configuration file and try {InputStream is = Resources. getResourceAsStream ("mybatis-config.xml"); SqlSessionFactory = 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 () ;}@ Testpublic 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 finished tione. printStackTrace () ;}/ * @ Testpublic 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 () ;}} */@ Testpublic 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) ;}@testpublic void testList () {SqlSession session = null; try {session = MyBatisUtil. createSession (); List
 
  
Us = session. selectList (Student. class. getName () + ". list ", null); System. out. println (us. size ();} finally {MyBatisUtil. closeSession (session );}}}
 

Factory class 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();}}

Summary: The core of mybatis + oracle primary key auto-increment implementation is to find a sequence value from the sequence and insert it into the corresponding table in two steps.

Select student_sequence.nextval from dual first

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

Compared with hibernate and mybatis, oracle primary key auto-increment requires two steps. In hibernate, whether it is an annotation version or a non-Annotation Version, you must map the id field to the created sequence name.

Supplement: The jar package imported by the mybatis framework is the mybatis-3.3.2.jar version, the junit is the junit-4.5.jar, And the jar package ojdbc14.jar connecting oracle, where MyBatisUtil. java files are actually a simple factory creation mode. If you are interested, you can look at the design patterns book.

For me, I still like to use SQL statements. I feel more primitive and better aware of what I am doing. Of course, the lower the layer, the higher the efficiency, the higher the mybatis, but now I still use hibernate a lot. Of course it's just my family. You are welcome to discuss related issues with other friends.

If there are any mistakes or suggestions in the article, you are welcome to share them and make progress together.