Example usage for java.sql ResultSet getObject

List of usage examples for java.sql ResultSet getObject

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. getObject

Introduction

In this page you can find the example usage for java.sql ResultSet getObject.

Prototype

Object getObject(String columnLabel) throws SQLException;

Source Link

Document

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

Usage

From source file:com.krawler.esp.servlets.AdminServlet.java

public static String getWorkWeek(Connection conn, HttpServletRequest request) throws ServiceException {
    String res = "";
    try {//from  w  w w  .  j  a  v  a2  s. c  om
        JSONObject jObj = new JSONObject();
        PreparedStatement pstmt = conn.prepareStatement(
                "SELECT day,intime,outtime,isholiday FROM proj_workweek WHERE projectid = ? ORDER BY day");
        pstmt.setString(1, request.getParameter("projid"));
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            JSONObject rObj = new JSONObject();
            rObj.put("day", rs.getObject("day").toString());
            rObj.put("intime", rs.getString("intime"));
            rObj.put("outtime", rs.getString("outtime"));
            rObj.put("isholiday", rs.getObject("isholiday").toString());
            jObj.append("data", rObj);
        }
        res = jObj.toString();
    } catch (Exception ex) {
        res = ex.getMessage();
    }
    return res;
}

From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java

public static long convertToAvroStream(final ResultSet rs, final OutputStream outStream, String recordName,
        ResultSetRowCallback callback, final int maxRows, boolean convertNames)
        throws SQLException, IOException {
    final Schema schema = createSchema(rs, recordName, convertNames);
    final GenericRecord rec = new GenericData.Record(schema);

    final DatumWriter<GenericRecord> datumWriter = new GenericDatumWriter<>(schema);
    try (final DataFileWriter<GenericRecord> dataFileWriter = new DataFileWriter<>(datumWriter)) {
        dataFileWriter.create(schema, outStream);

        final ResultSetMetaData meta = rs.getMetaData();
        final int nrOfColumns = meta.getColumnCount();
        long nrOfRows = 0;
        while (rs.next()) {
            if (callback != null) {
                callback.processRow(rs);
            }//from  w w w  .ja v  a 2s.  c om
            for (int i = 1; i <= nrOfColumns; i++) {
                final int javaSqlType = meta.getColumnType(i);

                // Need to handle CLOB and BLOB before getObject() is called, due to ResultSet's maximum portability statement
                if (javaSqlType == CLOB) {
                    Clob clob = rs.getClob(i);
                    if (clob != null) {
                        long numChars = clob.length();
                        char[] buffer = new char[(int) numChars];
                        InputStream is = clob.getAsciiStream();
                        int index = 0;
                        int c = is.read();
                        while (c > 0) {
                            buffer[index++] = (char) c;
                            c = is.read();
                        }
                        rec.put(i - 1, new String(buffer));
                        clob.free();
                    } else {
                        rec.put(i - 1, null);
                    }
                    continue;
                }

                if (javaSqlType == BLOB) {
                    Blob blob = rs.getBlob(i);
                    if (blob != null) {
                        long numChars = blob.length();
                        byte[] buffer = new byte[(int) numChars];
                        InputStream is = blob.getBinaryStream();
                        int index = 0;
                        int c = is.read();
                        while (c > 0) {
                            buffer[index++] = (byte) c;
                            c = is.read();
                        }
                        ByteBuffer bb = ByteBuffer.wrap(buffer);
                        rec.put(i - 1, bb);
                        blob.free();
                    } else {
                        rec.put(i - 1, null);
                    }
                    continue;
                }

                final Object value = rs.getObject(i);

                if (value == null) {
                    rec.put(i - 1, null);

                } else if (javaSqlType == BINARY || javaSqlType == VARBINARY || javaSqlType == LONGVARBINARY
                        || javaSqlType == ARRAY) {
                    // bytes requires little bit different handling
                    byte[] bytes = rs.getBytes(i);
                    ByteBuffer bb = ByteBuffer.wrap(bytes);
                    rec.put(i - 1, bb);

                } else if (value instanceof Byte) {
                    // tinyint(1) type is returned by JDBC driver as java.sql.Types.TINYINT
                    // But value is returned by JDBC as java.lang.Byte
                    // (at least H2 JDBC works this way)
                    // direct put to avro record results:
                    // org.apache.avro.AvroRuntimeException: Unknown datum type java.lang.Byte
                    rec.put(i - 1, ((Byte) value).intValue());
                } else if (value instanceof Short) {
                    //MS SQL returns TINYINT as a Java Short, which Avro doesn't understand.
                    rec.put(i - 1, ((Short) value).intValue());
                } else if (value instanceof BigDecimal) {
                    // Avro can't handle BigDecimal as a number - it will throw an AvroRuntimeException such as: "Unknown datum type: java.math.BigDecimal: 38"
                    try {
                        int scale = meta.getScale(i);
                        BigDecimal bigDecimal = ((BigDecimal) value);
                        if (scale == 0) {
                            if (meta.getPrecision(i) < 10) {
                                rec.put(i - 1, bigDecimal.intValue());
                            } else {
                                rec.put(i - 1, bigDecimal.longValue());
                            }
                        } else {
                            rec.put(i - 1, bigDecimal.doubleValue());
                        }
                    } catch (Exception e) {
                        rec.put(i - 1, value.toString());
                    }
                } else if (value instanceof BigInteger) {
                    // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that.
                    // It the SQL type is BIGINT and the precision is between 0 and 19 (inclusive); if so, the BigInteger is likely a
                    // long (and the schema says it will be), so try to get its value as a long.
                    // Otherwise, Avro can't handle BigInteger as a number - it will throw an AvroRuntimeException
                    // such as: "Unknown datum type: java.math.BigInteger: 38". In this case the schema is expecting a string.
                    if (javaSqlType == BIGINT) {
                        int precision = meta.getPrecision(i);
                        if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                            rec.put(i - 1, value.toString());
                        } else {
                            try {
                                rec.put(i - 1, ((BigInteger) value).longValueExact());
                            } catch (ArithmeticException ae) {
                                // Since the value won't fit in a long, convert it to a string
                                rec.put(i - 1, value.toString());
                            }
                        }
                    } else {
                        rec.put(i - 1, value.toString());
                    }

                } else if (value instanceof Number || value instanceof Boolean) {
                    if (javaSqlType == BIGINT) {
                        int precision = meta.getPrecision(i);
                        if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                            rec.put(i - 1, value.toString());
                        } else {
                            rec.put(i - 1, value);
                        }
                    } else {
                        rec.put(i - 1, value);
                    }

                } else {
                    // The different types that we support are numbers (int, long, double, float),
                    // as well as boolean values and Strings. Since Avro doesn't provide
                    // timestamp types, we want to convert those to Strings. So we will cast anything other
                    // than numbers or booleans to strings by using the toString() method.
                    rec.put(i - 1, value.toString());
                }
            }
            dataFileWriter.append(rec);
            nrOfRows += 1;

            if (maxRows > 0 && nrOfRows == maxRows)
                break;
        }

        return nrOfRows;
    }
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

/**
 * @param conn/*  ww w  . j  a v  a 2s  .  co  m*/
 * @param sql
 * @param includeHeaderRow
 * @return
 */
public static Vector<Object[]> query(final Connection conn, final String sql, final boolean includeHeaderRow) {
    Vector<Object[]> list = new Vector<Object[]>();
    Statement stmt = null;

    Connection connection = null;
    boolean doCloseConn = false;
    boolean doSkipConnSet = false;
    boolean isStale = true;
    int tries = 0;

    while (isStale && tries < 3) {
        try {
            if (!doSkipConnSet) {
                if (conn != null) {
                    connection = conn;

                } else if (dbConn != null) {
                    connection = dbConn;
                } else {
                    connection = DBConnection.getInstance().createConnection();
                    doCloseConn = true;
                }
            }

            if (connection == null) {
                return list;
            }

            tries++;
            stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            int numCols = metaData.getColumnCount();
            if (includeHeaderRow) {
                Object[] colData = new Object[numCols];
                list.add(colData);
                for (int i = 0; i < numCols; i++) {
                    colData[i] = metaData.getColumnName(i + 1);
                }
            }
            while (rs.next()) {
                Object[] colData = new Object[numCols];
                list.add(colData);
                for (int i = 0; i < numCols; i++) {
                    colData[i] = rs.getObject(i + 1);
                }
            }
            rs.close();

            isStale = false;

        } catch (CommunicationsException ex) {
            connection = DBConnection.getInstance().createConnection();
            doCloseConn = true;
            doSkipConnSet = true;

        } catch (com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException e) {
            e.printStackTrace();
            if (!skipTrackExceptions) {
                edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, e);
            }

        } catch (SQLException ex) {
            ex.printStackTrace();

            if (!skipTrackExceptions) {
                edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
                edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
            }

        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception ex) {
                }
            }
        }

        if (!isStale && connection != null && doCloseConn) {
            try {
                connection.close();
            } catch (Exception ex) {
            }
        }
    }

    return list;
}

From source file:mondrian.test.loader.MondrianFoodMartLoader.java

/**
 * String representation of the column in the result set, suitable for
 * inclusion in a SQL insert statement.<p/>
 *
 * The column in the result set is transformed according to the type in
 * the column parameter.<p/>//from w w  w .  j  a  va 2 s .co m
 *
 * Different DBMSs (and drivers) return different Java types for a given
 * column; {@link ClassCastException}s may occur.
 *
 * @param rs        ResultSet row to process
 * @param column    Column to process
 * @return          String representation of column value
 */
private String columnValue(ResultSet rs, Column column) throws Exception {
    Object obj = rs.getObject(column.name);
    String columnType = column.typeName;

    if (obj == null) {
        return "NULL";
    }

    /*
     * Output for an INTEGER column, handling Doubles and Integers
     * in the result set
     */
    if (columnType.startsWith(Type.Integer.name)) {
        if (obj.getClass() == Double.class) {
            try {
                Double result = (Double) obj;
                return integerFormatter.format(result.doubleValue());
            } catch (ClassCastException cce) {
                LOGGER.error("CCE: " + column.name + " to Long from: " + obj.getClass().getName() + " - "
                        + obj.toString());
                throw cce;
            }
        } else {
            try {
                int result = ((Number) obj).intValue();
                return Integer.toString(result);
            } catch (ClassCastException cce) {
                LOGGER.error("CCE: " + column.name + " to Integer from: " + obj.getClass().getName() + " - "
                        + obj.toString());
                throw cce;
            }
        }

        /*
         * Output for an SMALLINT column, handling Integers
         * in the result set
         */
    } else if (columnType.startsWith(Type.Smallint.name)) {
        if (obj instanceof Boolean) {
            return (Boolean) obj ? "1" : "0";
        } else {
            try {
                Integer result = (Integer) obj;
                return result.toString();
            } catch (ClassCastException cce) {
                LOGGER.error("CCE: " + column.name + " to Integer from: " + obj.getClass().getName() + " - "
                        + obj.toString());
                throw cce;
            }
        }
        /*
         * Output for an BIGINT column, handling Doubles and Longs
         * in the result set
         */
    } else if (columnType.startsWith("BIGINT")) {
        if (obj.getClass() == Double.class) {
            try {
                Double result = (Double) obj;
                return integerFormatter.format(result.doubleValue());
            } catch (ClassCastException cce) {
                LOGGER.error("CCE: " + column.name + " to Double from: " + obj.getClass().getName() + " - "
                        + obj.toString());
                throw cce;
            }
        } else {
            try {
                Long result = (Long) obj;
                return result.toString();
            } catch (ClassCastException cce) {
                LOGGER.error("CCE: " + column.name + " to Long from: " + obj.getClass().getName() + " - "
                        + obj.toString());
                throw cce;
            }
        }

        /*
         * Output for a String, managing embedded quotes
         */
    } else if (columnType.startsWith("VARCHAR")) {
        return embedQuotes((String) obj);

        /*
         * Output for a TIMESTAMP
         */
    } else {
        if (columnType.startsWith("TIMESTAMP")) {
            Timestamp ts = (Timestamp) obj;

            // REVIEW jvs 26-Nov-2006:  Is it safe to replace
            // these with dialect.quoteTimestampLiteral, etc?

            switch (dialect.getDatabaseProduct()) {
            case ORACLE:
            case LUCIDDB:
            case NEOVIEW:
                return "TIMESTAMP '" + ts + "'";
            default:
                return "'" + ts + "'";
            }
            //return "'" + ts + "'" ;

            /*
             * Output for a DATE
             */
        } else if (columnType.startsWith("DATE")) {
            Date dt = (Date) obj;
            switch (dialect.getDatabaseProduct()) {
            case ORACLE:
            case LUCIDDB:
            case NEOVIEW:
                return "DATE '" + dateFormatter.format(dt) + "'";
            default:
                return "'" + dateFormatter.format(dt) + "'";
            }

            /*
             * Output for a FLOAT
             */
        } else if (columnType.startsWith(Type.Real.name)) {
            Float result = (Float) obj;
            return result.toString();

            /*
             * Output for a DECIMAL(length, places)
             */
        } else if (columnType.startsWith("DECIMAL")) {
            final Matcher matcher = decimalDataTypeRegex.matcher(columnType);
            if (!matcher.matches()) {
                throw new Exception("Bad DECIMAL column type for " + columnType);
            }
            DecimalFormat formatter = new DecimalFormat(decimalFormat(matcher.group(1), matcher.group(2)));
            if (obj.getClass() == Double.class) {
                try {
                    Double result = (Double) obj;
                    return formatter.format(result.doubleValue());
                } catch (ClassCastException cce) {
                    LOGGER.error("CCE: " + column.name + " to Double from: " + obj.getClass().getName() + " - "
                            + obj.toString());
                    throw cce;
                }
            } else {
                // should be (obj.getClass() == BigDecimal.class)
                try {
                    BigDecimal result = (BigDecimal) obj;
                    return formatter.format(result);
                } catch (ClassCastException cce) {
                    LOGGER.error("CCE: " + column.name + " to BigDecimal from: " + obj.getClass().getName()
                            + " - " + obj.toString());
                    throw cce;
                }
            }

            /*
             * Output for a BOOLEAN (Postgres) or BIT (other DBMSs)
             */
        } else if (columnType.startsWith("BOOLEAN") || columnType.startsWith("BIT")) {
            Boolean result = (Boolean) obj;
            return result.toString();
            /*
             * Output for a BOOLEAN - TINYINT(1) (MySQL)
             */
        } else if (columnType.startsWith("TINYINT(1)")) {
            return (Boolean) obj ? "1" : "0";
        }
    }
    throw new Exception("Unknown column type: " + columnType + " for column: " + column.name);
}

From source file:it.webappcommon.lib.dao.AbstractBaseDAO.java

public <X> List<X> getAllFieldDistinct(F filtro, String field, Class<X> clazz) throws Exception {
    List<X> res = new ArrayList<X>();

    PreparedStatement prpStmt = null;
    StringBuilder sqlQuery = null;
    ResultSet rs = null;

    try {/*from   w w  w . j  a  va  2  s .co  m*/
        sqlQuery = new StringBuilder();

        if (StringUtils.isEmpty(field)) {
            field = getCampoID();
        }

        sqlQuery.append("SELECT DISTINCT(" + field + ") as IDTROVATI FROM ");
        sqlQuery.append(" " + getNomeTabella() + " ");
        sqlQuery.append("WHERE ");

        // Verifico se e' specificato un filtro
        if (filtro != null) {
            sqlQuery.append(filtro.getSQLWhere());
        } else {
            sqlQuery.append(QueryBuilder.ALWAYS_TRUE); // TODO: se il
            // filtro e' null
            // non
            // posso accodare il resto delle
            // conzioni di base se non faccio
            // questo.
        }
        // Verifico se e' specificato un campo cancellazione
        if (getCampoDataCancellazione() != null && !filtro.isIncludeDeleted()) {
            sqlQuery.append(" AND " + getCampoDataCancellazione() + " IS NULL");
        }
        // Verifico se e' specificato un ordinamento
        if (filtro != null) {
            sqlQuery.append(filtro.getSQLSort());
        }
        // Verifico se e' specificato un limit per le paginazioni
        if (filtro != null) {
            sqlQuery.append(filtro.getSQLLimit());
        }

        String sqlString = sqlQuery.toString();
        logger.debug(sqlString);
        prpStmt = getConnection().prepareStatement(sqlString);
        logger.debug(prpStmt);
        rs = prpStmt.executeQuery();

        while (rs.next()) {
            res.add(clazz.cast(rs.getObject("IDTROVATI")));
        }

    } catch (Exception ex) {
        // rollback();
        logger.error(prpStmt, ex);
        throw ex;
    } finally {
        DAOUtils.close(getConnection(), prpStmt, rs, connApertaQui);
    }

    return res;

}

From source file:com.alibaba.otter.manager.biz.utils.DataSourceChecker.java

@SuppressWarnings("resource")
public String check(String url, String username, String password, String encode, String sourceType) {
    Connection conn = null;/*from   w w w.  j a v a2  s  . c om*/
    Statement stmt = null;
    ResultSet rs = null;
    // boolean typeConflict = true;
    // if ((sourceType.toLowerCase().equals(MYSQL_FLAG) &&
    // url.toLowerCase().contains(MYSQL_FLAG))
    // || sourceType.toLowerCase().equals(ORACLE_FLAG) &&
    // url.toLowerCase().contains(ORACLE_FLAG)) {
    // typeConflict = false;
    // }
    //
    // if (typeConflict) {
    // return DBTYPE_CONFLICT;
    // }

    DataSource dataSource = null;
    try {

        DbMediaSource dbMediaSource = new DbMediaSource();
        dbMediaSource.setUrl(url);
        dbMediaSource.setUsername(username);
        dbMediaSource.setPassword(password);
        dbMediaSource.setEncode(encode);

        if (sourceType.equalsIgnoreCase("MYSQL")) {
            dbMediaSource.setType(DataMediaType.MYSQL);
            dbMediaSource.setDriver("com.mysql.jdbc.Driver");
        } else if (sourceType.equalsIgnoreCase("ORACLE")) {
            dbMediaSource.setType(DataMediaType.ORACLE);
            dbMediaSource.setDriver("oracle.jdbc.driver.OracleDriver");
        }

        dataSource = dataSourceCreator.createDataSource(dbMediaSource);
        try {
            conn = dataSource.getConnection();
        } catch (Exception e) {
            logger.error("check error!", e);
        }

        if (null == conn) {
            return DATABASE_FAIL;
        }

        stmt = conn.createStatement();
        String sql = null;
        if (sourceType.equals("MYSQL")) {
            sql = "SHOW VARIABLES LIKE 'character_set_database'";
        } else if (sourceType.equals("ORACLE")) {
            // sql
            // ="select * from V$NLS_PARAMETERS where parameter in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET')";
            sql = "select * from V$NLS_PARAMETERS where parameter in('NLS_CHARACTERSET')";
        }
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            String defaultEncode = null;
            if (sourceType.equals("MYSQL")) {
                defaultEncode = ((String) rs.getObject(2)).toLowerCase();
                defaultEncode = defaultEncode.equals("iso-8859-1") ? "latin1" : defaultEncode;
                if (!encode.toLowerCase().equals(defaultEncode)) {
                    return ENCODE_FAIL + defaultEncode;
                }
            } else if (sourceType.equals("ORACLE")) {
                // ORACLE?????
                defaultEncode = ((String) rs.getObject(2)).toLowerCase();
                defaultEncode = defaultEncode.equalsIgnoreCase("zhs16gbk") ? "gbk" : defaultEncode;
                defaultEncode = defaultEncode.equalsIgnoreCase("us7ascii") ? "iso-8859-1" : defaultEncode;
                if (!encode.toLowerCase().equals(defaultEncode)) {
                    return ENCODE_FAIL + defaultEncode;
                }
            }

        }

    } catch (SQLException se) {
        logger.error("check error!", se);
        return ENCODE_QUERY_ERROR;
    } catch (Exception e) {
        logger.error("check error!", e);
        return DATABASE_FAIL;
    } finally {
        closeConnection(conn);
        dataSourceCreator.destroyDataSource(dataSource);
    }

    return DATABASE_SUCCESS;

}

From source file:com.itemanalysis.jmetrik.graph.nicc.NonparametricCurveAnalysis.java

public void evaluateDIF() throws SQLException {
    Statement stmt = null;//from w  w  w.ja  v a2  s .  c o  m
    ResultSet rs = null;

    //create focal map
    focalRegression = new TreeMap<VariableAttributes, KernelRegressionItem>();
    for (VariableAttributes v : variables) {
        KernelRegressionItem kItem = new KernelRegressionItem(v, kernelFunction, bandwidth,
                uniformDistributionApproximation);
        focalRegression.put(v, kItem);
    }

    //create reference map
    if (hasGroupVariable) {
        referenceRegression = new TreeMap<VariableAttributes, KernelRegressionItem>();
        for (VariableAttributes v : variables) {
            KernelRegressionItem kItem = new KernelRegressionItem(v, kernelFunction, bandwidth,
                    uniformDistributionApproximation);
            referenceRegression.put(v, kItem);
        }
    }

    //determine whether group variable is double or not
    boolean groupVariableIsDouble = false;
    if (groupByVariable.getType().getDataType() == DataType.DOUBLE)
        groupVariableIsDouble = true;

    try {
        //connect to db
        Table sqlTable = new Table(tableName.getNameForDatabase());
        SelectQuery select = new SelectQuery();
        for (VariableAttributes v : variables) {
            select.addColumn(sqlTable, v.getName().nameForDatabase());
        }
        select.addColumn(sqlTable, regressorVariable.getName().nameForDatabase());
        select.addColumn(sqlTable, groupByVariable.getName().nameForDatabase());

        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        rs = stmt.executeQuery(select.toString());

        KernelRegressionItem kernelRegressionItem;
        Object itemResponse;
        Double score;
        Object tempGroup;
        String group;

        //analyze by groups
        while (rs.next()) {
            tempGroup = rs.getObject(groupByVariable.getName().nameForDatabase());
            if (tempGroup == null) {
                group = "";//will not be counted if does not match focal or reference code
            } else {
                if (groupVariableIsDouble) {
                    group = Double.valueOf((Double) tempGroup).toString();
                } else {
                    group = ((String) tempGroup).trim();
                }
            }

            //get independent variable value
            //omit examinees with missing data
            //examinees with missing group code omitted
            score = rs.getDouble(regressorVariable.getName().nameForDatabase());
            if (!rs.wasNull()) {
                if (focalCode.equals(group)) {
                    for (VariableAttributes v : focalRegression.keySet()) {
                        kernelRegressionItem = focalRegression.get(v);
                        itemResponse = rs.getObject(v.getName().nameForDatabase());
                        if (itemResponse != null)
                            kernelRegressionItem.increment(score, itemResponse);
                    }
                } else if (referenceCode.equals(group)) {
                    for (VariableAttributes v : referenceRegression.keySet()) {
                        kernelRegressionItem = referenceRegression.get(v);
                        itemResponse = rs.getObject(v.getName().nameForDatabase());
                        if (itemResponse != null)
                            kernelRegressionItem.increment(score, itemResponse);
                    }
                }
            }
            updateProgress();
        }
    } catch (SQLException ex) {
        throw ex;
    } finally {
        if (rs != null)
            rs.close();
        if (stmt != null)
            stmt.close();
    }

    this.firePropertyChange("progress-ind-on", null, null);
}

From source file:kenh.xscript.database.beans.ResultSetBean.java

/**
 * Use result set to initial a bean.//from  w  w  w  .j  a  v a  2  s .c om
 * 
 * @param rs
 * @param includeFieldName
 * @throws SQLException
 * @throws IllegalAccessException
 * @throws InstantiationException
 */
public ResultSetBean(ResultSet rs, boolean includeFieldName)
        throws SQLException, IllegalAccessException, InstantiationException {
    include_field_name = includeFieldName;

    LazyDynaClass beanClass = new LazyDynaClass();

    ResultSetMetaData m = rs.getMetaData();
    for (int i = 1; i <= m.getColumnCount(); i++) {
        Column c = new Column();

        try {
            c.catalogName = m.getCatalogName(i);
        } catch (SQLException e) {
        }
        try {
            c.className = m.getColumnClassName(i);
        } catch (SQLException e) {
        }
        try {
            c.displaySize = m.getColumnDisplaySize(i);
        } catch (SQLException e) {
        }
        try {
            c.label = m.getColumnLabel(i);
        } catch (SQLException e) {
        }
        try {
            c.name = m.getColumnName(i);
        } catch (SQLException e) {
        }
        try {
            c.type = m.getColumnType(i);
        } catch (SQLException e) {
        }
        try {
            c.typeName = m.getColumnTypeName(i);
        } catch (SQLException e) {
        }
        try {
            c.precision = m.getPrecision(i);
        } catch (SQLException e) {
        }
        try {
            c.scale = m.getScale(i);
        } catch (SQLException e) {
        }
        try {
            c.schemaName = m.getSchemaName(i);
        } catch (SQLException e) {
        }
        try {
            c.tableName = m.getTableName(i);
        } catch (SQLException e) {
        }

        beanClass.add(m.getColumnLabel(i).toLowerCase());
        beanClass.add("" + i);

        cols.add(c);
    }

    DynaBean colBean = beanClass.newInstance();
    int i = 1;
    for (Column col : cols) {
        String field = col.getLabel().toLowerCase();
        colBean.set(field, col.getLabel());
        colBean.set("" + i, col.getLabel());
        i++;
    }

    if (include_field_name)
        rows.add(colBean);

    while (rs.next()) {
        DynaBean bean = beanClass.newInstance();
        i = 1;
        for (Column c : cols) {
            String field = c.getLabel().toLowerCase();
            Object obj = rs.getObject(field);
            bean.set(field, obj);
            bean.set("" + i, obj);
            i++;
        }
        rows.add(bean);
    }

}

From source file:edu.lternet.pasta.dml.parser.document.DocumentDataPackageParserTest.java

private void mergeDataPackages(DataPackage dataPackage, DataPackage metadataPackage) throws Exception {

    //build query
    Query query = new Query();
    ResultSet resultSet = null;

    /******* get the data entity ******/
    Entity dataEntity = dataPackage.getEntityList()[0];
    Attribute[] dataAttributes = dataEntity.getAttributeList().getAttributes();
    //all attributes         
    for (int j = 0; j < dataAttributes.length; j++) {
        Attribute attribute = dataAttributes[j];
        /* SELECT clause */
        SelectionItem selectionItem = new SelectionItem(dataEntity, attribute);
        query.addSelectionItem(selectionItem);
    }/*from  w  w w  .  j  a v  a  2 s . co  m*/
    /* FROM clause */
    TableItem dataTableItem = new TableItem(dataEntity);
    query.addTableItem(dataTableItem);

    /******* get the metadata entity ******/
    Entity metadataEntity = metadataPackage.getEntityList()[0];
    Attribute[] metadataAttributes = metadataEntity.getAttributeList().getAttributes();
    //all attributes         
    for (int j = 0; j < metadataAttributes.length; j++) {
        Attribute attribute = metadataAttributes[j];
        /* SELECT clause */
        SelectionItem selectionItem = new SelectionItem(metadataEntity, attribute);
        query.addSelectionItem(selectionItem);
    }
    /* FROM clause */
    TableItem metadataTableItem = new TableItem(metadataEntity);
    query.addTableItem(metadataTableItem);

    /* WHERE clause */
    Join idJoin = new Join(dataEntity, dataEntity.getAttributes()[0], metadataEntity,
            metadataEntity.getAttributes()[0]);
    WhereClause where = new WhereClause(idJoin);
    //TODO join condition! right now it's on the first column - no bueno!
    //query.setWhereClause(where);

    log.debug("Query SQL = " + query.toSQLString());

    try {
        //try to get the results
        log.debug("about to select data");

        //make a list out of the two input DPs
        DataPackage[] dataPackages = new DataPackage[2];
        dataPackages[0] = dataPackage;
        dataPackages[1] = metadataPackage;

        //get the data
        resultSet = dataManager.selectData(query, dataPackages);

        if (resultSet != null) {

            int j = 1;

            while (resultSet.next()) {
                log.debug("row " + j + ":");
                for (int col = 1; col <= resultSet.getMetaData().getColumnCount(); col++) {
                    Object column = resultSet.getObject(col);

                    log.debug("\t" + resultSet.getMetaData().getColumnName(col) + "=" + column);
                }
                j++;
            }
        } else {
            throw new Exception("resultSet is null");
        }
    } catch (Exception e) {
        log.error("Exception: " + e.getMessage());
        e.printStackTrace();
        throw (e);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

    }
}

From source file:com.funambol.json.coredb.dao.DBManager.java

public List<Map<String, String>> executeQuery(String query, DataFilter... filters) throws Exception {
    log.info("Executing query [" + query + "].");
    if (ds == null)
        throw new Exception("Data source is null.");

    Connection connection = null;
    Statement stmt = null;//from w  w  w .java  2  s .  co m
    ResultSet rsltSet = null;
    ResultSetMetaData metadata = null;

    try {
        connection = ds.getConnection();
    } catch (SQLException ex) {
        release(connection, stmt, rsltSet);
        throw new Exception("An error occurred retrieving connection.", ex);
    }

    if (connection == null) {
        throw new Exception("Connection is null.");
    }

    try {
        stmt = connection.createStatement();
    } catch (SQLException ex) {
        release(connection, stmt, rsltSet);
        throw new Exception("An error occurred creating statement.", ex);
    }

    try {
        rsltSet = stmt.executeQuery(query);
        metadata = rsltSet.getMetaData();
    } catch (SQLException ex) {
        release(connection, stmt, rsltSet);
        throw new Exception("An error occurred executing query [" + query + "].", ex);
    }

    try {
        if (rsltSet != null) {

            Map<String, DataFilter> columnNamesFilters = new HashMap<String, DataFilter>();
            Map<String, DataFilter> columnClassFilters = new HashMap<String, DataFilter>();
            populateFilterMap(columnNamesFilters, columnClassFilters, filters);

            List<Map<String, String>> result = new ArrayList<Map<String, String>>();
            int numberOfColumns = metadata.getColumnCount();
            while (rsltSet.next()) {
                Map<String, String> newItem = new HashMap<String, String>();
                for (int i = 1; i <= numberOfColumns; i++) {
                    String columnName = metadata.getColumnName(i);
                    String columnValue = null;
                    String columnClass = metadata.getColumnClassName(i);
                    // Retrieving filter bound to column class or column name
                    DataFilter filter = null;
                    if (!columnNamesFilters.isEmpty() && columnNamesFilters.containsKey(columnName)) {
                        filter = columnNamesFilters.get(columnName);
                    } else if (!columnClassFilters.isEmpty() && columnClassFilters.containsKey(columnClass)) {
                        filter = columnClassFilters.get(columnClass);
                    }

                    if (filter != null) {
                        Object obj = rsltSet.getObject(i);
                        columnValue = filter.applyFilter(obj);
                    } else
                        columnValue = rsltSet.getString(i);

                    newItem.put(columnName, columnValue);
                }
                result.add(newItem);
            }
            return result;

        } else
            throw new Exception("ResultSet is null.");
    } catch (SQLException ex) {
        throw new Exception("An error occurred creating result list for query [" + query + "].", ex);
    } finally {
        release(connection, stmt, rsltSet);
    }

}