跨数据源拷贝表

需求

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

思路

采用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()); } } } } } 上面是测试查找与插入语句的执行。最后的测试类是用于限制每次插入的行数。