Mybatis动态sql增删改查

最新推荐文章于 2023-04-17 00:49:27 发布

Liuss0217

最新推荐文章于 2023-04-17 00:49:27 发布

阅读量552

点赞数

文章标签： java mybatis

本文链接：https://blog.csdn.net/Liuss0217/article/details/121661831

版权

实体类
package com.entity;

public class Domitory {
    private String Domitory_ID;
    private String Domitory_BuildingID;
    private String Domitory_Name;
    private String Domitory_Type;
    private String Domitory_Number;
    private String Domitory_Tel;
    private Building building;

    public String getDomitory_ID() {
        return Domitory_ID;
    }

    public void setDomitory_ID(String domitory_ID) {
        Domitory_ID = domitory_ID;
    }

    public String getDomitory_BuildingID() {
        return Domitory_BuildingID;
    }

    public void setDomitory_BuildingID(String domitory_BuildingID) {
        Domitory_BuildingID = domitory_BuildingID;
    }

    public String getDomitory_Name() {
        return Domitory_Name;
    }

    public void setDomitory_Name(String domitory_Name) {
        Domitory_Name = domitory_Name;
    }

    public String getDomitory_Type() {
        return Domitory_Type;
    }

    public void setDomitory_Type(String domitory_Type) {
        Domitory_Type = domitory_Type;
    }

    public String getDomitory_Number() {
        return Domitory_Number;
    }

    public void setDomitory_Number(String domitory_Number) {
        Domitory_Number = domitory_Number;
    }

    public String getDomitory_Tel() {
        return Domitory_Tel;
    }

    public void setDomitory_Tel(String domitory_Tel) {
        Domitory_Tel = domitory_Tel;
    }

    public Building getBuilding() {
        return building;
    }

    public void setBuilding(Building building) {
        this.building = building;
    }

    public Domitory(String domitory_ID, String domitory_BuildingID, String domitory_Name, String domitory_Type, String domitory_Number, String domitory_Tel, Building building) {
        Domitory_ID = domitory_ID;
        Domitory_BuildingID = domitory_BuildingID;
        Domitory_Name = domitory_Name;
        Domitory_Type = domitory_Type;
        Domitory_Number = domitory_Number;
        Domitory_Tel = domitory_Tel;
        this.building = building;
    }

    public Domitory() {
    }
}
接口

package com.dao;


import com.entity.Domitory;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface UserInfoDao {
    public List<Domitory> findByArrey(int [] array);
    public List<Domitory> findByList(List<Integer> list);
    public List<Domitory> findByChoose(@Param("Domitory_Name") String Domitory_Name,@Param("Domitory_Number")String Domitory_Number);
    public List<Domitory> findByPage(@Param("pagesize") int pagesize,@Param("pagecode") int pagecode);
    public List<Domitory> findByTest(@Param("Domitory_Name") String Domitory_Name,@Param("Domitory_Number")String Domitory_Number);
    public Domitory findById(int i);
    public int update(Domitory domitory);


}
实现方法

<?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.dao.UserInfoDao">
   <select id="findByArrey" resultType="Domitory">
       select * from domitory where Domitory_BuildingID in
        <foreach collection="array" item="Domitory_BuildingID" open="(" separator="," close=")">
            #{Domitory_BuildingID}
        </foreach>
   </select>

    <select id="findByList" resultType="Domitory">
        select * from domitory where Domitory_BuildingID in
        <foreach collection="list" item="Domitory_BuildingID" open="(" separator="," close=")">
            #{Domitory_BuildingID}
        </foreach>
    </select>

    <select id="findByChoose" resultType="Domitory">
        select * from domitory where 1=1
        <choose>
            <when test="Domitory_Name!=null and Domitory_Name!=''">
                and Domitory_Name like concat(#{Domitory_Name},'%')
            </when>
            <when test="Domitory_Number!=null and Domitory_Number!=''">
                and Domitory_Number=#{Domitory_Number}
            </when>
            <otherwise>

            </otherwise>
        </choose>
    </select>

    <select id="findByPage" resultType="Domitory">
        select * from domitory
        <if test="pagecode!=0">
            limit #{pagesize},#{pagecode}
        </if>
    </select>

    <select id="findByTest" resultType="Domitory">
        select * from domitory
        <where>
            <trim suffix="" suffixOverrides="and">
                <if test="Domitory_Name!=null and Domitory_Name!=''">
                    and Domitory_Name=#{Domitory_Name}
                </if>
                <if test="Domitory_Number!=null and Domitory_Number!=''">
                    and Domitory_Number=#{Domitory_Number}
                </if>
            </trim>
        </where>
    </select>

    <select id="findById" resultType="Domitory">
        select * from domitory where Domitory_ID=#{Domitory_ID}
    </select>

    <update id="update" parameterType="Domitory">
        update domitory
        <trim suffix="where Domitory_ID=#{Domitory_ID}" suffixOverrides=",">
            <set>
                <if test="Domitory_BuildingID!=null and Domitory_BuildingID!=''">Domitory_BuildingID=#{Domitory_BuildingID},</if>
                <if test="Domitory_Name!=null and Domitory_Name!=''">Domitory_Name=#{Domitory_Name},</if>
                <if test="Domitory_Type!=null and Domitory_Type!=''">Domitory_Type=#{Domitory_Type},</if>
                <if test="Domitory_Number!=null and Domitory_Number!=''">Domitory_Number=#{Domitory_Number},</if>
                <if test="Domitory_Tel!=null and Domitory_Tel!=''">Domitory_Tel=#{Domitory_Tel},</if>
            </set>
        </trim>
    </update>

</mapper>

测试类

package com.test;


import com.dao.UserInfoDao;
import com.entity.Domitory;
import com.entity.UserInfo;
import com.util.MybytisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.ArrayList;
import java.util.List;

public class test {
    public static void main(String[] args) {
        //test1();
        //test2();
        //test3();
        //test4();
        //test6();
        test7();
    }

    private static void test7() {
        SqlSession session = MybytisUtil.getSession();
        Domitory domitory = session.getMapper(UserInfoDao.class).findById(2);
        domitory.setDomitory_Name("赵日天");
        int a = session.getMapper(UserInfoDao.class).update(domitory);
        if (a!=0){
            System.out.println("ok");
            session.commit();
        }
    }

    private static void test6() {
        SqlSession session = MybytisUtil.getSession();
        List<Domitory> list = session.getMapper(UserInfoDao.class).findByTest("11","11");
        for (Domitory d: list){
            System.out.println(d.getDomitory_ID());
        }
    }

    private static void test4() {
        SqlSession session = MybytisUtil.getSession();
        int pagesize=2;int pagecode=1;
        int befor = (pagecode-1)*pagesize;
        int after = pagesize;
        List<Domitory> list = session.getMapper(UserInfoDao.class).findByPage(befor,after);
        for (Domitory d : list){
            System.out.println(d.getDomitory_Name());
        }
    }

    private static void test3() {
        SqlSession session = MybytisUtil.getSession();
        List<Domitory> list = session.getMapper(UserInfoDao.class).findByChoose("1","");
        for (Domitory d : list){
            System.out.println(d.getDomitory_ID());
        }
    }

    private static void test2() {
         SqlSession session = MybytisUtil.getSession();
         List<Integer> list = new ArrayList<Integer>();
         list.add(1);
         list.add(2);
         List<Domitory> domitoryList = session.getMapper(UserInfoDao.class).findByList(list);
         for (Domitory d :domitoryList){
             System.out.println(d.getDomitory_Name());
         }
    }

    private static void test1() {
        SqlSession session = MybytisUtil.getSession();
        int[] array = {1,2};
        List<Domitory> list =  session.getMapper(UserInfoDao.class).findByArrey(array);
        for (Domitory d : list){
            System.out.println(d.getDomitory_Name());
        }
    }
}

工具类

package com.util;

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 java.io.IOException;
import java.io.InputStream;

public class MybytisUtil {
    private static SqlSessionFactory sessionFactory;
    static {
        String resource="mybits.xml";
        try {
            InputStream is= Resources.getResourceAsStream(resource);
            sessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSession(){
        return sessionFactory.openSession();
    }
    public static void closeSession(SqlSession session){
        if (session!=null){
            session.close();
        }
    }
}

xml配置

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <typeAliases>
        <package name="com.entity"/>
    </typeAliases>

    <environments default="cs">
        <environment id="cs">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mydb"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="com/dao/UserInfoDao.xml"/>
    </mappers>

</configuration>

jar包