springboot根据表名和字段查询和更新实现动态sql(jpa,mybatis)

最近需要做一个根据表名和字段更新表的功能，项目结构是springboot+jpa，记录如下：

1.经查阅资料jpa和hibernate自带对原生sql的支持都是不支持该操作的，原因是：jpa和hibernate在进行参数替换的时候是使用占位符的形式，防止了sql的注入，在解析会给参数带上单引号，但是可以通过定义原生的sql进行解决动态sql查询，但无法实现动态sql更新，如果需要更新必须获取到表对应的实体类

例子实现动态查询sql，假设目前有两张表，表ta、tb,拥有相同的字段 id 、name、desc

pom.xml

 <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

ta实体类

package com.example.demo;

import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.util.Objects;

/**
 * Author: hezishan
 * Date: 2018/7/11.
 * Description:
 **/
@Entity
public class Ta {
    private int id;
    private String name;
    private String desc;

    @Id
    @Column(name = "id")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Basic
    @Column(name = "name")
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Basic
    @Column(name = "desc")
    public String getDesc() {
        return desc;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Ta ta = (Ta) o;
        return id == ta.id &&
                Objects.equals(name, ta.name) &&
                Objects.equals(desc, ta.desc);
    }

    @Override
    public int hashCode() {

        return Objects.hash(id, name, desc);
    }
}

动态sql类：

entityManager来定义原生sql

package com.example.demo;

import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.Map;

/**
 * Author: hezishan
 * Date: 2018/7/11.
 * Description:
 **/
@Component
public class UpdateResponsity {

    @PersistenceContext //注入的是实体管理器,执行持久化操作
    EntityManager entityManager;

    public  Object selectByTableNameAndAttr(String tableName,String attr,String attrVal){
        String sql="select * from "+tableName + " where "+attr+" = "+attrVal;
        Object object=entityManager.createNativeQuery(sql).getSingleResult();
        return object;
    }
    
}

测试类：

@RunWith(SpringRunner.class)
@SpringBootTest
public class DemoApplicationTests {

    @Autowired
    UpdateResponsity updateResponsity;

    

    @Autowired


    @Test
    public void contextLoads() {
        Object object= updateResponsity.selectByTableNameAndAttr("ta","id","1");
        Object object1= updateResponsity.selectByTableNameAndAttr("tb","id","1");
        System.out.println(object);
        System.out.println(object1);
        
    }

}

2.jpa和hibernate做不了，可mybatis可以实现，mybatis不同于其他持久层框架就是其能实现动态sql，mybatis提供两种方式参数的传入，分别是#、$.其区别如下：mybatis 在对 sql 语句进行预编译之前，会对 sql 进行动态解析，解析为一个 BoundSql 对象，也是在此处对动态 SQL 进行处理的。

在动态 SQL 解析阶段， #{ } 和 ${ } 会有不同的表现：#{ } 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符。例如，sqlMap 中如下的 sql 语句

select * from user where name = #{name};

解析为：
select * from user where name = ?;
一个 #{ } 被解析为一个参数占位符 ? 。
而${ } 仅仅为一个纯碎的 string 替换，在动态 SQL 解析阶段将会进行变量替换
例如，sqlMap 中如下的 sql
select * from user where name = '${name}';
当我们传递的参数为 "ruhua" 时，上述 sql 的解析为：
select * from user where name = "ruhua";
预编译之前的 SQL 语句已经不包含变量 name 了。

综上所得， ${ } 的变量的替换阶段是在动态 SQL 解析阶段，而 #{ }的变量的替换是在 DBMS 中。

1.1使用注解方式：

pom.xml

dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

UpdateResposity.java：在表名和属性名的使用$作为值传入

/**
 * Author: hezishan
 * Date: 2018/7/11.
 * Description:
 **/
@Mapper
public interface UpdateResposity {

    @Select("select ${id} as id,${val} as val from ${tableName} where ${atrr}=#{attrVal} ")
    public Map<String,Object> queryByTableNameAndAtrr(@Param("tableName") String tableName,@Param("id") String id,@Param("val") String val,@Param("atrr") String atrr,@Param("attrVal") String attrVal);

    @Update("update ${tableName} set  ${updateAttr}=#{updateAttrVal}  where ${condition}=#{conditionVal}")
    public void updateByTableNameAndAttr(@Param("tableName") String tableName,@Param("condition") String condition,@Param("conditionVal") String conditionVal,@Param("updateAttr") String updateAttr,@Param("updateAttrVal") String updateAttrVal);

}

测试：

@RunWith(SpringRunner.class)
@MapperScan("com.example.demo")
@SpringBootTest
public class DemoApplicationTests {

   @Autowired
   UpdateResposity resposity;

    @Test
    public void contextLoads() {

        Map<String,Object> ob =resposity.queryByTableNameAndAtrr("ta","id","name","id","1");
        if(ob!=null){
            String name=ob.get("val")+"hello ";
            System.out.println(ob.get("id"));
            resposity.updateByTableNameAndAttr("ta","id",ob.get("id")+""
                    ,"name",name);
        }

        System.out.println(ob);
    }
}

1.2使用xml配置文件方式：

pom.xml

   <!--数据库-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--通用mapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>1.1.7</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

mapper接口：

/**
 * Author: hezishan
 * Date: 2018/7/12.
 * Description:
 **/
public interface TMapper {

    Map<String,Object> selectByTableNameAndPrimaryAttr(@Param("tableName") String tableName,@Param("conditions")List<Condition> conditions);

    List<Map<String,Object>> selectByTableNameAndCommonAttr(@Param("tableName") String tableName,@Param("conditions")List<Condition> conditions);

    void updateByTableName(@Param("tableName") String tableName,@Param("changeList")List<Condition> changeList,@Param("conditions")List<Condition> conditions);

}

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="com.example.demo.mapper.TMapper">
  <select id="selectByTableNameAndPrimaryAttr" resultType="java.util.Map" statementType="STATEMENT">
  select * from ${tableName}
  <where>
    <if test="conditions!=null and conditions.size()!=0">
      <foreach collection="conditions" item="condition"
               index="index"  separator=",">
        ${condition.attr}=${condition.value}
      </foreach>
    </if>
  </where>
  </select>
  <select id="selectByTableNameAndCommonAttr" resultType="java.util.Map" statementType="STATEMENT">
    select * from ${tableName}
    <where>
      <if test="conditions!=null and conditions.size()!=0">
        <foreach collection="conditions" item="condition"
                 index="index"  separator=",">
          ${condition.attr}=${condition.value}
        </foreach>
      </if>
    </where>
  </select>

  <update id="updateByTableName" statementType="STATEMENT">
    update ${tableName} set
    <if test="changeList!=null and changeList.size()!=0">
      <foreach collection="changeList" item="change"
               index="index"  separator=",">
        ${change.attr}= #{change.value}
      </foreach>
    </if>
    <where>
      <if test="conditions!=null and conditions.size()!=0">
        <foreach collection="conditions" item="condition"
                 index="index"  separator=",">
          ${condition.attr}=${condition.value}
        </foreach>
      </if>
    </where>
  </update>

</mapper>

condition类：存放属性、属性值

/**
 * Author: hezishan
 * Date: 2018/7/12.
 * Description:
 **/
public class Condition {

    private String attr;

    private String opera;

    private Object value; //get set}

测试：

  public String update() {
        List<Condition> list=new ArrayList<Condition>();
        Condition condition=new Condition();
        condition.setAttr("id");
        condition.setValue(1);
        list.add(condition);
        Map<String,Object> map=tMapper.selectByTableNameAndPrimaryAttr("ta",list);
        if(map!=null){
            List<Condition> changeList=new ArrayList<Condition>();
            Condition cond=new Condition();
            cond.setAttr("name");
            cond.setValue("hello world");
            changeList.add(cond);
            tMapper.updateByTableName("ta",changeList,list);
        }
        return "success" ;
    }

通过上面的简单例子可以看出，使用xml配置方式能够指定多参数，适用范围为大。

提供mybatis带通用mapper逆向工程：https://github.com/hzsbrave/mybatisgenerator.git