@Override public Set<EmpVO> getEmpsByDeptno(Integer deptno) { Set<EmpVO> set = new LinkedHashSet<EmpVO>(); EmpVO empVO = null; Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(driver); con = DriverManager.getConnection(url, userid, passwd); pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT); pstmt.setInt(1, deptno); rs = pstmt.executeQuery(); while (rs.next()) { empVO = new EmpVO(); empVO.setEmpno(rs.getInt("empno")); empVO.setEname(rs.getString("ename")); empVO.setJob(rs.getString("job")); empVO.setHiredate(rs.getDate("hiredate")); empVO.setSal(rs.getDouble("sal")); empVO.setComm(rs.getDouble("comm")); empVO.setDeptno(rs.getInt("deptno")); set.add(empVO); // Store the row in the vector } // Handle any driver errors } catch (ClassNotFoundException e) { throw new RuntimeException("Couldn't load database driver. " + e.getMessage()); // Handle any SQL errors } catch (SQLException se) { throw new RuntimeException("A database error occured. " + se.getMessage()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException se) { se.printStackTrace(System.err); } } if (con != null) { try { con.close(); } catch (Exception e) { e.printStackTrace(System.err); } } } return set; }
public static List<PriceBar> getPriceDatas( String sym, java.util.Date beginDT, java.util.Date endDT, int priceMagnifier, int multiplier) { List<PriceBar> priceDatas = new ArrayList<PriceBar>(); try { PreparedStatement datedRangeBySymbol = DBopsMySql.datedRangeBySymbol( sym, new Timestamp(beginDT.getTime()), new Timestamp(endDT.getTime())); ResultSet res = datedRangeBySymbol.executeQuery(); while (res.next()) { PriceBar priceBar = new PriceBar( res.getTimestamp("datetime").getTime(), res.getDouble("open") / priceMagnifier * multiplier, res.getDouble("high") / priceMagnifier * multiplier, res.getDouble("low") / priceMagnifier * multiplier, res.getDouble("close") / priceMagnifier * multiplier, res.getLong("volume")); priceDatas.add(priceBar); } // int i = 1; } catch (SQLException ex) { MsgBox.err2(ex); } catch (Exception ex) { MsgBox.err2(ex); } finally { return priceDatas; } }
public static void refreshLumberLog(LumberLog lumberLog) { Logger logger = Logger.getLogger("dao"); StringBuilder lumberLogData = new StringBuilder("select diameter, metric from lumberlog_diameter where lumberlog_id = ") .append(lumberLog.getId()); Connection con = null; Statement stm = null; ResultSet rs = null; try { con = DataAccess.getInstance().getDatabaseConnection(); con.setAutoCommit(true); stm = con.createStatement(); logger.info(lumberLogData.toString()); rs = stm.executeQuery(lumberLogData.toString()); lumberLog.setMediumRadius(new ArrayList<Double>()); while (rs.next()) { lumberLog.getMediumRadius().add(rs.getDouble(1)); } } catch (Exception e) { logger.warning(e.getMessage()); logger.log(Level.INFO, "Error", e); } finally { if (rs != null) try { rs.close(); } catch (Exception e) { } if (stm != null) try { stm.close(); } catch (Exception e) { } } }
public ValoresAtualizaJob retornaNovosValores(Connection conn, JobLote job) throws SQLException { PreparedStatement stmt = null; ResultSet rs = null; ValoresAtualizaJob obj = null; String sql = " select sum(qtd_transf) qtd_transf, sum(qtd_sucata) qtd_sucata, sum(hr_tot) hr_tot from joblote\n" + " where job = ? and operacao = ? "; stmt = conn.prepareStatement(sql); stmt.setString(1, job.getJob().trim().replace(".", "")); stmt.setInt(2, job.getOperNum()); rs = stmt.executeQuery(); if (rs.next()) { obj = new ValoresAtualizaJob(); obj.setQtdTransf(rs.getDouble("qtd_transf")); obj.setQtdSucata(rs.getDouble("qtd_sucata")); obj.setHoraTotal(rs.getDouble("hr_tot")); } return obj; }
// metoda za pretragu po broju stanovnika public ArrayList<Country> SearchCountryPopulation(long Population) { ArrayList<Country> countries = new ArrayList<Country>(); try { Connection connection = getConnected("world"); PreparedStatement statement = connection.prepareStatement( "SELECT * FROM country WHERE Population <= " + Population + ";"); ResultSet result = statement.executeQuery(); while (result.next()) { countries.add( new Country( result.getString("Code"), result.getString("Name"), result.getString("Continent"), result.getString("Region"), result.getDouble("SurfaceArea"), result.getInt("IndepYear"), result.getLong("Population"), result.getDouble("LifeExpectancy"), result.getDouble("GNP"), result.getDouble("GNPOld"), result.getString("LocalName"), result.getString("GovernmentForm"), result.getString("HeadOfState"), result.getInt("Capital"), result.getString("Code2"))); } connection.close(); } catch (Exception e) { System.out.println(e.toString()); return null; } return countries; }
public static City getCity(int cityIndex) { City city = new City(); city.setMonthsIrradiance(new double[12]); Connection connection = null; String select_sql = "select * from cities natural join states where CityID =?"; PreparedStatement select = null; try { connection = DriverManager.getConnection( "jdbc:google:rdbms://solarcalculator372:solarcalculator/solar"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { select = connection.prepareStatement(select_sql); select.setInt(1, cityIndex); ResultSet rs = select.executeQuery(); while (rs.next()) { city.setCityName(rs.getString("CityName")); city.setZoneRating(rs.getDouble("ZoneRating")); city.setAvgProduePerkw(rs.getDouble("AvgProducePerkw")); city.setFeedInTariff(rs.getDouble("FeedInTariff")); city.setElectricityCost(rs.getDouble("AvgElectricityCost")); city.setPostcode(rs.getInt("Postcode")); city.setOptimalYearDegree(rs.getInt("OptimalYearRound")); city.setBestWinterDegree(rs.getInt("BestWinter")); city.setBestSummerDegree(rs.getInt("BestSummer")); for (int i = 0; i < 12; i++) { city.getMonthsIrradiance()[i] = rs.getDouble(10 + i); } return city; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { if (select != null) { select.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; }
@Test public void twoRowRS() throws SQLException { PreparedStatement prep = conn.prepareStatement("select ? union all select ?;"); prep.setDouble(1, Double.MAX_VALUE); prep.setDouble(2, Double.MIN_VALUE); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(Double.MAX_VALUE, rs.getDouble(1)); assertTrue(rs.next()); assertEquals(Double.MIN_VALUE, rs.getDouble(1)); assertFalse(rs.next()); rs.close(); }
public void executeQuery() throws SQLException { log( "select account, branch, balance, filler from " + m_Driver.getAccountName() + " where account < 101\n", 2); Statement stmt = null; try { stmt = m_conn.createStatement(); ResultSet set = stmt.executeQuery( "select account, branch, balance, filler from " + m_Driver.getAccountName() + " where account < 101"); while (set.next()) { set.getInt(1); set.getInt(2); set.getDouble(3); set.getString(4); } set.close(); } finally { if (stmt != null) stmt.close(); stmt = null; } }
@Test public void batch() throws SQLException { ResultSet rs; stat.executeUpdate("create table test (c1, c2, c3, c4);"); PreparedStatement prep = conn.prepareStatement("insert into test values (?,?,?,?);"); for (int i = 0; i < 10; i++) { prep.setInt(1, Integer.MIN_VALUE + i); prep.setFloat(2, Float.MIN_VALUE + i); prep.setString(3, "Hello " + i); prep.setDouble(4, Double.MAX_VALUE + i); prep.addBatch(); } assertArrayEq(prep.executeBatch(), new int[] {1, 1, 1, 1, 1, 1, 1, 1, 1, 1}); assertEquals(0, prep.executeBatch().length); prep.close(); rs = stat.executeQuery("select * from test;"); for (int i = 0; i < 10; i++) { assertTrue(rs.next()); assertEquals(Integer.MIN_VALUE + i, rs.getInt(1)); assertEquals(Float.MIN_VALUE + i, rs.getFloat(2)); assertEquals("Hello " + i, rs.getString(3)); assertEquals(Double.MAX_VALUE + i, rs.getDouble(4)); } rs.close(); stat.executeUpdate("drop table test;"); }
/** * Transforma os dados obtidos atraves de uma consulta a tabela de fatos do prestador em um objeto * do tipo ResumoFato * * @param rset - um ResultSet contendo o resultado da consulta a tabela de fatos do prestador * @return um objeto do tipo ResumoFato */ private static final ResumoFato montaResumoFato(ResultSet rset) throws SQLException { /* double qtdeNotas; String strCodServico; String strDsServico; double qtdeServico; double valorTotal; strCodServico = rset.getString("pef_codigo"); strDsServico = rset.getString("Descricao"); qtdeNotas = rset.getDouble("QtdeNotas"); qtdeServico = rset.getDouble("QtdeServicos"); valorTotal = rset.getDouble("ValorTotal"); return ( new ResumoFato(qtdeNotas, strCodServico, strDsServico, qtdeServico, valorTotal) );*/ int tipoNota = 0; int qtdeNotas = 0; int tipoRegistro = 0; double valorTotal = 0; tipoNota = rset.getInt("tipo_nota"); tipoRegistro = rset.getInt("tipo_registro"); qtdeNotas = rset.getInt("qtde_notas"); valorTotal = rset.getDouble("valor_total"); return (new ResumoFato(tipoNota, tipoRegistro, qtdeNotas, valorTotal)); } // montaResumoFato()
public JobIniciado retornaUltimoLote(Connection conn, String job, int operacao) throws SQLException { String sql = " select job, operacao, tripulacao,recurso, max(data_fim) \n" + "from joblote where job = ? and operacao = ? \n" + "group by job, operacao, tripulacao, recurso "; PreparedStatement stmt = null; ResultSet rs = null; JobIniciado iniciado = null; stmt = conn.prepareStatement(sql); stmt.setString(1, job.trim().replace(".", "")); stmt.setInt(2, operacao); rs = stmt.executeQuery(); if (rs.next()) { iniciado = new JobIniciado(); iniciado.setJob(rs.getString(1)); iniciado.setOperacao(rs.getInt(2)); iniciado.setTripulacao(rs.getDouble(3)); iniciado.setRecurso(rs.getString(4)); Timestamp data = rs.getTimestamp(5); iniciado.setData(Validacoes.getDataHoraString(data)); } return iniciado; }
@Override public ManagerPanel getById(int userId) throws DbException { PreparedStatement ps = null; ResultSet rs = null; ManagerPanel managerPanel = null; try { managerPanel = new ManagerPanel(); prepareConnection(); ps = connection.prepareStatement(MANAGER_SELECT_BY_ID); ps.setInt(1, userId); rs = ps.executeQuery(); rs.next(); Money finresult = Money.dollars(rs.getDouble("summaryfinresult")); managerPanel.setSummaryFinRes(finresult); } catch (SQLException e) { throw new DbException("Can't execute SQL = '" + MANAGER_SELECT_BY_ID + "'", e); } finally { daoHelper.closeDataBaseEntities(ps, rs, connection); } return managerPanel; }
public static void main(String[] args) { Connection conn = null; try { String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc"; conn = DriverManager.getConnection(connUrl, "sa", "passw0rd"); String qryStmt = "SELECT ename, salary FROM employee"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(qryStmt); while (rs.next()) { System.out.print("name = " + rs.getString("ename") + ", "); System.out.println("salary = " + rs.getDouble("salary")); } } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } // end of main()
@Override public List<MstGasto> read() { Connection cn; PreparedStatement pst; ResultSet rs; String sql; List<MstGasto> lst = new ArrayList(); try { Class.forName(bd.getDriver()); cn = DriverManager.getConnection(bd.getUrl(), bd.getUser(), bd.getPasswd()); sql = "select * from mst_tipo_gastos order by corr_gasto"; pst = cn.prepareStatement(sql); rs = pst.executeQuery(); while (rs.next()) { lst.add( new MstGasto( rs.getInt("cod_residencial"), rs.getInt("corr_gasto"), rs.getString("desc_gasto"), rs.getString("cod_cta_conta"), rs.getDouble("valor_gasto"), rs.getDate("fecha_creacion"), rs.getString("cod_usuario"), rs.getString("activo"))); } rs.close(); pst.close(); cn.close(); } catch (SQLException e) { log.severe(e.toString()); } catch (Exception e) { log.severe(e.toString()); } return lst; }
static void task1() throws FileNotFoundException, IOException, SQLException { // Read Input System.out.println("Task1 Started..."); BufferedReader br = new BufferedReader(new FileReader(inputFile)); br.readLine(); String task1Input = br.readLine(); br.close(); double supportPercent = Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim()); if (supportPercent >= 0) { System.out.println("Task1 Support Percent :" + supportPercent); // Prepare query String task1Sql = "select temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent" + " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i" + " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*" + supportPercent / 100 + " from trans)" + ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent"; PreparedStatement selTask1 = con.prepareStatement(task1Sql); ResultSet rsTask1 = selTask1.executeQuery(); BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1")); while (rsTask1.next()) { bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%"); bw.newLine(); } rsTask1.close(); bw.close(); System.out.println("Task1 Completed...\n"); } else System.out.println("Support percent should be a positive number"); }
public static void getProfile() { String url = "jdbc:mysql://" + var.db_host + "/" + var.db_name; String login = var.db_username; String passwd = var.db_psswd; Connection cn = null; Statement stmt = null; String query = "SELECT * FROM profiles WHERE Name='" + getCurrentProfile() + "'"; try { Class.forName("com.mysql.jdbc.Driver"); cn = DriverManager.getConnection(url, login, passwd); stmt = cn.createStatement(); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { Status.Name = rs.getString("Name"); Status.Sunrise = rs.getTime("Sunrise"); Status.Sunset = rs.getTime("Sunset"); Status.Interval = rs.getTime("Interval"); Status.Working_Time = rs.getTime("Working_Time"); Status.Tank_Capacity = rs.getDouble("Tank_Capacity"); Status.Pump_Flow = rs.getDouble("Pump_Flow"); Status.Watering_Hour = rs.getTime("Watering_Hour"); Status.Water_Amount = rs.getDouble("Water_Amount"); Status.Temperature = rs.getDouble("Temperature"); Status.Humidity = rs.getDouble("Humidity"); Status.Water_Days[0] = rs.getInt("Monday") == 1; Status.Water_Days[1] = rs.getInt("Tuesday") == 1; Status.Water_Days[2] = rs.getInt("Wednesday") == 1; Status.Water_Days[3] = rs.getInt("Thursday") == 1; Status.Water_Days[4] = rs.getInt("Friday") == 1; Status.Water_Days[5] = rs.getInt("Saturday") == 1; Status.Water_Days[6] = rs.getInt("Sunday") == 1; } } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
public Collection<domain.BridgeInfo> findBlock(double height, double length, double width) { connection = getConnection(); Collection<domain.BridgeInfo> brigeInf = new ArrayList<>(); String query = "SELECT OBJECTID, COLLOQUIAL_NAME_1, COLLOQUIAL_NAME_2, COLLOQUIAL_NAME_3, " + "CAST(MIN_CLEARANCE AS FLOAT) AS MIN_CLEARANCE, " + "CAST(OVERALL_LENGTH AS FLOAT) AS OVERALL_LENGTH, " + "CAST(OVERALL_WIDTH AS FLOAT) AS OVERALL_WIDTH, LAT, LONGIT FROM guest.Tbl_bridge_structure_vic " + "WHERE CAST(MIN_CLEARANCE AS FLOAT) > ? " + "AND CAST(OVERALL_LENGTH AS FLOAT) > ? AND CAST(OVERALL_WIDTH AS FLOAT) > ?;"; try { preps = connection.prepareStatement(query); preps.setDouble(1, height); preps.setDouble(2, length); preps.setDouble(3, width); ResultSet rset = preps.executeQuery(); while (rset.next()) { domain.BridgeInfo bridge = new BridgeInfo(); bridge.setObjectId(rset.getString(1)); bridge.setCollName1(rset.getString(2)); bridge.setCollName2(rset.getString(3)); bridge.setCollName3(rset.getString(4)); bridge.setMinClearance(rset.getDouble(5)); bridge.setLength(rset.getDouble(6)); bridge.setWidth(rset.getDouble(7)); bridge.setLat(rset.getDouble(8)); bridge.setLongit(rset.getDouble(9)); brigeInf.add(bridge); } connection.close(); rset.close(); } catch (SQLException ex) { Logger.getLogger(jdbc.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { System.out.println(ex.getMessage()); } System.out.println(brigeInf.size()); return brigeInf; }
public Racquet getRacquet(int racquetId) throws SQLException { start(); rs = stmt.executeQuery("SELECT * FROM racquetInfo WHERE racquetID = '" + racquetId + "'"); Racquet rtrn = null; if (rs.next()) { rtrn = new Racquet(); rtrn.setModelName(rs.getString(2)); rtrn.setBrand(rs.getString(3)); rtrn.setMass(rs.getDouble(4)); rtrn.setLength(rs.getDouble(5)); // rtrn.setSwingWeight(rs.getDouble(6)); rtrn.setBalancePoint(rs.getDouble(7)); rtrn.setQualityIndex(rs.getDouble(8)); } close(); return rtrn; }
/** * Method to build a vector from a ResultSet * * @param <code>ResultSet rsResult</code> the result from a query * @return Vector of objects from the ResultSet * @exception Throws Exception on error */ protected Vector buildObjectVector(ResultSet rsResult) throws Exception { // vector for return data Vector vReturn = null; try { // init the vector vReturn = new Vector(super.VECT_INIT_SIZE, super.VECT_GROW_SIZE); // loop through the entire result set while (rsResult.next()) { // create a new one StageUsacForm objTmp = new StageUsacForm(); // set the attributes // ADDING A NEW FIELD "ROWID" objTmp.ROWID = rsResult.getString("ROWID"); objTmp.HDR_SPIN = rsResult.getLong("HDR_SPIN"); objTmp.SPIN_NM = rsResult.getString("SPIN_NM"); objTmp.RCPNT_EMAIL = rsResult.getString("RCPNT_EMAIL"); objTmp.USAC_EMAIL = rsResult.getString("USAC_EMAIL"); objTmp.RFRNC_NMBR = rsResult.getString("RFRNC_NMBR"); objTmp.RCRD_CNT = rsResult.getLong("RCRD_CNT"); objTmp.TOT_PAYMENT = rsResult.getDouble("TOT_PAYMENT"); objTmp.USAC_PRCS_DAT = rsResult.getDate("USAC_PRCS_DAT"); objTmp.RTRCT_FLAG = rsResult.getString("RTRCT_FLAG"); objTmp.DTL_SPIN = rsResult.getLong("DTL_SPIN"); objTmp.FRN = rsResult.getLong("FRN"); objTmp.SDC_INV_NO = rsResult.getString("SDC_INV_NO"); objTmp.AMT_PAID = rsResult.getDouble("AMT_PAID"); objTmp.DSBRSMNT_TXT = rsResult.getString("DSBRSMNT_TXT"); objTmp.EMAIL_DATE = rsResult.getDate("EMAIL_DATE"); objTmp.FILENAME = rsResult.getString("FILENAME"); objTmp.PROCESS_DATE = rsResult.getDate("PROCESS_DATE"); objTmp.STATUS = rsResult.getLong("STATUS"); // add it to the vector vReturn.addElement(objTmp); } return vReturn; } catch (Exception e) { throw new Exception("BuildObjectVector()\n" + e.getMessage()); } }
protected LumberLog fromResultSet(ResultSet rs) throws SQLException { LumberLog lumberLog = new LumberLog(); lumberLog.setId(rs.getLong("id")); lumberLog.setLength(rs.getDouble("length")); lumberLog.setRealLength(rs.getLong("reallength")); lumberLog.setVolume(rs.getDouble("volume")); lumberLog.setRealVolume(rs.getDouble("realvolume")); lumberLog.setSmallRadius(rs.getDouble("small_diameter")); lumberLog.setBigRadius(rs.getDouble("big_diameter")); lumberLog.setLumberType(rs.getLong("lumbertype")); lumberLog.setLumberClass(rs.getLong("lumberclass")); lumberLog.setCutPlanId(rs.getLong("planId")); LumberStack stack = new LumberStack(); stack.setName(rs.getString("stackName")); stack.setId(rs.getLong("stack")); lumberLog.setStack(stack); IDPlate plate = new IDPlate(); plate.setId(rs.getLong("idplate")); plate.setLabel(rs.getString("plateName")); lumberLog.setPlate(plate); lumberLog.setSupplierId(rs.getLong("SupplierId")); lumberLog.setTransportCertifiateId(rs.getLong("TransportCertificateId")); if (rs.wasNull()) { lumberLog.setTransportCertifiateId(null); } lumberLog.setMarginPercent(rs.getInt("Margin")); lumberLog.setMarginVolume(rs.getDouble("MarginVolume")); lumberLog.setMarginRealVolume(rs.getDouble("RealMarginVolume")); return lumberLog; }
private Producto rsToBean(ResultSet rs) throws SQLException { Producto o = new Producto(); o.setIdprod(rs.getInt("idprod")); o.setIdcat(rs.getInt("idcat")); o.setNombre(rs.getString("nombre")); o.setPrecio(rs.getDouble("precio")); o.setStock(rs.getInt("stock")); return o; }
@SuppressWarnings("unchecked") public static <T> T getValueFromResultSet( int index, final ResultSet resultSet, final Class<T> type) { try { if (java.sql.Date.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new java.sql.Date(time); } else if (Time.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new java.sql.Time(time); } else if (Timestamp.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new java.sql.Timestamp(time); } else if (Date.class.isAssignableFrom(type)) { final long time = resultSet.getLong(index); return (T) new Date(time); } else if (Long.class.isAssignableFrom(type)) { return (T) Long.valueOf(resultSet.getLong(index)); } else if (Integer.class.isAssignableFrom(type)) { return (T) Integer.valueOf(resultSet.getInt(index)); } else if (Short.class.isAssignableFrom(type)) { return (T) Short.valueOf(resultSet.getShort(index)); } else if (Float.class.isAssignableFrom(type)) { return (T) Float.valueOf(resultSet.getFloat(index)); } else if (Double.class.isAssignableFrom(type)) { return (T) Double.valueOf(resultSet.getDouble(index)); } else if (Boolean.class.isAssignableFrom(type)) { return (T) Boolean.valueOf(resultSet.getBoolean(index)); } else if (BigDecimal.class.isAssignableFrom(type)) { return (T) resultSet.getBigDecimal(index); } else if (CharSequence.class.isAssignableFrom(type)) { return (T) resultSet.getString(index); } else if (byte[].class.isAssignableFrom(type)) { return (T) resultSet.getBytes(index); } else { throw new IllegalStateException("Type " + type + " not supported."); } } catch (Exception e) { throw new IllegalStateException( "Unable to read the value from the resultSet. Index:" + index + ", type: " + type, e); } }
protected Map<String, Object> getARow( ResultSet resultSet, boolean convertType, List<String> colNames, Map<String, Integer> fieldNameVsType) { if (resultSet == null) return null; Map<String, Object> result = new HashMap<>(); for (String colName : colNames) { try { if (!convertType) { // Use underlying database's type information except for BigDecimal and BigInteger // which cannot be serialized by JavaBin/XML. See SOLR-6165 Object value = resultSet.getObject(colName); if (value instanceof BigDecimal || value instanceof BigInteger) { result.put(colName, value.toString()); } else { result.put(colName, value); } continue; } Integer type = fieldNameVsType.get(colName); if (type == null) type = Types.VARCHAR; switch (type) { case Types.INTEGER: result.put(colName, resultSet.getInt(colName)); break; case Types.FLOAT: result.put(colName, resultSet.getFloat(colName)); break; case Types.BIGINT: result.put(colName, resultSet.getLong(colName)); break; case Types.DOUBLE: result.put(colName, resultSet.getDouble(colName)); break; case Types.DATE: result.put(colName, resultSet.getTimestamp(colName)); break; case Types.BOOLEAN: result.put(colName, resultSet.getBoolean(colName)); break; case Types.BLOB: result.put(colName, resultSet.getBytes(colName)); break; default: result.put(colName, resultSet.getString(colName)); break; } } catch (SQLException e) { logError("Error reading data ", e); wrapAndThrow(SEVERE, e, "Error reading data from database"); } } return result; }
public void atestHSQLDBConnect() throws SQLException { Statement stmt = conn.createStatement(); String query = "SELECT name, age, payment, bonus, birthDate FROM employee order by age desc"; ResultSet rs = stmt.executeQuery(query); while (rs.next()) { String name = rs.getString("name"); int age = rs.getInt("age"); double payment = rs.getDouble("payment"); double bonus = rs.getDouble("bonus"); Date date = rs.getDate("birthDate"); System.out.println("Name: " + name); System.out.println("Age: " + age); System.out.println("Payment: " + payment); System.out.println("Bonus: " + bonus); System.out.println("BirthDate: " + date); } rs.close(); stmt.close(); }
@Test public void singleRowRS() throws SQLException { PreparedStatement prep = conn.prepareStatement("select ?;"); prep.setInt(1, Integer.MAX_VALUE); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(Integer.MAX_VALUE, rs.getInt(1)); assertEquals(Integer.toString(Integer.MAX_VALUE), rs.getString(1)); assertEquals(new Integer(Integer.MAX_VALUE).doubleValue(), rs.getDouble(1)); assertFalse(rs.next()); rs.close(); prep.close(); }
@Override public Void call() throws Exception { db = new Database(); tweetDAO = db.getTweetDAO(); connect(); try { resultSet = statement.executeQuery("select * from elektro7"); while (resultSet.next()) { Tweet tweet = new Tweet(); tweet.setId(String.valueOf(resultSet.getLong("tweetId"))); tweet.setText(resultSet.getString("text")); tweet.setTimestamp(resultSet.getDate("createdAt")); tweet.setRetweetCount(resultSet.getInt("retweetCount")); tweet.setFavoriteCount(resultSet.getInt("favoriteCount")); tweet.setLanguage(resultSet.getString("tweetLanguage")); tweet.setLatitude(resultSet.getDouble("tweetLatitude")); tweet.setLongitude(resultSet.getDouble("tweetLongitude")); User user = new User(); user.setId(resultSet.getLong("userId")); user.setUserName(resultSet.getString("userName")); user.setScreenName(resultSet.getString("screenName")); user.setLocation(resultSet.getString("userLocation")); user.setLanguage(resultSet.getString("userLanguage")); user.setFollowers(resultSet.getLong("followersCount")); user.setFriends(resultSet.getLong("friendsCount")); tweet.setUser(user); tweetDAO.save(tweet); } } finally { close(); db.close(); } return null; }
public JobProtheus retornaJob(Connection conn, JobLote lote) throws SQLException { ResultSet rs = null; PreparedStatement stmt = null; JobProtheus job = null; String sql = "select job , B1_DESC, operacao , produto, "; sql += " dt_release, job_start_date , qtd_release, setor, qtd_transf "; sql += " from job left join " + DB_PROTHEUS.trim() + ".dbo.SB1000 SB1 "; sql += " on(produto collate SQL_Latin1_General_CP1_CI_AS = B1_COD) "; sql += " where job = ? and operacao = ? and SB1.D_E_L_E_T_ = '' "; sql += " order by dt_release, produto "; stmt = conn.prepareStatement(sql); stmt.setString(1, lote.getJob().trim()); stmt.setInt(2, lote.getOperNum()); rs = stmt.executeQuery(); while (rs.next()) { String nJob = rs.getString("job"); int operacao = rs.getInt("operacao"); String produto = rs.getString("produto"); Date dataEmissao = rs.getDate("dt_release"); Date dataPrevisaoInicio = rs.getDate("job_start_date"); String descricaoProduto = rs.getString("B1_DESC"); double quantidade = rs.getDouble("qtd_release"); String wc = rs.getString("setor"); String emissao = dataEmissao != null ? getDateToString(dataEmissao) : ""; String previsaoInicio = dataPrevisaoInicio != null ? getDateToString(dataPrevisaoInicio) : ""; job = new JobProtheus(); job.setStatus(""); job.setJob(nJob); job.setOperacao(operacao); job.setProduto(produto.trim()); job.setDataEmissao(emissao); job.setQuantidadeLiberada(quantidade); job.setDescricaoProduto(descricaoProduto); job.setCentroTrabalho(wc); double qtdTotal = retornaQtdTotal(conn, job); // calcula o total transferido para o job job.setQuantidadeCompleta(qtdTotal); job.setDataPrivisaoInicio(previsaoInicio); job.setQuantidadeFaltando(quantidade - qtdTotal); // seta o valor qtd faltando } return job; // retorna lista de jobs }
/** static method executes a standard jdbc query */ public static void testStandardJDBC() { Connection con = null; try { Class.forName(driverName).newInstance(); con = DriverManager.getConnection(connURL, username, password); PreparedStatement ps = con.prepareStatement(sqlSelect); ps.setInt(1, 8); ps.setDate(2, Date.valueOf("2003-05-10")); ResultSet rs = ps.executeQuery(); String out = "SQL RESULTS:\n"; while (rs.next()) // still have records... out += rs.getLong("TEST_ID") + " " + rs.getString("NOTES") + " " + rs.getDate("TEST_DT") + " " + rs.getDouble("AMOUNT") + " " + rs.getString("CODE") + "\n"; System.out.println(out); } catch (SQLException sqle) { sqle.printStackTrace(); // was it a data integrity violation? switch (sqle.getErrorCode()) // ORACLE SPECIFIC CODES { case 1: case 1407: case 1722: applyDataIntegrityViolationRecovery(); } } catch (ClassNotFoundException cnfe) { cnfe.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (con != null) con.close(); } catch (SQLException sqle) { sqle.printStackTrace(); } } }
public ArrayList<Aulas> importarDatos() throws ClassNotFoundException, SQLException { ArrayList<Aulas> listAulas = new ArrayList<>(); ResultSet rs = consultarDatos(); while (rs.next()) { Aulas aula = new Aulas(); aula.setId(rs.getInt(1)); aula.setEdificio(rs.getString(2)); aula.setPiso(rs.getString(3)); aula.setCoordenadaX(rs.getDouble(4)); aula.setCoordenadaY(rs.getDouble(5)); aula.setContenido(rs.getString(6)); listAulas.add(aula); } return listAulas; }
public Object get(ResultSet resultSet, int column) throws SQLException { switch (this) { case OBJECT: return resultSet.getObject(column + 1); case STRING: return resultSet.getString(column + 1); case INT: return resultSet.getInt(column + 1); case LONG: return resultSet.getLong(column + 1); case DOUBLE: return resultSet.getDouble(column + 1); default: throw Util.unexpected(this); } }