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.chiorichan.database.DatabaseEngine.java

public LinkedHashMap<String, Object> selectOne(String table, List<String> keys, List<? extends Object> values)
        throws SQLException {
    if (con == null)
        throw new SQLException("The SQL connection is closed or was never opened.");

    if (isNull(keys) || isNull(values)) {
        Loader.getLogger().warning("[DB ERROR] Either keys array or values array equals null!\n");
        return null;
    }/*  w  ww. j a v a 2s . c o  m*/

    if (keys.size() != values.size()) {
        System.err.print("[DB ERROR] Keys array and values array must match in length!\n");
        return null;
    }

    LinkedHashMap<String, Object> result = new LinkedHashMap<String, Object>();

    String where = "";

    if (keys.size() > 0 && values.size() > 0) {
        int x = 0;
        String prefix = "";
        for (String s : keys) {
            where += prefix + "`" + s + "` = '" + values.get(x) + "'";
            x++;
            prefix = " AND ";
        }
    }

    ResultSet rs = query("SELECT * FROM `" + table + "` WHERE " + where + " LIMIT 1;");

    if (rs != null && getRowCount(rs) > 0) {
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            do {
                for (int i = 1; i < columnCount + 1; i++) {
                    result.put(rsmd.getColumnName(i), rs.getObject(i));
                }
            } while (rs.next());

            return result;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    return null;
}

From source file:org.mayocat.shop.marketplace.store.jdbi.AbstractEntityAndTenantMapper.java

@Override
public EntityAndTenant<Product> map(int index, ResultSet result, StatementContext ctx) throws SQLException {
    Product product = extractEntity(index, result, ctx);

    String slug = result.getString("tenant_entity_slug");
    String defaultHost = result.getString("tenant_entity_default_host");
    ObjectMapper mapper = new ObjectMapper();
    mapper.registerModule(new GuavaModule());
    Integer configurationVersion = result.getInt("tenant_entity_configuration_version");
    TenantConfiguration configuration;/*from   w  ww  .  ja v a  2 s  .c  o  m*/
    if (Strings.isNullOrEmpty(result.getString("tenant_entity_configuration"))) {
        configuration = new TenantConfiguration(configurationVersion,
                Collections.<String, Serializable>emptyMap());
    } else {
        try {
            Map<String, Serializable> data = mapper.readValue(result.getString("tenant_entity_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("tenant_entity_id"), slug, configuration);
    tenant.setFeaturedImageId((UUID) result.getObject("tenant_entity_featured_image_id"));
    tenant.setSlug(slug);
    tenant.setDefaultHost(defaultHost);
    tenant.setCreationDate(result.getTimestamp("tenant_entity_creation_date"));
    tenant.setName(result.getString("tenant_entity_name"));
    tenant.setDescription(result.getString("tenant_entity_description"));
    tenant.setContactEmail(result.getString("tenant_entity_contact_email"));

    return new EntityAndTenant<>(product, tenant);
}

From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java

public List<RowVO> getRows(String sql, List<ColumnVO> columns) throws Exception {
    List<RowVO> rows = new LinkedList<RowVO>();

    Exception error = null;//from ww  w . j av  a  2s . c om

    Connection connection = null;
    PreparedStatement preparedStmnt = null;

    try {
        DataSource dataSource = poolDataSources.get(schemaId);
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);

        preparedStmnt = connection.prepareStatement(sql);
        ResultSet result = preparedStmnt.executeQuery();
        while (result.next()) {
            RowVO row = new RowVO();
            for (ColumnVO column : columns) {
                String columnName = column.getNameOnTable();
                String columnValue = null;
                Object columnValueObj = result.getObject(columnName);
                if (columnValueObj != null) {
                    columnValue = columnValueObj.toString();

                }

                /* if the column is a geometry type then set the
                 * geometry column name of the row */
                if (column.getSqlType() == Types.OTHER) {
                    row.setGeomFieldName(columnName);
                }
                row.addField(columnName, columnValue);
            }
            rows.add(row);
        }
    } catch (SQLException e) {
        error = e;
    } finally {
        if (preparedStmnt != null) {
            try {
                preparedStmnt.close();
            } catch (SQLException se2) {
                log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage()));
            }
        }
        if (connection != null) {
            try {
                if (error != null) {
                    connection.rollback();
                }
            } catch (SQLException se) {
                log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage()));
            }
            try {
                connection.close();
            } catch (SQLException se) {
                log.warn("Se produjo un error al intentar cerrar la conexin: "
                        .concat(se.getLocalizedMessage()));
            }
        }
    }

    if (error != null) {
        throw error;
    }

    return rows;
}

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

private DBConn getDBConnManager(String dbName) throws Exception {
    try {//from  w  ww . ja  va2 s  .  co  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));
           try {
              dbtype = new DBType(new String(_props.getProperty(dbName  + "." + Constants.DATABASES_TYPE)));
           } catch (Exception ex) {
              SmartLogger.logThis(Level.ERROR,
          "Error on Configurator getConnection while getting "
                + dbName + "." + Constants.DATABASES_TYPE + " "
                + ex.getMessage());
           }
                   
           DriverAdapterCPDS cpds = new DriverAdapterCPDS();
           cpds.setDriver(dbtype.getJDBCDriverClass());
           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(dbtype.getValidationQuery());
           Connection con = null;
           con = tds.getConnection();
           PreparedStatement p_stmt = null;
           p_stmt = con.prepareStatement(dbtype.getWhoAmIQuery());
           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(dbtype.getDbNameQuery());
           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(),dbtype);
           */
        DBcfg mydbcgf = new DBcfg(_props, dbName);
        DBConnManager dbcm = new DBConnManager(mydbcgf);
        DBConn mydbconn = new DBConn(dbcm, dbName.toString());
        Connection con = null;
        con = mydbconn.getDBCM().getDs().getConnection();
        PreparedStatement p_stmt = null;

        ResultSet rs = null;
        p_stmt = con.prepareStatement(mydbconn.getDBCM().getDBType().getWhoAmIQuery());
        rs = p_stmt.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        int numColumns = rsmd.getColumnCount();
        String tempStr = "";
        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 = mydbconn.getDBCM().getDs().getConnection();
        p_stmt = null;
        rs = null;
        p_stmt = con.prepareStatement(mydbconn.getDBCM().getDBType().getDbNameQuery());
        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);

        return mydbconn;

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

From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java

private <T> List<T> commandToRecords(Class objClass, String command, HashMap<Method, String> fieldMapping,
        Object[] params) {/*w  ww.  j ava2  s .  c o  m*/
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
        ArrayList<T> records = new ArrayList();
        st = conn.prepareStatement(command);
        setParams(st, params);
        rs = st.executeQuery();
        while (rs.next()) {
            T newObj = (T) objClass.newInstance();
            for (Method method : fieldMapping.keySet()) {
                try {
                    Object val = convertType(rs.getObject((String) fieldMapping.get(method)), method);
                    if (val != null)
                        method.invoke(newObj, val);
                } catch (Exception ex) {
                    if (!method.isAnnotationPresent(Optional.class)) {
                        throw new NativeQueryException(command, method.getName(), ex);
                    }
                }
            }
            records.add(newObj);
        }
        return records;
    } catch (Exception ex) {
        if (ex instanceof NativeQueryException)
            throw (NativeQueryException) ex;
        else
            throw new NativeQueryException(command, null, ex);
    } finally {
        closeOnFinally(rs, st);
    }
}

From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java

private <T> List<T> commandToDbStruct(Class dbStructClazz, String command, HashMap<Field, String> fieldMapping,
        Object[] params) {//from  www .  ja  va2 s  . c o  m
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
        ArrayList<T> records = new ArrayList();
        st = conn.prepareStatement(command);
        setParams(st, params);
        rs = st.executeQuery();
        while (rs.next()) {
            T newObj = (T) dbStructClazz.newInstance();
            for (Field field : fieldMapping.keySet()) {
                try {
                    Object val = convertType(rs.getObject((String) fieldMapping.get(field)), field);
                    if (val != null)
                        field.set(newObj, val);
                } catch (Exception ex) {
                    throw new NativeQueryException(command, field.getName(), ex);
                }
            }
            records.add(newObj);
        }
        return records;
    } catch (Exception ex) {
        if (ex instanceof NativeQueryException)
            throw (NativeQueryException) ex;
        else
            throw new NativeQueryException(command, null, ex);
    } finally {
        closeOnFinally(rs, st);
    }
}

From source file:computer_store.GUI.java

private void fillCompTable(javax.swing.JTable table, java.sql.ResultSet rs) {
    try {/*from w  w  w . j  av  a 2 s .co m*/

        //To remove previously added rows
        while (table.getRowCount() > 0) {
            ((javax.swing.table.DefaultTableModel) table.getModel()).removeRow(0);
        }
        int columns = rs.getMetaData().getColumnCount();
        int rows = 0;
        java.util.ArrayList<String> helper = new java.util.ArrayList();
        //Adding column titles
        Object[] ids = new Object[columns];
        for (int i = 1; i <= columns; i++) {
            ids[i - 1] = rs.getMetaData().getColumnName(i);
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).setColumnIdentifiers(ids);
        //Adding rows from ResultSet to table model.
        while (rs.next()) {
            helper.add(rs.getString("name"));
            rows++;
            Object[] row = new Object[columns];
            for (int i = 1; i <= columns; i++) {
                row[i - 1] = rs.getObject(i);
            }
            ((javax.swing.table.DefaultTableModel) table.getModel()).insertRow(rs.getRow() - 1, row);
        }

        //Adding new column with restock numbers            
        Object[] restock = new Object[rows];
        for (int i = 0; i < restock.length; i++) {
            restock[i] = handler.getCompRestock(helper.get(i));
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("# to Restock", restock);
        //Adding selling prices
        Object[] sellPrice = new Object[rows];
        for (int i = 0; i < sellPrice.length; i++) {
            sellPrice[i] = ((int) jTable1.getValueAt(i, 1)) * 1.3;
        }
        ((javax.swing.table.DefaultTableModel) table.getModel()).addColumn("Selling price", sellPrice);

        rs.close();
    } catch (Exception e) {
        System.out.print(e);
    }
}

From source file:at.alladin.rmbt.statisticServer.export.ExportResource.java

@Get
public Representation request(final String entity) {
    //Before doing anything => check if a cached file already exists and is new enough
    String property = System.getProperty("java.io.tmpdir");

    final String filename_zip;
    final String filename_csv;

    //allow filtering by month/year
    int year = -1;
    int month = -1;
    int hours = -1;
    boolean hoursExport = false;
    boolean dateExport = false;

    if (getRequest().getAttributes().containsKey("hours")) { // export by hours
        try {/*  ww  w  .  j a v a  2 s  . c o m*/
            hours = Integer.parseInt(getRequest().getAttributes().get("hours").toString());
        } catch (NumberFormatException ex) {
            //Nothing -> just fall back
        }
        if (hours <= 7 * 24 && hours >= 1) { //limit to 1 week (avoid DoS)
            hoursExport = true;
        }
    } else if (!hoursExport && getRequest().getAttributes().containsKey("year")) { // export by month/year 
        try {
            year = Integer.parseInt(getRequest().getAttributes().get("year").toString());
            month = Integer.parseInt(getRequest().getAttributes().get("month").toString());
        } catch (NumberFormatException ex) {
            //Nothing -> just fall back
        }
        if (year < 2099 && month > 0 && month <= 12 && year > 2000) {
            dateExport = true;
        }
    }

    if (hoursExport) {
        filename_zip = FILENAME_ZIP_HOURS.replace("%HOURS%", String.format("%03d", hours));
        filename_csv = FILENAME_CSV_HOURS.replace("%HOURS%", String.format("%03d", hours));
        cacheThresholdMs = 5 * 60 * 1000; //5 minutes
    } else if (dateExport) {
        filename_zip = FILENAME_ZIP.replace("%YEAR%", Integer.toString(year)).replace("%MONTH%",
                String.format("%02d", month));
        filename_csv = FILENAME_CSV.replace("%YEAR%", Integer.toString(year)).replace("%MONTH%",
                String.format("%02d", month));
        cacheThresholdMs = 23 * 60 * 60 * 1000; //23 hours
    } else {
        filename_zip = FILENAME_ZIP_CURRENT;
        filename_csv = FILENAME_CSV_CURRENT;
        cacheThresholdMs = 3 * 60 * 60 * 1000; //3 hours
    }

    final File cachedFile = new File(property + File.separator + ((zip) ? filename_zip : filename_csv));
    final File generatingFile = new File(
            property + File.separator + ((zip) ? filename_zip : filename_csv) + "_tmp");
    if (cachedFile.exists()) {

        //check if file has been recently created OR a file is currently being created
        if (((cachedFile.lastModified() + cacheThresholdMs) > (new Date()).getTime())
                || (generatingFile.exists()
                        && (generatingFile.lastModified() + cacheThresholdMs) > (new Date()).getTime())) {

            //if so, return the cached file instead of a cost-intensive new one
            final OutputRepresentation result = new OutputRepresentation(
                    zip ? MediaType.APPLICATION_ZIP : MediaType.TEXT_CSV) {

                @Override
                public void write(OutputStream out) throws IOException {
                    InputStream is = new FileInputStream(cachedFile);
                    IOUtils.copy(is, out);
                    out.close();
                }

            };
            if (zip) {
                final Disposition disposition = new Disposition(Disposition.TYPE_ATTACHMENT);
                disposition.setFilename(filename_zip);
                result.setDisposition(disposition);
            }
            return result;

        }
    }

    final String timeClause;

    if (dateExport)
        timeClause = " AND (EXTRACT (month FROM t.time AT TIME ZONE 'UTC') = " + month
                + ") AND (EXTRACT (year FROM t.time AT TIME ZONE 'UTC') = " + year + ") ";
    else if (hoursExport)
        timeClause = " AND time > now() - interval '" + hours + " hours' ";
    else
        timeClause = " AND time > current_date - interval '31 days' ";

    final String sql = "SELECT" + " ('P' || t.open_uuid) open_uuid,"
            + " ('O' || t.open_test_uuid) open_test_uuid,"
            + " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') time_utc,"
            + " nt.group_name cat_technology," + " nt.name network_type,"
            + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN"
            + " t.geo_lat" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_lat*1111)/1111" + " ELSE null"
            + " END) lat,"
            + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN"
            + " t.geo_long" + " WHEN (t.geo_accuracy < ?) THEN" + " ROUND(t.geo_long*741)/741 " + " ELSE null"
            + " END) long," + " (CASE WHEN ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN"
            + " 'rastered'" + //make raster transparent
            " ELSE t.geo_provider" + " END) loc_src,"
            + " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') "
            + " THEN round(t.geo_accuracy::float * 10)/10 "
            + " WHEN (t.geo_accuracy < 100) AND ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN 100"
            + // limit accuracy to 100m
            " WHEN (t.geo_accuracy < ?) THEN round(t.geo_accuracy::float * 10)/10"
            + " ELSE null END) loc_accuracy, "
            + " (CASE WHEN (t.zip_code < 1000 OR t.zip_code > 9999) THEN null ELSE t.zip_code END) zip_code,"
            + " t.gkz gkz," + " t.country_location country_location," + " t.speed_download download_kbit,"
            + " t.speed_upload upload_kbit," + " round(t.ping_median::float / 100000)/10 ping_ms,"
            + " t.lte_rsrp," + " t.lte_rsrq," + " ts.name server_name," + " duration test_duration,"
            + " num_threads," + " t.plattform platform," + " COALESCE(adm.fullname, t.model) model,"
            + " client_software_version client_version," + " network_operator network_mcc_mnc,"
            + " network_operator_name network_name," + " network_sim_operator sim_mcc_mnc," + " nat_type,"
            + " public_ip_asn asn," + " client_public_ip_anonymized ip_anonym,"
            + " (ndt.s2cspd*1000)::int ndt_download_kbit," + " (ndt.c2sspd*1000)::int ndt_upload_kbit,"
            + " COALESCE(t.implausible, false) implausible," + " t.signal_strength" + " FROM test t"
            + " LEFT JOIN network_type nt ON nt.uid=t.network_type"
            + " LEFT JOIN device_map adm ON adm.codename=t.model"
            + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN test_ndt ndt ON t.uid=ndt.test_id"
            + " WHERE " + " t.deleted = false" + timeClause + " AND status = 'FINISHED'" + " ORDER BY t.uid";

    final String[] columns;
    final List<String[]> data = new ArrayList<>();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = conn.prepareStatement(sql);

        //insert filter for accuracy
        double accuracy = Double.parseDouble(settings.getString("RMBT_GEO_ACCURACY_DETAIL_LIMIT"));
        ps.setDouble(1, accuracy);
        ps.setDouble(2, accuracy);
        ps.setDouble(3, accuracy);
        ps.setDouble(4, accuracy);
        ps.setDouble(5, accuracy);
        ps.setDouble(6, accuracy);

        if (!ps.execute())
            return null;
        rs = ps.getResultSet();

        final ResultSetMetaData meta = rs.getMetaData();
        final int colCnt = meta.getColumnCount();
        columns = new String[colCnt];
        for (int i = 0; i < colCnt; i++)
            columns[i] = meta.getColumnName(i + 1);

        while (rs.next()) {
            final String[] line = new String[colCnt];

            for (int i = 0; i < colCnt; i++) {
                final Object obj = rs.getObject(i + 1);
                line[i] = obj == null ? null : obj.toString();
            }

            data.add(line);
        }
    } catch (final SQLException e) {
        e.printStackTrace();
        return null;
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    final OutputRepresentation result = new OutputRepresentation(
            zip ? MediaType.APPLICATION_ZIP : MediaType.TEXT_CSV) {
        @Override
        public void write(OutputStream out) throws IOException {
            //cache in file => create temporary temporary file (to 
            // handle errors while fulfilling a request)
            String property = System.getProperty("java.io.tmpdir");
            final File cachedFile = new File(
                    property + File.separator + ((zip) ? filename_zip : filename_csv) + "_tmp");
            OutputStream outf = new FileOutputStream(cachedFile);

            if (zip) {
                final ZipOutputStream zos = new ZipOutputStream(outf);
                final ZipEntry zeLicense = new ZipEntry("LIZENZ.txt");
                zos.putNextEntry(zeLicense);
                final InputStream licenseIS = getClass().getResourceAsStream("DATA_LICENSE.txt");
                IOUtils.copy(licenseIS, zos);
                licenseIS.close();

                final ZipEntry zeCsv = new ZipEntry(filename_csv);
                zos.putNextEntry(zeCsv);
                outf = zos;
            }

            final OutputStreamWriter osw = new OutputStreamWriter(outf);
            final CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);

            for (final String c : columns)
                csvPrinter.print(c);
            csvPrinter.println();

            for (final String[] line : data) {
                for (final String f : line)
                    csvPrinter.print(f);
                csvPrinter.println();
            }
            csvPrinter.flush();

            if (zip)
                outf.close();

            //if we reach this code, the data is now cached in a temporary tmp-file
            //so, rename the file for "production use2
            //concurrency issues should be solved by the operating system
            File newCacheFile = new File(property + File.separator + ((zip) ? filename_zip : filename_csv));
            Files.move(cachedFile.toPath(), newCacheFile.toPath(), StandardCopyOption.ATOMIC_MOVE,
                    StandardCopyOption.REPLACE_EXISTING);

            FileInputStream fis = new FileInputStream(newCacheFile);
            IOUtils.copy(fis, out);
            fis.close();
            out.close();
        }
    };
    if (zip) {
        final Disposition disposition = new Disposition(Disposition.TYPE_ATTACHMENT);
        disposition.setFilename(filename_zip);
        result.setDisposition(disposition);
    }

    return result;
}

From source file:com.streamsets.pipeline.lib.jdbc.JdbcUtil.java

public Field resultToField(ResultSetMetaData md, ResultSet rs, int columnIndex, int maxClobSize,
        int maxBlobSize, DataType userSpecifiedType, UnknownTypeAction unknownTypeAction,
        boolean timestampToString) throws SQLException, IOException, StageException {
    Field field;/*  ww  w  .  j  a v  a2 s .  c o m*/
    if (userSpecifiedType != DataType.USE_COLUMN_TYPE) {
        // If user specifies the data type, overwrite the column type returned by database.
        field = Field.create(Field.Type.valueOf(userSpecifiedType.getLabel()), rs.getObject(columnIndex));
    } else {
        // All types as of JDBC 2.0 are here:
        // https://docs.oracle.com/javase/8/docs/api/constant-values.html#java.sql.Types.ARRAY
        // Good source of recommended mappings is here:
        // http://www.cs.mun.ca/java-api-1.5/guide/jdbc/getstart/mapping.html
        switch (md.getColumnType(columnIndex)) {
        case Types.BIGINT:
            field = Field.create(Field.Type.LONG, rs.getObject(columnIndex));
            break;
        case Types.BINARY:
        case Types.LONGVARBINARY:
        case Types.VARBINARY:
            field = Field.create(Field.Type.BYTE_ARRAY, rs.getBytes(columnIndex));
            break;
        case Types.BIT:
        case Types.BOOLEAN:
            field = Field.create(Field.Type.BOOLEAN, rs.getObject(columnIndex));
            break;
        case Types.CHAR:
        case Types.LONGNVARCHAR:
        case Types.LONGVARCHAR:
        case Types.NCHAR:
        case Types.NVARCHAR:
        case Types.VARCHAR:
            field = Field.create(Field.Type.STRING, rs.getObject(columnIndex));
            break;
        case Types.CLOB:
        case Types.NCLOB:
            field = Field.create(Field.Type.STRING, getClobString(rs.getClob(columnIndex), maxClobSize));
            break;
        case Types.BLOB:
            field = Field.create(Field.Type.BYTE_ARRAY, getBlobBytes(rs.getBlob(columnIndex), maxBlobSize));
            break;
        case Types.DATE:
            field = Field.create(Field.Type.DATE, rs.getDate(columnIndex));
            break;
        case Types.DECIMAL:
        case Types.NUMERIC:
            field = Field.create(Field.Type.DECIMAL, rs.getBigDecimal(columnIndex));
            field.setAttribute(HeaderAttributeConstants.ATTR_SCALE,
                    String.valueOf(rs.getMetaData().getScale(columnIndex)));
            field.setAttribute(HeaderAttributeConstants.ATTR_PRECISION,
                    String.valueOf(rs.getMetaData().getPrecision(columnIndex)));
            break;
        case Types.DOUBLE:
            field = Field.create(Field.Type.DOUBLE, rs.getObject(columnIndex));
            break;
        case Types.FLOAT:
        case Types.REAL:
            field = Field.create(Field.Type.FLOAT, rs.getObject(columnIndex));
            break;
        case Types.INTEGER:
            field = Field.create(Field.Type.INTEGER, rs.getObject(columnIndex));
            break;
        case Types.ROWID:
            field = Field.create(Field.Type.STRING, rs.getRowId(columnIndex).toString());
            break;
        case Types.SMALLINT:
        case Types.TINYINT:
            field = Field.create(Field.Type.SHORT, rs.getObject(columnIndex));
            break;
        case Types.TIME:
            field = Field.create(Field.Type.TIME, rs.getObject(columnIndex));
            break;
        case Types.TIMESTAMP:
            final Timestamp timestamp = rs.getTimestamp(columnIndex);
            if (timestampToString) {
                field = Field.create(Field.Type.STRING, timestamp == null ? null : timestamp.toString());
            } else {
                field = Field.create(Field.Type.DATETIME, timestamp);
                if (timestamp != null) {
                    final long actualNanos = timestamp.getNanos() % NANOS_TO_MILLIS_ADJUSTMENT;
                    if (actualNanos > 0) {
                        field.setAttribute(FIELD_ATTRIBUTE_NANOSECONDS, String.valueOf(actualNanos));
                    }
                }
            }
            break;
        // Ugly hack until we can support LocalTime, LocalDate, LocalDateTime, etc.
        case Types.TIME_WITH_TIMEZONE:
            OffsetTime offsetTime = rs.getObject(columnIndex, OffsetTime.class);
            field = Field.create(Field.Type.TIME, Date.from(offsetTime.atDate(LocalDate.MIN).toInstant()));
            break;
        case Types.TIMESTAMP_WITH_TIMEZONE:
            OffsetDateTime offsetDateTime = rs.getObject(columnIndex, OffsetDateTime.class);
            field = Field.create(Field.Type.ZONED_DATETIME, offsetDateTime.toZonedDateTime());
            break;
        //case Types.REF_CURSOR: // JDK8 only
        case Types.SQLXML:
        case Types.STRUCT:
        case Types.ARRAY:
        case Types.DATALINK:
        case Types.DISTINCT:
        case Types.JAVA_OBJECT:
        case Types.NULL:
        case Types.OTHER:
        case Types.REF:
        default:
            if (unknownTypeAction == null) {
                return null;
            }
            switch (unknownTypeAction) {
            case STOP_PIPELINE:
                throw new StageException(JdbcErrors.JDBC_37, md.getColumnType(columnIndex),
                        md.getColumnLabel(columnIndex));
            case CONVERT_TO_STRING:
                Object value = rs.getObject(columnIndex);
                if (value != null) {
                    field = Field.create(Field.Type.STRING, rs.getObject(columnIndex).toString());
                } else {
                    field = Field.create(Field.Type.STRING, null);
                }
                break;
            default:
                throw new IllegalStateException("Unknown action: " + unknownTypeAction);
            }
        }
    }

    return field;
}

From source file:de.iritgo.aktario.jdbc.LoadAllObjects.java

/**
 * Perform the command./*from w  ww  .  j av  a 2  s .  c  om*/
 */
public void perform() {
    if (properties.getProperty("type") == null) {
        Log.logError("persist", "LoadObjects", "The type of the objects to load wasn't specified");

        return;
    }

    final String type = ((String) properties.getProperty("type"));

    final AbstractIObjectFactory factory = (AbstractIObjectFactory) Engine.instance().getIObjectFactory();

    IObject sample = null;

    try {
        sample = factory.newInstance(type);
    } catch (NoSuchIObjectException ignored) {
        Log.logError("persist", "LoadObjects", "Attemting to load objects of unknown type '" + type + "'");

        return;
    }

    if (!DataObject.class.isInstance(sample)) {
        Log.logError("persist", "LoadObjects", "Attemting to load objects that are not persitable");

        return;
    }

    final BaseRegistry registry = Engine.instance().getBaseRegistry();

    JDBCManager jdbcManager = (JDBCManager) Engine.instance().getManager("persist.JDBCManager");
    DataSource dataSource = jdbcManager.getDefaultDataSource();

    try {
        QueryRunner query = new QueryRunner(dataSource);

        ResultSetHandler resultSetHandler = properties.get("resultSetHandle") != null
                ? (ResultSetHandler) properties.get("resultSetHandler")
                : new ResultSetHandler() {
                    public Object handle(ResultSet rs) throws SQLException {
                        ResultSetMetaData meta = rs.getMetaData();

                        int numObjects = 0;

                        while (rs.next()) {
                            try {
                                DataObject object = (DataObject) factory.newInstance(type);

                                object.setUniqueId(rs.getLong("id"));

                                for (Iterator i = object.getAttributes().entrySet().iterator(); i.hasNext();) {
                                    Map.Entry attribute = (Map.Entry) i.next();

                                    if (attribute.getValue() instanceof IObjectList) {
                                        //                               loadList (
                                        //                                  dataSource, object,
                                        //                                  object.getIObjectListAttribute (
                                        //                                     (String) attribute.getKey ()));
                                    } else {
                                        object.setAttribute((String) attribute.getKey(),
                                                rs.getObject((String) attribute.getKey()));
                                    }
                                }

                                registry.add(object);
                                ++numObjects;
                            } catch (NoSuchIObjectException ignored) {
                            }
                        }

                        return new Integer(numObjects);
                    }
                };

        Object numObjects = query.query("select * from " + type, resultSetHandler);

        Log.logVerbose("persist", "LoadObjects",
                "Successfully loaded " + numObjects + " objects of type '" + type + "'");
    } catch (Exception x) {
        Log.logError("persist", "LoadObjects", "Error while loading objects of type '" + type + "': " + x);
    }
}