一个通用的用于数据库操作的类

通用类接口:

 

public interface SQLUtils {

    public List<Map<String, Object>> getResultSet(String dataSourceName, String query) throws SQLException;

    public List<Map<String, Object>> getResultSet(String dataSourceName, String query, String[] params)
            throws SQLException;

    public boolean updateTable(String dataSourceName, String statment) throws SQLException;

    public boolean updateTable(String dataSourceName, String statment, String[] params) throws SQLException;
}

 

实现部分

查询方法的实现: 

 

@Override
    public List<Map<String, Object>> getResultSet(String dataSourceName, String query, String[] params)
            throws SQLException {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        try {
            DataSource dataSource = getDatasource(dataSourceName);
            connection = dataSource.getConnection();
            ps = connection.prepareStatement(query);
            log.debug("DB Query to run " + query);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setString(i + 1, params[i]);
                    log.debug("Param" + (i + 1) + " " + params[i]);
                }
            }
            resultSet = ps.executeQuery();
            int numColumns = resultSet.getMetaData().getColumnCount();

            while (resultSet.next()) {
                Map<String, Object> row = new LinkedHashMap<String, Object>();
                for (int i = 0; i < numColumns; ++i) {
                    String column = resultSet.getMetaData().getColumnName(i + 1);
                    Object value = resultSet.getObject(i + 1);
                    if (value instanceof String) {
                        value = (StringUtils.trim((String) value));
                    }
                    if (value instanceof Clob) {
                        value = clobToString((Clob) value);
                    }
                    row.put(StringUtils.trim(column), value);
                }
                rows.add(row);
            }
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (Exception e) {
            }
            try {
                if (ps != null)
                    ps.close();
            } catch (Exception e) {
            }
            try {
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
            }
        }
        return rows;
    }

 

处理blog数据:

private String clobToString(Clob data) {
        StringBuilder sb = new StringBuilder();
        try {
            Reader reader = data.getCharacterStream();
            BufferedReader br = new BufferedReader(reader);

            String line;
            while (null != (line = br.readLine())) {
                sb.append(line);
            }
            br.close();
        } catch (SQLException e) {
            log.error("[SQLUtilsImpl] - Unable to handle clob", data, e);
        } catch (IOException e) {
            log.error("[SQLUtilsImpl] -  Unable to handle clob", data, e);
        }
        return sb.toString();
    }

更新方法的实现:

@Override
    public boolean updateTable(String dataSourceName, String statment, String[] params) throws SQLException {
        boolean flag = false;
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            DataSource dataSource = getDatasource(dataSourceName);
            connection = dataSource.getConnection();
            ps = connection.prepareStatement(statment);
            log.debug("DB statement to run: " + statment);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setString(i + 1, params[i]);
                    log.debug("Param" + (i + 1) + " " + params[i]);
                }
            }
            int returnCount = ps.executeUpdate();
            log.debug("returnCount:" + returnCount);
            flag = (returnCount == 1) ? true : false;
            //connection.commit();
        } finally {
            try {
                if (resultSet != null)
                    resultSet.close();
            } catch (Exception e) {
            }
            try {
                if (ps != null)
                    ps.close();
            } catch (Exception e) {
            }
            try {
                if (connection != null)
                    connection.close();
            } catch (Exception e) {
            }
        }
        return flag;
    }

获得数据源:

@Reference
    private DataSourcePool dataSourceService;

    private DataSource getDatasource(String dataSourceName) {
        log.debug("getDatasource called");
        DataSource dataSource = null;
        try {
            dataSource = (DataSource) dataSourceService.getDataSource(dataSourceName);
        } catch (Exception e) {
            log.error("[SQLUtilsImpl] - Datasource not found -", dataSourceName, e);
        }
        return dataSource;
    }