Java JdbcTemplate類代碼示例

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
/**
 * 刪除曆史節點.
 */
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);
    }
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
@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());
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
@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());
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
/**
 * 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;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
@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);
    }
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
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;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
/**
 * 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;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
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);
			}
		}
	}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
@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;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
/**
 * 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++;
	}
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
/**
 * 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;
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
@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);
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
/**
 * 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);
}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
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;
	}

}
 

import org.springframework.jdbc.core.JdbcTemplate; //導入依賴的package包/類
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;
	}