JPA框架下使用纯粹的原生SQL

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

最近遇到一个需求，查询数据库中对应表的字段是动态的，项目使用的框架使用JPA+Spring Boot，JPA自带原生SQL支持的传入参数是强类型的，无法用于查询语句的字段更改，因为插入字符串的话带有单引号，需要另外定义原生SQL

因此我们创建一个查询类

import com.weiqitonggame.trade.model.WebInfo;

import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

@Component  //标记为组件，spring启动时会将该类扫描进容器


public class InformationDaoEM {
@PersistenceContext </span><span style="color: #008000;">//</span><span style="color: #008000;">注入的是实体管理器,执行持久化操作</span>


EntityManager entityManager;
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getInfoWebWaring(String column){

    String SQL </span>= "SELECT "+column+" FROM config LIMIT 1"<span style="color: #000000;">;

    Object obj </span>=<span style="color: #000000;"> entityManager.createNativeQuery(SQL).getSingleResult();

    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> String.valueOf(obj);

}

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> WebInfo getWebInfo(String [] args){

    String SQL </span>= "SELECT id, "+<span style="color: #000000;">

            args[</span>0]+" log_url, "+<span style="color: #000000;">

            args[</span>1]+" title, "+<span style="color: #000000;">

            args[</span>2]+" key_word, "+<span style="color: #000000;">

            args[</span>3]+" description, "+<span style="color: #000000;">

            args[</span>4]+" address, "+<span style="color: #000000;">

            args[</span>5]+" contact "+

            "FROM config LIMIT 1"<span style="color: #000000;">;

    WebInfo webInfo </span>= (WebInfo) entityManager.createNativeQuery(SQL,WebInfo.<span style="color: #0000ff;">class</span><span style="color: #000000;">).getSingleResult();

    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> webInfo;

}


}

其中
EntityManager.createNativeQuery(SQL)返回的是Object对象
entityManager.createNativeQuery(SQL,WebInfo.class)返回的是映射后的实例对象
实体类WebInfo需要进行以下处理，确保被扫描进spring容器

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import lombok.experimental.Accessors;

import javax.persistence.*;

@Data //生成读写方法


@AllArgsConstructor //生成全参构造方法


@NoArgsConstructor //生成无参构造方法


@Accessors(chain = true)


@Entity(name = "tableName") //标记为entity


public class WebInfo {


@Id


@GeneratedValue(strategy= GenerationType.AUTO)


@Column(name = "id") //查询结果对应字段


private Integer id;


@Column(name = "log_url")


private String logUrl;


@Column(name = "title")


private String title;


@Column(name = "key_word")


private String keyWord;


@Column(name = "description")


private String description;


@Column(name = "address")


private String address;


@Column(name = "contact")


private String contact;

}

Query.getSingleResult() 执行SQL语句，返回一个查询结果，常用的还有以下方法
Query.getResultList() () 执行SQL语句，返回一个List集合
Query.getFirstResult() () 执行SQL语句，返回一个系列结果集合的第一个

调用方式如下:

import org.springframework.stereotype.Service;

import com.weiqitonggame.trade.service.InformationService;

import org.springframework.beans.factory.annotation.Autowired;

import com.weiqitonggame.trade.dao.InformationDaoEM;

import com.weiqitonggame.trade.model.WebInfo;

@Service  //标记为service


public class InformationServiceImpl implements InformationService {
@Autowired  </span><span style="color: #008000;">//</span><span style="color: #008000;">注入informationDaoEM</span>

<span style="color: #0000ff;">private</span><span style="color: #000000;"> InformationDaoEM informationDaoEM;

@Override

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getWebWaring(String column) {

    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> informationDaoEM.getInfoWebWaring(column);

}

@Override

</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> WebInfo getWebInfo(String[] args) {

    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> informationDaoEM.getWebInfo(args);

}


}