From da30aeabac71e08d866d504b908a032763794e3e Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期四, 05 十月 2023 13:06:41 +0800
Subject: [PATCH] 添加批量操作接口
---
src/main/java/com/smartearth/poiexcel/service/EntService.java | 2
src/main/java/com/smartearth/poiexcel/mapper/EntMapper.java | 4
src/main/java/com/smartearth/poiexcel/extend/CustomizedSqlInjector.java | 26 ++++
src/main/java/com/smartearth/poiexcel/mapper/BasicMapper.java | 43 +++++++
src/main/java/com/smartearth/poiexcel/entity/EntEntity.java | 22 +--
src/main/java/com/smartearth/poiexcel/config/MybatisPlusConfig.java | 52 ++++++++
src/main/java/com/smartearth/poiexcel/extend/UpdateBatchMethod.java | 86 ++++++++++++++
src/main/java/com/smartearth/poiexcel/extend/InsertBatchMethod.java | 76 ++++++++++++
8 files changed, 296 insertions(+), 15 deletions(-)
diff --git a/src/main/java/com/smartearth/poiexcel/config/MybatisPlusConfig.java b/src/main/java/com/smartearth/poiexcel/config/MybatisPlusConfig.java
new file mode 100644
index 0000000..dbf9a94
--- /dev/null
+++ b/src/main/java/com/smartearth/poiexcel/config/MybatisPlusConfig.java
@@ -0,0 +1,52 @@
+package com.smartearth.poiexcel.config;
+
+import com.baomidou.mybatisplus.annotation.DbType;
+import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
+import com.baomidou.mybatisplus.core.MybatisConfiguration;
+import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
+import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
+import com.smartearth.poiexcel.extend.CustomizedSqlInjector;
+import org.apache.ibatis.type.JdbcType;
+import org.mybatis.spring.annotation.MapperScan;
+import org.springframework.context.annotation.Bean;
+import org.springframework.context.annotation.Configuration;
+import org.springframework.transaction.annotation.EnableTransactionManagement;
+
+/**
+ * Mybatis-Plus鍒嗛〉閰嶇疆
+ * @author WWW
+ */
+@EnableTransactionManagement
+@Configuration
+@MapperScan("com.smartearth.poiexcel.mapper")
+public class MybatisPlusConfig {
+ /**
+ * 鏂扮殑鍒嗛〉鎻掍欢,涓�缂撳拰浜岀紦閬靛惊mybatis鐨勮鍒�,闇�瑕佽缃�
+ * MybatisConfiguration#useDeprecatedExecutor = false
+ * 閬垮厤缂撳瓨鍑虹幇闂(璇ュ睘鎬т細鍦ㄦ棫鎻掍欢绉婚櫎鍚庝竴鍚岀Щ闄�)
+ */
+ @Bean
+ public MybatisPlusInterceptor mybatisPlusInterceptor() {
+ MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
+ interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
+ return interceptor;
+ }
+
+ @Bean
+ public ConfigurationCustomizer configurationCustomizer() {
+ return new ConfigurationCustomizer() {
+ @Override
+ public void customize(MybatisConfiguration configuration) {
+ configuration.setCacheEnabled(true);
+ configuration.setMapUnderscoreToCamelCase(true);
+ configuration.setCallSettersOnNulls(true);
+ configuration.setJdbcTypeForNull(JdbcType.NULL);
+ }
+ };
+ }
+
+ @Bean
+ public CustomizedSqlInjector customizedSqlInjector() {
+ return new CustomizedSqlInjector();
+ }
+}
diff --git a/src/main/java/com/smartearth/poiexcel/entity/EntEntity.java b/src/main/java/com/smartearth/poiexcel/entity/EntEntity.java
index 2e2a977..dc39763 100644
--- a/src/main/java/com/smartearth/poiexcel/entity/EntEntity.java
+++ b/src/main/java/com/smartearth/poiexcel/entity/EntEntity.java
@@ -1,5 +1,10 @@
package com.smartearth.poiexcel.entity;
+import com.baomidou.mybatisplus.annotation.TableName;
+import lombok.AllArgsConstructor;
+import lombok.Data;
+import lombok.EqualsAndHashCode;
+
import java.io.Serializable;
import java.util.Date;
@@ -8,12 +13,14 @@
* @author WWW
* @date 2023-10-05
*/
+@Data
+@AllArgsConstructor
+@TableName("smart_earth.yz_qiyexinxi")
+@EqualsAndHashCode(callSuper = false)
public class EntEntity implements Serializable {
private static final long serialVersionUID = -8624235184539814990L;
private Long id;
-
- private String enterprise_id;
private String entName;
@@ -48,8 +55,7 @@
public EntEntity() {
}
- public EntEntity(String enterprise_id, String entName, String fingerId, Integer regCapital, Integer regCapitalCNY, String legalPerson, String address, Date buildDate, String entType, String industryCategory, String businessScope, String qylabel, String creator, Double x, Double y, String enterprise_area) {
- this.enterprise_id = enterprise_id;
+ public EntEntity(String entName, String fingerId, Integer regCapital, Integer regCapitalCNY, String legalPerson, String address, Date buildDate, String entType, String industryCategory, String businessScope, String qylabel, String creator, Double x, Double y, String enterprise_area) {
this.entName = entName;
this.fingerId = fingerId;
this.regCapital = regCapital;
@@ -73,14 +79,6 @@
public void setId(Long id) {
this.id = id;
- }
-
- public String getEnterprise_id() {
- return enterprise_id;
- }
-
- public void setEnterprise_id(String enterprise_id) {
- this.enterprise_id = enterprise_id;
}
public String getEntName() {
diff --git a/src/main/java/com/smartearth/poiexcel/extend/CustomizedSqlInjector.java b/src/main/java/com/smartearth/poiexcel/extend/CustomizedSqlInjector.java
new file mode 100644
index 0000000..7c30413
--- /dev/null
+++ b/src/main/java/com/smartearth/poiexcel/extend/CustomizedSqlInjector.java
@@ -0,0 +1,26 @@
+package com.smartearth.poiexcel.extend;
+
+import com.baomidou.mybatisplus.core.injector.AbstractMethod;
+import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
+import com.baomidou.mybatisplus.core.metadata.TableInfo;
+
+import java.util.List;
+
+/**
+ * 鑷畾涔夋柟娉昐QL娉ㄥ叆鍣�
+ * @author WWW
+ */
+public class CustomizedSqlInjector extends DefaultSqlInjector {
+ /**
+ * 濡傛灉鍙渶澧炲姞鏂规硶锛屼繚鐣檓ybatis plus鑷甫鏂规硶锛�
+ * 鍙互鍏堣幏鍙杝uper.getMethodList()锛屽啀娣诲姞add
+ */
+ @Override
+ public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
+ List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
+ methodList.add(new InsertBatchMethod());
+ methodList.add(new UpdateBatchMethod());
+
+ return methodList;
+ }
+}
diff --git a/src/main/java/com/smartearth/poiexcel/extend/InsertBatchMethod.java b/src/main/java/com/smartearth/poiexcel/extend/InsertBatchMethod.java
new file mode 100644
index 0000000..21f0197
--- /dev/null
+++ b/src/main/java/com/smartearth/poiexcel/extend/InsertBatchMethod.java
@@ -0,0 +1,76 @@
+package com.smartearth.poiexcel.extend;
+
+import com.baomidou.mybatisplus.core.injector.AbstractMethod;
+import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
+import com.baomidou.mybatisplus.core.metadata.TableInfo;
+import com.smartearth.poiexcel.entity.StaticData;
+import org.apache.ibatis.executor.keygen.NoKeyGenerator;
+import org.apache.ibatis.mapping.MappedStatement;
+import org.apache.ibatis.mapping.SqlSource;
+
+/**
+ * 鎵归噺鎻掑叆鏂规硶
+ * @author WWW
+ */
+@SuppressWarnings("ALL")
+public class InsertBatchMethod extends AbstractMethod {
+ /**
+ * insert into user(id, name, age) values (1, "a", 17), (2, "b", 18);
+ * <script>
+ * insert into user(id, name, age) values
+ * <foreach collection="list" item="item" index="index" open="(" separator="),(" close=")">
+ * #{item.id}, #{item.name}, #{item.age}
+ * </foreach>
+ * </script>
+ */
+ @Override
+ public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
+ final String sql = "<script>insert into %s %s values %s</script>";
+ final String fieldSql = prepareFieldSql(tableInfo);
+ final String valueSql = prepareValuesSql(tableInfo);
+ final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
+
+ SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
+
+ // 绗笁涓弬鏁板繀椤诲拰RootMapper鐨勮嚜瀹氫箟鏂规硶鍚嶄竴鑷�
+ return this.addInsertMappedStatement(mapperClass, modelClass, "insertBatch", sqlSource, new NoKeyGenerator(), null, null);
+ }
+
+ private String prepareFieldSql(TableInfo tableInfo) {
+ StringBuilder fieldSql = new StringBuilder();
+ // fieldSql.append(tableInfo.getKeyColumn()).append(",")
+ // tableInfo.getFieldList().forEach(x -> fieldSql.append(x.getColumn()).append(","))
+ for (TableFieldInfo f : tableInfo.getFieldList()) {
+ if (StaticData.INSERT_EXCLUDE_FIELDS.contains(f.getProperty())) {
+ continue;
+ }
+
+ fieldSql.append(f.getColumn()).append(",");
+ }
+
+ fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
+ fieldSql.insert(0, "(");
+ fieldSql.append(")");
+
+ return fieldSql.toString();
+ }
+
+ private String prepareValuesSql(TableInfo tableInfo) {
+ final StringBuilder valueSql = new StringBuilder();
+ valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
+ // valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},")
+ // tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"))
+ for (TableFieldInfo f : tableInfo.getFieldList()) {
+ if (StaticData.INSERT_EXCLUDE_FIELDS.contains(f.getProperty())) {
+ continue;
+ }
+
+ valueSql.append("geom".equals(f.getProperty()) ? "${item." : "#{item.").append(f.getProperty()).append("},");
+ }
+
+ valueSql.delete(valueSql.length() - 1, valueSql.length());
+ valueSql.append("</foreach>");
+
+ return valueSql.toString();
+ }
+}
diff --git a/src/main/java/com/smartearth/poiexcel/extend/UpdateBatchMethod.java b/src/main/java/com/smartearth/poiexcel/extend/UpdateBatchMethod.java
new file mode 100644
index 0000000..edccf71
--- /dev/null
+++ b/src/main/java/com/smartearth/poiexcel/extend/UpdateBatchMethod.java
@@ -0,0 +1,86 @@
+package com.smartearth.poiexcel.extend;
+
+import com.baomidou.mybatisplus.core.injector.AbstractMethod;
+import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
+import com.baomidou.mybatisplus.core.metadata.TableInfo;
+import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
+import com.smartearth.poiexcel.entity.StaticData;
+import org.apache.ibatis.mapping.MappedStatement;
+import org.apache.ibatis.mapping.SqlSource;
+
+/**
+ * 鎵归噺鏇存柊鏂规硶
+ * @author WWW
+ */
+@SuppressWarnings("ALL")
+public class UpdateBatchMethod extends AbstractMethod {
+ /**
+ * update user set name = "a", age = 17 where id = 1;
+ * update user set name = "b", age = 18 where id = 2;
+ * <script>
+ * <foreach collection="list" item="item" separator=";">
+ * update user
+ * <set>
+ * <if test="item.name != null and item.name != ''">
+ * name = #{item.name,jdbcType=VARCHAR},
+ * </if>
+ * <if test="item.age != null">
+ * age = #{item.age,jdbcType=INTEGER},
+ * </if>
+ * </set>
+ * where id = #{item.id,jdbcType=INTEGER}
+ * </foreach>
+ * </script>
+ */
+ @Override
+ public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
+ String sql = "<script>\n<foreach collection=\"list\" item=\"item\" separator=\";\">\nupdate %s %s where %s=#{%s} %s\n</foreach>\n</script>";
+ String additional = tableInfo.isWithVersion() ? tableInfo.getVersionFieldInfo().getVersionOli("item", "item.") : "" + tableInfo.getLogicDeleteSql(true, true);
+ //String setSql = sqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");
+ String setSql = getSqlSet(tableInfo.isWithLogicDelete(), false, tableInfo, false, "item", "item.");
+ String sqlResult = String.format(sql, tableInfo.getTableName(), setSql, tableInfo.getKeyColumn(), "item." + tableInfo.getKeyProperty(), additional);
+
+ // update %s %s where %s=#{%s} %s
+ // update tab set a=#{a} where gid=1
+
+ SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
+
+ // 绗笁涓弬鏁板繀椤诲拰RootMapper鐨勮嚜瀹氫箟鏂规硶鍚嶄竴鑷�
+ return this.addUpdateMappedStatement(mapperClass, modelClass, "updateBatch", sqlSource);
+ }
+
+ private String getSqlSet(boolean logic, boolean ew, TableInfo table, boolean judgeAliasNull, final String alias, final String prefix) {
+ // String sqlScript = table.getAllSqlSet(logic, prefix);
+ String sqlScript = getSqlSet(table);
+ if (judgeAliasNull) {
+ sqlScript = SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", alias), true);
+ }
+ if (ew) {
+ sqlScript = sqlScript + "\n";
+ sqlScript = sqlScript + this.convertIfEwParam("ew.sqlSet", false);
+ }
+
+ sqlScript = SqlScriptUtils.convertSet(sqlScript);
+
+ return sqlScript;
+ }
+
+ private String getSqlSet(TableInfo tableInfo) {
+ StringBuilder sb = new StringBuilder();
+ for (TableFieldInfo f : tableInfo.getFieldList()) {
+ if (StaticData.UPDATE_EXCLUDE_FIELDS.contains(f.getProperty())) {
+ continue;
+ }
+
+ if ("geom".equals(f.getProperty())) {
+ sb.append("<if test=\"item['geom'] != null\">geom=${item.geom},</if>\n");
+ continue;
+ }
+
+ sb.append(String.format("<if test=\"item['%s'] != null\">%s=#{item.%s},</if>\n", f.getProperty(), f.getColumn(), f.getProperty()));
+ }
+ sb.deleteCharAt(sb.length() - 1);
+
+ return sb.toString();
+ }
+}
diff --git a/src/main/java/com/smartearth/poiexcel/mapper/BasicMapper.java b/src/main/java/com/smartearth/poiexcel/mapper/BasicMapper.java
new file mode 100644
index 0000000..f6ff41f
--- /dev/null
+++ b/src/main/java/com/smartearth/poiexcel/mapper/BasicMapper.java
@@ -0,0 +1,43 @@
+package com.smartearth.poiexcel.mapper;
+
+import com.baomidou.mybatisplus.core.mapper.BaseMapper;
+import org.apache.ibatis.annotations.Param;
+import org.apache.ibatis.annotations.Select;
+
+import java.util.List;
+
+/**
+ * 鍩虹Mapper
+ * @author WWW
+ * @param <T> 娉涘瀷
+ */
+public interface BasicMapper<T> extends BaseMapper<T> {
+ /**
+ * 妯＄硦鎼滅储瀛楁
+ *
+ * @param tab 琛ㄥ悕
+ * @param field 瀛楁
+ * @param value 鍊�
+ * @return 缁撴灉鍒楄〃
+ */
+ @Select("select ${field} from ${tab} where upper(${field}) like #{value} order by ${field} limit 10")
+ public List<String> selectFieldFuzzy(@Param("tab") String tab, @Param("field") String field, @Param("value") String value);
+
+ /**
+ * 鑷畾涔夋壒閲忔彃鍏�
+ * 濡傛灉瑕佽嚜鍔ㄥ～鍏咃紝@Param(xx) xx鍙傛暟鍚嶅繀椤绘槸 list/collection/array 3涓殑鍏朵腑涔嬩竴
+ *
+ * @param list
+ * @return
+ */
+ public int insertBatch(@Param("list") List<T> list);
+
+ /**
+ * 鑷畾涔夋壒閲忔洿鏂帮紝鏉′欢涓轰富閿�
+ * 濡傛灉瑕佽嚜鍔ㄥ～鍏咃紝@Param(xx) xx鍙傛暟鍚嶅繀椤绘槸 list/collection/array 3涓殑鍏朵腑涔嬩竴
+ *
+ * @param list
+ * @return
+ */
+ public int updateBatch(@Param("list") List<T> list);
+}
diff --git a/src/main/java/com/smartearth/poiexcel/mapper/EntMapper.java b/src/main/java/com/smartearth/poiexcel/mapper/EntMapper.java
index 98920ca..1c162ef 100644
--- a/src/main/java/com/smartearth/poiexcel/mapper/EntMapper.java
+++ b/src/main/java/com/smartearth/poiexcel/mapper/EntMapper.java
@@ -1,5 +1,6 @@
package com.smartearth.poiexcel.mapper;
+import com.smartearth.poiexcel.entity.EntEntity;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@@ -10,6 +11,5 @@
*/
@Mapper
@Repository
-public interface EntMapper {
-
+public interface EntMapper extends BasicMapper<EntEntity> {
}
diff --git a/src/main/java/com/smartearth/poiexcel/service/EntService.java b/src/main/java/com/smartearth/poiexcel/service/EntService.java
index 218c467..1cbc6e0 100644
--- a/src/main/java/com/smartearth/poiexcel/service/EntService.java
+++ b/src/main/java/com/smartearth/poiexcel/service/EntService.java
@@ -20,7 +20,7 @@
* @date 2023-10-05
*/
@Service
-public class EntService implements EntMapper {
+public class EntService {
@Resource
EntMapper entMapper;
--
Gitblit v1.9.3