DatabaseQuery.java 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396
  1. package org.ssssssss.magicapi.functions;
  2. import org.springframework.jdbc.core.RowMapper;
  3. import org.springframework.jdbc.datasource.DataSourceUtils;
  4. import org.ssssssss.magicapi.cache.SqlCache;
  5. import org.ssssssss.magicapi.config.DynamicDataSource;
  6. import org.ssssssss.magicapi.config.DynamicDataSource.DataSourceNode;
  7. import org.ssssssss.magicapi.config.MagicModule;
  8. import org.ssssssss.magicapi.dialect.Dialect;
  9. import org.ssssssss.magicapi.dialect.DialectUtils;
  10. import org.ssssssss.magicapi.exception.MagicAPIException;
  11. import org.ssssssss.magicapi.model.Page;
  12. import org.ssssssss.magicapi.provider.PageProvider;
  13. import org.ssssssss.magicapi.provider.ResultProvider;
  14. import org.ssssssss.script.MagicScriptContext;
  15. import org.ssssssss.script.annotation.UnableCall;
  16. import org.ssssssss.script.functions.StreamExtension;
  17. import org.ssssssss.script.parsing.GenericTokenParser;
  18. import org.ssssssss.script.parsing.Parser;
  19. import org.ssssssss.script.parsing.TokenStream;
  20. import org.ssssssss.script.parsing.Tokenizer;
  21. import java.sql.Connection;
  22. import java.util.*;
  23. import java.util.concurrent.atomic.AtomicBoolean;
  24. import java.util.function.Function;
  25. public class DatabaseQuery extends HashMap<String, DatabaseQuery> implements MagicModule {
  26. @UnableCall
  27. private DynamicDataSource dynamicDataSource;
  28. @UnableCall
  29. private DataSourceNode dataSourceNode;
  30. @UnableCall
  31. private PageProvider pageProvider;
  32. @UnableCall
  33. private ResultProvider resultProvider;
  34. @UnableCall
  35. private RowMapper<Map<String, Object>> rowMapper;
  36. @UnableCall
  37. private SqlCache sqlCache;
  38. @UnableCall
  39. private String cacheName;
  40. @UnableCall
  41. private long ttl;
  42. public DatabaseQuery() {
  43. }
  44. public DatabaseQuery(DynamicDataSource dynamicDataSource) {
  45. this.dynamicDataSource = dynamicDataSource;
  46. this.dataSourceNode = dynamicDataSource.getDataSource();
  47. }
  48. @UnableCall
  49. public void setPageProvider(PageProvider pageProvider) {
  50. this.pageProvider = pageProvider;
  51. }
  52. @UnableCall
  53. public void setResultProvider(ResultProvider resultProvider) {
  54. this.resultProvider = resultProvider;
  55. }
  56. @UnableCall
  57. public void setRowMapper(RowMapper<Map<String, Object>> rowMapper) {
  58. this.rowMapper = rowMapper;
  59. }
  60. @UnableCall
  61. public void setDynamicDataSource(DynamicDataSource dynamicDataSource) {
  62. this.dynamicDataSource = dynamicDataSource;
  63. }
  64. @UnableCall
  65. public void setSqlCache(SqlCache sqlCache) {
  66. this.sqlCache = sqlCache;
  67. }
  68. @UnableCall
  69. public void setDataSourceNode(DataSourceNode dataSourceNode) {
  70. this.dataSourceNode = dataSourceNode;
  71. }
  72. @UnableCall
  73. public void setCacheName(String cacheName) {
  74. this.cacheName = cacheName;
  75. }
  76. @UnableCall
  77. public void setTtl(long ttl) {
  78. this.ttl = ttl;
  79. }
  80. @UnableCall
  81. public DatabaseQuery cloneQuery() {
  82. DatabaseQuery query = new DatabaseQuery();
  83. query.setDynamicDataSource(this.dynamicDataSource);
  84. query.setDataSourceNode(this.dataSourceNode);
  85. query.setPageProvider(this.pageProvider);
  86. query.setRowMapper(this.rowMapper);
  87. query.setSqlCache(this.sqlCache);
  88. query.setTtl(this.ttl);
  89. query.setResultProvider(this.resultProvider);
  90. return query;
  91. }
  92. /**
  93. * 开启事务,在一个回调中进行操作
  94. * @param function 回调函数
  95. * @return
  96. */
  97. public Object transaction(Function<?, ?> function) {
  98. Transaction transaction = transaction(); //创建事务
  99. try {
  100. Object val = function.apply(null);
  101. transaction.commit(); //提交事务
  102. return val;
  103. } catch (Throwable throwable) {
  104. transaction.rollback(); //回滚事务
  105. throw throwable;
  106. }
  107. }
  108. /**
  109. * 开启事务,手动提交和回滚
  110. * @return
  111. */
  112. public Transaction transaction() {
  113. return new Transaction(this.dataSourceNode.getDataSourceTransactionManager());
  114. }
  115. /**
  116. * 添加至缓存
  117. * @param value 缓存名
  118. */
  119. @UnableCall
  120. private <T> T putCacheValue(T value, BoundSql boundSql) {
  121. if (this.cacheName != null) {
  122. this.sqlCache.put(this.cacheName, boundSql.getCacheKey(this.sqlCache), value, this.ttl);
  123. }
  124. return value;
  125. }
  126. /**
  127. * 使用缓存
  128. * @param cacheName 缓存名
  129. * @param ttl 过期时间
  130. * @return
  131. */
  132. public DatabaseQuery cache(String cacheName, long ttl) {
  133. if (cacheName == null) {
  134. return this;
  135. }
  136. DatabaseQuery query = cloneQuery();
  137. query.setCacheName(cacheName);
  138. query.setTtl(ttl);
  139. return query;
  140. }
  141. /**
  142. * 使用缓存(采用默认缓存时间)
  143. * @param cacheName 缓冲名
  144. * @return
  145. */
  146. public DatabaseQuery cache(String cacheName) {
  147. return cache(cacheName, 0);
  148. }
  149. /**
  150. * 数据源切换
  151. */
  152. @Override
  153. public DatabaseQuery get(Object key) {
  154. DatabaseQuery query = cloneQuery();
  155. if (key == null) {
  156. query.setDataSourceNode(dynamicDataSource.getDataSource());
  157. } else {
  158. query.setDataSourceNode(dynamicDataSource.getDataSource(key.toString()));
  159. }
  160. return query;
  161. }
  162. /**
  163. * 查询List
  164. */
  165. public List<Map<String, Object>> select(String sql) {
  166. BoundSql boundSql = new BoundSql(sql);
  167. return (List<Map<String, Object>>) boundSql.getCacheValue(this.sqlCache, this.cacheName)
  168. .orElseGet(() -> putCacheValue(dataSourceNode.getJdbcTemplate().query(boundSql.getSql(), this.rowMapper, boundSql.getParameters()), boundSql));
  169. }
  170. /**
  171. * 执行update
  172. */
  173. public int update(String sql) {
  174. BoundSql boundSql = new BoundSql(sql);
  175. int value = dataSourceNode.getJdbcTemplate().update(boundSql.getSql(), boundSql.getParameters());
  176. if (this.cacheName != null) {
  177. this.sqlCache.delete(this.cacheName);
  178. }
  179. return value;
  180. }
  181. /**
  182. * 分页查询
  183. */
  184. public Object page(String sql) {
  185. Page page = pageProvider.getPage(MagicScriptContext.get());
  186. return page(sql, page.getLimit(), page.getOffset());
  187. }
  188. /**
  189. * 分页查询(手动传入limit和offset参数)
  190. */
  191. public Object page(String sql, long limit, long offset) {
  192. BoundSql boundSql = new BoundSql(sql);
  193. Connection connection = null;
  194. Dialect dialect;
  195. try {
  196. connection = dataSourceNode.getJdbcTemplate().getDataSource().getConnection();
  197. dialect = DialectUtils.getDialectFromUrl(connection.getMetaData().getURL());
  198. } catch (Exception e) {
  199. throw new MagicAPIException("自动获取数据库方言失败", e);
  200. } finally {
  201. DataSourceUtils.releaseConnection(connection, dataSourceNode.getJdbcTemplate().getDataSource());
  202. }
  203. if (dialect == null) {
  204. throw new MagicAPIException("自动获取数据库方言失败");
  205. }
  206. int count = (int) boundSql.getCacheValue(this.sqlCache, this.cacheName)
  207. .orElseGet(() -> putCacheValue(dataSourceNode.getJdbcTemplate().queryForObject(dialect.getCountSql(boundSql.getSql()), Integer.class, boundSql.getParameters()), boundSql));
  208. List<Object> list = null;
  209. if (count > 0) {
  210. String pageSql = dialect.getPageSql(boundSql.getSql(), boundSql, offset, limit);
  211. list = (List<Object>) boundSql.removeCacheKey().getCacheValue(this.sqlCache, this.cacheName)
  212. .orElseGet(() -> putCacheValue(dataSourceNode.getJdbcTemplate().query(pageSql, this.rowMapper, boundSql.getParameters()), boundSql));
  213. }
  214. return resultProvider.buildPageResult(count, list);
  215. }
  216. /**
  217. * 查询int值
  218. */
  219. public Integer selectInt(String sql) {
  220. BoundSql boundSql = new BoundSql(sql);
  221. return (Integer) boundSql.getCacheValue(this.sqlCache, this.cacheName)
  222. .orElseGet(() -> putCacheValue(dataSourceNode.getJdbcTemplate().queryForObject(boundSql.getSql(), boundSql.getParameters(), Integer.class), boundSql));
  223. }
  224. /**
  225. * 查询Map
  226. */
  227. public Map<String, Object> selectOne(String sql) {
  228. BoundSql boundSql = new BoundSql(sql);
  229. return (Map<String, Object>) boundSql.getCacheValue(this.sqlCache, this.cacheName)
  230. .orElseGet(() -> {
  231. List<Map<String, Object>> list = dataSourceNode.getJdbcTemplate().query(boundSql.getSql(), this.rowMapper, boundSql.getParameters());
  232. return list != null && list.size() > 0 ? list.get(0) : null;
  233. });
  234. }
  235. /**
  236. * 查询单行单列的值
  237. */
  238. public Object selectValue(String sql) {
  239. BoundSql boundSql = new BoundSql(sql);
  240. return boundSql.getCacheValue(this.sqlCache, this.cacheName)
  241. .orElseGet(() -> putCacheValue(dataSourceNode.getJdbcTemplate().queryForObject(boundSql.getSql(), boundSql.getParameters(), Object.class), boundSql));
  242. }
  243. private static Tokenizer tokenizer = new Tokenizer();
  244. private static GenericTokenParser concatTokenParser = new GenericTokenParser("${", "}", false);
  245. private static GenericTokenParser replaceTokenParser = new GenericTokenParser("#{", "}", true);
  246. private static GenericTokenParser ifTokenParser = new GenericTokenParser("?{", "}", true);
  247. private static GenericTokenParser ifParamTokenParser = new GenericTokenParser("?{", ",", true);
  248. @UnableCall
  249. @Override
  250. public String getModuleName() {
  251. return "db";
  252. }
  253. public static class BoundSql {
  254. private String sql;
  255. private List<Object> parameters = new ArrayList<>();
  256. private String cacheKey;
  257. BoundSql(String sql) {
  258. MagicScriptContext context = MagicScriptContext.get();
  259. // 处理?{}参数
  260. this.sql = ifTokenParser.parse(sql.trim(), text -> {
  261. AtomicBoolean ifTrue = new AtomicBoolean(false);
  262. String val = ifParamTokenParser.parse("?{" + text, param -> {
  263. Object result = Parser.parseExpression(new TokenStream(tokenizer.tokenize(param))).evaluate(context);
  264. //如果是String则判断是否是空,否则和判断值是否为true
  265. if (result != null) {
  266. if (result instanceof String) {
  267. ifTrue.set(!result.toString().isEmpty());
  268. } else {
  269. ifTrue.set(!Objects.equals(false, result));
  270. }
  271. }
  272. return null;
  273. });
  274. if (ifTrue.get()) {
  275. return val;
  276. }
  277. return "";
  278. });
  279. // 处理${}参数
  280. this.sql = concatTokenParser.parse(this.sql, text -> String.valueOf(Parser.parseExpression(new TokenStream(tokenizer.tokenize(text))).evaluate(context)));
  281. // 处理#{}参数
  282. this.sql = replaceTokenParser.parse(this.sql, text -> {
  283. Object value = Parser.parseExpression(new TokenStream(tokenizer.tokenize(text))).evaluate(context);
  284. try {
  285. //对集合自动展开
  286. List<Object> objects = StreamExtension.arrayLikeToList(value);
  287. StringBuilder sb = new StringBuilder();
  288. for (int i = 0, size = objects.size(); i < size; i++) {
  289. sb.append("?");
  290. if (i + 1 < size) {
  291. sb.append(",");
  292. }
  293. parameters.add(objects.get(i));
  294. }
  295. return sb.toString();
  296. } catch (Exception e) {
  297. parameters.add(value);
  298. return "?";
  299. }
  300. });
  301. }
  302. /**
  303. * 添加SQL参数
  304. */
  305. public void addParameter(Object value) {
  306. parameters.add(value);
  307. }
  308. /**
  309. * 获取要执行的SQL
  310. */
  311. public String getSql() {
  312. return sql;
  313. }
  314. /**
  315. * 获取要执行的参数
  316. */
  317. public Object[] getParameters() {
  318. return parameters.toArray();
  319. }
  320. /**
  321. * 清空缓存key
  322. */
  323. public BoundSql removeCacheKey() {
  324. this.cacheKey = null;
  325. return this;
  326. }
  327. /**
  328. * 获取缓存key
  329. */
  330. public String getCacheKey(SqlCache sqlCache) {
  331. if (cacheKey == null) {
  332. cacheKey = sqlCache.buildSqlCacheKey(this);
  333. }
  334. return cacheKey;
  335. }
  336. /**
  337. * 获取缓存值
  338. */
  339. public <T> Optional<T> getCacheValue(SqlCache sqlCache, String cacheName) {
  340. return Optional.ofNullable(cacheName == null ? null : sqlCache.get(cacheName, getCacheKey(sqlCache)));
  341. }
  342. }
  343. }