mybatis条件查询

最新推荐文章于 2024-08-04 23:27:27 发布

Brisk Walking

最新推荐文章于 2024-08-04 23:27:27 发布

阅读量522

点赞数

文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/QQ1602288425/article/details/128247335

版权

1:我这里直接使用的maven项目结构可能不一样;

这里首先是核心的配置文件;

<?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>
<!--    可以配置实体类的别名,扫描这个包下的所有文件,在sql中的xml文件只需要写对应的类名即可-->
    <typeAliases>
        <package name="domain"/>
    </typeAliases>
<!--    environments是配置数据库连接环境信息,可以配置多个environment,通过default可以切换不同的environments-->
    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务管理-->
            <transactionManager type="JDBC" />
            <!-- 数据库连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/user?characterEncoding=utf-8" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--        加载sql映射文件-->
<!--        <mapper resource="mapperImpl/UserMapper.xml"/>-->
<!--        mapper代理方式,要保证名称相同,同一目录下,直接映射文件夹-->
        <package name="mapperImpl"/>
    </mappers>
</configuration>

2:接下来就是一些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">
<!--
    namespace:名称空间
    resultType:对应返回结果的类型
    parameterType:
    -->
<!--如果使用mapper代理,namespace必须写对应的接口全部地名;-->
<!--如果在mapper接口中定义方法,那么这里的sql语句中的id与参数类型,与返回值必须一致-->
<mapper namespace="mapperImpl.UserMapper">
<!--        普通查询全部用户-->
    <select id="findAllUser" resultType="user">
        select * from u_user
    </select>
<!--    使用动态sql查询条件,查询所有用户-->
    <select id="findAllUseDynamic" resultType="user">
        select * from u_user
        <where>
            <if test="uid != null and uid != '' ">
               and uid like ${uid}
            </if>
            <if test="uname != null and uname != '' ">
               and uname like ${uname}
            </if>
        </where>
    </select>
<!--    多条件in查询-->
    <select id="findAllUseList" resultType="user">
        select * from u_user
        <where>
            <if test="list != null and list != ''">
                <foreach collection="list" item="id" index="index" open=" uid in (" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
<!--    一对一根据指定用户id查询订单-->
<!--    使用映射的方式加载类-->
    <resultMap id="fruitsUser" type="domain.Fruits">
        <result column="fname" property="fname"></result>
        <result column="fmoney" property="fmoney"></result>
<!--        映射类里面的类-->
        <association property="user" javaType="domain.User">
            <id column="uid" property="uid"></id>
            <result column="uname" property="uname"></result>
            <result column="state" property="state"></result>
        </association>
    </resultMap>
    <select id="findUserFruits" resultMap="fruitsUser">
        select u.uid,u.uname,u.state,f.fname,f.fmoney from u_user u,fruits f where u.uid=#{id} and u.uid=f.uid
    </select>
<!--    多对一查询,根据订单查询用户-->
    <resultMap id="fruitsUserFruits" type="domain.User">
        <id column="uid" property="uid"></id>
        <result column="uname" property="uname"></result>
        <result column="state" property="state"></result>
        <collection property="fruits" ofType="domain.Fruits">
            <result property="fname" column="fname"></result>
            <result column="fmoney" property="fmoney"></result>
        </collection>
    </resultMap>
    <select id="findFruitsAndUser" resultMap="fruitsUserFruits">
        select u.uid,u.uname,u.state,f.fname,f.fmoney from u_user u left join fruits f on f.uid=u.uid
    </select>
<!--    添加功能,添加一个用户-->
<!--    主键返回:
                1:设置属性useGeneratedKeys="true"为true
                2:keyProperty="uid":里面的值对应你主键的名称
-->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="uid">
        insert into u_user (uid,uname,upassword,state,fid) value (#{uid},#{uname},#{upassword},#{state},#{fid})
    </insert>
<!--    动态sql修改指定用户的信息-->
    <update id="updateUser">
        update u_user
        <set>
            <if test="uname != null and uname != '' ">
                uname = #{uname},
            </if>
            <if test="upassword != null and upassword != '' ">
                upassword = #{upassword},
            </if>
            <if test="state != null and state != '' ">
                state = #{state},
            </if>
            <if test="fid != null and fid != '' ">
                fid = #{fid}
            </if>
        </set>
        <where>
            <if test="uid != null and uid != '' ">
                uid = #{uid}
            </if>
        </where>
    </update>
<!--    删除功能-->
    <delete id="deleteUser">
        delete from u_user where uid = #{uid}
    </delete>
<!--    批量删除选中用户-->
    <select id="deletePitchUser">
        delete from u_user
        <where>
            <if test="list != null and list != ''">
                <foreach collection="list" item="id" index="index" open=" uid in (" separator="," close=")">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>
<!--    多对多查询,根据订单查询用户-->
    <resultMap id="orderAndUser" type="domain.Order">
        <id column="orderid" property="orderid"></id>
        <result column="orderamount" property="orderamount"></result>
        <result column="ordermoney" property="ordermoney"></result>
        <association property="orderuser" javaType="domain.User">
            <result column="uname" property="uname"></result>
            <collection property="fruits" ofType="domain.Fruits">
                <result column="fname" property="fname"></result>
                <result column="fmoney" property="fmoney"></result>
            </collection>
        </association>
    </resultMap>
    <select id="selectOrderAndUser" resultMap="orderAndUser">
        select o.orderid,o.orderamount,u.uname,f.fname,f.fmoney,o.ordermoney
        from orderfruits o,u_user u,fruits f where o.fid=f.fid and o.uid=u.uid
    </select>
</mapper>

3:因为里面有反射我这里使用了接口的,与上面sql语句的注释都是一一对应的;

package mapperImpl;

import domain.Fruits;
import domain.Order;
import domain.User;
import org.apache.ibatis.annotations.Select;

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

/**
 * 使用mapper代理
 * 1:定义的mapper接口名称必须和与sql映射文件同名
 * 2:并且mapper接口文件路径必须和sql映射文件放在同移目录下
 */
public interface UserMapper {
    /**
     * 普通查询所有用户,使用注解的方式开发
     * @return
     */
    @Select("select * from u_user")
    List<User> findAllUser();
    /**
     * 使用条件动态sql语句查询用户
     * @param user
     * @return
     */
    List<User> findAllUseDynamic(User user);

    /**
     * 使用in多条件查询
     * @param list
     * @return
     */
    List<User> findAllUseList(List<Integer> list);

    /**
     * 一对一根据指定用户id查询订单
     * @return
     */
    List<Fruits> findUserFruits(String str);

    /**
     * 多对一查询,根据订单查询用户
     * @return
     */
    List<User> findFruitsAndUser();

    /**
     * 添加用户
     * @param user
     */
    void insertUser(User user);

    /**
     * 动态sql修改指定用户的信息
     * @param user
     * @return
     */
    int updateUser(User user);

    /**
     * 删除指定用户功能
     * @param str
     */
    void deleteUser(String str);

    /**
     * 批量删除选中的用户
     * @param list
     */
    void deletePitchUser(ArrayList<String> list);

    /**
     * 多对多查询,根据订单查询用户
     * @return
     */
    List<Order> selectOrderAndUser();
}

4:下面就是一些测试类了,也是与sql一一对应的;

package userTset;

import domain.Fruits;
import domain.Order;
import domain.User;
import mapperImpl.UserMapper;
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.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class TestUser {
    SqlSessionFactory build = null;
    @Before
//    加载mybatis的核心文件,获取SqlSessionFactory
    public void creatFactory(){
        InputStream input = null;
        try {
//          通过mybatis提供的Resources方法,进行加载资源
            input = Resources.getResourceAsStream("mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        build = new SqlSessionFactoryBuilder().build(input);
    }

    /**
     * 普通查询所有用户
     */
    @Test
    public void findAllUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
//        执行sql语句,查询一个selectOne
        //User user = sqlSession.selectOne("test.findAllUser", "1");
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> user = mapper.findAllUser();
        System.out.println(user);
//        释放资源
        sqlSession.close();
    }
    /**
     * 使用动态sql查询用户
     */
    @Test
    public void findAllUseDynamic(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
//        执行sql语句,查询一个selectOne
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//        定义变量
        String u_id = "1";
        String u_name = "儿";
//        处理变量,进行模糊查询
        String uid = "%"+u_id+"%";
        String uname = "%"+u_name+"%";
        User user = new User();
        //user.setUid("2");
        //user.setUname(uname);
        List<User> allUseDynamic = mapper.findAllUseDynamic(user);
        System.out.println(allUseDynamic);
//        释放资源
        sqlSession.close();
    }

    /**
     * 使用in多条件查询
     */
    @Test
    public void findAllUseList(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
//        执行sql语句,查询一个selectOne
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //user.setUname(uname);
        ArrayList<Integer> list1 = new ArrayList<>();
//        list1.add(1);
//        list1.add(3);
        List<User> list = mapper.findAllUseList(list1);
        System.out.println(list);
//        释放资源
        sqlSession.close();
    }

    /**
     * 一对一根据指定用户id查询订单
     */
    @Test
    public void findUserFruits(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Fruits> fruits = mapper.findUserFruits("1669709518274");
        System.out.println(fruits);
//        释放资源
        sqlSession.close();
    }

    /**
     * 多对一查询,根据订单查询用户
     */
    @Test
    public void findFruitsAndUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> fruits = mapper.findFruitsAndUser();
        System.out.println(fruits);
        sqlSession.close();
    }

    /**
     * 添加用户
     */
    @Test
    public void insertUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        Date date = new Date();
        user.setUid(date.getTime()+"");
        user.setUname("谯幺儿");
        user.setUpassword("123");
        user.setState("0");
        user.setFid("1");
        mapper.insertUser(user);
        System.out.println(user.getUid());
//        添加修改删除需要提交事务
//        提交事务
        sqlSession.commit();
        sqlSession.close();
    }
    /**
     * 动态sql修改指定用户信息
     */
    @Test
    public void updateUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession(true);//设置为true提交事务
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setUid("16696861166658");
        user.setState("0");
        user.setUpassword("123");
        int i = mapper.updateUser(user);
        System.out.println(i);
        sqlSession.close();
    }

    /**
     * 删除指定用户的功能
     */
    @Test
    public void deleteUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession(true);//设置为true提交事务
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser("1669687739185");
        sqlSession.close();
    }
    /**
     * 批量删除选中用户的功能
     */
    @Test
    public void deletePitchUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        ArrayList<String> list = new ArrayList<>();
        list.add("1669709372727");
        if (list.size() != 0) {
            mapper.deletePitchUser(list);
            sqlSession.commit();
            sqlSession.close();
        }else {
            System.out.println("数据为空不执行删除语句");
        }
    }
    /**
     * 多对多查询,根据订单查询用户
     */
    @Test
    public void selectOrderAndUser(){
//    获取sqlSession对象,用他执行sql语句
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<Order> list = mapper.selectOrderAndUser();
        System.out.println(list);
        sqlSession.close();
    }

}

最后就是数据库,可以参考参考