【mybatis查询多个id】

今天药忘吃喽~ 2024-03-31 09:14 57阅读 0赞

## 代码部分 ##

com.qingruan.bean下新建QueryVo.java

QueryVo.java

>     package com.qingruan.bean;
>     
>     import java.util.List;
>     
>     public class QueryVo {
>         //选中多个id，放进集合
>         private List<Integer> ids;
>     
>     
>     
>         public List<Integer> getIds() {
>             return ids;
>         }
>     
>         public void setIds(List<Integer> ids) {
>             this.ids = ids;
>         }
>     }

IDeptDao.java

package com.qingruan.dao;
    
    import com.qingruan.bean.Dept;
    import com.qingruan.bean.QueryVo;
    
    import java.util.List;
    
    public interface IDeptDao {
        /**
         * 查询所有部门信息
         */
        public List<Dept> findAllDept();
    
        /**
         * 根据id查询单条数据
         * @param deptNo
         * @return
         */
        public Dept findById(Integer deptNo);
    
        /**
         * 新增部门
         * @param dept
         * @return 受影响的行数
         */
        public int saveDept(Dept dept);
    
        /**
         * 修改
         * @param dept
         * @return
         */
        public int updateDept(Dept dept);
    
        /**
         * 删除
         * @param dno
         * @return
         */
        public int deleteDept(Integer dno);
    
        /**
         * 模糊查询
         * @return
         */
        public List<Dept> findByDeptName(String deptName);
    
        /**
         * 总记录数
         * @return
         */
        public int findTotal();
    
        /**
         * 根据部门信息，查询部门列表
         */
        public List<Dept> findByDept(Dept dept);
    
        /**
         * 根据id集合查询部门信息
         */
        public List<Dept> findIds(QueryVo vo);
    
    }

IDeptDao.xml

>     <?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.qingruan.dao.IDeptDao">
>     
>         
>         <sql id="queryAll">
>             select * from dept
>         </sql>
>     
>     
>         
>         <resultMap id="DeptMap" type="com.qingruan.bean.Dept">
>             
>             <id property="dno" column="dept_id"></id>
>             <result property="deptName" column="dept_name"></result>
>             <result property="deptLoc" column="dept_address"></result>
>         </resultMap>
>     
>     
>         
>         <select id="findAllDept"  resultMap="DeptMap">
>             <include refid="queryAll"></include>
>         </select>
>     
>         
>         <select id="findById" resultMap="DeptMap" parameterType="int">
>             <include refid="queryAll"></include>
>             where dept_id=#{deptno}
>         </select>
>     
>         
>         <insert id="saveDept" parameterType="com.qingruan.bean.Dept">
>             /*配置保存时获取插入的id*/
>             <selectKey keyColumn="dno" keyProperty="dno" resultType="int">
>                 /*得到刚新增进去的自增主键id*/
>                 select last_insert_id();
>             </selectKey>
>     
>             insert into dept(dept_name,dept_address) values(#{deptName},#{deptLoc})
>         </insert>
>     
>         <update id="updateDept" parameterType="com.qingruan.bean.Dept">
>             update dept set dept_name=#{deptName},dept_address=#{deptLoc} where dept_id=#{dno}
>         </update>
>     
>         <delete id="deleteDept" parameterType="java.lang.Integer">
>             delete from dept where dept_id=#{deptno}
>         </delete>
>     
>         
>         <select id="findByDeptName" parameterType="String" resultMap="DeptMap">
>             <include refid="queryAll"></include>
>             where dept_name like #{deptName}
>         </select>
>     
>         <select id="findTotal" resultType="int">
>             select count(*) from dept
>         </select>
>     
>     
>     
>         <select id="findByDept" resultMap="DeptMap" parameterType="com.qingruan.bean.Dept">
>             <include refid="queryAll"></include>
>             <where>
>                 <if test="deptName!=null and deptName!='' ">
>                     and dept_name like #{deptName}
>                 </if>
>                 <if test="deptLoc!=null and deptLoc!='' ">
>                     and dept_address like #{deptLoc}
>                 </if>
>             </where>
>     
>         </select>
>     
>         
>         <select id="findIds" resultMap="DeptMap" parameterType="com.qingruan.bean.Dept">
>             <include refid="queryAll"></include>
>             <where>
>                 <if test="ids!=null and ids.size()>0">
>                     <foreach collection="ids" open="dept_id in(" close=")" item="dno" separator=",">
>                         #{dno}
>                     </foreach>
>                 </if>
>             </where>
>     
>         </select>
>     
>     </mapper>

MyBatisTest.java

>     package com.qingruan.test;
>     
>     import com.qingruan.bean.Dept;
>     import com.qingruan.bean.QueryVo;
>     import com.qingruan.dao.IDeptDao;
>     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.After;
>     import org.junit.Before;
>     import org.junit.Test;
>     
>     import java.io.InputStream;
>     import java.util.ArrayList;
>     import java.util.List;
>     
>     public class MyBatisTest {
>         private InputStream is;
>         private SqlSessionFactoryBuilder builder;
>         private SqlSessionFactory sessionFactory;
>         private SqlSession session;
>         private IDeptDao dao;
>     
>         @Before //在测试方法执行之前执行
>         public void init() throws  Exception{
>             is=Resources.getResourceAsStream("mybatis-config.xml");
>             //创建sqlSessionFacotory的构建着对象
>             builder=new SqlSessionFactoryBuilder();
>             //通过构建着对象创建工厂对象
>             sessionFactory = builder.build(is);
>             //获得session对象
>             session = sessionFactory.openSession();
>             //使用session创建dao接口的代理对象
>             dao = session.getMapper(IDeptDao.class);
>         }
>     
>         @After  //在测试方法执行完成之后执行
>         public void destroy() throws  Exception{
>             //提交事务  增删改后一定要控制事物的提交
>             session.commit();
>             //方式资源
>             session.close();
>             is.close();
>         }
>     
>         @Test
>         public void testFindAll() throws  Exception{
>             //读取mybatis主配置文件
>             //使用dao代理对象来执行查询所有的方法
>             List<Dept> depts = dao.findAllDept();
>             for (Dept dept : depts) {
>                 System.out.println(dept.toString());
>             }
>         }
>     
>         @Test
>         public void testFindById(){
>             Dept dept = dao.findById(1);
>             System.out.println(dept);
>         }
>     
>         @Test
>         public void testSaveDept(){
>             Dept dept = new Dept();
>             dept.setDeptName("后勤部");
>             dept.setDeptLoc("北京");
>             int result = dao.saveDept(dept);
>             System.out.println("插入数据的主键："+dept.getDno());
>     
>         }
>     
>         @Test
>         public void testUpdateDept(){
>             Dept dept = dao.findById(9);
>             dept.setDeptName("新部门");
>             dept.setDeptLoc("湖北");
>             int result = dao.updateDept(dept);
>             System.out.println(result+"行受影响");
>     
>         }
>     
>         @Test
>         public void testDeleteDept(){
>             int result = dao.deleteDept(9);
>             System.out.println(result+"行受影响");
>         }
>     
>         @Test
>         public void testLikeDept(){
>             List<Dept> depts = dao.findByDeptName("%部%");
>             for (Dept dept : depts) {
>                 System.out.println(dept);
>             }
>     
>         }
>     
>         @Test
>         public void testFindTotal(){
>             int total = dao.findTotal();
>             System.out.println("总记录数："+total);
>     
>         }
>     
>         @Test
>         public void testFindByDept(){
>             Dept dept = new Dept();
>             dept.setDeptName("%部%");
>             dept.setDeptLoc("上海");
>             List<Dept> depts = dao.findByDept(dept);
>             for (Dept dept1 : depts) {
>                 System.out.println(dept1.toString());
>             }
>         }
>     
>         @Test
>         public void testFindIds(){
>             QueryVo vo = new QueryVo();
>             List<Integer> ids = new ArrayList<Integer>();
>             ids.add(1);
>             ids.add(3);
>             ids.add(7);
>             vo.setIds(ids);
>             List<Dept> depts = dao.findIds(vo);
>             for (Dept dept : depts) {
>                 System.out.println(dept.toString());
>             }
>     
>         }
>     
>     }

运行结果

![771f8f5607cc47539ac528b3b2fe70b3.png][]

[771f8f5607cc47539ac528b3b2fe70b3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/560b473658d34312a2cde0b820fa4cda.png