Java Code Examples for org.springframework.jdbc.core.JdbcTemplate#queryForObject()

@Override
public void afterPropertiesSet() throws Exception {
    Assert.state(this.dataSource != null, "DataSource must be set");
    Assert.state(this.databasePopulator != null, "DatabasePopulator must be set");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
    boolean existingData;
    try {
        existingData = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM QRTZ_SCHEDULER_STATE", Integer.class) > 0;
    } catch (BadSqlGrammarException e) {
        LOG.debug("Could not access to table QRTZ_SCHEDULER_STATE", e);
        existingData = false;
    }

    if (existingData) {
        LOG.info("Quartz tables found in the database, leaving untouched");
    } else {
        LOG.info("No Quartz tables found, creating");

        DatabasePopulatorUtils.execute(databasePopulator, this.dataSource);
    }
}
 

@Override
public String findByDocId(String productName, String version, String component, String locale,
		JdbcTemplate jdbcTemplate) {
	// TODO Auto-generated method stub
	String sql = "select v.messages::text from vip_msg v where v.product = ? and v.version = ? and v.component= ? and v.locale = ?";

	logger.debug(((DruidDataSource) (jdbcTemplate.getDataSource())).getName());
	logger.debug(sql);
	String[] params = { productName, version, component, locale };
	logger.debug(String.join(", ", params));
	String resultjson = null;

	try {
		resultjson = jdbcTemplate.queryForObject(sql, params, String.class);

	} catch (EmptyResultDataAccessException empty) {
		
		logger.error(empty.getMessage(), empty);
	}

	if (resultjson != null) {
		resultjson = "{ \"component\" : \"" + component + "\", \"messages\" : " + resultjson + ", \"locale\" : \""
				+ locale + "\" }";
	}
	return resultjson;
}
 

@Test
public void testConfigurationProperties() throws Exception {
    assertThat(dataSource.unwrap(PGSimpleDataSource.class).getProperty("stringtype")).isEqualTo("unspecified");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    String collate = jdbcTemplate.queryForObject("show lc_collate", String.class);
    assertThat(collate).isEqualTo("cs_CZ.UTF-8");

    String maxConnections = jdbcTemplate.queryForObject("show max_connections", String.class);
    assertThat(maxConnections).isEqualTo("100");

    String sharedBuffers = jdbcTemplate.queryForObject("show shared_buffers", String.class);
    assertThat(sharedBuffers).isEqualTo("64MB");
}
 

/**
 * 校验目标节点是否可以回退.
 */
public boolean validateTargetActivity(String targetActivityId) {
    JdbcTemplate jdbcTemplate = ApplicationContextHelper
            .getBean(JdbcTemplate.class);
    String historyTaskId = jdbcTemplate
            .queryForObject(
                    "select id_ from ACT_HI_TASKINST where act_id_=? order by END_TIME_ desc",
                    String.class, targetActivityId);

    // 先找到历史任务
    HistoricTaskInstanceEntity historicTaskInstanceEntity = Context
            .getCommandContext().getHistoricTaskInstanceEntityManager()
            .findHistoricTaskInstanceById(historyTaskId);

    // 再反向查找历史任务对应的历史节点
    HistoricActivityInstanceEntity historicActivityInstanceEntity = getHistoricActivityInstanceEntity(historyTaskId);

    logger.info("historic activity instance is : {}",
            historicActivityInstanceEntity.getId());

    Graph graph = new ActivitiHistoryGraphBuilder(
            historicTaskInstanceEntity.getProcessInstanceId()).build();

    Node node = graph.findById(historicActivityInstanceEntity.getId());

    if (!this.checkCouldRollback(node)) {
        logger.info("cannot rollback {}", taskId);

        return false;
    }

    return true;
}
 

@Test
public void testDataSource() throws SQLException {
    assertThat(dataSource.unwrap(PGSimpleDataSource.class).getPassword()).isEqualTo("docker-postgis");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    String postgresVersion = jdbcTemplate.queryForObject("show server_version", String.class);
    assertThat(postgresVersion).startsWith("9.6.");

    jdbcTemplate.update("create extension postgis");
    String postgisVersion = jdbcTemplate.queryForObject("select postgis_version()", String.class);
    assertThat(postgisVersion).startsWith("2.5");
}
 

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);
}
 

@Bean
public UserDetailsService userDetailsService(JdbcTemplate jdbcTemplate) {
	RowMapper<User> userRowMapper = (ResultSet rs, int i) ->
		new User(
			rs.getString("ACCOUNT_NAME"),
			rs.getString("PASSWORD"),
			rs.getBoolean("ENABLED"),
			rs.getBoolean("ENABLED"),
			rs.getBoolean("ENABLED"), 
			rs.getBoolean("ENABLED"),
			AuthorityUtils.createAuthorityList("ROLE_USER", "ROLE_ADMIN"));
	return username ->
		jdbcTemplate.queryForObject("SELECT * from ACCOUNT where ACCOUNT_NAME = ?",
				userRowMapper, username);
}
 

@Test
public void testCreateJob() throws Exception {
	JdbcTemplate db = new JdbcTemplate(dataSource);
	int count =
			db.queryForObject("SELECT COUNT(*) FROM SQOOP_SESSIONS WHERE JOB_NAME = 'newtest'", Integer.class);
	assertTrue("Metadata rows should have been created", count > 50);
}
 

@Bean
public UserDetailsService userDetailsService(JdbcTemplate jdbcTemplate) {
	RowMapper<User> userRowMapper = (ResultSet rs, int i) ->
		new User(
			rs.getString("ACCOUNT_NAME"),
			rs.getString("PASSWORD"),
			rs.getBoolean("ENABLED"),
			rs.getBoolean("ENABLED"),
			rs.getBoolean("ENABLED"), 
			rs.getBoolean("ENABLED"),
			AuthorityUtils.createAuthorityList("ROLE_USER", "ROLE_ADMIN"));
	return username ->
		jdbcTemplate.queryForObject("SELECT * from ACCOUNT where ACCOUNT_NAME = ?",
				userRowMapper, username);
}
 

public int queryObjJoinCountByCondition(String sql,Object[] paramArray){
	/*		JdbcTemplate jdbcTemplate = (JdbcTemplate) MicroDbHolder
	.getDbSource(dbName);*/
	JdbcTemplate jdbcTemplate = getMicroJdbcTemplate();
	logger.debug(sql);
	logger.debug(Arrays.toString(paramArray));
	Integer total=jdbcTemplate.queryForObject(sql,Integer.class,paramArray);
	return total;
}
 

@Test
public void testDataSource() throws SQLException {
    assertThat(dataSource.unwrap(PGSimpleDataSource.class).getPassword()).isEqualTo("docker-postgres");

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String version = jdbcTemplate.queryForObject("show server_version", String.class);
    assertThat(version).startsWith("9.6.");
}
 

private DatabaseInstance(DatabaseConfig config) throws IOException {
    EmbeddedPostgres.Builder builder = EmbeddedPostgres.builder();
    config.applyTo(builder);

    postgres = builder.start();

    DataSource dataSource = postgres.getDatabase("postgres", "postgres");
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    Integer maxConnections = jdbcTemplate.queryForObject("show max_connections", Integer.class);

    semaphore = new Semaphore(maxConnections);
}
 

@Test(expected = BadSqlGrammarException.class)
public void whenIncorrectSql_thenBadSqlGrammarException() {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(restDataSource);

    jdbcTemplate.queryForObject("select * fro foo where id=3", Integer.class);
}
 

@Override
protected AuthenticationHandlerExecutionResult doAuthentication(Credential credential) throws GeneralSecurityException, PreventedException {

    CustomCredential customCredential = (CustomCredential) credential;

    String username = customCredential.getUsername();
    String password = customCredential.getPassword();
    String email = customCredential.getEmail();
    String telephone = customCredential.getTelephone();
    String capcha = customCredential.getCapcha();

    ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    String right = attributes.getRequest().getSession().getAttribute("captcha_code").toString();


    if (!capcha.equalsIgnoreCase(right)) {
        throw new CheckCodeErrorException();
    }

    // 添加邮箱和电话的判断逻辑


    System.out.println("username : " + username);
    System.out.println("password : " + password);
    System.out.println("email : " + email);
    System.out.println("telephone : " + telephone);
    System.out.println("capcha : " + capcha);
    System.out.println("right : " + right);


    // JDBC模板依赖于连接池来获得数据的连接，所以必须先要构造连接池
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/cas");
    dataSource.setUsername("root");
    dataSource.setPassword("123");

    // 创建JDBC模板
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);

    String sql = "SELECT * FROM user WHERE username = ?";

    User info = (User) jdbcTemplate.queryForObject(sql, new Object[]{username}, new BeanPropertyRowMapper(User.class));

    System.out.println("database username : " + info.getUsername());
    System.out.println("database password : " + info.getPassword());


    if (info == null) {
        throw new AccountException("Sorry, username not found!");
    }

    if (!info.getPassword().equals(password)) {
        throw new FailedLoginException("Sorry, password not correct!");
    } else {

        final List<MessageDescriptor> list = new ArrayList<>();
        // 可自定义返回给客户端的多个属性信息
        HashMap<String, Object> returnInfo = new HashMap<>();
        returnInfo.put("expired", info.getDisabled());
        returnInfo.put("email", info.getEmail());
        returnInfo.put("username", info.getUsername());
        returnInfo.put("password", info.getPassword());
        returnInfo.put("disabled", info.getDisabled());

        return createHandlerResult(customCredential,
                this.principalFactory.createPrincipal(username, returnInfo), list);
    }


}
 

@Override
protected AuthenticationHandlerExecutionResult doAuthentication(Credential credential) throws GeneralSecurityException, PreventedException {

    CustomCredential customCredential = (CustomCredential) credential;

    String username = customCredential.getUsername();
    String password = customCredential.getPassword();
    String email = customCredential.getEmail();
    String telephone = customCredential.getTelephone();
    String capcha = customCredential.getCapcha();

    ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    String right = attributes.getRequest().getSession().getAttribute("captcha_code").toString();


    if (!capcha.equalsIgnoreCase(right)) {
        throw new CheckCodeErrorException();
    }

    // 添加邮箱和电话的判断逻辑


    System.out.println("username : " + username);
    System.out.println("password : " + password);
    System.out.println("email : " + email);
    System.out.println("telephone : " + telephone);
    System.out.println("capcha : " + capcha);
    System.out.println("right : " + right);


    // JDBC模板依赖于连接池来获得数据的连接，所以必须先要构造连接池
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/cas");
    dataSource.setUsername("root");
    dataSource.setPassword("123");

    // 创建JDBC模板
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);

    String sql = "SELECT * FROM user WHERE username = ?";

    User info = (User) jdbcTemplate.queryForObject(sql, new Object[]{username}, new BeanPropertyRowMapper(User.class));

    System.out.println("database username : " + info.getUsername());
    System.out.println("database password : " + info.getPassword());


    if (info == null) {
        throw new AccountException("Sorry, username not found!");
    }

    if (!info.getPassword().equals(password)) {
        throw new FailedLoginException("Sorry, password not correct!");
    } else {

        final List<MessageDescriptor> list = new ArrayList<>();
        // 可自定义返回给客户端的多个属性信息
        HashMap<String, Object> returnInfo = new HashMap<>();
        returnInfo.put("expired", info.getDisabled());
        returnInfo.put("email", info.getEmail());
        returnInfo.put("username", info.getUsername());
        returnInfo.put("password", info.getPassword());
        returnInfo.put("disabled", info.getDisabled());

        return createHandlerResult(customCredential,
                this.principalFactory.createPrincipal(username, returnInfo), list);
    }


}
 

@Test(expected = DataRetrievalFailureException.class)
public void whenRetrievingNonExistentValue_thenDataRetrievalException() {
    final JdbcTemplate jdbcTemplate = new JdbcTemplate(restDataSource);

    jdbcTemplate.queryForObject("select * from foo where id=3", Integer.class);
}
 

/**
 * Count the rows in the given table, using the provided {@code WHERE} clause.
 * <p>If the provided {@code WHERE} clause contains text, it will be prefixed
 * with {@code " WHERE "} and then appended to the generated {@code SELECT}
 * statement. For example, if the provided table name is {@code "person"} and
 * the provided where clause is {@code "name = 'Bob' and age > 25"}, the
 * resulting SQL statement to execute will be
 * {@code "SELECT COUNT(0) FROM person WHERE name = 'Bob' and age > 25"}.
 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
 * @param tableName the name of the table to count rows in
 * @param whereClause the {@code WHERE} clause to append to the query
 * @return the number of rows in the table that match the provided
 * {@code WHERE} clause
 */
public static int countRowsInTableWhere(JdbcTemplate jdbcTemplate, String tableName, String whereClause) {
	String sql = "SELECT COUNT(0) FROM " + tableName;
	if (StringUtils.hasText(whereClause)) {
		sql += " WHERE " + whereClause;
	}
	Integer result = jdbcTemplate.queryForObject(sql, Integer.class);
	return (result != null ? result : 0);
}
 

/**
     * 利用 spring JdbcTemplate 进行分页查询
     * 分页需要进行两次查询
     * 排序条件不在这里维护，占位符不能用于 order by
     *
     * @param jdbcTemplate   注入 JdbcTemplate 方法
     *                       //@Autowired JdbcTemplate jdbcTemplate;
     *                       -
     *                       如果是多数据源，并且配置时数据源设置了　 @Primary 指向需要连接的数据库，仅需要
     *                       //@Autowired JdbcTemplate jdbcTemplate;
     *                       -
     *                       如果是多数据源，配置时数据源没有设置 @Primary
     *                       //@Autowired //@Qualifier("oracleDataSource") JdbcTemplate jdbcTemplate; 需要指定数据源名称
     * @param dbDialect      数据库类型，不同的数据库生成的分页语句不一样
     * @param queryNativeSql 本地查询条件，和不分页时相同，如
     *                       select * from standard st where st.namecn like '%中国%' ,
     *                       select st.id,st.name from standard st where st.id ='239711'  等
     * @param queryArgs      arguments to bind to the query ，查询时，绑定在 queryNativeSql 上的参数，按照位置对应
     *                       (leaving it to the PreparedStatement to guess the corresponding SQL type ，是 Object 类型，系统可以自动匹配成 sql 类型)
     *                       无参数时，传入 new Objects[]{} 空数组即可
     *                       占位符方式，可以避免 sql 注入  Queries with Named Parameters
     * @param countNativeSql 本地计算总数的语句
     * @param countArgs      计算总数时，绑定 countNativeSql 上的参数
     * @param currentPageNo  当前页码，从 1 开始
     * @param pageSize       页大小
     * @param rowMapper      自定义返回值
     * @param <T>
     * @return
     */
    private static <T> PageBean<T> queryPageByNativeSqlString(final JdbcTemplate jdbcTemplate, final SqlUtils.Dialect dbDialect,
                                                              final String queryNativeSql, Object[] queryArgs,
                                                              final String countNativeSql, Object[] countArgs,
                                                              int currentPageNo, int pageSize, RowMapper<T> rowMapper) {

        Assert.isTrue(currentPageNo >= 1, "currentPageNo : 起始页不应小于 1 ，且从 1 开始。");
        Assert.isTrue(pageSize >= 0, "pageSize : 页大小不能小于 0");
        Assert.isTrue(countNativeSql.contains("count"), "queryNativeSql 和 countNativeSql 参数顺序不对");


        String queryNativeSqlString = SqlUtils.createNativePageSqlString(dbDialect, queryNativeSql, currentPageNo, pageSize);

        log.info("countNativeSql : \n {} ", countNativeSql);
        log.info("queryPageNativeSql : \n {} ", queryNativeSqlString);

        // 计算总数
        final int totalRecordsSize = jdbcTemplate.queryForObject(countNativeSql, countArgs, Integer.class);
//        log.info("totalRecordsSize : " + totalRecordsSize);
        if (totalRecordsSize == 0)
            return new PageBean(pageSize, 0 + 1, 0, Collections.EMPTY_LIST); //currentPageNo 从 1 开始

        //不能用 queryForList(java.lang.String sql, java.lang.Class<T> elementType)
        // 他的参数 elementType 只能是简单类型 String.class,Integer.class ，不能是一个bean
        List<T> content = jdbcTemplate.query(queryNativeSqlString, queryArgs, rowMapper);

        return new PageBean(pageSize, currentPageNo, totalRecordsSize, content);
    }
 

@Override
protected AuthenticationHandlerExecutionResult authenticateUsernamePasswordInternal(UsernamePasswordCredential usernamePasswordCredential, String s) throws GeneralSecurityException, PreventedException {

    String username = usernamePasswordCredential.getUsername();

    String password = usernamePasswordCredential.getPassword();

    System.out.println("username : " + username);
    System.out.println("password : " + password);


    // JDBC模板依赖于连接池来获得数据的连接，所以必须先要构造连接池
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/cas");
    dataSource.setUsername("root");
    dataSource.setPassword("123");

    // 创建JDBC模板
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);

    String sql = "SELECT * FROM user WHERE username = ?";

    User info = (User) jdbcTemplate.queryForObject(sql, new Object[]{username}, new BeanPropertyRowMapper(User.class));

    System.out.println("database username : "+ info.getUsername());
    System.out.println("database password : "+ info.getPassword());



    if (info == null) {
        throw new AccountException("Sorry, username not found!");
    }

    if (!info.getPassword().equals(password)) {
        throw new FailedLoginException("Sorry, password not correct!");
    } else {

        // 可自定义返回给客户端的多个属性信息
        HashMap<String, Object> returnInfo = new HashMap<>();
        returnInfo.put("expired", info.getDisabled());

        final List<MessageDescriptor> list = new ArrayList<>();

        return createHandlerResult(usernamePasswordCredential,
                this.principalFactory.createPrincipal(username, returnInfo), list);
    }
}
 

@Override
protected AuthenticationHandlerExecutionResult authenticateUsernamePasswordInternal(UsernamePasswordCredential usernamePasswordCredential, String s) throws GeneralSecurityException, PreventedException {

    String username = usernamePasswordCredential.getUsername();

    String password = usernamePasswordCredential.getPassword();

    System.out.println("username : " + username);
    System.out.println("password : " + password);


    // JDBC模板依赖于连接池来获得数据的连接，所以必须先要构造连接池
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost:3306/cas");
    dataSource.setUsername("root");
    dataSource.setPassword("123");

    // 创建JDBC模板
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    jdbcTemplate.setDataSource(dataSource);

    String sql = "SELECT * FROM user WHERE username = ?";

    User info = (User) jdbcTemplate.queryForObject(sql, new Object[]{username}, new BeanPropertyRowMapper(User.class));

    System.out.println("database username : "+ info.getUsername());
    System.out.println("database password : "+ info.getPassword());



    if (info == null) {
        throw new AccountException("Sorry, username not found!");
    }

    if (!info.getPassword().equals(password)) {
        throw new FailedLoginException("Sorry, password not correct!");
    } else {

        // 可自定义返回给客户端的多个属性信息
        HashMap<String, Object> returnInfo = new HashMap<>();
        returnInfo.put("expired", info.getDisabled());

        final List<MessageDescriptor> list = new ArrayList<>();

        return createHandlerResult(usernamePasswordCredential,
                this.principalFactory.createPrincipal(username, returnInfo), list);
    }
}