事务提交与不同数据库的自增方式

事务自动提交

　　手动提交:

//Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devOracle");
        SqlSession session = sessionFactory.openSession();

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        Student student=new Student(6,"xx006",105,"xx");


        studentMapper.addStudent(student);

        session.commit();//手动提交
        session.close();

　　自动提交:

　　　　　//Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devOracle");
        SqlSession session = sessionFactory.openSession(true);//设置自动提交

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        Student student=new Student(6,"xx006",105,"xx");


        System.out.println(student);
        studentMapper.addStudent(student);

       // session.commit();//手动提交
        session.close();

 自增问题

　　a.mysql支持自增

创建自增表

create table student1(
    stuno int(4) primary key auto_increment,
    stuname varchar(10),
    stuage int(4),
    graname varchar(10)
);

src\org\myy\mapper\studentMapper.xml

自增写入:只需要配置两个属性即可:useGeneratedKeys="true" keyProperty="stuNo"

    <insert id="addStudent" parameterType="Student" databaseId="mysql"
            useGeneratedKeys="true" keyProperty="stuNo">
        insert into student1(stuname,stuage,graname) values(#{stuName},#{stuAge},#{graName})
    </insert>

src\org\myy\test\Test.java

        //Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devMysql");
        SqlSession session = sessionFactory.openSession(true);//设置自动提交

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        Student student=new Student(null,"xx006",105,"xx");

        /*int result=studentMapper.addStudent(student);
        System.out.println("增加了"+result+"个学生");*/

        System.out.println(student);
        studentMapper.addStudent(student);
        Integer stuno=student.getStuNo();
        System.out.println(stuno);//自增写入:只需要配置两个属性即可:useGeneratedKeys="true" keyProperty="stuNo"
        System.out.println(student);
       // session.commit();//手动提交
        session.close();        

 

b.oracle不支持自增：通过序列模拟实现
创建自增序列

create sequence myseq
increment by 1
start with 1;

 

序列自带的两个属性:
nextval:序列中下一个值
currval:当前值

src\org\myy\mapper\studentMapper.xml

//推荐

<insert id="addStudent" parameterType="org.myy.entity.Student" databaseId="oracle">
        <selectKey keyProperty="stuNo" resultType="Integer" order="BEFORE">
            select myseq.nextval from dual
        </selectKey>
        insert into student1(stuno,stuname,stuage,graname) values(#{stuNo},#{stuName},#{stuAge},#{graName})
</insert>

 通过<insert>的子标签<selectKey>实现:在<selectKey>中查询下一个序列(自增后的值)，再将此值传入keyProperty="stuNo"属性，最后在真正执行时使用该属性值

或者

<insert id="addStudent" parameterType="Student" databaseId="oracle">
        <selectKey keyProperty="stuNo" resultType="Integer" order="AFTER"> 
            select myseq.currval from dual
        </selectKey>
        insert into student1(stuno,stuname,stuage,graname)
        values(myseq.nextval,#{stuName},#{stuAge},#{graName})
</insert>

 

src\org\myy\test\Test.java

　　　　　//Connection - SqlSession操作Mybatis
        //conf.xml->reader
        Reader reader = Resources.getResourceAsReader("conf.xml");
        //reader->sqlSession

        //可以通过build的第二参数 指定数据库环境
        SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader,"devOracle");
        SqlSession session = sessionFactory.openSession(true);//设置自动提交

        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        Student student=new Student(null,"xx006",105,"xx");


        System.out.println(student);
        studentMapper.addStudent(student);
        Integer stuno=student.getStuNo();
        System.out.println(stuno);
        System.out.println(student);
       // session.commit();//手动提交
        session.close();