Java SimpleJdbcTemplate类代码示例

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
@Override
public NonceVerifier createVerifier(int maxAge) {
	DataSource dataSource = new SingleConnectionDataSource(
			"org.hsqldb.jdbcDriver",
			"jdbc:hsqldb:mem:saasstore_security_client", "sa", "", true);
	SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	jdbcTemplate.getJdbcOperations().execute(
			"DROP TABLE IF EXISTS openid_nonce;");
	jdbcTemplate
			.getJdbcOperations()
			.execute(
					"CREATE TABLE openid_nonce (  "
							+ "opurl varchar(255) NOT NULL,  nonce varchar(25) NOT NULL,  "
							+ "date datetime DEFAULT NULL,  PRIMARY KEY (opurl,nonce))");

	JdbcNonceVerifier jdbcNonceVerifier = new JdbcNonceVerifier(maxAge,
			"openid_nonce");
	jdbcNonceVerifier.setDataSource(dataSource);
	return jdbcNonceVerifier;
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
/**
 * Consulta issues pelo id. Traz detalhes do repositorio e nome do projeto
 * 
 * @return
 */
public List<IssueVO> pesquisarIssues(Long... issueId) {

	final String queryIssue = 
	" select " +
	"		i.id, " +
	"		st.is_closed, " +
	" 	r.url, " +
	"		r.root_url, " +
	"		r.project_id, " +
	"		p.name," +
	"		p.parent_id " + 
	" from issues i " +
	" inner join repositories r on r.project_id = i.project_id " +
	" inner join issue_statuses st on st.id = i.status_id " +
	" inner join projects p on p.id = r.project_id" + 
	" where i.id in (:ids) ";

	SimpleJdbcTemplate simpleJdbcTemplate = getSimpleJdbcTemplate();
	List<IssueVO> issue = simpleJdbcTemplate.query(queryIssue, new IssueVORowMapper(), Collections.singletonMap("ids", Arrays.asList(issueId)));

	return issue;
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
protected FileInfo loadData(ServerRequest sr) throws IOException, DataAccessException {

        if (!(sr instanceof TableServerRequest)) {
            throw new IllegalArgumentException("FileInfoProcessor.loadData Requires an TableServerRequest");
        }
        TableServerRequest request= (TableServerRequest)sr;
        SimpleJdbcTemplate jdbc = JdbcFactory.getSimpleTemplate(getDbInstance());

        String sql = getSql(request);
        Object[] params = getSqlParams(request);

        final FileInfoRowMapper fim = makeRowMapper(request);
        ParameterizedRowMapper<FileInfo> mapper = new ParameterizedRowMapper<FileInfo>() {
            public FileInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                return fim.mapRow(resultSet, i);
            }
        };

        LOGGER.info("Executing SQL query: " + sql,
                 "         Parameters: " + "{" + CollectionUtil.toString(params) + "}");
        FileInfo val = jdbc.queryForObject(sql, mapper, params);

        return val;

    }
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public void setupDatabase() throws Exception {

        setDataSource(new SimpleDataSource(m_driver, m_url + getTestDatabase(),
                m_adminUser, m_adminPassword));
        setAdminDataSource(new SimpleDataSource(m_driver, m_url + "template1",
                m_adminUser, m_adminPassword));

        if (!m_useExisting) {
            // Synchronize around a static mutex to prevent multiple connections
            // to the template1 database
            synchronized(TEMPLATE1_MUTEX) {
                createTestDatabase();
            }
        }

        // Test connecting to test database.
        Connection connection = getConnection();
        connection.close();

        setJdbcTemplate(new SimpleJdbcTemplate(this));
    }
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
@Before
public void setUp() throws Exception
{
    ctx = ApplicationContextHelper.getApplicationContext();
    dataSource = (DataSource) ctx.getBean("dataSource");
    tx = (PlatformTransactionManager) ctx.getBean("transactionManager"); 
    jdbcTemplate = new SimpleJdbcTemplate(dataSource);
    exporter = new ExportDb(ctx);
    exporter.setNamePrefix("export_test_");
    dialect = (Dialect) ctx.getBean("dialect");
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
@Autowired
public void init(DataSource dataSource) {
	this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);

	this.insertOwner = new SimpleJdbcInsert(dataSource)
		.withTableName("owners")
		.usingGeneratedKeyColumns("id");
	this.insertPet = new SimpleJdbcInsert(dataSource)
		.withTableName("pets")
		.usingGeneratedKeyColumns("id");
	this.insertVisit = new SimpleJdbcInsert(dataSource)
		.withTableName("visits")
		.usingGeneratedKeyColumns("id");
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public Collection<Integer> listConversationsSince(int id)
        throws DataAccessException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    try {
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.query(SELECT_CONVERSATIONS, ID_MAPPER, params);
    } catch (EmptyResultDataAccessException erdae) {
        return Collections.emptyList();
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public int getMessageContentId(int headerId) throws DataAccessException {
    try {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(ID, headerId, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.queryForInt(SELECT_CONTENT_ID, params);
    } catch (EmptyResultDataAccessException erdae) {
        return -1;
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public int getMessageContentSize(int id) throws DataAccessException {
    try {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.queryForInt(SELECT_CONTENT_SIZE, params);
    } catch (EmptyResultDataAccessException erdae) {
        return -1;
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public Conversation getConversation(int id) throws DataAccessException {
    try {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.queryForObject(SELECT_SUMMARY, CONVERSATION_MAPPER,
                params);
    } catch (EmptyResultDataAccessException erdae) {
        return null;
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public byte[] loadMessageContent(int id) throws DataAccessException {
    try {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.queryForObject(SELECT_CONTENT, CONTENT_MAPPER,
                params);
    } catch (EmptyResultDataAccessException erdae) {
        return null;
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public RequestHeader loadRequestHeader(int id) throws DataAccessException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    try {
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.queryForObject(SELECT_REQUEST, REQUEST_MAPPER,
                params);
    } catch (EmptyResultDataAccessException erdae) {
        return null;
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
public MutableResponseHeader loadResponseHeader(int id)
        throws DataAccessException {
    try {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(ID, id, Types.INTEGER);
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(
                getNamedParameterJdbcTemplate());
        return template.queryForObject(SELECT_RESPONSE, RESPONSE_MAPPER,
                params);
    } catch (EmptyResultDataAccessException erdae) {
        return null;
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
/**
 * Detecta repositorios registrados no redmine e seus respectivos projetos que foram afetados pela mudança, a partir da URL alterada
 * @param urlAlteracao
 * @param repoUUID
 * @return
 */
protected List<RepositorioVO> affectedRepoUUID(String urlAlteracao, String repoUUID) {
	final String queryRepositoriosAfetados = " select " + 
	"     p.id, " + 
	"     p.name, " + 
	"		  p.parent_id, " +		
	"     r.id repository_id, " + 
	"     r.url, " + 
	"     r.repouuid, " + 
	"     r.root_url " + 
	" from  " +
	"     repositories r "+ 
	" inner join projects p on " + 
	"     p.id = r.id  " + 
	" where   " + 
	"     r.repouuid = ? and " + 
	"     instr(concat(r.root_url,'/',?),r.url)  " + 
	" order by  " +
	"     length(url) desc";

	SimpleJdbcTemplate jdbcTemplate = getSimpleJdbcTemplate();

	List<RepositorioVO> repositorioVOs = jdbcTemplate.query(queryRepositoriosAfetados, new RepositorioRowMapper(), repoUUID, urlAlteracao);

	determinaProjetosAfetados(repositorioVOs);

	return repositorioVOs;
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; //导入依赖的package包/类
/**
 * Consulta projetos afetados pela mudança realizada na url informada.
 * 
 * @return
 */
protected List<RepositorioVO> affectedRepoNoUUID(String urlAlteracao) {

	final String queryRepositoriosAfetados = " select " + 
	"     p.id, " + 
	"     p.name, " + 
	"		  p.parent_id, " +		
	"     r.id repository_id, " + 
	"     r.url, " + 
	"     r.root_url " + 
	" from  " +
	"     repositories r "+ 
	" inner join projects p on " + 
	"     p.id = r.id  " + 
	" where   " + 
	"     instr(concat(r.root_url,'/',?),r.url)  " + 
	" order by  " +
	"     length(url) desc";

	SimpleJdbcTemplate jdbcTemplate = getSimpleJdbcTemplate();

	List<RepositorioVO> repositorioVOs = jdbcTemplate.query(queryRepositoriosAfetados, new RepositorioRowMapper(false), urlAlteracao);

	determinaProjetosAfetados(repositorioVOs);

	return repositorioVOs;
}