一、插入数据并获取ID
1.1、GameMapper实体类
public class GameRecord {
/**
* 记录ID
*/
private String recordId;
/**
* 主队ID
*/
private Integer homeTeamId;
/**
* 比赛时间
*/
private Date gameDate;
/**
* 比分
*/
private Integer score;
/**
* 客队ID
*/
private Integer visitingTeamId;
public GameRecord() {
}
public GameRecord(String recordId, Integer homeTeamId, Date gameDate, Integer score, Integer visitingTeamId) {
this.recordId = recordId;
this.homeTeamId = homeTeamId;
this.gameDate = gameDate;
this.score = score;
this.visitingTeamId = visitingTeamId;
}
public String getRecordId() {
return recordId;
}
public void setRecordId(String recordId) {
this.recordId = recordId;
}
public Integer getHomeTeamId() {
return homeTeamId;
}
public void setHomeTeamId(Integer homeTeamId) {
this.homeTeamId = homeTeamId;
}
public Date getGameDate() {
return gameDate;
}
public void setGameTime(Date gameDate) {
this.gameDate = gameDate;
}
public Integer getScore() {
return score;
}
public void setScore(Integer score) {
this.score = score;
}
public Integer getVisitingTeamId() {
return visitingTeamId;
}
public void setVisitingTeamId(Integer visitingTeamId) {
this.visitingTeamId = visitingTeamId;
}
}
1.2、GameMapper接口
public interface GameRecordMapper {
/**
* 新增
* @param gameRecord
* @return
*/
Integer add(GameRecord gameRecord);
}
1.3、GameMapper.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="com.jsonliu.test.mapper.GameRecordMapper">
<!-- 添加一条比赛记录 -->
<insert id="add" parameterType="com.jsonliu.test.entity.GameRecord">
<!-- 插入数据之前先获取36字符串作为ID放入属性recordId中,order="BEFORE/AFTER"是在insert之前还是之后,resultType是返回值类型 -->
<selectKey keyProperty="recordId" order="BEFORE" resultType="String">
select uuid()
</selectKey>
INSERT INTO gamerecord ( recordId, homeTeamId, gameDate, score, visitingTeamId )
VALUES (#{recordId},#{homeTeamId},#{gameDate},#{score},#{visitingTeamId} )
</insert>
</mapper>
1.4、注册到Mybatis.xml中
<configuration>
...
<mappers>
<mapper resource="com/jsonliu/test/entity/Team.xml"/>
<mapper resource="com/jsonliu/test/mapper/TeamMapper.xml"/>
<mapper resource="com/jsonliu/test/mapper/GameRecordMapper.xml"/>
</mappers>
</configuration>
1.5、自增ID的获取
<mapper namespace="com.jsonliu.test.mapper.TeamMapper">
...
<!--parameterType="com.jsonliu.test.entity.Team" 将对象作为参数,
#{}必须是实体类中的属性名称,即占位符?-->
<insert id="insert" parameterType="com.jsonliu.test.entity.Team">
<!--新增成功之后将自增ID赋值给参数teamId,keyProperty表示新增ID赋值给哪个属性,
order:AFTER/BEFORE表示selectKey中的语句在insert之前执行还是之后执行,resultType表示返回值类型-->
<selectKey keyProperty="teamId" order="AFTER" resultType="java.lang.Integer">
select LAST_INSERT_ID()
</selectKey>
insert into team(teamName,location,createTime)
values(#{teamName},#{location},#{createTime})
</insert>
...
</mapper>
1.6、测试类
public class GameRecordMapperTest {
SqlSession sqlSession=MybatisUtil.getSqlSession();
@Test
public void test() {
GameRecordMapper mapper = sqlSession.getMapper(GameRecordMapper.class);
GameRecord gameRecord = new GameRecord();
gameRecord.setHomeTeamId(1007);
gameRecord.setVisitingTeamId(1002);
gameRecord.setScore(108);
gameRecord.setGameTime(new Date());
Integer num = mapper.add(gameRecord);
sqlSession.commit();
System.out.println("add的添加结果:" + num);
System.out.println(gameRecord.getRecordId());
}
}
执行结果:
二、输入映射
2.1、parameterType 多个参数
接口中方法的类型,必须是完全限定名或者别名。该属性非必填,因为Mybatis框架能自行判断具体传入语句参数,默认值设置为unset。
<mapper>
...
<!--多参数:标签中不需要parameterType属性,
mybatis3.3版本之前:可以直接写#{0} #{1}
从mybatis3.4开始:#{arg0} #{arg1}... 或者是 #{param1} #{param2}...
sql语句中不能使用小于号,使用转移符号替换;大于号没有限制,也可以使用转义符号替换>
-->
<select id="queryByRange" resultType="com.jsonliu.test.entity.Team">
select * from team where teamId >= #{arg0} and teamId <= #{arg1}
</select>
</mapper>
测试结果:
2.2、@Param注解
在方法的形参前面加@Param(“自定义参数名称”),mapper文件中使用#{自定义参数名称}的方式传值。
TeamMapper接口中添加:
List<Team> queryByRange2(@Param("min") Integer min,@Param("max") Integer max);
xml中添加:
<!--
#{}中名称必须与接口的方法中的参数注解@Param()保持一致
#{param1} #{param2}也可以使用,不推荐; #{arg0} #{arg1}却是不可使用的
-->
<select id="queryByRange2" resultType="com.jsonliu.test.entity.Team">
select * from team where teamId >= #{min} and teamId <= #{max}
</select>
测试结果:
2.3、通过map传递多个参数
Map集合一次可以存储多个值,可以使用Map向mapper文件传递多个参数。Map集合使用String的key,Object类型的值存储参数。在mapper文件使用#{key}引用参数值。
接口中添加方法:
List<Team> queryByRange3(Map<String,Object> map);
xml文件中添加映射
<!-- 使用map传递多个参数:映射文件中参数占位符必须和map中string类型字段名一样 -->
<select id="queryByRange3" resultType="com.jsonliu.test.entity.Team">
select * from team where teamId >= #{min} and teamId <= #{max}
</select>
测试结果:
2.4、通过pojo传递多个参数
与map传递多个参数类似,要求映射文件中的参数占位符必须与pojo类中属性一致。
接口中添加方法:
List<Team> queryByCondition(QueryVO vo);
xml文件中添加映射:
<!-- 使用pojo传递多个参数:映射文件中参数占位符必须和pojo中字段名一样 -->
<select id="queryByCondition" resultType="com.jsonliu.test.entity.Team">
select * from team where teamId >= #{min} and teamId <= #{max}
and teamName like #{name} and location =#{location}
</select>
测试结果:
2.5、#{}与${}区别
#{} 表示一个占位符,通知mybatis用实际参数替代,并使用prepareStatement对象执行sql语句,#{} 代替了sql语句中 ? (这是mybatis首选做法,安全快速)
表 示 字 符 串 原 样 替 换 , 通 知 m y b a t i s 使 用 {} 表示字符串原样替换,通知mybatis使用 表示字符串原样替换,通知mybatis使用包含的“字符串”替换所在位置,使用Statement或者prepareStatement将sql语句和${}内容连起来。一般用在替换表名,列名,不同列排序等操作。
2.5.1、例子#{}:
List<Team> queryByName(String teamName);
List<Team> queryByLocation(String location);
<select id="queryByName" resultType="com.jsonliu.test.entity.Team">
select * from team where teamName=#{teamName}
</select>
<select id="queryByLocation" resultType="com.jsonliu.test.entity.Team">
select * from team where location=#{location}
</select>
@Test
public void test7(){
TeamMapper teamMapper = sqlSession.getMapper(TeamMapper.class);
System.out.println("根据球队名称查询:");
List<Team> teams = teamMapper.queryByName("勇士");
teams.forEach(team -> {
System.out.println(team);
});
System.out.println("根据球队位置查询:");
teams = teamMapper.queryByLocation("洛杉矶");
teams.forEach(team -> {
System.out.println(team);
});
}
例子中,#{} 代替了sql语句中 ?
2.5.2、例子${}:
List<Team> queryByField(@Param("column")String column,@Param("columnValue")String columnValue);
<select id="queryByField" resultType="com.jsonliu.test.entity.Team">
select * from team where ${column} = #{columnValue}
</select>
@Test
public void test8(){
TeamMapper teamMapper = sqlSession.getMapper(TeamMapper.class);
System.out.println("根据球队名称查询:");
List<Team> teams = teamMapper.queryByField("teamName","勇士");
teams.forEach(team -> {
System.out.println(team);
});
System.out.println("根据球队位置查询:");
teams = teamMapper.queryByField("location","洛杉矶");
teams.forEach(team -> {
System.out.println(team);
});
}
执行结果:
${}进行了原样替换,#{} 代替了sql语句中 ?
三、输出映射
resultType:执行sql得到resultSet转换的类型,使用类型的完全限定名或者别名。如果返回的是集合,设置的是集合的元素类型,而不是集合本身。resultType和resultMap不能同时使用。
3.1、输出简单类型
TeamMapper接口添加:
Integer getCount();
TeamMapper.xml添加映射:
<select id="getCount" resultType="int">
select count(1) from team
</select>
添加测试方法:
@Test
public void test9(){
TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
Integer count = mapper.getCount();
System.out.println("总共的行数:"+count);
}
3.2、输出pojo类型
List<Team> queryAll();
<!-- 接口方法返回的是集合类型,但是映射文件中的resultType需要指定集合中的类型,不是集合本身 -->
<select id="queryAll" resultType="com.jsonliu.test.entity.Team">
select * from team
</select>
3.3、输出Map类型
当我们只需要在表中查询几列数据的时候可以将sql的查询结果作为Map的key和value,一般使用的是Map<Object,Object>,Map作为接口返回值,sql语句的查询结果最多只能有一条记录。大于一条记录会抛出TooManyResultException。
如果有多行使用List<Map<Object,Object>>
TeamMapper接口添加:
Map<String,Object> queryTwoColumn(int teamId);
List<Map<String,Object>> queryTwoColumnList();
TeamMapper.xml添加映射:
<select id="queryTwoColumn" resultType="hashMap">
select teamName,location from team where teamId=#{teamId}
</select>
<select id="queryTwoColumnList" resultType="hashMap">
select teamName,location from team
</select>
添加测试方法:
@Test
public void test10(){
TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
Map<String, Object> map = mapper.queryTwoColumn(1006);
System.out.println(map);
}
@Test
public void test11(){
TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
List<Map<String, Object>> maps = mapper.queryTwoColumnList();
maps.forEach(map->{
System.out.println(map);
});
}
3.4、输出resultMap
resultMap可以自定义sql的结果和java对象属性的映射关系,更灵活的把列值赋值给属性。常用在列名和java对象属性不一致的情况。
List<Team> queryAll2();
<!-- 自己编写表中列与实体中属性的映射,id:resultMap的名称,要求唯一。type:期待要映射的java类型 -->
<resultMap id="baseResultMap" type="com.jsonliu.test.entity.Team">
<!-- 一般主键列用id,其他列用result。 column:表示数据库中列名,不区分大小写。
property:实体中属性名,区分大小写。 javaType:实体中属性类型,可以省略,Mybatis自己推断。
jdbcType: 数据库字段类型,一般省略-->
<id column="teamId" property="teamId" javaType="java.lang.Integer" ></id>
<result column="teamName" property="teamName" javaType="java.lang.String"></result>
<result column="location" property="location" javaType="java.lang.String"></result>
<result column="createTime" property="createTime" javaType="java.util.Date"></result>
</resultMap>
<select id="queryAll2" resultMap="baseResultMap">
select * from team
</select>
@Test
public void test12(){
TeamMapper mapper = sqlSession.getMapper(TeamMapper.class);
List<Team> teams = mapper.queryAll2();
teams.forEach(team->{
System.out.println(team);
});
}
3.5、数据库表中列与实体属性不一致
数据库表:
CREATE TABLE `users` (
`user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
`user_name` varchar(50) NULL COMMENT '用户姓名',
`user_age` int DEFAULT NULL COMMENT '用户年龄',
PRIMARY KEY (`user_id`)
) COMMENT '用户表';
INSERT INTO `mybatis`.`users`(`user_id`, `user_name`, `user_age`) VALUES (1, '贾宝玉', 14);
INSERT INTO `mybatis`.`users`(`user_id`, `user_name`, `user_age`) VALUES (2, '林黛玉', 13);
INSERT INTO `mybatis`.`users`(`user_id`, `user_name`, `user_age`) VALUES (3, '薛宝钗', 15);
3.5.1使用列别名
实体类Users:
public class Users {
private Integer userId;
private String userName;
private Integer userAge;
public Users() {
}
public Users(Integer userId, String userName, Integer userAge) {
this.userId = userId;
this.userName = userName;
this.userAge = userAge;
}
@Override
public String toString() {
return "Users{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userAge=" + userAge +
'}';
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getUserAge() {
return userAge;
}
public void setUserAge(Integer userAge) {
this.userAge = userAge;
}
}
UsersMapper接口:
public interface UsersMapper {
Users queryById(Integer userId);
}
UsersMapper.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="com.jsonliu.test.mapper.UsersMapper">
<select id="queryById" resultType="com.jsonliu.test.entity.Users">
select user_id as userId,user_name as userName,user_age as userAge from users where user_id=#{userId}
</select>
</mapper>
UsersMapperTest测试类:
public class UsersMapperTest {
private UsersMapper usersMapper = MybatisUtil.getSqlSession().getMapper(UsersMapper.class);
@Test
public void Test1() {
Users users = usersMapper.queryById(1);
System.out.println(users);
}
}
3.5.2、使用resultMap
添加接口方法:
Users queryById2(Integer userId);
添加xml映射:
<select id="queryById2" resultMap="baseResultMap">
select * from users where user_id=#{userId}
</select>
<resultMap id="baseResultMap" type="com.jsonliu.test.entity.Users">
<id column="user_id" property="userId" javaType="java.lang.Integer"></id>
<result column="user_name" property="userName" javaType="java.lang.String"></result>
<result column="user_age" property="userAge" javaType="java.lang.Integer"></result>
</resultMap>
添加测试方法:
@Test
public void test2(){
Users users = usersMapper.queryById2(1);
System.out.println(users);
}