SpringDataJPA原生sql查询方式的封装操作

工具类相关代码

使用到了apache的map2bean工具类导入方法

<dependency>
    <groupId>commons-beanutils</groupId>
    <artifactId>commons-beanutils</artifactId>
    <version>1.9.3</version>
</dependency>

import org.apache.commons.beanutils.BeanUtils;
import java.util.Map;
/**
 * 将查询结果 map 封装成对应的javaBean，支持级联 ，但是属性不能重复
 * 对应的javaBean的属性名必须以小驼峰形式命名，否则无法填充数据
 */
public class Map2Bean {
    private Map2Bean() {
    }
    /**
     * 将 map 数据封装成javaBean
     *
     * @param map   Map类型数据
     * @param clazz 需要转换的JavaBean
     * @param <T>   泛型
     * @return JavaBean
     */
    public static <T> T convert(Map<String, Object> map, Class<T> clazz) {
        if (map == null || clazz == null) {
            return null;
        }
        T result = null;
        try {
            result = clazz.newInstance();
            BeanUtils.populate(result, map);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}

100

101

import java.io.Serializable;
import java.util.List;
/**
 * Page is the result of Model.paginate(......) or Db.paginate(......)
 */
public class Page<T> implements Serializable {
    private static final long serialVersionUID = -5395997221963176643L;
    private List<T> list;           // list result of this page
    private int pageNumber;             // page number
    private int pageSize = 10;            // result amount of this page
    private int totalPage;          // total page
    private int totalRow;           // total row
    public Page(int pageNumber) {
        this.pageNumber = pageNumber;
    }
    /**
     * Constructor.
     *
     * @param list       the list of paginate result
     * @param pageNumber the page number
     * @param pageSize   the page size
     * @param totalPage  the total page of paginate
     * @param totalRow   the total row of paginate
     */
    public Page(List<T> list, int pageNumber, int pageSize, int totalPage, int totalRow) {
        this.list = list;
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
        this.totalPage = totalPage;
        this.totalRow = totalRow;
    }
    public Page(int pageNumber, int pageSize) {
        this.pageNumber = pageNumber;
        this.pageSize = pageSize;
    }
    /**
     * Return list of this page.
     */
    public List<T> getList() {
        return list;
    }
    /**
     * Return page number.
     */
    public int getPageNumber() {
        return pageNumber;
    }
    /**
     * Return page size.
     */
    public int getPageSize() {
        return pageSize;
    }
    /**
     * Return total page.
     */
    public int getTotalPage() {
        totalPage = totalRow / pageSize;
        if (totalRow % pageSize > 0) {
            totalPage++;
        }
        return totalPage;
    }
    /**
     * Return total row.
     */
    public int getTotalRow() {
        return totalRow;
    }
    public boolean isFirstPage() {
        return pageNumber == 1;
    }
    public boolean isLastPage() {
        return pageNumber == totalPage;
    }
    public void setList(List<T> list) {
        this.list = list;
    }
    public void setPageNumber(int pageNumber) {
        this.pageNumber = pageNumber;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    public void setTotalRow(int totalRow) {
        this.totalRow = totalRow;
    }
    @Override
    public String toString() {
        return "Page{" +
                "list=" + list +
                ", pageNumber=" + pageNumber +
                ", pageSize=" + pageSize +
                ", totalPage=" + totalPage +
                ", totalRow=" + totalRow +
                '}';
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
/**
 * Record
 */
public class Record implements Serializable {
    private static final long serialVersionUID = 905784513600884082L;
    private Map<String, Object> columns = new HashMap<>();
    public Record() {
    }
    public Record(Map<String, Object> columns) {
        this.columns = columns;
    }
    public Map<String, Object> getColumns() {
        return columns;
    }
    public Record setColumns(Map<String, Object> columns) {
        this.getColumns().putAll(columns);
        return this;
    }
    public Record setColumns(Record record) {
        getColumns().putAll(record.getColumns());
        return this;
    }
    public Record remove(String column) {
        getColumns().remove(column);
        return this;
    }
    public Record remove(String... columns) {
        if (columns != null) {
            for (String c : columns) {
                this.getColumns().remove(c);
            }
        }
        return this;
    }
    public Record removeNullValueColumns() {
        for (java.util.Iterator<Map.Entry<String, Object>> it = getColumns().entrySet().iterator(); it.hasNext(); ) {
            Map.Entry<String, Object> e = it.next();
            if (e.getValue() == null) {
                it.remove();
            }
        }
        return this;
    }
    /**
     * Keep columns of this record and remove other columns.
     *
     * @param columns the column names of the record
     */
    public Record keep(String... columns) {
        if (columns != null && columns.length > 0) {
            Map<String, Object> newColumns = new HashMap<String, Object>(columns.length);    // getConfig().containerFactory.getColumnsMap();
            for (String c : columns) {
                if (this.getColumns().containsKey(c)) {    // prevent put null value to the newColumns
                    newColumns.put(c, this.getColumns().get(c));
                }
            }
            this.getColumns().clear();
            this.getColumns().putAll(newColumns);
        } else {
            this.getColumns().clear();
        }
        return this;
    }
    /**
     * Keep column of this record and remove other columns.
     *
     * @param column the column names of the record
     */
    public Record keep(String column) {
        if (getColumns().containsKey(column)) {    // prevent put null value to the newColumns
            Object keepIt = getColumns().get(column);
            getColumns().clear();
            getColumns().put(column, keepIt);
        } else {
            getColumns().clear();
        }
        return this;
    }
    public Record clear() {
        getColumns().clear();
        return this;
    }
    public Record set(String column, Object value) {
        getColumns().put(column, value);
        return this;
    }
    public <T> T get(String column) {
        return (T) getColumns().get(column);
    }
    public <T> T get(String column, Object defaultValue) {
        Object result = getColumns().get(column);
        return (T) (result != null ? result : defaultValue);
    }
    /**
     * Get column of mysql type: varchar, char, enum, set, text, tinytext, mediumtext, longtext
     */
    public String getStr(String column) {
        return (String) getColumns().get(column);
    }
    /**
     * Get column of mysql type: int, integer, tinyint(n) n > 1, smallint, mediumint
     */
    public Integer getInt(String column) {
        return (Integer) getColumns().get(column);
    }
    /**
     * Get column of mysql type: bigint
     */
    public Long getLong(String column) {
        return (Long) getColumns().get(column);
    }
    /**
     * Get column of mysql type: unsigned bigint
     */
    public java.math.BigInteger getBigInteger(String column) {
        return (java.math.BigInteger) getColumns().get(column);
    }
    /**
     * Get column of mysql type: date, year
     */
    public java.util.Date getDate(String column) {
        return (java.util.Date) getColumns().get(column);
    }
    /**
     * Get column of mysql type: time
     */
    public java.sql.Time getTime(String column) {
        return (java.sql.Time) getColumns().get(column);
    }
    /**
     * Get column of mysql type: timestamp, datetime
     */
    public java.sql.Timestamp getTimestamp(String column) {
        return (java.sql.Timestamp) getColumns().get(column);
    }
    /**
     * Get column of mysql type: real, double
     */
    public Double getDouble(String column) {
        return (Double) getColumns().get(column);
    }
    /**
     * Get column of mysql type: float
     */
    public Float getFloat(String column) {
        return (Float) getColumns().get(column);
    }
    /**
     * Get column of mysql type: bit, tinyint(1)
     */
    public Boolean getBoolean(String column) {
        return (Boolean) getColumns().get(column);
    }
    /**
     * Get column of mysql type: decimal, numeric
     */
    public java.math.BigDecimal getBigDecimal(String column) {
        return (java.math.BigDecimal) getColumns().get(column);
    }
    /**
     * Get column of mysql type: binary, varbinary, tinyblob, blob, mediumblob, longblob
     * I have not finished the test.
     */
    public byte[] getBytes(String column) {
        return (byte[]) getColumns().get(column);
    }
    /**
     * Get column of any type that extends from Number
     */
    public Number getNumber(String column) {
        return (Number) getColumns().get(column);
    }
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(super.toString()).append(" {");
        boolean first = true;
        for (Map.Entry<String, Object> e : getColumns().entrySet()) {
            if (first) {
                first = false;
            } else {
                sb.append(", ");
            }
            Object value = e.getValue();
            if (value != null) {
                value = value.toString();
            }
            sb.append(e.getKey()).append(":").append(value);
        }
        sb.append("}");
        return sb.toString();
    }
    @Override
    public boolean equals(Object o) {
        if (!(o instanceof Record)) {
            return false;
        }
        if (o == this) {
            return true;
        }
        return this.getColumns().equals(((Record) o).getColumns());
    }
    @Override
    public int hashCode() {
        return getColumns() == null ? 0 : getColumns().hashCode();
    }
    /**
     * Return column names of this record.
     */
    public String[] getColumnNames() {
        Set<String> attrNameSet = getColumns().keySet();
        return attrNameSet.toArray(new String[attrNameSet.size()]);
    }
    /**
     * Return column values of this record.
     */
    public Object[] getColumnValues() {
        java.util.Collection<Object> attrValueCollection = getColumns().values();
        return attrValueCollection.toArray(new Object[attrValueCollection.size()]);
    }
    /**
     * Return json string of this record.
     */
    public String toJson() {
        throw new UnsupportedOperationException("还未实现");
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
/**
 * 作者：guoyzh
 * 时间：2019/8/20 12:53
 * 功能：使用jpa进行原生sql查询的工具类 使用AutoWrite注入即可使用
 */
@Component
public class SqlUtils {
    @Autowired
    @PersistenceContext
    private EntityManager entityManager;
    public SqlUtils(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    public SqlUtils() {
    }
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    /**
     * 返回查询的一个Record，没有则为null
     */
    public Record findFirst(String sql, Object... params) {
        return findFirst(sql, Record.class, params);
    }
    public Record findFirst(String sql, Map<String, Object> searchMap) {
        return findFirst(sql, Record.class, searchMap);
    }
    /**
     * 返回查询的一个实体，没有则为null
     */
    public <T> T findFirst(String sql, Class<T> clazz, Object... params) {
        List<T> ts = find(sql, clazz, params);
        return (ts == null || ts.size() == 0) ? null : ts.get(0);
    }
    public <T> T findFirst(String sql, Class<T> clazz, Map<String, Object> searchMap) {
        List<T> ts = find(sql, clazz, searchMap);
        return (ts == null || ts.size() == 0) ? null : ts.get(0);
    }
    public List<Record> find(String sql, Object... params) {
        return find(sql, Record.class, params);
    }
    public List<Record> find(String sql, Map<String, Object> searchMap) {
        return find(sql, Record.class, searchMap);
    }
    public List<Record> find(String sql) {
        return find(sql, Record.class, (Map<String, Object>) null);
    }
    /**
     * 查询列表
     *
     * @param sql    native sql语句，可以包含？
     * @param clazz  返回的类型，可以是JavaBean，可以是Record
     * @param params 参数列表
     * @param <T>    泛型
     * @return 查询列表结果
     */
    public <T> List<T> find(String sql, Class<T> clazz, Object... params) {
        Session session = entityManager.unwrap(Session.class);
        org.hibernate.Query query = session.createSQLQuery(sql);
        //0-Based
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i, params[i]);
        }
        List list = getList(query, clazz);
        return list;
    }
    /**
     * 查询列表
     *
     * @param sql       native sql语句，可以包含 :具名参数
     * @param clazz     返回的类型，可以是JavaBean，可以是Record
     * @param searchMap 具名参数列表
     * @param <T>       泛型
     * @return 查询列表结果
     */
    public <T> List<T> find(String sql, Class<T> clazz, Map<String, Object> searchMap) {
        Session session = entityManager.unwrap(Session.class);
        org.hibernate.Query query = session.createSQLQuery(sql);
        if (null != searchMap) {
            searchMap.forEach(query::setParameter);
        }
        List list = getList(query, clazz);
        return list;
    }
    /**
     * ----------------------------------------------record-positioned-parameter---------------------------------------------------
     */
    public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Object... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Object... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) {
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Object... params) {
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, params);
    }
    /**
     * ----------------------------------------------record-maped-parameter---------------------------------------------------
     */
    public Page<Record> paginate(String nativeSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    public Page<Record> paginate(String nativeSQL, Boolean isGroupBySql, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    public Page<Record> paginate(String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        return paginate(null, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    public Page<Record> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, Record.class, pageNumber, pageSize, searchMap);
    }
    /**
     * ----------------------------------------------JavaBean-positioned-parameter---------------------------------------------------
     */
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
    }
    public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
    }
    public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, String... params) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, params);
    }
    /**
     * ----------------------------------------------JavaBean-maped-parameter---------------------------------------------------
     */
    public <T> Page<T> paginate(String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
    }
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        String nativeCountSQL = getCountSQL(nativeSQL);
        return paginate(isGroupBySql, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
    }
    public <T> Page<T> paginate(String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        return paginate(null, nativeSQL, nativeCountSQL, clazz, pageNumber, pageSize, searchMap);
    }
    /**
     * @param pageNumber     pageNumber
     * @param pageSize       pageSize
     * @param isGroupBySql   是否包含Group by语句，影响总行数
     * @param nativeSQL      原生SQL语句 {@see QueryHelper}
     * @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper}
     * @param clazz          JavaBean风格的DTO或者Record，需要用别名跟JavaBean对应
     * @param <T>            返回JavaBean风格的DTO或者Record
     * @param params         按照顺序给条件
     */
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Object... params) {
        if (pageNumber < 1 || pageSize < 1) {
            throw new IllegalArgumentException("pageNumber and pageSize must more than 0");
        }
        Query countQuery = entityManager.createNativeQuery(nativeCountSQL);
        //坑死人，1-Based
        for (int i = 1; i <= params.length; i++) {
            countQuery.setParameter(i, params[i - 1]);
        }
        List countQueryResultList = countQuery.getResultList();
        int size = countQueryResultList.size();
        if (isGroupBySql == null) {
            isGroupBySql = size > 1;
        }
        long totalRow;
        if (isGroupBySql) {
            totalRow = size;
        } else {
            totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0;
        }
        if (totalRow == 0) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0);
        }
        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }
        if (pageNumber > totalPage) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow);
        }
        Session session = entityManager.unwrap(Session.class);
        int offset = pageSize * (pageNumber - 1);
        org.hibernate.Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize);
        //坑死人，0-Based
        for (int i = 0; i < params.length; i++) {
            query.setParameter(i, params[i]);
        }
        final List list = getList(query, clazz);
        return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow);
    }
    /**
     * @param pageNumber     pageNumber
     * @param pageSize       pageSize
     * @param isGroupBySql   是否包含Group by语句，影响总行数
     * @param nativeSQL      原生SQL语句 {@see QueryHelper}
     * @param nativeCountSQL 原生求总行数的SQL语句 {@see QueryHelper}
     * @param clazz          JavaBean风格的DTO或者Record，需要用别名跟JavaBean对应
     * @param <T>            返回JavaBean风格的DTO或者Record
     * @param searchMap      k-v条件
     */
    public <T> Page<T> paginate(Boolean isGroupBySql, String nativeSQL, String nativeCountSQL, Class<T> clazz, int pageNumber, int pageSize, Map<String, Object> searchMap) {
        if (pageNumber < 1 || pageSize < 1) {
            throw new IllegalArgumentException("pageNumber and pageSize must more than 0");
        }
        Query countQuery = entityManager.createNativeQuery(nativeCountSQL);
        if (null != searchMap) {
            searchMap.forEach(countQuery::setParameter);
        }
        List countQueryResultList = countQuery.getResultList();
        int size = countQueryResultList.size();
        if (isGroupBySql == null) {
            isGroupBySql = size > 1;
        }
        long totalRow;
        if (isGroupBySql) {
            totalRow = size;
        } else {
            totalRow = (size > 0) ? ((Number) countQueryResultList.get(0)).longValue() : 0;
        }
        if (totalRow == 0) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, 0, 0);
        }
        int totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }
        if (pageNumber > totalPage) {
            return new Page<>(new ArrayList<>(0), pageNumber, pageSize, totalPage, (int) totalRow);
        }
        Session session = entityManager.unwrap(Session.class);
        int offset = pageSize * (pageNumber - 1);
        org.hibernate.Query query = session.createSQLQuery(nativeSQL).setFirstResult(offset).setMaxResults(pageSize);
        if (null != searchMap) {
            searchMap.forEach(query::setParameter);
        }
        final List list = getList(query, clazz);
        return new Page<T>(list, pageNumber, pageSize, totalPage, (int) totalRow);
    }
    private <T> List getList(org.hibernate.Query query, Class<T> clazz) {
        final List list;
        //Object[].class
        if (Object[].class == clazz) {
            return query.list();
        }
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List mapList = query.list();
        list = new ArrayList(mapList.size());
        mapList.forEach(map -> {
            Map<String, Object> tmp = (Map<String, Object>) map;
            //Record.class
            if (Record.class == clazz) {
                list.add(new Record(tmp));
                //Map及子类
            } else if (Map.class.isAssignableFrom(clazz)) {
                list.add(tmp);
                //JavaBean风格
            } else {
                list.add(Map2Bean.convert(tmp, clazz));
            }
        });
        return list;
    }
    /*private <T> List getList(org.hibernate.Query query, Class<T> clazz) {
        final List list;
        if(Record.class == clazz){
            //返回Record
            query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            List mapList = query.list();
            list = new ArrayList(mapList.size());
            mapList.forEach(map->{
                Map<String , Object> tmp = (Map<String , Object>) map;
                list.add(new Record(tmp));
            });
        }else {
            //返回JavaBean
            //只能返回简单的Javabean，不具备级联特性
            query.setResultTransformer(Transformers.aliasToBean(clazz));
            list = query.list();
        }
        return list;
    }*/
    private String getCountSQL(String sql) {
        String countSQL = "SELECT COUNT(*) AS totalRow " + sql.substring(sql.toUpperCase().indexOf("FROM"));
        return replaceOrderBy(countSQL);
    }
    protected static class Holder {
        private static final Pattern ORDER_BY_PATTERN = Pattern.compile(
                "order\\s+by\\s+[^,\\s]+(\\s+asc|\\s+desc)?(\\s*,\\s*[^,\\s]+(\\s+asc|\\s+desc)?)*",
                Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
    }
    public String replaceOrderBy(String sql) {
        return Holder.ORDER_BY_PATTERN.matcher(sql).replaceAll("");
    }
}

代码中调用

@Autowired
SqlUtils mSqlUtils;
。。。
@Transactional
@ApiOperation("测试")
@PostMapping("/get1")
public Result get1() {
    HashMap<String, Object> map = new HashMap<>();
    map.put("id", "SPA0000001");
    TestResp record = mSqlUtils.findFirst("select * from st_PkgActvty where id = :id", TestResp.class, map);
    return ResultGenerator.genSuccessResult(record);
}

Spring data jpa@query使用原生SQl，需要注意的坑

根据代码来解说：

@Query(value = "select bill.id_ as id, bill.created_date as date, bill.no, lawyer_case .case_no as caseNo, " +
            "lawyer_case .case_name as caseName, customer.no as customerNo, customer.cn_name as customerName, " +
            "bill.total_expense_after_tax, bill.collected_money, bill.book_ticket_amount, bill.version " +
            "e1.name as creator, bill.status" +
            "from bill " +
            "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
            "left join customer on customer.no=bill.customer_no " +
            "left join employee e1 on e1.id_=bill.creator " +
            "where IF (?1!='', customer_no=?1, 1=1) " +
            "and   IF (?2!='', case_no=?2, 1=1) " +
            "and   IF (?3!='', status=?3, 1=1) " +
            "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
            "and   create_by=?5 " +
            "ORDER BY ?#{#pageable} ",
            countQuery = "select count(*) " +
                    "from bill " +
                    "left join lawyer_case on lawyer_case .case_no=bill.case_no " +
                    "left join customer on customer.no=bill.customer_no " +
                    "left join employee e1 on e1.id_=bill.creator " +
                    "where IF (?1!='', customer_no=?1, 1=1) " +
                    "and   IF (?2!='', case_no=?2, 1=1) " +
                    "and   IF (?3!='', status=?3, 1=1) " +
                    "and   IF (?4!='', creator'%',?4,'%')), 1=1) " +
                    "and   create_by=?5 "+
                    "ORDER BY ?#{#pageable} ",
            nativeQuery = true)
    Page<Object[]> findAllBill(String customerNo, String caseNo, Integer status, String creator,
                               String createBy, Pageable pageable);

需要注意的方法有以下几点：

1、From 不支持重命名.

2、返回的是一个page<Object[]>，数组中只保存了数据，没有对应的key，只能根据返回数据的顺序，依次注入到DTO中。

3、对于使用分页，需要：“ORDER BY ?#{#pageable}”，可以直接传入一个pageable对象，会自动解析。

4、注意格式问题，很多时候就是换行的时候，没有空格。

5、仔细对应数据库中表字段，很多时候报某个字段找不到，就是因为字段名写错，和数据库中对应不上。

6、这是解决使用微服务，大量的数据都需要远程调用，会降低程序的性能。

7、使用Pageabel作为参数的时候，去进行分页。刚开始的时候，觉得还是一个可行的办法，但是得注意的时候，当需要排序的时候，是无法加入sort字段的。会一直报错left*。

8、针对7的解决方案，把原生SQL的数据查询和countQuery分成两个查询方法。

得到count，然后进行判断，若是等于0，则直接返回空集合；反之，则取获取数据。需要自己进行分页计算，传入正确的pageNumber和pageSize。

大部分系统都是按照修改时间进行降序排序。所以，order by可以写死。

然后pageNumber和pageSize动态传入。 pageNumber的算法= (pageNumber - 1) * pageSize, 前提是PageNumber是从1开始，若0，则pageNumber=pageNumber * PageSize；这样就可以保证数据的正确。

/**
* pageInfos: 转换之后的数据。
* pageable：传入的pageable.
* totalPage: 第一条SQL算好的返回值。
* 这样就可以统一的返回各种pageDTO。
*/
private Page<T> convertForPage(List<T> pageInfos, Pageable pageable, Integer totalPage) {
        return new PageImpl<>(pageInfos, pageable, totalPage);
    }

以上为个人经验，希望能给大家一个参考，也希望大家多多支持自学编程网。

编程技巧