Java Examples for org.springframework.jdbc.core.RowMapper

private void readTokenContent(Long textId, List<JuxtaAnnotation> annotations) {
    try {
        if (annotations.size() == 0) {
            return;
        }
        // get a reader for the text content associated with the annotations
        final String sql = "select content from text_content where id=?";
        Reader txtReader = DataAccessUtils.uniqueResult(this.jdbcTemplate.query(sql, new RowMapper<Reader>() {

            @Override
            public Reader mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getCharacterStream("content");
            }
        }, textId));
        // pick the first non-gap annotation
        Iterator<JuxtaAnnotation> itr = annotations.iterator();
        JuxtaAnnotation currAnno = null;
        while (itr.hasNext()) {
            JuxtaAnnotation a = itr.next();
            if (a.getRange().length() > 0) {
                currAnno = a;
                break;
            }
        }
        if (currAnno == null) {
            // all gaps? nothin to do
            return;
        }
        // fill in the content for all non-gap annotations
        int pos = 0;
        StringBuilder currToken = new StringBuilder();
        while (true) {
            int data = txtReader.read();
            if (data == -1) {
                currAnno.setContent(currToken.toString());
                break;
            } else {
                if (pos >= currAnno.getRange().getStart() && pos < currAnno.getRange().getEnd()) {
                    currToken.append((char) data);
                    if ((pos + 1) == currAnno.getRange().getEnd()) {
                        //System.err.println("["+currToken.toString()+"] len " +currToken.length());
                        currAnno.setContent(currToken.toString());
                        currAnno = null;
                        while (itr.hasNext()) {
                            JuxtaAnnotation test = itr.next();
                            if (test.getRange().length() > 0) {
                                currAnno = test;
                                currToken = new StringBuilder();
                                break;
                            }
                        }
                        if (currAnno == null) {
                            break;
                        }
                    }
                }
                pos++;
            }
        }
    } catch (IOException e) {
        throw new DataAccessResourceFailureException("Unable to retrieve annotation content", e);
    }
}

/**
	 * Method to perform the actual compilation.  Subclasses can override this template method to perform
	 * their own compilation.  Invoked after this base class's compilation is complete.
	 */
protected void compileInternal() {
    this.callMetaDataContext.initializeMetaData(getJdbcTemplate().getDataSource());
    // iterate over the declared RowMappers and register the corresponding SqlParameter
    for (Map.Entry<String, RowMapper<?>> entry : this.declaredRowMappers.entrySet()) {
        SqlParameter resultSetParameter = this.callMetaDataContext.createReturnResultSetParameter(entry.getKey(), entry.getValue());
        this.declaredParameters.add(resultSetParameter);
    }
    this.callMetaDataContext.processParameters(this.declaredParameters);
    this.callString = this.callMetaDataContext.createCallString();
    if (logger.isDebugEnabled()) {
        logger.debug("Compiled stored procedure. Call string is [" + this.callString + "]");
    }
    this.callableStatementFactory = new CallableStatementCreatorFactory(getCallString(), this.callMetaDataContext.getCallParameters());
    this.callableStatementFactory.setNativeJdbcExtractor(getJdbcTemplate().getNativeJdbcExtractor());
    onCompileInternal();
}

/**
	 * @see org.springframework.beans.factory.dynamic.persist.AbstractPersistenceStoreRefreshableTargetSource#loadFromPersistentStore()
	 */
protected Object loadFromPersistentStore() {
    List l = jdbcTemplate.query(sql, new Object[] { new Long(getPrimaryKey()) }, new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            return AbstractJdbcTargetSource.this.mapRow(rs);
        }
    });
    return DataAccessUtils.requiredUniqueResult(l);
}

@Override
public Managers getUserById(int userId) {
    String sql = "select id,username,nickname,password,salt,enabled,registerTime from Managers where id=" + userId;
    return userNameJdbcTemplate.getJdbcOperations().queryForObject(sql, new RowMapper<Managers>() {

        @Override
        public Managers mapRow(ResultSet rs, int arg1) throws SQLException {
            Managers user = new Managers();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setNickname(rs.getString("nickname"));
            user.setPassword(rs.getString("password"));
            user.setSalt(rs.getString("salt"));
            user.setEnabled(rs.getBoolean("enabled"));
            user.setRegisterTime(rs.getDate("registerTime"));
            return user;
        }
    });
}

@Test
public void testMappingRow() throws Exception {
    Person expected = new Person("John", "Doe");
    Map<String, Object> data = new HashMap<String, Object>();
    data.put("first_name", expected.getFirstname());
    data.put("last_name", expected.getLastname());
    MockSingleRowResultSet rs = new MockSingleRowResultSet();
    rs.addExpectedNamedValues(data);
    RowMapper mapper = new PersonRowMapper();
    assertEquals(expected, mapper.mapRow(rs, 1));
}

public List getOrdersByUsername(String paramString) {
    String str = "select * from orders where userid='" + paramString + "'";
    if (this.log.isInfoEnabled())
        this.log.info("sql:" + str);
    RowMapper local1 = new RowMapper() {

        public Object mapRow(ResultSet paramResultSet, int paramInt) throws SQLException {
            Order localOrder = new Order();
            localOrder.setOrderId(paramResultSet.getInt("orderid"));
            localOrder.setUsername(paramResultSet.getString("userId"));
            localOrder.setOrderDate(paramResultSet.getDate("orderdate"));
            localOrder.setShipAddress1(paramResultSet.getString("shipaddr1"));
            localOrder.setShipAddress2(paramResultSet.getString("shipaddr2"));
            localOrder.setShipCity(paramResultSet.getString("shipcity"));
            localOrder.setShipState(paramResultSet.getString("shipstate"));
            localOrder.setShipZip(paramResultSet.getString("shipzip"));
            localOrder.setShipCountry(paramResultSet.getString("shipcountry"));
            localOrder.setCreditCard(paramResultSet.getString("creditcard"));
            localOrder.setExpiryDate(paramResultSet.getString("exprdate"));
            localOrder.setCardType(paramResultSet.getString("cardtype"));
            return localOrder;
        }
    };
    return (List) this.jdbcTemplate.query(str, new RowMapperResultSetExtractor(local1));
}

private RowMapper<Cep> buildCep() {
    return new RowMapper<Cep>() {

        public Cep mapRow(ResultSet rs, int rowNum) throws SQLException {
            Cep cep = new Cep();
            cep.setCep(rs.getString("cep"));
            cep.setEndereco(rs.getString("endereco"));
            cep.setLogradouro(rs.getString("logradouro"));
            cep.setBairro(rs.getString("bairro"));
            cep.setCidade(rs.getString("cidade"));
            cep.setUf(rs.getString("uf"));
            return cep;
        }
    };
}

@Override
public RowMapper<Address> addressRowMapper() {
    return new RowMapper<Address>() {

        @Override
        public Address mapRow(ResultSet resultSet, int i) throws SQLException {
            Object longitude = resultSet.getObject("longitude"), latitude = resultSet.getObject("latitude");
            return new Address(resultSet.getString("street"), resultSet.getString("city"), resultSet.getString("state"), resultSet.getString("postal_code"), resultSet.getString("country"), longitude == null ? null : (Double) longitude, latitude == null ? null : (Double) latitude);
        }
    };
}

private RowMapper<Conference> rowMapper() {
    return new RowMapper<Conference>() {

        @Override
        public Conference mapRow(ResultSet rs, int rowNum) throws SQLException {
            Conference conf = new Conference();
            conf.setId(rs.getLong("id"));
            conf.setName(rs.getString("name"));
            return conf;
        }
    };
}

public List<State> findAll() {
    return this.jdbcTemplate.query("select * from current_states", new RowMapper<State>() {

        public State mapRow(ResultSet rs, int rowNum) throws SQLException {
            State s = new State();
            s.setId(rs.getLong("id"));
            s.setStateCode(rs.getString("state_code"));
            s.setName(rs.getString("name"));
            return s;
        }
    });
}

@Override
@Transactional(readOnly = true)
public List<Person> all() {
    return this.jdbcTemplate.query("SELECT * FROM Person", new RowMapper<Person>() {

        @Override
        public Person mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            return new Person(resultSet.getString("firstName"), resultSet.getString("lastName"));
        }
    });
}

public void dumpDbInfo() {
    try {
        Connection c = jdbcTemplate.getDataSource().getConnection();
        DatabaseMetaData md = c.getMetaData();
        ResultSet rs = md.getTables(null, null, "%", null);
        while (rs.next()) {
            if (rs.getString(4).equalsIgnoreCase("TABLE")) {
                LOG.debug("TABLE NAME = " + rs.getString(3) + ", Cat = " + rs.getString(1) + ", Schema = " + rs.getString(2) + ", Type = " + rs.getString(4));
                String tableName = rs.getString(3);
                List<String> sl = jdbcTemplate.query("select * from " + tableName, new RowMapper<String>() {

                    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                        StringBuffer sb = new StringBuffer();
                        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                            sb.append(rs.getString(i)).append(' ');
                        }
                        return sb.toString();
                    }
                });
                LOG.debug("No of rows: {}", sl.size());
                for (String s : sl) {
                    LOG.debug(s);
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

@Bean
UserDetailsService userDetailsService(JdbcTemplate jdbcTemplate) {
    // @formatter:off
    RowMapper<User> userDetailsRowMapper = ( rs,  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 = ?", userDetailsRowMapper, username);
// @formatter:on
}

/**
     * 根�项目主键查询所有任务
     *
     * @param iD
     * @return
     */
public List<GenTaskByTableViewSp> getTasksByProjectId(int iD) {
    try {
        return this.jdbcTemplate.query("SELECT id, project_id,db_name,table_names,view_names,sp_names,prefix,suffix," + "cud_by_sp,pagination,`generated`,version,update_user_no,update_time," + "comment,sql_style,api_list,approved,approveMsg FROM task_table " + "WHERE project_id=?", new Object[] { iD }, new RowMapper<GenTaskByTableViewSp>() {

            public GenTaskByTableViewSp mapRow(ResultSet rs, int rowNum) throws SQLException {
                return GenTaskByTableViewSp.visitRow(rs);
            }
        });
    } catch (DataAccessException ex) {
        ex.printStackTrace();
        return null;
    }
}

public ExportData getData(String sql, Object[] params, final List<String> ignoreParameters) {
    final List<String> headers = new ArrayList<String>();
    List<List<Object>> ret = getJdbcTemplate().query(sql, params, new RowMapper() {

        public Object mapRow(ResultSet resultSet, int i) throws SQLException {
            List<Object> data = new ArrayList<Object>();
            boolean addHeader = headers.isEmpty();
            //kolumny sa chyba od 1 ;)
            for (int j = 1; j <= resultSet.getMetaData().getColumnCount(); j++) {
                if (ignoreParameters.contains(resultSet.getMetaData().getColumnName(j))) {
                    continue;
                }
                data.add(resultSet.getObject(j));
                if (addHeader) {
                    headers.add(resultSet.getMetaData().getColumnName(j));
                }
            }
            return data;
        }
    });
    return new ExportData(ret, headers);
}

public Account findAccountByUsername(String username) {
    return jdbcTemplate.queryForObject("select username, firstName, lastName from Account where username = ?", new RowMapper<Account>() {

        public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Account(rs.getString("username"), null, rs.getString("firstName"), rs.getString("lastName"));
        }
    }, username);
}

@Override
public Collection<TeamServiceProvider> forTeam(String teamId) {
    return jdbcTemplate.query("select sp_entity_id, team_id from service_provider_group where team_id = ?", new String[] { teamId }, new RowMapper<TeamServiceProvider>() {

        @Override
        public TeamServiceProvider mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new TeamServiceProvider(rs.getString("sp_entity_id"), rs.getString("team_id"));
        }
    });
}

@Override
protected RowMapper<AffinityGroup> createEntityRowMapper() {
    return ( rs,  rowNum) -> {
        AffinityGroup affinityGroup = new AffinityGroup();
        affinityGroup.setId(getGuid(rs, "id"));
        affinityGroup.setName(rs.getString("name"));
        affinityGroup.setDescription(rs.getString("description"));
        affinityGroup.setClusterId(getGuid(rs, "cluster_id"));
        affinityGroup.setVmEnforcing(rs.getBoolean("vm_enforcing"));
        affinityGroup.setVdsEnforcing(rs.getBoolean("vds_enforcing"));
        if (rs.getBoolean("vds_positive")) {
            affinityGroup.setVdsAffinityRule(EntityAffinityRule.POSITIVE);
        } else {
            affinityGroup.setVdsAffinityRule(EntityAffinityRule.NEGATIVE);
        }
        if (rs.getBoolean("vms_affinity_enabled")) {
            if (rs.getBoolean("vm_positive")) {
                affinityGroup.setVmAffinityRule(EntityAffinityRule.POSITIVE);
            } else {
                affinityGroup.setVmAffinityRule(EntityAffinityRule.NEGATIVE);
            }
        } else {
            affinityGroup.setVmAffinityRule(EntityAffinityRule.DISABLED);
        }
        String[] rawUuids = (String[]) rs.getArray("vm_ids").getArray();
        List<String> vms = Arrays.asList(rawUuids);
        rawUuids = (String[]) rs.getArray("vds_ids").getArray();
        List<String> hosts = Arrays.asList(rawUuids);
        List<String> vmNames = Arrays.asList((String[]) rs.getArray("vm_names").getArray());
        List<String> vdsNames = Arrays.asList((String[]) rs.getArray("vds_names").getArray());
        affinityGroup.setVmIds(vms.stream().filter( v -> v != null).map(Guid::new).collect(Collectors.toList()));
        affinityGroup.setVdsIds(hosts.stream().filter( v -> v != null).map(Guid::new).collect(Collectors.toList()));
        affinityGroup.setVmEntityNames(vmNames.stream().filter( v -> v != null).collect(Collectors.toList()));
        affinityGroup.setVdsEntityNames(vdsNames.stream().filter( v -> v != null).collect(Collectors.toList()));
        return affinityGroup;
    };
}

private void collectRelatedTags(List<Long> fragmentIds, final RelatedTags relatedTags) {
    if (fragmentIds.isEmpty())
        return;
    StringBuilder sql = new StringBuilder();
    sql.append("select tag_id, count(tag_id) from tagging");
    sql.append(" where target_type = " + QueryUtils.TAGGING_TARGET_FRAGMENT);
    sql.append(" and target_id in (");
    for (int i = 0; i < fragmentIds.size(); i++) {
        if (i > 0)
            sql.append(", ");
        sql.append(fragmentIds.get(i));
    }
    sql.append(")");
    sql.append(" group by tag_id");
    logger.debug("collectRelatedTags: " + sql.toString());
    getJdbcTemplate().query(sql.toString(), new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            relatedTags.add(rs.getLong(1), rs.getInt(2));
            return null;
        }
    });
}

protected ItemReader<Foo> getItemReader() throws Exception {
    JdbcPagingItemReader<Foo> reader = new JdbcPagingItemReader<Foo>();
    reader.setDataSource(dataSource);
    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    factory.setDataSource(dataSource);
    factory.setSelectClause("select ID, NAME, VALUE");
    factory.setFromClause("from T_FOOS");
    Map<String, Order> sortKeys = new LinkedHashMap<String, Order>();
    sortKeys.put("VALUE", Order.ASCENDING);
    factory.setSortKeys(sortKeys);
    reader.setQueryProvider(factory.getObject());
    reader.setRowMapper(new RowMapper<Foo>() {

        @Override
        public Foo mapRow(ResultSet rs, int i) throws SQLException {
            Foo foo = new Foo();
            foo.setId(rs.getInt(1));
            foo.setName(rs.getString(2));
            foo.setValue(rs.getInt(3));
            return foo;
        }
    });
    reader.setPageSize(pageSize);
    reader.afterPropertiesSet();
    reader.setSaveState(true);
    return reader;
}

@Override
public Iterable<Client> findAll() {
    return this.template.query("select * from client", new RowMapper<Client>() {

        @Override
        public Client mapRow(ResultSet resultSet, int i) throws SQLException {
            Client client = new Client();
            client.setId(resultSet.getString("id"));
            client.setLogin(resultSet.getString("login"));
            client.setPassword(resultSet.getString("password"));
            return client;
        }
    });
}

public Account findAccountByUsername(String username) {
    return jdbcTemplate.queryForObject("select username, firstName, lastName from Account where username = ?", new RowMapper<Account>() {

        public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Account(rs.getString("username"), null, rs.getString("firstName"), rs.getString("lastName"));
        }
    }, username);
}

public Account findAccountByUsername(String username) {
    return jdbcTemplate.queryForObject("select username, firstName, lastName from Account where username = ?", new RowMapper<Account>() {

        public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Account(rs.getString("username"), null, rs.getString("firstName"), rs.getString("lastName"));
        }
    }, username);
}

@Override
public void afterPropertiesSet() throws Exception {
    setRowMapper(new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            StringBuilder builder = new StringBuilder();
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                builder.append(JdbcUtils.getResultSetValue(rs, i, String.class)).append(delimiter);
            }
            return builder.substring(0, builder.length() - delimiter.length());
        }
    });
    super.afterPropertiesSet();
}

@Override
public InputStream findContentsById(int documentId) {
    InputStream contentsStream = jdbcTemplate.queryForObject(FIND_CONTENTS_BY_ID, Collections.singletonMap("documentId", documentId), new RowMapper<InputStream>() {

        public InputStream mapRow(ResultSet rs, int i) throws SQLException {
            InputStream blobStream = lobHandler.getBlobAsBinaryStream(rs, "contents");
            return blobStream;
        }
    });
    return contentsStream;
}

//private JdbcTemplate jdbcTemplate;
public static List<String> giveAllStationNames() {
    System.out.println("Querying for stations names");
    List<String> names = jdbcTemplate.query("select " + PSQLConnection.NAME + " from " + PSQLConnection.STATION, new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString(PSQLConnection.NAME);
        }
    });
    for (String name : names) {
        System.out.println(name);
    }
    return names;
}

@Test
public void testRepairProcessingMessages() {
    msg.setState(MsgStateEnum.PROCESSING);
    msg.setStartProcessTimestamp(msg.getMsgTimestamp());
    messageDao.insert(msg);
    em.flush();
    int msgCount = JdbcTestUtils.countRowsInTable(getJdbcTemplate(), "message");
    assertThat(msgCount, is(1));
    // call repairing
    repairMsgService.repairProcessingMessages();
    em.flush();
    // verify results
    msgCount = JdbcTestUtils.countRowsInTable(getJdbcTemplate(), "message");
    assertThat(msgCount, is(1));
    getJdbcTemplate().query("select * from message", new RowMapper<Message>() {

        @Override
        public Message mapRow(ResultSet rs, int rowNum) throws SQLException {
            // verify row values
            assertThat(rs.getLong("msg_id"), is(1L));
            assertThat((int) rs.getShort("failed_count"), is(1));
            assertThat(rs.getTimestamp("last_update_timestamp"), notNullValue());
            assertThat(MsgStateEnum.valueOf(rs.getString("state")), is(MsgStateEnum.PARTLY_FAILED));
            return new Message();
        }
    });
}

public OAuth2AccessToken getAccessToken(OAuth2ProtectedResourceDetails resource, Authentication authentication) {
    OAuth2AccessToken accessToken = null;
    try {
        accessToken = jdbcTemplate.queryForObject(selectAccessTokenSql, new RowMapper<OAuth2AccessToken>() {

            public OAuth2AccessToken mapRow(ResultSet rs, int rowNum) throws SQLException {
                return SerializationUtils.deserialize(rs.getBytes(2));
            }
        }, keyGenerator.extractKey(resource, authentication));
    } catch (EmptyResultDataAccessException e) {
        if (LOG.isInfoEnabled()) {
            LOG.debug("Failed to find access token for authentication " + authentication);
        }
    }
    return accessToken;
}

protected List<String> runQuery() {
    @SuppressWarnings("unchecked") List<String> results = getNamedParameterJdbcTemplate().query(sql, new BeanPropertySqlParameterSource(this), new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString(0);
        }
    });
    return results;
}

/**
     * Queries the template for a single object, but makes the result optial, it is ok not to find
     * any object in the db
     * 
     * @param sql
     * @param rowMapper
     * @param params
     * @return
     */
public <T> T queryForOptionalObject(String sql, RowMapper<T> rowMapper, Map params) {
    try {
        List<T> results = query(sql, params, rowMapper);
        if (results.size() > 1) {
            throw new IncorrectResultSizeDataAccessException(1, results.size());
        } else if (results.size() == 1) {
            return results.get(0);
        } else {
            return null;
        }
    } catch (DataAccessException e) {
        throw new ParametricDataAccessException(sql, params, e);
    }
}

@Override
public void initialise() {
    String sqlEntities = "SELECT " + datasource.quoteAndPrefix("token") + " FROM " + datasource.quoteAndPrefix("survey_" + sid) + " WHERE " + datasource.quoteAndPrefix("submitdate") + " is not NULL and " + datasource.quoteAndPrefix("token") + " is not NULL";
    JdbcTemplate jdbcTemplate = new JdbcTemplate(datasource.getDataSource());
    List<VariableEntity> entityList = null;
    try {
        entityList = jdbcTemplate.query(sqlEntities, new RowMapper<VariableEntity>() {

            @Override
            public VariableEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
                String entityId = rs.getString("token");
                return new VariableEntityBean(LimesurveyValueTable.PARTICIPANT, entityId);
            }
        });
    } catch (BadSqlGrammarException e) {
        entityList = Lists.newArrayList();
        log.info("survey_{} is probably not active", sid);
    }
    entities = Sets.newHashSet(entityList);
}

public JSONObject bigData() {
    JSONObject jsonObject = new JSONObject();
    //获��价
    String queryAvgPay = "SELECT truncate(sum(work_income_avg)/count(1),0) as value FROM mite_position_final WHERE work_income_avg != 0;";
    List<Integer> listAvgPay = jdbcTemplate.query(queryAvgPay, new RowMapper<Integer>() {

        @Override
        public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
            return resultSet.getInt("value");
        }
    });
    int avg_pay = 0;
    if (listAvgPay.size() >= 1) {
        avg_pay = listAvgPay.get(0);
    }
    jsonObject.put("avg_pay", avg_pay);
    //�地域需求
    String queryCity = "SELECT work_place_city as name,count(1) as value FROM mite_position_final WHERE work_place_city != \"其他\" GROUP BY work_place_city ORDER BY value desc;";
    List<JSONObject> listCity = jdbcTemplate.query(queryCity, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    jsonObject.put("listCity", MiteGovUtils.cutListAndMergeOther(listCity, 15, 0));
    //�学历需求
    String queryEdu = "SELECT edu as name, count(1) as value FROM mite_position_result GROUP BY edu;";
    List<JSONObject> listEdu = jdbcTemplate.query(queryEdu, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    jsonObject.put("listEdu", listEdu);
    //�利待�
    String queryDy = "SELECT company_welfare_tag as name, count(1) as value FROM mite_company_welfare_tag GROUP BY company_welfare_tag ORDER BY value desc;";
    List<JSONObject> listDy = jdbcTemplate.query(queryDy, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    jsonObject.put("listDy", MiteGovUtils.cutListAndMergeOther(listDy, 25, 0));
    //收入
    String queryIncome = "SELECT income as name, count(1) as value FROM mite_position_result GROUP BY income ORDER BY name;";
    List<JSONObject> listIncome = jdbcTemplate.query(queryIncome, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    List<JSONObject> listIn = new ArrayList<>(10);
    for (int i = 0; i < 10; i++) {
        listIn.add(new JSONObject());
    }
    for (JSONObject jsonObject1 : listIncome) {
        String name = jsonObject1.getString("name");
        if (name.equals("0-5K")) {
            listIn.set(0, jsonObject1);
        } else if (name.equals("5K-10K")) {
            listIn.set(1, jsonObject1);
        } else if (name.equals("10K-15K")) {
            listIn.set(2, jsonObject1);
        } else if (name.equals("15K-20K")) {
            listIn.set(3, jsonObject1);
        } else if (name.equals("20K-25K")) {
            listIn.set(4, jsonObject1);
        } else if (name.equals("25K-30K")) {
            listIn.set(5, jsonObject1);
        } else if (name.equals("30K-35K")) {
            listIn.set(6, jsonObject1);
        } else if (name.equals("35K-40K")) {
            listIn.set(7, jsonObject1);
        } else if (name.equals("40K�以上")) {
            listIn.set(8, jsonObject1);
        } else if (name.equals("�议")) {
            listIn.set(9, jsonObject1);
        }
    }
    jsonObject.put("listIn", CollectionsSort.listSortF(listIn));
    //�验
    String queryExp = "SELECT exp as name, count(1) as value FROM mite_position_result GROUP BY exp ORDER BY value DESC;";
    List<JSONObject> listExp = jdbcTemplate.query(queryExp, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    jsonObject.put("listExp", listExp);
    //�业方�
    String queryTech = "SELECT tech as name, count(1) as value FROM mite_position_result GROUP BY tech ORDER BY value DESC;";
    List<JSONObject> listTech = jdbcTemplate.query(queryTech, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    jsonObject.put("listTech", listTech);
    //规模
    String queryScale = "SELECT scale as name, count(1) as value FROM mite_position_result GROUP BY scale ORDER BY value DESC;";
    List<JSONObject> listScaleTmp = jdbcTemplate.query(queryScale, new RowMapper<JSONObject>() {

        @Override
        public JSONObject mapRow(ResultSet resultSet, int i) throws SQLException {
            JSONObject jsonObject1 = new JSONObject();
            jsonObject1.put("name", resultSet.getString("name"));
            jsonObject1.put("value", resultSet.getInt("value"));
            return jsonObject1;
        }
    });
    List<JSONObject> listScale = new ArrayList<>(6);
    for (int i = 0; i < 6; i++) {
        listScale.add(new JSONObject());
    }
    for (JSONObject jsonObject1 : listScaleTmp) {
        String name = jsonObject1.getString("name");
        if (name.equals("未知")) {
            listScale.set(0, jsonObject1);
        } else if (name.equals("0-100人")) {
            listScale.set(1, jsonObject1);
        } else if (name.equals("100-300人")) {
            listScale.set(2, jsonObject1);
        } else if (name.equals("300-1000")) {
            JSONObject jsonObject11 = new JSONObject();
            jsonObject11.put("name", "300-1000人");
            jsonObject11.put("value", jsonObject1.get("value"));
            listScale.set(3, jsonObject11);
        } else if (name.equals("1000-10000人")) {
            listScale.set(4, jsonObject1);
        } else if (name.equals("10000人�以上")) {
            listScale.set(5, jsonObject1);
        }
    }
    jsonObject.put("listScale", CollectionsSort.listSortF(listScale));
    return jsonObject;
}

private int getCountForSql() {
    long t0 = System.currentTimeMillis();
    String sql = sqlDictionary.getSQLQuery(qName);
    //SqlParameterSource namedParams = new MapSqlParameterSource("id", id);
    SqlParameterSource namedParams = null;
    List<Integer> counts = new NamedParameterJdbcTemplate(dsLocator.getDataSource()).query(sql, namedParams, new RowMapper<Integer>() {

        @Override
        public Integer mapRow(ResultSet resultSet, int row) throws SQLException {
            return new Integer(resultSet.getInt("cnt"));
        }
    });
    timeSQL = (int) (System.currentTimeMillis() - t0);
    countSQL = counts.get(0);
    return countSQL;
}

public UserProfile getUserProfile(final String userId) {
    LOG.debug("SQL SELECT ON UserProfile: {}", userId);
    return jdbcTemplate.queryForObject("select * from UserProfile where userId = ?", new RowMapper<UserProfile>() {

        public UserProfile mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new UserProfile(userId, rs.getString("name"), rs.getString("firstName"), rs.getString("lastName"), rs.getString("email"), rs.getString("username"));
        }
    }, userId);
}

/** 
	 * @return 
	 * @see org.spc.ofp.observer.domain.RepositoryImpl#find(java.lang.String, java.util.Map)
	 */
@Override
public <T> T find(final String query, final RowMapper<T> mapper, final Object... args) {
    final SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
    // The query should find a unique record, so the only overhead is messing around with a list
    // that contains either zero or one item.
    final List<T> temp = template.query(query, mapper, args);
    return temp.isEmpty() ? null : temp.get(0);
}

/*
   * (non-Javadoc)
   * 
   * @see
   * nl.surfnet.coin.eb.EngineBlock#getPersistentNameIdentifier(java.lang.String
   * )
   */
@Override
public String getUserUUID(String identifier) {
    Assert.hasText(identifier, "Not allowed to provide a null or empty identifier");
    String sql = "SELECT user_uuid FROM saml_persistent_id WHERE persistent_id =  ?";
    LOG.debug("Executing query with identifier {}: {}", identifier, sql);
    List<String> results = ebJdbcTemplate.query(sql, new String[] { identifier }, new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString(1);
        }
    });
    if (CollectionUtils.isEmpty(results)) {
        throw new RuntimeException("No persistent_id found for user_uuid(" + identifier + ")");
    }
    LOG.debug("Numer of results from query: {}", results.size());
    return results.get(0);
}

private void jooqSql() {
    Query query = this.dsl.select(BOOK.TITLE, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).from(BOOK).join(AUTHOR).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID)).where(BOOK.PUBLISHED_IN.equal(2015));
    Object[] bind = query.getBindValues().toArray(new Object[] {});
    List<String> list = this.jdbc.query(query.getSQL(), bind, new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString(1) + " : " + rs.getString(2) + " " + rs.getString(3);
        }
    });
    System.out.println("jOOQ SQL " + list);
}

@SuppressWarnings("unchecked")
public String getXmlItem(final Long id) {
    List<String> l = jdbcTemplate.query("select xml_text from xml_table where id = ?", new Object[] { id }, new RowMapper() {

        public Object mapRow(ResultSet rs, int i) throws SQLException {
            String s = sqlXmlHandler.getXmlAsString(rs, 1);
            return s;
        }
    });
    if (l.size() > 0) {
        return l.get(0);
    }
    return null;
}

public OAuth2AccessToken getAccessToken(OAuth2ProtectedResourceDetails resource, Authentication authentication) {
    OAuth2AccessToken accessToken = null;
    try {
        accessToken = jdbcTemplate.queryForObject(selectAccessTokenSql, new RowMapper<OAuth2AccessToken>() {

            public OAuth2AccessToken mapRow(ResultSet rs, int rowNum) throws SQLException {
                return SerializationUtils.deserialize(rs.getBytes(2));
            }
        }, keyGenerator.extractKey(resource, authentication));
    } catch (EmptyResultDataAccessException e) {
        if (LOG.isInfoEnabled()) {
            LOG.debug("Failed to find access token for authentication " + authentication);
        }
    }
    return accessToken;
}

public void executeQueryWithPreparedStatementAndRowMapper() {
    template.query("SELECT name, age FROM person WHERE dep = ?",  ps -> ps.setString(1, "Sales"), ( rs,  rowNum) -> new Person(rs.getString(1), rs.getString(2)));
    // VS.
    template.query("SELECT name, age FROM person WHERE dep = ?", new PreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps) throws SQLException {
            ps.setString(1, "Sales");
        }
    }, new RowMapper<Person>() {

        @Override
        public Person mapRow(ResultSet resultSet, int i) throws SQLException {
            return new Person(rs.getString(1), rs.getString(2));
        }
    });
}

@Override
public Iterable<? extends IPortalData> apply(IPortalDataType input) {
    return this.jdbcOperations.query(QUERY, new RowMapper<IPortalData>() {

        @Override
        public IPortalData mapRow(ResultSet rs, int rowNum) throws SQLException {
            final StringBuilder key = new StringBuilder();
            key.append(rs.getString("OWNER")).append("|");
            key.append(rs.getString("ENTITY_TYPE_NAME")).append("|");
            key.append(rs.getString("PRINCIPAL_KEY")).append("|");
            key.append(rs.getString("ACTIVITY")).append("|");
            key.append(rs.getString("PRINCIPAL_TYPE"));
            return new SimpleStringPortalData(key.toString(), null, null);
        }
    });
}

public Account findAccountByUsername(String username) {
    return jdbcTemplate.queryForObject("select username, password, firstName, lastName, role from Account where username = ?", new RowMapper<Account>() {

        public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Account(rs.getString("username"), rs.getString("password"), rs.getString("firstName"), rs.getString("lastName"), rs.getString("role"));
        }
    }, username);
}

/**
     * <p>Retrieves all the stocks from database, using passed {@link JdbcTemplate}.</p>
     *
     * @param jdbcTemplate the jdbc template to use
     *
     * @return list of stocks retrieved from database
     */
public static List<Stock> retrieveAllStocks(JdbcTemplate jdbcTemplate) {
    return jdbcTemplate.query("select id, name, symbol, value, date from Stock order by name", new RowMapper<Stock>() {

        public Stock mapRow(ResultSet rs, int rowNum) throws SQLException {
            int index = 1;
            Stock result = new Stock();
            result.setId(rs.getLong(index++));
            result.setName(rs.getString(index++));
            result.setSymbol(rs.getString(index++));
            result.setValue(rs.getBigDecimal(index++));
            result.setDate(rs.getDate(index++));
            return result;
        }
    });
}

/**
     * <p>Retrieves all the stocks from database, using passed {@link JdbcTemplate}.</p>
     *
     * @param jdbcTemplate the jdbc template to use
     *
     * @return list of stocks retrieved from database
     */
public static List<Stock> retrieveAllStocks(JdbcTemplate jdbcTemplate) {
    return jdbcTemplate.query("select id, name, symbol, value, date from Stock order by name", new RowMapper<Stock>() {

        public Stock mapRow(ResultSet rs, int rowNum) throws SQLException {
            int index = 1;
            Stock result = new Stock();
            result.setId(rs.getLong(index++));
            result.setName(rs.getString(index++));
            result.setSymbol(rs.getString(index++));
            result.setValue(rs.getBigDecimal(index++));
            result.setDate(rs.getDate(index++));
            return result;
        }
    });
}

@Bean
UserDetailsService userDetailsService(JdbcTemplate jdbcTemplate) {
    RowMapper<User> userRowMapper = ( rs,  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 Contact getById(Long id) {
    List<Contact> list = getJdbcTemplate().query("select id, contact_name, email from contacts where id = ? order by id", new RowMapper<Contact>() {

        public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
            return mapContact(rs);
        }
    }, id);
    if (list.size() == 0) {
        return null;
    } else {
        return (Contact) list.get(0);
    }
}

@SuppressWarnings("unchecked")
@Test
public void shouldLoadMetaData() {
    when(mockJdbcTemplate.query(any(String.class), eq(new Object[] { testPortletId, COMMUNITY_ID }), any(RowMapper.class))).thenReturn(Arrays.asList(new ConfigurationMetaData("testuser", new Date(), new Date(), null)));
    ConfigurationMetaData metaData = configurationDao.readConfigMetaData(testPortletId, COMMUNITY_ID);
    assertEquals("testuser", metaData.getUser());
}

public final List<String> getPOINames() {
    class POINamesMapper implements RowMapper<String> {

        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString("name");
        }
    }
    return this.jdbc.query("SELECT name FROM place_of_interest;", new Object[] {}, new POINamesMapper());
}

// internal helpers
private Invite queryForInvite(String token) throws NoSuchInviteException {
    try {
        return jdbcTemplate.queryForObject(SELECT_INVITE, new RowMapper<Invite>() {

            public Invite mapRow(ResultSet rs, int rowNum) throws SQLException {
                Invitee invitee = new Invitee(rs.getString("firstName"), rs.getString("lastName"), rs.getString("email"));
                ProfileReference sentBy = ProfileReference.textOnly(rs.getLong("sentById"), rs.getString("sentByUsername"), rs.getString("sentByFirstName"), rs.getString("sentByLastName"));
                return new Invite(invitee, sentBy, rs.getBoolean("accepted"));
            }
        }, token, token);
    } catch (EmptyResultDataAccessException e) {
        throw new NoSuchInviteException(token);
    }
}

@Override
@Timed
@ExceptionMetered
public List<QueueEntry<T>> getEntries(long since, int max) throws IOException {
    return jdbcTemplate.query("select * from " + tableName + " where id > ? limit " + max, new Object[] { since }, new RowMapper<QueueEntry<T>>() {

        @Override
        public QueueEntry<T> mapRow(ResultSet rs, int row) throws SQLException {
            QueueEntry<T> entry = new QueueEntry<T>();
            entry.setId(rs.getLong("id"));
            entry.setEnqueuedDate(rs.getLong("enqueuedDate"));
            try {
                entry.setPayload(fromJson(rs.getString("payload")));
            } catch (IOException e) {
                throw new SQLException(e);
            }
            return entry;
        }
    });
}

public <T> List<T> query(String sql, Object[] params, RowMapper<T> rowMapper) throws DataAccessException {
    //dynamic change catalog name
    sql = changeCatalog(sql);
    try {
        return jdbcTemplate.query(sql, params, rowMapper);
    } catch (DataAccessException e) {
        StringBuilder sb = new StringBuilder();
        sb.append("[");
        for (Object p : params) {
            sb.append(p + " | ");
        }
        sb.append("]");
        logger.error("Error SQL: " + sql + " Params: " + sb.toString());
        throw e;
    }
}

public Contact getById(Long id) {
    List<Contact> list = getJdbcTemplate().query("select id, contact_name, email from contacts where id = ? order by id", new RowMapper<Contact>() {

        public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
            return mapContact(rs);
        }
    }, id);
    if (list.size() == 0) {
        return null;
    } else {
        return (Contact) list.get(0);
    }
}

@Nonnull
public List<UserLogItem> getLogItems(@Nonnull User user, boolean includeSelf) {
    String sql = includeSelf ? "SELECT id, userid, action_userid, action_date, action, info FROM user_log WHERE userid=? ORDER BY id DESC" : "SELECT id, userid, action_userid, action_date, action, info FROM user_log WHERE userid=? AND userid!=action_userid ORDER BY id DESC";
    return jdbcTemplate.query(sql, new RowMapper<UserLogItem>() {

        @Override
        public UserLogItem mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new UserLogItem(rs.getInt("id"), rs.getInt("userid"), rs.getInt("action_userid"), new DateTime(rs.getTimestamp("action_date")), UserLogAction.valueOf(rs.getString("action").toUpperCase()), (Map<String, String>) rs.getObject("info"));
        }
    }, user.getId());
}

/**
	 * Reads properties from the shared data source.
	 * 
	 * @return Properties Returns properties fetched from the shared database.
	 */
public Properties readPropertiesFromDataSource() {
    properties = new Properties();
    jdbcTemplate.query(PROPERTIES_QUERY_SQL, new RowMapper<Properties>() {

        @Override
        public Properties mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            String key = resultSet.getString("KEY_COLUMN");
            String value = resultSet.getString("VALUE_COLUMN");
            properties.setProperty(key, value);
            return properties;
        }
    });
    return properties;
}

/*
   * (non-Javadoc)
   * 
   * @see
   * nl.surfnet.coin.shared.log.ApiCallLogService#findApiCallLog(java.lang.String
   * )
   */
@Override
public List<ApiCallLog> findApiCallLog(String serviceProvider) {
    return jdbcTemplate.query("select user_id, spentity_id, ip_address, api_version, resource_url, consumer_key, log_timestamp from api_call_log where spentity_id = ?", new String[] { serviceProvider }, new RowMapper<ApiCallLog>() {

        @Override
        public ApiCallLog mapRow(ResultSet rs, int rowNum) throws SQLException {
            ApiCallLog log = new ApiCallLog();
            log.setApiVersion(rs.getString("api_version"));
            log.setConsumerKey(rs.getString("consumer_key"));
            log.setIpAddress(rs.getString("ip_address"));
            log.setResourceUrl(rs.getString("resource_url"));
            log.setSpEntityId(rs.getString("spentity_id"));
            log.setTimestamp(rs.getDate("log_timestamp"));
            log.setUserId(rs.getString("user_id"));
            return log;
        }
    });
}

private List<UpdateRow> getIndexSectorPlaces(Integer rank, Integer geometrySector) {
    return template.query("select place_id, indexed_status from placex where rank_search = ?" + " and geometry_sector = ? and indexed_status > 0;", new Object[] { rank, geometrySector }, new RowMapper<UpdateRow>() {

        @Override
        public UpdateRow mapRow(ResultSet rs, int rowNum) throws SQLException {
            UpdateRow updateRow = new UpdateRow();
            updateRow.setPlaceId(rs.getLong("place_id"));
            updateRow.setIndexdStatus(rs.getInt("indexed_status"));
            return updateRow;
        }
    });
}

private List extractDataList(ResultSet rs, RowMapper rowMapper, int pageNumber, int pageSize) throws SQLException {
    List result = new ArrayList(pageSize);
    if (pageNumber > 1) {
        rs.absolute(((pageNumber - 1) * pageSize));
    }
    int rowNum = 0;
    while (rs.next()) {
        Object row = rowMapper.mapRow(rs, rowNum++);
        result.add(row);
        if (pageSize == (rowNum + 1))
            break;
    }
    return result;
}

@Override
public void execute() {
    if (!isAuditLogIntegrityEnabled()) {
        return;
    }
    RowMapper<VerifyAuditLogEvent> rowMapper = getRowMapper();
    List<VerifyAuditLogEvent> archivedAuditLogEvent = jdbcTemplate.query(SELECT_FROM_SIMBA_ARCHIVED_AUDIT_LOG, rowMapper);
    for (VerifyAuditLogEvent event : archivedAuditLogEvent) {
        String digestToCheck = digester.digest(AuditLogIntegrityMessageFactory.createMessage(event));
        String digestFromDb = event.getDigest();
        if (digestFromDb == null || digestToCheck == null || !digestFromDb.equals(digestToCheck)) {
            throw new AuditLogTamperedException("Archived Audit Log Integrity Check failed: calculated digest is not equal to the saved digest in the database");
        }
    }
    LOG.debug("Verified #" + archivedAuditLogEvent.size() + " archived audit log entries.");
}

/* (non-Javadoc)
	 * @see org.springboot.sample.service.IStudentService#getList()
	 */
@Override
public List<Student> getList() {
    String sql = "SELECT ID,NAME,SCORE_SUM,SCORE_AVG, AGE   FROM STUDENT";
    return (List<Student>) jdbcTemplate.query(sql, new RowMapper<Student>() {

        @Override
        public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
            Student stu = new Student();
            stu.setId(rs.getInt("ID"));
            stu.setAge(rs.getInt("AGE"));
            stu.setName(rs.getString("NAME"));
            stu.setSumScore(rs.getString("SCORE_SUM"));
            stu.setAvgScore(rs.getString("SCORE_AVG"));
            return stu;
        }
    });
}

// internal helpers
private Invite queryForInvite(String token) throws NoSuchInviteException {
    try {
        return jdbcTemplate.queryForObject(SELECT_INVITE, new RowMapper<Invite>() {

            public Invite mapRow(ResultSet rs, int rowNum) throws SQLException {
                Invitee invitee = new Invitee(rs.getString("firstName"), rs.getString("lastName"), rs.getString("email"));
                ProfileReference sentBy = ProfileReference.textOnly(rs.getLong("sentById"), rs.getString("sentByUsername"), rs.getString("sentByFirstName"), rs.getString("sentByLastName"));
                return new Invite(invitee, sentBy, rs.getBoolean("accepted"));
            }
        }, token, token);
    } catch (EmptyResultDataAccessException e) {
        throw new NoSuchInviteException(token);
    }
}

/**
	 * Loads the token data for the supplied series identifier.
	 *
	 * If an error occurs, it will be reported and null will be returned (since the result
	 * should just be a failed persistent login).
	 *
	 * @param seriesId
	 * @return the token matching the series, or null if no match found or an exception
	 * occurred.
	 */
public PersistentRememberMeToken getTokenForSeries(String seriesId) {
    try {
        return getJdbcTemplate().queryForObject(tokensBySeriesSql, new RowMapper<PersistentRememberMeToken>() {

            public PersistentRememberMeToken mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new PersistentRememberMeToken(rs.getString(1), rs.getString(2), rs.getString(3), rs.getTimestamp(4));
            }
        }, seriesId);
    } catch (EmptyResultDataAccessException zeroResults) {
        if (logger.isDebugEnabled()) {
            logger.debug("Querying token for series '" + seriesId + "' returned no results.", zeroResults);
        }
    } catch (IncorrectResultSizeDataAccessException moreThanOne) {
        logger.error("Querying token for series '" + seriesId + "' returned more than one value. Series" + " should be unique");
    } catch (DataAccessException e) {
        logger.error("Failed to load token for series " + seriesId, e);
    }
    return null;
}

public String listDbEntries() {
    List<Order> orders = jdbcTemplate.query("select id, state from orders", new RowMapper<Order>() {

        public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Order(rs.getInt("id"), rs.getString("state"));
        }
    });
    StringBuilder buf = new StringBuilder();
    for (Order order : orders) {
        buf.append(order);
        buf.append("\n");
    }
    return buf.toString();
}

public ProcessInstance findProcessInstance(long processInstId) {
    return this.getJdbcTemplate().queryForObject(findProcessInstanceSQL, new RowMapper<ProcessInstance>() {

        @Override
        public ProcessInstance mapRow(ResultSet resultSet, int index) throws SQLException {
            ProcessInstance processInstance = new ProcessInstance();
            processInstance.setProcessInstId(resultSet.getLong("processInstId"));
            processInstance.setProcessDefId(resultSet.getLong("processDefId"));
            processInstance.setProcessInstName(resultSet.getString("processInstName"));
            processInstance.setCreator(resultSet.getString("creator"));
            processInstance.setCreateTime(resultSet.getDate("createTime"));
            processInstance.setSubFlow(resultSet.getString("subFlow"));
            processInstance.setLimitNum(resultSet.getLong("limitNum"));
            processInstance.setCurrentState(resultSet.getInt("currentState"));
            processInstance.setMainProcInstId(resultSet.getLong("mainProcInstId"));
            processInstance.setParentProcInstId(resultSet.getLong("parentProcInstId"));
            processInstance.setActivityInstId(resultSet.getLong("activityInstId"));
            return processInstance;
        }
    }, processInstId);
}

public void testEmptyCriterias() throws Exception {
    Capture<RowMapper<JsonObject>> mapper = new Capture<RowMapper<JsonObject>>();
    Capture<PreparedStatementCreator> creator = new Capture<PreparedStatementCreator>();
    Map<String, String[]> params = new HashMap<String, String[]>();
    params.put("port", new String[] { "test" });
    JsonObject result = fetchResult(mapper, creator, params);
    @SuppressWarnings("unused") RowMapper<JsonObject> rowMapper = mapper.getValue();
    assertEquals(10, result.get("count").getAsInt());
    JsonArray aggregated = (JsonArray) result.get("aggregated");
    assertEquals(1, aggregated.size());
    JsonObject res = (JsonObject) aggregated.get(0);
    assertEquals(3, res.get("elapsed").getAsInt());
    assertEquals(2, res.get("types").getAsJsonArray().size());
    assertEquals("consumer_host", res.get("consumer_host").getAsString());
    assertEquals("consumer_ip", res.get("consumer_ip").getAsString());
    assertEquals("provider_ip", res.get("provider_ip").getAsString());
    assertEquals("provider_host", res.get("provider_host").getAsString());
    System.err.println(creator.getValue());
}

/**
	 * Query for cursor
	 * 
	 * @param <T>
	 * @param rowMapper
	 * @return a cursor
	 * 
	 * @see SQLiteDatabase#query(String, String[], String, String[], String,
	 *      String, String, String)
	 */
public <T> T queryForObject(RowMapper<T> rowMapper, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
    T object = null;
    final Cursor c = getDb(false).query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    try {
        if (c.moveToFirst()) {
            object = rowMapper.mapRow(c, c.getCount());
        }
    } finally {
        c.close();
    }
    return object;
}

public List query(String sql, Object[] params) {
    return query(sql, params, new RowMapper() {

        public Object mapRow(ResultSet rs, int index) throws SQLException {
            int columnCount = rs.getMetaData().getColumnCount();
            if (columnCount == 1) {
                return rs.getObject(1);
            } else {
                Object[] row = new Object[columnCount];
                for (int i = 0; i < columnCount; i++) {
                    row[i] = rs.getObject(i + 1);
                }
                return row;
            }
        }
    });
}

@Override
protected boolean exceedsThreshold(final HttpServletRequest request) {
    final String query = "SELECT AUD_DATE FROM COM_AUDIT_TRAIL WHERE AUD_CLIENT_IP = ? AND AUD_USER = ? " + "AND AUD_ACTION = ? AND APPLIC_CD = ? AND AUD_DATE >= ? ORDER BY AUD_DATE DESC";
    final String userToUse = constructUsername(request, getUsernameParameter());
    final Calendar cutoff = Calendar.getInstance();
    cutoff.add(Calendar.SECOND, -1 * getFailureRangeInSeconds());
    final List<Timestamp> failures = this.jdbcTemplate.query(query, new Object[] { request.getRemoteAddr(), userToUse, this.authenticationFailureCode, this.applicationCode, cutoff.getTime() }, new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP }, new RowMapper<Timestamp>() {

        @Override
        public Timestamp mapRow(final ResultSet resultSet, final int i) throws SQLException {
            return resultSet.getTimestamp(1);
        }
    });
    if (failures.size() < 2) {
        return false;
    }
    // Compute rate in submissions/sec between last two authn failures and compare with threshold
    return 1000.0 / (failures.get(0).getTime() - failures.get(1).getTime()) > getThresholdRate();
}

@Override
public List<AuditTrailInfo> getAuditTrails(String transactionId, String conversationId, String correlationId, Integer level, int maxResult) {
    SqlPagingQueryProviderFactoryBean factory = new SqlPagingQueryProviderFactoryBean();
    String sortClause = "SEQ_ID";
    String whereClause = "where 1=1 ";
    List<Object> args = new ArrayList<Object>();
    if (level != null) {
        whereClause += " and LOGLEVEL <= ? ";
        sortClause = "LOGLEVEL";
        args.add(level);
    }
    if (StringUtils.hasText(correlationId)) {
        whereClause += " and CORRELATION_ID = ? ";
        sortClause = "CORRELATION_ID";
        args.add(correlationId);
    }
    if (StringUtils.hasText(conversationId)) {
        whereClause += " and CONVERSATION_ID = ? ";
        sortClause = "CONVERSATION_ID";
        args.add(conversationId);
    }
    if (StringUtils.hasText(transactionId)) {
        whereClause += " and TRANSACTION_ID = ? ";
        sortClause = "TRANSACTION_ID";
        args.add(transactionId);
    }
    String selectClause = "select " + "SEQ_ID," + "TRANSACTION_ID," + "CONVERSATION_ID," + "CORRELATION_ID," + "OCCURRENCE," + "LOGLEVEL," + "CONTEXT," + "INSTANCE_ID," + "MESSAGE_TYPE";
    factory.setDataSource(getDataSource());
    factory.setFromClause("from COP_AUDIT_TRAIL_EVENT ");
    factory.setSelectClause(selectClause);
    factory.setWhereClause(whereClause);
    factory.setSortKey(sortClause);
    PagingQueryProvider queryProvider = null;
    try {
        queryProvider = (PagingQueryProvider) factory.getObject();
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
        return null;
    }
    String query = queryProvider.generateFirstPageQuery(maxResult);
    // this.getJdbcTemplate().setQueryTimeout(1000);
    long start = System.currentTimeMillis();
    RowMapper<AuditTrailInfo> rowMapper = new RowMapper<AuditTrailInfo>() {

        public AuditTrailInfo mapRow(ResultSet rs, int arg1) throws SQLException {
            return new AuditTrailInfo(rs.getLong("SEQ_ID"), rs.getString("TRANSACTION_ID"), rs.getString("CONVERSATION_ID"), rs.getString("CORRELATION_ID"), rs.getTimestamp("OCCURRENCE").getTime(), rs.getInt("LOGLEVEL"), rs.getString("CONTEXT"), rs.getString("INSTANCE_ID"), rs.getString("MESSAGE_TYPE"));
        }
    };
    List<AuditTrailInfo> res = this.getJdbcTemplate().query(query, rowMapper, args.toArray());
    long end = System.currentTimeMillis();
    logger.info("query took: " + (end - start) + " ms : " + query);
    return res;
}

///---------------------
protected Subdomain safeQueryForObject(String sql, RowMapper<Subdomain> rowMapper, Object... args) {
    List<Subdomain> results = jdbc.query(sql, args, new RowMapperResultSetExtractor<Subdomain>(rowMapper, 1));
    int size = (results != null ? results.size() : 0);
    if (results.size() > 1) {
        throw new IncorrectResultSizeDataAccessException(1, size);
    }
    return results.iterator().next();
}

/**
	 * Query for cursor
	 * 
	 * @param <T>
	 * @param rowMapper
	 * @return a cursor
	 * 
	 * @see SQLiteDatabase#query(String, String[], String, String[], String,
	 *      String, String, String)
	 */
public <T> T queryForObject(RowMapper<T> rowMapper, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
    T object = null;
    final Cursor c = getDb(false).query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
    try {
        if (c.moveToFirst()) {
            object = rowMapper.mapRow(c, c.getCount());
        }
    } finally {
        c.close();
    }
    return object;
}

@SuppressWarnings("unchecked")
public List<String> findRolsAmbCodi(String codi) throws PersonesPluginException {
    List<String> rols = new ArrayList<String>();
    try {
        String query = GlobalProperties.getInstance().getProperty("app.persones.plugin.jdbc.filter.roles");
        if (query != null && !query.isEmpty()) {
            Map<String, Object> parametres = new HashMap<String, Object>();
            parametres.put("codi", codi);
            String jndi = GlobalProperties.getInstance().getProperty("app.persones.plugin.jdbc.jndi.parameter");
            Context initContext = new InitialContext();
            DataSource ds = (DataSource) initContext.lookup(jndi);
            namedJdbcTemplate = new NamedParameterJdbcTemplate(ds);
            MapSqlParameterSource parameterSource = new MapSqlParameterSource(parametres) {

                public boolean hasValue(String paramName) {
                    return true;
                }
            };
            rols = namedJdbcTemplate.query(query, parameterSource, new RowMapper() {

                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            });
        }
    } catch (Exception ex) {
        throw new PersonesPluginException("No s'ha pogut trobar la persona", ex);
    }
    return rols;
}

/** {@inheritDoc} */
@Override
public Person load(Long key) {
    System.out.println(">>> Store load [key=" + key + ']');
    try {
        return jdbcTemplate.queryForObject("select * from PERSON where id = ?", new RowMapper<Person>() {

            @Override
            public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new Person(rs.getLong(1), rs.getString(2), rs.getString(3));
            }
        }, key);
    } catch (EmptyResultDataAccessException ignored) {
        return null;
    }
}

private RowMapper getRowMapper(SProcCall scA) {
    if (scA.resultMapper() != Void.class) {
        try {
            return (RowMapper<?>) scA.resultMapper().newInstance();
        } catch (final InstantiationExceptionIllegalAccessException |  ex) {
            LOG.error("Result mapper for sproc can not be instantiated", ex);
            throw new IllegalArgumentException("Result mapper for sproc can not be instantiated");
        }
    }
    return null;
}

@Override
protected <T> Optional<T> doQueryForOptional(String sql, final JdbcResultSetRowMapper<T> rowMapper, Object[] objects) {
    try {
        T object = jdbcTemplate.queryForObject(sql, new RowMapper<T>() {

            @Override
            public T mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rowMapper.mapRow(rs, rowNum);
            }
        }, objects);
        return Optional.of(object);
    } catch (EmptyResultDataAccessException e) {
        return Optional.absent();
    }
}

public List<IInstance> getInstances(final DataSet ds) throws IOException {
    IDataSource dataSrc = ds.getDataSource();
    List<IInstance> result = new ArrayList<IInstance>();
    if (dataSrc instanceof JDBCRow) {
        JDBCRow row = (JDBCRow) dataSrc;
        row.setTemplate(findTemplate(row));
        result.add(row);
    } else if (dataSrc instanceof JDBCDataSourceWrapper) {
        String sqlQuery = getSql();
        if (sqlQuery == null) {
            sqlQuery = ((JDBCDataSourceWrapper) dataSrc).getQuery();
        }
        this.jdbcTemplate = ((JDBCDataSourceWrapper) dataSrc).loadJDBCTemplate();
        result = this.jdbcTemplate.query(sqlQuery, new RowMapper() {

            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                ResultSetMetaData meta = rs.getMetaData();
                JDBCRow row = new JDBCRow();
                row.setAutoCreate(isAutoCreate());
                row.setLocalId(rowNum + "");
                row.setDataSet(ds);
                int cols = meta.getColumnCount();
                for (int col = 1; col <= cols; col++) {
                    String name = meta.getColumnName(col);
                    Object data = rs.getObject(col);
                    row.addCol(col, name, data);
                }
                row.setTemplate(findTemplate(row));
                return (row);
            }
        });
    } else {
        throw new IllegalArgumentException("DataSource not supported! Must be instanceof JDBCDataSourceWarpper.");
    }
    return (result);
}

@Test
public void findAllQueryOneToManyEquals() throws Exception {
    String oneToManyAttrName = "oneToManyAttr";
    EntityType entityType = createEntityMetaOneToMany(oneToManyAttrName);
    //noinspection unchecked
    Query<Entity> query = mock(Query.class);
    int queryValue = 2;
    QueryRule queryRule = new QueryRule(oneToManyAttrName, EQUALS, queryValue);
    when(query.getRules()).thenReturn(singletonList(queryRule));
    String sql = "SELECT DISTINCT this.\"entityId\", (SELECT array_agg(\"refEntityId\" ORDER BY \"refEntityId\" ASC) FROM \"RefEntity\" WHERE this.\"entityId\" = \"RefEntity\".\"xrefAttr\") AS \"oneToManyAttr\" FROM \"Entity\" AS this LEFT JOIN \"RefEntity\" AS \"oneToManyAttr_filter1\" ON (this.\"entityId\" = \"oneToManyAttr_filter1\".\"xrefAttr\") WHERE \"oneToManyAttr_filter1\".\"refEntityId\" = ?  LIMIT 1000";
    //noinspection unchecked
    RowMapper<Entity> rowMapper = mock(RowMapper.class);
    when(postgreSqlEntityFactory.createRowMapper(entityType, null)).thenReturn(rowMapper);
    Entity entity0 = mock(Entity.class);
    when(jdbcTemplate.query(sql, new Object[] { queryValue }, rowMapper)).thenReturn(singletonList(entity0));
    postgreSqlRepo.setEntityType(entityType);
    assertEquals(postgreSqlRepo.findAll(query).collect(toList()), singletonList(entity0));
}

@Override
public Void call() throws Exception {
    List<T> entitiesList = Lists.newArrayListWithExpectedSize(entityIds.size());
    Map<Long, T> entitiesMap = Maps.newHashMapWithExpectedSize(entityIds.size());
    RowMapper<?> rowMapper;
    Class<? extends AbstractResultRowMapper<?>> rowMapperClass = entityInformation.getRowMapperClass();
    if (AbstractMapResultRowMapper.class.isAssignableFrom(rowMapperClass)) {
        rowMapper = rowMapperClass.getConstructor(Map.class).newInstance(entitiesMap);
    } else if (AbstractListResultRowMapper.class.isAssignableFrom(rowMapperClass)) {
        rowMapper = rowMapperClass.getConstructor(List.class).newInstance(entitiesList);
    } else {
        throw new IllegalStateException(String.format("Type de rowmapper non reconnu %1$s", rowMapperClass.getSimpleName()));
    }
    AutowireCapableBeanFactory autowire = applicationContext.getAutowireCapableBeanFactory();
    autowire.autowireBean(rowMapper);
    autowire.initializeBean(rowMapper, rowMapper.getClass().getSimpleName());
    prepareRowMapper(rowMapper, entityIds);
    if (entityInformation.getParameterIds() == null) {
        getJdbcTemplate().query(entityInformation.getSqlRequest(), rowMapper);
    } else {
        MapSqlParameterSource entityIdsParameterSource = new MapSqlParameterSource();
        entityIdsParameterSource.addValue(entityInformation.getParameterIds(), entityIds);
        getNamedParameterJdbcTemplate().query(entityInformation.getSqlRequest(), entityIdsParameterSource, rowMapper);
    }
    Collection<T> entities;
    if (AbstractMapResultRowMapper.class.isAssignableFrom(rowMapperClass)) {
        entities = entitiesMap.values();
    } else if (AbstractListResultRowMapper.class.isAssignableFrom(rowMapperClass)) {
        entities = entitiesList;
    } else {
        throw new IllegalStateException(String.format("Type de rowmapper non reconnu %1$s", rowMapperClass.getSimpleName()));
    }
    try {
        for (T entity : entities) {
            if (GenericListItem.class.isAssignableFrom(entityInformation.getEntityClass())) {
                genericListItemService.create((GenericListItem<?>) entity);
            } else if (genericEntityService != null) {
                genericEntityService.create(entity);
            } else {
                entityManagerUtils.getEntityManager().persist(entity);
            }
        }
    } catch (RuntimeExceptionServiceException | SecurityServiceException |  e) {
        LOGGER.error("Erreur lors de la persistence d'un(e) " + entityInformation.getEntityClass().getSimpleName() + ". {} créations annulées.", entities.size(), e);
    }
    clazzSet.add(entityInformation.getEntityClass());
    return null;
}

public InputStream getBlobAsBinaryStream(final String columnName) {
    return (InputStream) getJdbcTemplate().queryForObject(getSql(), getParams(null), new RowMapper<InputStream>() {

        @Override
        public InputStream mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new DefaultLobHandler().getBlobAsBinaryStream(rs, columnName);
        }
    });
}

private Page<JobLog> getLogs(String sqlBase, SqlParameterSource params, Query query) {
    String sql = query.applySqlLimit("select * " + sqlBase + " order by logDate asc");
    List<JobLog> jobLogs = jdbcTemplate.query(sql, params, new RowMapper<JobLog>() {

        @Override
        public JobLog mapRow(ResultSet rs, int rowNum) throws SQLException {
            return doMapRow(rs, rowNum);
        }
    });
    String countSql = "select count(*) " + sqlBase;
    Page<JobLog> page = Page.fromQuery(query);
    page.setItems(jobLogs);
    page.setTotalCount(jdbcTemplate.queryForObject(countSql, params, Integer.class));
    return page;
}

public Boolean patientExists(final String id, final String name) {
    return getJdbcTemplate().queryForObject("SELECT * FROM person_name WHERE person_name_id = ?", new Object[] { id }, new RowMapper<Boolean>() {

        public Boolean mapRow(ResultSet rs, int i) throws SQLException {
            if (rs.getString("given_name").equals(name)) {
                return true;
            } else {
                return false;
            }
        }
    });
}

public Object remove(String key) {
    try {
        MessageWrapper mw = this.jdbcOperations.queryForObject(getFirstSql(), new RowMapper<MessageWrapper>() {

            public MessageWrapper mapRow(ResultSet rs, int rowNum) throws SQLException {
                try {
                    byte[] bytes = getBlobAsBytes(rs);
                    ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
                    ObjectInputStream ois = new ObjectInputStream(bais);
                    return new MessageWrapper(rs.getLong("id"), (Message<?>) ois.readObject());
                } catch (IOException e) {
                    throw new SQLException(e);
                } catch (ClassNotFoundException e) {
                    throw new SQLException(e);
                }
            }
        }, key);
        this.jdbcOperations.update("delete from queued where id = ?", mw.getId());
        return mw.getMessage();
    } catch (EmptyResultDataAccessException e) {
        return null;
    }
}

@Bean
public StoredProcExecutor storedProcExecutor() {
    StoredProcExecutor executor = new StoredProcExecutor(dataSource());
    executor.setIgnoreColumnMetaData(true);
    executor.setStoredProcedureName("GET_PRIME_NUMBERS");
    List<ProcedureParameter> procedureParameters = new ArrayList<ProcedureParameter>();
    procedureParameters.add(new ProcedureParameter("beginRange", 1, null));
    procedureParameters.add(new ProcedureParameter("endRange", 10, null));
    executor.setProcedureParameters(procedureParameters);
    List<SqlParameter> sqlParameters = new ArrayList<SqlParameter>();
    sqlParameters.add(new SqlParameter("beginRange", Types.INTEGER));
    sqlParameters.add(new SqlParameter("endRange", Types.INTEGER));
    executor.setSqlParameters(sqlParameters);
    executor.setReturningResultSetRowMappers(Collections.<String, RowMapper<?>>singletonMap("out", new PrimeMapper()));
    return executor;
}

@Override
public List<Entity> execute(String query) {
    List<Entity> entities = jdbcTemplate.query(query, new RowMapper<Entity>() {

        @Override
        public Entity mapRow(ResultSet rs, int i) throws SQLException {
            ResultSetMetaData rsmd = rs.getMetaData();
            Entity entity = new QueryEntity();
            for (int j = 1; j <= rsmd.getColumnCount(); j++) {
                entity.setProperty(rsmd.getColumnName(j), rs.getObject(j));
            }
            return entity;
        }
    });
    return entities;
}

@Override
public List<Entity> execute(String query) {
    List<Entity> entities = jdbcTemplate.query(query, new RowMapper<Entity>() {

        @Override
        public Entity mapRow(ResultSet rs, int i) throws SQLException {
            ResultSetMetaData rsmd = rs.getMetaData();
            Entity entity = new QueryEntity();
            for (int j = 1; j <= rsmd.getColumnCount(); j++) {
                entity.setProperty(rsmd.getColumnName(j), rs.getObject(j));
            }
            return entity;
        }
    });
    return entities;
}

@Path("reports")
@GET
@Produces(MediaType.APPLICATION_JSON)
public List<TherapyPathReport> getReports(@PathParam("sourceKey") String sourceKey) {
    try {
        Source source = getSourceRepository().findBySourceKey(sourceKey);
        String tableQualifier = source.getTableQualifier(SourceDaimon.DaimonType.Results);
        String sql_statement = ResourceHelper.GetResourceAsString("/resources/therapypathresults/sql/getTherapyPathReports.sql");
        sql_statement = SqlRender.renderSql(sql_statement, new String[] { "OHDSI_schema" }, new String[] { tableQualifier });
        sql_statement = SqlTranslate.translateSql(sql_statement, "sql server", source.getSourceDialect());
        return getSourceJdbcTemplate(source).query(sql_statement, new RowMapper<TherapyPathReport>() {

            @Override
            public TherapyPathReport mapRow(final ResultSet rs, final int arg1) throws SQLException {
                final TherapyPathReport report = new TherapyPathReport();
                report.reportId = rs.getInt(1);
                report.reportCaption = rs.getString(2);
                report.year = Integer.toString(rs.getInt(3));
                if (report.year.equals("9999")) {
                    report.year = "All Years";
                }
                report.disease = rs.getString(4);
                report.datasource = rs.getString(5);
                return report;
            }
        });
    } catch (Exception exception) {
        throw new RuntimeException("Error getting therapy path reports" + exception.getMessage());
    }
}

@Override
protected UserDetails createUserDetails(String username, UserDetails userFromUserQuery, List<GrantedAuthority> combinedAuthorities) {
    final User user = (User) super.createUserDetails(username, userFromUserQuery, combinedAuthorities);
    List<UserDetails> users = getJdbcTemplate().query(selectUserExtraColumns, new String[] { username }, new RowMapper<UserDetails>() {

        public UserDetails mapRow(ResultSet rs, int rowNum) throws SQLException {
            ExtendedUser extendedUser = new ExtendedUser(user.getUsername(), user.getPassword(), user.isEnabled(), user.isAccountNonExpired(), user.isCredentialsNonExpired(), user.isAccountNonLocked(), user.getAuthorities());
            extendedUser.setAllowedRemoteAddresses(rs.getString(1));
            extendedUser.setComments(rs.getString(2));
            return extendedUser;
        }
    });
    if (users.size() == 0) {
        throw new UsernameNotFoundException(messages.getMessage("JdbcDaoImpl.notFound", new Object[] { username }, "Username {0} not found"), username);
    }
    return users.get(0);
}

@Override
@TransactionalReadOnly
public IndexMetadata findIndexMetadata(String indexName) throws ZepException {
    final String sql = "SELECT * FROM index_metadata WHERE zep_instance=:zep_instance AND index_name=:index_name";
    Map<String, Object> fields = new HashMap<String, Object>();
    fields.put(COLUMN_ZEP_INSTANCE, uuidConverter.toDatabaseType(zepInstanceId));
    fields.put(COLUMN_INDEX_NAME, indexName);
    final List<IndexMetadata> l = this.template.query(sql, new RowMapper<IndexMetadata>() {

        @Override
        public IndexMetadata mapRow(ResultSet rs, int rowNum) throws SQLException {
            IndexMetadata md = new IndexMetadata();
            md.setZepInstance(uuidConverter.fromDatabaseType(rs, COLUMN_ZEP_INSTANCE));
            md.setIndexName(rs.getString(COLUMN_INDEX_NAME));
            md.setIndexVersion(rs.getInt(COLUMN_INDEX_VERSION));
            md.setIndexVersionHash(rs.getBytes(COLUMN_INDEX_VERSION_HASH));
            return md;
        }
    }, fields);
    return (l.isEmpty()) ? null : l.get(0);
}

/**
	 * Delegate method to perform the actual compilation.
	 * <p>Subclasses can override this template method to perform their own compilation.
	 * Invoked after this base class's compilation is complete.
	 */
protected void compileInternal() {
    this.callMetaDataContext.initializeMetaData(getJdbcTemplate().getDataSource());
    // Iterate over the declared RowMappers and register the corresponding SqlParameter
    for (Map.Entry<String, RowMapper<?>> entry : this.declaredRowMappers.entrySet()) {
        SqlParameter resultSetParameter = this.callMetaDataContext.createReturnResultSetParameter(entry.getKey(), entry.getValue());
        this.declaredParameters.add(resultSetParameter);
    }
    this.callMetaDataContext.processParameters(this.declaredParameters);
    this.callString = this.callMetaDataContext.createCallString();
    if (logger.isDebugEnabled()) {
        logger.debug("Compiled stored procedure. Call string is [" + this.callString + "]");
    }
    this.callableStatementFactory = new CallableStatementCreatorFactory(getCallString(), this.callMetaDataContext.getCallParameters());
    onCompileInternal();
}

public List<SecondData> getSecondDataFromBackupDatabase(String userId, Ted5000 ted, Mtu mtu, String datapointsBack) {
    List<SecondData> data = jdbcTemplate.query(queryForSecondData, new Object[] { mtu.getId() }, new RowMapper<SecondData>() {

        public SecondData mapRow(ResultSet rs, int arg1) throws SQLException {
            return secondDataParser.parse(new ByteArrayInputStream(rs.getString("raw").getBytes())).get(0);
        }
    });
    return data;
}

/*
	 * (non-Javadoc)
	 * @see org.openmhealth.reference.data.ThirdPartyBin#getThirdParty(java.lang.String)
	 */
@Override
public ThirdParty getThirdParty(final String thirdParty) throws OmhException {
    try {
        return SqlDao.getInstance().getJdbcTemplate().queryForObject("SELECT " + User.JSON_KEY_USERNAME + ", " + ThirdParty.JSON_KEY_ID + ", " + ThirdParty.JSON_KEY_SHARED_SECRET + ", " + ThirdParty.JSON_KEY_NAME + ", " + ThirdParty.JSON_KEY_DESCRIPTION + ", " + ThirdParty.JSON_KEY_REDIRECT_URI + " " + "FROM " + UserBin.DB_NAME + ", " + ThirdPartyBin.DB_NAME + " " + "WHERE " + UserBin.DB_NAME + "." + SqlDao.KEY_DATABASE_ID + " = " + ThirdPartyBin.DB_NAME + "." + UserBin.DB_NAME + "_id " + "AND " + ThirdParty.JSON_KEY_ID + " = ?", new String[] { thirdParty }, new RowMapper<ThirdParty>() {

            /**
							 * Maps the row to a {@link ThirdParty} object. 
							 */
            @Override
            public ThirdParty mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
                String username = resultSet.getString(User.JSON_KEY_USERNAME);
                String id = resultSet.getString(ThirdParty.JSON_KEY_ID);
                String sharedSecret = resultSet.getString(ThirdParty.JSON_KEY_SHARED_SECRET);
                String name = resultSet.getString(ThirdParty.JSON_KEY_NAME);
                String description = resultSet.getString(ThirdParty.JSON_KEY_DESCRIPTION);
                URI uri;
                try {
                    uri = new URI(resultSet.getString(ThirdParty.JSON_KEY_REDIRECT_URI));
                } catch (URISyntaxException e) {
                    throw new SQLException("The redirect URI is malformed.", e);
                }
                return new ThirdParty(username, id, sharedSecret, name, description, uri);
            }
        });
    }// we may still be alright.
     catch (IncorrectResultSizeDataAccessException e) {
        if (e.getActualSize() == 0) {
            return null;
        }
        throw new OmhException("Multiple third-parties have the same ID: " + thirdParty, e);
    }// For all other issues, we simply propagate the exception.
     catch (DataAccessException e) {
        throw new OmhException("There was an error querying for a third-party.", e);
    }
}

@SuppressWarnings("unchecked")
public List<ItemVO<Integer, Integer>> getItemsAssociatedToUser(final Integer tenantId, final Integer userId, final Integer itemTypeId, final Integer sourceTypeId) {
    final StringBuilder query = new StringBuilder("SELECT DISTINCT\n   ");
    query.append(DEFAULT_ITEM_TO_COLUMN_NAME);
    query.append("\nFROM ");
    query.append(DEFAULT_TABLE_NAME);
    query.append("\nWHERE\n  ");
    query.append(DEFAULT_TENANT_COLUMN_NAME);
    query.append(" = ? AND\n  ");
    query.append(DEFAULT_USER_FROM_COLUMN_NAME);
    query.append(" = ? AND\n  ");
    query.append(DEFAULT_ITEM_TO_TYPE_COLUMN_NAME);
    query.append(" = ? AND\n  ");
    query.append(DEFAULT_SOURCE_TYPE_COLUMN_NAME);
    query.append(" = ?");
    final Object[] args = new Object[] { tenantId, userId, itemTypeId, sourceTypeId };
    final int[] argt = new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER };
    final RowMapper mapper = new ItemRowMapper(tenantId, itemTypeId);
    final List<ItemVO<Integer, Integer>> items = getJdbcTemplate().query(query.toString(), args, argt, mapper);
    return items;
}

/**
	 *
	 * @param identifier
	 *            the identifier of the job
	 * @return a job execution
	 */
public final JobInstance load(final Long identifier) {
    JobParameters jobParameters = getJobParameters(identifier);
    RowMapper<JobInstance> rowMapper = new JobInstanceRowMapper(jobParameters);
    JobInstance jobInstance = getJdbcTemplate().queryForObject("SELECT JOB_INSTANCE_ID, JOB_NAME, VERSION from BATCH_JOB_INSTANCE where JOB_INSTANCE_ID = ?", rowMapper, identifier);
    return jobInstance;
}

@SuppressWarnings("all")
private Page pageQuery(String sql, Map paramMap, final int totalItems, int pageSize, int pageNumber, RowMapper rowMapper) {
    if (totalItems <= 0) {
        return new Page(new Paginator(pageNumber, pageSize, 0));
    }
    Paginator paginator = new Paginator(pageNumber, pageSize, totalItems);
    List list = pageQueryForList(sql, paramMap, paginator.getOffset(), pageSize, rowMapper);
    return new Page(list, paginator);
}

public List<Integer> findOperableResourceIds(final AuthzSubject subj, final String resourceTable, final String resourceColumn, final String resType, final String operation, final String addCond) {
    final StringBuffer sql = new StringBuffer("SELECT DISTINCT(s.").append(resourceColumn).append(") FROM ").append(resourceTable).append(OPERABLE_SQL);
    if (addCond != null) {
        sql.append(" AND s.").append(addCond);
    }
    List<Integer> resTypeIds = this.jdbcTemplate.query(new PreparedStatementCreator() {

        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement stmt = con.prepareStatement(sql.toString());
            int i = 1;
            stmt.setString(i++, resType);
            stmt.setString(i++, operation);
            stmt.setInt(i++, subj.getId().intValue());
            stmt.setInt(i++, subj.getId().intValue());
            return stmt;
        }
    }, new RowMapper<Integer>() {

        public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getInt(1);
        }
    });
    return resTypeIds;
}

@WebMethod(exclude = true)
public List<RmUserSessionVo> queryByCondition(String queryCondition, String orderStr, int startIndex, int size) {
    List<RmUserSessionVo> result = new ArrayList<RmUserVo.RmUserSessionVo>();
    final Map<String, HttpSession> mSession = RmSessionListener.getSessions();
    String yesterday = new Timestamp(System.currentTimeMillis() - 1000 * 60 * 60 * 24 * 1).toString().substring(0, 19);
    String sql = "select uor.cluster_node_id, uor.login_sign, uor.login_uuid, u.* from RM_USER u " + "join RM_USER_ONLINE_RECORD uor on (u.id=uor.user_id and u.last_login_date=uor.login_time and uor.logout_time is null) " + "where u.usable_status='1' and u.login_status='1' and u.last_login_date>" + RmSqlHelper.getSqlDateStr(yesterday) + " order by u.last_login_date desc";
    //sessionId -> RmUserSessionVo对象
    final Map<String, RmUserSessionVo> mResult = new RmSequenceMap<String, RmUserSessionVo>();
    //对clusterNodeId分组存放
    final Map<String, List<String>> mOther = new RmSequenceMap<String, List<String>>();
    RmProjectHelper.getCommonServiceInstance().doQueryStartIndex(sql, new RowMapper() {

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            RmUserSessionVo vo = new RmUserSessionVo();
            RmPopulateHelper.populate(vo, rs);
            vo.setSessionId(rs.getString("login_sign"));
            //登录æœ?务器主机å??
            vo.setClusterNodeId(rs.getString("cluster_node_id"));
            if (//如果在本机
            mSession.containsKey(vo.getSessionId())) {
                HttpSession session = mSession.get(vo.getSessionId());
                populateSessionVo(vo, session);
            } else {
                if (!mOther.containsKey(vo.getClusterNodeId())) {
                    mOther.put(vo.getClusterNodeId(), new ArrayList<String>());
                }
                mOther.get(vo.getClusterNodeId()).add(vo.getSessionId());
            }
            mResult.put(vo.getSessionId(), vo);
            return null;
        }
    }, startIndex, size);
    //通过soa查询其他节点的session
    for (String clusterNodeId : mOther.keySet()) {
        String[] sessionIds = mOther.get(clusterNodeId).toArray(new String[0]);
        try {
            IRmSessionService remoteSs = getRemoteSessionService(clusterNodeId);
            if (remoteSs == null) {
                continue;
            }
            List<RmUserSessionVo> lBrother = remoteSs.listSessionLocal(sessionIds);
            for (RmUserSessionVo sourceVo : lBrother) {
                if (sourceVo == null) {
                    continue;
                }
                RmUserSessionVo destinationVo = mResult.get(sourceVo.getSessionId());
                if (sourceVo.getId() != null) {
                    RmPopulateHelper.populate(destinationVo, sourceVo);
                } else {
                    populateSessionVo(destinationVo, sourceVo);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    for (String sessionId : mResult.keySet()) {
        result.add(mResult.get(sessionId));
    }
    return result;
}

/**
   * Return a List of Portfolio objects with just the name set
   * 
   * @param startPortfolioName
   * @param count
   * @return
   */
public List<Portfolio> getPortfoliosLazy(String startPortfolioName, int count) {
    // TODO hack setObject proxy in jdbc-pool
    String portfoliosCql = "select FIRST 1000 ''..'' FROM Portfolios WHERE KEY >= ? LIMIT " + count;
    List<Portfolio> portfolios = jdbcTemplate.query(portfoliosCql, new SqlParameterValue[] { new SqlParameterValue(Types.VARCHAR, startPortfolioName) }, new RowMapper<Portfolio>() {

        public Portfolio mapRow(ResultSet rs, int arg1) throws SQLException {
            CassandraResultSet crs = (CassandraResultSet) rs;
            Portfolio portfolio = new Portfolio();
            portfolio.setName(new String(crs.getKey()));
            return portfolio;
        }
    });
    return portfolios;
}

public QueryResult query(String query) throws DataAccessException {
    final DefaultQueryResult queryResult = new DefaultQueryResult(query);
    final List<QueryResultColumn> columns = new ArrayList<>();
    final Map<String, Integer> displayNameMap = new HashMap<>();
    if (query != null && !query.toLowerCase().startsWith("show")) {
        query = safeQuery(query);
    }
    try {
        //  Setting in order to query complex formats like parquet
        jdbcTemplate.execute("set hive.optimize.index.filter=false");
        jdbcTemplate.query(query, new RowMapper<Map<String, Object>>() {

            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                if (columns.isEmpty()) {
                    ResultSetMetaData rsMetaData = rs.getMetaData();
                    for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
                        String colName = rsMetaData.getColumnName(i);
                        DefaultQueryResultColumn column = new DefaultQueryResultColumn();
                        column.setField(rsMetaData.getColumnName(i));
                        String displayName = rsMetaData.getColumnLabel(i);
                        column.setHiveColumnLabel(displayName);
                        //remove the table name if it exists
                        displayName = StringUtils.substringAfterLast(displayName, ".");
                        Integer count = 0;
                        if (displayNameMap.containsKey(displayName)) {
                            count = displayNameMap.get(displayName);
                            count++;
                        }
                        displayNameMap.put(displayName, count);
                        column.setDisplayName(displayName + "" + (count > 0 ? count : ""));
                        column.setTableName(StringUtils.substringAfterLast(rsMetaData.getColumnName(i), "."));
                        column.setDataType(ParserHelper.sqlTypeToHiveType(rsMetaData.getColumnType(i)));
                        columns.add(column);
                    }
                    queryResult.setColumns(columns);
                }
                Map<String, Object> row = new LinkedHashMap<>();
                for (QueryResultColumn column : columns) {
                    row.put(column.getDisplayName(), rs.getObject(column.getHiveColumnLabel()));
                }
                queryResult.addRow(row);
                return row;
            }
        });
    } catch (DataAccessException dae) {
        dae.printStackTrace();
        throw dae;
    }
    return queryResult;
}

/**
     * Return a list of user id to notify.
     *
     * @param upTo
     * @return
     */
public Set<Integer> check(Date upTo) {
    final List<Integer> userWithChanges = new ArrayList<>();
    List<SqlParameterSource> res = jdbc.query(queries.countNewForUsersId(), new RowMapper<SqlParameterSource>() {

        @Override
        public SqlParameterSource mapRow(ResultSet rs, int rowNum) throws SQLException {
            int userId = rs.getInt("USER_ID");
            userWithChanges.add(userId);
            return new MapSqlParameterSource("count", rs.getInt("COUNT_EVENT_ID")).addValue("userId", userId);
        }
    });
    if (!res.isEmpty()) {
        jdbc.batchUpdate(queries.updateCount(), res.toArray(new SqlParameterSource[res.size()]));
    }
    queries.updateCheckDate(upTo);
    // select users that have pending notifications that were not present in this check round
    MapSqlParameterSource userWithChangesParam = new MapSqlParameterSource("userWithChanges", userWithChanges);
    //
    List<Integer> usersToNotify = jdbc.queryForList(queries.usersToNotify() + " " + (userWithChanges.isEmpty() ? "" : queries.notIn()), userWithChangesParam, Integer.class);
    //
    jdbc.update(queries.reset() + " " + (userWithChanges.isEmpty() ? "" : queries.notIn()), userWithChangesParam);
    //
    return new TreeSet<>(usersToNotify);
}

protected <T> List<T> namedQueryWithLimit(String sql, RowMapper<T> rowMapper, Map<String, Object> args, int limit) {
    long t = System.nanoTime();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(daoHelper.getDataSource());
    jdbcTemplate.setMaxRows(limit);
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    List<T> result = namedTemplate.query(sql, args, rowMapper);
    log(sql, t);
    return result;
}

@SuppressWarnings("unchecked")
public List<String> getGroupNames(String username) {
    if (groupNamesQuery == null) {
        // TODO !? null if you want to deleteGroups on sync
        return Collections.emptyList();
    }
    return jdbcTemplate.query(groupNamesQuery, new String[] { username }, new RowMapper() {

        public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
            return resultSet.getString(1);
        }
    });
}

@Override
public DiscrepancyNotesSummary calculateNotesSummary(StudyBean currentStudy, ViewNotesFilterCriteria filter) {
    Map<String, Object> arguments = new HashMap<String, Object>(2);
    arguments.put("studyId", currentStudy.getId());
    List<String> terms = new ArrayList<String>();
    terms.add(queryStore.query(QUERYSTORE_FILE, "countDiscrepancyNotes.main"));
    //  terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter.studyHideCrf"));
    if (currentStudy.isSite(currentStudy.getParentStudyId())) {
        terms.add(queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter.siteHideCrf"));
    }
    // queries load data from the same view
    if (filter != null) {
        for (String filterKey : filter.getFilters().keySet()) {
            String filterQuery = queryStore.query(QUERYSTORE_FILE, "findAllDiscrepancyNotes.filter." + filterKey);
            terms.add(filterQuery);
            arguments.put(filterKey, filter.getFilters().get(filterKey));
        }
    }
    terms.add(queryStore.query(QUERYSTORE_FILE, "countDiscrepancyNotes.group"));
    String query = StringUtils.join(terms, ' ');
    final Integer[][] result = new Integer[ResolutionStatus.list.size() + 1][DiscrepancyNoteType.list.size() + 1];
    LOG.debug("SQL: " + query);
    getNamedParameterJdbcTemplate().query(query, arguments, new RowMapper<Void>() {

        // Using 'void' as return type as the extractor uses the
        // pre-populated 'result' object
        @Override
        public Void mapRow(ResultSet rs, int rowNum) throws SQLException {
            result[rs.getInt("resolution_status_id")][rs.getInt("discrepancy_note_type_id")] = rs.getInt("total");
            return null;
        }
    });
    return new DiscrepancyNotesSummary(result);
}