Mapper动态代理

目录

1.通过id查询记录（SELECT * FROM user WHERE id =#{id}）

2.通过名字模糊查询用户列表（SELECT * FROM user WHERE username like "%"#{name}"%"）

3.查询记录数量（SELECT count(*) from user）

4.手动映射（此时要用resultMap,而不是resultType了）-模糊查询

---

1.通过id查询记录（SELECT * FROM user WHERE id =#{id}）

User.java:

package com.sikiedu.bean;

public class User {
	private Integer id;
	private String username;
	private String password;
	
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
	}
	
	
}

UserMapper.java:

package mapper;

import com.sikiedu.bean.User;

public interface UserMapper {

	public User selectUserById(Integer id);
}

UserMapper.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="mapper.UserMapper">
 
	<select id="selectUserById" parameterType="Integer" resultType="com.sikiedu.bean.User">
		SELECT * FROM user WHERE id =#{id}
	</select>
</mapper>

MapperTest.java:

package test;

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

import com.sikiedu.bean.User;

import mapper.UserMapper;

public class MapperTest {

	@Test
	public void Test() throws IOException{
		String resource="sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
		SqlSessionFactory ssf=ssfb.build(in);
		SqlSession session  = ssf.openSession();
		
		UserMapper mapper = session.getMapper(UserMapper.class);
		User user = mapper.selectUserById(1);
		System.out.println(user);

	}
}

运行结果：

2.通过名字模糊查询用户列表（SELECT * FROM user WHERE username like "%"#{name}"%"）

MapperTest.java:

	@Test
	public void Test2() throws IOException{
		String resource="sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
		SqlSessionFactory ssf=ssfb.build(in);
		SqlSession session  = ssf.openSession();
		
		UserMapper mapper = session.getMapper(UserMapper.class);
		List<User> user = mapper.selectUserByName("李");
		for (User u : user) {
			System.out.println(u);
		}
	}

UserMapper.java:

package mapper;


import java.util.List;

import com.sikiedu.bean.User;

public interface UserMapper {

//	public User selectUserById(Integer id);
	
	public List<User> selectUserByName(String name);
}

UserMapper.xml:

	<!-- 	${}字符串拼接  这里不推荐 -->
<!-- 	#{}占位符 推荐 -->
	<select id="selectUserByName" parameterType="String" resultType="com.sikiedu.bean.User">
<!-- 		SELECT * FROM user WHERE username like '%${value}%' -->
			SELECT * FROM user WHERE username like "%"#{name}"%"
	</select>

运行结果：

3.查询记录数量（SELECT count(*) from user）

MapperTest.java:

	@Test
	public void Test4() throws Exception{
		String resource="sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
		SqlSessionFactory ssf=ssfb.build(in);
		SqlSession session  = ssf.openSession();
		
		UserMapper mapper = session.getMapper(UserMapper.class);
		Integer count = mapper.selectUserCount();
		System.out.println(count);
	}

UserMapper.java:

public Integer selectUserCount();

UserMapper.xml:

<select id="selectUserCount" resultType="Integer">
		SELECT count(*) from user
	</select>

运行结果：

4.手动映射（此时要用resultMap,而不是resultType了）-模糊查询

如果数据库中每列的字段名和bean中对象中的属性名相同，那么系统会自动映射，此时可以使用resultType。

如果数据库中某列的字段名和bean中对象中的属性名不同，那么系统不会自动映射该属性，此时就不能使用resultType了，此时给该属性使用resultMap来手动映射就可以了。

User2.java:

package com.sikiedu.bean;

public class User2 {

	private Integer id;
	private String u_username;//此属性名和数据库中的字段名不相同，需手动映射
	private String password;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getU_username() {
		return u_username;
	}
	public void setU_username(String u_username) {
		this.u_username = u_username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public String toString() {
		return "User2 [id=" + id + ", u_username=" + u_username + ", password=" + password + "]";
	}
	
	
}

User2Mapper.java:

package com.sikiedu.mapper;

import java.util.List;

import com.sikiedu.bean.User2;

public interface User2Mapper {

	public List<User2> selectUserByName(String name);
}

User2Mapper.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.sikiedu.mapper.User2Mapper">

	<resultMap type="com.sikiedu.bean.User2" id="user2">
		<result property="u_username" column="username"/>
	</resultMap>
	<select id="selectUserByName" resultMap="user2">
<!-- 		SELECT * FROM user WHERE username like '%${value}%' -->
			SELECT * FROM user WHERE username like "%"#{name}"%"
	</select>

</mapper>

Mapper2Test.java:

package test;

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.Test;

import com.sikiedu.bean.User2;
import com.sikiedu.mapper.User2Mapper;

public class Mapper2Test {

	@Test
	public void Test() throws Exception{
		String resource="sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
		SqlSessionFactory ssf=ssfb.build(in);
		SqlSession session  = ssf.openSession();
		
		User2Mapper mapper = session.getMapper(User2Mapper.class);
		
		List<User2> user = mapper.selectUserByName("李");
		for (User2 u : user) {
			System.out.println(u);
		}
	}
}

运行结果：