1 映射文件XXMapper.xml语法:
<mapper namespace="cn.smbms.dao.user.UserMapper">
<select id="getUserList" …
……
</select>
</mapper>
1.1 namespace:命名空间
namespace的命名必须跟某个接口同名
1.2 id: 命名空间中唯一的标识符
接口中的方法与映射文件中的SQL语句id一一对应
1.3 parameterType: 参数类型
传入SQL语句的参数类型
1.4 resultType:返回值类型
SQL语句返回值类型的完整类名或别名
1.5 实战:
1.6 实现:
(1)导入库
smbms_db.sql
(2)创建工程,目录
(3)pom
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.36</version> </dependency>
(4)实体类
package cn.kgc.entity;
public class User {
private Integer id;
private String userCode;
private String userName;
private String userPassword;
private Integer gender;
private String birthday;
private String phone;
private String address;
private Integer userRole;
private Integer createdBy;
private String creationDate;
private Integer modifyBy;
private String modifyDate;
private String userRoleName;
public String getUserRoleName() {
return userRoleName;
}
public void setUserRoleName(String userRoleName) {
this.userRoleName = userRoleName;
}
public String getCreationDate() {
return creationDate;
}
public void setCreationDate(String creationDate) {
this.creationDate = creationDate;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getUserRole() {
return userRole;
}
public void setUserRole(Integer userRole) {
this.userRole = userRole;
}
public Integer getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Integer createdBy) {
this.createdBy = createdBy;
}
public Integer getModifyBy() {
return modifyBy;
}
public void setModifyBy(Integer modifyBy) {
this.modifyBy = modifyBy;
}
public String getModifyDate() {
return modifyDate;
}
public void setModifyDate(String modifyDate) {
this.modifyDate = modifyDate;
}
}
(5)mybatis-config.xml
<span style="background-color:#f8f8f8"><span style="color:#333333"><?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/smbms"/>
<property name="username" value="root"/>
<property name="password" value="ok"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="cn/kgc/mapper/UserMapper.xml"/>
</mappers>
</configuration></span></span>
(6)mapper/UserMapper.java
<span style="background-color:#f8f8f8"><span style="color:#333333">package cn.kgc.mapper;
import cn.kgc.entity.User;
import java.util.List;
public interface UserMapper {
public List<User> findByName(String userName);
}
</span></span>
(7)mapper/UserMapper.xml
<span style="background-color:#f8f8f8"><span style="color:#333333"><?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="cn.kgc.mapper.UserMapper">
<select id="findByName" parameterType="java.lang.String" resultType="cn.kgc.entity.User">
select * from smbms_user where userName like concat('%',#{userName},'%')
</select>
</mapper></span></span>
(8)MyBatisUtil
<span style="background-color:#f8f8f8"><span style="color:#333333">package cn.smbms.utils;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MyBatisUtil {
private static SqlSessionFactory factory;
static{//在静态代码块下,factory只会被创建一次
System.out.println("static factory===============");
try {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
factory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static SqlSession createSqlSession(){
return factory.openSession(false);//true 为自动提交事务
}
public static void closeSqlSession(SqlSession sqlSession){
if(null != sqlSession)
sqlSession.close();
}
}
</span></span>
(9)TestUserMapper.java
<span style="background-color:#f8f8f8"><span style="color:#333333">package cn.kgc.test;
import cn.kgc.entity.User;
import cn.kgc.mapper.UserMapper;
import cn.kgc.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.List;
public class TestUserMapper {
@Test
public void testfindByName() throws IOException {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
List<User> list = sqlSession.getMapper(UserMapper.class).findByName("孙");
for(User u:list){
System.out.println("用户名称:"+u.getUserName()+" 用户密码:"+u.getUserPassword());
}
sqlSession.close();
}
}
</span></span>
1.7 parameterType 基础数据类型/复杂数据类型
<span style="background-color:#f8f8f8"><span style="color:#333333">parameterType
(1)基础数据类型:
int、String、Date等
只能传入一个,通过#{参数名}即可获取传入的值
(2)复杂数据类型:
Java实体类、Map等
通过#{属性名}或者#{map的keyName}即可获取传入值</span></span>
1.8 多参实战:
需求:通过用户名模糊匹配和用户角色id 条件查询
1.9第一种实现:
(1)UserMapper.java
<span style="background-color:#f8f8f8"><span style="color:#333333">public List<User> findAllByUser(User user);</span></span>
(2)UserMapper.xml
<span style="background-color:#f8f8f8"><span style="color:#333333"><select id="findAllByUser" parameterType="cn.kgc.entity.User" resultType="cn.kgc.entity.User">
select * from smbms_user where userName like concat('%',#{userName},'%')
and userRole = #{userRole}
</select></span></span>
(3)TestUserMapper
<span style="background-color:#f8f8f8"><span style="color:#333333">@Test
public void testfindAllByUser() throws IOException {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
User user = new User();
user.setUserName("孙");
user.setUserRole(5);
List<User> list = sqlSession.getMapper(UserMapper.class).findAllByUser(user);
for(User u:list){
System.out.println("用户名称:"+u.getUserName()+" 用户密码:"+u.getUserPassword());
}
sqlSession.close();
}</span></span>
1.10第二种实现:
(1)UserMapper.java
<span style="background-color:#f8f8f8"><span style="color:#333333">public List<User> findAllByMap(Map<String,String> map);</span></span>
(2)UserMapper.xml
<span style="background-color:#f8f8f8"><span style="color:#333333"><select id="findAllByMap" parameterType="java.util.Map" resultType="cn.kgc.entity.User">
select * from smbms_user where userName like concat('%',#{userName},'%')
and userRole = #{userRole}
</select></span></span>
(3)TestUserMapper
<span style="background-color:#f8f8f8"><span style="color:#333333">@Test
public void testfindAllByMap() throws IOException {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
Map map =new HashMap<>();
map.put("userName","孙");
map.put("userRole","5");
List<User> list = sqlSession.getMapper(UserMapper.class).findAllByMap(map);
for(User u:list){
System.out.println("用户名称:"+u.getUserName()+" 用户密码:"+u.getUserPassword());
}
sqlSession.close();
}</span></span>
1.11 resultMap 描述如何将结果集映射到Java对象
<span style="background-color:#f8f8f8"><span style="color:#333333">resultType :直接表示返回类型
基本数据类型
复杂数据类型
resultMap :对外部resultMap的引用
应用场景:
数据库字段信息与对象属性不一致
复杂的联合查询,自由控制映射结果
二者不能同时存在,本质上都是Map数据结构</span></span>
1.12 通过用户名和角色编码查询用户列表
(1)User.java
<span style="background-color:#f8f8f8"><span style="color:#333333">private String userRoleName;
public String getUserRoleName() {
return userRoleName;
}
public void setUserRoleName(String userRoleName) {
this.userRoleName = userRoleName;
}</span></span>
(2)UserMapper.java
<span style="background-color:#f8f8f8"><span style="color:#333333"> public List<User> findAllByUser2(User user);</span></span>
(3)UserMapper.xml
<span style="background-color:#f8f8f8"><span style="color:#333333"><select id="findAllByUser2" parameterType="cn.kgc.entity.User" resultMap="userList">
select u.*,r.roleName from smbms_user u,smbms_role r
where u.userName like CONCAT ('%',#{userName},'%')
and u.userRole = #{userRole} and u.userRole = r.id
</select>
<resultMap id="userList" type="cn.kgc.entity.User">
<result property="id" column="id"/>
<result property="userCode" column="userCode"/>
<result property="userName" column="userName"/>
<result property="phone" column="phone"/>
<result property="birthday" column="birthday"/>
<result property="gender" column="gender"/>
<result property="userRole" column="userRole"/>
<result property="userRoleName" column="roleName"/>
</resultMap></span></span>
(4)TestUserMapper.java
<span style="background-color:#f8f8f8"><span style="color:#333333">@Test
public void testfindAllByUser2() throws IOException {
SqlSession sqlSession = MyBatisUtil.createSqlSession();
User user = new User();
user.setUserName("孙");
user.setUserRole(3);
List<User> list = sqlSession.getMapper(UserMapper.class).findAllByUser2(user);
for(User u:list){
System.out.println("用户名称:"+u.getUserName()+" 用户密码:"+u.getUserPassword()+" 用户角色:"+u.getUserRoleName());
}
sqlSession.close();
}</span></span>