Mybatis 基础

id	username	birthday	sex	address
1	王五		2	
10	张三	2014-07-10	1	北京市
16	张小明		1	河南郑州
22	陈小明		1	河南郑州
24	张三丰		1	河南郑州
25	陈小明		1	河南郑州
26	王五			
27	东哥hm19			宝盛西里24号楼

<?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">
<!-- mybatis的主配置文件 -->
<configuration>
    <!-- 配置环境 -->
    <environments default="mysql">
        <!-- 配置mysql的环境-->
        <environment id="mysql">
            <!-- 配置事务的类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源（连接池） -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的4个基本信息 -->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 指定映射配置文件的位置，映射配置文件指的是每个dao独立的配置文件 -->
    <mappers>

          <package name="com.mapper"></package>
    </mappers>
</configuration>

package com.domain;

import java.util.Date;

public class User {

	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 + ", address=" + address + "]";
	}
}

package com.domain;

import java.util.List;

public class UserQueryVO {
	private User user;// 用户信息
	private List<Integer> idList;// 商品ID集合
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public List<Integer> getIdList() {
		return idList;
	}
	public void setIdList(List<Integer> idList) {
		this.idList = idList;
	}
	@Override
	public String toString() {
		return "UserQueryVO [user=" + user + ", idList=" + idList + "]";
	}
}

package com.mapper;

import java.util.List;

import com.domain.User;
import com.domain.UserQueryVO;

public interface UserMapper {
	// 1、 根据用户ID查询用户信息
	public User findUserById(int id) throws Exception;

	// 3、 添加用户
	public void insertUser(User user) throws Exception;
	
	//综合查询
	public List<User> findUserList(UserQueryVO vo);
	
	//综合查询用户总数
	public int findUserCount(UserQueryVO vo);
	
	//resultMap入门
	public User findUserRstMap(int id);
}

<?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.mapper.UserMapper">
	<!-- 根据id查询用户 -->
	<select id="findUserById" parameterType="java.lang.String"
		resultType="com.domain.User">
		SELECT * FROM USER WHERE id = #{uid}
	</select>
	
	<!-- 添加用户 -->
	<insert id="insertUser" parameterType="com.domain.User">
		<selectKey keyProperty="id" resultType="int" order="AFTER">
			SELECT
			LAST_INSERT_ID()
		</selectKey>

		INSERT INTO USER
		(username,birthday,sex,address)
		VALUES(#{username},#{birthday},#{sex},#{address})
	</insert>
	
	<!-- 定义sql片段 -->
	<!-- sql片段内，可以定义sql语句中任何部分 -->
	<!-- sql片段内，最好不用将where和select关键字声明在内 -->
	<sql id="whereClause">
		<!-- if标签：可以对输入的参数进行判断 -->
		<!-- test:指定判断表达式 -->
		<if test="user != null">
			<if test="user.username != null and user.username != ''">
				AND username LIKE '%${user.username}%'
			</if>
			<if test="user.sex != null and user.sex != ''">
				AND sex = #{user.sex}
			</if>
		</if>

		<if test="idList != null">
			<!-- AND id IN (#{id},#{id},#{id}) -->

			<!-- collection：表示pojo中集合属性的属性名称 -->
			<!-- item:为遍历出的结果声明一个变量名称 -->
			<!-- open：遍历开始时，需要拼接的字符串 -->
			<!-- close:遍历结束时，需要拼接的字符串 -->
			<!-- separator：遍历中间需要拼接的连接符 -->
			AND id IN
			<foreach collection="idList" item="id" open="(" close=")"
				separator=",">
				#{id}
			</foreach>
		</if>
	</sql>

	<!-- 综合查询，查询用户列表 -->
	<select id="findUserList" parameterType="com.domain.UserQueryVO"
		resultType="com.domain.User">
		SELECT * FROM user
		<!-- where标签：默认去掉后面第一个AND，如果没有参数，则把自己干掉 -->
		<where>
			<!-- 引入sql片段 -->
			<include refid="whereClause" />
		</where>
	</select>

	<!-- 综合查询用户总数 -->
	<select id="findUserCount" parameterType="com.domain.UserQueryVO"
		resultType="int">
		SELECT count(*) FROM user
		<!-- where标签：默认去掉后面第一个AND，如果没有参数，则把自己干掉 -->
		<where>
			<!-- 引入sql片段 -->
			<include refid="whereClause" />
		</where>
	</select>
</mapper>

package com.test;

import java.io.InputStream;
import java.util.ArrayList;
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.After;
import org.junit.Before;
import org.junit.Test;

import com.domain.User;
import com.domain.UserQueryVO;
import com.mapper.UserMapper;

public class MapperTest {
	private InputStream in;
	private SqlSession sqlSession;
	private UserMapper userMapper;

	@Before // 用于在测试方法执行之前执行
	public void init() throws Exception {
		// 1.读取配置文件，生成字节输入流
		in = Resources.getResourceAsStream("mybatisConfig.xml");
		// 2.获取SqlSessionFactory
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
		// 3.获取SqlSession对象
		sqlSession = factory.openSession();
		// 4.获取dao的代理对象
		userMapper = sqlSession.getMapper(UserMapper.class);
	}

	@After // 用于在测试方法执行之后执行
	public void destroy() throws Exception {
		// 提交事务
		sqlSession.commit();
		// 6.释放资源
		sqlSession.close();
		in.close();
	}

	/**
	 * 根据id查询用户
	 * @throws Exception 
	 */
	@Test
	public void testfindUserById() throws Exception {
		// 5.执行查询所有方法
		User user = userMapper.findUserById(10);
		System.out.println(user);
	}
	
	@Test
	public void testinsertUser() throws Exception{
		
		User user = new User();
		user.setUsername("东哥hm19");
		user.setAddress("宝盛西里24号楼");
		
		System.out.println("保存操作之前：" + user);
		userMapper.insertUser(user);
		System.out.println("保存操作之前：" + user);
	}
	
	@Test
	public void testFindUserList() throws Exception {

		UserQueryVO vo = new UserQueryVO();
		
		List<Integer> idList = new ArrayList<>();
		idList.add(1);
		idList.add(16);
		idList.add(10);
		
		vo.setIdList(idList);

		List<User> list = userMapper.findUserList(vo);
		int count = userMapper.findUserCount(vo);

		System.out.println(list);
		System.out.println(count);
	}
}