Mybatis implements the oracle primary key auto-increment mechanism tutorial, mybatisoracle

Mybatis implements the oracle primary key auto-increment mechanism tutorial, mybatisoracle

How to Implement oracle primary key auto-increment using mybatis

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

In 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 ('hangsan', 20 );

In 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 ('hangsan', 20 );

In 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

);

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

[SQL] view plain copy

Create sequence student_sequence

Increment by 1

NOMAXVALUE

NOCYCLE

CACHE 10;

Insert into Student values (student_sequence.nextval, 'A', 20 );

If a trigger is used, it is simpler.

[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;

/

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

[SQL] view plain copy

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

[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 files

[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>

<! -- Add the mapper file to the configuration file -->

<Mappers>

<Mapper resource = "com/bean/Student. xml"/>

</Mappers>

</Configuration>

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

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

Corresponding Student. xml file

[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>

If the trigger is not used, keep this comment

--!>

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>

Test class

[Java] view plain copy

Public class TestMybatis {

@ Test

Public void testAdd () {// create a session for the original configuration file.

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

}

}

}

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

}

}

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.