org.apache.commons.dbutils.DbUtils Java Examples

public static void executeDrop(String schemaName,String viewName) {
    LOG.trace("executeDrop");
    Connection connection = null;
    Statement statement = null;
    try {
        connection = SpliceNetConnection.getConnection();
        statement = connection.createStatement();
        statement.execute("drop view " + schemaName.toUpperCase() + "." + viewName.toUpperCase());
        connection.commit();
    } catch (Exception e) {
        LOG.error("error Dropping " + e.getMessage());
        e.printStackTrace(System.err);
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(statement);
        DbUtils.commitAndCloseQuietly(connection);
    }
}
 

@Override
protected List<String> getTableNameList() {
    if (StringUtils.isEmpty(filterRegexp)) {
        throw new RuntimeException("`Filter regular expression` needed to be set");
    }

    ResultSet resultSet = null;
    List<String> tableNames = new ArrayList<>();
    try {
        DatabaseMetaData dbMetadata = connection.getMetaData();
        resultSet = dbMetadata.getTables(null, ((HiveProp) prop).getDbName().toUpperCase(), filterRegexp.toUpperCase(),
                new String[] { "TABLE" });
        while (resultSet.next()) {
            String name = resultSet.getString("TABLE_NAME");
            tableNames.add(name);
        }

        return tableNames;
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    } finally {
        DbUtils.closeQuietly(resultSet);
    }
}
 

public TableStruct analyse(Connection connection, String table) throws SQLException {
    ResultSet structRs = null;
    ResultSet columnSet = null;
    TableStruct tableStruct = new TableStruct(table);
    try {
        structRs = connection.getMetaData().getPrimaryKeys(connection.getCatalog(), null, table);
        columnSet = connection.getMetaData().getColumns(null, "%", table, "%");
        while (structRs.next()) {
            tableStruct.getPrimaryKeys().add(structRs.getString("COLUMN_NAME"));
        }
        while (columnSet.next()) {
            tableStruct.getColumns().put(columnSet.getString("COLUMN_NAME"), columnSet.getString("TYPE_NAME"));
        }
    } catch (SQLException e) {
        try {
            DbUtils.close(structRs);
            DbUtils.close(columnSet);
        } catch (SQLException ignored) {
        }
        throw e;
    }
    return tableStruct;
}
 

@Override
protected Result check() throws Exception {

	GenericObjectPool<PoolableConnection> pool = (GenericObjectPool<PoolableConnection>) connectionPool;
	if (pool.getNumWaiters() > maxWaitingConnections) {
		return Result.unhealthy("Overloaded connection pool.  name="
				+ poolName + " nbrWaiters=" + pool.getNumWaiters());
	}

	PoolableConnectionFactory poolFactory = (PoolableConnectionFactory) pool
			.getFactory();
	PoolableConnection conn = null;
	try {
		conn = pool.borrowObject();
		poolFactory.validateConnection(conn);
	} catch (Exception e) {
		return Result
				.unhealthy("Database connection validation error.  error="
						+ ExceptionUtils.getStackTrace(e));
	} finally {
		DbUtils.closeQuietly(conn);
	}

	return Result.healthy();
}
 

@Test
public void testCallGetTypeInfo() throws Exception{
    String[] expectedTypes = {"BIGINT", "LONG VARCHAR FOR BIT DATA", "VARCHAR () FOR BIT DATA", "CHAR () FOR BIT DATA",
        "LONG VARCHAR", "CHAR", "NUMERIC", "DECIMAL", "INTEGER", "SMALLINT", "FLOAT", "REAL", "DOUBLE", "VARCHAR",
        "BOOLEAN", "DATE", "TIME", "TIMESTAMP", "OBJECT", "BLOB", "CLOB", "XML"};
    Arrays.sort(expectedTypes);

    CallableStatement cs = methodWatcher.prepareCall("call SYSIBM.SQLGETTYPEINFO(0,null)");
    ResultSet rs = cs.executeQuery();
    try {
        List<String> actual = new ArrayList<>(expectedTypes.length);
        while (rs.next()) {
            actual.add(rs.getString(1));
        }
        String[] actualArray = actual.toArray(new String[actual.size()]);
        Arrays.sort(actualArray);
        Assert.assertArrayEquals(expectedTypes, actualArray);
    } finally {
        DbUtils.closeQuietly(rs);
    }
}
 

private String getDatabasePosition() {
    ResultSet resultSet = null;
    try {
        DatabaseMetaData dbMetadata = connection.getMetaData();
        resultSet = dbMetadata.getSchemas();
        while (resultSet.next()) {
            String schema = resultSet.getString("TABLE_SCHEM");
            if (schema != null && schema.equalsIgnoreCase(((HiveProp) prop).getDbName())) {
                return schema;
            }
        }
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    } finally {
        DbUtils.closeQuietly(resultSet);
    }
    throw new RuntimeException("Please add db_name in `jdbcUrl`");
}
 

public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);

		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<List<Employee>> resultListHandler = new BeanListHandler<Employee>(
				Employee.class);

		try {
			List<Employee> empList = run.query(conn, "SELECT * FROM employee",
					resultListHandler);
			System.out.println(empList);
		} finally {
			DbUtils.close(conn);
		}

	}
 

/**
 * Save row data.
 * 
 * @param insertSql
 * @throws Exception
 */
public void saveRowdata(String insertSql) throws Exception {
	Connection conn = null;
	try {
		QueryRunner runner = new QueryRunner();
		conn = dbFactory.getConnection();
		int num = runner.update(conn, insertSql);
		if (num <= 0) {
			log.warn("Failed save rowdata for sql: " + insertSql);
		}
	} catch (Exception e) {
		throw e;
	} finally {
		DbUtils.close(conn);
	}

}
 

public static int queryRecordSum(Connection conn, String sql) {
    int result = -1;
    Statement stmt = null;
    ResultSet resultSet = null;
    try {
        stmt = conn.createStatement();
        resultSet = stmt.executeQuery(sql);
        if (resultSet.next()) {
            result = resultSet.getInt(1);
        }
    } catch (SQLException e1) {
        logger.error("执行sql出错,sql = {}", sql);
    } finally {
        DbUtils.closeQuietly(resultSet);
        DbUtils.closeQuietly(stmt);
    }
    return result;
}
 

public Result redirectToNationFlag(String nation) throws SQLException {
	Connection conn = null;
	try {
		conn = getConnection();
		final String flag = Utils.getNationFlag(nation, conn, null);
		if (flag != null) {
			Result result = Utils.handleDefaultGetHeaders(request(), response(), String.valueOf(flag.hashCode()), "21600");
			if (result == null) {
				return Results.redirect(flag);
			}
			return result;
		}
		Utils.handleDefaultPostHeaders(request(), response());
		return Results.notFound();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
 

@Override
public void applyUndoLog(List<StatementInfo> statementInfoList) throws SQLException {
    Connection connection = null;
    try {
        connection = queryRunner.getDataSource().getConnection();
        connection.setAutoCommit(false);
        for (StatementInfo statementInfo : statementInfoList) {
            log.debug("txc > Apply undo log. sql: {}, params: {}", statementInfo.getSql(), statementInfo.getParams());
            queryRunner.update(connection, statementInfo.getSql(), statementInfo.getParams());
        }
        connection.commit();
    } catch (SQLException e) {
        if (connection != null) {
            connection.rollback();
        }
        throw e;
    } finally {
        if (connection != null) {
            connection.setAutoCommit(true);
            DbUtils.close(connection);
        }
    }
}
 

private long getNewToolContentId(long newToolId, Connection conn) throws DeployException {
PreparedStatement stmt = null;
ResultSet results = null;
try {
    stmt = conn.prepareStatement("INSERT INTO lams_tool_content (tool_id) VALUES (?)");
    stmt.setLong(1, newToolId);
    stmt.execute();
    stmt = conn.prepareStatement("SELECT LAST_INSERT_ID() FROM lams_tool_content");
    results = stmt.executeQuery();
    if (results.next()) {
	return results.getLong("LAST_INSERT_ID()");
    } else {
	throw new DeployException("No tool content id found");
    }

} catch (SQLException sqlex) {
    throw new DeployException("Could not get new tool content id", sqlex);
} finally {
    DbUtils.closeQuietly(stmt);
    DbUtils.closeQuietly(results);
}
   }
 

public static void executeDrop(String schemaName,String functionName) {
	LOG.trace("executeDrop");
	Connection connection = null;
	Statement statement = null;
	try {
		connection = SpliceNetConnection.getConnection();
		statement = connection.createStatement();
		statement.execute("drop function " + schemaName.toUpperCase() + "." + functionName.toUpperCase());
		connection.commit();
	} catch (Exception e) {
		LOG.error("error Dropping " + e.getMessage());
		e.printStackTrace();
		throw new RuntimeException(e);
	} finally {
		DbUtils.closeQuietly(statement);
		DbUtils.commitAndCloseQuietly(connection);
	}
}
 

private long runLibraryScript(final String scriptSQL, final Connection conn) throws DeployException {
runScript(scriptSQL, conn);
PreparedStatement stmt = null;
ResultSet results = null;
try {
    stmt = conn.prepareStatement("SELECT LAST_INSERT_ID() FROM lams_learning_library");
    results = stmt.executeQuery();
    if (results.next()) {
	return results.getLong("LAST_INSERT_ID()");
    } else {
	throw new DeployException("Could not get learning_library_id");
    }
} catch (SQLException sqlex) {
    throw new DeployException("Failed to run learning library script", sqlex);
} finally {
    DbUtils.closeQuietly(stmt);
    DbUtils.closeQuietly(results);
}

   }
 

@Override
public String getValue(final String propertyName) {
    try {
        final Connection connection = dataSource.getConnection();
        final PreparedStatement query =
                connection.prepareStatement("SELECT VALUE FROM CONFIGURATIONS WHERE NAME = ?");
        query.setString(1, propertyName);
        final ResultSet value = query.executeQuery();

        if (value.next()) {
            return value.getString(1);
        }

        DbUtils.closeQuietly(value);
        DbUtils.closeQuietly(query);
        DbUtils.closeQuietly(connection);
    } catch (final SQLException e) {
        e.printStackTrace();
    }

    return null;
}
 

public static JsonNode getRegionalMap(Connection conn, String region) throws SQLException {
	Map<String, String> links = new HashMap<String, String>(3);
	PreparedStatement update = conn.prepareStatement("SELECT regional_map, regional_map_preview FROM assembly.region WHERE name = ?");
	update.setString(1, Utils.sanitizeName(region));
	ResultSet set = update.executeQuery();
	if (set.next()) {
		String mapLink = set.getString(1);
		if (!set.wasNull()) {
			links.put("regional_map", mapLink);
		}
		String mapPreview = set.getString(2);
		if (!set.wasNull()) {
			links.put("regional_map_preview", mapPreview);
		}
	}
	DbUtils.closeQuietly(set);
	DbUtils.closeQuietly(update);
	return Json.toJson(links);
}
 

public String queryTool(String toolSig, String column) {
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet results = null;
try {
    stmt = conn
	    .prepareStatement("select " + column + " from lams_tool where tool_signature= \"" + toolSig + "\"");
    System.out.println("SQL stmt: " + stmt);
    results = stmt.executeQuery();

    if (results.first()) {
	return results.getString(column);

    }
} catch (SQLException se) {

    throw new DeployException("Could not get entry from lams_tool: " + column + "\n" + se.getMessage());

} finally {
    DbUtils.closeQuietly(stmt);
}
return "ERROR";
   }
 

/**
    * Executes an array of statements againt a connection.
    * Note that it will *WILL NOT* close the connection, commit or roll back.
    */
   protected void executeStatements(String[] statements, Connection newConn) throws DeployException {
Statement stmt = null;
try {
    stmt = newConn.createStatement();
    for (int i = 0, length = statements.length; i < length; i++) {
	stmt.addBatch(statements[i]);
    }

    stmt.executeBatch();

} catch (SQLException sqlex) {
    throw new DeployException("Could not execute statements", sqlex);
} finally {
    DbUtils.closeQuietly(stmt);
}
   }
 

/**
    * Looks up current tool installations for the tool with <code>toolSignature</code>
    * The tool_id for the tool with the matching tool signature will be returned.
    * 
    * @param toolSignature
    * @param conn
    * @return
    */
   public long getToolId(String toolSignature, Connection conn) {
PreparedStatement stmt = null;
ResultSet results = null;
try {
    stmt = conn.prepareStatement("SELECT tool_id FROM lams_tool WHERE tool_signature=?");
    stmt.setString(1, toolSignature);
    stmt.execute();

    results = stmt.executeQuery();
    if (results.next()) {
	return results.getLong("tool_id");
    } else {
	throw new DeployException("Tool id for tool signature " + toolSignature + " not found.");
    }

} catch (SQLException sqlex) {
    throw new DeployException("Tool id for tool signature " + toolSignature + " not found.", sqlex);
} finally {
    DbUtils.closeQuietly(stmt);
    DbUtils.closeQuietly(results);
}
   }
 

public void importData(String filename, String timestamp) {
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = SpliceNetConnection.getConnection();
        ps = connection.prepareStatement("call SYSCS_UTIL.IMPORT_DATA (?, ?, null,?,',',null,?,null,null,0,null,true,null)");
        ps.setString(1,schemaName);
        ps.setString(2,tableName);
        ps.setString(3,filename);
        ps.setString(4, timestamp);
        ps.executeQuery();
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(ps);
        DbUtils.commitAndCloseQuietly(connection);
    }
}
 

private int getRMBCommentNation(Connection conn, int commentId) throws SQLException {
	PreparedStatement select = null;
	ResultSet result = null;
	try {
		select = conn.prepareStatement("SELECT nation_id FROM assembly.rmb_comments WHERE id = ?");
		select.setInt(1, commentId);
		result = select.executeQuery();
		if (result.next()) {
			return result.getInt(1);
		}
		return -1;
	} finally {
		DbUtils.closeQuietly(result);
		DbUtils.closeQuietly(select);
	}
}
 

public Result redirectToRegionFlag(String region) throws SQLException {
	Connection conn = null;
	try {
		conn = getConnection();
		final String flag = Utils.getRegionFlag(region, conn, null);
		if (flag != null) {
			Result result = Utils.handleDefaultGetHeaders(request(), response(), String.valueOf(flag.hashCode()), "21600");
			if (result == null) {
				return Results.redirect(flag);
			}
			return result;
		}
		Utils.handleDefaultPostHeaders(request(), response());
		return Results.notFound();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}
 

public Result regionFlags(String regions) throws SQLException {
	String[] regionNames = regions.split(",");
	Map<String, String> json = new HashMap<String, String>(regionNames.length);
	Connection conn = null;
	try {
		conn = getConnection();
		for (String region : regionNames) {
			json.put(region, Utils.getRegionFlag(region, conn));
		}
	} finally {
		DbUtils.closeQuietly(conn);
	}
	Result result = Utils.handleDefaultGetHeaders(request(), response(), String.valueOf(json.hashCode()), "21600");
	if (result != null) {
		return result;
	}
	return ok(Json.toJson(json)).as("application/json");
}
 

public Result nationFlags(String nations) throws SQLException {
	String[] nationNames = nations.split(",");
	Map<String, String> json = new HashMap<String, String>(nationNames.length);
	Connection conn = null;
	try {
		conn = getConnection();
		for (String nation : nationNames) {
			json.put(nation, Utils.getNationFlag(nation, conn));
		}
	} finally {
		DbUtils.closeQuietly(conn);
	}

	Result result = Utils.handleDefaultGetHeaders(request(), response(), String.valueOf(json.hashCode()), "21600");
	if (result != null) {
		return result;
	}
	return ok(Json.toJson(json)).as("application/json");
}
 

private void validateStep2(DataSource ds, String physicalSchemaStr, JdbcHelper jdbc) throws Exception {
    List<Map<String, Object>> results;
    Connection conn = ds.getConnection();
    try {
        results = jdbc.queryForList(conn, "select * from " + physicalSchemaStr + "TestTable order by idField");
    } finally {
        DbUtils.closeQuietly(conn);
    }

    assertEquals(5, results.size());
    this.validateResultRow(results.get(0), 1, "str1", 0);
    this.validateResultRow(results.get(1), 3, "str3Changed", 0);
    this.validateResultRow(results.get(2), 4, "str4", 0);
    this.validateResultRow(results.get(3), 5, "str5", 0);
    this.validateResultRow(results.get(4), 6, "str6", 0);
}
 

private Pair<Statement, ResultSet> queryAndLeaveStatementOpenInternal(Connection conn, int retryCount, String sql) {
    Statement statement = null;
    try {
        statement = conn.createStatement();
        if (LOG.isDebugEnabled()) {
            LOG.debug("Executing query on {}: {}", displayConnection(conn), sql);
        }
        return Tuples.pair(statement, statement.executeQuery(sql));
    } catch (SQLException e) {
        DbUtils.closeQuietly(statement);  // on an exception, close the existing statement (on success, we'd leave it open)
        DataAccessException dataAccessException = new DataAccessException(e);
        boolean retry = this.jdbcHandler.handleException(this, conn, retryCount, dataAccessException);
        if (retry) {
            return this.queryAndLeaveStatementOpenInternal(conn, retryCount + 1, sql);
        } else {
            throw dataAccessException;
        }
    }
}
 

public Promise<Result> getPostRatings(final int rmbPost, final int rmbCache) throws SQLException {
	Promise<JsonNode> promise = Promise.wrap(akka.dispatch.Futures.future((new Callable<JsonNode>() {
		@Override
		public JsonNode call() throws Exception {
			Connection conn = null;
			try {
				conn = getConnection();
				JsonNode ratings = calculatePostRatings(conn, rmbPost);
				return ratings;
			} finally {
				DbUtils.closeQuietly(conn);
			}
		}
	}), Akka.system().dispatcher()));
	Promise<Result> result = promise.flatMap(getAsyncResult(request(), response(), rmbCache == -1 ? "10" : "86400"));
	return result;
}
 

@Override
protected List<ColumnValue> convertColumnValue(Long tbId, String tableName, String dbName) {
    List<ColumnValue> columns = new ArrayList<>();
    ResultSet resultSet = null;
    try {
        DatabaseMetaData dbMetadata = connection.getMetaData();
        resultSet = dbMetadata.getColumns(null, dbName.toUpperCase(), tableName.toUpperCase(), "%");
        while (resultSet.next()) {
            String name = resultSet.getString("COLUMN_NAME");
            String type = resultSet.getString("TYPE_NAME");
            int columnIdx = resultSet.getInt("ORDINAL_POSITION");

            ColumnValue value = new ColumnValue();
            value.setColumnName(name);
            value.setTypeName(type);
            value.setCdId(tbId);
            value.setIntegerIdx(columnIdx);
            columns.add(value);
        }
        return columns;
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    } finally {
        DbUtils.closeQuietly(resultSet);
    }
}
 

public Result getRecruitmentNations() throws SQLException {
	Map<String, Object> nations = new HashMap<String, Object>();
	Connection conn = null;
	try {
		conn = getConnection();
		PreparedStatement select = conn.prepareStatement("SELECT name, title, region_name, timestamp, type FROM assembly.recruitment_nations LIMIT 0, 100");
		List<Object> nationData = new ArrayList<Object>();
		ResultSet set = select.executeQuery();
		while (set.next()) {
			Map<String, Object> nation = new HashMap<String, Object>();
			nation.put("name", set.getString(1));
			nation.put("title", set.getString(2));
			nation.put("region", set.getString(3));
			nation.put("timestamp", set.getLong(4));
			nation.put("status", HappeningType.getType(set.getInt(5)).getName());
			
			nationData.add(nation);
		}
		nations.put("nations", nationData);
		
		DbUtils.closeQuietly(set);
		DbUtils.closeQuietly(select);
	} finally {
		DbUtils.closeQuietly(conn);
	}
	Result result = Utils.handleDefaultGetHeaders(request(), response(), String.valueOf(nations.hashCode()), "1");
	if (result != null) {
		return result;
	}
	return Results.ok(Json.toJson(nations)).as("application/json");
}
 

public static void main(String[] args) {
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;

	final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
	final String driver = "org.h2.Driver";
	final String usr = "sa";
	final String pwd = "";

	List<User> users = null;
	try {
		DbUtils.loadDriver(driver);
		conn = DriverManager.getConnection(url, usr, pwd);
		stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT * FROM user");
		BeanListHandler<User> listHandler = new BeanListHandler<User>(
				User.class);
		users = listHandler.handle(rs);

		for (User user : users) {
			System.out.println("User Object:: " + user.getUserId() + "\t"
					+ user.getFirstName() + "\t" + user.getEmailId());
		}

	} catch (SQLException se) {
		se.printStackTrace();
	} finally {
		DbUtils.closeQuietly(conn);
	}
}