List of usage examples for org.apache.commons.dbutils ResultSetHandler ResultSetHandler
ResultSetHandler
From source file:org.openconcerto.sql.replication.MemoryRep.java
protected final void replicateData() throws SQLException, IOException, InterruptedException { final SQLSyntax slaveSyntax = SQLSyntax.get(this.slave); final File tempDir = FileUtils.createTempDir(getClass().getCanonicalName() + "_StoreData"); try {//ww w . j a v a2s . c o m final List<String> queries = new ArrayList<String>(); final List<ResultSetHandler> handlers = new ArrayList<ResultSetHandler>(); final Map<File, SQLTable> files = new HashMap<File, SQLTable>(); for (final Entry<String, Set<String>> e : this.tables.entrySet()) { if (Thread.interrupted()) throw new InterruptedException("While creating handlers"); final String rootName = e.getKey(); final File rootDir = new File(tempDir, rootName); FileUtils.mkdir_p(rootDir); final DBRoot root = this.master.getRoot(rootName); final DBRoot slaveRoot = this.slave.getRoot(rootName); for (final String tableName : e.getValue()) { final SQLTable masterT = root.getTable(tableName); final SQLSelect select = new SQLSelect(true).addSelectStar(masterT); queries.add(select.asString()); // don't use cache to be sure to have up to date data handlers.add(new IResultSetHandler(new ResultSetHandler() { private final CSVHandler csvH = new CSVHandler(masterT.getOrderedFields()); @Override public Object handle(ResultSet rs) throws SQLException { final File tempFile = new File(rootDir, FileUtils.FILENAME_ESCAPER.escape(tableName) + ".csv"); assert !tempFile.exists(); try { FileUtils.write(this.csvH.handle(rs), tempFile); files.put(tempFile, slaveRoot.getTable(tableName)); } catch (IOException e) { throw new SQLException(e); } return null; } }, false)); } } try { SQLUtils.executeAtomic(this.master.getDataSource(), new ConnectionHandlerNoSetup<Object, SQLException>() { @Override public Object handle(SQLDataSource ds) throws SQLException { SQLUtils.executeMultiple(MemoryRep.this.master, queries, handlers); return null; } }); } catch (RTInterruptedException e) { final InterruptedException exn = new InterruptedException("Interrupted while querying the master"); exn.initCause(e); throw exn; } SQLUtils.executeAtomic(this.slave.getDataSource(), new ConnectionHandlerNoSetup<Object, IOException>() { @Override public Object handle(SQLDataSource ds) throws SQLException, IOException { for (final Entry<File, SQLTable> e : files.entrySet()) { final SQLTable slaveT = e.getValue(); // loadData() fires table modified slaveSyntax.loadData(e.getKey(), slaveT, true); } return null; } }); this.count.incrementAndGet(); } finally { FileUtils.rm_R(tempDir); } }
From source file:org.openconcerto.task.TodoListPanel.java
private void initViewableUsers(final User currentUser) { final SwingWorker2<List<Tuple3<String, Integer, String>>, Object> worker = new SwingWorker2<List<Tuple3<String, Integer, String>>, Object>() { @Override/* www.java 2 s . co m*/ protected List<Tuple3<String, Integer, String>> doInBackground() throws Exception { final List<Integer> canViewUsers = new ArrayList<Integer>(); for (final UserTaskRight right : UserTaskRight.getUserTaskRight(currentUser)) { if (right.canRead()) canViewUsers.add(right.getIdToUser()); } // final Vector users = new Vector(); final SQLTable userT = UserManager.getInstance().getTable(); final DBSystemRoot systemRoot = Configuration.getInstance().getSystemRoot(); final SQLSelect select1 = new SQLSelect(systemRoot, false); select1.addSelect(userT.getKey()); select1.addSelect(userT.getField("NOM")); select1.addSelect(userT.getField("PRENOM")); select1.addSelect(userT.getField("SURNOM")); final Where meWhere = new Where(userT.getKey(), "=", currentUser.getId()); final Where canViewWhere = new Where(userT.getKey(), canViewUsers); select1.setWhere(meWhere.or(canViewWhere)); final List<Tuple3<String, Integer, String>> result = new ArrayList<Tuple3<String, Integer, String>>(); userT.getDBSystemRoot().getDataSource().execute(select1.asString(), new ResultSetHandler() { public Object handle(ResultSet rs) throws SQLException { while (rs.next()) { String displayName = rs.getString(4).trim(); if (displayName.length() == 0) { displayName = rs.getString(3).trim() + " " + rs.getString(2).trim().toUpperCase(); } final int uId = rs.getInt(1); final String name = rs.getString(2); result.add(new Tuple3<String, Integer, String>(displayName, uId, name)); } return null; } }); return result; } @Override protected void done() { try { final List<Tuple3<String, Integer, String>> tuples = get(); for (Tuple3<String, Integer, String> tuple3 : tuples) { final JCheckBoxMenuItem checkBoxMenuItem = new JCheckBoxMenuItem(tuple3.get0()); TodoListPanel.this.comboUser.add(checkBoxMenuItem); final int uId = tuple3.get1(); final String name = tuple3.get2(); TodoListPanel.this.users.add(new User(uId, name)); checkBoxMenuItem.addActionListener(new ActionListener() { public void actionPerformed(ActionEvent e) { if (checkBoxMenuItem.isSelected()) addUserListenerId(uId); else removeUserListenerId(uId); } }); } } catch (Exception e) { ExceptionHandler.handle("Unable to get tasks users", e); } } }; worker.execute(); }
From source file:org.openflexo.technologyadapter.jdbc.util.SQLHelper.java
public static JDBCResultSet select(final JDBCFactory factory, final JDBCTable from, String where, String orderBy, int limit, int offset) throws SQLException { // TODO : maybe resource leak, cannot use lexical scope for auto-closing Connection connection = from.getResourceData().getConnection(); final JDBCResultSetDescription description = factory.makeResultSetDescription(from.getResourceData(), from.getName(), null, null, null, where, orderBy, limit, offset); String request = createSelectRequest(description); return new QueryRunner().query(connection, request, new ResultSetHandler<JDBCResultSet>() { @Override// www . ja v a 2s .c o m public JDBCResultSet handle(ResultSet resultSet) throws SQLException { return factory.makeJDBCResult(description, resultSet, from.getSchema()); } }); }
From source file:org.openlogics.gears.jdbc.SelectTest.java
@Test public void testResultHandler() { DataStore ds = new JdbcDataStore(basicDataSource); Query query = new Query("select * from FOO"); String result = query.toString(); assertEquals("select * from FOO", result); logger.info("Result=" + result); try {//w w w. java2 s . com String response = ds.select(query, new ResultSetHandler<String>() { @Override public String handle(ResultSet rs) throws SQLException { while (rs.next()) { logger.debug("Record found..." + rs.getInt(1)); } return "success"; } }); assertEquals("success", response); } catch (SQLException e) { e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates. } }
From source file:org.openlogics.gears.jdbc.SelectTest.java
@Test public void testContextQuery() throws SQLException { DataStore ds = new JdbcDataStore(basicDataSource); Query query = new Query("select * from FOO where FOO_ID = ?", 2); List<Object> result = ds.select(query, new ResultSetHandler<List<Object>>() { @Override//w w w. j a v a 2 s . c o m public List<Object> handle(ResultSet rs) throws SQLException { List<Object> result = Lists.newArrayList(); if (rs.next()) { result.add(rs.getObject(1)); result.add(rs.getObject(2)); result.add(rs.getObject(3)); result.add(rs.getObject(4)); } return result; } }); assertTrue(!result.isEmpty()); for (Object o : result) { logger.info("Found....." + o); } }
From source file:org.openmrs.contrib.databaseexporter.DatabaseExporter.java
public static void export(final Configuration configuration) throws Exception { FileOutputStream fos = null;/*from ww w .j a va 2 s. co m*/ Connection connection = null; try { connection = DbUtil.openConnection(configuration); if (configuration.getTargetDirectory() != null) { File f = new File(configuration.getTargetDirectory()); if (!f.exists()) { f.mkdirs(); } } File outputFile = configuration.getOutputFile(); fos = new FileOutputStream(outputFile); OutputStreamWriter osWriter = new OutputStreamWriter(fos, "UTF-8"); PrintWriter out = new PrintWriter(osWriter); final ExportContext context = new ExportContext(configuration, out); context.log("Context initialized"); DbUtil.writeExportHeader(context); try { List<RowFilter> rowFilters = new ArrayList<RowFilter>(); rowFilters.add(Util.nvl(configuration.getPatientFilter(), new PatientFilter())); rowFilters.add(Util.nvl(configuration.getUserFilter(), new UserFilter())); rowFilters.add(Util.nvl(configuration.getProviderFilter(), new ProviderFilter())); for (RowFilter filter : rowFilters) { context.log("Applying filter: " + filter.getClass().getSimpleName()); filter.filter(context); } for (RowFilter filter : rowFilters) { for (DependencyFilter df : filter.getDependencyFilters()) { df.filter(context); } } ListMap<String, RowTransform> tableTransforms = new ListMap<String, RowTransform>(true); for (final String table : context.getTableData().keySet()) { TableConfig tableConfig = context.getTableData().get(table); if (tableConfig.isExportSchema()) { DbUtil.writeTableSchema(table, context); context.log(table + " schema exported"); } if (tableConfig.isExportData()) { context.log("Starting " + table + " data export"); DbUtil.writeTableExportHeader(table, context); context.log("Constructing query"); String query = context.buildQuery(table, context); context.log("Determining applicable transforms for table"); for (RowFilter filter : rowFilters) { tableTransforms.putAll(table, filter.getTransforms()); } for (RowTransform transform : configuration.getRowTransforms()) { if (transform.canTransform(table, context)) { tableTransforms.putInList(table, transform); } } final List<RowTransform> transforms = Util.nvl(tableTransforms.get(table), new ArrayList<RowTransform>()); context.log("Determining number of rows for table"); String rowNumQuery = query.replace(table + ".*", "count(*)"); final Long totalRows = context.executeQuery(rowNumQuery, new ScalarHandler<Long>()); final int batchSize = context.getConfiguration().getBatchSize(); context.log("***************************** Executing query **************************"); context.log("Query: " + query); context.log("Transforms: " + transforms); context.log("Total Rows: " + totalRows); Integer rowsAdded = context.executeQuery(query, new ResultSetHandler<Integer>() { public Integer handle(ResultSet rs) throws SQLException { List<TableRow> results = new ArrayList<TableRow>(); ResultSetMetaData md = rs.getMetaData(); int numColumns = md.getColumnCount(); int rowsChecked = 0; int rowsAdded = 0; int rowIndex = 0; while (rs.next()) { rowsChecked++; TableRow row = new TableRow(table); for (int i = 1; i <= numColumns; i++) { String columnName = md.getColumnName(i); ColumnValue value = new ColumnValue(table, columnName, md.getColumnType(i), rs.getObject(i)); row.addColumnValue(columnName, value); } boolean includeRow = true; for (RowTransform transform : transforms) { includeRow = includeRow && transform.transformRow(row, context); } if (includeRow) { rowsAdded++; rowIndex = (rowIndex >= batchSize ? 0 : rowIndex) + 1; DbUtil.writeInsertRow(row, rowIndex, rowsAdded, context); } if (rowsChecked % 1000 == 0) { context.log("Processed " + table + " rows " + (rowsChecked - 1000) + " to " + rowsChecked + " (" + Util.toPercent(rowsChecked, totalRows, 0) + "%)"); } } return rowsAdded; } }); if (rowsAdded % batchSize != 0) { context.write(";"); context.write(""); } context.log(rowsAdded + " rows retrieved and transformed from initial queries"); context.log("********************************************************************"); DbUtil.writeTableExportFooter(table, context); } } // Handle any post-processing transforms that have been defined for (String table : tableTransforms.keySet()) { List<TableRow> rows = new ArrayList<TableRow>(); for (RowTransform transform : tableTransforms.get(table)) { rows.addAll(transform.postProcess(table, context)); } if (rows != null && !rows.isEmpty()) { DbUtil.writeTableExportHeader(table, context); for (int i = 1; i <= rows.size(); i++) { TableRow row = rows.get(i - 1); DbUtil.writeInsertRow(row, i, i, context); } context.write(";"); context.write(""); DbUtil.writeTableExportFooter(table, context); } } } catch (Exception e) { context.log("An error occurred during export: " + e.getMessage()); e.printStackTrace(System.out); } finally { context.log("Cleaning up temporary tables"); context.cleanupTemporaryTables(); } DbUtil.writeExportFooter(context); context.log("Exporting Database Completed"); context.log("***** Summary Data *****"); for (final String table : context.getTableData().keySet()) { TableConfig tableConfig = context.getTableData().get(table); context.log( tableConfig.getTableMetadata().getTableName() + ": " + tableConfig.getNumRowsExported()); } context.log("**************************"); context.log("Export completed in: " + Util.formatTimeDifference(context.getEventLog().getTotalTime())); out.flush(); } finally { IOUtils.closeQuietly(fos); DbUtil.closeConnection(connection); } }
From source file:org.openmrs.contrib.databaseexporter.transform.IdentifierTransform.java
public IdentifierGenerator getReplacementGenerator(TableRow row, ExportContext context) { IdentifierGenerator ret = null;//from w ww .j a va2 s . co m if (identifierTypeNameCache == null) { String q = "select patient_identifier_type_id, name from patient_identifier_type"; identifierTypeNameCache = context.executeQuery(q, new ResultSetHandler<Map<Integer, String>>() { public Map<Integer, String> handle(ResultSet rs) throws SQLException { Map<Integer, String> result = new HashMap<Integer, String>(); while (rs.next()) { result.put(rs.getInt(1), rs.getString(2)); } return result; } }); for (Iterator<Integer> i = identifierTypeNameCache.keySet().iterator(); i.hasNext();) { Integer typeId = i.next(); if (!getReplacementGenerators().containsKey(identifierTypeNameCache.get(typeId))) { i.remove(); } } } Object attTypeId = row.getRawValue("identifier_type"); if (attTypeId != null) { String typeName = identifierTypeNameCache.get(attTypeId); ret = getReplacementGenerators().get(typeName); } if (ret != null) { return ret; } return getDefaultGenerator(); }
From source file:org.openmrs.contrib.databaseexporter.transform.PersonAttributeTransform.java
public List<String> getReplacementsForRow(TableRow row, ExportContext context) { if (attributeNameCache == null) { String q = "select person_attribute_type_id, name from person_attribute_type"; attributeNameCache = context.executeQuery(q, new ResultSetHandler<Map<Integer, String>>() { public Map<Integer, String> handle(ResultSet rs) throws SQLException { Map<Integer, String> result = new HashMap<Integer, String>(); while (rs.next()) { result.put(rs.getInt(1), rs.getString(2)); }// w w w. j a va 2 s.co m return result; } }); for (Iterator<Integer> i = attributeNameCache.keySet().iterator(); i.hasNext();) { Integer typeId = i.next(); if (!getReplacements().containsKey(attributeNameCache.get(typeId))) { i.remove(); } } } Object attTypeId = row.getRawValue("person_attribute_type_id"); if (attTypeId != null) { String typeName = attributeNameCache.get(attTypeId); return getReplacements().get(typeName); } return null; }
From source file:org.openmrs.contrib.databaseexporter.transform.PersonNameTransform.java
public Map<Integer, String> getGenderMap(ExportContext context) { String q = "select person_id, gender from person"; Map<Integer, String> m = context.executeQuery(q, new ResultSetHandler<Map<Integer, String>>() { public Map<Integer, String> handle(ResultSet rs) throws SQLException { Map<Integer, String> ret = new HashMap<Integer, String>(); while (rs.next()) { ret.put(rs.getInt(1), rs.getString(2)); }//from w w w.j a v a 2 s . c o m return ret; } }); return m; }
From source file:org.openmrs.contrib.databaseexporter.util.DbUtil.java
public static Map<String, TableMetadata> getTableMetadata(ExportContext context) { final Map<String, TableMetadata> ret = new LinkedHashMap<String, TableMetadata>(); // Get all of the tables for (String table : getAllTables(context)) { ret.put(table, new TableMetadata(table)); }//from ww w . j a v a2 s . c o m // Retrieve the foreign key relationships for each column in each table StringBuilder foreignKeyQuery = new StringBuilder(); foreignKeyQuery.append( "select lower(referenced_table_name), lower(referenced_column_name), lower(table_name), lower(column_name) "); foreignKeyQuery.append("from information_schema.key_column_usage "); foreignKeyQuery.append("where table_schema = database()"); context.executeQuery(foreignKeyQuery.toString(), new ResultSetHandler<Integer>() { public Integer handle(ResultSet rs) throws SQLException { int rowsHandled = 0; while (rs.next()) { TableMetadata tableMetadata = ret.get(rs.getString(1)); if (tableMetadata != null) { ListMap<String, String> foreignKeyMap = tableMetadata.getForeignKeyMap(); foreignKeyMap.putInList(rs.getString(2), rs.getString(3) + "." + rs.getString(4)); rowsHandled++; } } return rowsHandled; } }); TableMetadata usersMetadata = ret.get("users"); usersMetadata.getForeignKeyMap().putInList("user_id", "person_name.changed_by"); // This seems to be missing return ret; }