Mybatis学习笔记---day01基础知识

最新推荐文章于 2023-06-19 12:18:57 发布

DJTUDaker

最新推荐文章于 2023-06-19 12:18:57 发布

阅读量1k

点赞数 1

分类专栏： ssm框架文章标签： mybatis学习笔记 mybatis

本文链接：https://blog.csdn.net/wingzhezhe/article/details/52096578

版权

ssm框架专栏收录该内容

12 篇文章 0 订阅

订阅专栏

一、Mybatis执行流程图及优缺点

1、执行流程

2、与hibernate对比优缺点

二：Mybatis入门小案例

1.创建java项目，导入mybatis、mysql、junit 依赖的jar包

2.创建并编写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>
	<!-- 和spring整合后 environments配置将废除-->
	<environments default="development">
		<environment id="development">
		<!-- 使用jdbc事务管理-->
			<transactionManager type="JDBC" />
		<!-- 数据库连接池-->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
				<property name="username" value="root" />
				<property name="password" value="wingzhe" />
			</dataSource>
		</environment>
	</environments>
	
	<!-- 指定Mapper的位置 -->
	<mappers>
		<mapper resource="sqlmap/UserMapper.xml"/>
	</mappers>
</configuration>

3、log4j.properties

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=debug, stdout

4.创建POJO类

package com.mybatis.pojo;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable{

	private static final long serialVersionUID = 1L;
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address="
				+ address + "]";
	}
}

5.创建Mapper.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="test">  
    <!-- 根据id获取用户对象  
  
    id : 门号  
    parameterMap ： 废弃  
    parameterType : 入参的类型   Integer String  POJO  com.mybatis.pojo.User  
    resultType : 返回值类型 com.mybatis.pojo.User  只要sql的字段与POJO属性字段一样时 自动映射POJO里  
    resultMap : sql的字段与POJO属性字段不一样时 resultMap进行手动映射   
    #{} : sql语句的占位符   POJO的类型转成JDBC类型，即将传递的参数自动加上''号，如果参数是基本数据类型，  
    则括号中的内容可以是任意值  例如： 1   '1'   张三   '张三'  {中随便写}  
     -->  
    <select id="selectUserById" parameterType="Integer" resultType="com.mybatis.pojo.User">  
        select * from user where id = #{vv}  
    </select>  
      
    <!-- 根据用户名模糊查询   
        #{}:mybatis框架自动提供特殊模式进行模糊查询语句组装:"%"#{username}"%"=='%张%'
			特点:
				1,"%"#{username}"%" 是mybatis提供特殊写法,自动构造模糊查询
				2,如果传递数据是字符串类型,#{username}获取的数据自动加上双引号  "张"
				3,如果传递参数是基本类型,#{}可以是任意值
				4,如果传递参数是pojo对象,#{}使用ognl(对象导航语言)表达式获取参数
				ognl语法:属性.属性.属性...
		${}:sql语句拼接:'%${value}%' 	将参数原样输出，不加''  
				1,${}获取数据无论是什么类型数据,原样获取,不加任何修饰
				2,${}如果获取是基本类型(int,long,string)的数据,${}里面必须是value
				3,如果传递参数是pojo对象,#{}使用ognl(对象导航语言)表达式获取参数
				4,${}就是sql拼接,会有sql语句注入风险,一般使用#
    -->  
    <select id="selectUsersByName" parameterType="String" resultType="com.mybatis.pojo.User">  
        select * from user where username like '%${value}%'  
    </select>  
      
    <!-- 修改用户信息 -->  
    <update id="updateUserById" parameterType="com.mybatis.pojo.User">  
        update user set username = #{username} where id = #{id}  
    </update>  
      
    <!-- 添加用户信息  
        mysql:先保存数据，后生成ID  
        oracle：先生成id，后保存数据  
        keyProperty:指定获取到的生成的id放入哪个字段中  
          
        将数据库生成的id放入对象中，使用以下这种方法数据库可移植性不强，不建议使用，建议使用 useGeneratedKeys="true" keyProperty="id"  
        <selectKey keyProperty="id" resultType="Integer" order="AFTER">  
            select_LAST_INSERT_ID()  
        </selectKey>  
     -->  
    <insert id="insertUser" parameterType="com.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">  
          
        insert into user(username,address) values (#{username},#{address});  
    </insert>  
      
    <!-- 删除用户信息 -->  
    <delete id="deleteUserById" parameterType="Integer">  
        delete from user where id = #{id}  
    </delete>  
      
</mapper>

6.创建Junit单元测试类

package com.mybatis.junit;

import java.io.InputStream;
import java.util.List;

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.Before;
import org.junit.Test;

import com.mybatis.pojo.User;

/**
 * junit单元测试类
 * @author Administrator
 *
 */
public class MybatisJunit {

	private SqlSessionFactory sqlSessionFactory = null;
	
	@Before
	public void testBefore() throws Exception{
		//读取配置文件到输入流中
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//工厂构建类
		//创建sqlSessionFactory工厂
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	}
	@Test
	public void testMybatis() {
		//打开sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//查询一条数据
		User user = sqlSession.selectOne("test.selectUserById",1);
		System.out.println(user);
	}
	/*
	 * 根据用户名模糊查询
	 */
	@Test
	public void testMybatisByName(){
		//打开sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		List<User> list = sqlSession.selectList("test.selectUsersByName", "王五");
		for (User user : list) {
			System.out.println(user);
		}
	}
	
	/*
	 * 根据ID修改用户信息
	 */
	@Test
	public void testUpdate(){
		//打开sqlSessiono
		SqlSession sqlSession = sqlSessionFactory.openSession();
		User user = sqlSession.selectOne("test.selectUserById",1);
		user.setUsername("王者1");
		sqlSession.update("test.updateUserById", user);
		sqlSession.commit();
	}
	/*
	 * 新增用户
	 */
	@Test
	public void testAddUser(){
		//打开sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		User user = new User();
		user.setUsername("你好");
		user.setAddress("河南平顶山");
		sqlSession.insert("test.insertUser", user);
		sqlSession.commit();
	}
	/*
	 * 根据id删除用户信息
	 */
	@Test
	public void testDelete(){
		//打开sqlSession
		SqlSession sqlSession = sqlSessionFactory.openSession();
		sqlSession.delete("test.deleteUserById", 28);
		sqlSession.commit();
	}
}

三：使用原始Dao实现MyBatis小案例

1.dao接口以及实现类

a、接口

package com.mybatis.dao;

import com.mybatis.pojo.User;

public interface UserDao {

	public User selectUserById(Integer id) throws Exception;
}

b、实现类

package com.mybatis.dao;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.mybatis.pojo.User;

public class UserDaoImpl implements UserDao {

	private SqlSessionFactory sqlSessionFactory = null;
	public UserDaoImpl(SqlSessionFactory sqlSessionFactory){
		this.sqlSessionFactory = sqlSessionFactory;
	};
	
	public User selectUserById(Integer id) throws Exception{
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//返回一个用户
		User user = sqlSession.selectOne("test.selectUserById", id);
		return user;
	}
}

2.编写测试类

package com.mybatis.junit;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import com.mybatis.dao.UserDao;
import com.mybatis.dao.UserDaoImpl;
import com.mybatis.pojo.User;

/**
 * junit单元测试类
 * @author Administrator
 *
 */
public class MybatisDaoJunit {

	private SqlSessionFactory sqlSessionFactory = null;
	
	@Before
	public void testBefore() throws Exception{
		//读取配置文件到输入流中
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//工厂构建类
		//创建sqlSessionFactory工厂
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	}
	@Test
	public void testSelectUser() throws Exception{
		UserDao userDao = new UserDaoImpl(sqlSessionFactory);
		User user = userDao.selectUserById(1);
		System.out.println(user);
		
	}
}

四：使用接口动态代理实现MyBatis小案例

1.规则：

a、 Mapper.xml文件中的namespace与mapper接口的类路径相同。
b、 Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
c、 Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql 的parameterType的类型相同
d、 Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同

2.编写mapper接口类

package com.mybatis.mapper;

import com.mybatis.pojo.User;

public interface UserMapper {
	//根据id获取用户信息
	public User selectUserById(Integer id);
}

3.编写mapper.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.mybatis.mapper.UserMapper">
	<!-- 根据ID获取用户对象 -->
	<select id="selectUserById" parameterType="Integer" resultType="com.mybatis.pojo.User">
		select * from user where id = #{id}
	</select>
</mapper>

4.编写junit测试类

package com.mybatis.junit;

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;
import org.junit.Before;
import org.junit.Test;

import com.mybatis.dao.UserDao;
import com.mybatis.dao.UserDaoImpl;
import com.mybatis.mapper.UserMapper;
import com.mybatis.pojo.User;

/**
 * junit单元测试类
 * @author Administrator
 *
 */
public class MybatisMapperDaoJunit {

	private SqlSessionFactory sqlSessionFactory = null;
	
	@Before
	public void testBefore() throws Exception{
		//读取配置文件到输入流中
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//工厂构建类
		//创建sqlSessionFactory工厂
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	}
	@Test
	public void testSelectUser() throws Exception{
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//获取接口代理
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = userMapper.selectUserById(10);
		System.out.println(user);
	}
}

五：sqlMapConfig.xml配置文件说明

1.properties配置

2.setting配置

mybatis全局配置参数

3.typeAliases

配置别名，配置完成之后直接可在mapper.xml中使用

<configuration>
	<typeAliases>
		<!-- 单个别名定义 -->
		<typeAlias alias="User" type="com.mybatis.pojo.User"/>
		<!-- 配置包下自动别名 -->
		<package name="com.mybatis.pojo"/>
	</typeAliases>

4.typeHandlers

类处理器，将java类型和jdbc类型映射，例如：将java中的string转换成jdbc中的varchar等，mybatis中自带的已经基本满足要求

5.mappers

指定mapper.xml文件的位置

a、<mapper resource=" "/>

相对于类路径的资源，例如：

<mapper resource="sqlMap/UserMapper.xml" />

b、<mapper url=" "/>

使用完全限定路径，该方式几乎不用。例如：

<mapper url=" file://D:\worksapce\mybatis\config\sqlMp\UserMapper.xml" />

c、<mapper class=" "/>

使用Mapper接口类路径，例如：

<mapper class="com.mybatis.mapper.UserMapper" />

注意：

此方式要求mapper接口名称和mapper.xml映射文件的 名称相同，且位于 同一个包中，且只能使用 接口代理开发方式进行开发

缺点：

此种方式只能扫描单个接口，如果接口多，则不适用

d、<package name=" " />

注册指定包下的所有mapper接口，例如：

<package name="com.mybatis.mapper" />

注意：

此方式要求mapper接口名称和mapper.xml映射文件的 名称相同，且位于 一个包中，且只能使用接口代理开发方式进行开发

六：Mapper.xml文件配置说明

1.当入参类型为pojo时，${}中的内容应该为pojo类的属性字段名称，不能随意写

例如：

<insert id="insertUser" parameterType="com.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">
		insert into user(username,address) values (#{username},#{address});
	</insert>

2.当入参为包装类时

a、编写包装类

package com.mybatis.pojo;
/**
 * 查询的包装类
 * @author Administrator
 *
 */
public class QueryVo {

	private User user;

	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	
}

b、编写Mapper接口文件中的方法

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	//根据id获取用户信息
	public User selectUserById(Integer id);
	
	//使用包装类，根据用户名模糊查询
	public List<User> selectUserByQuery(QueryVo queryVo);
}

c、编写mapper.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.mybatis.mapper.UserMapper">
	<!-- 使用包装类进行模糊查询 -->
	<select id="selectUserByQuery" parameterType="QueryVo" resultType="User">
		select * from user where username like '%${user.username}%'
	</select>
</mapper>

d、编写junit测试类

package com.mybatis.junit;

import java.io.InputStream;
import java.util.List;

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.Before;
import org.junit.Test;

import com.mybatis.mapper.UserMapper;
import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

/**
 * junit单元测试类
 * @author Administrator
 *
 */
public class MybatisMapperDaoJunit {

	private SqlSessionFactory sqlSessionFactory = null;
	
	@Before
	public void testBefore() throws Exception{
		//读取配置文件到输入流中
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		//工厂构建类
		//创建sqlSessionFactory工厂
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	}
	//使用包装类进行模糊查询
	@Test
	public void testSelectByQuery(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setUsername("明");
		QueryVo queryVo = new QueryVo();
		queryVo.setUser(user);
		List<User> list = userMapper.selectUserByQuery(queryVo);
		for (User user1 : list) {
			System.out.println(user1);
		}
	}
}

3.当入参为包装类型中的数组时

a、编写包装类的pojo

package com.mybatis.pojo;
/**
 * 查询的包装类
 * @author Administrator
 *
 */
public class QueryVo {

	private User user;
	private Object[] object;

	public Object[] getObject() {
		return object;
	}
	public void setObject(Object[] object) {
		this.object = object;
	}

b、编写Mapper接口方法

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	//根据id获取用户信息
	public User selectUserById(Integer id);
	
	//使用包装类，根据用户名模糊查询
	public List<User> selectUserByQuery(QueryVo queryVo);

	//使用包装类，数组
	public List<User> selectUserByQueryArray(QueryVo queryVo);
}

c、编写Mapper.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.mybatis.mapper.UserMapper">
	<!-- 使用包装类进行模糊查询 -->
	<select id="selectUserByQuery" parameterType="QueryVo" resultType="User">
		select * from user where username like '%${user.username}%'
	</select>
	
	<!-- 使用包装类查询    数组类型
		collection属性的值为包装类中数组的属性名称
	 -->
	<select id="selectUserByQueryArray" parameterType="QueryVo" resultType="User">
		select * from user where id in
		<foreach collection="object" item="id" open="(" close=")" separator=",">
			#{id}
		</foreach>
	</select>
</mapper>

d、编写junit测试类

package com.mybatis.junit;

import java.io.InputStream;
import java.util.List;

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.Before;
import org.junit.Test;

import com.mybatis.mapper.UserMapper;
import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

/**
 * junit单元测试类
 * 
 * @author Administrator
 *
 */
public class MybatisMapperDaoJunit {

	private SqlSessionFactory sqlSessionFactory = null;

	@Before
	public void testBefore() throws Exception {
		// 读取配置文件到输入流中
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		// 工厂构建类
		// 创建sqlSessionFactory工厂
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	}

	// 使用包装类进行模糊查询
	@Test
	public void testQueryArray() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		QueryVo queryVo = new QueryVo();
		Object[] object = new Object[2];
		object[0] = 1;
		object[1] = 29;
		queryVo.setObject(object);
		List<User> list = userMapper.selectUserByQueryArray(queryVo);
		for (User user1 : list) {
			System.out.println(user1);
		}
	}
}

4.当传递的参数类型为HashMap时

a、编写mapper接口文件

package com.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	//根据id获取用户信息
	public User selectUserById(Integer id);
	
	//使用包装类，根据用户名模糊查询
	public List<User> selectUserByQuery(QueryVo queryVo);

	//使用包装类   数组
	public List<User> selectUserByQueryArray(QueryVo queryVo);
	
	//使用hashMap封装参数
	public List<User> selectUsersByHashMap(Map<String, String> map);
}

b、编写mapper.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.mybatis.mapper.UserMapper">
	
	
	<!-- 使用hashMap封装参数 -->
	<select id="selectUsersByHashMap" parameterType="hashmap" resultType="User">
		select * from user where username like "%${username}%" and address = #{address}
	</select>
</mapper>

c、编写junit测试类

	//测试HashMap封装参数
	@Test
	public void testQueryHashMap(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		Map<String, String> map = new HashMap<String, String>();
		map.put("username", "明");
		map.put("address", "河南郑州");
		List<User> users = userMapper.selectUsersByHashMap(map);
		for (User user : users) {
			System.out.println(user);
		}
	}

5.当返回值类型为Integer时

a、编写Mapper接口文件中的方法

import java.util.List;
import java.util.Map;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	//根据id获取用户信息
	public User selectUserById(Integer id);
	
	//使用包装类，根据用户名模糊查询
	public List<User> selectUserByQuery(QueryVo queryVo);

	//使用包装类   数组
	public List<User> selectUserByQueryArray(QueryVo queryVo);
	
	//使用hashMap封装参数
	public List<User> selectUsersByHashMap(Map<String, String> map);
	
	//返回类型为Integer
	public Integer selectUsersCount(String username);
}

b、编写Mapper.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.mybatis.mapper.UserMapper">
	<!-- 返回结果类型为Integer -->
	<select id="selectUsersCount" parameterType="String" resultType="Integer">
		select count(1) from user where username like "%"#{username}"%"
	</select>
</mapper>

c、编写junit测试类

	//测试返回结果类型为Integer
	@Test
	public void testCount(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setUsername("明");
		int count = userMapper.selectUsersCount("明");
		System.out.println(count);
	}

6.使用resultMap手动映射pojo类属性和数据库表字段之间的关系

a、编写mapper接口方法

package com.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	//根据id获取用户信息
	public User selectUserById(Integer id);
	
	//使用包装类，根据用户名模糊查询
	public List<User> selectUserByQuery(QueryVo queryVo);

	//使用包装类   数组
	public List<User> selectUserByQueryArray(QueryVo queryVo);
	
	//使用hashMap封装参数
	public List<User> selectUsersByHashMap(Map<String, String> map);
	
	//返回类型为Integer
	public Integer selectUsersCount(String username);
	
	//使用resultMap手动映射
	public List<User> selectUserByUsername(String username);
}

b、编写mapper.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.mybatis.mapper.UserMapper">
	
	<!-- 手动映射resultMap -->
	<resultMap type="User" id="resultMapId">
		<result column="user_name" property="username"/>
		<result column="user_address" property="address"/>
	</resultMap>
	
	<!-- 当数据库表中查询出的字段与pojo类的属性名称不相同时使用resultMap进行手动映射 -->
	<select id="selectUserByUsername" parameterType="String" resultMap="resultMapId">
		select id,sex,username as user_name,address as user_address from user where username like "%"#{username}"%"
	</select>
</mapper>

c、编写junit测试类

//测试数据库表字段与pojo类属性名称不相同时
	@Test
	public void testResultMap(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		List<User> users = userMapper.selectUserByUsername("明");
		for (User user : users) {
			System.out.println(user);
		}
	}

7.使用动态sql --- < if >

a、编写mapper接口

package com.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	
	// 动态sql查询， 使用if
	public List<User> selectUsersByIf(Map<String, String> map);
}

b、编写mapper.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.mybatis.mapper.UserMapper">

	<!-- 动态sql查询之   if -->
	<select id="selectUsersByIf" parameterType="hashMap" resultType="User">
		select * from user
		<where> <!-- 用where标签时，当第一个if不执行时可以自动去除第二个if标签中语句的 and ，即where标签可以自动去除第一个and -->

			<if test="username != null and username !=''"><!-- 进行判断的username是hashMap中的key -->
				and username like "%"#{username}"%"
			</if>
			<if test="address != null and address != ''"><!-- 进行判断的address是hashMap中的key -->
				and address = #{address}
			</if>
		</where>
	</select>
</mapper>

c、编写junit测试类

	//测试动态sql中的if的使用
	@Test
	public void testDynamicSQLWithIf(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		Map<String, String> map = new HashMap<String, String>();
		map.put("username", "明");
		map.put("address", "河南郑州");
		List<User> usersByIf = userMapper.selectUsersByIf(map);
		for (User user : usersByIf) {
			System.out.println(user);
		}
	}

8.动态sql --- < foreach > 当传递的参数直接为数组时

a、编写mapper接口

package com.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {

	
	//参数直接是array数组时
	public List<User> selectUsersByIdArray(Integer[] ids);
}

b、编写mapper.xml映射文件

	<!-- 动态sql查询之   入参为Integer数组
		入参为Integer数组时parameterType属性的值为Integer或者Integer[]都可以
	 -->
	<select id="selectUsersByIdArray" parameterType="Integer" resultType="User">
		select * from user 
		<where>
			id in 
			<foreach collection="array" item="id" open="(" close=")" separator=",">
				<!-- 当入参直接是数组时，collection属性的值应该写为array -->
				#{id}
			</foreach>
		</where>
	</select>

c、编写junit测试类

	//测试动态sql中的foreach
	@Test
	public void testDynamicForEach(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		Integer[] ids = new Integer[]{1,29};
		List<User> users = userMapper.selectUsersByIdArray(ids);
		for (User user : users) {
			System.out.println(user);
		}
		
	}

9.动态sql，当传递的参数为list集合时

a、编写mapper接口

package com.mybatis.mapper;

import java.util.List;
import java.util.Map;

import com.mybatis.pojo.QueryVo;
import com.mybatis.pojo.User;

public interface UserMapper {
	
	//参数为list集合
	public List<User> selectUsersByList(List<Integer> ids);
}

b、编写mapper.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.mybatis.mapper.UserMapper">

	<!-- 动态sql查询之   入参为list集合
		入参为list集合时，collection属性的值为list
	 -->
	 <select id="selectUsersByList" parameterType="Integer" resultType="User">
	 	select * from user
	 	<where>
	 		id in 
	 		<foreach collection="list" item="id" open="(" close=")" separator=",">
	 			#{id}
	 		</foreach>
	 	</where>
	 </select>
	
</mapper>

c、编写junit测试类

	//动态测试-- 入参类型为list
	@Test
	public void testDynamicForEachWithList(){
		SqlSession sqlSession = sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1);
		ids.add(29);
		List<User> users = userMapper.selectUsersByList(ids);
		for (User user : users) {
			System.out.println(user);
		}
	}

10.sql片段

mapper.xml配置文件中的书写方式

<!-- 使用sql片段 -->
	<sql id="selectUser">
		select * from user
	</sql>
	<!-- 动态sql查询之   入参为list集合
		入参为list集合时，collection属性的值为list
	 -->
	 <select id="selectUsersByList" parameterType="Integer" resultType="User">
	 	<include refid="selectUser"></include>
	 	<where>
	 		id in 
	 		<foreach collection="list" item="id" open="(" close=")" separator=",">
	 			#{id}
	 		</foreach>
	 	</where>
	 </select>