Java 类org.springframework.jdbc.core.JdbcTemplate 实例源码

/**
 * 删除历史节点.
 */
public void deleteHistoryActivities(List<String> historyNodeIds) {
    JdbcTemplate jdbcTemplate = ApplicationContextHelper
            .getBean(JdbcTemplate.class);
    logger.info("historyNodeIds : {}", historyNodeIds);

    for (String id : historyNodeIds) {
        String taskId = jdbcTemplate.queryForObject(
                "select task_id_ from ACT_HI_ACTINST where id_=?",
                String.class, id);

        if (taskId != null) {
            Context.getCommandContext()
                    .getHistoricTaskInstanceEntityManager()
                    .deleteHistoricTaskInstanceById(taskId);
        }

        jdbcTemplate.update("delete from ACT_HI_ACTINST where id_=?", id);
    }
}

public List<Map<String, Object>> queryObjDataPageByCondition(String tableName, String condition,String cols,String orders, int start,int end ) {
    /*      JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
    .getDbSource(dbName);*/
    JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
    String sql = "";
    String tempType=calcuDbType();
    if(tempType!=null && tempType.equals("mysql")){
    //if(dbType!=null && dbType.equals("mysql")){
        int pageRows=end-start;
        String limit=start+","+pageRows;
        sql="select "+cols+" from " + tableName + " where "+condition+" order by "+orders+ " limit "+limit;
    }else{
        String startLimit=" WHERE NHPAGE_RN >= "+start;
        String endLimit=" WHERE ROWNUM < "+end;
        String innerSql="select "+cols+" from " + tableName + " where "+condition+" order by "+orders;
        sql="SELECT * FROM ( SELECT NHPAGE_TEMP.*, ROWNUM NHPAGE_RN FROM ("+ innerSql +" ) NHPAGE_TEMP "+endLimit+" ) "+ startLimit;
    }
    logger.debug(sql);

    List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql);
    return retList;
}

@Test
public void test() throws SQLException {
  BasicDataSource dataSource = getDataSource("");

  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  jdbcTemplate.execute("CREATE TABLE employee (id INTEGER)");

  dataSource.close();

  List<MockSpan> finishedSpans = mockTracer.finishedSpans();
  assertEquals(1, finishedSpans.size());
  checkTags(finishedSpans, "myservice", "jdbc:hsqldb:mem:spring");
  checkSameTrace(finishedSpans);

  assertNull(mockTracer.scopeManager().active());
}

@Test
public void testZUploadWithInsertWithFailAuth() throws Exception {
    httpServer.stubFor(get(urlPathEqualTo("/login.jsp")).willReturn(aResponse().withStatus(HttpStatus.SC_NOT_FOUND)));
    httpServer.start();

    this.subscription = getSubscription("gStack");
    resource.upload(new ClassPathResource("csv/upload/nominal-complete2.csv").getInputStream(), ENCODING, subscription,
            UploadMode.FULL);
    final ImportStatus result = jiraResource.getTask(subscription);
    Assert.assertEquals(1, result.getChanges().intValue());
    Assert.assertEquals(30, result.getStep());
    Assert.assertEquals(10000, result.getJira().intValue());
    Assert.assertTrue(result.getCanSynchronizeJira());
    Assert.assertTrue(result.getScriptRunner());
    Assert.assertFalse(result.getSynchronizedJira());

    // Greater than the maximal "pcounter"
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
    Assert.assertEquals(5124,
            jdbcTemplate.queryForObject("SELECT pcounter FROM project WHERE ID = ?", Integer.class, 10000).intValue());
}

/**
 * Finds the single Category record having the specified Name,
 * and returns it.
 * 
 * @param name
 *          The Name column value of the record to find.
 * @return Category - the Category object whose Name matches
 *         the specified Name, or null if no match was found.
 */
public Category findByName(String name) {
  Category ret = null;
  String sql = "SELECT * FROM " + Category.TABLE_NAME + " WHERE name = ?";
  Object[] paramValues = { name };
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  List<Category> categories = jdbc.query(sql, paramValues, new CategoryRowMapper());
  if (categories.size() > 1) {
    throw new RuntimeException("Expected 1 result from findByName(), instead found " + categories.size()
        + " (DB configuration error, maybe?)");
  }
  if (!categories.isEmpty()) {
    ret = categories.get(0);
  }
  return ret;
}

@Override
public void dumpHisAfnemerindicatieTabel(final File outputFile) {
    LOGGER.info("Genereer hisafnemerindicatie rijen naar {}", outputFile.getAbsolutePath());
    try (FileOutputStream fos = new FileOutputStream(outputFile)) {
        final SqlRowSet sqlRowSet = new JdbcTemplate(masterDataSource).queryForRowSet("select hpa.* from autaut.his_persafnemerindicatie hpa\n"
                + "inner join (select pa.id as afnemerindid \n"
                + "from autaut.persafnemerindicatie pa, autaut.levsautorisatie la where pa.levsautorisatie = la.id and la.dateinde is null) \n"
                + "as x on hpa.persafnemerindicatie = x.afnemerindid");
        while (sqlRowSet.next()) {
            IOUtils.write(String.format("%s,%s,%s,%s,%s,%s,%s,%s%n",
                    sqlRowSet.getString(INDEX_HIS_ID),
                    sqlRowSet.getString(INDEX_HIS_PERSAFNEMERINDICATIE),
                    sqlRowSet.getString(INDEX_HIS_TSREG),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_TSVERVAL), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DIENSTINHOUD), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DIENSTVERVAL), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DATAANVANGMATERIELEPERIODE), AfnemerindicatieConversie.NULL_VALUE),
                    StringUtils.defaultIfBlank(sqlRowSet.getString(INDEX_HIS_DATEINDEVOLGEN), AfnemerindicatieConversie.NULL_VALUE)
            ), fos, StandardCharsets.UTF_8);
        }
    } catch (IOException e) {
        throw new IllegalStateException(e);
    }
}

/**
 * Return all status changes of issues of given project.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @param pkey
 *            the project 'pkey'.
 * @param resultType
 *            the bean type to build in result list.
 * @param timing
 *            When <code>true</code> time spent data is fetched.
 * @param summary
 *            When <code>true</code> Summary is fetched.
 * @return status changes of all issues of given project.
 */
public <T> List<T> getChanges(final DataSource dataSource, final int jira, final String pkey, final Class<T> resultType, final boolean timing,
        final boolean summary) {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    final RowMapper<T> rowMapper = new BeanPropertyRowMapper<>(resultType);

    // First, get all created issues (first change)
    final List<T> issues;
    final String sqlPart = ", RESOLUTION AS resolution, PRIORITY AS priority, issuestatus AS status, ASSIGNEE AS assignee,"
            + " REPORTER AS reporter, issuetype AS type, ? AS toStatus, DUEDATE AS dueDate, created"
            + (timing ? ", TIMESPENT AS timeSpent, TIMEESTIMATE AS timeEstimate, TIMEORIGINALESTIMATE AS timeEstimateInit" : "")
            + (summary ? ", SUMMARY AS summary" : "") + "  FROM jiraissue WHERE PROJECT = ?";
    if (getJiraVersion(dataSource).compareTo("6.0.0") < 0) {
        // JIRA 4-5 implementation, use "pkey"
        issues = jdbcTemplate.query("SELECT ID AS id, pkey AS pkey" + sqlPart, rowMapper, STATUS_OPEN, jira);
    } else {
        // JIRA 6+, "pkey" is no more available in the 'jiraissue' table
        issues = jdbcTemplate.query("SELECT ID AS id, CONCAT(?, issuenum) AS pkey" + sqlPart, rowMapper, pkey + "-", STATUS_OPEN, jira);
    }
    return issues;
}

public void addPointForBuying(OrderMessage msg){

        BusinessIdentifer businessIdentifer = ReflectUtil.getBusinessIdentifer(msg.getClass());

        JdbcTemplate jdbcTemplate = util.getJdbcTemplate(applicationName,businessIdentifer.busCode(),msg);
        int update = jdbcTemplate.update("update `point` set point = point + ? where user_id = ?;", 
                msg.getAmount(),msg.getUserId());

        if(update != 1){
            throw new RuntimeException("can not find specific user id!");
        }

        //for unit test
        if(successErrorCount != null){
            currentErrorCount++;
            if(successErrorCount < currentErrorCount){
                currentErrorCount = 0;
            } else {
                throw new UtProgramedException("error in message consume time:" + currentErrorCount);
            }
        }
    }

@Override
public List<String> queryColumnByMedia(DataMedia dataMedia) {
    List<String> columnResult = new ArrayList<String>();
    if (dataMedia.getSource().getType().isNapoli()) {
        return columnResult;
    }

    DataSource dataSource = dataSourceCreator.createDataSource(dataMedia.getSource());
    // 针对multi表，直接获取第一个匹配的表结构
    String schemaName = dataMedia.getNamespaceMode().getSingleValue();
    String tableName = dataMedia.getNameMode().getSingleValue();
    try {
        Table table = DdlUtils.findTable(new JdbcTemplate(dataSource), schemaName, schemaName, tableName);
        for (Column column : table.getColumns()) {
            columnResult.add(column.getName());
        }
    } catch (Exception e) {
        logger.error("ERROR ## DdlUtils find table happen error!", e);
    }

    return columnResult;
}

/**
 * Indicate the installation and activation of "script runner" plug-in.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @param issues
 *            The issues to add. The ID property of each inserted issue is
 *            also updated in these objects.
 * @param workflowStepMapping
 *            the {@link Map} linking type identifier to a {@link Map}
 *            linking status name to steps.
 */
public void addIssues(final DataSource dataSource, final int jira, final List<JiraIssueRow> issues,
        final Map<Integer, Workflow> workflowStepMapping) {
    final JdbcOperations jdbcTemplate = new JdbcTemplate(dataSource);
    int nextId = prepareForNextId(dataSource, ISSUE_NODE, issues.size());
    reserveProjectCounter(dataSource, jira, issues);
    int nextCurrentStepId = prepareForNextId(dataSource, "OSCurrentStep", issues.size());
    int nextWfEntryId = prepareForNextId(dataSource, "OSWorkflowEntry", issues.size());
    int counter = 0;
    for (final JiraIssueRow issueRow : issues) {
        issueRow.setId(nextId);
        log.info("Inserting issue {}-{}({}) {}/{}", issueRow.getPkey(), issueRow.getIssueNum(), issueRow.getId(),
                counter, issues.size());
        Workflow workflow = workflowStepMapping.get(issueRow.getType());
        if (workflow == null) {
            workflow = workflowStepMapping.get(0);
        }

        addIssue(jira, jdbcTemplate, nextId, nextCurrentStepId, nextWfEntryId, issueRow, workflow);
        nextId++;
        nextWfEntryId++;
        nextCurrentStepId++;
        counter++;
    }
}

/**
 * Add the given versions to the given project
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param jira
 *            the JIRA project identifier.
 * @param versions
 *            the version names to add
 * @return the {@link Map} where value is the created version identifier.
 */
public Map<String, Integer> addVersions(final DataSource dataSource, final int jira,
        final Collection<String> versions) {
    final Map<String, Integer> result = new HashMap<>();
    final JdbcOperations jdbcTemplate = new JdbcTemplate(dataSource);
    int nextId = prepareForNextId(dataSource, VERSION_NODE, versions.size());
    int nextSequence = getNextVersionSequence(jira, jdbcTemplate);
    for (final String version : versions) {
        jdbcTemplate.update("INSERT INTO projectversion (ID,PROJECT,vname,SEQUENCE) values(?,?,?,?)", nextId, jira,
                version, nextSequence);
        result.put(version, nextId);
        nextId++;
        nextSequence++;
    }
    return result;
}

@Bean
public JdbcTemplate postgresJdbcTemplate(
        @Value("${postgres.jdbcUrl}") String postgresJdbcUrl,
        @Value("${postgres.jdbcDriver}") String postgresJdbcDriver,
        @Value("${postgres.jdbcUser}") String postgresJdbcUser,
        @Value("${postgres.jdbcPassword}") String postgresJdbcPassword) {

    DataSource targetDataSource = DataSourceBuilder
            .create()
            .driverClassName(postgresJdbcDriver)
            .url(postgresJdbcUrl)
            .username(postgresJdbcUser)
            .password(postgresJdbcPassword)
            .build();

    return new JdbcTemplate(targetDataSource);
}

/**
 * return the block, with transactions added.
 *
 * @param block
 *            the block, to add transactions to.
 */
private void getTransactionsForBlock(final Block block) {
    final JdbcTemplate t = new JdbcTemplate(ds);
    final String sql = getSql("getTransactionsWithIndex");
    final byte[] blockIndexBa = block.index.toByteArray();
    final List<byte[]> dataList = t.queryForList(sql, byte[].class, blockIndexBa);

    for (final byte[] data : dataList) {
        final Transaction transaction = new Transaction(ByteBuffer.wrap(data));
        block.getTransactionList().add(transaction);
    }

    getTransactionOutputsWithIndex(block, t, blockIndexBa);

    getTransactionInputsWithIndex(block, t, blockIndexBa);

    getTransactionScriptsWithIndex(block, t, blockIndexBa);
}

public JdbcProtobufTemplate(JdbcTemplate jdbcTemplate,
        Class<M> messageClass, String tableName) {
    this.jdbcTemplate = jdbcTemplate;
    if (messageClass == null) {
        this.messageClass = this.parseMessageClass();
    } else {
        this.messageClass = messageClass;
    }
    this.descriptor = this.getDescriptor(messageClass);
    if (tableName == null) {
        this.tableName = this.parseTableName();
    } else {
        this.tableName = tableName;
    }

}

public List<Map<String, Object>> queryObjByCondition(String tableName, String condition,String cols, String orders,Object[] paramArray) {

        /*      JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
        .getDbSource(dbName);*/
        JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
        String sql = "select "+cols+" from " + tableName + " where "+condition+" order by "+orders;
        logger.debug(sql);
        logger.debug(Arrays.toString(paramArray));
        List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql,paramArray);
        return retList;
    }

@Override
protected void analyzeImpl(DatabaseContext ctx) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(ctx.getDataSource());
    String schemaName = ctx.getSchemaName();
    List<Map<String, Object>> foreignKeys = jdbcTemplate.queryForList(FOREIGN_KEY_QUERY, schemaName, schemaName);
    if (foreignKeys.size() > 0) {
        Issue issue = issueBuilder.builder()
            .type(getName())
            .weight(foreignKeys.size())
            .query(FOREIGN_KEY_QUERY)
            .dataSourceName(ctx.getDataSourceName())
            .weightUnit("idx")
            .description(buildDescription(foreignKeys))
            .build();
        issueRepository.addIssue(issue);
    }
}

@Test
public void testUploadDefaultWorkflowScheme() throws Exception {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
    try {
        // Delete the default project/workflow mapping, where type = '0'
        jdbcTemplate.update(
                "update nodeassociation SET SOURCE_NODE_ID=? WHERE SOURCE_NODE_ID=? AND SOURCE_NODE_ENTITY=? AND SINK_NODE_ID=? AND SINK_NODE_ENTITY=? AND ASSOCIATION_TYPE=?",
                1, 10074, "Project", 10025, "WorkflowScheme", "ProjectScheme");
        resource.upload(new ClassPathResource("csv/upload/nominal-simple.csv").getInputStream(), ENCODING, subscription,
                UploadMode.PREVIEW);
    } finally {
        jdbcTemplate.update(
                "update nodeassociation SET SOURCE_NODE_ID=? WHERE SOURCE_NODE_ID=? AND SOURCE_NODE_ENTITY=? AND SINK_NODE_ID=? AND SINK_NODE_ENTITY=? AND ASSOCIATION_TYPE=?",
                10074, 1, "Project", 10025, "WorkflowScheme", "ProjectScheme");
    }

    final ImportStatus result = jiraResource.getTask(subscription);
    Assert.assertEquals(UploadMode.PREVIEW, result.getMode());
    Assert.assertFalse(result.isFailed());
    Assert.assertEquals(22, result.getStep());
}

@Test
public void testSpringLocalTx() throws Exception {
    DataSource ds = wrap(createHsqlDataSource());
    JdbcTemplate jdbc = new JdbcTemplate(ds);
    TransactionTemplate tx = new TransactionTemplate(new DataSourceTransactionManager(ds));

    jdbc.execute(DROP_USER);
    jdbc.execute(CREATE_TABLE_USER);

    tx.execute(ts -> jdbc.update(INSERT_INTO_USER, 1, "user1"));
    User user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
    assertEquals(new User(1, "user1"), user);
    tx.execute(ts -> jdbc.update(DELETE_FROM_USER_BY_ID, 1));

    tx.execute(ts -> {
        int nb = jdbc.update(INSERT_INTO_USER, 1, "user1");
        ts.setRollbackOnly();
        return nb;
    });
    try {
        user = tx.execute(ts -> jdbc.queryForObject(SELECT_FROM_USER_BY_ID, new BeanPropertyRowMapper<>(User.class), 1));
        fail("Expected a EmptyResultDataAccessException");
    } catch (EmptyResultDataAccessException e) {
        // expected
    }
}

public int updateObjByCondition(String tableName, String condition,String setStr) {
    //JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
    JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
    String timeName=getTimeName();
    if(autoOperTime){
        setStr="update_time="+timeName+","+setStr;
    }

    String sql = "update " + tableName +" set "+setStr+ " where "+condition;
    logger.debug(sql);
    Integer retStatus=jdbcTemplate.update(sql);
    return retStatus;
}

public int getUserExpressCount(int userId){

    JdbcTemplate jdbcTemplate = util.getJdbcTemplate(Constant.APPID,ExpressDeliverAfterTransMethod.BUSINESS_CODE,(ExpressDeliverAfterTransMethodRequest)null);
    Integer queryForObject = jdbcTemplate.queryForObject("select count(1) from express where user_id = ?", Integer.class,userId);
    if(queryForObject == null){
        queryForObject = 0;
    }
    return queryForObject;
}

@Override
@Before
public void setUp() throws Exception {
    new JdbcTemplate(dataSource).execute("CREATE TABLE COM_AUDIT_TRAIL ( "
            + "AUD_USER      VARCHAR(100)  NOT NULL, "
            + "AUD_CLIENT_IP VARCHAR(15)    NOT NULL, "
            + "AUD_SERVER_IP VARCHAR(15)    NOT NULL, "
            + "AUD_RESOURCE  VARCHAR(100)  NOT NULL, "
            + "AUD_ACTION    VARCHAR(100)  NOT NULL, "
            + "APPLIC_CD     VARCHAR(5)    NOT NULL, "
            + "AUD_DATE      TIMESTAMP      NOT NULL)");
}

/**
 * Finds All Item records in the DB.
 * 
 * @return List<Item> - the List of item objects in the DB.
 *         Will be empty if none found.
 */
public List<Item> findAll() {
  List<Item> ret;
  String sql = "SELECT * FROM " + Item.TABLE_NAME;
  JdbcTemplate jdbc = new JdbcTemplate(getDataSource());
  ret = jdbc.query(sql, new ItemRowMapper());
  if (ret.size() > 1) {
    log.info("Found " + ret.size() + " rows from query");
  }
  return ret;
}

public WalletPayTccMethodResult doTryPay(WalletPayCascadeTccMethodRequest param) {
    JdbcTemplate jdbcTemplate = util.getJdbcTemplate(Constant.APPID,WalletPayTccMethod.METHOD_NAME,param);
    int update = jdbcTemplate.update("update `wallet` set freeze_amount = freeze_amount + ? where user_id = ? and (total_amount - freeze_amount) >= ?;", 
            param.getPayAmount(),param.getUserId(),param.getPayAmount());

    if(update != 1){
        throw new RuntimeException("can not find specific user id or have not enought money");
    }

    //start cascading transaction
    transaction.startEasyTrans(WalletPayCascadeTccMethod.METHOD_NAME, String.valueOf(System.currentTimeMillis()));
    //publish point message with an consumer cascading this transaction 
    OrderMessageForCascadingTest msg = new OrderMessageForCascadingTest();
    msg.setUserId(param.getUserId());
    msg.setAmount(param.getPayAmount());
    transaction.execute(msg);

    /**
     * 
     */
    ExpressDeliverAfterTransMethodRequest expressDeliverAfterTransMethodRequest = new ExpressDeliverAfterTransMethodRequest();
    expressDeliverAfterTransMethodRequest.setUserId(param.getUserId());
    expressDeliverAfterTransMethodRequest.setPayAmount(param.getPayAmount());
    transaction.execute(expressDeliverAfterTransMethodRequest);

    checkUtProgramedException();


    WalletPayTccMethodResult walletPayTccMethodResult = new WalletPayTccMethodResult();
    walletPayTccMethodResult.setFreezeAmount(param.getPayAmount());
    return walletPayTccMethodResult;
}

public void doConfirmPay(WalletPayCascadeTccMethodRequest param) {
    JdbcTemplate jdbcTemplate = util.getJdbcTemplate(Constant.APPID,WalletPayTccMethod.METHOD_NAME,param);
    int update = jdbcTemplate.update("update `wallet` set freeze_amount = freeze_amount - ?, total_amount = total_amount - ? where user_id = ?;", 
            param.getPayAmount(),param.getPayAmount(),param.getUserId());

    if(update != 1){
        throw new RuntimeException("unknow Exception!");
    }
}

private String getOwner(final String appId) {
    final JdbcTemplate simpleJdbcTemplate = new JdbcTemplate(dataSource);
    final List<Map<String, Object>> results = simpleJdbcTemplate.queryForList(
            "SELECT unique_id FROM locks WHERE application_id=?", appId);
    if (results.isEmpty()) {
        return null;
    }
    return (String) results.get(0).get("unique_id");
}

private static List<MetricRetention> loadRetentions(JdbcTemplate jdbcTemplate, String configName) {

        log.info("Loading retentions from ClickHouse, config: " + configName);

        List<MetricRetention> retentions = jdbcTemplate.query(
            "SELECT priority, is_default, regexp, function, " +
                "groupArray(age) AS ages, groupArray(precision) AS precisions FROM (" +
                "   SELECT * FROM system.graphite_retentions WHERE config_name = ? ORDER BY priority, age" +
                ") GROUP BY regexp, function, priority, is_default ORDER BY priority",
            (rs, rowNum) -> {
                String pattern = rs.getString("regexp") + ".*";
                String function = rs.getString("function");
                MetricRetention.MetricDataRetentionBuilder builder = MetricRetention.newBuilder(pattern, function);
                int[] ages = getIntArray(rs.getString("ages"));
                int[] precisions = getIntArray(rs.getString("precisions"));
                for (int i = 0; i < ages.length; i++) {
                    builder.addRetention(ages[i], precisions[i]);
                }
                return builder.build();
            },
            configName
        );
        if (retentions.isEmpty()) {
            throw new IllegalStateException("No retentions found in ClickHouse for config: " + configName);
        }
        log.info("Loaded " + retentions.size() + " retentions");
        return retentions;
    }

/**
 * 查找离当前节点最近的上一个userTask.
 */
public String findNearestUserTask(CommandContext commandContext) {
    TaskEntity taskEntity = commandContext.getTaskEntityManager()
            .findTaskById(taskId);

    if (taskEntity == null) {
        logger.debug("cannot find task : {}", taskId);

        return null;
    }

    Graph graph = new ActivitiHistoryGraphBuilder(
            taskEntity.getProcessInstanceId()).build();
    JdbcTemplate jdbcTemplate = ApplicationContextHelper
            .getBean(JdbcTemplate.class);
    String historicActivityInstanceId = jdbcTemplate.queryForObject(
            "SELECT ID_ FROM ACT_HI_ACTINST WHERE TASK_ID_=?",
            String.class, taskId);
    Node node = graph.findById(historicActivityInstanceId);

    String previousHistoricActivityInstanceId = this.findIncomingNode(
            graph, node);

    if (previousHistoricActivityInstanceId == null) {
        logger.debug(
                "cannot find previous historic activity instance : {}",
                taskEntity);

        return null;
    }

    return jdbcTemplate.queryForObject(
            "SELECT TASK_ID_ FROM ACT_HI_ACTINST WHERE ID_=?",
            String.class, previousHistoricActivityInstanceId);
}

@Override
public void migrate(final JdbcTemplate jdbcTemplate) throws Exception {
    Long acsAuthorizationZoneId = createDefaultAuthzZone(jdbcTemplate);
    /*
     * Let's find all the possible OAuth issuer_id and client_id combinations that exist in the old database
     * schema.
     */
    Set<ZoneClientEntity> existingOAuthClients = findExistingOAuthClients(jdbcTemplate);
    addOAuthClientsToDefaultAuthzZone(jdbcTemplate, acsAuthorizationZoneId, existingOAuthClients);
    dropConstraintsAndColumns(jdbcTemplate);
    removeDuplicateRows(jdbcTemplate, acsAuthorizationZoneId);
}

public int insertObj(String tableName, String cols,String values,final Object[] paramArray, KeyHolder keyHolder, final String idCol) {
    //JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
    JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
    String timeName=getTimeName();
    if(autoOperTime){
        cols="create_time,update_time,"+cols;
        values=timeName+","+timeName+","+values;
    }
    final String sql = "insert into " + tableName + " ("+cols+") values ("+values+")";
    logger.debug(sql);
    logger.debug(Arrays.toString(paramArray));

    //Integer retStatus=jdbcTemplate.update(sql,paramArray);
    Integer retStatus=jdbcTemplate.update(new PreparedStatementCreator() { 

        public PreparedStatement createPreparedStatement(Connection con) 
        throws SQLException { 
                String[] keyColNames=new String[1];
                keyColNames[0]=idCol;
                PreparedStatement ps=con.prepareStatement(sql,keyColNames); 
                if(paramArray!=null){
                    int size=paramArray.length;
                    for(int i=0;i<size;i++){
                        ps.setObject(i+1, paramArray[i]);
                    }
                }

                return ps; 
            } 
        }, keyHolder);      

    return retStatus;
}

@Before
public void setUp() throws Exception
{
    bundleExecutor = ctx.getBean("bundleExecutor", ScriptBundleExecutorImpl.class);
    dataSource = ctx.getBean("dataSource", DataSource.class);
    jdbcTmpl = new JdbcTemplate(dataSource);
}

public List<Map<String, Object>> queryObjJoinByCondition(String sql,Object[] paramArray) {

        /*      JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
        .getDbSource(dbName);*/
        JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
        logger.debug(sql);
        logger.debug(Arrays.toString(paramArray));
        List<Map<String, Object>> retList = jdbcTemplate.queryForList(sql,paramArray);
        return retList;
    }

/**
 * 判断跳过节点.
 */
public boolean isSkipActivity(String historyActivityId) {
    JdbcTemplate jdbcTemplate = ApplicationContextHelper
            .getBean(JdbcTemplate.class);
    String historyTaskId = jdbcTemplate.queryForObject(
            "select task_id_ from ACT_HI_ACTINST where id_=?",
            String.class, historyActivityId);

    HistoricTaskInstanceEntity historicTaskInstanceEntity = Context
            .getCommandContext().getHistoricTaskInstanceEntityManager()
            .findHistoricTaskInstanceById(historyTaskId);
    String deleteReason = historicTaskInstanceEntity.getDeleteReason();

    return "跳过".equals(deleteReason);
}

@Test
public void testUploadInvalidVersion() throws Exception {
    thrown.expect(ValidationJsonException.class);
    thrown.expect(MatcherUtil.validationMatcher("jira", "Required JIRA version is 6.0.0, and the current version is 4.4.2"));
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource);
    try {
        jdbcTemplate.update("update pluginversion SET pluginversion=? WHERE ID = ?", "4.4.2", 10075);
        resource.upload(new ClassPathResource("csv/upload/nominal-simple.csv").getInputStream(), ENCODING, subscription,
                UploadMode.PREVIEW);
    } finally {
        jdbcTemplate.update("update pluginversion SET pluginversion=? WHERE ID = ?", "6.0.1", 10075);
    }
}

public int delObjByCondition(String tableName, String condition,Object[] paramArray,int[] typeArray) {
    //JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder.getDbSource(dbName);
    JdbcTemplate jdbcTemplate =getMicroJdbcTemplate();
    String sql = "delete from " + tableName + " where "+condition;
    logger.debug(sql);
    logger.debug(Arrays.toString(paramArray));
    Integer retStatus=jdbcTemplate.update(sql,paramArray,typeArray);
    return retStatus;
}

/**
 * !!! Only supports MySQL
 */
public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern,
                                       final DdlSchemaFilter ddlSchemaFilter) {
    List<String> schemas = findSchemas(jdbcTemplate, schemaPattern);
    if (ddlSchemaFilter == null) {
        return schemas;
    }
    List<String> filterSchemas = new ArrayList<String>();
    for (String schema : schemas) {
        if (ddlSchemaFilter.accept(schema)) {
            filterSchemas.add(schema);
        }
    }
    return filterSchemas;
}

/**
 * Reset the login failed to bypass CAPTCHA guard.
 * 
 * @param dataSource
 *            The data source of JIRA database.
 * @param login
 *            The user login to reset.
 */
public void clearLoginFailed(final DataSource dataSource, final String login) {
    // Reset the CAPTCHA only for Jira 6+
    if (getJiraVersion(dataSource).compareTo("6.0.0") >= 0) {
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        final List<Integer> ids = jdbcTemplate.query("SELECT ID FROM cwd_user WHERE lower_user_name = ?", (rs, n) -> rs.getInt("ID"),
                login.toLowerCase(Locale.ENGLISH));
        if (!ids.isEmpty()) {
            jdbcTemplate.update("UPDATE cwd_user_attributes SET attribute_value=0, lower_attribute_value=0 WHERE attribute_name=? AND user_id=?",
                    "login.currentFailedCount", ids.get(0));
        }
    }
}

private void init(final JdbcTemplate jdbcTemplate, final String markTableName, final String markTableColumn) {
    int count = jdbcTemplate.queryForInt(MessageFormat.format(checkDataSql, markTableName, GLOBAL_THREAD_COUNT - 1));
    if (count != GLOBAL_THREAD_COUNT) {
        if (logger.isInfoEnabled()) {
            logger.info("Interceptor: init " + markTableName + "'s data.");
        }
        TransactionTemplate transactionTemplate = new TransactionTemplate();
        transactionTemplate.setTransactionManager(new DataSourceTransactionManager(jdbcTemplate.getDataSource()));
        transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_NOT_SUPPORTED);// 注意这里强制使用非事务，保证多线程的可见性
        transactionTemplate.execute(new TransactionCallback() {

            public Object doInTransaction(TransactionStatus status) {
                jdbcTemplate.execute(MessageFormat.format(deleteDataSql, markTableName));
                String batchSql = MessageFormat.format(updateSql, new Object[] { markTableName, markTableColumn });
                jdbcTemplate.batchUpdate(batchSql, new BatchPreparedStatementSetter() {

                    public void setValues(PreparedStatement ps, int idx) throws SQLException {
                        ps.setInt(1, idx);
                        ps.setInt(2, 0);
                        // ps.setNull(3, Types.VARCHAR);
                    }

                    public int getBatchSize() {
                        return GLOBAL_THREAD_COUNT;
                    }
                });
                return null;
            }
        });

        if (logger.isInfoEnabled()) {
            logger.info("Interceptor: Init EROSA Client Data: " + updateSql);
        }
    }

}

public AbstractDbDialect(JdbcTemplate jdbcTemplate, LobHandler lobHandler, String name, int majorVersion,
                         int minorVersion){
    this.jdbcTemplate = jdbcTemplate;
    this.lobHandler = lobHandler;
    // 初始化transction
    this.transactionTemplate = new TransactionTemplate();
    transactionTemplate.setTransactionManager(new DataSourceTransactionManager(jdbcTemplate.getDataSource()));
    transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);

    this.databaseName = name;
    this.databaseMajorVersion = majorVersion;
    this.databaseMinorVersion = minorVersion;

    initTables(jdbcTemplate);
}

@Override
public Transaction getTransactionWithHash(final UInt256 hash) {
    final JdbcTemplate t = new JdbcTemplate(ds);
    final String sql = getSql("getTransactionWithHash");
    final List<Map<String, Object>> dataList = t.queryForList(sql, hash.toByteArray());

    if (dataList.isEmpty()) {
        return null;
    }

    final Map<String, Object> data = dataList.get(0);

    final byte[] blockIndexBa = (byte[]) data.get("block_index");
    final byte[] transactionIndexBa = (byte[]) data.get(TRANSACTION_INDEX);
    final byte[] transactionBa = (byte[]) data.get("transaction");
    final int transactionIndex = getTransactionIndex(transactionIndexBa);

    final Transaction transaction = new Transaction(ByteBuffer.wrap(transactionBa));

    final Map<Integer, List<TransactionOutput>> outputsMap = getMapList(t,
            "getTransactionOutputsWithBlockAndTransactionIndex", new TransactionOutputMapToObject(), blockIndexBa,
            transactionIndexBa);
    transaction.outputs.addAll(outputsMap.get(transactionIndex));

    final Map<Integer, List<CoinReference>> inputsMap = getMapList(t,
            "getTransactionInputsWithBlockAndTransactionIndex", new CoinReferenceMapToObject(), blockIndexBa,
            transactionIndexBa);
    transaction.inputs.addAll(inputsMap.get(transactionIndex));

    final Map<Integer, List<Witness>> scriptsMap = getMapList(t,
            "getTransactionScriptsWithBlockAndTransactionIndex", new WitnessMapToObject(), blockIndexBa,
            transactionIndexBa);
    transaction.scripts.addAll(scriptsMap.get(transactionIndex));

    return transaction;
}

/**
 * Query JIRA database to collect activities of given users. For now, only the last success connection is
 * registered.
 * 
 * @param dataSource The JIRA datasource to query the user activities.
 * @param users
 *            the users to query.
 * @return activities.
 */
public Map<String, Activity> getActivities(final DataSource dataSource, final Collection<String> users) {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    final Map<String, Activity> result = new HashMap<>();
    if (!users.isEmpty()) {
        jdbcTemplate.query("SELECT u.lower_user_name AS login, attribute_value AS value FROM cwd_user AS u"
                + " INNER JOIN cwd_user_attributes AS ua ON ua.user_id = u.ID WHERE u.lower_user_name IN (" + newIn(users)
                + ") AND attribute_name=?;", rs -> {
                    final Activity activity = new Activity();
                    activity.setLastConnection(new Date(Long.valueOf(rs.getString("value"))));
                    result.put(rs.getString("login"), activity);
                }, ArrayUtils.add(users.toArray(), "lastAuthenticated"));
    }
    return result;
}