Example usage for org.springframework.jdbc.core JdbcTemplate query

List of usage examples for org.springframework.jdbc.core JdbcTemplate query

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. query

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate query.

Prototype

@Override
    public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException

Source Link

Usage

From source file:sf.wicklet.site.gwt.test.db.TestSpringJDBCH201.java

@Test
public void test01() throws SQLException, ClassNotFoundException {
    final String dbpath1 = new File("trash/TestSpringJDBCH201/db01").getAbsolutePath();
    final File dbfile1 = new File(dbpath1 + ".h2.db");
    dbfile1.delete();/* w w  w.j  a v a  2 s  .c  om*/
    final DbSupport dao = new DbSupport(createDatabase(dbfile1, dbpath1));
    try {
        final JdbcTemplate t = dao.getJdbcTemplate();
        final List<StringPair> ret = t.query("SELECT * from User, Address where User.addressId = Address.id",
                new RowMapper<StringPair>() {
                    @Override
                    public StringPair mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                        return new StringPair(rs.getString("name"), rs.getString("address"));
                    }
                });
        if (TestSpringJDBCH201.DEBUG) {
            for (final StringPair p : ret) {
                System.out.println(p.first() + ": " + p.second());
            }
        }
        Assert.assertEquals(3, ret.size());
        final Map<String, String> map = new TreeMap<String, String>();
        for (final StringPair p : ret) {
            map.put(p.first(), p.second());
        }
        Assert.assertEquals("address3", map.get("user2"));
    } finally {
        dao.shutdown();
        Assert.assertTrue(dbfile1.exists());
    }
}

From source file:sf.wicklet.site.gwt.test.db.TestSpringJDBCH201.java

@Test
public void test02() throws SQLException, ClassNotFoundException {
    final String dbpath2 = new File("trash/TestSpringJDBCH201/db02").getAbsolutePath();
    final File dbfile2 = new File(dbpath2 + ".h2.db");
    dbfile2.delete();/*from   w w w.java 2  s  . c om*/
    Assert.assertFalse(dbfile2.exists());
    DbSupport dao = new DbSupport(createDatabase(dbfile2, dbpath2));
    try {
        final JdbcTemplate t = dao.getJdbcTemplate();
        final List<StringPair> ret = t.query("SELECT * from User, Address where User.addressId = Address.id",
                new RowMapper<StringPair>() {
                    @Override
                    public StringPair mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                        return new StringPair(rs.getString("name"), rs.getString("address"));
                    }
                });
        Assert.assertEquals(3, ret.size());
    } finally {
        dao.shutdown();
    }
    Assert.assertTrue(dbfile2.exists());
    // Try again with existing dbfile.
    dao = new DbSupport(createDatabase(dbfile2, dbpath2));
    try {
        final JdbcTemplate t = dao.getJdbcTemplate();
        final List<StringPair> ret = t.query("SELECT * from User, Address where User.addressId = Address.id",
                new RowMapper<StringPair>() {
                    @Override
                    public StringPair mapRow(final ResultSet rs, final int rowNum) throws SQLException {
                        return new StringPair(rs.getString("name"), rs.getString("address"));
                    }
                });
        Assert.assertEquals(3, ret.size());
    } finally {
        dao.shutdown();
    }
}

From source file:gov.nih.nci.ncicb.cadsr.common.persistence.dao.jdbc.JDBCDataElementDAO.java

public boolean isDEDerived(final String deIdSeq) {

    final String qry = "select * from complex_data_elements_view where p_de_idseq = ?";

    JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
    Object result = jdbcTemplate.query(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement(qry);
            ps.setString(1, deIdSeq);/*www.  j ava 2 s . co m*/
            return ps;
        }
    }, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return new Boolean(true);
            } else {
                return new Boolean(false);
            }
        }
    });
    return (Boolean) result;
}

From source file:db.migration.V2017_08_27_14_00__Import_Images_Into_Db.java

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    LOG.info("Importing images into db");
    List<ImageObject> images = jdbcTemplate.query(
            "select `" + FIELD_ID + "`, `" + FIELD_FILE_PATH + "` from `" + TABLE_NAME + "`",
            new ImageObjectRowMapper<>());
    for (ImageObject entry : images) {
        try {//w  ww  .  j a  v  a2s .com
            File file = new File(entry.getFilePath());
            byte[] data = IOUtils.toByteArray(new FileInputStream(file));
            if (data != null) {
                ImageCategory category = getCategory(entry.getFilePath());
                jdbcTemplate.update(connection -> {
                    PreparedStatement ps = connection.prepareStatement(
                            String.format("update `%s` set `%s` = ?, `%s` = ?, `%s` = ? where `%s` = ?",
                                    TABLE_NAME, FIELD_CONTENT_LENGTH, FIELD_CONTENT, FIELD_CATEGORY, FIELD_ID),
                            new String[] { FIELD_CONTENT });
                    ps.setLong(1, 0L + data.length);
                    ps.setBytes(2, data);
                    ps.setString(3, category.name());
                    ps.setLong(4, entry.getId());
                    return ps;
                });
            }
        } catch (Exception e) {
            LOG.error(String.format("Unable to import file %s: %s", entry.getFilePath(), e.getMessage()));
        }
    }
    LOG.info("Done importing images into db");
}

From source file:db.postgres.V2_0_1__InitializeIdentityZones.java

private void removeDuplicateRows(final JdbcTemplate jdbcTemplate, final Long zone) {
    final List<SubjectEntity> subjects = jdbcTemplate
            .query("SELECT DISTINCT subject_identifier, attributes FROM subject", new SubjectRowMapper());
    jdbcTemplate.update("DELETE FROM subject *");
    for (SubjectEntity s : subjects) {
        jdbcTemplate.update(//  ww  w  . ja va2  s .  c o m
                "INSERT INTO subject (subject_identifier, attributes, "
                        + " authorization_zone_id) VALUES (?,?,?)",
                s.getSubjectIdentifier(), s.getAttributesAsJson(), zone);
    }
    final List<ResourceEntity> resources = jdbcTemplate
            .query("SELECT DISTINCT resource_identifier, attributes FROM resource", new ResourceRowMapper());
    jdbcTemplate.update("DELETE FROM resource *");
    for (ResourceEntity r : resources) {
        jdbcTemplate.update(
                "INSERT INTO resource (resource_identifier, attributes, "
                        + " authorization_zone_id) VALUES (?,?,?)",
                r.getResourceIdentifier(), r.getAttributesAsJson(), zone);
    }

    final List<PolicySetEntity> policysets = jdbcTemplate
            .query("SELECT DISTINCT policy_set_id, policy_set_json FROM policy_set", new PolicySetRowMapper());
    jdbcTemplate.update("DELETE FROM policy_set *");
    for (PolicySetEntity ps : policysets) {
        SqlRowSet row = jdbcTemplate.queryForRowSet("SELECT * FROM policy_set WHERE policy_set_id =?",
                ps.getPolicySetID());
        if (row.next()) {
            jdbcTemplate.update("UPDATE policy_set SET policy_set_json = ? WHERE policy_set_id = ?",
                    ps.getPolicySetJson(), ps.getPolicySetID());
        } else {
            jdbcTemplate.update(
                    "INSERT INTO policy_set (policy_set_id, policy_set_json, "
                            + " authorization_zone_id) VALUES (?,?,?)",
                    ps.getPolicySetID(), ps.getPolicySetJson(), zone);
        }
    }
}

From source file:com.rplt.studioMusik.member.MemberDAO.java

@Override
public List<Member> getDataList() {
    List<Member> memberList = new ArrayList<Member>();

    String sql = "SELECT * FROM member_studio_musik";

    sql = "SELECT * FROM `studiomusik`.`member_studio_musik`";

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    memberList = jdbcTemplate.query(sql, new MemberRowMapper());
    return memberList;
}

From source file:annis.sqlgen.AnnotateSqlGenerator.java

public T queryAnnotationGraph(JdbcTemplate jdbcTemplate, String toplevelCorpusName, String documentName) {
    return (T) jdbcTemplate.query(getDocumentQuery(toplevelCorpusName, documentName), this);
}

From source file:com.talkingdata.orm.tool.SqlParser.java

public List<ClassDefinition> getTables(final JdbcTemplate jdbcTemplate, String db, final String packageName) {
    final List<ClassDefinition> list = new ArrayList<>();
    String sqlTable = String.format(SQL_TABLE, db);
    jdbcTemplate.query(sqlTable, new RowCallbackHandler() {
        @Override//from w w w  .j  a va  2  s  . c o m
        public void processRow(ResultSet rs) throws SQLException {
            String table = rs.getString("tableName");
            final List<ClassDefinitionColumn> columns = new ArrayList<ClassDefinitionColumn>();
            ClassDefinition classDefinition = new ClassDefinition(packageName, table);
            list.add(classDefinition);
            classDefinition.setColumns(columns);

            String sqlColumn = String.format(SQL_COLUMN, db, table);
            jdbcTemplate.query(sqlColumn, new RowCallbackHandler() {
                @Override
                public void processRow(ResultSet rs) throws SQLException {
                    String columnName = rs.getString("Field");
                    String columnType = rs.getString("Type").replaceAll("\\(\\d+\\)", "").toUpperCase();
                    System.out.println(columnType);
                    String primaryKey = rs.getString("Key");
                    columns.add(new ClassDefinitionColumn(columnName, DATA_MAP.get(columnType), primaryKey));
                }
            });
        }
    });

    return list;
}

From source file:db.postgres.V2_0_1__InitializeIdentityZones.java

private Set<ZoneClientEntity> findExistingOAuthClients(final JdbcTemplate jdbcTemplate) {
    Set<ZoneClientEntity> oauthClients = new HashSet<>();
    List<ZoneClientEntity> subjectOAuthClients = jdbcTemplate
            .query("SELECT DISTINCT issuer_id, client_id FROM subject", new ZoneClientRowMapper());
    oauthClients.addAll(subjectOAuthClients);

    List<ZoneClientEntity> resourceOAuthClients = jdbcTemplate
            .query("SELECT DISTINCT issuer_id, client_id FROM resource", new ZoneClientRowMapper());
    oauthClients.addAll(resourceOAuthClients);

    List<ZoneClientEntity> policySetOAuthClients = jdbcTemplate
            .query("SELECT DISTINCT issuer_id, client_id FROM policy_set", new ZoneClientRowMapper());
    oauthClients.addAll(policySetOAuthClients);
    return oauthClients;
}