List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForList
@Override
public List<Map<String, Object>> queryForList(String sql) throws DataAccessException
From source file:com.pontecultural.flashcards.JdbcFlashcardsDao.java
public List<Card> fetchCardsByDeck(int aDeckId) { List<Card> rc = null;//from w w w . j a va2s . c om StringBuilder sql = new StringBuilder("SELECT enText,ptText FROM cards where deckId = "); sql.append(aDeckId); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString()); rc = new ArrayList<Card>(rows.size()); for (Map<String, Object> r : rows) { Card c = new Card(); c.setEnText((String) r.get("enText")); c.setPtText((String) r.get("ptText")); c.setId((Integer) r.get("deckId")); rc.add(c); } return rc; }
From source file:com.pontecultural.flashcards.JdbcFlashcardsDao.java
public List<Deck> fetchAllDecks() { List<Deck> rc = null;// w w w .j av a 2 s . c o m StringBuilder sql = new StringBuilder( "SELECT _id,name,description FROM decks WHERE name not like \"Tutorial\""); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString()); rc = new ArrayList<Deck>(rows.size()); for (Map<String, Object> r : rows) { Deck d = new Deck(); d.setName((String) r.get("name")); d.setDescription((String) r.get("description")); d.setId((Integer) r.get("_id")); rc.add(d); } return rc; }
From source file:eionet.transfer.dao.MetadataServiceJdbc.java
@Override public List<Upload> getAll() { String query = "SELECT id, expires, filename, uploader, contenttype, filesize FROM uploads"; JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); List<Upload> uploadList = new ArrayList<Upload>(); List<Map<String, Object>> uploadRows = jdbcTemplate.queryForList(query); for (Map<String, Object> row : uploadRows) { Upload uploadRec = new Upload(); uploadRec.setId((String) (row.get("id"))); uploadRec.setFilename((String) (row.get("filename"))); uploadRec.setExpires((Date) (row.get("expires"))); uploadRec.setUploader((String) (row.get("uploader"))); uploadRec.setContentType((String) (row.get("contenttype"))); uploadRec.setSize((Long) (row.get("filesize"))); uploadList.add(uploadRec);/* w w w. java2 s . co m*/ } return uploadList; }
From source file:de.hybris.platform.core.TenantRestartTest.java
private void assertTableExists(final JdbcTemplate template, final String tableName) { template.queryForList("SELECT ID FROM " + tableName); }
From source file:org.openmrs.module.tribe.TribeActivator.java
private void extractTribeColumn() { // check whether tribe module is installed in an old OpenMRS // throw exception if it is boolean isOldOpenMRS = true; try {/*from w w w . ja v a 2s .c o m*/ Class cls = OpenmrsClassLoader.getInstance().loadClass("org.openmrs.Tribe"); if (cls.isAnnotationPresent(Deprecated.class)) isOldOpenMRS = false; } catch (ClassNotFoundException e) { // OpenMRS version ok isOldOpenMRS = false; } if (isOldOpenMRS) { throw new ModuleException( "Tribe module cannot be used with this OpenMRS version. Please upgrade OpenMRS."); } // now convert patient tribes to tribe attributes AdministrationService as = Context.getAdministrationService(); // check whether patient table has tribe column log.info("Ignore the error message if occurs: " + "Error while running sql: SELECT distinct tribe from patient where 1 = 0"); log.info("The above message indicates that you are running a new implementation " + "which does not have a tribe column in the patient table."); boolean isTribeColumnExists = true; DataSource ds = new SingleConnectionDataSource(Context.getRuntimeProperties().getProperty("connection.url"), Context.getRuntimeProperties().getProperty("connection.username"), Context.getRuntimeProperties().getProperty("connection.password"), true); JdbcTemplate jdbc = new JdbcTemplate(ds); jdbc.setMaxRows(1); try { jdbc.queryForList("SELECT distinct tribe from patient where 1 = 0"); } catch (Exception e) { isTribeColumnExists = false; } // now convert patient tribes to tribe attributes if (isTribeColumnExists) { // create tribe attributes try { log.info("Transforming tribe details"); Context.addProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); as.executeSQL( "INSERT INTO person_attribute (person_id, value, person_attribute_type_id, creator, date_created, uuid)" + " SELECT patient_id, tribe," + " (SELECT person_attribute_type_id FROM person_attribute_type WHERE name = 'Tribe')" + " , 1, now(), UUID() FROM patient WHERE tribe IS NOT NULL;", false); } finally { Context.removeProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); } // drop tribe column in patient, this will make these scripts not run again log.info("Dropping old tribe column"); try { Context.addProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); as.executeSQL("ALTER TABLE patient DROP FOREIGN KEY belongs_to_tribe;", false); } catch (Exception e) { log.warn("Unable to drop foreign key patient.belongs_to_tribe", e); } finally { Context.removeProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); } try { Context.addProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); as.executeSQL("ALTER TABLE patient DROP COLUMN tribe;", false); } catch (Exception e) { log.warn("Unable to drop column patient.tribe", e); } finally { Context.removeProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); } log.info("Tribe data conversion complete"); } // add View Tribes privilege to Authenticated role if not exists try { Context.addProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); UserService us = Context.getUserService(); Role authenticatedRole = us.getRole(OpenmrsConstants.AUTHENTICATED_ROLE); if (!authenticatedRole.hasPrivilege(TribeConstants.PRIV_VIEW_TRIBES)) { as.executeSQL("INSERT INTO role_privilege (role, privilege) VALUES ('" + OpenmrsConstants.AUTHENTICATED_ROLE + "', '" + TribeConstants.PRIV_VIEW_TRIBES + "')", false); } } finally { Context.removeProxyPrivilege(OpenmrsConstants.PRIV_SQL_LEVEL_ACCESS); } }
From source file:com.univocity.examples.ExampleWithDatabase.java
protected final String printTable(String table, String... columns) { JdbcTemplate db = new JdbcTemplate(dataSource); StringBuilder order = new StringBuilder(); for (String column : columns) { if (order.length() != 0) { order.append(','); }// ww w .j av a2s .c o m order.append(column); } String names = order.toString(); List<Map<String, Object>> results = db .queryForList("select " + names + " from " + table + " order by " + names); return printRows(results, table, columns); }
From source file:com.pontecultural.flashcards.JdbcFlashcardsDao.java
public List<Card> fetchRandomCards() { List<Card> rc = null;/*from w w w . jav a2s . c om*/ JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); int tutorialId = -1; // find deckId for Tutorial, so that, we can ignore it. StringBuilder sql = new StringBuilder("SELECT _id FROM decks where name = \"Tutorial\""); tutorialId = jdbcTemplate.queryForInt(sql.toString()); sql = new StringBuilder("SELECT enText,ptText,deckId FROM cards where deckId != "); sql.append(tutorialId); sql.append(" order by rand()"); List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString()); rc = new ArrayList<Card>(rows.size()); for (Map<String, Object> r : rows) { Card c = new Card(); c.setEnText((String) r.get("enText")); c.setPtText((String) r.get("ptText")); c.setId((Integer) r.get("deckId")); rc.add(c); } return rc; }
From source file:io.pivotal.spring.xd.jdbcgpfdist.LoadIT.java
@Test public void testInsert() { context.register(Config1.class, CommonConfig.class); context.refresh();/*from w w w. j a v a2 s . c om*/ JdbcTemplate template = context.getBean(JdbcTemplate.class); String drop = "DROP TABLE IF EXISTS AbstractLoadTests;"; String create = "CREATE TABLE AbstractLoadTests (data text);"; template.execute(drop); template.execute(create); List<String> data = new ArrayList<String>(); for (int i = 0; i < 10; i++) { data.add("DATA" + i + "\n"); } broadcastData(data); GreenplumLoad greenplumLoad = context.getBean(GreenplumLoad.class); greenplumLoad.load(); List<Map<String, Object>> queryForList = template.queryForList("SELECT * from AbstractLoadTests;"); assertThat(queryForList, notNullValue()); assertThat(queryForList.size(), is(10)); List<String> queryData = new ArrayList<String>(); for (int i = 0; i < 10; i++) { queryData.add((String) queryForList.get(i).get("data")); } assertThat(queryData, containsInAnyOrder("DATA0", "DATA1", "DATA2", "DATA3", "DATA4", "DATA5", "DATA6", "DATA7", "DATA8", "DATA9")); }
From source file:io.pivotal.spring.xd.jdbcgpfdist.LoadIT.java
@Test public void testUpdate() { context.register(Config2.class, CommonConfig.class); context.refresh();//from w w w .j av a2 s .c o m JdbcTemplate template = context.getBean(JdbcTemplate.class); String drop = "DROP TABLE IF EXISTS AbstractLoadTests;"; String create = "CREATE TABLE AbstractLoadTests (col1 text, col2 text);"; template.execute(drop); template.execute(create); List<String> data = new ArrayList<String>(); for (int i = 0; i < 10; i++) { template.execute("insert into AbstractLoadTests values('DATA" + i + "', 'DATA');"); data.add("DATA" + i + "\tDATA" + i + "\n"); } broadcastData(data); GreenplumLoad greenplumLoad = context.getBean(GreenplumLoad.class); greenplumLoad.load(); List<Map<String, Object>> queryForList = template.queryForList("SELECT * from AbstractLoadTests;"); assertThat(queryForList, notNullValue()); assertThat(queryForList.size(), is(10)); for (int i = 0; i < 10; i++) { assertThat(queryForList.get(i).get("col2"), is(queryForList.get(i).get("col1"))); } }