SQLModule.java 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372
  1. package org.ssssssss.magicapi.modules;
  2. import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
  3. import org.springframework.jdbc.core.RowMapper;
  4. import org.springframework.jdbc.support.GeneratedKeyHolder;
  5. import org.springframework.jdbc.support.KeyHolder;
  6. import org.ssssssss.magicapi.adapter.ColumnMapperAdapter;
  7. import org.ssssssss.magicapi.adapter.DialectAdapter;
  8. import org.ssssssss.magicapi.cache.SqlCache;
  9. import org.ssssssss.magicapi.config.MagicModule;
  10. import org.ssssssss.magicapi.controller.MagicDynamicDataSource;
  11. import org.ssssssss.magicapi.controller.MagicDynamicDataSource.DataSourceNode;
  12. import org.ssssssss.magicapi.dialect.Dialect;
  13. import org.ssssssss.magicapi.interceptor.SQLInterceptor;
  14. import org.ssssssss.magicapi.model.Page;
  15. import org.ssssssss.magicapi.provider.PageProvider;
  16. import org.ssssssss.magicapi.provider.ResultProvider;
  17. import org.ssssssss.script.MagicScriptContext;
  18. import org.ssssssss.script.annotation.Comment;
  19. import org.ssssssss.script.annotation.UnableCall;
  20. import java.sql.PreparedStatement;
  21. import java.sql.Statement;
  22. import java.util.HashMap;
  23. import java.util.List;
  24. import java.util.Map;
  25. import java.util.function.Function;
  26. /**
  27. * 数据库查询模块
  28. */
  29. public class SQLModule extends HashMap<String, SQLModule> implements MagicModule {
  30. @UnableCall
  31. private MagicDynamicDataSource dynamicDataSource;
  32. @UnableCall
  33. private DataSourceNode dataSourceNode;
  34. @UnableCall
  35. private PageProvider pageProvider;
  36. @UnableCall
  37. private ResultProvider resultProvider;
  38. @UnableCall
  39. private ColumnMapperAdapter columnMapperAdapter;
  40. @UnableCall
  41. private DialectAdapter dialectAdapter;
  42. @UnableCall
  43. private RowMapper<Map<String, Object>> columnMapRowMapper;
  44. @UnableCall
  45. private Function<String, String> rowMapColumnMapper;
  46. @UnableCall
  47. private SqlCache sqlCache;
  48. @UnableCall
  49. private String cacheName;
  50. @UnableCall
  51. private List<SQLInterceptor> sqlInterceptors;
  52. @UnableCall
  53. private long ttl;
  54. public SQLModule() {
  55. }
  56. public SQLModule(MagicDynamicDataSource dynamicDataSource) {
  57. this.dynamicDataSource = dynamicDataSource;
  58. this.dataSourceNode = dynamicDataSource.getDataSource();
  59. }
  60. @UnableCall
  61. public void setPageProvider(PageProvider pageProvider) {
  62. this.pageProvider = pageProvider;
  63. }
  64. @UnableCall
  65. public void setResultProvider(ResultProvider resultProvider) {
  66. this.resultProvider = resultProvider;
  67. }
  68. @UnableCall
  69. public void setColumnMapperProvider(ColumnMapperAdapter columnMapperAdapter) {
  70. this.columnMapperAdapter = columnMapperAdapter;
  71. }
  72. @UnableCall
  73. public void setDialectAdapter(DialectAdapter dialectAdapter) {
  74. this.dialectAdapter = dialectAdapter;
  75. }
  76. @UnableCall
  77. public void setColumnMapRowMapper(RowMapper<Map<String, Object>> columnMapRowMapper) {
  78. this.columnMapRowMapper = columnMapRowMapper;
  79. }
  80. @UnableCall
  81. public void setRowMapColumnMapper(Function<String, String> rowMapColumnMapper) {
  82. this.rowMapColumnMapper = rowMapColumnMapper;
  83. }
  84. private void setDynamicDataSource(MagicDynamicDataSource dynamicDataSource) {
  85. this.dynamicDataSource = dynamicDataSource;
  86. }
  87. @UnableCall
  88. public void setSqlInterceptors(List<SQLInterceptor> sqlInterceptors) {
  89. this.sqlInterceptors = sqlInterceptors;
  90. }
  91. @UnableCall
  92. public void setSqlCache(SqlCache sqlCache) {
  93. this.sqlCache = sqlCache;
  94. }
  95. private void setDataSourceNode(DataSourceNode dataSourceNode) {
  96. this.dataSourceNode = dataSourceNode;
  97. }
  98. private void setCacheName(String cacheName) {
  99. this.cacheName = cacheName;
  100. }
  101. private void setTtl(long ttl) {
  102. this.ttl = ttl;
  103. }
  104. @UnableCall
  105. private SQLModule cloneSQLModule() {
  106. SQLModule sqlModule = new SQLModule();
  107. sqlModule.setDynamicDataSource(this.dynamicDataSource);
  108. sqlModule.setDataSourceNode(this.dataSourceNode);
  109. sqlModule.setPageProvider(this.pageProvider);
  110. sqlModule.setColumnMapperProvider(this.columnMapperAdapter);
  111. sqlModule.setColumnMapRowMapper(this.columnMapRowMapper);
  112. sqlModule.setRowMapColumnMapper(this.rowMapColumnMapper);
  113. sqlModule.setSqlCache(this.sqlCache);
  114. sqlModule.setTtl(this.ttl);
  115. sqlModule.setResultProvider(this.resultProvider);
  116. sqlModule.setDialectAdapter(this.dialectAdapter);
  117. sqlModule.setSqlInterceptors(this.sqlInterceptors);
  118. return sqlModule;
  119. }
  120. /**
  121. * 开启事务,在一个回调中进行操作
  122. *
  123. * @param function 回调函数
  124. * @return
  125. */
  126. @Comment("开启事务,并在回调中处理")
  127. public Object transaction(@Comment("回调函数,如:()=>{....}") Function<?, ?> function) {
  128. Transaction transaction = transaction(); //创建事务
  129. try {
  130. Object val = function.apply(null);
  131. transaction.commit(); //提交事务
  132. return val;
  133. } catch (Throwable throwable) {
  134. transaction.rollback(); //回滚事务
  135. throw throwable;
  136. }
  137. }
  138. /**
  139. * 开启事务,手动提交和回滚
  140. *
  141. * @return
  142. */
  143. @Comment("开启事务,返回事务对象")
  144. public Transaction transaction() {
  145. return new Transaction(this.dataSourceNode.getDataSourceTransactionManager());
  146. }
  147. /**
  148. * 使用缓存
  149. *
  150. * @param cacheName 缓存名
  151. * @param ttl 过期时间
  152. * @return
  153. */
  154. @Comment("使用缓存")
  155. public SQLModule cache(@Comment("缓存名") String cacheName, @Comment("过期时间") long ttl) {
  156. if (cacheName == null) {
  157. return this;
  158. }
  159. SQLModule sqlModule = cloneSQLModule();
  160. sqlModule.setCacheName(cacheName);
  161. sqlModule.setTtl(ttl);
  162. return sqlModule;
  163. }
  164. /**
  165. * 使用缓存(采用默认缓存时间)
  166. *
  167. * @param cacheName 缓冲名
  168. * @return
  169. */
  170. @Comment("使用缓存,过期时间采用默认配置")
  171. public SQLModule cache(@Comment("缓存名") String cacheName) {
  172. return cache(cacheName, 0);
  173. }
  174. @Comment("采用驼峰列名")
  175. public SQLModule camel() {
  176. return columnCase("camel");
  177. }
  178. @Comment("采用帕斯卡列名")
  179. public SQLModule pascal() {
  180. return columnCase("pascal");
  181. }
  182. @Comment("采用全小写列名")
  183. public SQLModule lower() {
  184. return columnCase("lower");
  185. }
  186. @Comment("采用全大写列名")
  187. public SQLModule upper() {
  188. return columnCase("upper");
  189. }
  190. @Comment("列名保持原样")
  191. public SQLModule normal() {
  192. return columnCase("default");
  193. }
  194. @Comment("指定列名转换")
  195. public SQLModule columnCase(String name) {
  196. SQLModule sqlModule = cloneSQLModule();
  197. sqlModule.setColumnMapRowMapper(this.columnMapperAdapter.getColumnMapRowMapper(name));
  198. sqlModule.setRowMapColumnMapper(this.columnMapperAdapter.getRowMapColumnMapper(name));
  199. return sqlModule;
  200. }
  201. /**
  202. * 数据源切换
  203. */
  204. @Override
  205. public SQLModule get(Object key) {
  206. SQLModule sqlModule = cloneSQLModule();
  207. if (key == null) {
  208. sqlModule.setDataSourceNode(dynamicDataSource.getDataSource());
  209. } else {
  210. sqlModule.setDataSourceNode(dynamicDataSource.getDataSource(key.toString()));
  211. }
  212. return sqlModule;
  213. }
  214. /**
  215. * 查询List
  216. */
  217. @Comment("查询SQL,返回List类型结果")
  218. public List<Map<String, Object>> select(@Comment("`SQL`语句") String sql) {
  219. BoundSql boundSql = new BoundSql(sql, this.sqlCache, this.cacheName, this.ttl);
  220. return boundSql.getCacheValue(this.sqlInterceptors, () -> dataSourceNode.getJdbcTemplate().query(boundSql.getSql(), this.columnMapRowMapper, boundSql.getParameters()));
  221. }
  222. /**
  223. * 执行update
  224. */
  225. @Comment("执行update操作,返回受影响行数")
  226. public int update(@Comment("`SQL`语句") String sql) {
  227. BoundSql boundSql = new BoundSql(sql);
  228. sqlInterceptors.forEach(sqlInterceptor -> sqlInterceptor.preHandle(boundSql));
  229. int value = dataSourceNode.getJdbcTemplate().update(boundSql.getSql(), boundSql.getParameters());
  230. if (this.cacheName != null) {
  231. this.sqlCache.delete(this.cacheName);
  232. }
  233. return value;
  234. }
  235. /**
  236. * 插入并返回主键
  237. */
  238. @Comment("执行insert操作,返回插入条数")
  239. public long insert(@Comment("`SQL`语句") String sql) {
  240. BoundSql boundSql = new BoundSql(sql);
  241. sqlInterceptors.forEach(sqlInterceptor -> sqlInterceptor.preHandle(boundSql));
  242. KeyHolder keyHolder = new GeneratedKeyHolder();
  243. dataSourceNode.getJdbcTemplate().update(con -> {
  244. PreparedStatement ps = con.prepareStatement(boundSql.getSql(), Statement.RETURN_GENERATED_KEYS);
  245. new ArgumentPreparedStatementSetter(boundSql.getParameters()).setValues(ps);
  246. return ps;
  247. }, keyHolder);
  248. if (this.cacheName != null) {
  249. this.sqlCache.delete(this.cacheName);
  250. }
  251. Number key = keyHolder.getKey();
  252. if (key == null) {
  253. return -1;
  254. }
  255. return key.longValue();
  256. }
  257. /**
  258. * 分页查询
  259. */
  260. @Comment("执行分页查询,分页条件自动获取")
  261. public Object page(@Comment("`SQL`语句") String sql) {
  262. Page page = pageProvider.getPage(MagicScriptContext.get());
  263. return page(sql, page.getLimit(), page.getOffset());
  264. }
  265. /**
  266. * 分页查询(手动传入limit和offset参数)
  267. */
  268. @Comment("执行分页查询,分页条件手动传入")
  269. public Object page(@Comment("`SQL`语句") String sql, @Comment("限制条数") long limit, @Comment("跳过条数") long offset) {
  270. BoundSql boundSql = new BoundSql(sql, this.sqlCache, this.cacheName, this.ttl);
  271. Dialect dialect = dataSourceNode.getDialect(dialectAdapter);
  272. BoundSql countBoundSql = boundSql.copy(dialect.getCountSql(boundSql.getSql()));
  273. int count = countBoundSql.getCacheValue(this.sqlInterceptors, () -> dataSourceNode.getJdbcTemplate().queryForObject(countBoundSql.getSql(), Integer.class, countBoundSql.getParameters()));
  274. List<Map<String, Object>> list = null;
  275. if (count > 0) {
  276. String pageSql = dialect.getPageSql(boundSql.getSql(), boundSql, offset, limit);
  277. BoundSql pageBoundSql = boundSql.copy(dialect.getCountSql(boundSql.getSql()));
  278. list = pageBoundSql.getCacheValue(this.sqlInterceptors, () -> dataSourceNode.getJdbcTemplate().query(pageBoundSql.getSql(), this.columnMapRowMapper, pageBoundSql.getParameters()));
  279. }
  280. return resultProvider.buildPageResult(count, list);
  281. }
  282. /**
  283. * 查询int值
  284. */
  285. @Comment("查询int值,适合单行单列int的结果")
  286. public Integer selectInt(@Comment("`SQL`语句") String sql) {
  287. BoundSql boundSql = new BoundSql(sql, this.sqlCache, this.cacheName, this.ttl);
  288. return boundSql.getCacheValue(this.sqlInterceptors, () -> dataSourceNode.getJdbcTemplate().queryForObject(boundSql.getSql(), boundSql.getParameters(), Integer.class));
  289. }
  290. /**
  291. * 查询Map
  292. */
  293. @Comment("查询单条结果,查不到返回null")
  294. public Map<String, Object> selectOne(@Comment("`SQL`语句") String sql) {
  295. BoundSql boundSql = new BoundSql(sql, this.sqlCache, this.cacheName, this.ttl);
  296. return boundSql.getCacheValue(this.sqlInterceptors, () -> {
  297. List<Map<String, Object>> list = dataSourceNode.getJdbcTemplate().query(boundSql.getSql(), this.columnMapRowMapper, boundSql.getParameters());
  298. return list != null && list.size() > 0 ? list.get(0) : null;
  299. });
  300. }
  301. /**
  302. * 查询单行单列的值
  303. */
  304. @Comment("查询单行单列的值")
  305. public Object selectValue(@Comment("`SQL`语句") String sql) {
  306. BoundSql boundSql = new BoundSql(sql, this.sqlCache, this.cacheName, this.ttl);
  307. return boundSql.getCacheValue(this.sqlInterceptors, () -> dataSourceNode.getJdbcTemplate().queryForObject(boundSql.getSql(), boundSql.getParameters(), Object.class));
  308. }
  309. @Comment("指定table,进行一系列操作")
  310. public NamedTable table(String tableName) {
  311. return new NamedTable(tableName, this.dataSourceNode);
  312. }
  313. @UnableCall
  314. @Override
  315. public String getModuleName() {
  316. return "db";
  317. }
  318. }