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:net.sf.jdmf.data.sources.jdbc.JDBCDataSource.java

/**
 * @see net.sf.jdmf.data.sources.DataSource#getAttributes()
 *///from  w  w w.j  a  v  a 2s  .c  om
public Map<String, List<Comparable>> getAttributes() {
    Map<String, List<Comparable>> attributes = new LinkedHashMap<String, List<Comparable>>();

    try {
        Connection connection = DriverManager.getConnection(connectionString, userName, password);

        Statement statement = connection.createStatement();

        for (String query : queries) {
            ResultSet resultSet = statement.executeQuery(query);

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            for (int i = 1; i <= columnCount; ++i) {
                String attributeName = metaData.getColumnName(i);
                List<Comparable> attributeValues = new ArrayList<Comparable>();

                attributes.put(attributeName, attributeValues);
            }

            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; ++i) {
                    List<Comparable> attributeValues = attributes.get(metaData.getColumnName(i));

                    attributeValues.add(getValueAsComparable(resultSet.getObject(i)));
                }
            }

            resultSet.close();
        }

        statement.close();
        connection.close();
    } catch (SQLException e) {
        throw new DataSourceException("Could not retrieve data", e);
    }

    return attributes;
}

From source file:com.mongosqlmigrator.harsha.sql.DocBuilder.java

private void getMultiValuedEntity(ResultSet rs, Entity entity, Map<String, Object> rootEntityMap)
        throws SQLException {
    List<Object> fieldArray = new ArrayList<Object>();
    rs.beforeFirst();/*ww  w .  ja  v a 2 s  . c  om*/
    while (rs.next()) {
        if (entity.fields.size() > 1) {
            Map<String, Object> entityFieldsMap = new HashMap<String, Object>();
            for (Iterator<Field> iterator = entity.fields.iterator(); iterator.hasNext();) {
                Field field = iterator.next();
                FieldType fieldType = FieldType.valueOf(field.allAttributes.get("type").toUpperCase());
                entityFieldsMap.put(field.name, convertFieldType(fieldType, rs.getObject(field.column)).get(0));
            }
            fieldArray.add(entityFieldsMap);
        } else if (entity.fields.size() == 1) {
            fieldArray.add(rs.getObject(entity.fields.get(0).column));
        }
    }
    rootEntityMap.put(entity.name, fieldArray);
}

From source file:com.ws.WS_TCS201.java

@Path("/GetTCD")
@JSONP(queryParam = "callback")
@GET//from   ww w .jav a 2  s. co m
@Produces({ "application/x-javascript" })
public String GetTCD(@QueryParam("callback") String callback) {

    JSONObject obj1 = new JSONObject();
    LinkedList l1 = new LinkedList();
    //JSONArray l1 = new JSONArray();

    PreparedStatement prepStmt = null;

    try {
        String cSQL = "SELECT tcctcd,CONCAT(tcctcd,\" - \",trim(tcctxt)) AS name FROM TCSTCC "
                + "WHERE tcctcd NOT IN (\"A\",\"L\",\"N\",\"J\",\"R\",\"E\") " + "ORDER BY tcctcd ";
        prepStmt = connection.prepareStatement(cSQL);
        ResultSet result = prepStmt.executeQuery();
        ResultSetMetaData rsmd = result.getMetaData();
        int numcols = rsmd.getColumnCount();

        while (result.next()) {
            LinkedHashMap m1 = new LinkedHashMap();
            for (int j = 1; j <= numcols; j++) {
                Object obj = result.getObject(j);
                m1.put(rsmd.getColumnName(j).toString(), obj.toString());
            }
            l1.add(m1);
        }
        obj1.put("record", l1);
    } catch (SQLException e) {
        prepStmt = null;
        e.printStackTrace();
    } catch (Exception e) {
        prepStmt = null;
        e.printStackTrace();
    }
    return obj1.toString();
}

From source file:fr.gael.dhus.database.liquibase.CopyProductImagesBlobToFile.java

/**
 * This method executes://w  w w . jav  a 2s . c o m
 *  - extraction of quicklooks and thumbnails from the database to files,
 *  - references these files into the data base.
 *  
 *  remove/update processes are let to liquibase scripts.
 */
@Override
public void execute(Database database) throws CustomChangeException {
    PreparedStatement products = null;
    ResultSet products_res = null;
    JdbcConnection db_connection = (JdbcConnection) database.getConnection();
    try {
        products = db_connection.prepareStatement("SELECT PRODUCT.ID ID,"
                + "       PRODUCT.DOWNLOAD_PATH DWN_PATH, " + "       PRODUCT.PATH PRODUCT_PATH,"
                + "       IMAGE.QUICKLOOK QUICKLOOK," + "       IMAGE.THUMBNAIL THUMBNAIL "
                + "FROM PRODUCTS PRODUCT, PRODUCT_IMAGES IMAGE " + "WHERE PRODUCT.IMAGES_ID=IMAGE.ID");
        products_res = products.executeQuery();
        while (products_res.next()) {
            Blob ql = (Blob) products_res.getObject("QUICKLOOK");
            Blob th = (Blob) products_res.getObject("THUMBNAIL");
            long id = products_res.getLong("ID");
            String download_path = products_res.getString("DWN_PATH");
            String product_path = products_res.getString("PRODUCT_PATH");

            if (download_path == null) {
                logger.error("No download path for product '" + product_path + "': product images not managed");
                continue;
            }

            // copy blobs into files and update products table
            if (ql != null) {
                // Copy file
                String ql_path = download_path.replaceAll("(?i)(.*).zip", "$1-ql.gif");
                blobToFile(ql, ql_path);

                // Update products table
                PreparedStatement product_flags_stmt = null;
                // Add related flags
                try {
                    product_flags_stmt = db_connection
                            .prepareStatement("UPDATE PRODUCTS SET QUICKLOOK_PATH=? WHERE ID=?");
                    product_flags_stmt.setString(1, ql_path);
                    product_flags_stmt.setLong(2, id);
                    product_flags_stmt.execute();
                } finally {
                    if (product_flags_stmt != null)
                        try {
                            product_flags_stmt.close();
                        } catch (Exception e) {
                            logger.warn("Cannot close Statement !");
                        }
                }
            }

            if (th != null) {
                String th_path = download_path.replaceAll("(?i)(.*).zip", "$1-th.gif");
                blobToFile(th, th_path);
                // Update products table
                PreparedStatement product_flags_stmt = null;
                // Add related flags
                try {
                    product_flags_stmt = db_connection
                            .prepareStatement("UPDATE PRODUCTS SET THUMBNAIL_PATH=? WHERE ID=?");
                    product_flags_stmt.setString(1, th_path);
                    product_flags_stmt.setLong(2, id);
                    product_flags_stmt.execute();
                } finally {
                    if (product_flags_stmt != null)
                        try {
                            product_flags_stmt.close();
                        } catch (Exception e) {
                            logger.warn("Cannot close Statement !");
                        }
                }
            }
        }
        // RUN CHECKPOINT TO clean lob data
        PreparedStatement product_flags_stmt = null;
        try {
            product_flags_stmt = db_connection.prepareStatement("CHECKPOINT DEFRAG");
            product_flags_stmt.execute();
        } catch (Exception e) {
            logger.error("Cannot perform database checkpoint defrag command", e);
        } finally {
            if (product_flags_stmt != null)
                try {
                    product_flags_stmt.close();
                } catch (Exception e) {
                    logger.warn("Cannot close Statement !", e);
                }
        }
    } catch (Exception e) {
        throw new CustomChangeException("Cannot move Blobs from product", e);
    } finally {
        if (products_res != null) {
            try {
                products_res.close();
            } catch (Exception e) {
                logger.warn("Cannot close ResultSet !");
            }
        }
        if (products != null) {
            try {
                products.close();
            } catch (Exception e) {
                logger.warn("Cannot close Statement !");
            }
        }
        //if (db_connection!=null) try { db_connection.close (); }
        // catch (Exception e) {}
    }
}

From source file:com.ws.WS_TCS201.java

@Path("/GetID/{com}")
@JSONP(queryParam = "callback")
@GET/*from   w w  w . ja v a  2  s  .c  o  m*/
@Produces({ "application/x-javascript" })
public String GetID(@QueryParam("callback") String callback, @PathParam("com") String com) {
    //JOptionPane.showMessageDialog(null, "??", "Which way?", JOptionPane.INFORMATION_MESSAGE );
    JSONObject obj1 = new JSONObject();
    LinkedList l1 = new LinkedList();
    //JSONArray l1 = new JSONArray();

    PreparedStatement prepStmt = null;

    DateFormat day = new SimpleDateFormat("yyyyMMdd");
    String tmpday = day.format(new java.util.Date());

    try {
        String cSQL = "SELECT tceemp,tcenam FROM TCSTCE "
                + "WHERE tcecom = ? AND ( tceljd=0 OR tceljd + 100 > \"" + tmpday + "\" ) "
                + "ORDER BY tceemp,tcecom ";
        prepStmt = connection.prepareStatement(cSQL);
        prepStmt.setString(1, com);
        ResultSet result = prepStmt.executeQuery();
        ResultSetMetaData rsmd = result.getMetaData();
        int numcols = rsmd.getColumnCount();

        while (result.next()) {
            LinkedHashMap m1 = new LinkedHashMap();
            for (int j = 1; j <= numcols; j++) {
                Object obj = result.getObject(j);
                m1.put(rsmd.getColumnName(j).toString(), obj.toString());
            }
            l1.add(m1);
        }
        obj1.put("record", l1);
    } catch (SQLException e) {
        prepStmt = null;
        e.printStackTrace();
    } catch (Exception e) {
        prepStmt = null;
        e.printStackTrace();
    }
    return obj1.toString();
}

From source file:de.codecentric.multitool.db.DBUnitLibrary.java

/**
 * Liest einen Einzelwert aus einer Tabellenabfrage
 * /* ww  w.j  a v  a 2  s .  c  o m*/
 * Beispiel: | ${value} = | Read Single Value From Table | ACSD_FULL |
 * MYTABLE | STATUS | id = 5 |
 */
public String readSingleValueFromTable(String dsName, String table, String column, String whereStatment)
        throws IllegalArgumentException, SQLException {
    Statement statement = null;
    ResultSet rs = null;
    try {
        statement = getConnection(dsName).createStatement();
        String sql = "SELECT " + column + " FROM " + table
                + (StringUtils.isNotEmpty(whereStatment) ? " WHERE " + whereStatment : "");
        statement.execute(sql);

        rs = statement.getResultSet();
        if (rs.next()) {
            if (rs.getObject(1) == null)
                return null;
            else
                return rs.getObject(1).toString();
        }
        throw new NoSuchElementException(sql);

    } finally {
        if (rs != null) {
            rs.close();
        }
        if (statement != null) {
            statement.close();
        }
    }
}

From source file:com.quangphuong.crawler.dbutil.DBWrapper.java

public List<Object> searchFullText(Object entity, String searchVal) {
    String sql = "";
    Connection con = null;//  w ww .  ja v a  2 s  . c  o m
    if (this.isDisconnect) {
        con = DBHandler.openConnection();
    }
    List<Object> result = new ArrayList<Object>();
    try {
        Statement statement;
        if (this.isDisconnect) {
            statement = con.createStatement();
        } else {
            statement = connection.createStatement();
        }
        Field[] attributes = entity.getClass().getDeclaredFields();

        int count = 0;
        String fullTextFields = "";
        String searchClause = "";
        for (Field attribute : attributes) {
            attribute.setAccessible(true);
            if (!attribute.isAnnotationPresent(AutoIncrement.class)
                    && attribute.isAnnotationPresent(FullTextIndex.class)) {
                if (count == 0) {
                    fullTextFields = fullTextFields.concat(attribute.getName());
                } else {
                    fullTextFields = fullTextFields.concat("," + attribute.getName());
                }
                count++;
            }
        }
        sql = fulltextClause.replace("@", fullTextFields);
        if (lowerBound != 0 || upperBound != 0) {
            searchClause = searchRangeByBoolClause.replace("@1",
                    searchVal.replace("*", "").replace(" ", "* ").concat("*"));
            searchClause = searchClause.replace("@L1", String.valueOf(lowerBound)).replace("@L2",
                    String.valueOf(upperBound));
        } else {
            searchClause = searchByBoolClause.replace("@1",
                    searchVal.replace("*", "").replace(" ", "* ").concat("*"));
        }
        searchClause = searchClause.replace("@2", entity.getClass().getSimpleName());
        sql = sql.concat(searchClause);
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            List<Object> listFields = new ArrayList();
            List<Class<?>> listFieldTypes = new ArrayList();
            for (Field attribute : attributes) {
                Object obj = resultSet.getObject(attribute.getName());
                listFields.add(obj);
                listFieldTypes.add(attribute.getType());
            }
            Object obj = entity.getClass().getConstructor((Class<?>[]) listFieldTypes.toArray(new Class[0]))
                    .newInstance(listFields.toArray());
            result.add(obj);
        }
    } catch (Exception ex) {
        Logger.getLogger(DBWrapper.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            if (this.isDisconnect && con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return result;
}

From source file:com.quangphuong.crawler.dbutil.DBWrapper.java

public List<Object> suggest(Object entity, String searchVal) {
    String sql = "";
    Connection con = null;//from   ww w.j a  v  a2 s  .c om
    if (this.isDisconnect) {
        con = DBHandler.openConnection();
    }
    List<Object> result = new ArrayList<Object>();
    try {
        Statement statement;
        if (this.isDisconnect) {
            statement = con.createStatement();
        } else {
            statement = connection.createStatement();
        }
        Field[] attributes = entity.getClass().getDeclaredFields();

        int count = 0;
        String fullTextFields = "";
        String searchClause = "";
        for (Field attribute : attributes) {
            attribute.setAccessible(true);
            if (!attribute.isAnnotationPresent(AutoIncrement.class)
                    && attribute.isAnnotationPresent(FullTextIndex.class)) {
                if (count == 0) {
                    fullTextFields = fullTextFields.concat(attribute.getName());
                } else {
                    fullTextFields = fullTextFields.concat("," + attribute.getName());
                }
                count++;
            }
        }
        sql = fulltextClause.replace("@", fullTextFields);
        if (lowerBound != 0 || upperBound != 0) {
            searchClause = searchRangeByBoolClause.replace("@1",
                    searchVal.replace("*", "").replace(" ", "* ").concat("*"));
            searchClause = searchClause.replace("@L1", String.valueOf(lowerBound)).replace("@L2",
                    String.valueOf(upperBound));
        } else {
            searchClause = suggestClause.replace("@1",
                    searchVal.replace("*", "").replace(" ", "* ").concat("*"));
        }
        searchClause = searchClause.replace("@2", entity.getClass().getSimpleName());
        sql = sql.concat(searchClause);
        System.out.println(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            List<Object> listFields = new ArrayList();
            List<Class<?>> listFieldTypes = new ArrayList();
            for (Field attribute : attributes) {
                Object obj = resultSet.getObject(attribute.getName());
                listFields.add(obj);
                listFieldTypes.add(attribute.getType());
            }
            Object obj = entity.getClass().getConstructor((Class<?>[]) listFieldTypes.toArray(new Class[0]))
                    .newInstance(listFields.toArray());
            result.add(obj);
        }
    } catch (Exception ex) {
        Logger.getLogger(DBWrapper.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            if (this.isDisconnect && con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return result;
}

From source file:com.geodetix.geo.dao.PostGisGeometryDAOImpl.java

/**
 * PostGIS implementation of the entity bean's life cycle method 
 * <code>ejbLoad()</code>.//from   ww  w  .ja  va 2 s . c  om
 * 
 * @param pk the primary key of the bean to load.
 * @param ejb the ejb whose data must be loaded.
 * @throws javax.ejb.EJBException launched if a generic EJB error is encountered.
 */
public void load(java.lang.Integer pk, com.geodetix.geo.ejb.GeometryBean ejb) throws javax.ejb.EJBException {

    PreparedStatement pstm = null;
    Connection con = null;
    ResultSet result = null;

    try {

        con = this.dataSource.getConnection();

        pstm = con.prepareStatement(PostGisGeometryDAO.EJB_LOAD_STATEMENT);

        pstm.setInt(1, pk.intValue());

        result = pstm.executeQuery();

        if (result.next()) {
            ejb.setId(pk);
            ejb.setGeometry(((PGgeometry) result.getObject("geometry")).getGeometry());
            ejb.setDescription((String) result.getString("description"));

        } else {

            throw new EJBException("ejbLoad unable to load EJB.");
        }

    } catch (SQLException se) {
        throw new EJBException(se);

    } finally {
        try {
            if (result != null) {
                result.close();
            }
        } catch (Exception e) {
        }

        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (Exception e) {
        }

        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception e) {
        }
    }
}

From source file:edu.ku.brc.specify.toycode.mexconabio.DataObjTableModel.java

/**
 * The Data members must be set to call this:
 *     numColumns// ww  w.ja  va  2 s  . co  m
 *     itemsList
 * 
 */
protected void fillModels() {
    final String sqlStr = buildSQL();

    TimeLogger tml = new TimeLogger("Fetching Rows");
    values = new Vector<Object[]>();

    if (StringUtils.isNotEmpty(sqlStr)) {
        log.debug(sqlStr);
        try {
            PreparedStatement pStmt = conn.prepareStatement(sqlStr);
            if (searchValue != null) {
                pStmt.setObject(1, searchValue);
            }
            log.debug(sqlStr + " [" + searchValue + "]");

            tml.restart("Query");
            ResultSet rs = pStmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            tml.restart("Loading");
            while (rs.next()) {
                Object[] row = new Object[numColumns];
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    Object obj = rs.getObject(i + 1);
                    row[i] = obj instanceof String ? obj.toString().trim() : obj;
                }
                rowInfoList.add(new DataObjTableModelRowInfo(rs.getInt(1), false, false));
                values.add(row);
                processColumns(row);
            }
            rs.close();
            pStmt.close();
            tml.end();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    tml.restart("Step 2 - Addl Rows");
    addAdditionalRows(colDefItems, rowInfoList);

    tml.restart("Step 3");

    sameValues = new ArrayList<Boolean>(numColumns);
    hasDataList = new ArrayList<Boolean>(numColumns);
    for (int i = 0; i < numColumns; i++) {
        sameValues.add(true);
        hasDataList.add(false);
    }

    for (Object[] col : values) {
        for (int i = 0; i < numColumns; i++) {
            Object data = col[i];
            boolean hasData = data != null;

            if (hasData && !hasDataList.get(i)) {
                hasDataList.set(i, true);
                hasDataCols++;
            }
        }
    }

    tml.restart("Step  4 - adj cols");
    adjustHasDataColumns();

    tml.restart("Step  5 - Map");
    mapInx = new int[hasDataCols];
    int colInx = 0;
    //log.debug("-------------Has Data----------------------");
    for (int i = 0; i < numColumns; i++) {
        if (hasDataList.get(i)) {
            //log.debug(itemsList.get(i).getTitle());
            mapInx[colInx] = i;
            indexHash.put(i, colInx);
            //System.out.print("indexHash: "+i +" -> "+colInx);
            //log.debug("  mapInx:    "+colInx +" -> "+i);
            colInx++;
        }
    }

    tml.restart("Step  6 - same data");
    for (int i = 0; i < mapInx.length; i++) {
        colInx = mapInx[i];

        if (hasDataList.get(colInx)) {
            Object data = null;
            for (Object[] col : values) {
                Object newData = col[colInx];

                if (data == null) {
                    if (newData != null) {
                        data = newData;
                    }
                    continue;
                }

                if (newData != null && !data.equals(newData)) {
                    sameValues.set(colInx, false);
                    break;
                }
            }
        }
    }
    tml.end();
    /*
    log.debug("-----------Same------------------------");
    for (int i=0;i<mapInx.length;i++)
    {
        colInx = mapInx[i];
        if (sameValues.get(colInx))
        {
            log.debug(colInx + " " + itemsList.get(colInx).getTitle());
        }
    }*/

    items = new ArrayList<DBInfoBase>(colDefItems);
    doneFillingModels(values);
}