List of usage examples for org.apache.commons.dbutils QueryRunner query
public <T> T query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException
From source file:com.pymmasoftware.platform.login.loginmodule.DroolsLoginModule.java
@Override public boolean login() throws LoginException { succeeded = false;//from w ww . j ava2s .c o m QueryRunner queryRunner = null; try { userPrincipal = null; roles = null; if (callbackHandler == null) throw new LoginException("No callback handler"); NameCallback nameCallback = new NameCallback("Username"); PasswordCallback passwordCallback = new PasswordCallback("Password", false); Callback[] callbacks = new Callback[] { nameCallback, passwordCallback }; try { callbackHandler.handle(callbacks); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (UnsupportedCallbackException e) { // TODO Auto-generated catch block e.printStackTrace(); } username = nameCallback.getName(); password = new String(passwordCallback.getPassword()); queryRunner = new QueryRunner(dataSource); // Create a ResultSetHandler implementation to convert the // first row into an Object[]. ResultSetHandler<DroolsPrincipal> h = new ResultSetHandler<DroolsPrincipal>() { public DroolsPrincipal handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; } ResultSetMetaData meta = rs.getMetaData(); String userName = rs.getString("username"); DroolsPrincipal droolsPrincipal = new DroolsPrincipal(userName); droolsPrincipal.setId(rs.getInt("id")); return droolsPrincipal; } }; ResultSetHandler<List<String>> hh = new ResultSetHandler<List<String>>() { public List<String> handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; } List<String> droolsGroups = new ArrayList<>(); boolean goOne = true; while (goOne) { String groupName = rs.getString("groups"); droolsGroups.add(groupName); if (rs.next() == false) { goOne = false; } } return droolsGroups; } }; String sqlname = "select * from guvnorusers where username = ? and password = ? "; DroolsPrincipal user = queryRunner.query(sqlname, h, username, password); if (user == null) { succeeded = false; throw new FailedLoginException("The username or The password is incorrect"); } else { userPrincipal = user; String sqlname2 = "select groups from guvnorgroups gr,guvnorusers_groups gr_user " + "where gr.id = gr_user.groups_id " + "and gr_user.guvnorusers_id= ?"; List<String> droolsGroups = queryRunner.query(sqlname2, hh, user.getId()); if (droolsGroups != null) { int i = droolsGroups.size(); roles = new String[i]; i = 0; for (String droolsGroup : droolsGroups) { roles[i] = droolsGroup; i++; } } succeeded = true; return true; } } catch (Exception e) { throw new LoginException(e.getMessage()); } finally { queryRunner = null; } }
From source file:nl.b3p.catalog.arcgis.ArcSDE9xJDBCHelper.java
private Integer determineDatasetType(Connection c, ArcSDEJDBCDataset dataset) throws Exception { // determine dataset type (esriDatasetType) required for new row // in GDB_USERMETADATA. // I guess there is a dataset type for each OBJECTCLASSES.CLSID // value. Don't hardcode those here but make some effort try to find // rows in tables for feature datasets, feature classes and raster // catalogs// w w w . java 2s . c o m // will not work for all datasets QueryRunner runner = new QueryRunner(); Integer datasetType = null; Object[] datasetParams = dataset.getDatabaseName() != null ? new Object[] { dataset.getOwner(), dataset.getName(), dataset.getDatabaseName() } : new Object[] { dataset.getOwner(), dataset.getName() }; // is it a feature dataset? Object isDataset = runner.query(c, "select 1 from " + getTableName(TABLE_FEATUREDATASET) + " where owner = ? and name = ?" + databaseNameSQL(dataset), new ScalarHandler(), datasetParams); if (isDataset != null) { return esriDatasetType.esriDTFeatureDataset; } // check the feature type Integer featureType = (Integer) runner.query(c, "select fc.FeatureType from " + getTableName(TABLE_OBJECTCLASSES) + " oc " + "join " + getTableName(TABLE_FEATURECLASSES) + " fc on (fc.ObjectClassId = oc.ID) " + "where oc.Owner = ? and oc.Name = ?" + databaseNameSQL(dataset, "oc"), new ScalarHandler(), datasetParams); if (featureType == null) { throw new Exception( "Cannot find row in " + TABLE_FEATURECLASSES + " table for dataset " + dataset.getFullName()); } if (featureType == esriFeatureType.esriFTSimple) { // XXX maybe other feature types beside esriFTSimple are feature classes as well? datasetType = esriDatasetType.esriDTFeatureClass; } else if (featureType == esriFeatureType.esriFTRasterCatalogItem) { Integer isRasterDataset = (Integer) runner.query(c, "select rc.isRasterDataset from " + getTableName(TABLE_RASTERCATALOGS) + " rc " + "join " + getTableName(TABLE_OBJECTCLASSES) + " oc on (rc.ObjectClassID = oc.ID) " + "where oc.Owner = ? and oc.Name = ?" + databaseNameSQL(dataset, "oc"), new ScalarHandler(), datasetParams); if (isRasterDataset == null) { throw new Exception("Cannot find row in " + TABLE_RASTERCATALOGS + " table for dataset " + dataset.getFullName()); } datasetType = isRasterDataset == 1 ? esriDatasetType.esriDTRasterDataset : esriDatasetType.esriDTRasterCatalog; } else { // give up throw new Exception("Don't know the dataset type for feature type " + featureType + " for dataset " + dataset.getFullName()); } return datasetType; }
From source file:nl.opengeogroep.dbk.DBKAPI.java
/** * Process the call to /api/features.json[?srid=<integer>] * @param request The requestobject/* ww w . j a va 2s . c o m*/ * @return A JSONObject with the GeoJSON representation of all the DBK's * @throws SQLException */ private JSONObject processFeatureRequest(HttpServletRequest request) throws SQLException, Exception { JSONObject geoJSON = new JSONObject(); JSONArray jFeatures = new JSONArray(); boolean hasParameter = request.getParameter(PARAMETER_SRID) != null; Connection conn = getConnection(); if (conn == null) { throw new Exception("Connection could not be established"); } MapListHandler h = new MapListHandler(); QueryRunner run = new QueryRunner(); geoJSON.put("type", "FeatureCollection"); geoJSON.put("features", jFeatures); try { List<Map<String, Object>> features; if (hasParameter) { String sridString = request.getParameter(PARAMETER_SRID); Integer srid = Integer.parseInt(sridString); features = run.query(conn, "select \"feature\" from dbk.dbkfeatures_json(?)", h, srid); } else { features = run.query(conn, "select \"feature\" from dbk.dbkfeatures_json()", h); } for (Map<String, Object> feature : features) { JSONObject jFeature = processFeature(feature); jFeatures.put(jFeature); } } finally { DbUtils.close(conn); } return geoJSON; }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}//from w w w.j a va 2s. com */ @Override public synchronized List<String> getImportsForFile(String filePath, String repository) throws DatabaseAccessException { ResultSetHandler<List<String>> handler = new ResultSetHandler<List<String>>() { @Override public List<String> handle(ResultSet rs) throws SQLException { List<String> imports = new LinkedList<String>(); while (rs.next()) { imports.add(rs.getString("target_file_path")); } return imports; } }; QueryRunner run = new QueryRunner(dataSource); try { return run.query(STMT_GET_IMPORTS_FOR_FILE, handler, repository, filePath); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}/* w w w .j av a 2 s . c o m*/ */ @Override @SuppressWarnings("unchecked") public synchronized List<Usage> getUsagesForFile(String filePath, String repository) throws DatabaseAccessException { ResultSetHandler<byte[]> h = new SingleValueByteArrayHandler(); try { QueryRunner run = new QueryRunner(dataSource); byte[] result = run.query(STMT_GET_USAGES_FOR_FILE, h, filePath, repository); if (result != null) { ObjectInputStream regObjectStream = new ObjectInputStream(new ByteArrayInputStream(result)); return (List<Usage>) regObjectStream.readObject(); } else { return null; } } catch (IOException ex) { throw new DatabaseAccessException("The content of the blob storing the usages of the file " + filePath + " repository " + repository + " could not be parsed to an Object, the database content is probably corrupt"); } catch (ClassNotFoundException ex) { throw new DatabaseAccessException("The content of the blob storing the usages of the file " + filePath + " repository " + repository + " could not be parsed to an Object, the database content is probably corrupt"); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}/* w w w .j a v a 2 s .c o m*/ */ @Override public synchronized String getFilePathForTypeDeclaration(String fullyQualifiedName, String repository) throws DatabaseAccessException { ResultSetHandler<String> handler = new SingleValueStringHandler(); QueryRunner run = new QueryRunner(dataSource); try { return run.query(STMT_GET_FILE_PATH_FOR_TYPE_DECLARATION, handler, repository, fullyQualifiedName); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}//from www.j a va2s. co m */ @Override public synchronized AstNode getBinaryIndexForFile(String filePath, String repository) throws DatabaseAccessException { ObjectInputStream regObjectStream = null; SingleValueByteArrayHandler h = new SingleValueByteArrayHandler(); QueryRunner run = new QueryRunner(dataSource); try { byte[] result = run.query(STMT_GET_BINARY_INDEX_FOR_FILE, h, filePath, repository); if (result != null) { ByteArrayInputStream regArrayStream = new ByteArrayInputStream(result); regObjectStream = new ObjectInputStream(regArrayStream); return (AstNode) regObjectStream.readObject(); } else { return null; } } catch (ClassNotFoundException ex) { throw new DatabaseAccessException("The content of the blob storing the binary index of file " + filePath + " repository " + repository + " could not be parsed to an Object, the database content is probably corrupt"); } catch (IOException ex) { throw new DatabaseAccessException( "Could not create a ByteArrayInputStream from the content of the binaryIndex field in the database"); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while accessing the DB\n" + ex); } finally { try { if (regObjectStream != null) { regObjectStream.close(); } } catch (IOException ex) { throw new DatabaseAccessException( "Could not close the stream used to retrieve the content of the binary-index field\n" + ex); } } }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * {@inheritDoc}//from w w w . j a v a 2s.com */ @Override public synchronized Map<String, AstNode> getFilesImportingTargetFile(String targetFileName, String targetRepositoryName) throws DatabaseAccessException { Map<String, AstNode> files = new HashMap<String, AstNode>(); ObjectInputStream regObjectStream; ResultSetHandler<Map<String, byte[]>> h = new ResultSetHandler<Map<String, byte[]>>() { @Override public Map<String, byte[]> handle(ResultSet rs) throws SQLException { Map<String, byte[]> results = new HashMap<String, byte[]>(); while (rs.next()) { results.put(rs.getString(1), rs.getBytes(2)); } return results; } }; QueryRunner run = new QueryRunner(dataSource); String targetPackageName = targetFileName.substring(0, targetFileName.lastIndexOf('.')) + ".*"; try { Map<String, byte[]> results = run.query(STMT_GET_FILES_IMPORTING_FILE, h, targetFileName, targetPackageName); for (Map.Entry<String, byte[]> entry : results.entrySet()) { ByteArrayInputStream regArrayStream = new ByteArrayInputStream(entry.getValue()); regObjectStream = new ObjectInputStream(regArrayStream); AstNode binaryIndex = (AstNode) regObjectStream.readObject(); files.put(entry.getKey(), binaryIndex); } } catch (ClassNotFoundException ex) { throw new DatabaseAccessException( "The content of the blob storing the binary_index of a file could not be parsed to an Object, the database content is probably corrupt"); } catch (IOException ex) { throw new DatabaseAccessException( "Could not create a ByteArrayInputStream from the content of the binaryIndex field in the database"); } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } return files; }
From source file:org.codesearch.commons.database.DBAccessImpl.java
/** * returns the id for the file//from w ww . j ava 2 s . c om * * @param filePath the complete name of the file * @param repoId the id of the repository holding the file * @return the id if the file, or -1 if it wasn't found * @throws DatabaseAccessException */ private int getFileIdForFileName(String filePath, String repository) throws DatabaseAccessException { int fileId = -1; QueryRunner run = new QueryRunner(dataSource); SingleValueStringHandler h = new SingleValueStringHandler(); try { String result = run.query(STMT_GET_FILE_ID_FOR_FILE_NAME, h, filePath, repository); if (StringUtils.isNotEmpty(result)) { fileId = Integer.parseInt(result); } } catch (SQLException ex) { throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex); } return fileId; }
From source file:org.dbmfs.DatabaseAccessor.java
/** * ???????????.<br>//from ww w.j a va2 s . co m * * @param targetTableName ?? * @param pKeyConcatStr (?) * @retrun */ public boolean exsistData(String targetTableName, String pKeyConcatStr) throws Exception { boolean ret = false; try { // ? List<String> primaryKeyColumnNames = getPrimaryKeyColumnNames(targetTableName); Map<String, Map<String, Object>> allColumnMeta = getAllColumnMeta(targetTableName, true); if (primaryKeyColumnNames == null || primaryKeyColumnNames.size() == 0) return false; // ? pKeyConcatStr = pKeyConcatStr.replace(".json", ""); String[] keyStrSplit = pKeyConcatStr.split(primaryKeySep); if (keyStrSplit.length != primaryKeyColumnNames.size()) return false; // ?? StringBuilder queryBuf = new StringBuilder(); queryBuf.append("select count(*) as cnt from "); queryBuf.append(targetTableName); queryBuf.append(" where "); // ()? Object[] params = new Object[primaryKeyColumnNames.size()]; String whereSep = ""; for (int idx = 0; idx < primaryKeyColumnNames.size(); idx++) { Map<String, Object> meta = allColumnMeta.get(primaryKeyColumnNames.get(idx)); params[idx] = DbmfsUtil.deserializeType(keyStrSplit[idx], (String) meta.get("javaTypeName")); queryBuf.append(whereSep); queryBuf.append(primaryKeyColumnNames.get(idx)); queryBuf.append(" = ? "); whereSep = " and "; } ResultSetHandler<?> resultSetHandler = new MapListHandler(); QueryRunner qr = new QueryRunner(); // //System.out.println(queryBuf.toString()); //System.out.println(params[0]); List<Map<String, Object>> queryResult = (List<Map<String, Object>>) qr.query(injectConn, queryBuf.toString(), resultSetHandler, params); Map countRet = queryResult.get(0); Long count = (Long) countRet.get("cnt"); if (count.longValue() == 1) ret = true; } catch (Exception e) { e.printStackTrace(); throw e; } return ret; }