背景
在做需求的时候,出现这样的,往对方数据库推送数据
想法
接到这个需求,首先想到对于两个数据库之间的操作,用到的是JDBC的方式,Spring对JDBC也有支持。而对于推送数据有两种方式,一个是拼接sql语句,另一种是动态参数绑定
核心代码
String sql="insert into user (name,deptid) values (?,?)";
List<Object[]> batchArgs=new ArrayList<Object[]>();
batchArgs.add(new Object[]{"11",6});
batchArgs.add(new Object[]{"11",8});
batchArgs.add(new Object[]{"11",8});
jdbcTemplate.batchUpdate(sql, batchArgs);
具体做法
因为我这是定时任务job,可能不太一样
import com.ktamrinterface.model.YuXi;
import com.ktamrinterface.quartz.task.BaseJob;
import com.ktamrinterface.quartz.utils.DataSourceDB;
import org.apache.commons.beanutils.BeanUtils;
import org.quartz.DisallowConcurrentExecution;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionStatus;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@DisallowConcurrentExecution //作业不并发
@Component
public class YxssJob2 extends BaseJob implements Job {
protected final Logger logger = LoggerFactory.getLogger(YxssJob2.class);
@Override
public void execute(JobExecutionContext context) throws JobExecutionException {
DataSourceDB dataSourceDB = new DataSourceDB();
dataSourceDB.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
dataSourceDB.setUrl("jdbc:sqlserver://localhost:1433;database=test");
dataSourceDB.setUserName("sa");
dataSourceDB.setPassWord("111111");
JdbcTemplate jdbcTemplate = dataSourceDB.getJdbcTemplate();
DataSourceTransactionManager dataSourceTransactionManager = dataSourceDB.getDataSourceTransactionManager();
TransactionStatus status = null;
//待推送的数据 这里换成你的查询的比如 xxService.selectList();
List<Map<String,Object>> list = new ArrayList<>();
try {
status = dataSourceDB.beginTransaction(dataSourceTransactionManager);
List<Object[]> batchArgs = changeObject(list);
String sql="INSERT INTO test(MeterNo,CopyDate,ReadNumber,CompanyFlag,CustomNum) " +
" values (?,?,?,'1',?)";
jdbcTemplate.batchUpdate(sql, batchArgs);
//提交
dataSourceTransactionManager.commit(status);
}catch (Exception e){
e.printStackTrace();
logger.error("报错:"+e.getMessage());
//回滚
dataSourceTransactionManager.rollback(status);
}
}
/**
* 把List< Map<String,Object>> 转换为 List<Object[]>
* @param maps
* @return
*/
private List<Object[]> changeObject(List< Map<String,Object>> maps){
List<Object[]> batchArgs=new ArrayList<Object[]>();
//这里强转实体类,自己摸索要传什么数据,整理成实体类
YuXi yuXi=new YuXi();
Map<String, Object> map=null;
for(int i=0;i<maps.size();i++){
map = maps.get(i);
transMap2Bean2(map,yuXi);
batchArgs.add(new Object[]{yuXi.getMeternumber(),yuXi.getThrtime(),yuXi.getThnumber(),yuXi.getCustno()});
}
return batchArgs;
}
/**
* 将map转换为bean
* @param map
* @param obj
*/
public static void transMap2Bean2(Map<String, Object> map, Object obj) {
if (map == null || obj == null) {
return;
}
try {
BeanUtils.populate(obj, map);
} catch (Exception e) {
System.out.println("transMap2Bean2 Error " + e);
}
}
}
如果发现博文有问题,欢迎各位老鸟多多指点一二