使用注解配置sql映射器

最新推荐文章于 2023-02-15 04:44:26 发布

chengwei9975

最新推荐文章于 2023-02-15 04:44:26 发布

阅读量251

点赞数

文章标签： java 数据库

原文链接：https://my.oschina.net/guowei11/blog/3075509

版权

在映射器Mapper接口上使用注解

映射语句

insert

package com.mybatis3.mappers;
public interface StudentMapper{
    @Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,ADDR_ID, PHONE)
    VALUES(#{studId},#{name},#{email},#{address.addrId},#{phone})")
    int insertStudent(Student student);
}

使用@insert注解的方法将会返回insert语句执行后影响的行数。

自动生成主键

mysql

在使用xml进行配置时知道可以配置自动生成主键的，使用注解的时候也是可以配置自动生成主键的。使用@options注解的userGeerateKeys和keyProperty属性可以让数据库产生auto_increment(自增长)列的值，然后将生成的值设置到输入参数对象的属性中即可。

接口:

@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{name},#{email},#{address.addrId},#{phone})")
@Options(useGeneratedKeys = true, keyProperty = "studId")
int insertStudent(Student student);

stud_id列值将会通过mysql数据库自动生成，并且生成的值将会被设置到student对象的studId属性中。

StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
mapper.insertStudent(student);
int studentId = student.getStudId();

oracle

上面使用的是auto_increment，但是oracle并不支持自增长，它使用的是序列(sequence)来产生主键的值。此时可以使用@Selectkey注解来为任意的sql语句指定主键值(下面的案例中假设已经存在stud_id_seq序列)。

@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE)
VALUES(#{name},#{email},#{address.addrId},#{phone})")
@SelectKey(statement="SELECT STUD_ID_SEQ.CURRVAL FROM DUAL",
keyProperty="studId", resultType=int.class, before=false)
int insertStudent(Student student);

update

可以使用update注解来定义一个update映射语句

接口：

@Update("UPDATE STUDENTS SET NAME=#{name}, EMAIL=#{email},
PHONE=#{phone} WHERE STUD_ID=#{studId}")
int updateStudent(Student student);

使用了@Update的 updateStudent()方法将会返回执行了 update 语句后影响的行数。

调用代码:

StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
int noOfRowsUpdated = mapper.updateStudent(student);

delete

可以使用@delete注解来定义一个车delete映射语句

接口：

@Delete("DELETE FROM STUDENTS WHERE STUD_ID=#{studId}")
int deleteStudent(int studId);

使用了@Delete 的 deleteStudent()方法将会返回执行了 update 语句后影响的行数。

select

我们可以使用@ Select 注解来定义一个 SELECT 映射语句。

package com.mybatis3.mappers;
public interface StudentMapper{
    @Select("SELECT STUD_ID AS STUDID, NAME, EMAIL, PHONE FROM
    STUDENTS WHERE STUD_ID=#{studId}")
    Student findStudentById(Integer studId);
}

为了将列名和 Student bean 属性名匹配，我们为 stud_id 起了一个 studId 的别名。如果返回了多行结果，将抛出 TooManyResultsException 异常。

结果映射

将查询的结果与bean联系起来有两种方式：

使用别名，取的别名与javaBean里面的名称一致
使用@results注解

使用@results注解

package com.mybatis3.mappers;
public interface StudentMapper{
    @Select("SELECT * FROM STUDENTS")
    @Results({
    @Result(id = true, column = "stud_id", property = "studId"),
    @Result(column = "name", property = "name"),
    @Result(column = "email", property = "email"),
    @Result(column = "addr_id", property = "address.addrId")
    })
    List<Student> findAllStudents();
}

@Results注解和映射器xml配置文件<resultMap>相对应，不过Mybatis3.2.2不能为@results注解赋予一个ID，所以不能像<resultMap>元素，不能在不同的映射语句中重用@Results声明，即使@Results注解完全一样。看下面的例子:

@Select("SELECT * FROM STUDENTS WHERE STUD_ID=#{studId}")
@Results({
@Result(id = true, column = "stud_id", property = "studId"),
@Result(column = "name", property = "name"),
@Result(column = "email", property = "email"),
@Result(column = "addr_id", property = "address.addrId")
})
Student findStudentById(int studId);

@Select("SELECT * FROM STUDENTS")
@Results({
@Result(id = true, column = "stud_id", property = "studId"),
@Result(column = "name", property = "name"),
@Result(column = "email", property = "email"),
@Result(column = "addr_id", property = "address.addrId")
})
List<Student> findAllStudents();

上面的两个方法中注解完全相同，但是必须重复配置，此时有一个解决办法，可以创建一个映射器Mapper文件，然后配置<resultMap>元素，然后使用@ResultMap注解引用这个<resultMap>

mapper配置文件:

<mapper namespace="com.mybatis3.mappers.StudentMapper">
    <resultMap type="Student" id="StudentResult">
        <id property="studId" column="stud_id" />
        <result property="name" column="name" />
        <result property="email" column="email" />
        <result property="phone" column="phone" />
    </resultMap>
</mapper>

然后在接口中使用@ResultMap引用名为StudnetResult的resultMap。

接口:

public interface StudentMapper{
    @Select("SELECT * FROM STUDENTS WHERE STUD_ID=#{studId}")
    @ResultMap("com.mybatis3.mappers.StudentMapper.StudentResult")
    Student findStudentById(int studId);
    
    @Select("SELECT * FROM STUDENTS")
    @ResultMap("com.mybatis3.mappers.StudentMapper.StudentResult")
    List<Student> findAllStudents();
}

一对一映射

使用@one注解来使用嵌套select语句加载一对一关联映射查询。下面的例子中使用@one注解获取学生(Student类，该类中有一个属性是address对象)以及地址(Address类)。

public interface StudentMapper{
    @Select("SELECT ADDR_ID AS ADDRID, STREET, CITY, STATE, ZIP, COUNTRY
    FROM ADDRESSES WHERE ADDR_ID=#{id}")
    Address findAddressById(int id);
        
    @Select("SELECT * FROM STUDENTS WHERE STUD_ID=#{studId} ")
    @Results({
        @Result(id = true, column = "stud_id", property = "studId"),
        @Result(column = "name", property = "name"),
        @Result(column = "email", property = "email"),
        @Result(property = "address", column = "addr_id",
        one = @One(select = "com.mybatis3.mappers.StudentMapper.findAddressById"))})
    Student selectStudentWithAddress(int studId);
}

这里使用@one注解的select属性来指定一个使用完全限定名的方法，该方法会返回一个Address对象，使用 column=”addr_id”,则 STUEDNTS 表中列 addr_id 的值将会作为输入参数传递给 findAddressById()方法。如果@One SELECT 查询返回了多行结果，则会抛出 TooManyResultsException 异常。

测试代码：

int studId = 1;
StudentMapper studentMapper =
sqlSession.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentWithAddress(studId);
System.out.println("Student :"+student);
System.out.println("Address :"+student.getAddress());

使用xml配置的时候可以使用嵌套结果ResultMap来加载一对一关联查询，但是没有相对应的注解支持，但是可以在映射配置文件中使用<resultMap>并且使用@ResultMap注解来引用它。

在StudentMapper.xml中配置<resultMap>

<mapper namespace="com.mybatis3.mappers.StudentMapper">
    <resultMap type="Address" id="AddressResult">
        <id property="addrId" column="addr_id" />
        <result property="street" column="street" />
        <result property="city" column="city" />
        <result property="state" column="state" />
        <result property="zip" column="zip" />
        <result property="country" column="country" />
    </resultMap>
    <resultMap type="Student" id="StudentWithAddressResult">
        <id property="studId" column="stud_id" />
        <result property="name" column="name" />
        <result property="email" column="email" />
        <association property="address" resultMap="AddressResult" />
    </resultMap>
</mapper>

接口：

public interface StudentMapper{
    @Select("select stud_id, name, email, a.addr_id, street, city,
    state, zip, country FROM students s left outer join addresses a
    on s.addr_id=a.addr_id where stud_id=#{studId} ")
    @ResultMap("com.mybatis3.mappers.StudentMapper.
    StudentWithAddressResult")
    Student selectStudentWithAddress(int id);
}

一对多映射

mybatis使用@Many注解用来使用嵌套select语句加载一对多关联查询。

下面的案例是获取一个讲师以及其授课列表信息:

public interface TutorMapper{
    @Select("select addr_id as addrId, street, city, state, zip,
    country from addresses where addr_id=#{id}")
    Address findAddressById(int id);
    @Select("select * from courses where tutor_id=#{tutorId}")
    @Results(
    {
        @Result(id = true, column = "course_id", property = "courseId"),
        @Result(column = "name", property = "name"),
        @Result(column = "description", property = "description"),
        @Result(column = "start_date" property = "startDate"),
        @Result(column = "end_date" property = "endDate")
    })
	List<Course> findCoursesByTutorId(int tutorId);
            
    @Select("SELECT tutor_id, name as tutor_name, email, addr_id
    FROM tutors where tutor_id=#{tutorId}")
    @Results(
    {
        @Result(id = true, column = "tutor_id", property = "tutorId"),
        @Result(column = "tutor_name", property = "name"),
        @Result(column = "email", property = "email"),
        @Result(property = "address", column = "addr_id",
        one = @One(select = " com.mybatis3.
        mappers.TutorMapper.findAddressById")),
    @Result(property = "courses", column = "tutor_id",
    many = @Many(select = "com.mybatis3.mappers.TutorMapper.
    findCoursesByTutorId"))
    })
    Tutor findTutorById(int tutorId);
}

这里我们使用了@Many 注解的 select 属性来指向一个完全限定名称的方法，该方法将返回一个 List<Course>对象。使用 column=”tutor_id”，TUTORS 表中的 tutor_id 列值将会作为输入参数传递给 findCoursesByTutorId()方法。

使用xml配置sql映射文件的时候可以通过~映射配置，使用嵌套结果ResultMap来加载一对多关联查询，但是没有相应的注解，剧场可以在映射器mapper文件中配置<resultMap>并且使用@resultMap注解来引用。

在TutorMapper.xml中配置<resultMap>

<mapper namespace="com.mybatis3.mappers.TutorMapper">
    <resultMap type="Address" id="AddressResult">
        <id property="addrId" column="addr_id" />
        <result property="street" column="street" />
        <result property="city" column="city" />
        <result property="state" column="state" />
        <result property="zip" column="zip" />
    <result property="country" column="country" />
    </resultMap>
    <resultMap type="Course" id="CourseResult">
        <id column="course_id" property="courseId" />
        <result column="name" property="name" />
        <result column="description" property="description" />
        <result column="start_date" property="startDate" />
        <result column="end_date" property="endDate" />
    </resultMap>
    <resultMap type="Tutor" id="TutorResult">
        <id column="tutor_id" property="tutorId" />
        <result column="tutor_name" property="name" />
        <result column="email" property="email" />
        <association property="address" resultMap="AddressResult" />
        <collection property="courses" resultMap="CourseResult" />
    </resultMap>
</mapper>

接口代码：

public interface TutorMapper{
    @Select("SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL,
    A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY, COURSE_ID, C.NAME,
    DESCRIPTION, START_DATE, END_DATE FROM TUTORS T LEFT OUTER
    JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID LEFT OUTER JOIN COURSES
    C ON T.TUTOR_ID=C.TUTOR_ID WHERE T.TUTOR_ID=#{tutorId}")
    @ResultMap("com.mybatis3.mappers.TutorMapper.TutorResult")
    Tutor selectTutorById(int tutorId);
}

转载于:https://my.oschina.net/guowei11/blog/3075509

chengwei9975

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用注解配置sql映射器

在映射器Mapper接口上使用注解映射语句insertpackage com.mybatis3.mappers;public interface StudentMapper{ @Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,AD...
复制链接

扫一扫