package com.peng.mybatis.mapper;
import com.peng.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface ParameterMapper {
/**
* 验证登录(使用@Param)
*/
User checkLoginByParam(@Param("username") String username, @Param("password") String password);
/**
* 添加用户信息
*/
int insertUser(User user);
/**
* 验证登录(参数为map)
*/
User checkLoginByMap(Map<String, Object> map);
/**
* 验证登录
*/
User checkLogin(String username, String password);
/**
* 根据用户名查询用户信息
*/
User getUserByUsername(String username);
/**
* 查询所有的员工信息
*/
List<User> getAllUser();
}
package com.peng.mybatis.test;
import com.peng.mybatis.mapper.ParameterMapper;
import com.peng.mybatis.pojo.User;
import com.peng.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ParameterMapperTest {
/**
* MyBatis获取参数值的两种方式:${}和#{}
* ${}本质字符串拼接
* #{}本质占位符赋值
* MyBatis获取参数值的各种情况
* 1.mapper接口方法的参数为单个的字面量类型
* 可以用通过${}和#{}以任意的字符串获取参数值,但是需要注意${}的单引号问题
* 2.mapper接口方法的参数为多个时
* 此时MyBatis会将这些参数放在一个map集合中,以两种方式进行存储
* a>以arg0,arg1...为键,以参数为值
* b>以param1,param2...为键,以参数为值
* 因此只需要通过#{}和${}以键的方式访问值即可,但是需要注意${}的单引号问题
* 3.若mapper接口方法的参数有多个时,可以手动将这些参数放在一个map中存储
* 只需要通过#{}和${}以键的方式访问值即可,但是需要注意${}的单引号问题
* 4.mapper接口方法的参数是实体类类型的参数
* 只需要通过#{}和${}以属性的方式访问属性值即可,但是需要注意${}的单引号问题
* 5.使用@Param命名参数
* 此时MyBatis会将这些参数放在一个map集合中,以两种方式进行存储
* a>以@param注解的值为键,以参数为值
* b>以param1,param2...为键,以参数为值
* 因此只需要通过#{}和${}以键的方式访问值即可,但是需要注意${}的单引号问题
*/
@Test
//查询用户名字
public void testGetUserByUsername(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
User user = mapper.getUserByUsername("admin");
System.out.println(user);
}
@Test
//查询用户账号和密码
public void testCheckLogin(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
User user = mapper.checkLogin("admin","123456");
System.out.println(user);
}
@Test
//查询所有用户信息
public void testAllUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
List<User> list = mapper.getAllUser();
list.forEach(user -> System.out.println(user));
}
@Test
//登录用户
public void checkLoginByMap(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("username","admin");
map.put("password","123456");
User user = mapper.checkLoginByMap(map);
System.out.println(user);
}
@Test
//登录用户
public void checkLoginByParam(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
User user = mapper.checkLoginByParam("admin","123456");
System.out.println(user);
}
@Test
//添加用户信息
public void insertUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
ParameterMapper mapper = sqlSession.getMapper(ParameterMapper.class);
int result = mapper.insertUser(new User(null, "李四", "123", "18","123@qq.com"));
System.out.println(result);
}
}
<?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.peng.mybatis.mapper.ParameterMapper">
<!--List<User> getAllUser();-->
<select id="getAllUser" resultType="User">
select * from t_user
</select>
<!--User getUserByUsername(String username);-->
<select id="getUserByUsername" resultType="User">
<!--select * from t_user where username = #{username}-->
select * from t_user where username = '${username}'
</select>
<!--User checkLogin(String username, String password);-->
<select id="checkLogin" resultType="User">
<!--select * from t_user where username = #{arg0} and password = #{arg1}-->
select * from t_user where username = '${arg0}' and password = '${arg1}'
</select>
<!--User checkLoginByMap(Map<String, Object> map);-->
<select id="checkLoginByMap" resultType="User">
<!--select * from t_user where username = #{arg0} and password = #{arg1}-->
select * from t_user where username = #{username} and password = #{password}
</select>
<!--int insertUser(User user);-->
<insert id="insertUser">
insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})
</insert>
<!--User checkLoginByParam(@Param("username") String username, @Param("password") String password);-->
<select id="checkLoginByParam" resultType="User">
select * from t_user where username = #{username} and password = #{password}
</select>
</mapper>
package com.peng.mybatis.mapper;
import com.peng.mybatis.pojo.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
public interface SelectMapper {
/**
* 根据id查询用户信息
*/
User getUserById(@Param("id") Integer id);
/**
* 查询所有的用户信息
*/
List<User> getAllUser();
/**
* 查询用户信息的总记录数
*/
Integer getCount();
/**
* 根据id查询用户信息为一个map集合
*/
Map<String, Object> getUserByIdToMap(@Param("id") Integer id);
/**
* 查询所有用户信息为map集合
*/
/* List<Map<String, Object>> getAllUserToMap();*/
@MapKey("id")
Map<String, Object> getAllUserToMap();
}
<?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.peng.mybatis.mapper.SelectMapper">
<!--User getUserById(@Param("id") Integer id);-->
<select id="getUserById" resultType="User">
select * from t_user where id = #{id}
</select>
<!--List<User> getAllUser();-->
<select id="getAllUser" resultType="User">
select * from t_user
</select>
<!--Integer getCount();-->
<select id="getCount" resultType="java.lang.Integer">
select count(*) from t_user
</select>
<!--Map<String, Object> getUserByIdToMap(@Param("id") Integer id);-->
<select id="getUserByIdToMap" resultType="map">
select * from t_user where id = #{id}
</select>
<!--List<Map<String, Object>> getAllUserToMap();-->
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
</mapper>
package com.peng.mybatis.test;
import com.peng.mybatis.mapper.SelectMapper;
import com.peng.mybatis.pojo.User;
import com.peng.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
import java.util.Map;
public class SelectMapperTest {
/**
* MyBatis的各种查询功能
* 1、若查询出的数据只有一条
* a>可以通过实体类对象接收
* b>可以通过list集合接收
* c>可以通过map结合接收
* 结果:{password=123456, sex=男, id=3, age=23, email=12345@qq.com, username=admin}
* 2、若查询出的数据有多条
* a>可以通过List集合接收
* b>可以通过map结合接收
* c>可以在mapper接口的方法上添加@Mapper注解,此时就可以将每条数据转换的map集合作为值,以某个字段的值作为键,放在同一个map集合中
* 注意:一定不能通过实体类对象接收,此时会抛异常TooManyResultsException
*
* MyBatis中设置了默认的类型别名
*/
@Test
//查询一条用户信息
public void testGetUserById(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
System.out.println(mapper.getUserById(3));
}
@Test
//查询所有用户信息
public void testGetAllUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User> list = mapper.getAllUser();
list.forEach(user -> System.out.println(user));
}
@Test
//查询用户数量
public void testGetCount(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
System.out.println(mapper.getCount());
}
@Test
//根据id查询用户信息为一个map集合
public void testGetUserByIdToMap(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
System.out.println(mapper.getUserByIdToMap(3));
}
@Test
//查询所有用户信息为map集合
public void testGetAllUserToMap(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
/*List<Map<String,Object>> list = mapper.getAllUserToMap();
list.forEach(user -> System.out.println(user));*/
System.out.println(mapper.getAllUserToMap());
}
}
模糊查询的三种方式
select * from t_user where username like '%${username}%'
select * from t_user where username like concat('%',#{username},'%')
select * from t_user where username like "%"#{username}"%"
建议使用第三种