Example usage for java.sql ResultSetMetaData getColumnLabel

List of usage examples for java.sql ResultSetMetaData getColumnLabel

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSetMetaData
  6. getColumnLabel

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getColumnLabel.

Prototype

String getColumnLabel(int column) throws SQLException;

Source Link

Document

Gets the designated column's suggested title for use in printouts and displays.

Usage

From source file:uk.ac.susx.tag.method51.webapp.handler.DatabaseHandler.java

protected static void getDataJsonOrCsv(MySQLConnectionParams dbParams, String database, String table,
        GetDataParams.Format format, Integer limit, final Long sinceId, final Long offset,
        Character csvFieldSep, Request baseRequest, HttpServletResponse response) throws IOException {

    dbParams.setDatabase(database);// w  w  w.  j  a v a 2s .  co m

    String select = String.format(SELECT_ALL_SQL, table);

    try (Connection con = dbParams.buildConnection(); PreparedStatement stmt = con.prepareStatement(select)) {

        // allow them to get all tweets since some id
        Long lastId = sinceId;

        // if they didn't specify an id
        if (lastId == null) {
            // then we need an offset
            if (offset == null) {
                // if they didn't supply an offset either, just set
                // the last id to 0 so we get all the tweets.
                lastId = 0L;
            } else {
                // use the offset to find the id of the tweet at that
                // offset
                lastId = getFirstId(con, table, offset) - 1; // -1 for inclusivity
            }
        }

        //this also tests the connection, db, and table existence before modifying request
        Integer total = getCount(con, table);
        ;

        if (limit == null) {
            limit = total;
        }

        if (format == GetDataParams.Format.JSON) {
            response.setContentType("application/json;charset=utf-8");
        } else if (format == GetDataParams.Format.CSV) {
            response.setContentType("text/csv;charset=utf-8");
        } else {
            throw new GetDataParams.UnknownFormatException();
        }

        response.setStatus(HttpServletResponse.SC_OK);
        baseRequest.setHandled(true);

        int count = 0;

        try (OutputStream outStream = response.getOutputStream();) {

            if (format == GetDataParams.Format.JSON) {
                outStream.write("[".getBytes());
            }

            boolean firstTime = true;

            while (count < total && count < limit) {

                stmt.setLong(1, lastId);
                int l = Math.min(limit, Math.min(SQL_CHUNK_SIZE, total - count));
                stmt.setLong(2, l);

                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData rsmd = rs.getMetaData();
                int cols = rsmd.getColumnCount();

                int intermediateCount = 0;

                while (rs.next() && count < limit) {
                    if (!firstTime) {
                        if (format == GetDataParams.Format.JSON) {
                            outStream.write(",".getBytes());
                        }

                    } else {
                        if (format == GetDataParams.Format.CSV) {

                            //write headers

                            StringBuilder sb = new StringBuilder();

                            for (int i = 0; i < cols; i++) {
                                sb.append(rsmd.getColumnLabel(i + 1));
                                if (i < cols - 1) {
                                    sb.append(csvFieldSep);
                                }
                            }
                            sb.append("\n");
                            outStream.write(sb.toString().getBytes());
                        }
                        firstTime = false;
                    }

                    if (format == GetDataParams.Format.JSON) {
                        Tweet tweet = Schema.resultSetToTweet(rs);
                        String json = tweet.toJsonString();
                        outStream.write(json.getBytes());
                        outStream.write("\n".getBytes());

                    } else if (format == GetDataParams.Format.CSV) {

                        StringBuilder sb = new StringBuilder();

                        for (int i = 1; i <= cols; ++i) {
                            Object field = rs.getObject(i);

                            if (field != null) {
                                if (field instanceof String) {
                                    field = ((String) field).replace("\n", "").replace("\r", "")
                                            .replace(csvFieldSep, ' ');
                                }
                                sb.append(field.toString());
                            }

                            sb.append(csvFieldSep);
                        }
                        sb.delete(sb.length() - 1, sb.length());
                        sb.append("\n");

                        String csv = sb.toString();
                        outStream.write(csv.getBytes());

                    }

                    count += 1;

                    ++intermediateCount;
                    long id = rs.getLong("id");
                    if (id > lastId) {
                        lastId = id;
                    }
                }

                rs.close();
                if (intermediateCount == 0) {
                    break;
                }
            }

            if (format == GetDataParams.Format.JSON) {

                outStream.write("]".getBytes());
            }
        }

    } catch (SQLException e) {
        response.reset();

        throw new IOException(e);
    }
}

From source file:com.tesora.dve.sql.util.JdbcConnectionResourceResponse.java

private void assertEqualProxyConnMetadata(String cntxt, ProxyConnectionResourceResponse pcrr) throws Throwable {
    ResultSetMetaData rsmd = results.getMetaData();
    ColumnSet sysColumns = pcrr.getColumns();
    assertEquals(cntxt + " mismatched column set width", rsmd.getColumnCount(),
            sysColumns.getColumnList().size());
    List<ColumnMetadata> sysCols = sysColumns.getColumnList();
    for (int i = 0; i < rsmd.getColumnCount(); i++) {
        ColumnMetadata sc = sysCols.get(i);
        String colcntxt = cntxt + " column " + sc.getAliasName();
        // still don't handle non column labels right
        assertEquals(colcntxt + " mismatched column name", rsmd.getColumnName(i + 1), sc.getName());
        assertEquals(colcntxt + " mismatched column label", rsmd.getColumnLabel(i + 1), sc.getAliasName());
        if (rsmd.getColumnType(i + 1) != sc.getDataType()) {
            // emit names - easier to read
            fail(colcntxt + " mismatched column type.  Expected " + rsmd.getColumnTypeName(i + 1) + " ("
                    + rsmd.getColumnType(i + 1) + ") but found " + sc.getTypeName() + " (" + sc.getDataType()
                    + ")");
        }//from   ww w.ja v a  2s.c om
    }
}

From source file:org.sakaiproject.webservices.SakaiReport.java

protected String toCsvString(ResultSet rs, boolean includeHeaderRow) throws IOException, SQLException {
    StringWriter stringWriter = new StringWriter();
    CsvWriter writer = new CsvWriter(stringWriter, ',');
    writer.setRecordDelimiter('\n');
    writer.setForceQualifier(true);//www . ja  va 2  s  . c o m
    ResultSetMetaData rsmd = rs.getMetaData();
    int numColumns = rsmd.getColumnCount();

    if (includeHeaderRow) {
        String[] row = new String[numColumns];
        for (int i = 1; i < numColumns + 1; i++) {
            row[i - 1] = rsmd.getColumnLabel(i);
        }
        writer.writeRecord(row);
    }

    while (rs.next()) {
        String[] row = new String[numColumns];
        for (int i = 1; i < numColumns + 1; i++) {

            String column_name = rsmd.getColumnName(i);

            LOG.debug("Column Name=" + column_name + ",type=" + rsmd.getColumnType(i));

            switch (rsmd.getColumnType(i)) {
            case Types.BIGINT:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.BOOLEAN:
                row[i - 1] = String.valueOf(rs.getBoolean(i));
                break;
            case Types.BLOB:
                row[i - 1] = rs.getBlob(i).toString();
                break;
            case Types.DOUBLE:
                row[i - 1] = String.valueOf(rs.getDouble(i));
                break;
            case Types.FLOAT:
                row[i - 1] = String.valueOf(rs.getFloat(i));
                break;
            case Types.INTEGER:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.LONGVARCHAR:
                row[i - 1] = rs.getString(i);
                break;
            case Types.NVARCHAR:
                row[i - 1] = rs.getNString(i);
                break;
            case Types.VARCHAR:
                row[i - 1] = rs.getString(i);
                break;
            case Types.TINYINT:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.SMALLINT:
                row[i - 1] = String.valueOf(rs.getInt(i));
                break;
            case Types.DATE:
                row[i - 1] = rs.getDate(i).toString();
                break;
            case Types.TIMESTAMP:
                row[i - 1] = rs.getTimestamp(i).toString();
                break;
            default:
                row[i - 1] = rs.getString(i);
                break;

            }
            LOG.debug("value: " + row[i - 1]);
        }
        writer.writeRecord(row);
        //writer.endRecord();

    }

    LOG.debug("csv output:" + stringWriter.toString());

    return stringWriter.toString();
}

From source file:adapter.gbase.signalling.SignallingAdapter.java

@Override
public SignallingDetailEntity getSignallingDetail(String tabName, String startDate, String endDate,
        String phone, String xdrId, String rowKey) throws Exception {
    SignallingDetailEntity signallingDetail = new SignallingDetailEntity();
    Statement statement = null;// w  w w. j av  a 2 s .  co  m
    ResultSet resultSet = null;
    try {
        statement = DbKit.getConfig("gbase").getConnection().createStatement();
        String sql = "";
        if (tabName.equals("gn")) {
            sql = "SELECT `xdr_id`,`imsi`,`msisdn`,`imei`,`imei_tac`,`tac`,`eci`,`sgw_ip`,`sgw_port`,`apn`,`start_time`,`end_time`,`time_hour`,`time_day`,`protocol`,`app_type`,`app_subtype`,`app_content`,`app_status`,`user_ip`,`user_ip_type`,`user_port`,`l4_protocol`,`appserver_ip`,`appserver_ip_type`,`appserver_port`,`ulthroughput`,`dlthroughput`,`ulpackets`,`dlpackets`,`ultcp_disorder_packets`,`dltcp_disorder_packets`,`ultcp_retransfer_packets`,`dltcp_retransfer_packets`,`ultcp_response_time`,`dltcp_response_time`,`ultcp_flag_packets`,`dltcp_flag_packets`,`tcplink_response_time1`,`tcplink_response_time2`,`window_size`,`mss_size`,`tcplink_count`,`tcplink_state`,`finish`,`session_duration` FROM `dw`.`f_ms_s1u_general`";
            sql += " where 2>1";
            if (startDate != null) {
                sql += " and start_time=" + startDate;
            }
            if (endDate != null) {
                sql += " and end_time=" + endDate;
            }
            if (xdrId != "" && xdrId != null) {
                sql += " and xdr_id='" + xdrId + "'";
            }
            if (phone != "" && phone != null) {
                sql += " and msisdn=" + phone;
            }
            sql += " limit 0,1";
        } else if (tabName.equals("http")) {
            sql = "  SELECT `xdr_id`,`imsi`,`msisdn`,`imei`,`imei_tac`,`tac`,`eci`,`sgw_ip`,`sgw_port`,`apn`,`start_time`,`end_time`,`time_hour`,`time_day`,`protocol`,`app_type`,`app_subtype`,`app_content`,`app_status`,`user_ip`,`user_ip_type`,`user_port`,`l4_protocol`,`appserver_ip`,`appserver_ip_type`,`appserver_port`,`ulthroughput`,`dlthroughput`,`ulpackets`,`dlpackets`,`ultcp_disorder_packets`,`dltcp_disorder_packets`,`ultcp_retransfer_packets`,`dltcp_retransfer_packets`,`ultcp_response_time`,`dltcp_response_time`,`ultcp_flag_packets`,`dltcp_flag_packets`,`tcplink_response_time1`,`tcplink_response_time2`,`window_size`,`mss_size`,`tcplink_count`,`tcplink_state`,`finish`,`http_version`,`trans_type`,`http_status_code`,`response_time`,`lastpacket_delay`,`lastpacket_ack_delay`,`host`,`uri1`,`x_online_host`,`useragent`,`content_type`,`refer_uri`,`cookie`,`content_length`,`target_behavior`,`wtp_suspend_type`,`wtp_suspend_cause`,`title`,`keyword`,`action`,`succ_flag`,`action_delay`,`browse_tool`,`portals`,`session_duration` FROM `dw`.`f_ms_s1u_http`";
            sql += " where 2>1";
            if (startDate != null) {
                sql += " and start_time=" + startDate;
            }
            if (endDate != null) {
                sql += " and end_time=" + endDate;
            }
            if (xdrId != "" && xdrId != null) {
                sql += " and xdr_id='" + xdrId + "'";
            }
            if (phone != "" && phone != null) {
                sql += " and msisdn=" + phone;
            }
            sql += " limit 0,1";
        } else if (tabName.equals("dns")) {
            sql = "SELECT `xdr_id`,`imsi`,`msisdn`,`imei`,`imei_tac`,`tac`,`eci`,`sgw_ip`,`sgw_port`,`apn`,`start_time`,`end_time`,`time_hour`,`time_day`,`protocol`,`app_type`,`app_subtype`,`app_content`,`app_status`,`user_ip`,`user_ip_type`,`user_port`,`l4_protocol`,`appserver_ip`,`appserver_ip_type`,`appserver_port`,`ulthroughput`,`dlthroughput`,`ulpackets`,`dlpackets`,`ultcp_disorder_packets`,`dltcp_disorder_packets`,`ultcp_retransfer_packets`,`dltcp_retransfer_packets`,`ultcp_response_time`,`dltcp_response_time`,`ultcp_flag_packets`,`dltcp_flag_packets`,`tcplink_response_time1`,`tcplink_response_time2`,`window_size`,`mss_size`,`tcplink_count`,`tcplink_state`,`finish`,`domain`,`query_result_ip`,`dns_reply_code`,`dns_request_count`,`dns_response_count`,`dns_licensed_content`,`dns_add_content`,`session_duration` FROM `dw`.`f_ms_s1u_dns`";
            sql += " where 2>1";
            if (startDate != null) {
                sql += " and start_time=" + startDate;
            }
            if (endDate != null) {
                sql += " and end_time=" + endDate;
            }
            if (xdrId != "" && xdrId != null) {
                sql += " and xdr_id='" + xdrId + "'";
            }
            if (phone != "" && phone != null) {
                sql += " and msisdn=" + phone;
            }
            sql += " limit 0,1";
        } else if (tabName.equals("im")) {
            sql = "SELECT `xdr_id`,`imsi`,`msisdn`,`imei`,`imei_tac`,`tac`,`eci`,`sgw_ip`,`sgw_port`,`apn`,`start_time`,`end_time`,`time_hour`,`time_day`,`protocol`,`app_type`,`app_subtype`,`app_content`,`app_status`,`user_ip`,`user_ip_type`,`user_port`,`l4_protocol`,`appserver_ip`,`appserver_ip_type`,`appserver_port`,`ulthroughput`,`dlthroughput`,`ulpackets`,`dlpackets`,`ultcp_disorder_packets`,`dltcp_disorder_packets`,`ultcp_retransfer_packets`,`dltcp_retransfer_packets`,`ultcp_response_time`,`dltcp_response_time`,`ultcp_flag_packets`,`dltcp_flag_packets`,`tcplink_response_time1`,`tcplink_response_time2`,`window_size`,`mss_size`,`tcplink_count`,`tcplink_state`,`finish`,`username`,`version`,`client_type`,`content_type`,`session_duration` FROM `dw`.`f_ms_s1u_im`";
            sql += " where 2>1";
            if (startDate != null) {
                sql += " and start_time=" + startDate;
            }
            if (endDate != null) {
                sql += " and end_time=" + endDate;
            }
            if (xdrId != "" && xdrId != null) {
                sql += " and xdr_id='" + xdrId + "'";
            }
            if (phone != "" && phone != null) {
                sql += " and msisdn=" + phone;
            }
            sql += " limit 0,1";
        } else if (tabName.equals("mme")) {
            sql = "SELECT `xdr_id`,`imsi`,`msisdn`,`imei`,`imei_tac`,`tac`,`eci`,`other_tac`,`other_eci`,`apn`,`mme_ip`,`mme_port`,`start_time`,`end_time`,`time_hour`,`time_day`,`procedure_type`,`procedure_status`,`request_cause`,`failure_cause`,`eps_bearer_number`,`bearer_1_id`,`bearer_1_type`,`bearer_1_qci`,`bearer_1_status`,`bearer_1_request_cause`,`bearer_1_failure_cause`,`bearer_1_enb_gtp_teid`,`bearer_1_sgw_gtp_teid`,`bearer_2_id`,`bearer_2_type`,`bearer_2_qci`,`bearer_2_status`,`bearer_2_request_cause`,`bearer_2_failure_cause`,`bearer_2_enb_gtp_teid`,`bearer_2_sgw_gtp_teid`,`session_duration` FROM `dw`.`f_ms_s1mme`";
            sql += " where 2>1";
            if (startDate != null) {
                sql += " and start_time=" + startDate;
            }
            if (endDate != null) {
                sql += " and end_time=" + endDate;
            }
            if (xdrId != "" && xdrId != null) {
                sql += " and xdr_id='" + xdrId + "'";
            }
            if (phone != "" && phone != null) {
                sql += " and msisdn=" + phone;
            }
            sql += " limit 0,1";
        }

        System.out.println(sql);
        resultSet = statement.executeQuery(sql);

        String cols = "";
        JSONArray array = new JSONArray();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnLabel(i);
            if (!cols.equals("")) {
                cols += ",";
            }
            cols += columnName;
        }
        while (resultSet.next()) {
            JSONObject jsonObj = new JSONObject();

            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnLabel(i);
                String value = resultSet.getString(columnName);
                jsonObj.put(columnName, value);
            }
            array.add(jsonObj);
        }
        signallingDetail.setSql(sql);
        signallingDetail.setCols(cols);
        signallingDetail.setJsonArray(array);
    } catch (Exception ex) {
        throw ex;
    } finally {
        if (statement != null) {
            statement.close();
        }
        if (!resultSet.equals(null)) {
            resultSet.close();
        }
    }

    return signallingDetail;
}

From source file:org.sakaiproject.webservices.SakaiReport.java

protected String toJsonString(ResultSet rs) throws SQLException, JSONException {
    ResultSetMetaData rsmd = rs.getMetaData();
    JSONArray array = new JSONArray();
    int numColumns = rsmd.getColumnCount();

    while (rs.next()) {

        JSONObject obj = new JSONObject();
        for (int i = 1; i < numColumns + 1; i++) {

            String column_label = rsmd.getColumnLabel(i);

            LOG.debug("Column Name=" + column_label + ",type=" + rsmd.getColumnType(i));

            switch (rsmd.getColumnType(i)) {
            case Types.ARRAY:
                obj.put(column_label, rs.getArray(i));
                break;
            case Types.BIGINT:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.BOOLEAN:
                obj.put(column_label, rs.getBoolean(i));
                break;
            case Types.BLOB:
                obj.put(column_label, rs.getBlob(i));
                break;
            case Types.DOUBLE:
                obj.put(column_label, rs.getDouble(i));
                break;
            case Types.FLOAT:
                obj.put(column_label, rs.getFloat(i));
                break;
            case Types.INTEGER:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.NVARCHAR:
                obj.put(column_label, rs.getNString(i));
                break;
            case Types.VARCHAR:
                obj.put(column_label, rs.getString(i));
                break;
            case Types.TINYINT:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.SMALLINT:
                obj.put(column_label, rs.getInt(i));
                break;
            case Types.DATE:
                obj.put(column_label, rs.getDate(i));
                break;
            case Types.TIMESTAMP:
                obj.put(column_label, rs.getTimestamp(i));
                break;
            default:
                obj.put(column_label, rs.getObject(i));
                break;
            }//  ww  w  . ja v  a  2  s .  c  o m

        }
        array.put(obj);

    }
    return array.toString();
}

From source file:org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.java

@Override
public Schema getSchema(InitializerContext context, LinkConfiguration linkConfig,
        FromJobConfiguration fromJobConfig) {
    configureJdbcProperties(context.getContext(), linkConfig, fromJobConfig);

    String schemaName = fromJobConfig.fromJobConfig.tableName;
    if (schemaName == null) {
        schemaName = "Query";
    } else if (fromJobConfig.fromJobConfig.schemaName != null) {
        schemaName = fromJobConfig.fromJobConfig.schemaName + "." + schemaName;
    }/*from w ww .jav a 2  s  .c o m*/

    Schema schema = new Schema(schemaName);
    ResultSet rs = null;
    ResultSetMetaData rsmt = null;
    try {
        rs = executor.executeQuery(context.getString(GenericJdbcConnectorConstants.CONNECTOR_JDBC_FROM_DATA_SQL)
                .replace(GenericJdbcConnectorConstants.SQL_CONDITIONS_TOKEN, "1 = 0"));

        rsmt = rs.getMetaData();
        for (int i = 1; i <= rsmt.getColumnCount(); i++) {
            Column column = SqlTypesUtils.sqlTypeToAbstractType(rsmt.getColumnType(i));

            String columnName = rsmt.getColumnName(i);
            if (columnName == null || columnName.equals("")) {
                columnName = rsmt.getColumnLabel(i);
                if (null == columnName) {
                    columnName = "Column " + i;
                }
            }

            column.setName(columnName);
            schema.addColumn(column);
        }

        return schema;
    } catch (SQLException e) {
        throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0016, e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.info("Ignoring exception while closing ResultSet", e);
            }
        }
        if (executor != null) {
            executor.close();
        }
    }
}

From source file:org.apache.camel.component.jdbc.JdbcProducer.java

/**
 * Sets the result from the ResultSet to the Exchange as its OUT body.
 *///w w w  .j  a  v  a 2 s  . com
protected void setResultSet(Exchange exchange, ResultSet rs) throws SQLException {
    ResultSetMetaData meta = rs.getMetaData();

    // should we use jdbc4 or jdbc3 semantics
    boolean jdbc4 = getEndpoint().isUseJDBC4ColumnNameAndLabelSemantics();

    int count = meta.getColumnCount();
    List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
    int rowNumber = 0;
    while (rs.next() && (readSize == 0 || rowNumber < readSize)) {
        Map<String, Object> row = new HashMap<String, Object>();
        for (int i = 0; i < count; i++) {
            int columnNumber = i + 1;
            // use column label to get the name as it also handled SQL SELECT aliases
            String columnName;
            if (jdbc4) {
                // jdbc 4 should use label to get the name
                columnName = meta.getColumnLabel(columnNumber);
            } else {
                // jdbc 3 uses the label or name to get the name
                try {
                    columnName = meta.getColumnLabel(columnNumber);
                } catch (SQLException e) {
                    columnName = meta.getColumnName(columnNumber);
                }
            }
            // use index based which should be faster
            row.put(columnName, rs.getObject(columnNumber));
        }
        data.add(row);
        rowNumber++;
    }
    exchange.getOut().setHeader(JdbcConstants.JDBC_ROW_COUNT, rowNumber);
    exchange.getOut().setBody(data);
}

From source file:org.apache.nifi.processors.standard.util.TestJdbcCommon.java

@Test
public void testCreateSchemaOnlyColumnLabel() throws ClassNotFoundException, SQLException {

    final ResultSet resultSet = mock(ResultSet.class);
    final ResultSetMetaData resultSetMetaData = mock(ResultSetMetaData.class);
    when(resultSet.getMetaData()).thenReturn(resultSetMetaData);
    when(resultSetMetaData.getColumnCount()).thenReturn(2);
    when(resultSetMetaData.getTableName(1)).thenReturn("TEST");
    when(resultSetMetaData.getColumnType(1)).thenReturn(Types.INTEGER);
    when(resultSetMetaData.getColumnName(1)).thenReturn("");
    when(resultSetMetaData.getColumnLabel(1)).thenReturn("ID");
    when(resultSetMetaData.getColumnType(2)).thenReturn(Types.VARCHAR);
    when(resultSetMetaData.getColumnName(2)).thenReturn("VCHARC");
    when(resultSetMetaData.getColumnLabel(2)).thenReturn("NOT_VCHARC");

    final Schema schema = JdbcCommon.createSchema(resultSet);
    assertNotNull(schema);// w  w  w. j a v  a  2 s  . co m

    assertNotNull(schema.getField("ID"));
    assertNotNull(schema.getField("NOT_VCHARC"));

    // records name, should be result set first column table name
    assertEquals("TEST", schema.getName());
}

From source file:com.tascape.reactor.report.MySqlBaseBean.java

public void importJson(JSONObject json) throws NamingException, SQLException {
    JSONObject sr = json.getJSONObject("suite_result");
    String srid = sr.getString(SuiteResult.SUITE_RESULT_ID);
    LOG.debug("srid {}", srid);

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + SuiteResult.TABLE_NAME + " WHERE " + SuiteResult.SUITE_RESULT_ID
                + " = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setString(1, srid);//from w ww .  jav a  2 s  .com
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        if (rs.first()) {
            LOG.debug("already imported {}", srid);
            return;
        }
        rs.moveToInsertRow();
        for (int col = 1; col <= rsmd.getColumnCount(); col++) {
            String cn = rsmd.getColumnLabel(col);
            rs.updateObject(cn, sr.opt(cn));
        }
        rs.insertRow();
        rs.last();
        rs.updateRow();
        LOG.debug("sr imported");
    }

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + SuiteProperty.TABLE_NAME + " WHERE " + SuiteProperty.SUITE_RESULT_ID
                + " = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setString(1, srid);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();

        JSONArray sps = sr.getJSONArray("suite_properties");
        int len = sps.length();
        for (int i = 0; i < len; i++) {
            rs.moveToInsertRow();
            JSONObject tr = sps.getJSONObject(i);
            for (int col = 1; col <= rsmd.getColumnCount(); col++) {
                String cn = rsmd.getColumnLabel(col);
                if (SuiteProperty.SUITE_PROPERTY_ID.equals(cn)) {
                    continue;
                }
                rs.updateObject(cn, tr.get(cn));
            }
            rs.insertRow();
            rs.last();
            rs.updateRow();
        }
        LOG.debug("sps imported");
    }

    JSONArray trs = sr.getJSONArray("case_results");
    int len = trs.length();

    try (Connection conn = this.getConnection()) {
        String sql = String.format("SELECT * FROM %s WHERE %s=? AND %s=? AND %s=? AND %s=? AND %s=?;",
                TaskCase.TABLE_NAME, TaskCase.SUITE_CLASS, TaskCase.CASE_CLASS, TaskCase.CASE_METHOD,
                TaskCase.CASE_DATA_INFO, TaskCase.CASE_DATA);
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setMaxRows(1);
        for (int i = 0; i < len; i++) {
            JSONObject tr = trs.getJSONObject(i);
            stmt.setString(1, tr.getString(TaskCase.SUITE_CLASS));
            stmt.setString(2, tr.getString(TaskCase.CASE_CLASS));
            stmt.setString(3, tr.getString(TaskCase.CASE_METHOD));
            stmt.setString(4, tr.getString(TaskCase.CASE_DATA_INFO));
            stmt.setString(5, tr.getString(TaskCase.CASE_DATA));
            ResultSet rs = stmt.executeQuery();
            if (!rs.first()) {
                rs.moveToInsertRow();
                rs.updateString(TaskCase.SUITE_CLASS, tr.getString(TaskCase.SUITE_CLASS));
                rs.updateString(TaskCase.CASE_CLASS, tr.getString(TaskCase.CASE_CLASS));
                rs.updateString(TaskCase.CASE_METHOD, tr.getString(TaskCase.CASE_METHOD));
                rs.updateString(TaskCase.CASE_DATA_INFO, tr.getString(TaskCase.CASE_DATA_INFO));
                rs.updateString(TaskCase.CASE_DATA, tr.getString(TaskCase.CASE_DATA));
                rs.insertRow();
                rs.last();
                rs.updateRow();
                rs = stmt.executeQuery();
                rs.first();
            }
            tr.put(TaskCase.TASK_CASE_ID, rs.getLong(TaskCase.TASK_CASE_ID));
        }
        LOG.debug("tcid updated");
    }

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + CaseResult.TABLE_NAME + " WHERE " + CaseResult.SUITE_RESULT + " = ?;";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setString(1, srid);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 0; i < len; i++) {
            rs.moveToInsertRow();
            JSONObject tr = trs.getJSONObject(i);
            for (int col = 1; col <= rsmd.getColumnCount(); col++) {
                String cn = rsmd.getColumnLabel(col);
                rs.updateObject(cn, tr.opt(cn));
            }
            rs.insertRow();
            rs.last();
            rs.updateRow();
        }
        LOG.debug("crs imported");
    }

    try (Connection conn = this.getConnection()) {
        String sql = "SELECT * FROM " + CaseResultMetric.TABLE_NAME + ";";
        PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        stmt.setMaxRows(1);
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int i = 0; i < len; i++) {
            JSONArray jarr = trs.getJSONObject(i).optJSONArray("CASE_result_metrics");
            if (jarr == null) {
                continue;
            }
            int l = jarr.length();
            for (int j = 0; j < l; j++) {
                JSONObject trm = jarr.getJSONObject(j);
                rs.moveToInsertRow();
                for (int col = 1; col <= rsmd.getColumnCount(); col++) {
                    String cn = rsmd.getColumnLabel(col);
                    if (cn.equals(CaseResultMetric.CASE_RESULT_METRIC_ID)) {
                        continue;
                    }
                    rs.updateObject(cn, trm.get(cn));
                }
                rs.insertRow();
                rs.last();
                rs.updateRow();
            }
        }
        LOG.debug("crms imported");
    }
}

From source file:org.gbif.ipt.service.manage.impl.SourceManagerImpl.java

private List<String> columns(SqlSource source) {
    List<String> columns = new ArrayList<String>();
    Connection con = null;/*from w  w w . j av  a 2  s  .co m*/
    Statement stmt = null;
    ResultSet rs = null;
    try {
        con = getDbConnection(source);
        if (con != null) {
            // test sql
            stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(1);
            rs = stmt.executeQuery(source.getSqlLimited(1));
            // get column metadata
            ResultSetMetaData meta = rs.getMetaData();
            int idx = 1;
            int max = meta.getColumnCount();
            while (idx <= max) {
                columns.add(meta.getColumnLabel(idx));
                idx++;
            }
        } else {
            String msg = "Can't read sql source, the connection couldn't be created with the current parameters";
            columns.add(msg);
            log.warn(msg + " " + source);
        }
    } catch (SQLException e) {
        log.warn("Cant read sql source " + source, e);
    } finally {
        // close result set, statement, and connection in that order
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("ResultSet could not be closed: " + e.getMessage(), e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                log.error("Statement could not be closed: " + e.getMessage(), e);
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                log.error("Connection could not be closed: " + e.getMessage(), e);
            }
        }
    }
    return columns;
}