周三 mybatis 动态查询
mapper
package com.wzy.mapper; import com.wzy.pojo.Brand; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface Brandmapper { /* * 查询所有 * */ public List<Brand> selectall(); /* * 查看详情 * */ public Brand selectid(int id); /* * 散装参数：如果方法中有多个参数，需要使用@Param("sql占位符名称“） * 对象参数：对象属性名称要与参数占位符名称一致 * map集合参数 * * */ //public List<Brand> selectbycondition(@Param("status") int status, @Param("companyName") String companyName,@Param("brandName") String brandName); //public List<Brand> selectbycondition(Brand brand); public List<Brand> selectbycondition(Map map); /* * 单条件动态查询 * */ public List<Brand> selectbyconditionsingle(Brand brand); }
xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!-- namespace:名称空间 --> <mapper namespace="com.wzy.mapper.Brandmapper"> <!-- 数据库的字段名称 和 实体类的属性名称 不一样，则不能自动封装数据 sql片段 不灵活 *resultMap --> <resultMap id="brandResultMap" type="com.wzy.pojo.Brand"> <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="selectid" resultMap="brandResultMap"> select * from tb_brand where id <![CDATA[ = ]]> #{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> <if test="status !=null"> 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> </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> </choose> </where> </select> <!-- sql片段 --> <!-- <sql id="brand_colum"> id, brand_name as brandName, company_name as companyName, ordered, description, status </sql> <select id="selectall" resultType="com.wzy.pojo.Brand"> select <include refid="brand_colum"/> from tb_brand; </select>--> </mapper>
test
package com.wzy.test; import com.wzy.mapper.Brandmapper; import com.wzy.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 javax.swing.plaf.PanelUI; 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); 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.selectid(id); System.out.println(brand); 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); 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); /* 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.selectbyconditionsingle(brand); System.out.println(brands); sqlSession.close(); } }