mysql 函数动态sql

转载

mob64ca140d96d9 2024-12-03 18:58:52

文章标签 mysql 函数动态sql java jar intellij-idea tomcat 文章分类 MySQL 数据库

动态SQL是mybatis的一个强大的特性。在使用JDBC编程操作数据的时候，如果查询的条件特别多，将条件串联成SQL字符串是一件非常痛苦的事情，通常的解决方法是写很多if-else条件语句对字符串进行拼接，并不能确保不能忘了空格或在字段的最后省略逗号。mybatis使用了一种强大的动态SQL语言来改善这种情形。

用于实现动态SQL的元素如下：

if：利用if实现简单的条件拼接。
choose（when，otherwise）：相当于java中使用的Switch语句，通常与when和otherwise搭配。
where：简化SQL语句中的where的条件判断。
set：解决了动态更新语句
trim：可以灵活的去除多余的关键字
foreach：迭代一个集合，通常用于in条件

1.1 案例描述：

本篇文章将通过具体实例对动态SQL进行详细的讲解，实例如下：模仿“百合网”对人群根据不同要求进行条件查询，从而获得符合条件的人群的个人信息；

1.2 创建数据表：

创建会员表（member），表中包括了人员的id（member_id）、会员昵称（member_nick）、会员性别（member_gender）、会员年龄（member_age）、会员所在城市（member_icity）。代码如下：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`members` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `members`;

/*Table structure for table `member` */

DROP TABLE IF EXISTS `member`;

CREATE TABLE `member` (
  `member_id` int unsigned NOT NULL,
  `member_nick` varchar(20) NOT NULL,
  `member_gender` char(2) NOT NULL,
  `member_age` int NOT NULL,
  `member_city` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/*Data for the table `member` */

insert  into `member`(`member_id`,`member_nick`,`member_gender`,`member_age`,`member_city`) values (1,'小花','女',22,'上海'),(2,'翠花','女',31,'武汉'),(3,'王二狗','男',44,'吉林'),(4,'李二牛','男',28,'武汉'),(5,'王阿姨','女',50,'上海'),(6,'刘大叔','男',50,'武汉');

1.3 创建实体类：

实体类有两个类，Member类是对SQL属性实体的映射文件，而MemberSearchCondition类是后期进行条件查询时候所用到的一些条件属性，具体使用在下面会具体讲解~代码如下：

Member类	MemberSearchCondition类
package com.qhit.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.ToString;/** * Description: member_RemSql * Created by WuHuaSen . * Created Date: 2022/4/7 8:14 * Version: V1.0 */@Data@NoArgsConstructor@AllArgsConstructor@ToStringpublic class Member { private int MemberId; private String MemberNick; private String MemberGender; private int MemberAge; private String MemberCity;}	package com.qhit.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.ToString;/** * Description: member_RemSql * Created by WuHuaSen . * Created Date: 2022/4/7 9:24 * Version: V1.0 */@Data@ToString@AllArgsConstructor@NoArgsConstructorpublic class MemberSearchCondition { private String gender;//性别 private Integer minAge;//最小年纪 private Integer maxAge;//最大年纪 private String city;//所在城市}

1.4 mybatis主配置文件（mybatis-config.xml）：

配置mybatis-config.xml文件，使用properties标签引入外部文件来配置mysql连接信息，并注入MemberDaoMapper.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>
    <properties resource="jdbc.properties"></properties>
    <typeAliases>
        <typeAlias type="com.qhit.pojo.Member" alias="Member"></typeAlias>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql_driver}"/>
                <property name="url" value="${mysql_url}"/>
                <property name="username" value="${mysql_user}"/>
                <property name="password" value="${mysql_password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/MemberDaoMapper.xml"></mapper>
    </mappers>
</configuration>

1.5 SqlSession-工具类封装

封装SqlSession类，内置getSqlSession（boolean IsAutoCommit）设置是否自动提交（默认为false手动提交，true是自动提交）、getMapper（）方法;代码如下:

package com.qhit.Untils;

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;

/**
 * Description: NewSsm
 * Created by WuHuaSen .
 * Created Date:  2022/4/1 16:23
 * Version:  V1.0
 */
public class MybatisUtil {
    //封装会话工厂
    private static SqlSessionFactory Factory;
    /**在进行对象的跨层传递的时候，使用ThreadLocal可以避免多次传递，打破层次间的约束
     */
    private static ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();

    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            Factory = sqlSessionFactoryBuilder.build(is);

        } catch (
                IOException e)

        {
            e.printStackTrace();
        }
    }
    //封装factory方法
    public  static SqlSessionFactory getFactory(){
        return Factory;
    }

    //封装sqlSession会话
    public static SqlSession getSqlSession(boolean IsAutoComiit) {
        SqlSession sqlSession = local.get();
        if (sqlSession == null) {
            sqlSession = Factory.openSession(IsAutoComiit);
            local.set(sqlSession);
        }
        return sqlSession;
    }

    //使用泛型封装getMapper
    public static <T extends Object> T getMapper(Class<T> c) {
        SqlSession sqlSession = getSqlSession(true);
        return sqlSession.getMapper(c);
    }
}

1.6 接口层（Dao层）：

在Dao层中实现方法示例，根据年龄，城市，性别进行筛选会员信息，由于在多条件查询的时候条件不确定，因此在这里有两种实现方式，第一种：是通过采用HashMap来传递参数；第二种是专门定义一个用于存放条件查询的实体类来存放参数（就是上述实体类中MemberSearchCondition类的声明），这两种方式各有优缺点，分别如下：

HashMap VS 定义实体类

HashMap	定义实体类
优点：无需单独定义传递查询条件的类。缺点：当向Map中存放参数的时候，key必须与动态SQL保持一致	优点：设置参数时候无需关注属性名。缺点：需要单独定义一个类封装查询条件参数

具体方法声明，代码如下；

package com.qhit.Dao;

import com.mysql.fabric.xmlrpc.base.Params;
import com.qhit.pojo.Member;
import com.qhit.pojo.MemberSearchCondition;
import org.omg.CORBA.Object;

import java.util.HashMap;
import java.util.List;

/**
 * Description: member_RemSql
 * Created by WuHuaSen .
 * Created Date:  2022/4/7 8:26
 * Version:  V1.0
 */
public interface MemberDao {
    //在多条件查询中，如果查询条件不确定，可以直接使用HashMap作为参数
    public List<Member> SearchMember(HashMap<String, Object> Params);

    //也可以定义专门用于存放查询条件的实体类来存放参数
    public List<Member> SearchMember(MemberSearchCondition Params);

}

MemberDao的映射文件中主要是对SearchMember（）方法的实现，方法的实现使用到了<where>标签进行查询条件筛选，<trim>标签<trim prefix ="where">效果和<where>标签一样可以进行查询条件的筛选，但是单独定义的时候无法过滤本查询语句中的AND（OR），所以不建议使用，如果非要使用,可以定义 <trim prefix="where" prefixOverrides="and | or">以此达到过滤的效果，本查询具体代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.MemberDao">
    <sql id="Member_colum_List">
member_id,member_nick,member_gender,member_age,member_city
    </sql>
    <resultMap id="MemberMap" type="Member">
        <id column="member_id" property="MemberId"></id>
        <result column="member_nick" property="MemberNick"></result>
        <result column="member_gender" property="MemberGender"></result>
        <result column="member_age" property="MemberAge"></result>
        <result column="member_city" property="MemberCity"></result>
    </resultMap>
    <select id="SearchMember" resultMap="MemberMap">
        SELECT
        <include refid="Member_colum_List"/>FROM member
        <trim prefix="where" prefixOverrides="and | or" suffix="order by member_age">
            <if test="gender != null">
                AND member_gender = #{gender}
            </if>
            <if test="minAge != null">
                AND member_age >=#{minAge}
            </if>
            <if test="maxAge != null">
                AND member_age <=#{maxAge}
            </if>
            <if test="city != null">
                AND member_city =#{city}
            </if>
        </trim>
    </select>
</mapper>

<where>与<trim>标签各有千秋，可分不同情况分别进行使用即可，什么时候使用 <where>标签？什么时候使用 <trim>标签呢？下面将具体讲解：

<where>标签 && <trim> 标签 
 
<where>标签:当查询语句中没有后续附加条件的时候（排序），仅有一些拼接条件（AND || OR)的时候，使用该标签可以自动过滤AND || OR
<trim>标签：当查询语句中有附加条件，比如排序的时候，可以使用该标签，该标签提供了后缀的条件的添加。可以进行有效的条件拼接.例如<trim prefix="where" prefixOverrides="and | or" suffix="order by member_age">，即可完成排序操作

1.8 测试类：

测试类有两种实现方式，就是对查询条件的属性进行添加的两种方，一种是是通过HashMap，一种是对创建的条件查询实体类属性的添加，两种方式唯一的不同就是查询条件的添加方式，方法的调用方式都是一致的，即通过工具类进行调用，具体代码如下；

package com.qhit.Dao;

import com.qhit.Untils.MybatisUtil;
import com.qhit.pojo.Member;
import com.qhit.pojo.MemberSearchCondition;
import org.junit.Test;

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

/**
 * Description: member_RemSql
 * Created by WuHuaSen .
 * Created Date:  2022/4/7 8:31
 * Version:  V1.0
 */
public class MemberDaoTest {
    MybatisUtil mybatisUtil = new MybatisUtil();

    @Test
    public void QueryNumberByNick() {
 //------------第一种-------------------------
        HashMap<String, Object> Params1 = new HashMap<>();
        Params1.put("gender", "女");
        Params1.put("minAge", 20);


        //-------------第二种------------------------
        MemberSearchCondition memberSearchCondition = new MemberSearchCondition();
        memberSearchCondition.setCity("武汉");
        memberSearchCondition.setGender("女");
        memberSearchCondition.setMinAge(30);
        memberSearchCondition.setMaxAge(40);
        MemberDao memberDao = mybatisUtil.getMapper(MemberDao.class);
        List<Member> members = memberDao.SearchMember(memberSearchCondition);
        System.out.println(members);
    }
   
}

1.9 测试类结果：

本测试类查询了在武汉市，年龄在30-40岁的女性会员，查到了对个人信息进行打印，找不到打印空~

mysql 函数动态sql_mysql 函数动态sql

2.1 案例描述：

根据地域城市进行会员的筛选，查询到在“上海”、“武汉”的会员的个人信息

2.2 接口层（Dao层）

定义一个方法名为SearchMemberByCity（）的方法，方法返回一个List集合，类型是Member类。代码如下；

package com.qhit.Dao;

import com.mysql.fabric.xmlrpc.base.Params;
import com.qhit.pojo.Member;
import com.qhit.pojo.MemberSearchCondition;
import org.omg.CORBA.Object;

import java.util.HashMap;
import java.util.List;

/**
 * Description: member_RemSql
 * Created by WuHuaSen .
 * Created Date:  2022/4/7 8:26
 * Version:  V1.0
 */
public interface MemberDao {
    //通过城市进行筛选
    public  List<Member> SearchMemberByCity(List<String> cities);
}

2.3 MemberDao的映射文件：

在映射文件中实现接口中的方法，由于查询条件不唯一，所以我们需要使用到<foreach>标签进行条件查询，<foreach>标签中提供了几个属性方法，可以对查询条件进行拼接查询，属性如下；

<foreach>常用标签属性

Collection：传递的查询条件的集合类型
item：循环体中的具体对象，支持属性得点路径访问
separator：在每一个循环条件之后，需要添加的部分，即插入元素之间的分隔符
open：foreach开始的部分，一般与close（）连用
close: foreach结束的部分，一般与open连用；

该实例的接口方法的具体实现代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qhit.Dao.MemberDao">
    <sql id="Member_colum_List">
member_id,member_nick,member_gender,member_age,member_city
    </sql>
    <resultMap id="MemberMap" type="Member">
        <id column="member_id" property="MemberId"></id>
        <result column="member_nick" property="MemberNick"></result>
        <result column="member_gender" property="MemberGender"></result>
        <result column="member_age" property="MemberAge"></result>
        <result column="member_city" property="MemberCity"></result>
    </resultMap>
   
    <select id="SearchMemberByCity" resultMap="MemberMap">
        SELECT <include refid="Member_colum_List"/> FROM member WHERE member_city IN
        <foreach collection="list" item="cityName" separator="," open="(" close=")">
            #{cityName}
        </foreach>
    </select>
</mapper>

2.4 测试类

该测试类需要对查询条件集合进行赋值，即添加查询条件。然后通过接口调用方法，并传递赋值后的集合给方法。代码如下；

package com.qhit.Dao;

import com.qhit.Untils.MybatisUtil;
import com.qhit.pojo.Member;
import com.qhit.pojo.MemberSearchCondition;
import org.junit.Test;

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

/**
 * Description: member_RemSql
 * Created by WuHuaSen .
 * Created Date:  2022/4/7 8:31
 * Version:  V1.0
 */
public class MemberDaoTest {
    MybatisUtil mybatisUtil = new MybatisUtil();

    @Test
    public void SearchMemberByCityTest(){
        List<String> cities = new ArrayList<>();
        cities.add("上海");
        cities.add("武汉");
        MemberDao memberDao = mybatisUtil.getMapper(MemberDao.class);
        List<Member> members = memberDao.SearchMemberByCity(cities);
        for (Member member:members
             ) {
            System.out.println(member);
        }
    }
}

2.5 测试类结果：

mysql 函数动态sql_jar_02

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：正在与另一java安装是什么意思

下一篇：python 约束函数返回类型

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯