腾讯云·社区
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配置文件中的<resultMap>标签相对应。
我们在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属性的值需要与接口方法的名称相对应。
本文参与腾讯云自媒体分享计划，欢迎正在阅读的你也加入，一起分享。
相关文章
扫码关注云+社区
领取腾讯云代金券
我来说两句