MyBatis增删改查之查询操作
1.查询所有
Mapper接口
public interface BrandMapper {
/**
* 查询所有
*/
List<Brand> selectAll();
}SQL语句
<?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.itheima.mapper.BrandMapper">
<select id="selectAll" resultType="brand">
select *
from tb_brand;
</select>
</mapper>测试
public class MyBrandTest {
@Test
public void testselectAll() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
List<Brand> brands = brandMapper.selectAll();
System.out.println(brands);
sqlSession.close();
}
}
2.查询详情
Mapper接口
public interface BrandMapper {
/*
查看详情
*/
Brand selectById( int id);
}SQL语句
<?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.itheima.mapper.BrandMapper">
<select id="selectById" resultType="brand">
select * from tb_brand where id = #{id};
</select>
</mapper>测试
public class MyBrandTest {
@Test
public void testselectById() throws IOException {
int id=1;
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//获取SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
Brand brand = brandMapper.selectById(id);
System.out.println(brand);
sqlSession.close();
}
}3.多条件查询
Mapper接口
public interface BrandMapper {
/*
方法一:使用 @Param("参数名称") 标记每一个参数，在映射配置文件中就需要使用 #{参数名称} 进行占位
*/
List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
/*
方法二:将多个参数封装成一个 实体对象 ，将该实体对象作为接口的方法参数。该方式要求在映射配置文件的SQL中使用 #{内容} 时，里面的内容必须和实体类属性名保持一致。
*/
List<Brand> selectByConditionSingle(Brand brand);
/*
方法三:将多个参数封装到map集合中，将map集合作为接口的方法参数。该方式要求在映射配置文件的SQL中使用 #{内容}时，里面的内容必须和map集合中键的名称一致。
*/
List<Brand> selectByCondition(Map map);
}SQL语句
<?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.itheima.mapper.BrandMapper">
<select id="selectByCondition" resultType="brand">
select *
from tb_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName}
</select>
</mapper>测试
public class MyBrandTest {
@Test
public void testselectByCondition() throws IOException {
//接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
//处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//1.获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2.获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3.获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法
//方法一:接口方法参数使用 @Param 方式调用的方法
//List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//方法二:接口方法参数是 实体类对象 方式调用的方法
/* 封装对象
Brand brand = new Brand();
brand.setStatus(status);
brand.setCompanyName(setCompanyName);
brand.setBrandName(setBrandName);
*/
//List<Brand> brands = brandMapper.selectByCondition(brand);
//方式三 ：接口方法参数是 map集合对象方式调用的方法
Map map = new HashMap();
map.put("status" , status);
map.put("companyName", companyName);
map.put("brandName" , brandName);
List<Brand> brands = brandMapper.selectByCondition(map);
System.out.println(brands);
//5.释放资源
sqlSession.close();
}
}4.动态SQL
多条件查询 SQL语句
<select id="selectByCondition" resultType="brand">
select *
from tb_brand
<where>
<if test="status!= null">
and status = #{status}
</if>
<if test="companyName != null and companyName !=''">
and company_name like #{companyName}
</if>
<if test="brandName != null and brandName !=''">
and brand_name like #{brandName}
</if>
</where>
</select>单条件查询
Mapper接口
public interface BrandMapper {
/**
* 单条件动态查询
* @param brand
* @return
*/
List<Brand> selectByConditionSingle(Brand brand);
}SQL语句
<select id="selectByConditionSingle" resultType="brand">
select *
from tb_brand
<where>
<choose><!--相当于switch-->
<when test="status !=null"><!--相当于case-->
status = #{status}
</when>
<when test=" companyName!=null and companyName !=''">
company_name like #{companyName}
</when>
<when test="brandName !=null and brandName !=''">
brand_name like #{brandName}
</when>
</choose>
</where>
</select>测试
@Test
public void testSelectByConditionSingle() throws IOException {
//接收参数
int status = 1;
String companyName = "华为";
String brandName = "华为";
// 处理参数
companyName = "%" + companyName + "%";
brandName = "%" + brandName + "%";
//封装对象
Brand brand = new Brand();
//brand.setStatus(status);
brand.setCompanyName(companyName);
//brand.setBrandName(brandName);
//1. 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//2. 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
//3. 获取Mapper接口的代理对象
BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
//4. 执行方法
List<Brand> brands = brandMapper.selectByConditionSingle(brand);
System.out.println(brands);
//5. 释放资源
sqlSession.close();
}
}
