List of usage examples for org.apache.commons.dbutils ResultSetHandler ResultSetHandler
ResultSetHandler
From source file:esg.node.util.migrate.UserMigrationTool.java
public int migrateUsers() { int ret = 0;/*from w w w .j a v a 2s . co m*/ ResultSetHandler<Integer> usersResultSetHandler = new ResultSetHandler<Integer>() { public Integer handle(ResultSet rs) throws SQLException { int i = 0; int migrateCount = 0; int transCount = 0; int errorCount = 0; String currentUsername = null; String openid = null; String target = null; while (rs.next()) { try { currentUsername = rs.getString("username"); if (currentUsername != null && currentUsername.equals("rootAdmin")) { System.out.println("NOTE: Will not overwrite local rootAdmin information"); continue; } openid = rs.getString("openid"); log.trace("Inspecting openid: " + openid); UserInfo userInfo = UserMigrationTool.this.userDAO.getUserById(openid); userInfo.setFirstName(rs.getString("firstname")). //setMiddleName(rs.getString("middlename")). setLastName(rs.getString("lastname")).setEmail(rs.getString("email")) .setDn(rs.getString("dn")).setOrganization(rs.getString("organization")). //setOrgType(rs.getString("organization_type")). setCity(rs.getString("city")).setState(rs.getString("state")) .setCountry(rs.getString("country")); //NOTE: verification token not applicable //Status code msut be set separately... (below) field #13 //Password literal must be set separately... (see setPassword - with true boolean, below) field #14 if (currentUsername != null) userInfo.setUserName(currentUsername); if (userInfo.getOpenid().matches("http.*" + UserMigrationTool.this.source + ".*")) { if (verbatimMigration) { UserMigrationTool.this.userDAO.addUser(userInfo); UserMigrationTool.this.userDAO.setPassword(userInfo.getOpenid(), rs.getString("password"), true); //password (literal) System.out.println("**Migrated User #" + i + ": " + userInfo.getUserName() + " (" + openid + ") --> " + userInfo.getOpenid()); } userInfo.setOpenid(null); //This will cause the DAO to generate a local Openid UserMigrationTool.this.userDAO.addUser(userInfo); //UserMigrationTool.this.userDAO.setStatusCode(userInfo.getOpenid(),rs.getInt(13)); //statusCode UserMigrationTool.this.userDAO.setPassword(userInfo.getOpenid(), rs.getString("password"), true); //password (literal) System.out.println("Migrated User #" + i + ": " + userInfo.getUserName() + " (" + openid + ") --> " + userInfo.getOpenid()); migrateCount++; } else { UserMigrationTool.this.userDAO.addUser(userInfo); System.out.println("Transferred User #" + i + ": " + userInfo.getUserName() + " --> " + userInfo.getOpenid()); transCount++; } i++; } catch (Throwable t) { log.error("Sorry, could NOT migrate/transfer user: " + currentUsername); errorCount++; } } log.info("Inspected " + i + " User Records: Migrated " + migrateCount + ", Transferred " + transCount + ", with " + errorCount + " failed"); return i; } }; try { ret = queryRunner.query(sourceUserInfoQuery, usersResultSetHandler); log.info("Migrated [" + ret + "] user records"); } catch (SQLException e) { e.printStackTrace(); } return ret; }
From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java
/** * result to html_table//from ww w. j a v a2s . co m * * @param userDB * @param strQuery * @param listParam * @return * @throws Exception */ @SuppressWarnings("deprecation") public static String selectToHTML_TABLE(final UserDBDAO userDB, final String strQuery, final List<Object> listParam) throws Exception { SqlMapClient client = TadpoleSQLManager.getInstance(userDB); QueryRunner qr = new QueryRunner(client.getDataSource()); Object strHTMLTable = qr.query(strQuery, listParam.toArray(), new ResultSetHandler<Object>() { @Override public Object handle(ResultSet rs) throws SQLException { try { return ResultSetUtil.makeResultSetTOHTML(rs, 1000); } catch (Exception e) { return e.getMessage(); } } }); return strHTMLTable.toString(); }
From source file:esg.node.security.UserInfoDAO.java
public void init() { this.idResultSetHandler = new ResultSetHandler<Integer>() { public Integer handle(ResultSet rs) throws SQLException { if (!rs.next()) { return -1; }// ww w . jav a 2s .c o m return rs.getInt(1); } }; this.booleanResultSetHandler = new ResultSetHandler<Boolean>() { public Boolean handle(ResultSet rs) throws SQLException { if (!rs.next()) { return false; } return rs.getBoolean(1); } }; this.existsResultSetHandler = new ResultSetHandler<Boolean>() { public Boolean handle(ResultSet rs) throws SQLException { if (!rs.next()) { return false; } return (rs.getInt(1) > 0); } }; this.singleStringResultSetHandler = new ResultSetHandler<String>() { public String handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; } return rs.getString(1); } }; passwordQueryHandler = new ResultSetHandler<String>() { public String handle(ResultSet rs) throws SQLException { String password = null; while (rs.next()) { password = rs.getString(1); } return password; } }; //To handle the single record result userInfoResultSetHandler = new ResultSetHandler<UserInfo>() { public UserInfo handle(ResultSet rs) throws SQLException { UserInfo userInfo = null; while (rs.next()) { userInfo = new UserInfo(); userInfo.setid(rs.getInt(1)).setOpenid(rs.getString(2)).setFirstName(rs.getString(3)) .setMiddleName(rs.getString(4)).setLastName(rs.getString(5)) .setUserName(rs.getString(6)).setEmail(rs.getString(7)).setDn(rs.getString(8)) .setOrganization(rs.getString(9)).setOrgType(rs.getString(10)).setCity(rs.getString(11)) .setState(rs.getString(12)).setCountry(rs.getString(13)).setStatusCode(rs.getInt(14)); } return userInfo; } }; userPermissionsResultSetHandler = new ResultSetHandler<Map<String, Set<String>>>() { Map<String, Set<String>> permissions = new HashMap<String, Set<String>>(); Set<String> roleSet = null; public Map<String, Set<String>> handle(ResultSet rs) throws SQLException { permissions.clear(); if (!rs.next()) { return permissions; } do { addPermission(rs.getString(1), rs.getString(2)); } while (rs.next()); return permissions; } public void addPermission(String groupName, String roleName) { //lazily instantiate the set of values for group if not //there if ((roleSet = permissions.get(groupName)) == null) { roleSet = new HashSet<String>(); } //enter group associated with group value set roleSet.add(roleName); permissions.put(groupName, roleSet); } }; basicResultSetHandler = new ResultSetHandler<List<String[]>>() { public List<String[]> handle(ResultSet rs) throws SQLException { ArrayList<String[]> results = new ArrayList<String[]>(); String[] record = null; assert (null != results); ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); log.trace("Number of fields: " + cols); log.trace("adding column data..."); record = new String[cols]; for (int i = 0; i < cols; i++) { try { record[i] = meta.getColumnLabel(i + 1); } catch (SQLException e) { log.error(e); } } results.add(record); for (int i = 0; rs.next(); i++) { log.trace("Looking at record " + (i + 1)); record = new String[cols]; for (int j = 0; j < cols; j++) { record[j] = rs.getString(j + 1); log.trace("gathering result record column " + (j + 1) + " -> " + record[j]); } log.trace("adding record "); results.add(record); record = null; //gc courtesy } return results; } }; new InitAdmin(); }
From source file:esg.node.util.migrate.UserMigrationTool.java
public int migratePermissions() { int ret = 0;//from w ww .jav a 2 s . c o m ResultSetHandler<Integer> permissionsResultSetHandler = new ResultSetHandler<Integer>() { public Integer handle(ResultSet rs) throws SQLException { int i = 0; String oid = null; String gname = null; String rname = null; int errorCount = 0; int migrateCount = 0; while (rs.next()) { try { if (verbatimMigration) { oid = rs.getString(1); gname = transformGroupName(rs.getString(2)); rname = transformRoleName(rs.getString(3)); if (UserMigrationTool.this.userDAO.addPermissionByOpenid(oid, gname, rname)) { migrateCount++; System.out.println("**Migrated Permission #" + i + ": oid[" + oid + "] [" + gname + "] [" + rname + "]"); } } oid = transformOpenid(rs.getString(1)); gname = transformGroupName(rs.getString(2)); rname = transformRoleName(rs.getString(3)); if (UserMigrationTool.this.userDAO.addPermissionByOpenid(oid, gname, rname)) { migrateCount++; System.out.println("Migrated Permission #" + i + ": oid[" + oid + "] [" + gname + "] [" + rname + "]"); } } catch (ESGFDataAccessException e) { log.error("Sorry, could NOT create permission tuple: oid[" + oid + "] g[" + gname + "] r[" + rname + "] "); errorCount++; } i++; } log.info("Inspected " + i + " Permission Records: Migrated " + migrateCount + ", with " + errorCount + " failed"); return i; } }; try { ret = queryRunner.query(sourcePermissionInfoQuery, permissionsResultSetHandler); log.info("Migrated [" + ret + "] permission records"); } catch (SQLException e) { e.printStackTrace(); } return ret; }
From source file:net.sourceforge.seqware.common.metadata.MetadataWSTest.java
protected void testTimestamp(String sql, final String colname, Date beforeDate) { logger.debug(sql);// w ww . ja v a2 s .c o m try { Date date = DBAccess.get().executeQuery(sql, new ResultSetHandler<Date>() { @Override public Date handle(ResultSet rs) throws SQLException { if (rs.next()) { return rs.getTimestamp(colname); } else { return null; } } }); if (date != null) { if (date.before(beforeDate)) { logger.debug("before " + beforeDate.toString()); logger.debug("update " + date.toString()); Assert.fail("Update failed."); } } else { Assert.fail("No rows in ResultSet"); } } catch (SQLException ex) { ex.printStackTrace(); Assert.fail("SQL Exception"); } finally { DBAccess.close(); } }
From source file:net.sourceforge.seqware.common.metadata.MetadataWSTest.java
protected void testCount(String sql, int expectedCount) { logger.debug(sql);// w ww. java 2 s. c om try { int count = DBAccess.get().executeQuery(sql, new ResultSetHandler<Integer>() { @Override public Integer handle(ResultSet rs) throws SQLException { if (rs.next()) { return rs.getInt("count"); } else { return 0; } } }); if (count > 0) { Assert.assertEquals("Expected count is not the same:" + expectedCount + "!=<" + count, true, (expectedCount <= count)); } else { Assert.fail("No rows in ResultSet"); } } catch (SQLException ex) { ex.printStackTrace(); Assert.fail("SQL Exception"); } finally { DBAccess.close(); } }
From source file:net.sourceforge.seqware.webservice.resources.queries.GenericDBResource.java
/** {@inheritDoc} */ @Override/* w w w . j a va2 s . c om*/ public void handle(Request request, Response response) { authenticate(request.getChallengeResponse().getIdentifier()); String query = request.getEntityAsText(); if (query == null || query.trim().isEmpty()) { response.setEntity("<?xml version=\"1.0\"?>" + "<version major=\"0\" minor=\"11\" patch=\"0\"/>", MediaType.TEXT_XML); return; } if (request.getMethod() == Method.GET) { try { String table = DBAccess.get().executeQuery(query, new ResultSetHandler<String>() { @Override public String handle(ResultSet rs) throws SQLException { return printResultSet(rs); } }); response.setEntity(table, MediaType.TEXT_PLAIN); } catch (SQLException ex) { ex.printStackTrace(); response.setStatus(Status.SERVER_ERROR_INTERNAL, ex); } finally { DBAccess.close(); } } else if (request.getMethod() == Method.PUT) { try { int rows = DBAccess.get().executeUpdate(query); String reply = "Statement: " + query + "\n\t" + rows + " rows were modified"; response.setEntity(reply, MediaType.TEXT_PLAIN); } catch (SQLException ex) { ex.printStackTrace(); response.setStatus(Status.SERVER_ERROR_INTERNAL, ex); } finally { DBAccess.close(); } } }
From source file:net.sourceforge.seqware.webservice.resources.tables.FileChildWorkflowRunsResource.java
/** * Use SQL to directly retrieve relevant workflows runs (defined as any workflow runs that * include one or more files in the set we were given) * @param files//from w w w . ja v a 2 s. c o m * @param interestingWorkflows * @return * @throws SQLException */ protected static WorkflowRunList2 directRetrieveWorkflowRuns(List<Integer> files, List<Integer> interestingWorkflows) throws SQLException { final Hibernate3DtoCopier copier = new Hibernate3DtoCopier(); final WorkflowRunList2 runs = new WorkflowRunList2(); runs.setList(new ArrayList()); if (files.size() > 0) { ResultSet rs = null; MetadataDB mdb = null; try { WorkflowRunService ss = BeanFactory.getWorkflowRunServiceBean(); StringBuilder query = new StringBuilder(); query.append("select distinct r.sw_accession from workflow_run r, workflow w, "); query.append("workflow_run_input_files rf, file f WHERE r.workflow_run_id = rf.workflow_run_id " + "AND rf.file_id = f.file_id " + "AND w.workflow_id = r.workflow_id " + "AND ("); // handle file accessions for (int i = 0; i < files.size() - 1; i++) { Integer fInt = files.get(i); query.append(" f.sw_accession = ").append(fInt).append(" OR"); } Integer fInt = files.get(files.size() - 1); query.append(" f.sw_accession = ").append(fInt).append(")"); // handle interesting workflow accessions if (interestingWorkflows.size() > 0) { query.append(" AND ("); for (int i = 0; i < interestingWorkflows.size() - 1; i++) { Integer wInt = interestingWorkflows.get(i); query.append(" w.sw_accession = ").append(wInt).append(" OR"); } Integer wInt = interestingWorkflows.get(interestingWorkflows.size() - 1); query.append(" w.sw_accession = ").append(wInt).append(")"); } query.append(" ORDER BY sw_accession"); Log.info("Executing query: " + query); mdb = DBAccess.get(); List<Integer> workflowSWIDs = mdb.executeQuery(query.toString(), new ResultSetHandler<List<Integer>>() { @Override public List<Integer> handle(ResultSet rs) throws SQLException { List<Integer> ids = new ArrayList<>(); while (rs.next()) { ids.add(rs.getInt("sw_accession")); } return ids; } }); for (int workflowSWID : workflowSWIDs) { WorkflowRun workflowRun = (WorkflowRun) testIfNull(ss.findBySWAccession(workflowSWID)); CollectionPropertyName<WorkflowRun>[] createCollectionPropertyNames = CollectionPropertyName .createCollectionPropertyNames(WorkflowRun.class, new String[] { "inputFileAccessions" }); WorkflowRun dto = copier.hibernate2dto(WorkflowRun.class, workflowRun, ArrayUtils.EMPTY_CLASS_ARRAY, createCollectionPropertyNames); runs.add(dto); } } finally { if (mdb != null) { DbUtils.closeQuietly(mdb.getDb(), mdb.getSql(), rs); } DBAccess.close(); } } return runs; }
From source file:nl.b3p.catalog.arcgis.ArcSDE10JDBCHelper.java
@Override public List<Dir> getFeatureDatasets(final String currentPath) throws NamingException, SQLException { Connection c = getConnection(); try {/* w w w. j a v a 2 s . c o m*/ // Nested featuredatasets are not possible to create with ArcCatalog, // so no need to check relationships String sql = "select i.objectid, i.name from " + getTableName(TABLE_ITEMS) + " i where type = ?"; ResultSetHandler<List<Dir>> h = new ResultSetHandler<List<Dir>>() { public List<Dir> handle(ResultSet rs) throws SQLException { List<Dir> l = new ArrayList<Dir>(); while (rs.next()) { l.add(new Dir(rs.getString(2) + "", currentPath + rs.getInt(1))); } return l; } }; return new QueryRunner().query(c, sql, h, TYPE_FEATURE_DATASET); } finally { DbUtils.closeQuietly(c); } }
From source file:nl.b3p.catalog.arcgis.ArcSDE10JDBCHelper.java
@Override public List<DirEntry> getFeatureClasses(final String currentPath, ArcSDEJDBCDataset parent) throws NamingException, SQLException { Connection c = getConnection(); try {/*from w w w . j a va 2 s . c o m*/ String sql = "select i.objectid, i.name from " + getTableName(TABLE_ITEMS) + " i " + "join " + getTableName(TABLE_ITEMRELATIONSHIPS) + " r on (r.destid = i.uuid) " + "join " + getTableName(TABLE_ITEMS) + " parent_i on (parent_i.uuid = r.originid) " + "where i.type in (?,?) "; if (parent == null) { sql += "and parent_i.path = '\\'"; } else { sql += "and parent_i.objectid = ?"; } ResultSetHandler<List<DirEntry>> h = new ResultSetHandler<List<DirEntry>>() { public List<DirEntry> handle(ResultSet rs) throws SQLException { List<DirEntry> l = new ArrayList<DirEntry>(); while (rs.next()) { l.add(new DirEntry(rs.getString(2) + "", currentPath + rs.getInt(1))); } return l; } }; if (parent == null) { return new QueryRunner().query(c, sql, h, TYPE_FEATURE_CLASS, TYPE_RASTER); } else { return new QueryRunner().query(c, sql, h, TYPE_FEATURE_CLASS, TYPE_RASTER, parent.getObjectID()); } } finally { DbUtils.closeQuietly(c); } }