Java Query.executeBatch方法代码示例

import org.sql2o.Query; //导入方法依赖的package包/类
public void importStuff(Path path) throws IOException {
	Stream<String> stream;
	if (!path.toFile().exists()
			&& new File(path.toFile().getPath() + ".gz").exists()) {
		path = Paths.get(path.toFile().getPath() + ".gz");
		stream = GZIPFiles.lines(path);
	} else
		stream = Files.lines(path);
	LOGGER.info("Starting import of {} into {}", path, tableName);
	final Iterator<String[]> iter = stream.map(x -> x.split(","))
			.iterator();
	long i = 1; //error if 0 due to criteria below
	Query query = null;
	try {
		while (iter.hasNext()) {
			if (query == null)
				query = sql2o.beginTransaction()
						.createQuery(String.format(sql, tableName));

			final String[] data = iter.next();
			addParameters(data, query).addParameter("corpus", corpusId)
					.addToBatch();
			//Nicer batch import leaks memory, thus using new connections is recommended
			++i;

			if ((i % IMPORT_BATCH_SIZE) == 0)
				query.executeBatch();
			if ((i % IMPORT_COMMIT_SIZE) == 0) {
				query.getConnection().commit();
				LOGGER.info("Finishd import of {}", i);
				query = null;
			}
		}
	} finally {
		if (query != null)
			query.executeBatch().commit();
	}
	LOGGER.info("Finished import of {} rows from {} into {}", i, path,
			tableName);
}
 

import org.sql2o.Query; //导入方法依赖的package包/类
@Override
    public void calcOilWellScsj(Calendar c) {
        log.info("开始计算油井运行时间：" + LocalDateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
        if (Scheduler.youJingList != null && Scheduler.youJingList.size() > 0) {
            Map<String, String> yxMap = realtimeDataService.getEndTagVarInfo(Scheduler.youCodeList, VarSubTypeEnum.YOU_JING_YUN_XING.toString().toLowerCase());
//            String sql = "Insert into T_Oil_Well_Calc_Data"
//                    + "(ID, CODE, MINITE, IS_ON, LRSJ) "
//                    + "values (:ID, :CODE, :MINITE, :ISON, :LRSJ)";

            String updateSql = "update T_Oil_Well_Calc_Data set IS_ON = :ISON ,LRSJ = :LRSJ "
                    + "where CODE=:CODE and MINITE=:MINITE ";

            int minite = c.get(Calendar.MINUTE);
            if (c.get(Calendar.HOUR_OF_DAY) % 2 != 0) {
                minite += 60;
            }

            try (Connection con = sql2o.beginTransaction()) {
                Query query = con.createQuery(updateSql);

                for (EndTag youJing : Scheduler.youJingList) {
                    String code = youJing.getCode();
                    Integer isOn = yxMap.get(code) == null ? null : (Boolean.valueOf(yxMap.get(code)) ? 1 : 0);

                    query.addParameter("CODE", code)
                            .addParameter("MINITE", String.valueOf(minite))
                            .addParameter("ISON", isOn)
                            .addParameter("LRSJ", new Date())
                            .addToBatch();
                }
                query.executeBatch();
                con.commit();
                log.info("计算完:" + minite);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        log.info("完成计算油井运行时间：" + LocalDateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
    }
 

import org.sql2o.Query; //导入方法依赖的package包/类
private List<PriceEntry> fetchPrice(int itemId)
{
	try (Connection con = sql2o.beginTransaction())
	{
		RSPrices rsprice = fetchRSPrices(itemId);
		List<PriceEntry> entries = new ArrayList<>();
		Instant now = Instant.now();

		Query query = con.createQuery("insert into prices (item, price, time, fetched_time) values (:item, :price, :time, :fetched_time) "
			+ "ON DUPLICATE KEY UPDATE price = VALUES(price), fetched_time = VALUES(fetched_time)");

		for (Entry<Long, Integer> entry : rsprice.getDaily().entrySet())
		{
			long ts = entry.getKey(); // ms since epoch
			int price = entry.getValue(); // gp

			Instant time = Instant.ofEpochMilli(ts);

			PriceEntry priceEntry = new PriceEntry();
			priceEntry.setItem(itemId);
			priceEntry.setPrice(price);
			priceEntry.setTime(time);
			priceEntry.setFetched_time(now);
			entries.add(priceEntry);

			query
				.addParameter("item", itemId)
				.addParameter("price", price)
				.addParameter("time", time)
				.addParameter("fetched_time", now)
				.addToBatch();
		}

		query.executeBatch();
		con.commit();

		return entries;
	}
	catch (IOException ex)
	{
		logger.warn("unable to fetch price for item {}", itemId, ex);
		return null;
	}
}
 

import org.sql2o.Query; //导入方法依赖的package包/类
@RequestMapping(method = POST)
public void submit(@RequestBody XteaRequest xteaRequest)
{
	try (Connection con = sql2o.beginTransaction();
		Connection cacheCon = cacheSql2o.open())
	{
		CacheDAO cacheDao = new CacheDAO();
		CacheEntry cache = cacheDao.findMostRecent(cacheCon);

		if (cache == null)
		{
			throw new InternalServerErrorException("No most recent cache");
		}

		Query query = con.createQuery("insert into xtea (region, rev, key1, key2, key3, key4) "
			+ "values (:region, :rev, :key1, :key2, :key3, :key4)");

		for (XteaKey key : xteaRequest.getKeys())
		{
			int region = key.getRegion();
			int[] keys = key.getKeys();

			XteaEntry xteaEntry = findLatestXtea(con, region);

			if (keys.length != 4)
			{
				throw new IllegalArgumentException("Key length must be 4");
			}

			// already have these?
			if (xteaEntry != null
				&& xteaEntry.getKey1() == keys[0]
				&& xteaEntry.getKey2() == keys[1]
				&& xteaEntry.getKey3() == keys[2]
				&& xteaEntry.getKey4() == keys[3])
			{
				continue;
			}

			if (!checkKeys(cacheCon, cache, region, keys))
			{
				continue;
			}

			query.addParameter("region", region)
				.addParameter("rev", xteaRequest.getRevision())
				.addParameter("key1", keys[0])
				.addParameter("key2", keys[1])
				.addParameter("key3", keys[2])
				.addParameter("key4", keys[3])
				.addToBatch();
		}

		query.executeBatch();
		con.commit();
	}
}
 

import org.sql2o.Query; //导入方法依赖的package包/类
/**
 * 更新数据
 */
private void updateData(String sql, List<Map<String, Object>> fieldList, List<Map<String, Object>> recordList, Date date, String key) {
    String s2 = key.split(",")[1];
    con2 = sql2o2.beginTransaction();
    Query query = con2.createQuery(sql);
    int i = 0;
    for (Map<String, Object> map : recordList) {
        String jh = (String) map.get("jlmc");
        String code = (String) map.get("ysjlmc");
        for (Map<String, Object> fieldMap : fieldList) {
            String zdmc = (String) fieldMap.get("zdmc");
            String tscl = (String) fieldMap.get("tscl");
            String myTscl = tscl == null ? "" : tscl.toLowerCase();
            switch (myTscl) {
                case "":
                    query.addParameter(zdmc, "1");
                    break;
                case "cjsj":
                    query.addParameter(zdmc, date);
                    break;
                case "jh":
                    query.addParameter(zdmc, jh);
                    break;
                default:
                    query.addParameter(zdmc, "1");
                    break;
            }
        }
        query.addParameter(s2, (String) map.get(s2.toLowerCase()));
        query.addToBatch();
        i++;
        if (i >= 2) {// 每?条执行一次提交
            query.executeBatch();
            i = 0;
        }
    }
    if (i > 0) {
        query.executeBatch();
    }
    con2.commit();
}
 

import org.sql2o.Query; //导入方法依赖的package包/类
@Override
    public void calcWaterWellScsj(Calendar c) {
        log.info("开始计算水井运行时间：" + LocalDateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
        if (Scheduler.shuiJingList != null && Scheduler.shuiJingList.size() > 0) {
//            String sql = "Insert into T_Water_Well_Calc_Data"
//                    + "(ID, CODE, MINITE, IS_ON, LRSJ) "
//                    + "values (:ID, :CODE, :MINITE, :ISON, :LRSJ)";

            String updateSql = "update T_Water_Well_Calc_Data set IS_ON = :ISON ,LRSJ = :LRSJ "
                    + "where CODE=:CODE and MINITE=:MINITE ";

            int minite = c.get(Calendar.MINUTE);
            if (c.get(Calendar.HOUR_OF_DAY) % 2 != 0) {
                minite += 60;
            }

            try (Connection con = sql2o.beginTransaction()) {
                Query query = con.createQuery(updateSql);
                for (EndTag shuiJing : Scheduler.shuiJingList) {
                    //水井状态
                    Integer isOn = null;
                    String extConfigInfo = shuiJing.getExtConfigInfo();		// 获得扩展信息 
                    if (extConfigInfo != null && !"".equals(extConfigInfo.trim())) {
                        String[] framesLine = extConfigInfo.trim().replaceAll("\\r", "").split("\\n");// 替换字符串									
                        for (String varName : framesLine) {
                            if (varName.contains("yx|")) {
                                String varNames[] = varName.trim().split("\\|");
                                String varName1 = varNames[1];
                                String codeName = varNames[2];
                                String varNameStr = varNames[3];
//                                if (varName1.contains("shll")) { //瞬时流量
//                                    String ssllValue = realtimeDataService.getEndTagVarInfo(codeName, varNameStr);
//                                    if (ssllValue != null) {
//                                        isOn = Float.valueOf(ssllValue)>0.1 ? 1 : 0;
//                                    }
//                                    break;
//                                }
                                if (varName1.contains("fmqg")) { //阀门状态
                                    String fmqgValue = realtimeDataService.getEndTagVarInfo(codeName, varNameStr);
                                    if (fmqgValue != null) {
                                        if ("true".equals(fmqgValue)) {
                                            isOn = 0;
                                        } else {
                                            isOn = 1;
                                        }
                                    }
                                }
                            }
                        }
                    }

                    query.addParameter("CODE", shuiJing.getCode())
                            .addParameter("MINITE", String.valueOf(minite))
                            .addParameter("ISON", isOn)
                            .addParameter("LRSJ", new Date())
                            .addToBatch();
                }
                query.executeBatch();
                con.commit();
                log.info("计算完:" + minite);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        log.info("完成计算水井运行时间：" + LocalDateTime.now().toString("yyyy-MM-dd HH:mm:ss"));
    }