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 //注入的是实体管理器,执行持久化操作

    EntityManager entityManager;
public String getInfoWebWaring(String column){

String SQL = "SELECT "+column+" FROM config LIMIT 1";

Object obj = entityManager.createNativeQuery(SQL).getSingleResult();
return String.valueOf(obj);

}
public WebInfo getWebInfo(String [] args){
String SQL = "SELECT id, "+

args[0]+" log_url, "+

args[1]+" title, "+

args[2]+" key_word, "+

args[3]+" description, "+

args[4]+" address, "+

args[5]+" contact "+

"FROM config LIMIT 1";
WebInfo webInfo = (WebInfo) entityManager.createNativeQuery(SQL,WebInfo.class).getSingleResult();
return 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  //注入informationDaoEM

private InformationDaoEM informationDaoEM;
@Override

public String getWebWaring(String column) {
return informationDaoEM.getInfoWebWaring(column);

}
@Override

public WebInfo getWebInfo(String[] args) {
return informationDaoEM.getWebInfo(args);

}
}