學習mybatis檢視詳情、動態多條件/單條件查詢
點選檢視程式碼
<?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">
<!--
資料庫表的欄位名稱 和 實體類的屬性名稱 不一樣，不能自動封裝資料
*起別名
*resultMap
-->
<!--
id:唯一標識
type:對映型別，支援別名
-->
<resultMap id="brandResultMap" type="brand">
<!--
id:主鍵對映
result:一般欄位
column:表列名
property:實體類屬性名
-->
<result column="brand_name" property="brandName"/>
<result column="company_name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="brandResultMap">
select *
from tb_brand;
</select>
<!--<select id="selectAll" resultType="brand">
select id,brand_name as brandName,company_name as companyName,ordered,description,status
from tb_brand;
</select>-->
<!--<select id="selectAll" resultType="brand">
select *
from tb_brand;
</select>-->
<!--
*引數佔位符：
1.#{}：替換為？；
傳遞引數;
2.${}：拼字串，存在sql注入；
表名、列名不固定;
*特殊字元處理：
1.跳脫字元;
2.CDATA區:
<![CDATA[
<
]]>
-->
<select id="selectById" resultMap="brandResultMap">
select *
from tb_brand where id = #{id};
</select>
<!-- <select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
where status = #{status}
and company_name like #{companyName}
and brand_name like #{brandName};
</select>-->
<!--
動態條件查詢
-->
<select id="selectByCondition" resultMap="brandResultMap">
select *
from tb_brand
/*where 1 = 1*/
<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>
<select id="selectByConditionSingle" resultMap="brandResultMap">
select *
from tb_brand
<where>
<choose>
<when test="status != null">
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>
<!--<otherwise>
1 = 1
</otherwise>-->
</choose>
</where>
</select>
</mapper>
點選檢視程式碼
package com.itheima.test;
import com.itheima.mapper.BrandMapper;
import com.itheima.pojo.Brand;
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 java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyBatisTest {
@Test
public void testSelectAll() throws IOException {
//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.selectAll();
System.out.println(brands);
//5.釋放
sqlSession.close();
}
@Test
public void testSelectById() throws IOException {
int id = 1;
//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.執行方法
Brand brand = brandMapper.selectById(id);
System.out.println(brand);
//5.釋放
sqlSession.close();
}
@Test
public void testSelectByCondition() 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);*/
Map map = new HashMap();
map.put("status",status);
map.put("companyName",companyName);
map.put("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.執行方法
//List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//List<Brand> brands = brandMapper.selectByCondition(brand);
List<Brand> brands = brandMapper.selectByCondition(map);
System.out.println(brands);
//5.釋放
sqlSession.close();
}
@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.selectByCondition(status, companyName, brandName);
//List<Brand> brands = brandMapper.selectByCondition(brand);
List<Brand> brands = brandMapper.selectByConditionSingle(brand);
System.out.println(brands);
//5.釋放
sqlSession.close();
}
}