List of usage examples for org.apache.commons.dbutils ResultSetHandler ResultSetHandler
ResultSetHandler
From source file:org.gaixie.jibu.security.dao.impl.RoleDAODerby.java
public List<String> findByUsername(Connection conn, String username) throws SQLException { ResultSetHandler<List<String>> h = new ResultSetHandler<List<String>>() { public List<String> handle(ResultSet rs) throws SQLException { List<String> result = new ArrayList<String>(); while (rs.next()) { result.add(rs.getString(1)); }// w w w . java 2 s. c o m int len = result.size(); if (len <= 0) return null; return result; } }; return run.query(conn, "SELECT parent.name " + " FROM roles AS node, roles AS parent, user_role_map AS urm, userbase u " + " WHERE node.id = urm.role_id " + " AND node.lft BETWEEN parent.lft AND parent.rgt " + " AND u.id = urm.user_id " + " AND u.username = ? ", h, username); }
From source file:org.gaixie.jibu.security.dao.impl.RoleDAOOracle.java
public List<String> findByAuthid(Connection conn, int id) throws SQLException { ResultSetHandler<List<String>> h = new ResultSetHandler<List<String>>() { public List<String> handle(ResultSet rs) throws SQLException { List<String> result = new ArrayList<String>(); while (rs.next()) { result.add(rs.getString(1)); }/*from ww w . j a v a 2s . c om*/ return result; } }; return run.query(conn, "SELECT r.name " + " FROM roles r, role_authority_map ram " + " WHERE r.id = ram.role_id " + " AND ram.authority_id =? ", h, id); }
From source file:org.gaixie.jibu.security.dao.impl.RoleDAOOracle.java
public List<String> findByUsername(Connection conn, String username) throws SQLException { ResultSetHandler<List<String>> h = new ResultSetHandler<List<String>>() { public List<String> handle(ResultSet rs) throws SQLException { List<String> result = new ArrayList<String>(); while (rs.next()) { result.add(rs.getString(1)); }//from ww w. j a v a 2 s . co m int len = result.size(); if (len <= 0) return null; return result; } }; return run.query(conn, "SELECT parent.name " + " FROM roles node, roles parent, user_role_map urm, userbase u " + " WHERE node.id = urm.role_id " + " AND node.lft BETWEEN parent.lft AND parent.rgt " + " AND u.id = urm.user_id " + " AND u.username = ? ", h, username); }
From source file:org.ice.etl.myetl.pool.connection.MyConnectionTest.java
@Test public void testGetConnection() throws SQLException, ClassNotFoundException, PropertyVetoException { // MyConnection connection = new MyConnection(); // Connection c = connection.getConnection(); // PreparedStatement ps = c.prepareStatement("select count(1) from test.soccer;"); // ResultSet rs = ps.executeQuery(); // while (rs.next()) { // System.out.println(rs.getString(1)); // }/*from ww w . j av a 2 s . co m*/ ResultSetHandler<Object[]> rsh = new ResultSetHandler<Object[]>() { @Override public Object[] handle(ResultSet rs) throws SQLException { if (!rs.next()) { return null; } ResultSetMetaData meta = rs.getMetaData(); int cols = meta.getColumnCount(); Object[] objs = new Object[cols]; for (int i = 0; i < cols; i++) { objs[i] = rs.getObject(i + 1); } return objs; // throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } }; ComboPooledDataSource dataSource = new ComboPooledDataSource(); dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql://localhost"); dataSource.setUser("root"); dataSource.setPassword(""); QueryRunner qr = new QueryRunner(dataSource); Object[] result = qr.query("SELECT count(1) FROM test.soccer", rsh); System.out.println(result[0]); }
From source file:org.openconcerto.erp.core.finance.tax.model.TaxeCache.java
private TaxeCache() { final DBRoot root = ((ComptaPropsConfiguration) Configuration.getInstance()).getRootSociete(); final SQLTable table = root.getTable("TAXE"); final SQLSelect sel = new SQLSelect(table.getBase()); sel.addSelect(table.getField("ID_TAXE")); sel.addSelect(table.getField("TAUX")); final String req = sel.asString(); root.getDBSystemRoot().getDataSource().execute(req, new ResultSetHandler() { public Object handle(final ResultSet resultSet) throws SQLException { while (resultSet.next()) { final int idTaxe = resultSet.getInt(1); final Float resultTaux = Float.valueOf(resultSet.getFloat(2)); TaxeCache.this.mapTaux.put(Integer.valueOf(idTaxe), resultTaux); }/*from ww w. j a v a 2s . c om*/ return null; } }); }
From source file:org.openconcerto.sql.changer.convert.TextDefault.java
@SuppressWarnings("unchecked") @Override//from w ww . ja v a 2 s.co m protected void changeImpl(SQLTable t) throws SQLException { final String infoSchema = SQLSelect.quote( "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE from information_schema.COLUMNS where TABLE_SCHEMA=%s and TABLE_NAME=%s", t.getBase().getName(), t.getName()); final Map<String, Object> defaults = (Map<String, Object>) this.getDS().execute(infoSchema, new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { final Map<String, Object> res = new HashMap<String, Object>(); while (rs.next()) { res.put(rs.getString("COLUMN_NAME"), rs.getObject("COLUMN_DEFAULT")); } return res; } }); for (final SQLField f : t.getFields()) { if (f.getType().getJavaType().equals(String.class) && Boolean.FALSE.equals(f.isNullable()) && defaults.get(f.getName()) == null) { final String req = "ALTER TABLE " + SQLBase.quoteIdentifier(t.getName()) + " MODIFY COLUMN " + SQLBase.quoteIdentifier(f.getName()) + " " + f.getType().getTypeName() + "(" + f.getType().getSize() + ") NOT NULL DEFAULT ''"; System.err.println(req); this.getDS().execute(req); } } }
From source file:org.openconcerto.sql.model.SQLBase.java
Map<String, String> getFwkMetadata(final Collection<String> schemas, final String name) { if (schemas.isEmpty()) return Collections.emptyMap(); final Map<String, String> res = new LinkedHashMap<String, String>(); CollectionUtils.fillMap(res, schemas); final ResultSetHandler rsh = new ResultSetHandler() { @Override/*w w w .jav a 2s. co m*/ public Object handle(ResultSet rs) throws SQLException { while (rs.next()) { res.put(rs.getString(1), rs.getString(2)); } return null; } }; try { if (this.getDataSource().getTransactionPoint() == null) { exec(schemas, name, rsh); } else { // If already in a transaction, don't risk aborting it if a table doesn't exist. // (it's not strictly required for H2 and MySQL, since the transaction is *not* // aborted) SQLUtils.executeAtomic(this.getDataSource(), new ConnectionHandlerNoSetup<Object, SQLException>() { @Override public Object handle(SQLDataSource ds) throws SQLException { exec(schemas, name, rsh); return null; } }, false); } } catch (Exception exn) { final SQLException sqlExn = SQLUtils.findWithSQLState(exn); final boolean tableNotFound = sqlExn != null && (sqlExn.getSQLState().equals("42S02") || sqlExn.getSQLState().equals("42P01")); if (!tableNotFound) throw new IllegalStateException("Not a missing table exception", sqlExn); // The following fall back should not currently be needed since the table is created // by JDBCStructureSource.getNames(). Even without that most DB should contain the // metadata tables. // if only one schema, there's no ambiguity : just return null value // otherwise retry with each single schema to find out which ones are missing if (schemas.size() > 1) { // this won't loop indefinetly since schemas.size() will be 1 for (final String schema : schemas) res.put(schema, this.getFwkMetadata(schema, name)); } } return res; }
From source file:org.openconcerto.sql.model.SQLRow.java
public Set<List<SQLRow>> getRowsOnPath(final Path p, final List<? extends Collection<String>> fields) { final int pathSize = p.length(); if (pathSize == 0) throw new IllegalArgumentException("path is empty"); if (pathSize != fields.size()) throw new IllegalArgumentException( "path and fields size mismatch : " + pathSize + " != " + fields.size()); if (p.getFirst() != this.getTable()) throw new IllegalArgumentException( "path doesn't start with us : " + p.getFirst() + " != " + this.getTable()); final Set<List<SQLRow>> res = new LinkedHashSet<List<SQLRow>>(); final DBSystemRoot sysRoot = this.getTable().getDBSystemRoot(); Where where = sysRoot.getGraph().getJointure(p); // ne pas oublier de slectionner notre ligne where = where.and(this.getWhere()); final SQLSelect select = new SQLSelect(); final List<Collection<String>> fieldsCols = new ArrayList<Collection<String>>(pathSize); for (int i = 0; i < pathSize; i++) { final Collection<String> tableFields = fields.get(i); // +1 car p contient cette ligne final SQLTable t = p.getTable(i + 1); final Collection<String> fieldsCol; if (tableFields == null) { fieldsCol = t.getFieldsName(); } else {/*from w w w . j ava2 s . c om*/ fieldsCol = tableFields; } fieldsCols.add(fieldsCol); // les tables qui ne nous interessent pas if (fieldsCol.size() > 0) { // toujours mettre l'ID select.addSelect(t.getKey()); // plus les champs demands select.addAllSelect(t, fieldsCol); } } // dans tous les cas mettre l'ID de la dernire table final SQLTable lastTable = p.getLast(); select.addSelect(lastTable.getKey()); // on ajoute une SQLRow pour chaque ID trouv select.setWhere(where).addOrderSilent(lastTable.getName()); sysRoot.getDataSource().execute(select.asString(), new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { final ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { final List<SQLRow> rows = new ArrayList<SQLRow>(pathSize); for (int i = 0; i < pathSize; i++) { // les tables qui ne nous interessent pas if (fieldsCols.get(i).size() > 0) { // +1 car p contient cette ligne final SQLTable t = p.getTable(i + 1); rows.add(SQLRow.createFromRS(t, rs, rsmd, pathSize == 1)); } } res.add(rows); } return null; } }); return res; }
From source file:org.openconcerto.sql.model.SQLTable.java
@SuppressWarnings("unchecked") private static final Map<String, Number> getUndefIDs(final SQLSchema schema) { if (!UNDEFINED_IDs.containsKey(schema)) { final Map<String, Number> r; if (schema.contains(undefTable)) { final SQLBase b = schema.getBase(); final SQLTable undefT = schema.getTable(undefTable); final SQLSelect sel = new SQLSelect().addSelectStar(undefT); r = (Map<String, Number>) b.getDataSource().execute(sel.asString(), new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { final Map<String, Number> res = new HashMap<String, Number>(); while (rs.next()) { res.put(rs.getString("TABLENAME"), (Number) rs.getObject("UNDEFINED_ID")); }//from w ww . j a v a 2s . c om return res; } }); // be called early, since it's more likely that some transaction will create table, // set its undefined ID, then use it in requests, than some other transaction // needing the undefined ID. TODO The real fix is one tree per transaction. undefT.addTableModifiedListener(new ListenerAndConfig(new SQLTableModifiedListener() { @Override public void tableModified(SQLTableEvent evt) { synchronized (UNDEFINED_IDs) { UNDEFINED_IDs.remove(schema); undefT.removeTableModifiedListener(this); } } }, false)); } else { r = Collections.emptyMap(); } UNDEFINED_IDs.put(schema, r); } return UNDEFINED_IDs.get(schema); }
From source file:org.openconcerto.sql.model.SQLTable.java
/** * Vrifie cette table est intgre. C'est dire que toutes ses clefs externes pointent sur des * lignes existantes et non effaces. Cette mthode retourne une liste constitue de triplet : * SQLRow (la ligne incohrente), SQLField (le champ incohrent), SQLRow (la ligne invalide de * la table trangre).//from w ww . j a v a 2 s . com * * @return a list of inconsistencies or <code>null</code> if this table is not rowable. */ public List<Tuple3<SQLRow, SQLField, SQLRow>> checkIntegrity() { final SQLField pk; final Set<SQLField> fks; synchronized (this) { if (!this.isRowable()) return null; pk = this.getKey(); fks = this.getForeignKeys(); } final List<Tuple3<SQLRow, SQLField, SQLRow>> inconsistencies = new ArrayList<Tuple3<SQLRow, SQLField, SQLRow>>(); // si on a pas de relation externe, c'est OK if (!fks.isEmpty()) { final SQLSelect sel = new SQLSelect(); // on ne vrifie pas les lignes archives mais l'indfinie oui. sel.setExcludeUndefined(false); sel.addSelect(pk); sel.addAllSelect(fks); this.getBase().getDataSource().execute(sel.asString(), new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { while (rs.next()) { for (final SQLField fk : fks) { final SQLRow pb = SQLTable.this.checkValidity(fk.getName(), rs.getInt(fk.getFullName())); if (pb != null) { final SQLRow row = SQLTable.this.getRow(rs.getInt(pk.getFullName())); inconsistencies.add(Tuple3.create(row, fk, pb)); } } } // on s'en sert pas return null; } }); } return inconsistencies; }