跨数据源拷贝表

需求

做一个数据表自动介入功能。有一个系统页面可以进行数据源配置。选择源数据的某张表，在选择目标源数据库，就能实现从一个数据源拷贝表到另一个数据源。

思路

采用JdbcTemplate执行sql的方式。为了实现简单，目的数据源还是需要手动建表。

实现

通过数据源配置信息获取到数据源

package com.leadingsoft.resource_catalog.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

public class DaoPlatform {

  public static NamedParameterJdbcTemplate jdbcDatasource(String driverClassName, String pass, >String url,
          String username) {
      DataSource dataSource = >DataSourceBuilder.create().driverClassName(driverClassName).url(url).username(username)
              .password(pass).build();
      return new NamedParameterJdbcTemplate(dataSource);
  }

  public static void main(String[] args) {
      NamedParameterJdbcTemplate jdbcDatasource = >DaoPlatform.jdbcDatasource("com.microsoft.sqlserver.jdbc.SQLServerDriver", "",
              "jdbc:sqlserver://;databaseName=", "");
      String sql2 = "select * from AD_CODE_";
      Map<String, Object> paramMap2 = new HashMap<>();
      List<Map<String, Object>> queryForList = jdbcDatasource.queryForList(sql2, paramMap2);
      System.out.println(queryForList);
  }
}

数据源信息实体类-用于存储数据源配置信息

package com.leadingsoft.resource_catalog.model;

import javax.persistence.Entity;

import com.leadingsoft.common.model.AbstractModel;

import lombok.Getter;
import lombok.Setter;

@Entity
@Getter
@Setter
public class DataSourceParameter extends AbstractModel<Long> {

  private static final long serialVersionUID = 1L;

  private String driverClassName;
  private String pass;
  private String url;
  private String username;
}

测试类

package com.leadingsoft.resource_catalog.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.leadingsoft.resource_catalog.dao.DaoPlatform;
import com.leadingsoft.resource_catalog.model.DataSourceParameter;
import com.leadingsoft.resource_catalog.repository.DataSourceParameterRepository;

@RestController
@RequestMapping("test")
public class TestController {

  @Autowired
  DataSourceParameterRepository dataSourceParameterRepository;

  @GetMapping()
  public void test() {
      DataSourceParameter model = dataSourceParameterRepository.findById(1l).get();
      NamedParameterJdbcTemplate template = DaoPlatform.jdbcDatasource(model.getDriverClassName(), model.getPass(),
              model.getUrl(), model.getUsername());
      String sql = "select * from bf_user_";
      Map<String, Object> paramMap = new HashMap<>();
      List<Map<String, Object>> all = template.queryForList(sql, paramMap);
      if (CollectionUtils.isNotEmpty(all)) {
          for (int i = 0; i < all.size(); i++) {
              StringBuffer sBuffer = new StringBuffer();
              for (int j = 0; i < all.get(i).values().size(); j++) {
                  if (j == 0) {
                      sBuffer.append("(");
                      // sBuffer.append(all.get(i).values().)
                  }
              }
          }
      }

      System.out.println(all);
      DataSourceParameter model2 = dataSourceParameterRepository.findById(100l).get();
      NamedParameterJdbcTemplate template2 = >DaoPlatform.jdbcDatasource(model2.getDriverClassName(), model2.getPass(),
              model2.getUrl(), model2.getUsername());
      /* String sql2 = "select * from COUNTRY_CROSSOVER_INFO"; */
      String sql2 = "insert into test values(1,'name'),(2,'ysh')";
      Map<String, Object> paramMap2 = new HashMap<>();
      // template2.update(sql2, paramMap2);
      // System.out.println(all2);
  }

  public static void main(String[] args) {
      List<Map<String, Object>> maps = new ArrayList<>();
      Map<String, Object> map = new HashMap<>();
      map.put("test2", "221");
      map.put("test3", "222");
      map.put("test4", "223");
      map.put("test5", "224");
      map.put("test", "225");
      map.put("test6", "226");
      maps.add(map);
      Map<String, Object> map2 = new HashMap<>();
      map2.put("test2", "2211");
      map2.put("test3", "2221");
      map2.put("test4", "2231");
      map2.put("test5", "2241");
      map2.put("test", "2251");
      map2.put("test6", "2261");
      maps.add(map2);
      Map<String, Object> map3 = new HashMap<>();
      map3.put("test2", "2211");
      map3.put("test3", "2221");
      map3.put("test4", "2231");
      map3.put("test5", "2241");
      map3.put("test", "2251");
      map3.put("test6", "2261");
      maps.add(map3);
      Map<String, Object> map4 = new HashMap<>();
      map4.put("test2", "2211");
      map4.put("test3", "2221");
      map4.put("test4", "2231");
      map4.put("test5", "2241");
      map4.put("test", "2251");
      map4.put("test6", "2261");
      maps.add(map4);
      Map<String, Object> map5 = new HashMap<>();
      map5.put("test2", "2211");
      map5.put("test3", "2221");
      map5.put("test4", "2231");
      map5.put("test5", "2241");
      map5.put("test", "2251");
      map5.put("test6", "2261");
      maps.add(map5);
      StringBuffer sBuffer = new StringBuffer();
      for (int i = 1; i <= maps.size(); i++) {
          Map<String, Object> temp = maps.get(i - 1);
          Object[] obj = temp.values().toArray();
          for (int k = 0; k < obj.length; k++) {
              if (k == 0) {
                  sBuffer.append("('");
                  sBuffer.append(obj[k]);
                  sBuffer.append("',");
              } else if (k == obj.length - 1) {
                  sBuffer.append("'");
                  sBuffer.append(obj[k]);
                  sBuffer.append("'),");
              } else {
                  sBuffer.append("'");
                  sBuffer.append(obj[k]);
                  sBuffer.append("',");
              }
          }

          if (i % 2 == 0) {
              String string = sBuffer.substring(0, sBuffer.length() - 1);
              System.out.println(string);
              sBuffer.delete(0, sBuffer.length());
          }
          if (i == maps.size()) {
              if (sBuffer.length() > 0) {
                  String string = sBuffer.substring(0, sBuffer.length() - 1);
                  System.out.println(string);
                  sBuffer.delete(0, sBuffer.length());
              }
          }
      }
  }
}

上面是测试查找与插入语句的执行。最后的测试类是用于限制每次插入的行数。