Mybatis数据访问传参

Mybatis数据访问传参

• 简单数据类型入参
• 对象入参
• Map集合入参

配置文件:

<?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>

	<!--1. 配置数据源driver、url、username、password -->
	<!--  resource优先级高-->
	<properties resource="database.properties">

		 <property name="driver" value="com.mysql.jdbc.Driver"/>
          <!-- 连接地址 -->
          <property name="url" value="jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&amp;characterEncoding=utf-8" />
          <!-- 用户名 -->
          <property name="username" value="root"/>
          <!-- 密码 -->
          <property name="password" value="root"/>

	</properties>
	<!-- 2. 配置MyBatis运行全局设置 -->
	<settings>
	<setting name="lazyLoadingEnabled" value="false"/>
	</settings>
	
	
	<!--3. 配置别名 -->
	<typeAliases>
		<typeAlias type="com.oupeng.pojo.User" alias="User" />  
		<!--  <package name="com.oupeng.pojo"/>  -->
	</typeAliases>
	<!-- 4. 配置开发环境-->
	<environments default="development">
	  <!-- 配置第一个开发环境 -->
	<environment id="development">
			<!--事务的管理 -->
			<transactionManager type="JDBC"></transactionManager>
			<!-- 数据源的管理 -->
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	<!-- 配置测试开发环境 -->
		<environment id="test">
			<transactionManager type=""></transactionManager>
			<dataSource type=""></dataSource>
		</environment>
	</environments>
	<!-- 加载接口的映射文件 -->
       <mappers>
       
          <mapper resource="com//oupeng//user//dao//UserMapper.xml"/> 
         <!-- <mapper url="file:///c:/UserMapper.xml"/>-->
          
       </mappers>
</configuration>

实体类

package com.oupeng.pojo;

import java.util.Date;

public class User {
	private String userCode; //账号
	public String getUserCode() {
		return userCode;
	}
	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}
	private String userName; //姓名
	private String userPassword; //密码
	private Integer gender; //性别
	private Date birthday; //生日
	private String phone; //联系方式
	private String  address; //联系地址
	private Integer userRole;//角色编号
	private Date creationDate ;//创建日期 
	private Integer createdBy; //创建人
	private  Integer modifyBy;  //修改人
	public Integer getModifyBy() {
		return modifyBy;
	}
	public void setModifyBy(Integer modifyBy) {
		this.modifyBy = modifyBy;
	}
	public Date getModifyDate() {
		return modifyDate;
	}
	public void setModifyDate(Date modifyDate) {
		this.modifyDate = modifyDate;
	}
	private Date modifyDate;  //修改日期
	private String idPicPath;  //身份照
	
	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 Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date 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 Date getCreationDate() {
		return creationDate;
	}
	public void setCreationDate(Date creationDate) {
		this.creationDate = creationDate;
	}
	public Integer getCreatedBy() {
		return createdBy;
	}
	public void setCreatedBy(Integer createdBy) {
		this.createdBy = createdBy;
	}
	
	public String getIdPicPath() {
		return idPicPath;
	}
	public void setIdPicPath(String idPicPath) {
		this.idPicPath = idPicPath;
	}
	public String getWorkPicPath() {
		return workPicPath;
	}
	public void setWorkPicPath(String workPicPath) {
		this.workPicPath = workPicPath;
	}
	private String workPicPath;// 证件照
}

范例：查询用户表中指定姓用户：（简单数据类型入参）
1.接口方法

//查询用户表中指定姓用户
	public List<User> getUserListByUserName(String userName);

2.配置映射文件

<!-- //查询用户表中指定姓用户 -->
  <select id="getUserListByUserName" resultType="User" parameterType="String">
  select * from smbms_user where userName like concat('%',#{userName1},'%')
  <!-- concat()是mysql中字符串连接的函数  parameterType属性是接收参数的-->
  </select>

3、测试

@Test
    public void test3(){
    	//打开SqlSession
    	SqlSession sqlSession=SqlSessionUtil.creatSqlSession();
    	List<User> list=sqlSession.selectList("com.oupeng.user.dao.UserMapper.getUserListByUserName", "赵");
    	//遍历结果集
    	for(User u:list) {
    		System.out.println(u.getUserName()+"\t"+u.getUserPassword()+"\t"+u.getAddress());
    	}
    	//关闭SqlSession
    	SqlSessionUtil.closSqleSession(sqlSession);
    }

范例：对象入参，进行模糊查询：（对象入参）
1、接口方法

public List<User> getUserListByObject(User user);

2、映射文件

<!-- 对象入参 -->
  <select id="getUserListByObject" resultType="User" parameterType="User">
  select * from smbms_user where userName like concat('%',#{userName1},'%')
  <!-- 对象传参的时候#{内容}必须和对象里面的属性保持一致 -->
  </select>

测试

 @Test
    public void test4(){
    	//打开SqlSession
    	SqlSession sqlSession=SqlSessionUtil.creatSqlSession();
    	List<User> list=sqlSession.selectList("com.oupeng.user.dao.UserMapper.getUserListByObject", "赵");
    	//遍历结果集
    	for(User u:list) {
    		System.out.println(u.getUserName()+"\t"+u.getUserPassword()+"\t"+u.getAddress());
    	}
    	//关闭SqlSession
    	SqlSessionUtil.closSqleSession(sqlSession);
    }
    }

范例：map集合入参，进行模糊查询
1、接口方法

//Map入参
	public List<User> getUserListByMap(Map<String,String> map);
}

2、映射文件

<!-- Map入参 -->
	<select id="getUserListByMap" resultType="User" parameterType="Map">
	select * from smbms_user where userName like concat('%',#{uName},'%') and gender=#{uGender}
	</select>

测试

 @Test
	public void test5() {
		// 打开sqlSession
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();

	
		Map map=new HashMap();
		map.put("uName", "赵");
		map.put("uGender", 1);
		List<User> list=sqlSession.getMapper(UserMapper.class).getUserListByMap(map);
		// 遍历结果集
		for (User u : list) {
			System.out.println(u.getUserName() + "\t" + u.getUserPassword()
					+ "\t" + u.getAddress());
		}
		// 关闭sqlSession
		SqlSessionUtil.closSqleSession(sqlSession);

	}

范例：数组入参
1.接口

//数组入参
  	public List<User> getUserListByArray(Integer[] roleIds);

2、映射

<!-- 数组入参 -->
  <select id="getUserListByArray" resultMap="userResult">
  select * from smbms_user where userRole in 
  <foreach collection="array" item="roleIds" open="(" separator="," close=")">
   #{roleIds}
  </foreach>
     
  </select>
  <resultMap type="User" id="userResult">
  <id property="id" column="id"></id>
  <result property="userName" column="userName" />
  
  </resultMap>

3.测试

@Test
	public void test20(){  
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();
		//要查询的角色数组集合
		Integer roleIds[]= {1,2};
		List<User> list=sqlSession.getMapper(UserMapper.class).getUserListByArray(roleIds);
		for(User u:list) {
			System.out.println(u.getUserName()+"\t"+u.getUserPassword());
		}
		// 关闭sqlSession
				SqlSessionUtil.closSqleSession(sqlSession);
	}

范例：集合入参
1.接口

	//集合入参
  	public List<User> getUserListByList(List<Integer> aa);

2.映射

<!-- 集合入参 -->
  <select id="getUserListByList" resultMap="userResult">
  select * from smbms_user where userRole in
  <foreach collection="list" item="aa" open="(" separator="," close=")">
  #{aa}
  </foreach>

测试

@Test
	public void test21(){  
		SqlSession sqlSession = SqlSessionUtil.creatSqlSession();
		//要查询的角色集合
		List<Integer> roleIds=new ArrayList();
		roleIds.add(1);
		roleIds.add(2);
		List<User> list=sqlSession.getMapper(UserMapper.class).getUserListByList(roleIds);
		for(User u:list) {
			System.out.println(u.getUserName()+"\t"+u.getUserPassword());
		}
		// 关闭sqlSession
				SqlSessionUtil.closSqleSession(sqlSession);
	}