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.cloudera.recordbreaker.analyzer.DataQuery.java

public List<List<Object>> query(DataDescriptor desc1, DataDescriptor desc2, String projectionClause,
        String selectionClause) throws SQLException, IOException {
    String tablename1 = grabTable(desc1);
    String tablename2 = null;//from   w w  w . ja va 2 s.  c o  m
    if (desc2 != null) {
        tablename2 = grabTable(desc2);
    }

    //
    // Build the SQL query against the table
    //
    if (projectionClause == null || projectionClause.trim().length() == 0) {
        projectionClause = "*";
    }
    if (selectionClause == null) {
        selectionClause = "";
    }
    if (tablename2 == null) {
        projectionClause = projectionClause.replaceAll("DATA", tablename1);
        selectionClause = selectionClause.replaceAll("DATA", tablename1);
    }
    projectionClause = projectionClause.trim();
    selectionClause = selectionClause.trim();
    String query;
    if (tablename2 == null) {
        query = "SELECT " + projectionClause + " FROM " + tablename1;
    } else {
        query = "SELECT " + projectionClause + " FROM " + tablename1 + " DATA1" + ", " + tablename2 + " DATA2";
    }

    if (selectionClause.length() > 0) {
        query = query + " WHERE " + selectionClause;
    }

    //
    // Try to run it first with the impala connection.
    // If that fails, try hive.
    //
    List<List<Object>> result = new ArrayList<List<Object>>();
    Statement stmt = impalaCon.createStatement();
    LOG.info("Processing: " + query);
    try {
        ResultSet res = null;
        try {
            res = stmt.executeQuery(query);
            LOG.info("Ran Impala query: " + query);
        } catch (Exception iex) {
            iex.printStackTrace();
            // Fail back to Hive!
            stmt.close();
            stmt = hiveCon.createStatement();
            res = stmt.executeQuery(query);
            LOG.info("Ran Hive query: " + query);
        }

        // OK now do the real work
        ResultSetMetaData rsmd = res.getMetaData();
        List<Object> metatuple = new ArrayList<Object>();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
            metatuple.add(rsmd.getColumnLabel(i));
        }
        result.add(metatuple);

        while (res.next()) {
            List<Object> tuple = new ArrayList<Object>();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                tuple.add(res.getObject(i));
            }
            result.add(tuple);
        }
        return result;
    } finally {
        stmt.close();
    }
}

From source file:org.mayocat.accounts.store.jdbi.mapper.TenantMapper.java

@Override
public Tenant map(int index, ResultSet result, StatementContext statementContext) throws SQLException {
    String slug = result.getString("slug");
    String defaultHost = result.getString("default_host");
    ObjectMapper mapper = new ObjectMapper();
    mapper.registerModule(new GuavaModule());
    Integer configurationVersion = result.getInt("configuration_version");
    TenantConfiguration configuration;/*from   w ww .  j a v a  2  s. c  o  m*/
    if (Strings.isNullOrEmpty(result.getString("configuration"))) {
        configuration = new TenantConfiguration(configurationVersion,
                Collections.<String, Serializable>emptyMap());
    } else {
        try {
            Map<String, Serializable> data = mapper.readValue(result.getString("configuration"),
                    new TypeReference<Map<String, Object>>() {
                    });
            configuration = new TenantConfiguration(configurationVersion, data);
        } catch (IOException e) {
            final Logger logger = LoggerFactory.getLogger(TenantMapper.class);
            logger.error("Failed to load configuration for tenant with slug [{}]", e);
            configuration = new TenantConfiguration();
        }
    }

    Tenant tenant = new Tenant((UUID) result.getObject("id"), slug, configuration);
    tenant.setFeaturedImageId((UUID) result.getObject("featured_image_id"));
    tenant.setSlug(slug);
    tenant.setDefaultHost(defaultHost);
    tenant.setCreationDate(result.getTimestamp("creation_date"));
    tenant.setName(result.getString("name"));
    tenant.setDescription(result.getString("description"));
    tenant.setContactEmail(result.getString("contact_email"));

    return tenant;
}

From source file:com.haulmont.yarg.loaders.impl.SqlDataLoader.java

@Override
public List<Map<String, Object>> loadData(ReportQuery reportQuery, BandData parentBand,
        Map<String, Object> params) {
    List resList;//w ww.  j a  v a2s. co  m
    final List<OutputValue> outputValues = new ArrayList<OutputValue>();

    String query = reportQuery.getScript();
    if (StringUtils.isBlank(query)) {
        return Collections.emptyList();
    }

    try {
        if (Boolean.TRUE.equals(reportQuery.getProcessTemplate())) {
            query = processQueryTemplate(query, parentBand, params);
        }
        final QueryPack pack = prepareQuery(query, parentBand, params);

        ArrayList<Object> resultingParams = new ArrayList<Object>();
        QueryParameter[] queryParameters = pack.getParams();
        for (QueryParameter queryParameter : queryParameters) {
            if (queryParameter.isSingleValue()) {
                resultingParams.add(queryParameter.getValue());
            } else {
                resultingParams.addAll(queryParameter.getMultipleValues());
            }
        }

        resList = runQuery(reportQuery, pack.getQuery(), resultingParams.toArray(),
                new ResultSetHandler<List>() {
                    @Override
                    public List handle(ResultSet rs) throws SQLException {
                        List<Object[]> resList = new ArrayList<Object[]>();

                        while (rs.next()) {
                            ResultSetMetaData metaData = rs.getMetaData();
                            if (outputValues.size() == 0) {
                                for (int columnIndex = 1; columnIndex <= metaData
                                        .getColumnCount(); columnIndex++) {
                                    String columnName = metaData.getColumnLabel(columnIndex);
                                    OutputValue outputValue = new OutputValue(columnName);
                                    setCaseSensitiveSynonym(columnName, outputValue);
                                    outputValues.add(outputValue);
                                }
                            }

                            Object[] values = new Object[metaData.getColumnCount()];
                            for (int columnIndex = 0; columnIndex < metaData.getColumnCount(); columnIndex++) {
                                values[columnIndex] = convertOutputValue(rs.getObject(columnIndex + 1));
                            }
                            resList.add(values);
                        }

                        return resList;
                    }

                    private void setCaseSensitiveSynonym(String columnName, OutputValue outputValue) {
                        Matcher matcher = Pattern.compile("(?i)as\\s*(" + columnName + ")")
                                .matcher(pack.getQuery());
                        if (matcher.find()) {
                            outputValue.setSynonym(matcher.group(1));
                        }
                    }
                });
    } catch (DataLoadingException e) {
        throw e;
    } catch (Throwable e) {
        throw new DataLoadingException(
                String.format("An error occurred while loading data for data set [%s]", reportQuery.getName()),
                e);
    }

    return fillOutputData(resList, outputValues);
}

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

/**
 * /*from w w w . j a  va  2 s  .  c  om*/
 */
public void process() {
    boolean doQueryForCollNum = true;

    String pSQL = "INSERT INTO raw (old_id,data_provider_id,data_resource_id,resource_access_point_id, institution_code, collection_code, "
            + "catalogue_number, scientific_name, author, rank, kingdom, phylum, class, order_rank, family, genus, species, subspecies, latitude, longitude,  "
            + "lat_long_precision, max_altitude, min_altitude, altitude_precision, min_depth, max_depth, depth_precision, continent_ocean, country, state_province, county, collector_name, "
            + "locality,year, month, day, basis_of_record, identifier_name, identification_date,unit_qualifier, created, modified, deleted, collector_num) "
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    String gbifSQLBase = "SELECT r.id, r.data_provider_id, r.data_resource_id, r.resource_access_point_id, r.institution_code, r.collection_code, "
            + "r.catalogue_number, r.scientific_name, r.author, r.rank, r.kingdom, r.phylum, r.class, r.order_rank, r.family, r.genus, r.species, r.subspecies, "
            + "r.latitude, r.longitude, r.lat_long_precision, r.max_altitude, r.min_altitude, r.altitude_precision, r.min_depth, r.max_depth, r.depth_precision, "
            + "r.continent_ocean, r.country, r.state_province, r.county, r.collector_name, r.locality, r.year, r.month, r.day, r.basis_of_record, r.identifier_name, "
            + "r.identification_date, r.unit_qualifier, r.created, r.modified, r.deleted";

    String gbifSQL;
    if (doQueryForCollNum) {
        gbifSQL = gbifSQLBase + " FROM raw_occurrence_record r";
    } else {
        gbifSQL = gbifSQLBase
                + ", i.identifier FROM raw_occurrence_record r, identifier_record i WHERE r.id = i.occurrence_id AND i.identifier_type = 3";
    }

    BasicSQLUtils.update(srcDBConn, "DELETE FROM raw WHERE id > 0");

    long totalRecs = BasicSQLUtils.getCount(dbConn, "SELECT COUNT(*) FROM raw_occurrence_record");
    long procRecs = 0;
    long startTime = System.currentTimeMillis();
    int secsThreshold = 0;

    PrintWriter pw = null;

    final double HRS = 1000.0 * 60.0 * 60.0;

    Statement gStmt = null;
    PreparedStatement pStmt = null;
    PreparedStatement stmt = null;

    try {
        pw = new PrintWriter("gbif.log");

        pStmt = srcDBConn.prepareStatement(pSQL);

        stmt = dbConn2.prepareStatement(
                "SELECT identifier FROM identifier_record WHERE occurrence_id = ? AND identifier_type = 3");
        //stmt.setFetchSize(Integer.MIN_VALUE);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = dbConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        String fullSQL = gbifSQL;
        System.out.println(fullSQL);

        ResultSet gRS = gStmt.executeQuery(fullSQL);
        ResultSetMetaData rsmd = gRS.getMetaData();
        int lastColInx = rsmd.getColumnCount() + (doQueryForCollNum ? 1 : 0);

        while (gRS.next()) {
            int id = gRS.getInt(1);
            pStmt.setObject(1, id);

            for (int i = 2; i <= rsmd.getColumnCount(); i++) {
                Object obj = gRS.getObject(i);
                pStmt.setObject(i, obj);
            }

            String collNum = null;
            if (doQueryForCollNum) {
                //String tmpSQL = String.format("SELECT identifier FROM identifier_record WHERE occurrence_id = %d AND identifier_type = 3", id);
                //System.out.println(tmpSQL);
                stmt.setInt(1, id);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    collNum = rs.getString(1);
                }
                rs.close();
            } else {
                collNum = gRS.getString(lastColInx - 1);
            }

            if (StringUtils.isNotEmpty(collNum)) {
                if (collNum.length() < 256) {
                    pStmt.setString(lastColInx, collNum);

                } else {
                    pStmt.setString(lastColInx, collNum.substring(0, 255));
                }
            } else {
                pStmt.setObject(lastColInx, null);
            }

            try {
                pStmt.executeUpdate();

            } catch (Exception ex) {
                System.err.println("For ID[" + gRS.getObject(1) + "]");
                ex.printStackTrace();
                pw.print("For ID[" + gRS.getObject(1) + "] " + ex.getMessage());
                pw.flush();
            }

            procRecs++;
            if (procRecs % 10000 == 0) {
                long endTime = System.currentTimeMillis();
                long elapsedTime = endTime - startTime;

                double avergeTime = (double) elapsedTime / (double) procRecs;

                double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs - procRecs)
                        / HRS;

                int seconds = (int) (elapsedTime / 60000.0);
                if (secsThreshold != seconds) {
                    secsThreshold = seconds;

                    String msg = String.format(
                            "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                            ((double) (elapsedTime)) / HRS, avergeTime,
                            100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                    System.out.println(msg);
                    pw.println(msg);
                    pw.flush();
                }
            }
        }

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

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");

    /*
    int     count = 0;
    boolean cont = true;
    while (cont)
    {
    long start = System.currentTimeMillis();
            
    Statement         gStmt = null;
    PreparedStatement pStmt = null;
            
    try
    {
        gStmt = dbConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
                
        String fullSQL = gbifSQL + String.format(" AND r.id >= %d AND r.id < %d", count, count+recordStep);
        //System.out.println(fullSQL);
                
        int               cnt  = 0;
        ResultSet         rs   = gStmt.executeQuery(fullSQL);
        ResultSetMetaData rsmd = rs.getMetaData();
                
        //System.out.println("Done with query.");
                
        pStmt = srcDBConn.prepareStatement(pSQL);
        count += recordStep;
                
        while (rs.next())
        {
            Integer id  = rs.getInt(1);
            pStmt.setInt(1, id);
                    
            for (int i=2;i<=rsmd.getColumnCount();i++)
            {
                Object obj = rs.getObject(i);
                pStmt.setObject(i, obj);
            }
                    
            pStmt.executeUpdate();
                
            cnt++;
            procRecs++;
        }
        rs.close();
                
        if (count == 0)
        {
            break;
        }
                
    } catch (Exception ex)
    {
        ex.printStackTrace();
                
    } finally 
    {
        try
        {
            if (gStmt != null)
            {
                gStmt.close();
            }
            if (pStmt != null)
            {
                pStmt.close();
            }
        } catch (Exception ex)
        {
                    
        }
    }
            
    long endTime   = System.currentTimeMillis();
    long deltaTime = endTime - start;
               
    long elapsedTime = endTime - startTime;
            
    double avergeTime = (double)elapsedTime / (double)procRecs;
            
    double hrsLeft = (((double)procRecs / (double)elapsedTime) * (double)totalRecs)  / 3600000.0;
            
    int seconds = (int)(elapsedTime / 60000.0);
    if (secsThreshold != seconds)
    {
        secsThreshold = seconds;
                
        System.out.println(String.format("Elapsed %8.2f hr.mn   Time: %5.2f  Ave Time: %5.2f    Percent: %6.3f  Hours Left: Elapsed %8.2f ", 
                ((double)(elapsedTime)) / 3600000.0, 
                ((double)(deltaTime)) / 1000.0, 
                avergeTime,
                100.0 * ((double)procRecs / (double)totalRecs),
                hrsLeft));
    }
    }
    System.out.println("Done transferring.");*/

    /*Statement uStmt = null;
    try
    {
    uStmt = srcDBConn.createStatement();
    int rv = uStmt.executeUpdate("ALTER TABLE raw ADD FULLTEXT(catalogue_number, genus, species, subspecies, collector_num)");
            
    System.out.println("Indexing rv = "+rv);
            
    } catch (Exception ex)
    {
    ex.printStackTrace();
            
    } finally 
    {
    try
    {
        if (uStmt != null)
        {
            uStmt.close();
        }
    } catch (Exception ex)
    {
        ex.printStackTrace();
    }
    }
    System.out.println("Done Indexing.");*/
}

From source file:mayoapp.migrations.V0075_0003__update_tenant_configurations.java

@Override
public void migrate(Connection connection) throws Exception {
    connection.setAutoCommit(false);//from www  . java 2  s . c o m

    Statement queryIdsStatement = connection.createStatement();
    ResultSet tenants = queryIdsStatement.executeQuery("SELECT entity_id, slug, configuration FROM entity "
            + "INNER JOIN tenant ON entity.id = tenant.entity_id");

    Map<UUID, ConfigurationAndName> tenantsData = Maps.newHashMap();

    while (tenants.next()) {
        String json = tenants.getString("configuration");
        String name = tenants.getString("slug");
        ObjectMapper objectMapper = new ObjectMapper();
        Map<String, Object> configuration = objectMapper.readValue(json,
                new TypeReference<Map<String, Object>>() {
                });
        if (configuration.containsKey("general")) {
            Map<String, Object> generalConfiguration = (Map<String, Object>) configuration.get("general");
            if (generalConfiguration.containsKey("name")) {
                name = (String) generalConfiguration.get("name");
                ((Map<String, Object>) configuration.get("general")).remove("name");
                json = objectMapper.writeValueAsString(configuration);
            }

        }
        ConfigurationAndName configurationAndName = new ConfigurationAndName(json, name);
        tenantsData.put((UUID) tenants.getObject("entity_id"), configurationAndName);
    }

    queryIdsStatement.close();

    PreparedStatement statement = connection
            .prepareStatement("UPDATE tenant SET name=?, configuration=? WHERE entity_id =?");

    for (UUID id : tenantsData.keySet()) {
        statement.setString(1, tenantsData.get(id).getName());
        statement.setString(2, tenantsData.get(id).getConfiguration());
        statement.setObject(3, new PG_UUID(id));
        statement.addBatch();
    }

    try {
        statement.executeBatch();
    } finally {
        statement.close();
    }
}

From source file:com.smartmarmot.orabbix.Configurator.java

private DBConn getConnection(String dbName) throws Exception {
    try {/*from   ww w  . java2  s .  c o  m*/
        verifyConfig();

        SmartLogger.logThis(Level.DEBUG, "getConnection for database " + dbName);
        String url = "";
        try {
            url = new String(_props.getProperty(dbName + "." + Constants.CONN_URL));
        } catch (Exception ex) {
            SmartLogger.logThis(Level.ERROR, "Error on Configurator getConnection while getting " + dbName + "."
                    + Constants.CONN_URL + " " + ex.getMessage());
        }

        String uname = "";
        try {
            uname = new String(_props.getProperty(dbName + "." + Constants.CONN_USERNAME));
        } catch (Exception ex) {
            try {
                SmartLogger.logThis(Level.DEBUG, "Error on Configurator getConnection while getting " + dbName
                        + "." + Constants.CONN_USERNAME + " " + ex.getMessage());

                uname = new String(_props.getProperty(Constants.CONN_DEFAULT_USERNAME));
            } catch (Exception ex1) {
                SmartLogger.logThis(Level.ERROR, "Error on Configurator getConnection while getting "
                        + Constants.CONN_DEFAULT_USERNAME + " " + ex1.getMessage());
            }
        }
        String password = "";
        try {
            password = new String(_props.getProperty(dbName + "." + Constants.CONN_PASSWORD));
        } catch (Exception ex) {
            try {
                SmartLogger.logThis(Level.DEBUG, "Error on Configurator getConnection while getting " + dbName
                        + "." + Constants.CONN_PASSWORD + " " + ex.getMessage());
                password = new String(_props.getProperty(Constants.CONN_DEFAULT_PASSWORD));
            } catch (Exception ex1) {
                SmartLogger.logThis(Level.ERROR, "Error on Configurator getConnection while getting " + dbName
                        + "." + Constants.CONN_PASSWORD + " " + ex.getMessage());
            }
        }
        DriverAdapterCPDS cpds = new DriverAdapterCPDS();
        cpds.setDriver(Constants.ORACLE_DRIVER);
        cpds.setUrl(url.toString());
        cpds.setUser(uname.toString());
        cpds.setPassword(password.toString());
        SharedPoolDataSource tds = new SharedPoolDataSource();
        tds.setConnectionPoolDataSource(cpds);
        // tds.setMaxActive(5);
        Integer maxActive = new Integer(5);
        try {
            maxActive = new Integer(_props.getProperty(dbName + "." + Constants.CONN_MAX_ACTIVE));
        } catch (Exception ex) {
            SmartLogger.logThis(Level.DEBUG,
                    "Note: " + dbName + "." + Constants.CONN_MAX_ACTIVE + " " + ex.getMessage());
            try {
                maxActive = new Integer(
                        _props.getProperty(Constants.DATABASES_LIST + "." + Constants.CONN_MAX_ACTIVE));
            } catch (Exception e) {
                SmartLogger.logThis(Level.WARN, "Note: " + Constants.DATABASES_LIST + "."
                        + Constants.CONN_MAX_ACTIVE + " " + e.getMessage());
                SmartLogger.logThis(Level.WARN, "Warning I will use default value " + maxActive);
            }
        }
        tds.setMaxActive(maxActive.intValue());
        Integer maxWait = new Integer(100);
        try {
            maxWait = new Integer(_props.getProperty(dbName + "." + Constants.CONN_MAX_WAIT));
        } catch (Exception ex) {
            SmartLogger.logThis(Level.DEBUG,
                    "Note: " + dbName + "." + Constants.CONN_MAX_WAIT + " " + ex.getMessage());
            try {
                maxWait = new Integer(
                        _props.getProperty(Constants.DATABASES_LIST + "." + Constants.CONN_MAX_WAIT));
            } catch (Exception e) {
                SmartLogger.logThis(Level.WARN, "Note: " + Constants.DATABASES_LIST + "."
                        + Constants.CONN_MAX_WAIT + " " + e.getMessage());
                SmartLogger.logThis(Level.WARN, "Warning I will use default value " + maxWait);
            }
        }
        tds.setMaxWait(maxWait.intValue());
        Integer maxIdle = new Integer(1);
        try {
            maxIdle = new Integer(_props.getProperty(dbName + "." + Constants.CONN_MAX_IDLE));
        } catch (Exception ex) {
            SmartLogger.logThis(Level.DEBUG,
                    "Note: " + dbName + "." + Constants.CONN_MAX_IDLE + " " + ex.getMessage());
            try {
                maxIdle = new Integer(
                        _props.getProperty(Constants.DATABASES_LIST + "." + Constants.CONN_MAX_IDLE));
            } catch (Exception e) {
                SmartLogger.logThis(Level.WARN, "Note: " + Constants.DATABASES_LIST + "."
                        + Constants.CONN_MAX_IDLE + " " + e.getMessage());
                SmartLogger.logThis(Level.WARN, "Warning I will use default value " + maxIdle);
            }
        }
        tds.setMaxIdle(maxIdle.intValue());

        SmartLogger.logThis(Level.INFO, "DB Pool created: " + tds);
        SmartLogger.logThis(Level.INFO, "URL=" + url.toString());
        SmartLogger.logThis(Level.INFO, "maxPoolSize=" + tds.getMaxActive());
        SmartLogger.logThis(Level.INFO, "maxIdleSize=" + tds.getMaxIdle());
        SmartLogger.logThis(Level.INFO, "maxIdleTime=" + tds.getMinEvictableIdleTimeMillis() + "ms");
        SmartLogger.logThis(Level.INFO, "poolTimeout=" + tds.getMaxWait());
        SmartLogger.logThis(Level.INFO,
                "timeBetweenEvictionRunsMillis=" + tds.getTimeBetweenEvictionRunsMillis());
        SmartLogger.logThis(Level.INFO, "numTestsPerEvictionRun=" + tds.getNumTestsPerEvictionRun());

        tds.setValidationQuery(Constants.ORACLE_VALIDATION_QUERY);
        Connection con = null;
        con = tds.getConnection();
        PreparedStatement p_stmt = null;
        p_stmt = con.prepareStatement(Constants.ORACLE_WHOAMI_QUERY);
        ResultSet rs = null;
        rs = p_stmt.executeQuery();
        String tempStr = new String("");
        ResultSetMetaData rsmd = rs.getMetaData();
        int numColumns = rsmd.getColumnCount();
        while (rs.next()) {
            for (int r = 1; r < numColumns + 1; r++) {
                tempStr = tempStr + rs.getObject(r).toString().trim();
            }
        }
        SmartLogger.logThis(Level.INFO, "Connected as " + tempStr);

        con.close();
        con = null;
        con = tds.getConnection();
        p_stmt = con.prepareStatement(Constants.ORACLE_DBNAME_QUERY);
        rs = p_stmt.executeQuery();
        rsmd = rs.getMetaData();
        numColumns = rsmd.getColumnCount();
        tempStr = "";
        while (rs.next()) {
            for (int r = 1; r < numColumns + 1; r++) {
                tempStr = tempStr + rs.getObject(r).toString().trim();
            }
        }
        SmartLogger.logThis(Level.INFO, "--------- on Database -> " + tempStr);
        con.close();
        con = null;
        DBConn mydbconn = new DBConn(tds, dbName.toString());
        return mydbconn;

    } catch (Exception ex) {
        SmartLogger.logThis(Level.ERROR,
                "Error on Configurator for database " + dbName + " -->" + ex.getMessage());
        return null;
    }
}

From source file:com.twosigma.beaker.sql.QueryExecutor.java

private QueryResult executeQuery(int currentIterationIndex, BeakerParseResult queryLine, Connection conn,
        NamespaceClient namespaceClient) throws SQLException, ReadVariableException {

    QueryResult queryResult = new QueryResult();

    try (PreparedStatement statement = conn.prepareStatement(queryLine.getResultQuery())) {
        this.statement = statement;
        int n = 1;
        for (BeakerInputVar parameter : queryLine.getInputVars()) {
            if (parameter.getErrorMessage() != null)
                throw new ReadVariableException(parameter.getErrorMessage());
            Object obj;/* ww  w .java2s . c o  m*/
            try {
                obj = namespaceClient.get(parameter.objectName);

                if (!parameter.isArray() && !parameter.isObject()) {
                    statement.setObject(n, obj);
                } else if (!parameter.isArray() && parameter.isObject()) {
                    statement.setObject(n, getValue(obj, parameter.getFieldName()));
                } else if (parameter.isArray()) {
                    int index;
                    if (currentIterationIndex > 0 && parameter.isAll()) {
                        index = currentIterationIndex;
                    } else {
                        index = parameter.index;
                    }
                    if (!parameter.isObject()) {
                        if (obj instanceof List) {
                            statement.setObject(n, ((List) obj).get(index));
                        } else if (obj.getClass().isArray()) {
                            Object arrayElement = Array.get(obj, index);
                            statement.setObject(n, arrayElement);
                        }
                    } else {
                        if (obj instanceof List) {
                            statement.setObject(n, getValue(((List) obj).get(index), parameter.getFieldName()));
                        } else if (obj.getClass().isArray()) {
                            Object arrayElement = Array.get(obj, index);
                            statement.setObject(n, getValue(arrayElement, parameter.getFieldName()));
                        }
                    }
                }
                n++;
            } catch (Exception e) {
                throw new ReadVariableException(parameter.objectName, e);
            }
        }

        boolean hasResultSet = statement.execute();
        if (hasResultSet) {
            ResultSet rs = statement.getResultSet();

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                queryResult.getColumns().add(rs.getMetaData().getColumnName(i));
                queryResult.getTypes().add(rs.getMetaData().getColumnClassName(i));
            }

            while (rs.next()) {
                if (rs.getMetaData().getColumnCount() != 0) {
                    List<Object> row = new ArrayList<Object>();
                    queryResult.getValues().add(row);

                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        if (java.sql.Date.class.getName().equals(rs.getMetaData().getColumnClassName(i))) {
                            java.sql.Date sqlDate = rs.getDate(i);
                            row.add(sqlDate == null ? null : new Date(sqlDate.getTime()));
                        } else {
                            row.add(rs.getObject(i));
                        }
                    }
                }
            }
        }

    } catch (SQLException e) {
        //Logger.getLogger(QueryExecutor.class.getName()).log(Level.SEVERE, null, e);
        try {
            conn.rollback();
        } catch (Exception e1) {
            //Logger.getLogger(QueryExecutor.class.getName()).log(Level.SEVERE, null, e1);
        }

        throw e;
    }
    return queryResult;
}

From source file:com.twosigma.beakerx.sql.QueryExecutor.java

private QueryResult executeQuery(int currentIterationIndex, BeakerParseResult queryLine, Connection conn,
        BeakerXClient namespaceClient) throws SQLException, ReadVariableException {

    QueryResult queryResult = new QueryResult();

    try (PreparedStatement statement = conn.prepareStatement(queryLine.getResultQuery())) {
        this.statement = statement;
        int n = 1;
        for (BeakerInputVar parameter : queryLine.getInputVars()) {
            if (parameter.getErrorMessage() != null)
                throw new ReadVariableException(parameter.getErrorMessage());
            Object obj;// www .j  av  a2 s  . co  m
            try {
                obj = namespaceClient.get(parameter.objectName);

                if (!parameter.isArray() && !parameter.isObject()) {
                    statement.setObject(n, obj);
                } else if (!parameter.isArray() && parameter.isObject()) {
                    statement.setObject(n, getValue(obj, parameter.getFieldName()));
                } else if (parameter.isArray()) {
                    int index;
                    if (currentIterationIndex > 0 && parameter.isAll()) {
                        index = currentIterationIndex;
                    } else {
                        index = parameter.index;
                    }
                    if (!parameter.isObject()) {
                        if (obj instanceof List) {
                            statement.setObject(n, ((List) obj).get(index));
                        } else if (obj.getClass().isArray()) {
                            Object arrayElement = Array.get(obj, index);
                            statement.setObject(n, arrayElement);
                        }
                    } else {
                        if (obj instanceof List) {
                            statement.setObject(n, getValue(((List) obj).get(index), parameter.getFieldName()));
                        } else if (obj.getClass().isArray()) {
                            Object arrayElement = Array.get(obj, index);
                            statement.setObject(n, getValue(arrayElement, parameter.getFieldName()));
                        }
                    }
                }
                n++;
            } catch (Exception e) {
                throw new ReadVariableException(parameter.objectName, e);
            }
        }

        boolean hasResultSet = statement.execute();
        if (hasResultSet) {
            ResultSet rs = statement.getResultSet();

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                queryResult.getColumns().add(rs.getMetaData().getColumnName(i));
                queryResult.getTypes().add(rs.getMetaData().getColumnClassName(i));
            }

            while (rs.next()) {
                if (rs.getMetaData().getColumnCount() != 0) {
                    List<Object> row = new ArrayList<Object>();
                    queryResult.getValues().add(row);

                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        if (java.sql.Date.class.getName().equals(rs.getMetaData().getColumnClassName(i))) {
                            java.sql.Date sqlDate = rs.getDate(i);
                            row.add(sqlDate == null ? null : new Date(sqlDate.getTime()));
                        } else {
                            row.add(rs.getObject(i));
                        }
                    }
                }
            }
        }

    } catch (SQLException e) {
        //Logger.getLogger(QueryExecutor.class.getName()).log(Level.SEVERE, null, e);
        try {
            conn.rollback();
        } catch (Exception e1) {
            //Logger.getLogger(QueryExecutor.class.getName()).log(Level.SEVERE, null, e1);
        }

        throw e;
    }
    return queryResult;
}

From source file:com.twosigma.beaker.sqlsh.utils.QueryExecutor.java

private QueryResult executeQuery(int currentIterationIndex, BeakerParseResult queryLine, Connection conn,
        NamespaceClient namespaceClient) throws SQLException, ReadVariableException {

    QueryResult queryResult = new QueryResult();
    String sql = queryLine.getResultQuery();

    try (Statement statement = conn.createStatement()) {
        this.statement = statement;
        for (BeakerInputVar parameter : queryLine.getInputVars()) {
            if (parameter.getErrorMessage() != null)
                throw new ReadVariableException(parameter.getErrorMessage());
            Object obj;/*w w  w .j  a v  a2 s  .co m*/
            try {
                obj = namespaceClient.get(parameter.objectName);

                if (!parameter.isArray() && !parameter.isObject()) {
                    sql = setObject(sql, obj);
                } else if (!parameter.isArray() && parameter.isObject()) {
                    sql = setObject(sql, getValue(obj, parameter.getFieldName()));
                } else if (parameter.isArray()) {
                    int index;
                    if (currentIterationIndex > 0 && parameter.isAll()) {
                        index = currentIterationIndex;
                    } else {
                        index = parameter.index;
                    }
                    if (!parameter.isObject()) {
                        if (obj instanceof List) {
                            sql = setObject(sql, ((List) obj).get(index));
                        } else if (obj.getClass().isArray()) {
                            Object arrayElement = Array.get(obj, index);
                            sql = setObject(sql, arrayElement);
                        }
                    } else {
                        if (obj instanceof List) {
                            sql = setObject(sql, getValue(((List) obj).get(index), parameter.getFieldName()));
                        } else if (obj.getClass().isArray()) {
                            Object arrayElement = Array.get(obj, index);
                            sql = setObject(sql, getValue(arrayElement, parameter.getFieldName()));
                        }
                    }
                }
            } catch (Exception e) {
                throw new ReadVariableException(parameter.objectName, e);
            }
        }

        boolean hasResultSet = statement.execute(sql);
        if (hasResultSet) {
            ResultSet rs = statement.getResultSet();

            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                queryResult.getColumns().add(rs.getMetaData().getColumnName(i));
                queryResult.getTypes().add(rs.getMetaData().getColumnClassName(i));
            }

            while (rs.next()) {
                if (rs.getMetaData().getColumnCount() != 0) {
                    List<Object> row = new ArrayList<Object>();
                    queryResult.getValues().add(row);

                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        if (java.sql.Date.class.getName().equals(rs.getMetaData().getColumnClassName(i))) {
                            java.sql.Date sqlDate = rs.getDate(i);
                            row.add(sqlDate == null ? null : new Date(sqlDate.getTime()));
                        } else {
                            row.add(rs.getObject(i));
                        }
                    }
                }
            }
        }

    } catch (SQLException e) {
        //Logger.getLogger(QueryExecutor.class.getName()).log(Level.SEVERE, null, e);
        try {
            conn.rollback();
        } catch (Exception e1) {
            //Logger.getLogger(QueryExecutor.class.getName()).log(Level.SEVERE, null, e1);
        }

        throw e;
    }
    return queryResult;
}

From source file:com.nextep.designer.synch.services.impl.DataCaptureService.java

private IVersionable<IDataSet> fetchDataSet(Connection conn, DBVendor vendor, IBasicTable t,
        List<IBasicColumn> columns, IProgressMonitor m) throws SQLException {
    final String taskName = "Capturing " + t.getName() + " data";
    SubMonitor monitor = SubMonitor.convert(m, taskName, 100);
    monitor.subTask(taskName);//w  w  w  . j  a  v  a  2s . c  om
    final IVersionable<IDataSet> v = VersionableFactory.createVersionable(IDataSet.class);
    final IDataSet dataSet = v.getVersionnedObject().getModel();
    final Collection<IDataLine> datalineBuffer = new ArrayList<IDataLine>(BUFFER_SIZE);
    // Configuring dataset
    dataSet.setTable(t);
    // Aligning captured data set with repository dataset name
    if (!t.getDataSets().isEmpty()) {
        // Taking first one
        final IDataSet set = t.getDataSets().iterator().next();
        // Captured data set will be named just like the repository dataset to force name synch
        dataSet.setName(set.getName());
        // Captured columns are restricted to defined data set columns only
        columns = set.getColumns();
    } else {
        dataSet.setName(t.getName());
    }
    for (IBasicColumn c : columns) {
        dataSet.addColumn(c);
    }
    // Fetching data
    Statement stmt = null;
    ResultSet rset = null;
    long counter = 0;
    try {
        stmt = conn.createStatement();
        final String dataSelect = buildDataSelect(vendor, t, columns);
        monitor.subTask(taskName + " - querying data");
        rset = stmt.executeQuery(dataSelect);
        final ResultSetMetaData md = rset.getMetaData();
        int bufferCount = 0;
        while (rset.next()) {
            // Handling cancellation
            if (monitor.isCanceled()) {
                return v;
            } else {
                if (counter++ % 100 == 0) {
                    monitor.worked(100);
                }
            }
            // Preparing dataline
            final IDataLine line = typedObjectFactory.create(IDataLine.class);

            // Iterating over result set columns
            for (int i = 1; i <= md.getColumnCount(); i++) {
                // Fetching result set column value
                Object value = null;
                try {
                    value = rset.getObject(i);
                } catch (SQLException e) {
                    LOGGER.error("Data import problem on " + t.getName() + " column " + i + " of line "
                            + counter + " failed to fetch data, NULL will be used instead [" + e.getMessage()
                            + "]", e); //$NON-NLS-1$
                }
                // Preparing column value
                final IColumnValue colValue = typedObjectFactory.create(IColumnValue.class);
                colValue.setDataLine(line);
                colValue.setColumn(columns.get(i - 1));
                colValue.setValue(value);
                line.addColumnValue(colValue);
            }
            datalineBuffer.add(line);
            if (++bufferCount >= BUFFER_SIZE) {
                dataService.addDataline(dataSet, datalineBuffer.toArray(new IDataLine[datalineBuffer.size()]));
                datalineBuffer.clear();
                bufferCount = 0;
                monitor.subTask(taskName + " - " + counter + " lines fetched"); //$NON-NLS-1$
            }
        }
        // Flushing end of buffer
        if (!datalineBuffer.isEmpty()) {
            dataService.addDataline(dataSet, datalineBuffer.toArray(new IDataLine[datalineBuffer.size()]));
        }
        LOGGER.info("Captured " + counter + " data lines from " + t.getName());
    } catch (SQLException e) {
        LOGGER.error("Unable to fetch data from table " + t.getName()
                + ": this table may need structure synchronization: " + e.getMessage(), e);
    } finally {
        if (rset != null) {
            rset.close();
        }
        if (stmt != null) {
            stmt.close();
        }
    }
    monitor.done();
    // Only returning dataset if at least one row was fetched
    return counter == 0 ? null : v;
}