专栏首页程序猿的大杂烩Mybatis在接口上使用注解配置SQL语句以及接口与xml一起使用

Mybatis在接口上使用注解配置SQL语句以及接口与xml一起使用

在接口上使用注解配置SQL语句

MyBatis对于大部分的基于XML的映射器元素(包括<select>,<update>)提供了对应的基于注解的配置项。然而在某些情况下,基于注解配置 还不能支持基于XML的一些元素。MyBatis提供了多种注解来支持不同类型的语句(statement)如SELECT,INSERT,UPDATE,DELETE。下面我们通过一个小demo来简单演示一下这些基本注解的使用方式:

我现在有一张student表,表格结构如下:

首先编写表格的字段封装类,代码如下:

package org.zero01.pojo;

public class Student {

    private int sid;
    private String sname;
    private int age;
    private String sex;
    private String address;
    ... getter setter 略 ...
}

然后需要写一个接口,在该接口的方法上配置注解,注解的名称基本都能自注释了,我这里就不赘述它们的作用了。代码如下:

package org.zero01.mapper;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.zero01.pojo.Student;

import java.util.List;

public interface StudentMapper {

    @Insert("insert into student(sname,age,sex,address) values(#{sname},#{age},#{sex},#{address})")
    public int insertStu(Student student);

    @Delete("delete from student where sid=#{0}")
    public int delStu(int sid);

    @Select("select * from student where sid=#{0}")
    public Student selectById(int sid);

    @Select("select * from student")
    public List<Student> selectAll();

    @Select("select * from student limit #{param1},#{param2}")
    public List<Student> selectByLimit(int startRow, int endRow);

    @Update("update student set sname=#{sname},age=#{age},sex=#{sex},address=#{address} where sid=#{sid}")
    public int updateStu(Student student);

}

mybatis配置文件内容如下:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias type="org.zero01.pojo.Student" alias="Student"/>
        <typeAlias type="org.zero01.pojo.StudentLog" alias="StudentLog"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///school"/>
                <property name="username" value="root"/>
                <property name="password" value="your_password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- 通过包名引入接口 -->
        <mapper class="org.zero01.mapper.StudentMapper"/>
    </mappers>
</configuration>

编写一个测试类进行测试,代码如下:

package org.zero01.test;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.zero01.student.Student;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestMyBatis {

    private SqlSession sqlSession;
    private StudentMapper studentMapper;

    // 加载资源
    @Before
    public void testStart() throws IOException {
        // 配置文件路径
        String confPath = "mybatis-config.xml";
        // 读取配置文件得到输入流
        InputStream inputStream = Resources.getResourceAsStream(confPath);
        // 创建sql Session工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 建立与数据库的会话
        sqlSession = sqlSessionFactory.openSession();
        // 得到的是一个动态代理类
        studentMapper = sqlSession.getMapper(StudentMapper.class);
    }

    @Test
    public void testInsertStu() {
        Student student = new Student();
        student.setSname("Milen");
        student.setAge(20);
        student.setSex("女");
        student.setAddress("深圳");
        int result = studentMapper.insertStu(student);
        Assert.assertNotEquals(result, 0);
        sqlSession.commit();
    }

    @Test
    public void testDelStu() {
        int result = studentMapper.delStu(23);
        Assert.assertNotEquals(result, 0);
        sqlSession.commit();
    }

    @Test
    public void testSelectById() {
        Student student = studentMapper.selectById(2);
        Assert.assertNotNull(student);
        JSONObject stuJSON = new JSONObject(student);
        Assert.assertNotNull(stuJSON);
        System.out.println(stuJSON);
    }

    @Test
    public void testSelectAll() {
        List<Student> students = studentMapper.selectAll();
        Assert.assertNotEquals(students.size(), 0);
        Assert.assertNotNull(students);
        System.out.println("id\tsname\tage\tsex\taddress");
        for (Student student : students) {
            System.out.print(student.getSid() + "\t");
            System.out.print(student.getSname() + "\t");
            System.out.print(student.getAge() + "\t");
            System.out.print(student.getSex() + "\t");
            System.out.print(student.getAddress() + "\n");
        }
    }

    @Test
    public void testSelectByLimit() {
        List<Student> students = studentMapper.selectByLimit(0, 5);
        Assert.assertNotEquals(students.size(), 0);
        Assert.assertNotNull(students);
        System.out.println("id\tsname\tage\tsex\taddress");
        for (Student student : students) {
            System.out.print(student.getSid() + "\t");
            System.out.print(student.getSname() + "\t");
            System.out.print(student.getAge() + "\t");
            System.out.print(student.getSex() + "\t");
            System.out.print(student.getAddress() + "\n");
        }
    }

    @Test
    public void testUpdateStu() {
        Student student = new Student();
        student.setSid(7);
        student.setSname("Mkans");
        student.setAge(23);
        student.setSex("男");
        student.setAddress("湖南");
        int result = studentMapper.updateStu(student);
        Assert.assertNotEquals(result, 0);
        sqlSession.commit();
    }

    // 关闭资源
    @After
    public void testEnd() {
        if (sqlSession != null) {
            // 结束与数据库的会话
            sqlSession.close();
        }
    }
}

结果映射

除了基本的sql语句的配置外,我们还可以通过注解配置结果映射,如下示例:

@Select("select * from student")
@Results({
        @Result(id = true, property = "sid",column = "sid"),
        @Result(property = "sname",column = "sname"),
        @Result(property = "age",column = "age"),
        @Result(property = "sex",column = "sex"),
        @Result(property = "address",column = "address")
})
public List<Student> selectAll();

注:@Results注解与XML配置文件中的&lt;resultMap&gt;标签相对应。

我们在XML配置文件中可以配置一对多的连接查询,但是需要通过标签设置结果集与字段的映射关系。在注解里我们没法这么做,因为没有对应的注解支持。但是我们可以先在XML配置好映射关系,然后通过@ResultMap注解来引用它。如下示例:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.zero01.mapper.StudentMapper">
    <resultMap id="stuMap" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <association property="studentLog" resultMap="stuLogMap"/>
    </resultMap>
    <resultMap id="stuLogMap" type="StudentLog">
        <id property="log_id" column="log_id"/>
        <result property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <result property="operation_type" column="operation_type"/>
        <result property="log_time" column="log_time"/>
    </resultMap>
</mapper>

注解配置内容如下:

@Select("select * from student stu inner join studentlog stulog on  stu.`sid`=stulog.`sid`")
@ResultMap("org.zero01.mapper.StudentMapper.stuMap")  // 引用XML里配置的映射器
public List<Student> selectInnerLog();

接口与xml一起使用

通常情况下我们都是将接口与XML配置文件混合使用,这样比纯XML或者纯注解的方式要简单一些。

将接口代码的注解删除,修改如下:

package org.zero01.mapper;

import org.zero01.pojo.Student;

import java.util.List;

public interface StudentMapper {

    public int insertStu(Student student);

    public int delStu(int sid);

    public Student selectById(int sid);

    public List<Student> selectAll();

    public List<Student> selectByLimit(int startRow, int endRow);

    public int updateStu(Student student);

    public List<Student> selectInnerLog();

}

然后在mybatis配置文件中,加入如下内容:

<mappers>
    <mapper class="org.zero01.mapper.StudentMapper"/>
    <mapper resource="org/zero01/mapper/StudentMapper.xml"/>
</mappers>

新增的StudentMapper.xml文件内容如下:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.zero01.mapper.StudentMapper">
    <resultMap id="stuMap" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <association property="studentLog" resultMap="stuLogMap"/>
    </resultMap>
    <resultMap id="stuLogMap" type="StudentLog">
        <id property="log_id" column="log_id"/>
        <result property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="age" column="age"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <result property="operation_type" column="operation_type"/>
        <result property="log_time" column="log_time"/>
    </resultMap>
    <select id="selectInnerLog" resultMap="stuMap">
        select * from student stu inner join studentlog stulog on  stu.`sid`=stulog.`sid`
    </select>

    <insert id="insertStu" parameterType="Student">
        insert into student(sname,age,sex,address) values(#{sname},#{age},#{sex},#{address})
    </insert>
    <delete id="delStu" parameterType="int">
        delete from student where sid=#{0}
    </delete>
    <select id="selectById" parameterType="int" resultType="Student">
        select * from student where sid=#{0}
    </select>
    <select id="selectAll" resultType="Student">
        select * from student
    </select>
    <update id="updateStu" parameterType="Student">
        update student set sname=#{sname},age=#{age},sex=#{sex},address=#{address} where sid=#{sid}
    </update>
</mapper>

注:标签id属性的值需要与接口方法的名称相对应。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • ML简介与CSS3样式表

    XML是由W3C于1998年2月发布的一种可扩标记语言,是一种用于标记数据文件使其具有结构性的标记语言,它被设计用来传输和存储数据。在电子计算机中,标记指计算机...

    端碗吹水
  • Spring对JDBC的模板支持——JdbcTemplate

    Spring的JdbcTemplate是一个对JDBC的模板封装,它提供了一套JDBC的模板,能让我们写持久层代码时减少多余的代码,简化JDBC代码,使代码看起...

    端碗吹水
  • MyBatis结果集映射

    1.typeAliases标签,该标签用于配置全局的别名,配置别名后则不再需要写全名,在映射文件中只需要写配置的别名即可,例如:

    端碗吹水
  • 从锅炉工到AI专家(4)

    俺踏月色而来
  • 010.多线程-线程间通信

    安全问题: 当线程在读取资源的过程中,写线程操作了资源, 导致读线程读取的数据,一部分是写之前的数据,一部分是写之后的数据。

    qubianzhong
  • [docker]Tomcat安装及配置访问权限

    8.开始修改配置文件 因为容器不带vi文件编辑,需要将其cp至挂载的数据卷中 在宿主机进行修改再将其覆盖到容器中去 需要修改的文件分别为:

    贰叁壹小窝
  • 聊聊sentinel的SentinelGatewayFilter

    Sentinel-1.6.2/sentinel-adapter/sentinel-spring-cloud-gateway-adapter/src/main/j...

    codecraft
  • 聊聊sentinel的SentinelGatewayFilter

    Sentinel-1.6.2/sentinel-adapter/sentinel-spring-cloud-gateway-adapter/src/main/j...

    codecraft
  • 009.MongoDB分片群集部署

    shard:每个分片是分片数据的子集。从MongoDB 3.6开始,必须将分片部署为副本集。

    木二
  • [NewLife.XCode]扩展属性(替代多表关联Join提升性能)

    NewLife.XCode是一个有10多年历史的开源数据中间件,支持nfx/netstandard,由新生命团队(2002~2019)开发完成并维护至今,以下简...

    大石头

扫码关注云+社区

领取腾讯云代金券