List of usage examples for org.springframework.jdbc.core JdbcTemplate query
@Override
public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException
From source file:ca.nrc.cadc.tap.schema.TapSchemaDAO.java
/** * Creates and returns a TapSchema object representing all of the data in TAP_SCHEMA. * /*from w w w . jav a 2 s . com*/ * @param tableName fully qualified table name * @param depth * @return TapSchema containing all of the data from TAP_SCHEMA. */ public TapSchema get(String tableName, int depth) { JdbcTemplate jdbc = new JdbcTemplate(dataSource); TapSchema ret = new TapSchema(); // List of TAP_SCHEMA.schemas GetSchemasStatement gss = new GetSchemasStatement(schemasTableName); if (ordered) gss.setOrderBy(orderSchemaClause); ret.schemaDescs = jdbc.query(gss, new SchemaMapper()); // TAP_SCHEMA.tables GetTablesStatement gts = new GetTablesStatement(tablesTableName); gts.setTableName(tableName); if (ordered) gts.setOrderBy(orderTablesClause); List<TableDesc> tableDescs = jdbc.query(gts, new TableMapper()); // Add the Tables to the Schemas. addTablesToSchemas(ret.schemaDescs, tableDescs); // TAP_SCHEMA.columns if (depth > MIN_DEPTH) { GetColumnsStatement gcs = new GetColumnsStatement(columnsTableName); gcs.setTableName(tableName); if (ordered) gcs.setOrderBy(orderColumnsClause); List<ColumnDesc> columnDescs = jdbc.query(gcs, new ColumnMapper()); // Add the Columns to the Tables. addColumnsToTables(tableDescs, columnDescs); // List of TAP_SCHEMA.keys GetKeysStatement gks = new GetKeysStatement(keysTableName); gks.setTableName(tableName); if (ordered) gks.setOrderBy(orderKeysClause); List<KeyDesc> keyDescs = jdbc.query(gks, new KeyMapper()); // TAP_SCHEMA.key_columns GetKeyColumnsStatement gkcs = new GetKeyColumnsStatement(keyColumnsTableName); if (tableName != null) gkcs.setKeyDescs(keyDescs); // get keys for tableName only if (ordered) gkcs.setOrderBy(orderKeyColumnsClause); List<KeyColumnDesc> keyColumnDescs = jdbc.query(gkcs, new KeyColumnMapper()); // Add the KeyColumns to the Keys. addKeyColumnsToKeys(keyDescs, keyColumnDescs); // connect foreign keys to the fromTable addForeignKeys(ret, keyDescs); } // Add the List of FunctionDescs. ret.functionDescs = getFunctionDescs(); for (SchemaDesc s : ret.schemaDescs) { int num = 0; if (s.tableDescs != null) num = s.tableDescs.size(); log.debug("schema " + s.schemaName + " has " + num + " tables"); } return ret; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<String> getPolygonIDS(final String uazTbl, final String polygonStr) { final String query = "select a.propid, ST_Asgeojson(geom) from " + postgisDataStoreConfig.getDataStoreParams().get(SCHEMA.key) + "." + uazTbl + " as a INNER JOIN ST_GeomFromText('" + polygonStr + "', 4326) as b ON ST_Intersects(a.geom, b.geometry)" + " where a.propid is not null "; // this line added later for avoiding // null./*from w w w . ja v a 2 s. c o m*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getPolygonIDS: query is {} ", query); final List<String> entries = jdbcTemplate.query(query, new RowMapper<String>() { @Override public String mapRow(final ResultSet rs, final int arg1) throws SQLException { return rs.getObject(1).toString(); } }); LOGGER.info(" returning {} distinct entries ", entries.size()); return entries; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<roleData> getAllRoles() { String query = ""; try {/*from w w w . ja v a2 s . c om*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); query = "select * from roles"; LOGGER.info("getAllRoles query is {} ", query); final List<roleData> roles = jdbcTemplate.query(query, new BeanPropertyRowMapper(roleData.class)); return roles; } catch (final Exception e) { LOGGER.info("error in getAllRoles is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<acclvlData> getAllAccessLevels() { String query = ""; try {//from w w w . jav a 2 s . c o m final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); query = "select * from acclvls"; LOGGER.info("getAllAccessLevels query is {} ", query); final List<acclvlData> accs = jdbcTemplate.query(query, new BeanPropertyRowMapper(acclvlData.class)); return accs; } catch (final Exception e) { LOGGER.info("error in getAllAccessLevels is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<appData> getAllApplications() { String query = ""; try {//from www . j a v a 2s . c om final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); query = "select * from application"; LOGGER.info("getAllApplications query is {} ", query); final List<appData> apps = jdbcTemplate.query(query, new BeanPropertyRowMapper(appData.class)); return apps; } catch (final Exception e) { LOGGER.info("error in getAllApplications is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<orgData> getAllOrganisations() { String query = ""; try {//from w w w.j a v a 2s.c o m final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); query = "select * from organisations"; LOGGER.info("getAllOrganisations query is {} ", query); final List<orgData> orgs = jdbcTemplate.query(query, new BeanPropertyRowMapper(orgData.class)); return orgs; } catch (final Exception e) { LOGGER.info("error in getAllOrganisations is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<dummyuserData> getAllUsers() { String query = "select * from users"; try {/* www . ja va2 s. c o m*/ final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); LOGGER.info("getUser: query is {} ", query); query = "select a.user_id,a.email,a.firstname, a.lastname from users as a "; final List<dummyuserData> listusers = jdbcTemplate.query(query, new BeanPropertyRowMapper(dummyuserData.class)); return listusers; } catch (final Exception e) { LOGGER.info("error in getAllUsers is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<dummyuserData> searchAllUsers(String name, String family, String email) { String query = "select * from users"; try {//w w w . ja va2 s . c o m final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); email = email.replace("'", "''"); name = name.replace("'", "''"); family = family.replace("'", "''"); query = "select a.user_id,a.email,a.firstname, a.lastname from users as a where a.firstname ~* '" + name + "' and a.lastname ~* '" + family + "' and a.email ~* '" + email + "'"; LOGGER.info("serachAllUsers: query is {} ", query); final List<dummyuserData> listusers = jdbcTemplate.query(query, new BeanPropertyRowMapper(dummyuserData.class)); return listusers; } catch (final Exception e) { LOGGER.info("error in serachAllUsers is : {}", e.toString()); } return null; }
From source file:au.aurin.org.svc.GeodataFinder.java
public List<agreementData> getLicense(final String user_id, final String app_id, final String org_id) { String query = ""; try {//w ww. java2 s.c o m final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); query = "select a.agr_id,a.agreed, a.aggtime, b.lictext,b.licblob,b.licver,b.lic_id,c.orgname, d.appname from agreement as a , license as b, organisations as c, application as d " + " where a.lic_id = b.lic_id and b.org_id = c.org_id and a.app_id = d.app_id " + " and a.user_id = " + user_id + " and a.app_id = " + app_id + " and c.org_id = " + org_id + " order by b.licver desc"; LOGGER.info("getLicense: query is {} ", query); final List<agreementData> agguser = jdbcTemplate.query(query, new BeanPropertyRowMapper(agreementData.class)); return agguser; } catch (final Exception e) { LOGGER.info("error in getLicense is : {}", e.toString()); } return null; }