一. 查询一条数据
①可以通过实体类对象接收;
②可以通过List集合接收;
③可以通过Map集合接收
package mapper;
import org.apache.ibatis.annotations.Param;
import pojo.User;
import java.util.List;
import java.util.Map;
public interface SelectMapper {
//通过实体类对象接收
User queryUserToObject(@Param("username") String username);
//通过List集合接收
List<User> queryUserToList(@Param("username") String username);
//通过Map集合接收
Map<String, Object> queryUserToMap(@Param("username") String username);
}
<?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.SelectMapper"> <!--为mapper接口的全类名-->
<select id="queryUserToObject" resultType="User">
select * from user where username=#{username}
</select>
<select id="queryUserToList" resultType="User">
select * from user where username=#{username}
</select>
<select id="queryUserToMap" resultType="map">
select * from user where username=#{username}
</select>
</mapper>
import mapper.SelectMapper;
import mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import utils.SqlSessionUtils;
import java.util.List;
import java.util.Map;
public class SelectMapperTest {
@Test
public void testQueryUserToObject() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
User user = mapper.queryUserToObject("王二");
System.out.println(user); //User{id=7, username='王二', password='1357', age=22, sex=男, email='Wang@qq.com'}
}
@Test
public void testQueryUserToList() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
List<User> list = mapper.queryUserToList("王二");
System.out.println(list);; //[User{id=7, username='王二', password='1357', age=22, sex=男, email='Wang@qq.com'}]
}
@Test
public void testQueryUserToMap() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
Map<String,Object> user = mapper.queryUserToMap("王二");
System.out.println(user); //{password=1357, sex=男, id=7, age=22, email=Wang@qq.com, username=王二}
}
}
二. 查询多条数据
①可以通过实体类类型的List集合接收;
②可以通过Map类型的List集合接收;
③可以在Mapper接口的方法上添加@MapKey注解,此时结果以注解中设置的内容作为键,以每条数据转换成的map集合作为值,放在一个Map集合中。
package mapper;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;
import pojo.User;
import java.util.List;
import java.util.Map;
public interface SelectMapper {
//通过实体类类型的List集合接收
List<User> queryUserByObjectToList();
//通过通过Map类型的List集合接收
List<Map<String, Object>> queryUserByMapToList();
//使用注解,结果以注解中设置的内容作为键,以每条数据转换的map集合作为值
@MapKey("id")
Map<String, Object> queryUserByMapKeyToMap();
}
<?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.SelectMapper"> <!--为mapper接口的全类名-->
<select id="queryUserByObjectToList" resultType="User">
select * from user
</select>
<select id="queryUserByMapToList" resultType="map">
select * from user
</select>
<select id="queryUserByMapKeyToMap" resultType="map">
select * from user
</select>
</mapper>
import mapper.SelectMapper;
import mapper.UserMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import utils.SqlSessionUtils;
import java.util.List;
import java.util.Map;
public class SelectMapperTest {
@Test
public void testQueryUserToObject() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
List<User> list = mapper.queryUserByObjectToList();
System.out.println(list);
}
@Test
public void testQueryUserToList() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
List<Map<String,Object>> list = mapper.queryUserByMapToList();
System.out.println(list);
}
@Test
public void testQueryUserToMap() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
Map<String,Object> map = mapper.queryUserByMapKeyToMap();
System.out.println(map);
}
}
三. 模糊查询
①使用#{ }:select * from user where username like #{username}"%" (建议使用此方式)
②使用${ }:select * from user where username like '${username}%'
package mapper;
import org.apache.ibatis.annotations.Param;
import pojo.User;
import java.util.List;
public interface SelectMapper {
//根据用户名查询
List<User> queryUserByLikeName(@Param("username") String username);
}
<?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.SelectMapper"> <!--为mapper接口的全类名-->
<select id="queryUserByLikeName" resultType="User">
<!-- select * from user where username like '${username}%'-->
select * from user where username like #{username}"%"
</select>
</mapper>
import mapper.SelectMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import utils.SqlSessionUtils;
import java.util.List;
public class SelectMapperTest {
@Test
public void testQueryUserByLikeName() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
List<User> list = mapper.queryUserByLikeName("李"); //查询姓李的用户
System.out.println(list);
}
}
四. 批量删除
只能使用${ }方式实现批量删除:delete from user where id in (${ids})
package mapper;
import org.apache.ibatis.annotations.Param;
public interface SelectMapper {
//批量删除
void deleteMoreUser(@Param("ids") String ids);
}
<?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.SelectMapper"> <!--为mapper接口的全类名-->
<delete id="deleteMoreUser">
delete from user where id in (${ids})
</delete>
</mapper>
import mapper.SelectMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import utils.SqlSessionUtils;
public class SelectMapperTest {
@Test
public void testDeleteMoreUser() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
mapper.deleteMoreUser("1,2,3");
}
}
五. 根据表名查询
只能使用${ }方式实现动态设置表名:
package mapper;
import org.apache.ibatis.annotations.Param;
import pojo.User;
import java.util.List;
public interface SelectMapper {
//批量删除
List<User> queryUserByTableName(@Param("tableName") String tableName);
}
<?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.SelectMapper"> <!--为mapper接口的全类名-->
<select id="queryUserByTableName" resultType="User">
select * from ${tableName}
</select>
</mapper>
import mapper.SelectMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import utils.SqlSessionUtils;
import java.util.List;
public class SelectMapperTest {
@Test
public void testQueryUserByTableName() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
//调用方法操作数据库
List<User> list = mapper.queryUserByTableName("user");
System.out.println(list);
}
}
六. 获取添加功能自增的主键
往数据库添加用户信息之前,没有分配id的值,当执行完添加操作以后,id自增1,然后可以获取主键的值。
package mapper;
import pojo.User;
public interface SelectMapper {
//添加用户信息
void insertUser(User user);
}
<?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.SelectMapper"> <!--为mapper接口的全类名-->
<!--useGeneratedKeys:表示当前标签中的sql是否使用了自增的主键,
keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into user values(null,#{username},#{password},#{age},#{sex},#{email})
</insert>
</mapper>
import mapper.SelectMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.User;
import utils.SqlSessionUtils;
public class SelectMapperTest {
@Test
public void testInsertUser() {
//获取SqlSession
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
//获取Mapper接口(每个Mapper接口 对应 一个实体类 对应 一张表)
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User user = new User(null,"张三","112345",22,'男',"Zhang@163.com");
System.out.println(user); //User{id=null, username='张三', password='112345', age=22, sex=男, email='Zhang@163.com'}
mapper.insertUser(user);
System.out.println(user); //User{id=8, username='张三', password='112345', age=22, sex=男, email='Zhang@163.com'}
}
}