1. 在SpringBoot + SpringDataJPA的框架中,怎么使用HQL或者原生SQL呢?
- 第一步获取实体管理器EntityManager
@Autowired
private EntityManager entityManager;
- 第二步,根据语句创建Query对象
// 如果你的语句是JPQL,那么应当使用createQuery方法创建Query对象
Query query = entityManager.createQuery("SELECT u FROM User u WHERE u.username = :name");
// 使用命名参数时的参数传入方式
query.setParameter("name","张三");
// 如果你的语句是原生SQL,那么应当使用createNativeQuery方法创建Query对象
Query query = entityManager.createNativeQuery("SELECT * FROM tb_user WHERE username = ?1");
// 使用位置参数时的参数传入方式
query.setParameter("张三");
- 第三步,根据要获取的数据类型设置Query参数
// 使用原生SQL创建查询
Query query = entityManager.createNativeQuery("SELECT * FROM tb_user WHERE age > :age");
// 传入命名参数值
query.setParameter("age",20);
// 设置查询起始位置与最大查询数(设置分页信息)
// 如果你只打算查询一条数据,那么就不需要设置此参数,直接使用getSingleResult方法获取第一条数据即可
query.setFirstResult(startWith).setMaxResults(rows);
// 设置响应结果类型为MAP,如果你使用的是JPQL,那么在unwrap方法中传入的应当是QueryImpl.class
// 如果你不打算设置响应结果为MAP,则忽略此步骤
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
- 第四步,获取结果
// 获取多条响应数据
List<Map<String, Object>> result = query.getResultList();
// 获取单条响应数据
Map<String, Object> result = (Map<String, Object>) query.getSingleResult();
2. 保姆级代码示范
执行SQL可以获得的四种数据类型:
- List<Map<String,Object>>
- Map<String,Object>
- List<String>
- Object
在此只实现前三种
package com.auro.admin.videnter.service.impl;
import com.auro.admin.videnter.service.SqlTestService;
import com.auro.models.videnter.tools.JpqlBuilder;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.List;
import java.util.Map;
/**
* @author adinlead
* @create 2022/7/11 下午4:08
*/
public class SqlTestServiceImpl implements SqlTestService {
@Autowired
private EntityManager entityManager;
/**
* 执行SQL获取List<Map<String,Object>>类型的结果
* @param builder Jpql构建器
* @param page 页码
* @param rows 条目数
* @return 查询结果
*/
@Override
public List<Map<String, Object>> queryToMapList(JpqlBuilder builder, int page, int rows) {
int startWith = (page - 1) * rows;
Query query = entityManager.createNativeQuery(builder.getSql());
builder.paramWrite(query);
query.setFirstResult(startWith).setMaxResults(rows);
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.getResultList();
}
/**
* 执行SQL获取List<Object>类型的结果
* @param builder Jpql构建器
* @param page 页码
* @param rows 条目数
* @return
*/
@Override
public List<Object> queryToList(JpqlBuilder builder, int page, int rows) {
int startWith = (page - 1) * rows;
Query query = entityManager.createNativeQuery(builder.getSql());
builder.paramWrite(query);
query.setFirstResult(startWith).setMaxResults(rows);
return query.getResultList();
}
/**
* 执行SQL获取Map<String, Object>类型的结果
* @param builder Jpql构建器
* @return
*/
@Override
public Map<String, Object> queryToMap(JpqlBuilder builder) {
Query query = entityManager.createNativeQuery(builder.getSql());
builder.paramWrite(query);
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return (Map<String, Object>) query.getSingleResult();
}
}
测试代码:
package com.auro;
import com.auro.admin.videnter.service.SqlTestService;
import com.auro.models.videnter.tools.JpqlBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;
import java.util.Map;
/**
* @author adinlead
* @create 2022/7/11 下午4:20
*/
public class JpqlTest {
@Autowired
private SqlTestService sqlTestService;
/*
测试表:test_user
| id | username | sex | age | occupation |
| 15 | 张三 | 1 | 22 | 医生 |
| 16 | 李四 | 1 | 33 | 工程师 |
| 17 | 王五 | 1 | 18 | 学生 |
| 18 | 赵六 | 1 | 37 | 厨师 |
| .. | ... | .. | .. | .... |
*/
public void test() {
JpqlBuilder builder = new JpqlBuilder();
/* ========== 多条数据查询 ========== */
builder.append("SELECT * FROM test_user WHERE")
// 性别为1
.append("sex = ").element(1)
// 年龄大于30
.append("age > ").element(30);
// 注意! 传入的构建器需要调用builder()方法
List<Map<String, Object>> result0 = sqlTestService.queryToMapList(builder.builder(), 1, 10);
System.out.println(result0);
/* 输出结果(格式化后)
[
{
"id": 16,
"username": "李四",
"sex": 1,
"age": 33,
"occupation": "工程师"
},
{
"id": 18,
"username": "赵六",
"sex": 1,
"age": 37,
"occupation": "厨师"
}
]
*/
// 清理构建器
builder.clear();,
,
/* ========== 单条数据查询 ========== */
builder.append("SELECT * FROM test_user WHERE")
// ID为15
.append("id = ").element(15L);
Map<String, Object> result1 = sqlTestService.queryToMap(builder.builder());
System.out.println(result1);
/* 输出结果(格式化后)
{
"id": 16,
"username": "李四",
"sex": 1,
"age": 33,
"occupation": "工程师"
}
*/
// 清理构建器
builder.clear();
/* ========== 单列数据查询 ========== */
builder.append("SELECT distinct username FROM test_user WHERE")
// 性别为1
.append("sex = ").element(1);
List<Object> result2 = sqlTestService.queryToList(builder.builder(), 1, 10);
System.out.println(result2);
/* 输出结果(格式化后)
[
"张三",
"李四",
"王五",
"赵六"
]
*/
}
}
附上Jpql构建器代码:
package com.auro.models.videnter.tools;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author adinlead
* @create 2020/6/21 下午3:38
*/
public class JpqlBuilder {
public JpqlBuilder() {
this.c = 0;
this.sqlBuilder = new StringBuilder();
this.sqlParams = new ArrayList<>();
this.sqlArgs = new HashMap<>();
}
/**
* 参数位置指示器
*/
private int c;
/**
* sql拼接容器
*/
private final StringBuilder sqlBuilder;
/**
* 参数队列
*/
private final List<Object> sqlParams;
/**
* 键值参数容器
*/
private final Map<String, Object> sqlArgs;
/**
* 构建后的SQL
*/
private String sql;
/**
* 构建后的参数队列
*/
private Object[] params;
/**
* 构建后的键值参数容器
*/
private Map<String, Object> args;
public JpqlBuilder append(String query) {
this.sqlBuilder.append(query);
return this;
}
public JpqlBuilder element(Object o) {
this.sqlBuilder.append("?").append(++this.c);
this.sqlParams.add(o);
return this;
}
public JpqlBuilder element(String k, Object o) {
this.sqlArgs.put(k, o);
return this;
}
/**
* 执行构建,将SQL与参数固定下来
* @return this
*/
public JpqlBuilder builder() {
this.sql = this.sqlBuilder.toString();
this.params = this.sqlParams.toArray();
this.args = this.sqlArgs;
return this;
}
/**
* 清理构建器
* @return this
*/
public JpqlBuilder clear() {
this.c = 0;
this.sqlBuilder.setLength(0);
this.sqlParams.clear();
this.sqlArgs.clear();
this.sql = null;
this.params = null;
this.args = null;
return this;
}
public String getSql() {
return sql;
}
public Object[] getParams() {
return params;
}
public Map<String, Object> getArgs() {
return args;
}
@Override
public String toString() {
return "SqlBuilder{" +
"sqlBuilder=" + sqlBuilder +
", sqlParams=" + sqlParams +
'}';
}
/**
* 将参数写入Query中
* @param query
*/
public void paramWrite(Query query) {
if (this.params != null && this.params.length > 0) {
for (int i = 0; i < this.params.length; i++) {
int idx = i + 1;
Object param = this.params[i];
query.setParameter(idx, param);
}
}
if (this.args != null && !this.args.isEmpty()) {
this.args.forEach(query::setParameter);
}
}
}
public class SqlTestServiceImpl implements SqlTestService {
@Autowired
private EntityManager entityManager;
/**
* 执行SQL获取List<Map<String,Object>>类型的结果
* @param builder Jpql构建器
* @param page 页码
* @param rows 条目数
* @return 查询结果
*/
@Override
public List<Map<String, Object>> queryToMapList(JpqlBuilder builder, int page, int rows) {
int startWith = (page - 1) * rows;
Query query = entityManager.createNativeQuery(builder.getSql());
builder.paramWrite(query);
query.setFirstResult(startWith).setMaxResults(rows);
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.getResultList();
}
/**
* 执行SQL获取List<Object>类型的结果
* @param builder Jpql构建器
* @param page 页码
* @param rows 条目数
* @return
*/
@Override
public List<Object> queryToList(JpqlBuilder builder, int page, int rows) {
int startWith = (page - 1) * rows;
Query query = entityManager.createNativeQuery(builder.getSql());
builder.paramWrite(query);
query.setFirstResult(startWith).setMaxResults(rows);
return query.getResultList();
}
/**
* 执行SQL获取Map<String, Object>类型的结果
* @param builder Jpql构建器
* @return
*/
@Override
public Map<String, Object> queryToMap(JpqlBuilder builder) {
Query query = entityManager.createNativeQuery(builder.getSql());
builder.paramWrite(query);
query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return (Map<String, Object>) query.getSingleResult();
}
}
测试代码:
package com.auro;
import com.auro.admin.videnter.service.SqlTestService;
import com.auro.models.videnter.tools.JpqlBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;
import java.util.Map;
/**
* @author adinlead
* @create 2022/7/11 下午4:20
*/
public class JpqlTest {
@Autowired
private SqlTestService sqlTestService;
/*
测试表:test_user
| id | username | sex | age | occupation |
| 15 | 张三 | 1 | 22 | 医生 |
| 16 | 李四 | 1 | 33 | 工程师 |
| 17 | 王五 | 1 | 18 | 学生 |
| 18 | 赵六 | 1 | 37 | 厨师 |
| .. | ... | .. | .. | .... |
*/
public void test() {
JpqlBuilder builder = new JpqlBuilder();
/* ========== 多条数据查询 ========== */
builder.append("SELECT * FROM test_user WHERE")
// 性别为1
.append("sex = ").element(1)
// 年龄大于30
.append("age > ").element(30);
// 注意! 传入的构建器需要调用builder()方法
List<Map<String, Object>> result0 = sqlTestService.queryToMapList(builder.builder(), 1, 10);
System.out.println(result0);
/* 输出结果(格式化后)
[
{
"id": 16,
"username": "李四",
"sex": 1,
"age": 33,
"occupation": "工程师"
},
{
"id": 18,
"username": "赵六",
"sex": 1,
"age": 37,
"occupation": "厨师"
}
]
*/
// 清理构建器
builder.clear();,命名
,
/* ========== 单条数据查询 ========== */
builder.append("SELECT * FROM test_user WHERE")
// ID为15
.append("id = ").element(15L);
Map<String, Object> result1 = sqlTestService.queryToMap(builder.builder());
System.out.println(result1);
/* 输出结果(格式化后)
{
"id": 16,
"username": "李四",
"sex": 1,
"age": 33,
"occupation": "工程师"
}
*/
// 清理构建器
builder.clear();
/* ========== 单列数据查询 ========== */
builder.append("SELECT distinct username FROM test_user WHERE")
// 性别为1
.append("sex = ").element(1);
List<Object> result2 = sqlTestService.queryToList(builder.builder(), 1, 10);
System.out.println(result2);
/* 输出结果(格式化后)
[
"张三",
"李四",
"王五",
"赵六"
]
*/
}
}
附上Jpql构建器代码:
package com.auro.models.videnter.tools;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author adinlead
* @create 2020/6/21 下午3:38
*/
public class JpqlBuilder {
public JpqlBuilder() {
this.c = 0;
this.sqlBuilder = new StringBuilder();
this.sqlParams = new ArrayList<>();
this.sqlArgs = new HashMap<>();
}
/**
* 参数位置指示器
*/
private int c;
/**
* sql拼接容器
*/
private final StringBuilder sqlBuilder;
/**
* 位置参数容器
*/
private final List<Object> sqlParams;
/**
* 命名参数容器
*/
private final Map<String, Object> sqlArgs;
/**
* 构建后的SQL
*/
private String sql;
/**
* 构建后的位置参数容器
*/
private Object[] params;
/**
* 构建后的命名参数容器
*/
private Map<String, Object> args;
public JpqlBuilder append(String query) {
this.sqlBuilder.append(query);
return this;
}
public JpqlBuilder element(Object o) {
this.sqlBuilder.append("?").append(++this.c);
this.sqlParams.add(o);
return this;
}
public JpqlBuilder element(String k, Object o) {
this.sqlArgs.put(k, o);
return this;
}
/**
* 执行构建,将SQL与参数固定下来
* @return this
*/
public JpqlBuilder builder() {
this.sql = this.sqlBuilder.toString();
this.params = this.sqlParams.toArray();
this.args = this.sqlArgs;
return this;
}
/**
* 清理构建器
* @return this
*/
public JpqlBuilder clear() {
this.c = 0;
this.sqlBuilder.setLength(0);
this.sqlParams.clear();
this.sqlArgs.clear();
this.sql = null;
this.params = null;
this.args = null;
return this;
}
public String getSql() {
return sql;
}
public Object[] getParams() {
return params;
}
public Map<String, Object> getArgs() {
return args;
}
@Override
public String toString() {
return "SqlBuilder{" +
"sqlBuilder=" + sqlBuilder +
", sqlParams=" + sqlParams +
'}';
}
/**
* 将参数写入Query中
* @param query
*/
public void paramWrite(Query query) {
if (this.params != null && this.params.length > 0) {
for (int i = 0; i < this.params.length; i++) {
int idx = i + 1;
Object param = this.params[i];
query.setParameter(idx, param);
}
}
if (this.args != null && !this.args.isEmpty()) {
this.args.forEach(query::setParameter);
}
}
}
注意! 位置参数不能与命名参数混用,即不能出现下面的语法:
SELECT * FROM test_user WHERE sex = ?1 AND age > :age
997
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
