mysql动态查询

mysql动态查询

mapper接口文件:

package qin.com.mapper;/*
 *@program:learn_mybatis
 *@author: 小虎
 *@Time: 2022/7/22  20:36
 *@description:
 *@version 1.0.0
 */

import org.apache.ibatis.annotations.Param;
import qin.com.entity.Users;

import javax.swing.event.ListDataEvent;
import java.util.Date;
import java.util.List;

public interface UsersMapper {
    public List<Users> getAll();

    int update(Users users);

    Users getUsersById(int id);

    //模糊查询
    Users getNameLike(String userName);

    //优化后的模糊查询
    List<Users> getByNameGood(String name);

    int addUsers(Users users);

    int deleteUserById(int id);

    //模糊用户名和地址
    List<Users> getByNameAndAddress(@Param("columnName") String columnName,@Param("columnValue") String columnValue);



    //按照指定条件进行多条件查询
    List<Users> selectUserByCondition(Users users);

    //有选择的更新
    int updateBySet(Users users);

    //查询多个指定id用户的信息  批量查询指定id的用户信息
    List<Users> getByIds(Integer []arr);

    //批量删除
    int delByIds(Integer []arr);

    //批量增加
    int addByIds(List<Users> users);

    //批量更新
    int updateByIds(List<Users> users);

    //多id查询
    List<Users> selectByIDS(Integer []arr);

    int addUserList(List<Users> users);

    //查询指定日期范围内的信息
    List<Users> getByBirthday(Date begin,Date end);

}

mapper.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="qin.com.mapper.UsersMapper"> <!-- 一般为数据表在持久层对应接口的完整路径 -->
    <resultMap id="userBean" type="qin.com.entity.Users">     <!-- 一般为数据表对应实体类所在数据包 -->
        <id property="id" column="id"></id>
        <result property="userName" column="userName"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
    </resultMap>

    <!--定义SQL代码片段-->
    <sql id="allColumns">
        id,username,birthday,sex,address
    </sql>
    <!-- 查询数据表中所有数据,这相标签的id值要与数据表对应接口中的查询方法的名称要一样 -->
    <select id="getAll" resultType="qin.com.entity.Users">
        select <include refid="allColumns"/>
        from users
    </select>

  <!--  private int id;
    private String userName;
    private Date birth;
    private String sex;
    private String address;
    #{}写的是实体类的属性名,注意大小写
    -->
    <update id="update" parameterType="Users">
        update users set username=#{userName},birthday=#{birthday},sex=#{sex},address=#{address}
        where id=#{id}
    </update>

    <select id="getUsersById" resultType="qin.com.entity.Users">
        select * from users where id=#{id}
    </select>



    <insert id="addUsers" parameterType="Users">
        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select last_insert_id()
        </selectKey>

        insert into users(username, birthday, sex, address)
        values(#{userName},#{birthday},#{sex},#{address})
    </insert>

    <delete id="deleteUserById" parameterType="int">
        delete from users where id=#{id}
    </delete>


    <select id="getNameLike" resultType="qin.com.entity.Users">
        select * from users where username like '%${userName}%'
    </select>
   <!-- //优化后的模糊查询-->
    <select id="getByNameGood" parameterType="string" resultType="qin.com.entity.Users">
        select <include refid="allColumns"/>
        from users where username like concat('%',#{name},'%')
    </select>


    <!--//模糊用户名和地址
    如果参数超过一个,则parameterType不写
    List<Users> getByNameAndAddress(String colum,String columValue);-->
    <select id="getByNameAndAddress" resultType="qin.com.entity.Users">
        select <include refid="allColumns"/>
        from users
        where ${columnName} like concat('%',#{columnValue},'%')
    </select>

    <!--
    //有选择的更新
    int updateBySet(Users users);-->
    <update id="updateBySet" parameterType="Users">
        update users
        <set>
            <if test="userName != null and userName != ''">
                userName = #{userName},
            </if>
            <if test="birthday != null">
                brithday =#{brithday},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="address != null and address != ''">
                address #{address},
            </if>
        </set>
        where id = #{id}
    </update>


    <!-- //按照指定条件进行多条件查询
             根据实体类中的成员变量是否有值来决定是否添加条件
     List<Users> getByCondition(Users users);-->



    <select id="selectUserByCondition" resultType="Users" parameterType="Users">
        select <include refid="allColumns"></include>
        from users
        <where>
            <if test="userName != null and userName != ''" >
                and username like concat('%',#{userName},'%')
            </if>
            <if test="birthday != null" >
                and birthday = #{birthday}
            </if>
            <if test="sex != null and sex != ''" >
                and sex = #{sex}
            </if>
            <if test="address != null and address != ''" >
                and address like concat('%',#{address},'%')
            </if>
        </where>
    </select>





    <!-- //批量删除
     List<Users> delByIds(Integer []arr);-->
    <delete id="delByIds">
        delete from users
        where id in
        <foreach collection="array" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
    <!-- //批量增加
     int addByIds(List<Users> users);-->
    <insert id="addByIds">
        insert into  users(username, birthday, sex, address)
        values
        <foreach collection="list" item="user" separator=",">
            (#{user.userName},#{user.birthday},#{user.sex},#{user.address})
        </foreach>
    </insert>
    <!--
        //批量更新
        int updateByIds(List<Users> users);-->

    <update id="updateByIds">
        <foreach collection="list" item="user" separator=";">
            update users
            <set>
                <if test="user.userName != null and user.userName != ''">
                    username = #{user.userName},
                </if>
                <if test="user.birthday != null">
                    birthday = #{user.birthday},
                </if>
                <if test="user.sex != null and user.sex != ''">
                    sex = #{user.sex},
                </if>
                <if test="user.address != null and user.address != ''">
                    address = #{user.address}
                </if>
            </set>
            where id = #{user.id}
        </foreach>
    </update>

    <!--
       //查询多个指定id用户的信息
       List<Users> getByIds(Integer []arr);
       -->

    <select id="getByIds" resultType="qin.com.entity.Users">
        select <include refid="allColumns"></include>
        from users
        where id in
        <!-- (
         <foreach collection="array" item="id" separator=",">
             #{id}
         </foreach>
         )-->

        <foreach collection="array" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>

    </select>
<!--
    //多id查询
    List<Users> selectByIDS(Integer[] arr);-->
    <select id="selectByIDS" resultType="qin.com.entity.Users">
        select <include refid="allColumns"></include>
        from users
        where id in
        <foreach collection="array" item="id" separator="," open="(" close=")" >
            #{id}
        </foreach>
    </select>


    <!--  //批量添加
      int addUserList(List<Users> users);-->
    <insert id="addUserList">
        insert into users(username, birthday, sex, address) values
        <foreach collection="list" item="user" separator=",">
            (#{user.userName},#{user.birthday},#{user.sex},#{user.address})
        </foreach>
    </insert>



    <!--//查询指定日期范围内的信息
    List<Users> getByBirthday(Date begin,Date end);
                       对应between #{arg0} in #{arg1}
    -->
    <select id="getByBirthday" resultType="qin.com.entity.Users">
        select <include refid="allColumns"></include>
        from users
        where birthday between #{arg0} and #{arg1}
    </select>


</mapper>

mybatis-config.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>

    <!--读取jdbc.properties属性文件-->
  <!--  <properties resource="db.properties"></properties>-->
    <!--控制台打印sql输出语句-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="qin.com.entity"></package>
    </typeAliases>
    <!--配置数据库环境变量
        default值:使用environment的id属性名
    -->
    <environments default="mysql">
        <!--配置数据库连接环境-->
        <!--开发时的环境配置-->
        <environment id="mysql">
            <!--配置事务管理器
                type:指定事务管理的方式
                    JDBC:事务的控制交给程序员处理
                    MANAGED:由容器(Spring)来管理事务
            -->
            <transactionManager type="JDBC"></transactionManager>
            <!--配置数据源:driver、url、username、password
                type:指定不同的配置方式
                    JNDI :java命名目录接口,在服务器端进行数据库连接池的管理POOLED:使用数据库连接池
                    UNPOLLED:不使用数据库连接池 -->
            <dataSource type="POOLED">
                <!-- 连接mysql数据库的驱动程序 -->
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                <!-- 连接mysql数据库的地址 -->
                <property name="url" value="jdbc:mysql://localhost/mybatis"></property>
                <!-- 连接mysql数据库的管理员 -->
                <property name="username" value="root"></property>
                <!-- 连接mysql数据库的管理员的密码 -->
                <property name="password" value="root"></property>
            </dataSource>
        </environment>
        <!--在家时的环境配置-->
        <!--<environment id="home">
            <transactionManager type="JDBC"></transactionManager>
            &lt;!&ndash;数据源:driver、url、username、password&ndash;&gt;
            <dataSource type="POOLED">
                &lt;!&ndash; 连接mysql数据库的驱动程序 &ndash;&gt;
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                &lt;!&ndash; 连接mysql数据库的地址 &ndash;&gt;
                <property name="url" value="jdbc:mysql://localhost/dataName"></property>
                &lt;!&ndash; 连接mysql数据库的管理员 &ndash;&gt;
                <property name="username" value="root"></property>
                &lt;!&ndash; 连接mysql数据库的管理员的密码 &ndash;&gt;
                <property name="password" value="root"></property>
            </dataSource>
        </environment>-->
        <!--上线后的环境配置-->
      <!--  <environment id="online">
            <transactionManager type="JDBC"></transactionManager>
            &lt;!&ndash;数据源:driver、url、username、password&ndash;&gt;
            <dataSource type="POOLED">
                &lt;!&ndash; 连接mysql数据库的驱动程序 &ndash;&gt;
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                &lt;!&ndash; 连接mysql数据库的地址 &ndash;&gt;
                <property name="url" value="jdbc:mysql://localhost/demo"></property>
                &lt;!&ndash; 连接mysql数据库的管理员 &ndash;&gt;
                <property name="username" value="root"></property>
                &lt;!&ndash; 连接mysql数据库的管理员的密码 &ndash;&gt;
                <property name="password" value="root"></property>
            </dataSource>
        </environment>-->
    </environments>
    <!--注册mapper文件配置-->
    <!--注册mapper.xml文件
        resource: 从resources目录下找指定名称的文件注册url:使用绝对路径注册
        class:动态代理方式下的注册-->
    <mappers>
        <!--
            注册单个mapper.xml文件
        <mapper class="qin.com.mapper.UsersMapper"></mapper>
        -->
        <!--注册批量mapper.xml-->
        <package name="qin.com.mapper"/>
    </mappers>

</configuration>

Test测试类

package qin.com.service;/*
 *@program:learn_mybatis
 *@author: 小虎
 *@Time: 2022/7/22  18:19
 *@description:
 *@version 1.0.0
 */

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 qin.com.entity.Users;
import qin.com.mapper.UsersMapper;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

public class MyTest {
    //日期格式化刷子
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");





    SqlSession sqlSession;
    UsersMapper usersMapper;
    @Before
    public void openSqlSession() throws IOException {
        //读取核心配置文件
       InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
       //创建会话工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //取出SQLSession
        sqlSession = sqlSessionFactory.openSession();

        usersMapper = sqlSession.getMapper(UsersMapper.class);
    }

    @After
    public void closeSqlsSession(){
        //关闭Sqlsession
        sqlSession.close ();
    }
    @Test
    public void testgetAll() throws IOException {

        //取出动态代理的对象完成接口中的方法的调用实则是调用xml文件中相应的标签的功能

        System.out.println(usersMapper.getClass());

        List<Users> usersList = usersMapper.getAll();




    }
    @Test
    public void testUpdate() throws ParseException {
        Users users = new Users(31,"大白奶子", sdf.parse("2022-12-20"), "2", "美国");
        int count = usersMapper.update(users);
        System.out.println(count);
        sqlSession.commit();
    }

    @Test
    public void testgetUsersById(){

        Users usersById = usersMapper.getUsersById(2);
        usersById.toString();
    }

    @Test
    public void testgetNameLike(){
        Users nameLike = usersMapper.getNameLike("虎");
        nameLike.toString();
    }


    @Test
    public void testAddUser() throws ParseException {
        Users users = new Users("大屁股大白奶子", sdf.parse("2022-02-02"), "2", "日本");
        int i = usersMapper.addUsers(users);
        System.out.println(i);
        sqlSession.commit();
        System.out.println(users);
    }

    @Test
    public void testdeleteUserById(){
        int i = usersMapper.deleteUserById(33);
        System.out.println(i);
        sqlSession.commit();
    }

    @Test
    public void testgetByNameGood(){
        List<Users> nameLike = usersMapper.getByNameGood("虎");
        for(Users users:nameLike){
            System.out.println(users);
        }
    }

    @Test
    public void testgetByNameAndAddress(){
        List<Users> nameLike = usersMapper.getByNameAndAddress("address","市");
        for(Users users:nameLike){
            System.out.println(users);
        }
    }



    @Test
    public void testUUID(){
        UUID uuid = UUID.randomUUID();
        System.out.println(uuid);
    }

    @Test
    public void testUpdateByCondition(){
        Users users = new Users();
        users.setId(35);
        users.setUserName("小奶子");
        int num = usersMapper.updateBySet(users);
        System.out.println(num);
        sqlSession.commit();
    }

    /*<!--
    //多id查询
    List<Users> selectByIDS(Integer[] arr);-->*/
    @Test
    public void testSelectByIDS(){
        Integer[] array = {1,3,5,31,32,36};
        for(int i=0;i<array.length;i++){
            System.out.println(array[i]);
        }
        List<Users> list = usersMapper.selectByIDS(array);
        list.forEach(users -> System.out.println(users));

    }

    @Test
    public void testaddUserList() throws ParseException {
        Users users1 = new Users("翘臀大美女1",sdf.parse("2022-02-02"),"2","日本1");
        Users users2 = new Users("翘臀大美女2",sdf.parse("2022-02-02"),"2","日本2");
        Users users3 = new Users("翘臀大美女3",sdf.parse("2022-02-02"),"2","日本3");
        Users users4 = new Users("翘臀大美女4",sdf.parse("2022-02-02"),"2","日本4");
List list = new ArrayList();
list.add(users1);
list.add(users2);
list.add(users3);
list.add(users4);
        int i = usersMapper.addUserList(list);
        System.out.println(i);
        sqlSession.commit();
    }
   /*
    <!--//查询指定日期范围内的信息
    List<Users> getByBirthday(Date begin,Date end);
    对应between #{arg0} in #{arg1}*/
    @Test
    public void testgetBirthday() throws ParseException {
        Date begin = sdf.parse("2018-02-02");
        Date end = sdf.parse("2021-08-08");
        List<Users> byBirthday = usersMapper.getByBirthday(begin, end);
        byBirthday.forEach(users -> System.out.println(users));
        
    }
}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值