@SuppressWarnings("unchecked") public static final List<Record> build(Config config, ResultSet rs) throws SQLException { List<Record> result = new ArrayList<Record>(); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); String[] labelNames = new String[columnCount + 1]; int[] types = new int[columnCount + 1]; buildLabelNamesAndTypes(rsmd, labelNames, types); while (rs.next()) { Record record = new Record(); record.setColumnsMap(config.containerFactory.getColumnsMap()); Map<String, Object> columns = record.getColumns(); for (int i = 1; i <= columnCount; i++) { Object value; if (types[i] < Types.BLOB) value = rs.getObject(i); else if (types[i] == Types.CLOB) value = ModelBuilder.handleClob(rs.getClob(i)); else if (types[i] == Types.NCLOB) value = ModelBuilder.handleClob(rs.getNClob(i)); else if (types[i] == Types.BLOB) value = ModelBuilder.handleBlob(rs.getBlob(i)); else value = rs.getObject(i); columns.put(labelNames[i], value); } result.add(record); } return result; }
/** * INTERNAL: Get a timestamp value from a result set. Overrides the default behavior to * specifically return a timestamp. Added to overcome an issue with the oracle 9.0.1.4 JDBC * driver. */ public Object getObjectFromResultSet( ResultSet resultSet, int columnNumber, int type, AbstractSession session) throws java.sql.SQLException { // Bug#3381652 10G Drivers return sql.Date instead of timestamp on DATE field if ((type == Types.TIMESTAMP) || (type == Types.DATE)) { return resultSet.getTimestamp(columnNumber); } else if (type == OracleTypes.TIMESTAMPTZ) { TIMESTAMPTZ tsTZ = (TIMESTAMPTZ) resultSet.getObject(columnNumber); // Need to call timestampValue once here with the connection to avoid null point // exception later when timestampValue is called in converObject() if ((tsTZ != null) && (tsTZ.getLength() != 0)) { Connection connection = getConnection(session, resultSet.getStatement().getConnection()); // Bug#4364359 Add a wrapper to overcome TIMESTAMPTZ not serializable as of jdbc 9.2.0.5 // and 10.1.0.2. // It has been fixed in the next version for both streams return new TIMESTAMPTZWrapper(tsTZ, connection, isTimestampInGmt(connection)); } return null; } else if (type == OracleTypes.TIMESTAMPLTZ) { // TIMESTAMPLTZ needs to be converted to Timestamp here because it requires the connection. // However the java object is not know here. The solution is to store Timestamp and the // session timezone in a wrapper class, which will be used later in converObject(). TIMESTAMPLTZ tsLTZ = (TIMESTAMPLTZ) resultSet.getObject(columnNumber); if ((tsLTZ != null) && (tsLTZ.getLength() != 0)) { Timestamp ts = TIMESTAMPLTZ.toTimestamp( getConnection(session, resultSet.getStatement().getConnection()), tsLTZ.toBytes()); // Bug#4364359 Add a separate wrapper for TIMESTAMPLTZ. return new TIMESTAMPLTZWrapper( ts, ((OracleConnection) getConnection(session, resultSet.getStatement().getConnection())) .getSessionTimeZone()); } return null; } else if (type == OracleTypes.OPAQUE) { try { Object result = resultSet.getObject(columnNumber); if (!(result instanceof OPAQUE)) { // Report Queries can cause result to not be an instance of OPAQUE. return result; } return getXMLTypeFactory().getString((OPAQUE) result); } catch (SQLException ex) { throw DatabaseException.sqlException(ex, null, session, false); } } else { Object value = super.getObjectFromResultSet(resultSet, columnNumber, type, session); if (type == OracleTypes_NCLOB) { value = convertObject(value, ClassConstants.STRING); } return value; } }
public static String getPassword(String email) throws SQLException { String Password = ""; Connection l_conn = null; PreparedStatement l_prepStat = null; try { l_conn = createConnection(); l_prepStat = l_conn.prepareStatement( "SELECT password FROM app_user WHERE email_id=? and isfbaccount='N';"); // Constants.logger.info("l_callStat::{}"+l_prepStat); l_prepStat.setString(1, email); Constants.logger.info("l_callStat ::{}" + l_prepStat); ResultSet l_rs = l_prepStat.executeQuery(); ResultSetMetaData l_rsmd = l_rs.getMetaData(); int l_noColumns = l_rsmd.getColumnCount(); while (l_rs.next()) { for (int col = 1; col <= l_noColumns; col++) { Password = "" + l_rs.getObject("password"); l_rs.getObject("password"); } } l_rs.close(); l_prepStat.close(); l_conn.close(); } catch (SQLException e) { l_prepStat.close(); l_conn.close(); Constants.logger.error("Error : " + e.getMessage()); } return Password; }
protected final void map(final ResultSet rs, final T entity) throws SQLException { entity.setOsId(rs.getInt("os")); entity.setDescription(rs.getString("description")); entity.setComment(rs.getString("free_text_comment")); entity.setCreationDate(DbFacadeUtils.fromDate(rs.getTimestamp("creation_date"))); entity.setNumOfSockets(rs.getInt("num_of_sockets")); entity.setCpuPerSocket(rs.getInt("cpu_per_socket")); entity.setTimeZone(rs.getString("time_zone")); entity.setVmType(VmType.forValue(rs.getInt("vm_type"))); entity.setUsbPolicy(UsbPolicy.forValue(rs.getInt("usb_policy"))); entity.setFailBack(rs.getBoolean("fail_back")); entity.setDefaultBootSequence(BootSequence.forValue(rs.getInt("default_boot_sequence"))); entity.setNiceLevel(rs.getInt("nice_level")); entity.setCpuShares(rs.getInt("cpu_shares")); entity.setPriority(rs.getInt("priority")); entity.setAutoStartup(rs.getBoolean("auto_startup")); entity.setStateless(rs.getBoolean("is_stateless")); entity.setDbGeneration(rs.getLong("db_generation")); entity.setIsoPath(rs.getString("iso_path")); entity.setOrigin(OriginType.forValue(rs.getInt("origin"))); entity.setKernelUrl(rs.getString("kernel_url")); entity.setKernelParams(rs.getString("kernel_params")); entity.setInitrdUrl(rs.getString("initrd_url")); entity.setSmartcardEnabled(rs.getBoolean("is_smartcard_enabled")); entity.setDeleteProtected(rs.getBoolean("is_delete_protected")); entity.setSsoMethod(SsoMethod.fromString(rs.getString("sso_method"))); entity.setTunnelMigration((Boolean) rs.getObject("tunnel_migration")); entity.setVncKeyboardLayout(rs.getString("vnc_keyboard_layout")); entity.setRunAndPause(rs.getBoolean("is_run_and_pause")); entity.setCreatedByUserId(Guid.createGuidFromString(rs.getString("created_by_user_id"))); entity.setMigrationDowntime((Integer) rs.getObject("migration_downtime")); entity.setSerialNumberPolicy( SerialNumberPolicy.forValue((Integer) rs.getObject("serial_number_policy"))); entity.setCustomSerialNumber(rs.getString("custom_serial_number")); }
private void producto() { DefaultTableModel modelo = new DefaultTableModel(); try { Statement estatuto = conex.getCn().createStatement(); ResultSet resultado = estatuto.executeQuery( "SELECT idProducto Codigo, Producto, Precio, IF(Servicio = true, 'Servicio', Cantidad) Cantidad FROM producto order by Producto asc"); String[] Titulos = {"Codigo", "Nombre", "Precio", "Cantidad"}; modelo.setColumnIdentifiers(Titulos); while (resultado.next()) { Object[] fila = { resultado.getObject("Codigo"), resultado.getObject("Producto"), resultado.getObject("Precio"), resultado.getObject("Cantidad") }; modelo.addRow(fila); } TableProducto.setModel(modelo); } catch (SQLException e) { setTitle(e.toString()); JOptionPane.showMessageDialog( null, e.toString(), "Student Control", JOptionPane.INFORMATION_MESSAGE); } }
/** ensures two tables (or views, that is) have the same content */ private void ensureEqualContent(String tableNameLHS, String tableNameRHS) throws SQLException { ResultSet lhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameLHS + "\""); ResultSet rhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameRHS + "\""); ResultSetMetaData meta = lhs.getMetaData(); while (lhs.next() && rhs.next()) { for (int col = 1; col <= meta.getColumnCount(); ++col) { assertEquals( "table content does not match: cp. " + tableNameLHS + "-" + tableNameRHS + ", row " + lhs.getRow() + ", col " + col, lhs.getObject(col), rhs.getObject(col)); } } // lhs should be after last, rhs still on last assertTrue( "row count does not match: " + tableNameLHS + "-" + tableNameRHS, lhs.isAfterLast() && rhs.isLast()); }
/** 封装结果. */ private Object getResult(ResultSet resultSet, String resultType) throws SQLException { if ("simple".equals(resultType)) { // 单列单行 return resultSet.next() ? resultSet.getObject(1) : null; } else if ("simples".equals(resultType)) { // 单列多行 List<Object> rows = new ArrayList<>(); while (resultSet.next()) { rows.add(resultSet.getObject(1)); } return rows; } else if ("object".equals(resultType)) { // 多列单行 ResultSetMetaData metaData = resultSet.getMetaData(); Map<String, Object> row = null; if (resultSet.next()) { row = new HashMap<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String name = metaData.getColumnLabel(i); row.put(name, resultSet.getObject(name)); } } return row; } else { // 多列多行 ResultSetMetaData metaData = resultSet.getMetaData(); List<Map<String, Object>> rows = new ArrayList<>(); // 封装结果 while (resultSet.next()) { Map<String, Object> row = new HashMap<>(); for (int i = 1; i <= metaData.getColumnCount(); i++) { String name = metaData.getColumnLabel(i); // 别名,getColumnName-列名 row.put(name, resultSet.getObject(name)); } rows.add(row); } return rows; } }
public ClientDetails mapRow(ResultSet rs, int rowNum) throws SQLException { BaseClientDetails details = new BaseClientDetails( rs.getString(1), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(7), rs.getString(6)); details.setClientSecret(rs.getString(2)); if (rs.getObject(8) != null) { details.setAccessTokenValiditySeconds(rs.getInt(8)); } if (rs.getObject(9) != null) { details.setRefreshTokenValiditySeconds(rs.getInt(9)); } String json = rs.getString(10); if (json != null) { try { @SuppressWarnings("unchecked") Map<String, Object> additionalInformation = mapper.read(json, Map.class); details.setAdditionalInformation(additionalInformation); } catch (Exception e) { logger.warn("Could not decode JSON for additional information: " + details, e); } } String scopes = rs.getString(11); if (scopes != null) { details.setAutoApproveScopes(StringUtils.commaDelimitedListToSet(scopes)); } return details; }
/** * Find job in table 'jobs'. Get values of all fields in searched record. Create object of 'Job' * class by these values. * * @param jobId - id number of need job. * @return object of Job class. */ protected Job createJobById(int jobId) { ResultSet resultSet = null; try { resultSet = find("jobs", Job.JOB_ID, jobId); // find record by id resultSet.first(); // Getting values of fields. int id = (int) resultSet.getObject(Job.JOB_ID); String name = String.valueOf(resultSet.getObject(Job.JOB_NAME)); String description = String.valueOf(resultSet.getObject(Job.DESCRIPTION)); int percents = (int) resultSet.getObject(Job.PERCENTS); Date beginDate = (Date) resultSet.getObject(Job.BEGIN_DATE); Date endDate = (Date) resultSet.getObject(Job.END_DATE); Status status = createStatusById((int) resultSet.getObject(Job.STATUS_ID)); Priority priority = createPriorityById((int) resultSet.getObject(Job.PRIORITY_ID)); User worker = createUserById((int) resultSet.getObject(Job.WORKER_ID)); User manager = createUserById((int) resultSet.getObject(Job.MANAGER_ID)); // Create object of Job class by values. return new Job( id, name, description, percents, beginDate, endDate, status, priority, worker, manager); } catch (SQLException exception) { logger.getExceptionTextFileLogger().error(exception); return null; } }
private void testSetObject(Connection conn) throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST(C CHAR(1))"); PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)"); prep.setObject(1, 'x'); prep.execute(); stat.execute("DROP TABLE TEST"); stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)"); prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)"); prep.setInt(1, 1); prep.setObject(2, 11); prep.setObject(3, null); prep.execute(); prep.setInt(1, 2); prep.setObject(2, 101, Types.OTHER); prep.setObject(3, 103, Types.OTHER); prep.execute(); PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID"); ResultSet rs = p2.executeQuery(); rs.next(); Object o = rs.getObject(2); assertTrue(o instanceof byte[]); assertTrue(rs.getObject(3) == null); rs.next(); o = rs.getObject(2); assertTrue(o instanceof byte[]); o = rs.getObject(3); assertTrue(o instanceof Integer); assertEquals(103, ((Integer) o).intValue()); assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); }
/** * Submits query via JDBC * * @param query SQL query string * @param statement sql statement to execute the query with * @param outputFilename name of file result set is to be written to * @param timeout time allowed for query execution * @throws Exception */ public void submitQueryJDBC( String query, Statement statement, String outputFilename, long timeout) throws Exception { BufferedWriter writer = null; if (outputFilename != null) { writer = new BufferedWriter(new FileWriter(new File(outputFilename))); } ResultSet resultSet = null; try { RunThread runThread = new RunThread(statement, query); processThread(runThread, timeout); resultSet = runThread.getResultSet(); if (resultSet == null) { throw runThread.getException(); } if (outputFilename == null) { return; } int columnCount = resultSet.getMetaData().getColumnCount(); columnLabels = new ArrayList<String>(); for (int i = 1; i <= columnCount; i++) { columnLabels.add(resultSet.getMetaData().getColumnLabel(i)); } Object[] types = new Object[columnCount]; for (int i = 1; i <= columnCount; i++) { types[i - 1] = resultSet.getMetaData().getColumnType(i); } ColumnList.setTypes(types); LOG.debug("Result set data types:"); LOG.debug(Utils.getTypesInStrings(ColumnList.getTypes())); while (resultSet.next()) { Object[] values = new Object[columnCount]; for (int i = 1; i <= columnCount; i++) { try { if (resultSet.getObject(i) == null) { values[i - 1] = null; continue; } values[i - 1] = new String(resultSet.getBytes(i)); } catch (Exception e) { if (resultSet.getMetaData().getColumnType(i) == Types.DATE) { values[i - 1] = resultSet.getDate(i); } else { values[i - 1] = resultSet.getObject(i); } } } ColumnList columnList = new ColumnList(values); if (writer != null) { writer.write(columnList + "\n"); } } if (writer != null) { writer.close(); } } finally { if (resultSet != null) { resultSet.close(); } } }
@Override public Vector<Object> getAllStudentInfo(String dmid) throws QueryResultIsNullException, DataBaseException { Statement stmt = DB.CreateStatement(); String sql = "select * from View_Student_College_StuDormRoom where DormId = '" + dmid + "'"; // String sql = "select * from View_Student_College " ; // System.out.println(sql); try { ResultSet rs = stmt.executeQuery(sql); if (rs != null && !rs.next()) { throw new QueryResultIsNullException(); } Vector<Object> vectors = new Vector<Object>(); Vector<Object> vector = new Vector<Object>(); for (int i = 1; i <= 9; i++) { vector.add(rs.getObject(i)); } vectors.add(vector); while (rs.next()) { vector = new Vector<Object>(); for (int i = 1; i <= 9; i++) { vector.add(rs.getObject(i)); } vectors.add(vector); } return vectors; } catch (SQLException e) { e.printStackTrace(); throw new DataBaseException(); } }
public java.lang.Object[] getListofActivities() { java.lang.Object[] listofActivities = null; java.util.Vector listActVector = new java.util.Vector(1, 1); try { // java.sql.Connection connDB = // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner"); java.sql.Statement stmt1 = connectDB.createStatement(); java.sql.PreparedStatement pSet1 = connectDB.prepareStatement( "SELECT DISTINCT code,activity FROM pb_activity where activity_category ilike 'I%' order by code"); java.sql.ResultSet rSet1 = pSet1.executeQuery(); while (rSet1.next()) { System.out.println(rSet1.getObject(1).toString()); listActVector.addElement(rSet1.getObject(1).toString()); } // System.out.println(rSet1.getObject(1).toString()); } catch (java.sql.SQLException sqlExec) { javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage()); } listofActivities = listActVector.toArray(); System.out.println("Done list of activities ..."); return listofActivities; }
public LinkedList<Trigger> listaTrigger(String nombreT, Connection con) throws SQLException { Statement s = con.createStatement(); LinkedList<Trigger> lT = new LinkedList(); LinkedList<String> nombTrigger = nombreTrigger(nombreT, s); ResultSet rs; String disparador = null; for (int i = 0; i < nombTrigger.size(); i++) { LinkedList<String> condTrigger = new LinkedList(); rs = s.executeQuery( "SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='" + nombTrigger.get(i) + "' order by event_manipulation"); int u = 0; while (rs.next()) { u++; } rs = s.executeQuery( "SELECT event_manipulation,action_timing FROM information_schema.triggers where trigger_name ='" + nombTrigger.get(i) + "' order by event_manipulation"); while (rs.next()) { disparador = rs.getObject("action_timing").toString(); condTrigger.add(rs.getObject("event_manipulation").toString()); // System.out.println("COND "+nombTrigger.get(i)+" - // "+rs.getObject("event_manipulation").toString()); } lT.add(new Trigger(nombTrigger.get(i), condTrigger, disparador)); } s.close(); return lT; }
// 测试Oracle连接是否成功 public static void main(String[] args) throws SQLException { Connection connection = new OracleUtils().getConnection(); System.out.println("连接成功:" + connection); Statement statement = connection.createStatement(); // DOP线为分离 // ResultSet rs = // statement.executeQuery("select GW as 岗位,'2015-10-20' as 日期,round(sum(BQUA),0) as 产量 , // sum(case when Crtdate between to_date('2015-10-20 07:15:00','yyyy-mm-dd hh24:mi:ss') and // to_date('2015-10-20 15:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 早班 , sum(case // when Crtdate between to_date('2015-10-20 15:15:00','yyyy-mm-dd hh24:mi:ss') and // to_date('2015-10-20 23:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 中班 , sum(case // when Crtdate between to_date('2016-10-20 23:15:00','yyyy-mm-dd hh24:mi:ss') and // to_date('2015-10-21 07:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 晚班 from // baandb.ttiwcn108220 e,baandb.ttiwcn109220 f where e.BOXID=f.BOXID and ( f.DES1='封箱') and // f.CrtDate between to_date('2015-10-20 07:15:00','yyyy-mm-dd hh24:mi:ss') and // to_date('2015-10-21 07:15:00','yyyy-mm-dd hh24:mi:ss') group by GW"); // DOP线已分离 ResultSet rs = statement.executeQuery( "select (case when GW='DOP' then (case when CrtAcc in ('SCDOP1','SCDOP2','SCDOP3') then 'D15' else 'D10' end) else GW end) as 岗位,'2015-11-05' as 日期,round(sum(BQUA),0) as 产量 ,sum(case when Crtdate between to_date('2015-11-05 07:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-05 15:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 早班 ,sum(case when Crtdate between to_date('2015-11-05 15:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-05 23:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 中班 ,sum(case when Crtdate between to_date('2015-11-05 23:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-06 07:14:59','yyyy-mm-dd hh24:mi:ss') then BQUA else 0 end) as 晚班 from baandb.ttiwcn108220 e,baandb.ttiwcn109220 f where e.BOXID=f.BOXID and ( f.DES1='封箱') and f.CrtDate between to_date('2015-11-05 07:15:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-11-06 07:15:00','yyyy-mm-dd hh24:mi:ss') group by (case when GW='DOP' then (case when CrtAcc in ('SCDOP1','SCDOP2','SCDOP3') then 'D15' else 'D10' end) else GW end)"); while (rs.next()) { System.out.println("岗位:" + rs.getObject(1)); System.out.println("日期" + rs.getObject(2)); System.out.println("产量" + rs.getObject(3)); System.out.println(rs.getObject(4)); System.out.println(rs.getObject(5)); System.out.println(rs.getObject(6)); System.out.println("---------------"); } }
private Table getHouseholdFactBaseCubeTable() throws SQLException { Table householdFactBaseCubeTable = new Table(); householdFactBaseCubeTable.setSizeFull(); householdFactBaseCubeTable.setImmediate(true); householdFactBaseCubeTable.setColumnCollapsingAllowed(true); householdFactBaseCubeTable.addContainerProperty("ACCOUNT CITY", String.class, null); householdFactBaseCubeTable.addContainerProperty("BRANCH CITY", String.class, null); householdFactBaseCubeTable.addContainerProperty("FISCAL QUARTER", String.class, null); householdFactBaseCubeTable.addContainerProperty("STATUS REASON", String.class, null); householdFactBaseCubeTable.addContainerProperty("PRIMARY BALANCE", String.class, null); householdFactBaseCubeTable.addContainerProperty("TRANSACTION COUNT", String.class, null); householdFactBaseCubeTable.addContainerProperty("ACCOUNT COUNT", String.class, null); DecimalFormat formatDecimal = new DecimalFormat("#.##"); ResultSet resultSet = bankService.viewHouseholdBaseCube(); int i = 1; while (resultSet.next()) { householdFactBaseCubeTable.addItem( new Object[] { resultSet.getObject(1).toString(), resultSet.getObject(2).toString(), resultSet.getObject(3).toString(), resultSet.getObject(4).toString(), formatDecimal.format((Double) resultSet.getObject(5)).toString(), resultSet.getObject(6).toString().replace(".0", ""), resultSet.getObject(7).toString().replace(".0", "") }, new Integer(i)); i++; } return householdFactBaseCubeTable; }
public Object[][] getAllUnitInfo() { PreparedStatement getrelqty; ResultSet getResultSet; // float Qty = 0; Object[] UnitName; String strgetUnitRelativeQuantity = "select unit_id,unit_name,unit_relative_quantity from item_unit "; ArrayList<Object[]> data = new ArrayList<Object[]>(); // DBConnect getUnit = new DBConnect(); try { initConnection(); getrelqty = conn.prepareStatement(strgetUnitRelativeQuantity); // getrelqty.setInt(1, UnitId); getResultSet = getrelqty.executeQuery(); while (getResultSet.next()) { Object st[] = new Object[] { getResultSet.getObject("unit_id"), getResultSet.getObject("unit_name"), getResultSet.getObject("unit_relative_quantity") }; data.add(st); } } catch (SQLException e) { JOptionPane.showMessageDialog(null, e + "form getUnitINfo" + getClass().getName()); } finally { closeConnection(); } return data.toArray(new Object[data.size()][]); }
private void testUpdateLob() throws SQLException { Connection conn = getConnection(); Statement stat = conn.createStatement(); stat.execute( "CREATE TABLE object_index " + "(id integer primary key, object other, number integer)"); PreparedStatement prep = conn.prepareStatement("INSERT INTO object_index (id,object) VALUES (1,?)"); prep.setObject(1, "hello", Types.JAVA_OBJECT); prep.execute(); ResultSet rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1"); rs.next(); assertEquals("hello", rs.getObject(1).toString()); stat = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1"); rs.next(); assertEquals("hello", rs.getObject(1).toString()); rs.updateInt(2, 1); rs.updateRow(); rs.close(); stat = conn.createStatement(); rs = stat.executeQuery("SELECT object,id,number FROM object_index WHERE id =1"); rs.next(); assertEquals("hello", rs.getObject(1).toString()); conn.close(); }
public void checkSessionLogout() throws Exception { uti.writeAsteriskLog("- SYSTE - Check DateTime Agent unLogout"); String date = uti.getDate(); String sql = "SELECT * FROM login_action WHERE CAST(datetime_login AS DATE) >= '" + date + "'"; ResultSet rs = sqlQuery(sql); String datenow = uti.getDatetime(); while (rs.next()) { String datelogout = String.valueOf(rs.getObject("datetime_logout")); if (datelogout.equalsIgnoreCase("null")) { String agentid = String.valueOf(rs.getObject("agent_id")); String iface = String.valueOf(rs.getObject("interface")); String queue = String.valueOf(rs.getObject("queue")); String session = rs.getString("session"); updateStatus(agentid, "NULL", "NULL"); logoutAction(session, agentid); uti.writeAsteriskLog( "- SYSTE - Update datetime agent unlogout\t" + agentid + "\t" + session); System.out.println("update success logout\t" + session); // sql = "UPDATE login_action SET datetime_logout ='"+datenow+"'" // + " WHERE session = '"+session+"'" ; // int rs2 = sqlExecute(sql); // if(rs2 != 0) // uti.writeAsteriskLog("- SYSTE - Update datetime agent // unlogout\t"+agentid+"\t"+session); // System.out.println("update success\t"+session); // String sql2 = "UPDATE agent_status SET interface =null,queue=null // WHERE agent_id = '"+agentid+"'" ; // rs2 = sqlExecute(sql2); } } }
/** * puts the value for the column aginst the column Name * * @param os * @param rows * @param oldResultSet * @param index * @param modifiedColumns * @param columnName * @throws SQLException * @throws IOException */ public void writeUpdate( Writer os, ResultSet rows, ResultSet oldResultSet, int index, HashMap modifiedColumns, String columnName, ArrayList encodedCols) throws SQLException, IOException { Object newObject = rows.getObject(index); Object oldObject = oldResultSet.getObject(index); if (newObject == null) { write(os, "NULL", encodedCols, columnName); if (oldObject != null) { modifiedColumns.put(columnName, "NULL"); } } else { write(os, newObject, encodedCols, columnName); if (oldObject != null) { if (!(newObject.equals(oldObject))) { modifiedColumns.put(columnName, newObject); } } else { modifiedColumns.put(columnName, newObject); } } }
public String dlmResults(String sqlquery, boolean headers, String dlm) throws SQLException { stmt = conn.createStatement(); ResultSet tempRs = stmt.executeQuery(sqlquery); ResultSetMetaData metaData = tempRs.getMetaData(); int columnCount = metaData.getColumnCount(); String TempStr = ""; if (headers) { for (int i = 1; i <= columnCount; ++i) { if (i > 1) { TempStr += dlm + metaData.getColumnName(i); } else { TempStr += metaData.getColumnName(i); } } } while (tempRs.next()) { for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) { if (columnIndex > 1) { TempStr += dlm + tempRs.getObject(columnIndex); } else { TempStr += "\n" + tempRs.getObject(columnIndex); } } } tempRs.close(); return TempStr; }
@Override public Attachment map(int i, ResultSet resultSet, StatementContext statementContext) throws SQLException { Attachment attachment = new Attachment(); attachment.setId((UUID) resultSet.getObject("id")); attachment.setTitle(resultSet.getString("title")); attachment.setDescription(resultSet.getString("description")); attachment.setSlug(resultSet.getString("slug")); attachment.setData(resultSet.getBinaryStream("data")); attachment.setExtension(resultSet.getString("extension")); attachment.setParentId((UUID) resultSet.getObject("parent_id")); if (MapperUtils.hasColumn("localization_data", resultSet) && !Strings.isNullOrEmpty(resultSet.getString("localization_data"))) { ObjectMapper mapper = new ObjectMapper(); try { Map<Locale, Map<String, Object>> localizedVersions = Maps.newHashMap(); Map[] data = mapper.readValue(resultSet.getString("localization_data"), Map[].class); for (Map map : data) { localizedVersions.put( LocaleUtils.toLocale((String) map.get("locale")), (Map) map.get("entity")); } attachment.setLocalizedVersions(localizedVersions); } catch (IOException e) { throw new SQLException("Failed to de-serialize localization JSON data", e); } } return attachment; }
public static String getRate(String voter, String ans) { DBConnector dBConnector = new DBConnector(); Connection dbConnection = dBConnector.getConnection(); String selectTableSQL = "select * from RATING where idvoter= ? and idanswer=?"; String selectTableSQL2 = "select * from user where username= ? "; PreparedStatement statement; try { statement = dbConnection.prepareStatement(selectTableSQL2); statement.setString(1, voter); ResultSet rs = statement.executeQuery(); while (rs.next()) { voter = rs.getObject(1) + ""; } statement.close(); statement = dbConnection.prepareStatement(selectTableSQL); statement.setString(1, voter); statement.setString(2, ans); rs = statement.executeQuery(); int count = 0; while (rs.next()) { count++; return rs.getObject(4) + ""; } statement.close(); } catch (SQLException ex) { Logger.getLogger(Rating.class.getName()).log(Level.SEVERE, null, ex); } return "gagal"; }
/** private method which actually will do all of our work for the sample */ private void executeSample() { String query = "select anEmployee from staff2"; try { Statement stmt = _con.createStatement(); ; // Execute the query which will return an Employee object // We will cast this using the Person interface. Note the // Person interface class MUST be in your CLASSPATH. You // Do not need Employee in your CLASSPATH. ResultSet rs = stmt.executeQuery(query); output("***Using interface class\n"); while (rs.next()) { Person aPerson = (Person) rs.getObject(1); displayMethods(aPerson.getClass()); output( "The person is: " + aPerson.toString() + "\nFirst Name= " + aPerson.getFirstName() + "\nLast Name= " + aPerson.getLastName() + "\n"); } // Now execute the same query, but this time we will use // reflection to access the class. Again, only the interface // Person is required in the CLASSPATH rs = stmt.executeQuery(query); output("***Using reflection\n"); Object theObj = null; while (rs.next()) { theObj = rs.getObject(1); output("The person is: " + theObj.toString() + "\n"); Class theClass = theObj.getClass(); displayMethods(theClass); Method m1 = theClass.getMethod("toString", new Class[0]); Method m2 = theClass.getMethod("getFirstName", new Class[0]); Method m3 = theClass.getMethod("getLastName", new Class[0]); output( "The person is: " + (Object) m1.invoke(theObj, new Object[0]) + "\nFirst Name= " + (Object) m2.invoke(theObj, new Object[0]) + "\nLast Name= " + (Object) m3.invoke(theObj, new Object[0]) + "\n"); } rs.close(); stmt.close(); } catch (SQLException sqe) { displaySQLEx(sqe); } catch (Exception e) { error("Unexpected exception : " + e.toString() + "\n"); e.printStackTrace(); } }
@Override public Quota mapRow(ResultSet rs, int rowNum) throws SQLException { QuotaE quota = new QuotaE(); quota.setQuotaId((UUID) rs.getObject(1)); quota.setClusterId((UUID) rs.getObject(2)); quota.setProjectId((UUID) rs.getObject(3)); return quota; }
public Account mapRow(ResultSet rs, int rowNum) throws SQLException { Account account = new Account(); account.setId(rs.getInt("id")); account.setCurrency(rs.getObject("currency", Currency.class)); account.setBalance(rs.getDouble("balance")); account.setAccountType(rs.getObject("accountType", AccountType.class)); return account; }
private void testArray() throws SQLException { trace("Test ARRAY"); ResultSet rs; stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, VALUE ARRAY)"); PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)"); prep.setInt(1, 1); prep.setObject(2, new Object[] {new Integer(1), new Integer(2)}); prep.execute(); prep.setInt(1, 2); prep.setObject(2, new Object[] {11, 12}); prep.execute(); prep.close(); rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID"); rs.next(); assertEquals(1, rs.getInt(1)); Object[] list = (Object[]) rs.getObject(2); assertEquals(1, ((Integer) list[0]).intValue()); assertEquals(2, ((Integer) list[1]).intValue()); Array array = rs.getArray(2); Object[] list2 = (Object[]) array.getArray(); assertEquals(1, ((Integer) list2[0]).intValue()); assertEquals(2, ((Integer) list2[1]).intValue()); list2 = (Object[]) array.getArray(2, 1); assertEquals(2, ((Integer) list2[0]).intValue()); rs.next(); assertEquals(2, rs.getInt(1)); list = (Object[]) rs.getObject(2); assertEquals(11, ((Integer) list[0]).intValue()); assertEquals(12, ((Integer) list[1]).intValue()); array = rs.getArray("VALUE"); list2 = (Object[]) array.getArray(); assertEquals(11, ((Integer) list2[0]).intValue()); assertEquals(12, ((Integer) list2[1]).intValue()); list2 = (Object[]) array.getArray(2, 1); assertEquals(12, ((Integer) list2[0]).intValue()); list2 = (Object[]) array.getArray(Collections.<String, Class<?>>emptyMap()); assertEquals(11, ((Integer) list2[0]).intValue()); assertEquals(Types.NULL, array.getBaseType()); assertEquals("NULL", array.getBaseTypeName()); assertTrue(array.toString().endsWith(": (11, 12)")); // free array.free(); assertEquals("null", array.toString()); assertThrows(ErrorCode.OBJECT_CLOSED, array).getBaseType(); assertThrows(ErrorCode.OBJECT_CLOSED, array).getBaseTypeName(); assertThrows(ErrorCode.OBJECT_CLOSED, array).getResultSet(); assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); }
private void assertResultSetsEqual(Statement s1, Statement s2) throws SQLException { final TimeZone moscowTz = TimeZone.getTimeZone("Europe/Moscow"); final Calendar moscowCalendar = Calendar.getInstance(moscowTz); final TimeZone alaskaTz = TimeZone.getTimeZone("America/Anchorage"); final Calendar alaskaCalendar = Calendar.getInstance(alaskaTz); try (ResultSet rs1 = s1.getResultSet(); ResultSet rs2 = s2.getResultSet()) { assertEquals(rs1.getMetaData().getColumnCount(), rs2.getMetaData().getColumnCount()); int colCount = rs1.getMetaData().getColumnCount(); while (rs1.next() && rs2.next()) { for (int i = 0; i < colCount; i++) { Object o1 = rs1.getObject(i + 1); Object o2 = rs2.getObject(i + 1); if (o1 instanceof Integer && o2 instanceof Short) { // Hsqldb returns Integer for short columns; we prefer Short o1 = ((Number) o1).shortValue(); } if (o1 instanceof Integer && o2 instanceof Byte) { // Hsqldb returns Integer for tinyint columns; we prefer Byte o1 = ((Number) o1).byteValue(); } if (o1 instanceof Date) { Date d1 = rs1.getDate(i + 1, moscowCalendar); Date d2 = rs2.getDate(i + 1, moscowCalendar); assertEquals(d1, d2); d1 = rs1.getDate(i + 1, alaskaCalendar); d2 = rs2.getDate(i + 1, alaskaCalendar); assertEquals(d1, d2); d1 = rs1.getDate(i + 1, null); d2 = rs2.getDate(i + 1, null); assertEquals(d1, d2); d1 = rs1.getDate(i + 1); d2 = rs2.getDate(i + 1); assertEquals(d1, d2); } if (o1 instanceof Timestamp) { Timestamp d1 = rs1.getTimestamp(i + 1, moscowCalendar); Timestamp d2 = rs2.getTimestamp(i + 1, moscowCalendar); assertEquals(d1, d2); d1 = rs1.getTimestamp(i + 1, alaskaCalendar); d2 = rs2.getTimestamp(i + 1, alaskaCalendar); assertEquals(d1, d2); d1 = rs1.getTimestamp(i + 1, null); d2 = rs2.getTimestamp(i + 1, null); assertEquals(d1, d2); d1 = rs1.getTimestamp(i + 1); d2 = rs2.getTimestamp(i + 1); assertEquals(d1, d2); } assertEquals(o1, o2); } } assertEquals(rs1.next(), rs2.next()); } }
private double fetchValueOfDouble(String fieldname) { // As with fetch...String, the point is to handle nulls gracefully. try { return (rs_instrument.getObject(fieldname) == null) ? 0 : Double.parseDouble(rs_instrument.getObject(HEIGHT).toString()); } catch (SQLException sqle) { System.out.println("Unable to fetch value of " + fieldname + ".\n" + sqle); return -1; } }
private static Object getValue(final String columnName, final ResultSet resultSet) throws SQLException { Object result = resultSet.getObject(columnName); if (null == result) { result = resultSet.getObject(columnName.toUpperCase()); } if (null == result) { result = resultSet.getObject(columnName.toLowerCase()); } return result; }