List of usage examples for org.apache.commons.dbutils QueryRunner query
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException
From source file:io.apiman.manager.api.jpa.JpaStorageInitializer.java
/** * Called to initialize the database.//from w w w . j av a2s. co m */ @SuppressWarnings("nls") public void initialize() { QueryRunner run = new QueryRunner(ds); Boolean isInitialized; try { isInitialized = run.query("SELECT * FROM apis", new ResultSetHandler<Boolean>() { @Override public Boolean handle(ResultSet rs) throws SQLException { return true; } }); } catch (SQLException e) { isInitialized = false; } if (isInitialized) { System.out.println("============================================"); System.out.println("Apiman Manager database already initialized."); System.out.println("============================================"); return; } ClassLoader cl = JpaStorageInitializer.class.getClassLoader(); URL resource = cl.getResource("ddls/apiman_" + dbType + ".ddl"); try (InputStream is = resource.openStream()) { System.out.println("======================================="); System.out.println("Initializing apiman Manager database."); DdlParser ddlParser = new DdlParser(); List<String> statements = ddlParser.parse(is); for (String sql : statements) { System.out.println(sql); run.update(sql); } System.out.println("======================================="); } catch (Exception e) { throw new RuntimeException(e); } }
From source file:io.apiman.gateway.engine.jdbc.PollCachingJdbcRegistry.java
/** * Checks the ES store to see if the 'dataVersion' entry has been updated with a newer * version #. If it has, then we need to invalidate our cache. */// w ww. j av a2s.c om protected void checkCacheVersion() { // Be very aggressive in invalidating the cache. boolean invalidate = true; QueryRunner run = new QueryRunner(ds); try { long latestVersion = run.query("SELECT version FROM gw_dataversion", Handlers.LONG_HANDLER); //$NON-NLS-1$ if (latestVersion > -1 && dataVersion > -1 && latestVersion == dataVersion) { invalidate = false; } else { dataVersion = latestVersion; } } catch (SQLException e) { // TODO need to use the gateway logger to log this! e.printStackTrace(); } if (invalidate) { invalidateCache(); } }
From source file:com.example.data.PetData.java
public List<Map<String, Object>> findPetByTags(String tags) throws SQLException { QueryRunner run = new QueryRunner(H2DB.getDataSource()); List<String> tagList = Arrays.asList(tags.split(",")); if (tagList.isEmpty()) return Collections.EMPTY_LIST; else {/*w ww . ja v a2 s . com*/ return run .query("select * from pet", H2DB.mkResultSetHandler("id", "name", "categoryId", "photoUrls", "tags", "status")) .stream().map(m -> { m.put("photoUrls", H2DB.strToList((String) m.get("photoUrls"))); m.put("tags", H2DB.strToList((String) m.get("tags"))); m.put("category", getCategory(run, (Long) m.get("categoryId"))); m.remove("categoryId"); return m; }).filter(m -> { if (m.get("tags") == null) return false; else { List<String> its = (List<String>) m.get("tags"); List<String> tmp = new ArrayList<>(its); tmp.removeAll(tagList); return tmp.size() != its.size(); } }).collect(Collectors.toList()); } }
From source file:de.iritgo.aktario.jdbc.LoadObject.java
/** * Load an object.//from ww w. ja va 2 s .c om * * @param dataSource The data source to load from. * @param typeId The type of the object to load. * @param uniqueId The unique id of the object to load. * @return The loaded object (already registered with the base registry). */ private DataObject load(final DataSource dataSource, final String typeId, long uniqueId) { DataObject object = null; try { QueryRunner query = new QueryRunner(dataSource); object = (DataObject) query.query("select * from " + typeId + " where id=" + uniqueId, new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { rs.getMetaData(); if (rs.next()) { try { DataObject object = (DataObject) Engine.instance().getIObjectFactory() .newInstance(typeId); object.setUniqueId(rs.getLong("id")); for (Iterator i = object.getAttributes().entrySet().iterator(); i.hasNext();) { Map.Entry attribute = (Map.Entry) i.next(); if (attribute.getValue() instanceof IObjectList) { loadList(dataSource, object, object.getIObjectListAttribute((String) attribute.getKey())); } else { try { if (!object.getAttribute((String) attribute.getKey()).getClass() .equals(rs.getObject((String) attribute.getKey()) .getClass())) { System.out.println( "********* Datastruct is not compatible with dataobject:" + object.getTypeId() + ":" + attribute.getKey() + " Types:" + object.getAttribute( (String) attribute.getKey()).getClass() + "!=" + rs.getObject((String) attribute.getKey()) .getClass()); } object.setAttribute((String) attribute.getKey(), rs.getObject((String) attribute.getKey())); } catch (NullPointerException x) { System.out.println("LoadObject error: " + attribute.getKey()); } } } return object; } catch (NoSuchIObjectException ignored) { Log.logError("persist", "LoadObject", "NoSuchIObjectException"); } } else { } return null; } }); if (object != null) { Log.logVerbose("persist", "LoadObject", "Successfully loaded object " + typeId + ":" + uniqueId); } else { Log.logError("persist", "LoadObject", "Unable to find object " + typeId + ":" + uniqueId); } } catch (SQLException x) { Log.logError("persist", "LoadObject", "Error while loading the object " + typeId + ":" + uniqueId + ": " + x); } return object; }
From source file:de.iritgo.aktario.buddylist.BuddyListManager.java
public void generateGroups(final User user) { final DataSource dataSource = (DataSource) CommandTools.performSimple("persist.GetDefaultDataSource"); final BuddyList buddyList = addBuddyList(user); final ResourceService resources = Engine.instance().getResourceService(); QueryRunner query = new QueryRunner(dataSource); try {//from w w w . j a v a 2s . com query.query( "select * from akteragroup left join akteragroupentry on akteragroup.id = akteragroupentry.groupid left join keelusers on keelusers.uniqid = akteragroupentry.userid where keelusers.username=" + "'" + user.getName() + "' and akteragroup.visible = true", new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { while (rs.next()) { try { long groupId = rs.getLong("groupId"); String displayName = rs.getString("title"); if (StringTools.isTrimEmpty(displayName)) { displayName = rs.getString("name"); } displayName = resources.getStringWithoutException(displayName); final BuddyListGroup buddyListGroup = addBuddyListGroup(user, buddyList, groupId, displayName); QueryRunner query2 = new QueryRunner(dataSource); query2.query( "select * from akteragroup left join akteragroupentry on akteragroup.id = akteragroupentry.groupid " + "left join keelusers on keelusers.uniqid = akteragroupentry.userid where akteragroup.id=" + groupId, new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { while (rs.next()) { try { addParticipant(rs.getString("username"), buddyListGroup); } catch (Exception ignored) { Log.logError("persist", "LoadObject", "NoSuchIObjectException"); ignored.printStackTrace(); } } return null; } }); } catch (Exception x) { Log.logError("plugin", "BuddyListManager.generateGroups", x.toString()); } } return null; } }); } catch (Exception x) { Log.logError("plugin", "BuddyListManager.generateGroups", x.toString()); } }
From source file:com.dianping.puma.parser.meta.DefaultTableMetaInfoFetcher.java
protected TableMetaInfo _refreshTableMeta(final String database, final String table) throws SQLException { initDsIfNeeded();/* w w w. ja va2 s . com*/ QueryRunner runner = new QueryRunner(metaDs); Transaction t = Cat.newTransaction("SQL.meta", getKey(database, table)); try { TableMetaInfo tableMetaInfo = runner.query(genTableMetaSql(database, table), new ResultSetHandler<TableMetaInfo>() { @Override public TableMetaInfo handle(ResultSet rs) throws SQLException { TableMetaInfo result = new TableMetaInfo(); result.setDatabase(database); result.setTable(table); result.setColumns(new HashMap<Integer, String>()); result.setKeys(new ArrayList<String>()); result.setTypes(new HashMap<String, String>()); result.setSignedInfos(new HashMap<Integer, Boolean>()); while (rs.next()) { int i = rs.getRow(); String column = rs.getString("Field"); result.getColumns().put(i, column); if (rs.getString("Type").contains("unsigned")) { result.getSignedInfos().put(i, false); } else { result.getSignedInfos().put(i, true); } if (rs.getString("Key").equalsIgnoreCase("pri")) { result.getKeys().add(column); } } return result; } }); t.setStatus("0"); return tableMetaInfo; } catch (SQLException e) { t.setStatus("1"); throw e; } finally { t.complete(); } }
From source file:com.geaviation.controller.MainController.java
protected ModelAndView handleRequestInternal(HttpServletRequest request, HttpServletResponse response) throws Exception { ModelAndView mv = new ModelAndView(); DataSource ds = (DataSource) this.getApplicationContext().getBean("myDataSource"); try {//from ww w . j a v a2 s. com String action = request.getParameter("action"); String uri = request.getRequestURI(); if (uri.endsWith("home.htm")) { mv.setViewName("home"); } if ("request".equalsIgnoreCase(action)) { QueryRunner run = new QueryRunner(ds); ResultSetHandler<List<PartLicenseBean>> messages = new BeanListHandler<PartLicenseBean>( PartLicenseBean.class); List<PartLicenseBean> msg = run.query("select * from part_license", messages); if (msg != null) { System.out.println("printing license no received from DB: " + msg.get(0).getLicense_no()); mv.addObject("licenselist", msg); } mv.setViewName("request"); } else if ("validateinvoice".equalsIgnoreCase(action)) { mv.setViewName("validateinvoice"); } else if ("updatestatus".equalsIgnoreCase(action)) { mv.setViewName("updatestatus"); } else if ("viewshipstatus".equalsIgnoreCase(action)) { mv.setViewName("viewshipstatus"); } else if ("createrequest".equalsIgnoreCase(action)) { System.out.println("create request"); //go to database and insert } else if ("newfileSelect".equalsIgnoreCase(action)) { System.out.println("new file select"); String csvFile = request.getParameter("newfile"); if (csvFile != null) { StringBuffer data = readObj.getData(csvFile); System.out.println("printing data:" + data); mv.addObject("data", data); } mv.setViewName("validateinvoice"); //request.setAttribute("selected", "filedata"); } } catch (Exception e) { e.printStackTrace(); } return mv; }
From source file:com.example.data.PetData.java
public List<Map<String, Object>> findPetByStatus(String status) throws SQLException { QueryRunner run = new QueryRunner(H2DB.getDataSource()); String[] statues = status.split(","); String statusInStr = StringUtils.join( Arrays.asList(statues).stream().map(s -> "'" + s.trim() + "'").collect(Collectors.toList()), ","); if (statues.length > 0) { return run .query("select * from pet where status in (" + statusInStr + ")", H2DB.mkResultSetHandler("id", "name", "categoryId", "photoUrls", "tags", "status")) .stream().map(m -> {// w w w.ja v a 2s . com m.put("photoUrls", H2DB.strToList((String) m.get("photoUrls"))); m.put("tags", H2DB.strToList((String) m.get("tags"))); m.put("category", getCategory(run, (Long) m.get("categoryId"))); m.remove("categoryId"); return m; }).collect(Collectors.toList()); } return Collections.EMPTY_LIST; }
From source file:azkaban.scheduler.JdbcScheduleLoader.java
public void insertSchedule(Schedule s, EncodingType encType) throws ScheduleManagerException { String json = JSONUtils.toJSON(s.optionsToObject()); byte[] data = null; try {/* w ww. ja v a 2 s . com*/ byte[] stringData = json.getBytes("UTF-8"); data = stringData; if (encType == EncodingType.GZIP) { data = GZIPUtils.gzipBytes(stringData); } logger.debug("NumChars: " + json.length() + " UTF-8:" + stringData.length + " Gzip:" + data.length); } catch (IOException e) { throw new ScheduleManagerException("Error encoding the schedule options. " + s.getScheduleName()); } QueryRunner runner = createQueryRunner(); try { int inserts = runner.update(INSERT_SCHEDULE, s.getProjectId(), s.getProjectName(), s.getFlowName(), s.getStatus(), s.getFirstSchedTime(), s.getTimezone().getID(), Schedule.createPeriodString(s.getPeriod()), s.getLastModifyTime(), s.getNextExecTime(), s.getSubmitTime(), s.getSubmitUser(), encType.getNumVal(), data); long id = runner.query(LastInsertID.LAST_INSERT_ID, new LastInsertID()); if (id == -1l) { throw new ScheduleManagerException("Execution id is not properly created."); } logger.info("Schedule given " + s.getScheduleIdentityPair() + " given id " + id); s.setScheduleId((int) id); if (inserts == 0) { throw new ScheduleManagerException("No schedule has been inserted."); } } catch (SQLException e) { logger.error(INSERT_SCHEDULE + " failed."); throw new ScheduleManagerException("Insert schedule " + s.getScheduleName() + " into db failed. ", e); } }
From source file:gr.osmosis.rcpsamples.contact.db.derby.DerbyContactsDAO.java
public Contact[] selectContacts(int id) { // Create Query // and where statement String whereStatement = ""; if (id > -1) { whereStatement = " WHERE ID = " + id; }/*from www . java 2s .c om*/ StringBuffer sbSelect = new StringBuffer(); sbSelect.append("SELECT * "); sbSelect.append(" FROM "); sbSelect.append(ContactsConstants.CONTACTS_TABLE_NAME); sbSelect.append(whereStatement); // Create a QueryRunner that will use connections from // the given DataSource DataSource d = DerbyDAOFactory.getDataSource(); QueryRunner run = new QueryRunner(d); ResultSetHandler h = new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { BasicRowProcessor p = new BasicRowProcessor(); List l = p.toBeanList(rs, Contact.class); return l; } }; Object result; ArrayList list; Contact[] c = null; try { result = run.query(sbSelect.toString(), h); list = (ArrayList) result; c = new Contact[list.toArray().length]; list.toArray(c); System.out.print(result.toString()); } catch (SQLException sex) { sex.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } return c; }