Java Persistence with MyBatis 3(中文版) 第四章 使用注解配置SQL映射器
时间:2022-03-10 17:48
在上一章,我们看到了我们是怎样在映射器Mapper XML配置文件中配置映射语句的。MyBatis也支持使用注解来配置映射语句。当我们使用基于注解的映射器接口时,我们不再需要在XML配置文件中配置了。如果你愿意,你也可以同时使用基于XML和基于注解的映射语句。
本章将涵盖以下话题:
- l 在映射器Mapper接口上使用注解
- l 映射语句
@Insert,@Update,@Delete,@SeelctStatements
- l 结果映射
一对一映射
一对多映射
- l 动态SQL
@SelectProvider
@InsertProvider
@UpdateProvider
@DeleteProvider
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注解的insertMethod()方法将返回insert语句执行后影响的行数。
@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();
这里STUD_ID列值将会通过MySQL数据库自动生成。并且生成的值将会被设置到student对象的studId属性中。
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); mapper.insertStudent(student); int studentId = student.getStudId();
有一些数据库如Oracle,并不支持AUTO_INCREMENT列属性,它使用序列(SEQUENCE)来产生主键的值。
我们可以使用@SelectKey注解来为任意SQL语句来指定主键值,作为主键列的值。
假设我们有一个名为STUD_ID_SEQ的序列来生成STUD_ID主键值。@Insert("INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL,ADDR_ID, PHONE) VALUES(#{studId},#{name},#{email},#{address.addrId},#{phone})") @SelectKey(statement="SELECT STUD_ID_SEQ.NEXTVAL FROM DUAL", keyProperty="studId", resultType=int.class, before=true) int insertStudent(Student student);
这里我们使用了@SelectKey来生成主键值,并且存储到了student对象的studId属性上。由于我们设置了before=true,该语句将会在执行INSERT语句之前执行。
如果你使用序列作为触发器来设置主键值,我们可以在INSERT语句执行后,从sequence_name.currval获取数据库产生的主键值。@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 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 FROM STUDENTS WHERE STUD_ID=#{studId}")
int deleteStudent(int studId);
使用了@Delete的deleteStudent()方法将会返回执行了update语句后影响的行数。
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);
}
为了将列名和Studentbean属性名匹配,我们为stud_id起了一个studId的别名。如果返回了多行结果,将抛出 TooManyResultsException异常。
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();
}
例如,看下面的findStudentById()和findAllStudents()方法:
@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();
这里两个语句的@Results配置完全相同,但是我必须得重复它。这里有一个解决方法。我们可以创建一个映射器Mapper配置文件, 然后配置<resultMap>元素,然后使用@ResultMap注解引用此<resultMap>。
在StudentMapper.xml中定义一个ID为StudentResult的<resultMap>。
<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>
在StudentMapper.java中,使用@ResultMap引用名为StudentResult的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(); }
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()方法。如果@OneSELECT查询返回了多行结果,则会抛出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配置SQL映射器中我们讨论过,我们可以通过基于XML的映射器配置,使用嵌套结果ResultMap来加载一对一关联的查询。而MyBatis3.2.2版本,并没有对应的注解支持。但是我们可以在映射器Mapper配置文件中配置<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); }
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映射器中我们讨论过,我们可以通过基于XML的映射器配置,使用嵌套结果ResultMap来加载一对多关联的查询。而MyBatis3.2.2版本,并没有对应的注解支持。但是我们可以在映射器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); }
package com.mybatis3.sqlproviders; import org.apache.ibatis.jdbc.SQL; public class TutorDynaSqlProvider { public String findTutorByIdSql(int tutorId) { return "SELECT TUTOR_ID AS tutorId, NAME, EMAIL FROM TUTORS WHERE TUTOR_ID=" + tutorId; } } 在TutorMapper.java接口中创建一个映射语句,如下:
@SelectProvider(type=TutorDynaSqlProvider.class, method="findTutorByIdSql") Tutor findTutorById(int tutorId);
这里我们使用了@SelectProvider来指定了一个类,及其内部的方法,用来提供需要执行的SQL语句。
但是使用字符串拼接的方法唉构建SQL语句是非常困难的,并且容易出错。所以MyBaits提供了一个SQL工具类不使用字符串拼接的方式,简化构造动态SQL语句。
现在,让我们看看如何使用org.apache.ibatis.jdbc.SQL工具类来准备相同的SQL语句。
package com.mybatis3.sqlproviders; import org.apache.ibatis.jdbc.SQL; public class TutorDynaSqlProvider { public String findTutorByIdSql(final int tutorId) { return new SQL() { { SELECT("tutor_id as tutorId, name, email"); FROM("tutors"); WHERE("tutor_id=" + tutorId); } } .toString(); } }
SQL工具类会处理以合适的空格前缀和后缀来构造SQL语句。
动态SQL provider方法可以接收以下其中一种参数:
- ? 无参数
- ? 和映射器Mapper接口的方法同类型的参数
- ? java.util.Map
如果SQL语句的准备不取决于输入参数,你可以使用不带参数的SQL Provider方法。
例如:
public String findTutorByIdSql() { return new SQL() { { SELECT("tutor_id as tutorId, name, email"); FROM("tutors"); WHERE("tutor_id = #{tutorId}"); } } .toString(); }
这里我们没有使用输入参数构造SQL语句,所以它可以是一个无参方法。
如果映射器Mapper接口方法只有一个参数,那么可以定义SQLProvider方法,它接受一个与Mapper接口方法相同类型的参数。
例如映射器Mapper接口有如下定义:
<span style="font-family:Microsoft YaHei;font-size:12px;">Tutor findTutorById(int tutorId);</span>
这里findTutorById(int)方法只有一个int类型的参数。我们可以定义findTutorByIdSql(int)方法作为SQL provider方法。
public String findTutorByIdSql(final int tutorId) { return new SQL() { { SELECT("tutor_id as tutorId, name, email"); FROM("tutors"); WHERE("tutor_id=" + tutorId); } } .toString(); }如果映射器Mapper接口有多个输入参数,我们可以使用参数类型为java.util.Map的方法作为SQLprovider方法。然后映射器Mapper接口方法所有的输入参数将会被放到map中,以param1,param2等等作为key,将输入参数按序作为value。你也可以使用0,1,2等作为key值来取的输入参数。
@SelectProvider(type = TutorDynaSqlProvider.class, method = "findTutorByNameAndEmailSql") Tutor findTutorByNameAndEmail(String name, String email); public String findTutorByNameAndEmailSql(Map<String, Object> map) { String name = (String) map.get("param1"); String email = (String) map.get("param2"); //you can also get those values using 0,1 keys //String name = (String) map.get("0"); //String email = (String) map.get("1"); return new SQL() { { SELECT("tutor_id as tutorId, name, email"); FROM("tutors"); WHERE("name=#{name} AND email=#{email}"); } } .toString(); }
SQL工具类也提供了其他的方法来表示JOINS,ORDER_BY,GROUP_BY等等。
让我们看一个使用LEFT_OUTER_JOIN的例子:public class TutorDynaSqlProvider { public String selectTutorById() { return new SQL() { { SELECT("t.tutor_id, t.name as tutor_name, email"); SELECT("a.addr_id, street, city, state, zip, country"); SELECT("course_id, c.name as course_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 = #{id}"); } } .toString(); } } public interface TutorMapper { @SelectProvider(type = TutorDynaSqlProvider.class, method = "selectTutorById") @ResultMap("com.mybatis3.mappers.TutorMapper.TutorResult") Tutor selectTutorById(int tutorId); }由于没有支持使用内嵌结果ResultMap的一对多关联映射的注解支持,我们可以使用基于XML的<resultMap>配置,然后与@ResultMap映射。
<mapper namespace="com.mybatis3.mappers.TutorMapper"> <resultMap type="Address" id="AddressResult"> <id property="id" 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="id" /> <result column="course_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="id" /> <result column="tutor_name" property="name" /> <result column="email" property="email" /> <association property="address" resultMap="AddressResult" /> <collection property="courses" resultMap="CourseResult"></collection> </resultMap> </mapper>使用了动态的SQL provider,我们可以取得讲师及其地址和课程明细。
public class TutorDynaSqlProvider
{
public String insertTutor(final Tutor tutor)
{
return new SQL()
{
{
INSERT_INTO("TUTORS");
if (tutor.getName() != null)
{
VALUES("NAME", "#{name}");
}
if (tutor.getEmail() != null)
{
VALUES("EMAIL", "#{email}");
}
}
} .toString();
}
}
public interface TutorMapper
{
@InsertProvider(type = TutorDynaSqlProvider.class,
method = "insertTutor")
@Options(useGeneratedKeys = true, keyProperty = "tutorId")
int insertTutor(Tutor tutor);
}
public class TutorDynaSqlProvider
{
public String updateTutor(final Tutor tutor)
{
return new SQL()
{
{
UPDATE("TUTORS");
if (tutor.getName() != null)
{
SET("NAME = #{name}");
}
if (tutor.getEmail() != null)
{
SET("EMAIL = #{email}");
}
WHERE("TUTOR_ID = #{tutorId}");
}
} .toString();
}
}
public interface TutorMapper
{
@UpdateProvider(type = TutorDynaSqlProvider.class,
method = "updateTutor")
int updateTutor(Tutor tutor);
}
public class TutorDynaSqlProvider
{
public String deleteTutor(int tutorId)
{
return new SQL()
{
{
DELETE_FROM("TUTORS");
WHERE("TUTOR_ID = #{tutorId}");
}
} .toString();
}
}
public interface TutorMapper
{
@DeleteProvider(type = TutorDynaSqlProvider.class,
method = "deleteTutor")
int deleteTutor(int tutorId);
}
4.5 总结
在本章中,我们学习了怎样使用注解书写SQL映射语句。讨论了如何配置简单语句,一对一关系语句和一对多关系语句。我们还探讨了怎样使用SqlProvider注解来构建动态SQL语句。在下一章,我们将讨论如何将MyBatis与Spring框架集成。
前一章:
下一章:Java Persistence with MyBatis 3(中文版) 第五章 与Spring集成
-------------------------------------------------------------------------------------------------------------------------------
作者声明:本文出处是http://blog.csdn.net/luanlouis,如需转载,请注明出处!
public class TutorDynaSqlProvider
{
public String deleteTutor(int tutorId)
{
return new SQL()
{
{
DELETE_FROM("TUTORS");
WHERE("TUTOR_ID = #{tutorId}");
}
} .toString();
}
}
public interface TutorMapper
{
@DeleteProvider(type = TutorDynaSqlProvider.class,
method = "deleteTutor")
int deleteTutor(int tutorId);
}
4.5 总结
在本章中,我们学习了怎样使用注解书写SQL映射语句。讨论了如何配置简单语句,一对一关系语句和一对多关系语句。我们还探讨了怎样使用SqlProvider注解来构建动态SQL语句。在下一章,我们将讨论如何将MyBatis与Spring框架集成。
前一章:
下一章:Java Persistence with MyBatis 3(中文版) 第五章 与Spring集成
-------------------------------------------------------------------------------------------------------------------------------
作者声明:本文出处是http://blog.csdn.net/luanlouis,如需转载,请注明出处!
在本章中,我们学习了怎样使用注解书写SQL映射语句。讨论了如何配置简单语句,一对一关系语句和一对多关系语句。我们还探讨了怎样使用SqlProvider注解来构建动态SQL语句。在下一章,我们将讨论如何将MyBatis与Spring框架集成。
前一章:
下一章:Java Persistence with MyBatis 3(中文版) 第五章 与Spring集成
-------------------------------------------------------------------------------------------------------------------------------
作者声明:本文出处是http://blog.csdn.net/luanlouis,如需转载,请注明出处!Java Persistence with MyBatis 3(中文版) 第四章 使用注解配置SQL映射器,布布扣,bubuko.com