private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException { Vector currentRow = new Vector(); for (int i = 1; i <= rsmd.getColumnCount(); ++i) switch (rsmd.getColumnType(i)) { case Types.VARCHAR: currentRow.addElement(rs.getString(i)); break; case Types.INTEGER: currentRow.addElement(new Long(rs.getLong(i))); break; case Types.DATE: currentRow.addElement(rs.getDate(i)); break; case Types.FLOAT: currentRow.addElement(rs.getFloat(i)); break; case Types.CHAR: currentRow.addElement(rs.getString(i)); break; case Types.REAL: currentRow.addElement(rs.getFloat(i)); break; default: System.out.println("Type was: " + rsmd.getColumnTypeName(i)); } return currentRow; }
// Retrieve all the books from database public List<BookDetails> getAllBook() { String sql = "select * from books"; ArrayList<BookDetails> list = new ArrayList<BookDetails>(); try { getConnection(); PreparedStatement pstmt = con.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while (rs != null && rs.next()) { BookDetails book = new BookDetails(); book.setBookId(rs.getString("id")); book.setDescription(rs.getString("description")); book.setFirstName(rs.getString("first_name")); book.setInventory(rs.getInt("inventory")); book.setOnSale(rs.getBoolean("onSale")); book.setPrice(rs.getFloat("price")); book.setSurname(rs.getString("surname")); book.setTitle(rs.getString("title")); book.setYear(rs.getInt("calendar_year")); list.add(book); } pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } return list; }
// Retrieve book details based on bookId, null is returned if book is not found public BookDetails getBookDetails(String bookId) { String sql = "select * from books where id = ?"; BookDetails book = null; try { getConnection(); PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1, bookId); ResultSet rs = pstmt.executeQuery(); if (rs != null && rs.next()) { book = new BookDetails(); book.setBookId(rs.getString("id")); book.setDescription(rs.getString("description")); book.setFirstName(rs.getString("first_name")); book.setInventory(rs.getInt("inventory")); book.setOnSale(rs.getBoolean("onSale")); book.setPrice(rs.getFloat("price")); book.setSurname(rs.getString("surname")); book.setTitle(rs.getString("title")); book.setYear(rs.getInt("calendar_year")); } pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } return book; }
// 根据订单号查订单项 public List selectOrderItem(int id) { List list = new ArrayList(); OrderItem ordetrItem = null; DbUtil dao = new DbUtil(); PreparedStatement pre = null; ResultSet re = null; String sql = "select * from tb_orderItem where orderId = ? "; try { pre = dao.getCon().prepareStatement(sql); pre.setInt(1, id); re = pre.executeQuery(); while (re.next()) { ordetrItem = new OrderItem(); ordetrItem.setBookId(re.getInt("bookId")); ordetrItem.setBookName(re.getString("bookName")); ordetrItem.setBookNum(re.getInt("bookNum")); ordetrItem.setOrderId(re.getInt("orderId")); ordetrItem.setOrderItemId(re.getInt("orderItemId")); ordetrItem.setPrice(re.getFloat("price")); list.add(ordetrItem); } } catch (Exception e) { } return list; }
public static Funcionario populateFuncionario(ResultSet rs) throws SQLException { Funcionario toreturn = new Funcionario(); toreturn.setId(rs.getInt("id")); toreturn.setCpf(rs.getLong("cpf")); toreturn.setNome(rs.getString("nome")); toreturn.setEndereco(rs.getString("endereco")); toreturn.setCidade(rs.getString("cidade")); toreturn.setEstado(rs.getString("estado")); toreturn.setTelefone(rs.getString("telefone")); toreturn.setEmail(rs.getString("email")); toreturn.setSalario(rs.getFloat("salario")); toreturn.setCargo(rs.getString("cargo")); toreturn.setFaturamentoM(rs.getFloat("faturamensal")); toreturn.setFoto(rs.getString("foto")); return toreturn; }
@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;"); }
@Override public synchronized Account getAccount(String name) throws RemoteException, RejectedException { if (name == null) { return null; } Account acct = accounts.get(name); if (acct == null) { try { findAccountStatement.setString(1, name); ResultSet result = findAccountStatement.executeQuery(); if (result.next()) { acct = new AccountImpl( result.getString("name"), result.getFloat("balance"), getConnection()); result.close(); accounts.put(name, acct); } else { return null; } } catch (SQLException | ClassNotFoundException e) { throw new RejectedException("Unable to find account for " + name, e); } } return acct; }
static void ImportProducts(Statement stat) throws Exception { AbstractRepository<Product> pr = (ProductRepository) ctx.getBean("productRepository"); ResultSet rs = stat.executeQuery("select * from Products;"); while (rs.next()) { Product p = new Product( rs.getInt("productID"), rs.getString("productName"), rs.getInt("supplierID"), rs.getInt("categoryID"), rs.getString("quantityPerUnit"), rs.getFloat("unitPrice"), rs.getInt("unitsInStock"), rs.getInt("unitsOnOrder"), rs.getInt("reorderLevel"), rs.getString("discontinued")); pr.insert(p); /* String s = "Product [productID=" + rs.getString("productID") + ", productName=" + rs.getString("productName") + ", supplierID=" + rs.getString("supplierID") + ", categoryID=" + rs.getString("categoryID") + ", quantityPerUnit=" + rs.getString("quantityPerUnit") + ", unitPrice=" + rs.getString("unitPrice") + ", unitsInStock=" + rs.getString("unitsInStock") + ", unitsOnOrder=" + rs.getString("unitsOnOrder") + ", reorderLevel=" + rs.getString("reorderLevel") + ", discontinued=" + rs.getString("discontinued") + "]"; System.out.println(s); */ } rs.close(); }
public static void GenerateCustomerBill(Connection conn) { // We intended to produce the bills over certain time periods but // we will offer an option to check just the last 3 months. try { int c_id = BooksAThousand.getIntFromShell("Enter customer ID: "); Statement statement = conn.createStatement(); ResultSet rs = statement.executeQuery("select address, name from customer where customer_id = " + c_id); rs.next(); String address = rs.getString(1); String name = rs.getString(2); rs = statement.executeQuery( "select customer_id, isbn, quantity, price, customer_order_date" + " from customer_order where customer_id=" + c_id + " and is_paid='N'"); float total = 0; if (rs.next()) { Date date; SimpleDateFormat sdf = new SimpleDateFormat("MM-dd-yyyy hh:mm"); System.out.println("\n" + address); System.out.println("\nDear " + name + ", \nBelow are your unpaid orders. Pay up.\n"); System.out.printf( "%-10s %-20s %-5s %-8s %-11s\n", "ISBN", "Date", "Quant", "Price", "Total"); do { total += rs.getInt(3) * rs.getFloat(4); date = rs.getDate(5); System.out.printf( "%-10s %-20s %-5d $%-7.2f $%-10.2f \n", rs.getInt(2), sdf.format(date), rs.getInt(3), rs.getFloat(4), rs.getInt(3) * rs.getFloat(4)); } while (rs.next()); System.out.printf("Total: $%.2f\n", total); } else { System.out.println("This user has no unpaid orders"); } } catch (Throwable e) { e.printStackTrace(); } }
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; }
@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); } }
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Statement question; String query; ResultSet answer; connect(); try { query = "SELECT * FROM PILOT WHERE Address ='" + request.getParameter("city") + "'"; question = link.createStatement(); answer = question.executeQuery(query); PrintWriter pen; response.setContentType("text/html"); pen = response.getWriter(); pen.println("<HTML>"); pen.println("<HEAD> <TITLE> Answer </TITLE> </HEAD>"); pen.println("<BODY>"); while (answer.next()) { String pN = answer.getString("PilotNumber"); String lN = answer.getString("LastName"); String fN = answer.getString("FirstName"); String ad = answer.getString("Address"); float sa = answer.getFloat("Salary"); float pr = answer.getFloat("Premium"); Date hD = answer.getDate("HiringDate"); if (answer.wasNull() == false) { pen.println("<P><B> Pilot : </B>" + lN + " " + fN); pen.println("<P><B> ---Reference : </B>" + pN); pen.println("<P><B> ---Address : </B>" + ad); pen.println("<P><B> ---Salary : </B>" + sa); pen.println("<P><B> ---since : </B>" + hD); if (pr > 0) pen.println("<P><B> ---Premium : </B>" + pr); else pen.println("<P><B> ---No premium </B>"); } } pen.println("</BODY>"); pen.println("</HTML>"); answer.close(); question.close(); link.close(); } catch (SQLException e) { System.out.println("Connection error: " + e.getMessage()); } }
private float[][] parse(int colone, int coltwo, ResultSet rs) throws SQLException { ArrayList<Float> valsone = new ArrayList<Float>(); ArrayList<Float> valstwo = new ArrayList<Float>(); while (rs.next()) { valsone.add(rs.getFloat(colone + 1)); // 1 b/c ResultSet is 1 based valstwo.add( rs.getFloat( coltwo + 4)); // 4 b/c ResultSet is 1 based and there were three columns for channel one } rs.close(); float[][] output = new float[2][valsone.size()]; for (int i = 0; i < valsone.size(); i++) { output[0][i] = valsone.get(i); output[1][i] = valstwo.get(i); } valsone = null; valstwo = null; return output; }
public Team find(int id) { Team team = new Team(); try { ResultSet result = this.connect .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY) .executeQuery("SELECT * FROM teams WHERE te_id = " + id); if (result.first()) team = new Team(id, result.getFloat("te_points")); } catch (SQLException e) { e.printStackTrace(); } return team; }
@Test public void colNameAccess() throws SQLException { PreparedStatement prep = conn.prepareStatement("select ? as col1, ? as col2, ? as bingo;"); prep.setNull(1, 0); prep.setFloat(2, Float.MIN_VALUE); prep.setShort(3, Short.MIN_VALUE); prep.executeQuery(); ResultSet rs = prep.executeQuery(); assertTrue(rs.next()); assertNull(rs.getString("col1")); assertTrue(rs.wasNull()); assertEquals(Float.MIN_VALUE, rs.getFloat("col2")); assertEquals(Short.MIN_VALUE, rs.getShort("bingo")); rs.close(); prep.close(); }
public void populate(ResultSet rset) { try { setEmployeeID(rset.getInt("employee_id")); setLogonNo(rset.getInt("logon_no")); setLogonPass(rset.getInt("logon_pass")); setProfile(rset.getInt("profile")); setFirstName(rset.getString("fname")); setLastName(rset.getString("lname")); setMiddleInitial(rset.getString("mi")); setSSN(rset.getString("ssn")); setSalaryGrade(rset.getInt("sal_grade")); setHourlyWage((double) rset.getFloat("hourly_wage")); } catch (java.sql.SQLException e) { Application.dbConnection().setException(e); } }
@Ignore @Test public void getObject() throws SQLException { stat.executeUpdate( "create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);"); PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);"); prep.setInt(1, Integer.MAX_VALUE); prep.setFloat(2, Float.MAX_VALUE); prep.setDouble(3, Double.MAX_VALUE); prep.setLong(4, Long.MAX_VALUE); prep.setBoolean(5, false); prep.setByte(6, (byte) 7); prep.setBytes(7, b1); prep.executeUpdate(); ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;"); assertTrue(rs.next()); assertEquals(Integer.MAX_VALUE, rs.getInt(1)); assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1)); assertEquals(Float.MAX_VALUE, rs.getFloat(2)); assertEquals(Double.MAX_VALUE, rs.getDouble(3)); assertEquals(Long.MAX_VALUE, rs.getLong(4)); assertFalse(rs.getBoolean(5)); assertEquals((byte) 7, rs.getByte(6)); assertArrayEq(rs.getBytes(7), b1); assertNotNull(rs.getObject(1)); assertNotNull(rs.getObject(2)); assertNotNull(rs.getObject(3)); assertNotNull(rs.getObject(4)); assertNotNull(rs.getObject(5)); assertNotNull(rs.getObject(6)); assertNotNull(rs.getObject(7)); assertTrue(rs.getObject(1) instanceof Integer); assertTrue(rs.getObject(2) instanceof Double); assertTrue(rs.getObject(3) instanceof Double); assertTrue(rs.getObject(4) instanceof String); assertTrue(rs.getObject(5) instanceof Integer); assertTrue(rs.getObject(6) instanceof Integer); assertTrue(rs.getObject(7) instanceof byte[]); rs.close(); }
// ---------------------------------------------------------------// public ArrayList<Project> selectAllProjects() throws SQLException { Connection conn = null; PreparedStatement prepStmt = null; ArrayList<Project> rtrn = null; try { conn = select(); String sql = "SELECT pr.*,cst.CUST_NAME FROM PROJECTS pr,CUSTOMERS cst WHERE cst.CUST_ID=pr.CUST_ID"; prepStmt = conn.prepareStatement(sql); ResultSet rs = prepStmt.executeQuery(); rtrn = new ArrayList<Project>(); while (rs.next()) { Project proj = new Project( rs.getInt("PROJ_ID"), rs.getString("PROJ_NAME"), rs.getInt("CUST_ID"), rs.getString("PROJ_TYPE"), rs.getDate("PROJ_FROM"), rs.getDate("PROJ_TO"), rs.getDate("PROJ_DEADLINE"), (rs.getString("PROJ_ACTIVE")).equals("Y"), rs.getFloat("PROJ_BUDGET"), rs.getString("PROJ_DESCRIPTION"), rs.getTimestamp("INSERTED_AT"), rs.getString("INSERTED_BY"), rs.getTimestamp("MODIFIED_AT"), rs.getString("MODIFIED_BY"), rs.getInt("ROWVERSION"), rs.getInt("PAR_PROJ_ID"), rs.getString("CUST_NAME")); rtrn.add(proj); } rs.close(); } finally { if (prepStmt != null) conn.close(); if (conn != null) conn.close(); } return rtrn; }
public ResultSet processShipment(int sid) { try { ps = con.prepareStatement( "SELECT upc, quantity, supPrice " + "FROM ShipItem " + "WHERE sid = ?"); ps.setInt(1, sid); ResultSet rs = ps.executeQuery(); int upc; int quantity; float supPrice; while (rs.next()) { upc = rs.getInt("upc"); quantity = rs.getInt("quantity"); supPrice = rs.getFloat("supPrice"); ps = con.prepareStatement( "UPDATE Item " + "SET quantity = quantity + ?, sellPrice = (? * 1.2) " + "WHERE upc = ?"); ps.setInt(1, quantity); ps.setFloat(2, supPrice); ps.setInt(3, upc); ps.executeUpdate(); } con.commit(); return rs; } catch (SQLException ex) { ExceptionEvent event = new ExceptionEvent(this, ex.getMessage()); fireExceptionGenerated(event); return null; } }
@Override public synchronized Account newAccount(String name) throws RemoteException, RejectedException { AccountImpl account = (AccountImpl) accounts.get(name); if (account != null) { System.out.println("Account [" + name + "] exists!!!"); throw new RejectedException( "Rejected: Bank: Account for: " + name + " already exists: " + account); } ResultSet result = null; try { findAccountStatement.setString(1, name); result = findAccountStatement.executeQuery(); if (result.next()) { // account exists, instantiate, put in cache and throw exception. account = new AccountImpl(name, result.getFloat("balance"), getConnection()); accounts.put(name, account); throw new RejectedException("Rejected: Account for: " + name + " already exists"); } result.close(); // create account. createAccountStatement.setString(1, name); int rows = createAccountStatement.executeUpdate(); if (rows == 1) { account = new AccountImpl(name, getConnection()); accounts.put(name, account); System.out.println("Bank: Account: " + account + " has been created for " + name); return account; } else { throw new RejectedException("Cannot create an account for " + name); } } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); throw new RejectedException("Cannot create an account for " + name, e); } }
// ---------------------------------------------------------------------------------------------------------------- // Method: ReadRecords // Inputs: Result Set and Array List // Outputs: (by ref) Array List // Description: Read the Records, Fill Array List with JSON data // ---------------------------------------------------------------------------------------------------------------- public void ReadRecords(ResultSet rs, ArrayList records) { Gson gson = new GsonBuilder().create(); // Create Gson builder try // Try to get JSON, and save data to database { // while (rs.next()) // Loop through all the returned records, until EOF { // RFData RFMember = new RFData(); // Create new RF data // Capture the data from the record set RFMember.SampleNumber = rs.getInt("intSampleNum"); // Get sample # RFMember.XbeeID = rs.getInt("intXbeeID"); // Get Xbee ID RFMember.DeviceID = rs.getInt("intDeviceID"); // Get Device ID RFMember.RSSI = rs.getFloat("fltRSSI"); // Get RSSI RFMember.Latitude = rs.getFloat("fltLatitude"); // Get Latitude RFMember.Longitude = rs.getFloat("fltLongitude"); // Get Longitude RFMember.Yaw = rs.getFloat("fltYaw"); // Get Yaw RFMember.Pitch = rs.getFloat("fltPitch"); // Get Pitch RFMember.Roll = rs.getFloat("fltRoll"); // Get Roll RFMember.SampleDate = rs.getTimestamp("dtSampleDate"); // Get Sample Date // Print debug information to port SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); System.out.println( "Got Record: # " + RFMember.SampleNumber + " - XbeeID: " + RFMember.XbeeID + ", RSSI: " + RFMember.RSSI + " Date/Time: " + ft.format(RFMember.SampleDate)); String json_record = gson.toJson(RFMember); // Get JSON data of this record records.add(json_record); // Add the JSON string to the record } } catch (Exception e) { System.err.println("ReadRecords: " + e.getMessage()); // Print the exception data and exit } }
public void actionPerformed(ActionEvent ae) { AbstractButton jlab = null; if ((count > 7)) { JOptionPane.showMessageDialog( jlab, "UnSuccessful Submit", "Error", JOptionPane.ERROR_MESSAGE); } else if ((count == 0)) { AbstractButton jlab1 = null; JOptionPane.showMessageDialog( jlab1, "UnSuccessful Submit", "Error", JOptionPane.ERROR_MESSAGE); } else if ((ae.getActionCommand().equals("Submit"))) { AbstractButton jlab2 = null; System.out.println("Submitted"); // JOptionPane.showMessageDialog(jlab1, "Successfully Submitted."); JOptionPane.showMessageDialog( jlab2, "Submited Successfully!" + "\n" + "Choices are: " + choices); String nameofuser; // change username with passed parameter here nameofuser = arg; System.out.println("Username:"******"select * from user where username= '******'"; try { pst = con.prepareStatement(sql); rs = pst.executeQuery(); if (rs.next()) { this.hide(); allowedchoices.insert(0, rs.getString(9)); threshold = rs.getFloat(7); } int i; StringBuffer temp; temp = new StringBuffer("-------"); for (i = 0; i < 7; i++) { if (allowedchoices.charAt(i) != choices.charAt(i)) { temp.setCharAt(i, choices.charAt(i)); } } double risk = 0.0; for (i = 0; i < 7; i++) { switch (temp.charAt(i)) { case 'r': risk = risk + 0.15; break; case 'c': risk = risk + 0.0; break; case 'a': risk = risk + 0.20; break; case 'u': risk = risk + 0.0; break; case 'd': risk = risk + 0.5; break; case 'p': risk = risk + 0.3; break; case 'e': risk = risk + 0.25; break; } } System.out.println("Current Risk Value: " + risk); System.out.println( "PREMISSION ALLOWED(BY DEFAULT):" + allowedchoices + "THRESHOLD:" + threshold); if (threshold < risk) { con = ClientConnect.ConnectDB(); String sql1 = "update user set accountstatus = '" + 0 + "' where username= '******'"; JOptionPane.showMessageDialog( jlab1, "Account Has Been Disabled! \n \t Contact Administrator!", "Error", JOptionPane.ERROR_MESSAGE); Statement stmt = con.createStatement(); stmt.execute(sql1.toString()); stmt.close(); } else { GUIFRfetch gf = new GUIFRfetch(); } } catch (SQLException | HeadlessException e) { JOptionPane.showMessageDialog(null, e); } } }
public static void main(String[] argv) throws Exception { String[] sArray = new String[8]; long time = System.currentTimeMillis(); try { FileInputStream fstream = new FileInputStream("system.in"); DataInputStream in = new DataInputStream(fstream); BufferedReader br = new BufferedReader(new InputStreamReader(in)); int i = 0; while ((sArray[i] = br.readLine()) != null) { sArray[i] = sArray[i].split("=")[1].trim(); System.out.println(sArray[i]); i++; } in.close(); } catch (Exception e) { } System.out.println("-------- Oracle JDBC Connection Testing ------"); try { Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println("Where is your Oracle JDBC Driver?"); e.printStackTrace(); return; } System.out.println("Oracle JDBC Driver Registered!"); Connection connection = null; try { connection = DriverManager.getConnection( "jdbc:oracle:thin:@//oracle1.cise.ufl.edu:1521/orcl", sArray[0], sArray[1]); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); e.printStackTrace(); return; } ResultSet rset = null; if (connection != null) { System.out.println(" -|||- "); Process p = Runtime.getRuntime() .exec("sqlplus " + sArray[0] + "@orcl/" + sArray[1] + " @adwords.sql"); p.waitFor(); // CallableStatement cstmt; System.out.println(" ||| "); System.out.println( "Time taken in Milliseconds (establishing connection): " + (System.currentTimeMillis() - time)); time = System.currentTimeMillis(); Process proc3 = Runtime.getRuntime() .exec( "sqlldr " + sArray[0] + "/" + sArray[1] + "@orcl DATA=Keywords.dat CONTROL=Keywords.ctl LOG=Keywords.log"); proc3.waitFor(); Process proc1 = Runtime.getRuntime() .exec( "sqlldr " + sArray[0] + "/" + sArray[1] + "@orcl DATA=Advertisers.dat CONTROL=Advertisers.ctl LOG=Advertiser.log"); proc1.waitFor(); Process proc2 = Runtime.getRuntime() .exec( "sqlldr " + sArray[0] + "/" + sArray[1] + "@orcl DATA=Queries.dat CONTROL=Queries.ctl LOG=Queries.log"); proc2.waitFor(); System.out.println(" ||| "); System.out.println( "Time taken in Milliseconds (loading dat): " + (System.currentTimeMillis() - time)); time = System.currentTimeMillis(); CallableStatement callableStatement = null; String storeProc = "{call sq(?,?,?,?,?,?)}"; try { callableStatement = connection.prepareCall(storeProc); callableStatement.setInt(1, Integer.parseInt(sArray[2])); callableStatement.setInt(2, Integer.parseInt(sArray[3])); callableStatement.setInt(3, Integer.parseInt(sArray[4])); callableStatement.setInt(4, Integer.parseInt(sArray[5])); callableStatement.setInt(5, Integer.parseInt(sArray[6])); callableStatement.setInt(6, Integer.parseInt(sArray[7])); callableStatement.executeUpdate(); } catch (SQLException e) { System.out.println(e.getMessage()); } /*callableStatement = connection.prepareCall("{call sq( " + Integer.parseInt(sArray[2]) + ", " + Integer.parseInt(sArray[3]) + ", " + Integer.parseInt(sArray[4]) + ", " + Integer.parseInt(sArray[5]) + ", " + Integer.parseInt(sArray[6]) + ", " + Integer.parseInt(sArray[7]) + " ) }");*/ System.out.println(" ||| "); System.out.println( "Time taken in Milliseconds (sql procedure): " + (System.currentTimeMillis() - time)); time = System.currentTimeMillis(); FileWriter fw = null; File file = null; PrintWriter pw = null; Statement resStmt = null; ResultSet resRs = null; int qid = 0; int rank; for (int i = 0; i < 6; i++) { file = new File("system.out." + (i + 1)); try { if (!file.exists()) file.createNewFile(); else file.delete(); fw = new FileWriter(file.getPath(), true); } catch (Exception e) { System.out.println(e.getMessage()); } pw = new PrintWriter(fw); resStmt = connection.createStatement(); resRs = resStmt.executeQuery( "SELECT * FROM OUTPUT" + (i + 1) + " order by qid asc, adrank asc"); while (resRs.next()) { qid = resRs.getInt("QID"); rank = (int) resRs.getFloat("ADRANK"); int advertiserId = resRs.getInt("ADVERTISERID"); float balance = resRs.getFloat("BALANCE"); float budget = resRs.getFloat("BUDGET"); StringBuffer resStr = new StringBuffer(); resStr.append(qid + "," + rank + "," + advertiserId + "," + balance + "," + budget); // System.out.println(resStr.toString()); pw.println(resStr.toString()); } pw.close(); } } else { System.out.println("Failed to make connection!"); } System.out.println( "Time taken in Milliseconds (file write): " + (System.currentTimeMillis() - time)); }
public void logoff() { Timestamp stamp = new Timestamp((new Date()).getTime()); SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss"); try { ResultSet rs = Application.dbConnection() .executeWithResult( "SELECT max(employee_time_sheet_id) from Employee_TimeSheet where employee_id = " + employeeID()); if (rs.next()) { int timesheet_id = rs.getInt(1); Application.dbConnection() .execute( "UPDATE Employee_Timesheet SET logoff_time = now() WHERE employee_time_sheet_id = " + timesheet_id); ResultSet rs2 = Application.dbConnection() .executeWithResult( "SELECT logon_time, logoff_time, hourly_wage from Employee_Timesheet where employee_time_sheet_id = " + timesheet_id); rs2.next(); float hourlyWage = rs2.getFloat("hourly_wage"); // NOTE This is hacked to get the logoff time back from the DB. Some DB's apparently // transpose the time, so as long as it's consistent amountOwed will be OK Timestamp logon_time = rs2.getTimestamp("logon_time"); Timestamp logoff_time = rs2.getTimestamp("logoff_time"); double hoursWorked = (logoff_time.getTime() - logon_time.getTime()) / (1000.0d * 60.0d * 60.0d); double amountOwed = hourlyWage * hoursWorked; Application.dbConnection() .execute( "UPDATE Employee_Timesheet SET amount_owed = " + amountOwed + " WHERE employee_time_sheet_id = " + timesheet_id); } else { logger.error( "Could not find an appropriate logon entry for employee " + employeeID() + " who logged off at " + df.format(stamp)); } } catch (SQLException e) { logger.fatal( "Could not insert logoff time for employee:" + employeeID() + " who logged off at " + df.format(stamp), e); } }
void runPrepareTest() { setProgressMinMax(0, m_nNumRuns - 1); int nProgressStep = m_nNumRuns / 10 + 1; try { boolean m_bTransactionsUsed = m_bTrans && m_conn.getMetaData().supportsTransactions(); boolean bCurrentAutoCommit = m_conn.getAutoCommit(); m_conn.setAutoCommit(!m_bTransactionsUsed); int nAccNum, nBranchNum, nTellerNum; double dDelta, dBalance; log( "Starting SQL prepare/execute benchmark for " + m_nNumRuns + ((m_time > 0) ? " min.\n" : " runs\n"), 0); java.util.Random rand = new java.util.Random(m_nMaxAccount + m_nMaxBranch + m_nMaxTeller); for (int nRun = 0; (m_time > 0) ? true : nRun < m_nNumRuns; nRun++) { if (m_time > 0) { java.util.Date current = new java.util.Date(); if ((current.getTime() - m_time) > (m_nNumRuns * 60000)) break; } PreparedStatement updAccStmt = null, selAccStmt = null, updTellerStmt = null, updBranchStmt = null, insHistStmt = null; try { nAccNum = (int) (rand.nextFloat() * rand.nextFloat() * (m_nMaxAccount - 1)) + 1; nBranchNum = (int) (rand.nextFloat() * (m_nMaxBranch - 1)) + 1; nTellerNum = (int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1; dDelta = ((double) ((long) ((((int) (rand.nextFloat() * (m_nMaxTeller - 1)) + 1) * (rand.nextFloat() > 0.5 ? -1 : 1)) * 100))) / 100; // prepare statements updAccStmt = m_conn.prepareStatement( "UPDATE " + m_Driver.getAccountName() + " SET balance = balance + ? WHERE account = ?"); selAccStmt = m_conn.prepareStatement( "SELECT balance FROM " + m_Driver.getAccountName() + " WHERE account = ?"); updTellerStmt = m_conn.prepareStatement( "UPDATE " + m_Driver.getTellerName() + " SET balance = balance + ? WHERE teller = ?"); updBranchStmt = m_conn.prepareStatement( "UPDATE " + m_Driver.getBranchName() + " SET balance = balance + ? WHERE branch = ?"); insHistStmt = m_conn.prepareStatement( "INSERT INTO " + m_Driver.getHistoryName() + " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES (? , ? , ? , ? , ? , " + m_nowFunction + " , ?)"); // bind parameters updAccStmt.setDouble(1, dDelta); updAccStmt.setInt(2, nAccNum); // System.out.println(nAccNum); selAccStmt.setInt(1, nAccNum); updTellerStmt.setDouble(1, dDelta); updTellerStmt.setInt(2, nTellerNum); updBranchStmt.setDouble(1, dDelta); updBranchStmt.setInt(2, nBranchNum); insHistStmt.setInt(1, nRun); insHistStmt.setInt(2, nAccNum); insHistStmt.setInt(3, nTellerNum); insHistStmt.setInt(4, nBranchNum); insHistStmt.setDouble(5, dDelta); insHistStmt.setString(6, BenchPanel.strFiller.substring(0, 21)); java.util.Date startTime = new java.util.Date(); // execute statements log( "UPDATE " + m_Driver.getAccountName() + " SET balance = balance + " + dDelta + " WHERE account = " + nAccNum + "\n", 2); updAccStmt.executeUpdate(); log( "SELECT balance FROM " + m_Driver.getAccountName() + " WHERE account = " + nAccNum + "\n", 2); ResultSet balanceSet = selAccStmt.executeQuery(); balanceSet.next(); dBalance = balanceSet.getFloat(1); // if (balanceSet == null) // System.out.println("balanceSet is NULL"); // else { // String strBalance = balanceSet.getString(1); // System.out.println(balanceSet.wasNull() ? "SQL NULL" : strBalance); // } balanceSet.close(); log( "UPDATE " + m_Driver.getTellerName() + " SET balance = balance + " + dDelta + " WHERE teller = " + nTellerNum + "\n", 2); updTellerStmt.executeUpdate(); log( "UPDATE " + m_Driver.getBranchName() + " SET balance = balance + " + dDelta + " WHERE branch = " + nBranchNum + "\n", 2); updBranchStmt.executeUpdate(); log( "INSERT INTO " + m_Driver.getHistoryName() + " (histid, account, teller, branch, amount, timeoftxn, filler) VALUES (" + nRun + " , " + nAccNum + " , " + nTellerNum + " , " + nBranchNum + " , " + dDelta + " , " + m_nowFunction + " , \'" + BenchPanel.strFiller.substring(0, 21) + "\')\n", 2); insHistStmt.executeUpdate(); if (m_bQuery) executeQuery(); if (m_bTransactionsUsed) m_conn.commit(); java.util.Date endTime = new java.util.Date(); m_nTrans += 1; double diff = endTime.getTime() - startTime.getTime(); if (diff < 1000) m_nTrans1Sec += 1; else if (diff < 2000) m_nTrans2Sec += 1; m_nTimeSum += diff / 1000; } catch (SQLException e1) { // System.err.println(e1.getMessage()); break; } finally { try { if (updAccStmt != null) updAccStmt.close(); if (selAccStmt != null) selAccStmt.close(); if (updTellerStmt != null) updTellerStmt.close(); if (updBranchStmt != null) updBranchStmt.close(); if (insHistStmt != null) insHistStmt.close(); } catch (SQLException e) { } } if (nRun % nProgressStep == 0) setProgressValue(nRun); // yield(); } setProgressValue(m_nNumRuns - 1); m_conn.setAutoCommit(bCurrentAutoCommit); } catch (SQLException e) { // JOptionPane.showMessageDialog(null, e.getMessage(), "SQL Error in Text test", // JOptionPane.ERROR_MESSAGE); log("SQLError in prepare test : " + e.getMessage(), 0); } }
// ---------------------------------------------------------------------------------------------------------------- // Method: ListDataByEntryID // Inputs: SampleNum // Outputs: RF Data Entry (JSON) // Description: Get RF Data Entry // ---------------------------------------------------------------------------------------------------------------- public String ListDataByEntryID(int SampleNum) { String json = ""; // Return JSON entry try // Try to get JSON, and save data to database { // // Print debug information to port System.out.println("Select RF Data from Table - Sample #: " + SampleNum); String sql_string; // Build up SQL string sql_string = "SELECT "; // Select SQL statement sql_string += "intSampleNum,"; // Field: intSampleNum sql_string += "intXbeeID,"; // Field: intXbeeID sql_string += "intDeviceID,"; // Field: intDeviceID sql_string += "fltRSSI,"; // Field: fltRSSI sql_string += "fltLatitude,"; // Field: fltLatitude sql_string += "fltLongitude,"; // Field: fltLongitude sql_string += "fltYaw,"; // Field: fltYaw sql_string += "fltPitch,"; // Field: fltPitch sql_string += "fltRoll,"; // Field: fltRoll sql_string += "dtSampleDate "; // Field: dtSampleDate sql_string += "FROM RF_Fields "; // Table: RF_Fields sql_string += "WHERE "; // Where statement sql_string += "intSampleNum = "; // Field on Where and condition sql_string += SampleNum; // Condition value System.out.println("SQL: " + sql_string); // Debug print the SQL statement Statement stmt = conn.createStatement(); // Build SQL statement ResultSet rs = stmt.executeQuery(sql_string); // Execute the SQL statement as a query while (rs.next()) // Loop through all the returned records, until EOF { // However, there should only be one return record Gson gson = new GsonBuilder().create(); // Create Gson builder RFData RFMember = new RFData(); // Create new RF data // Capture the data from the record set RFMember.SampleNumber = rs.getInt("intSampleNum"); // Get sample # RFMember.XbeeID = rs.getInt("intXbeeID"); // Get Xbee ID RFMember.DeviceID = rs.getInt("intDeviceID"); // Get Device ID RFMember.RSSI = rs.getFloat("fltRSSI"); // Get RSSI RFMember.Latitude = rs.getFloat("fltLatitude"); // Get Latitude RFMember.Longitude = rs.getFloat("fltLongitude"); // Get Longitude RFMember.Yaw = rs.getFloat("fltYaw"); // Get Yaw RFMember.Pitch = rs.getFloat("fltPitch"); // Get Pitch RFMember.Roll = rs.getFloat("fltRoll"); // Get Roll RFMember.SampleDate = rs.getTimestamp("dtSampleDate"); // Get Sample Date // Print debug information to port SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); System.out.println( "Got Record: # " + RFMember.SampleNumber + " - XbeeID: " + RFMember.XbeeID + ", RSSI: " + RFMember.RSSI + " Date/Time: " + ft.format(RFMember.SampleDate)); json = gson.toJson(RFMember); // Get JSON data from this record } rs.close(); // Close the record set stmt.close(); // Close the statement if (json == "") // { // System.err.println("No Record Found!"); // Print the fact that no record was found } } // catch (Exception e) // Exception processing: { // System.err.println( "ListDataByEntryID: " + e.getMessage()); // Print the exception data and exit } // return json; // Return JSON string }
ArrayList funt(String id, int noofbottles) throws SQLException { ArrayList a1l = new ArrayList(); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/barshop", "root", "root"); PreparedStatement psmt, psm, psmt3, psmt_ret_temp, psmt_total; ResultSet res, res1, res3, res_ret_temp, res_total; // String id = request.getParameter("id"); int total = 0; // int noofbottles=Integer.parseInt(request.getParameter("noofbottles")); // out.println(id); String Query = "Select * from stock where barcode =" + id; // out.println(Query); // out.println(Query); psmt = con.prepareStatement(Query); res = psmt.executeQuery(); String Category = "kau", category_ret_temp = null; String Brand = null, Product = null; int Volume = 0, noofbottles1 = 0; float Price = 0; while (res.next()) { Category = res.getString("Category"); Brand = res.getString("Brand"); Product = res.getString("ProductName"); Volume = res.getInt("Litres"); Volume = Volume * noofbottles; noofbottles1 = res.getInt("NumberOfBottles"); Price = res.getFloat("Price"); Price *= noofbottles; } if (Category.equals("kau")) { a1l.add(1); a1l.add("Barcode does not exist"); } else if (noofbottles > noofbottles1) { a1l.add(2); a1l.add("Dont have sufficient stock"); } else if (Volume > 4500) { a1l.add(3); a1l.add("go to next bill volume exceeding 4.5L"); } else { String Query_ret_temp = "Select * from temp where barcode =" + id; psmt_ret_temp = con.prepareStatement(Query_ret_temp); res_ret_temp = psmt_ret_temp.executeQuery(); while (res_ret_temp.next()) { category_ret_temp = res_ret_temp.getString("Category"); } // out.println("category_ret_temp="+category_ret_temp); if (category_ret_temp == null) { String Query1 = "insert into temp values(" + id + ",'" + Category + "','" + Brand + "','" + Product + "'," + Volume + "," + noofbottles + "," + Price + ")"; psmt = con.prepareStatement(Query1); psmt.execute(); } else { String Query1_update_tmp = "update temp set price=price+" + Price + ",noofbottles=noofbottles+" + noofbottles + " where barcode=" + id; psmt = con.prepareStatement(Query1_update_tmp); psmt.execute(); } // out.println(Query1); // out.println(res1); String Query2 = "Select * from temp "; // out.println(Query); // out.println(Query2); psmt3 = con.prepareStatement(Query2); res3 = psmt3.executeQuery(); a1l.add(4); while (res3.next()) { a1l.add(res3.getString("barcode")); a1l.add(res3.getString("category")); a1l.add(res3.getString("brand")); a1l.add(res3.getString("product")); a1l.add(res3.getString("liters")); a1l.add(res3.getString(6)); a1l.add(res3.getString(7)); } String Query_total = "SELECT sum( Price ) as t FROM temp"; psmt_total = con.prepareStatement(Query_total); res_total = psmt_total.executeQuery(); while (res_total.next()) total = res_total.getInt("t"); } return a1l; }
private void getChannelList() { Connection con = null; ChannelInfo ci; this.channel_data.clear(); try { con = pool.getConnection(timeout); ResultSet rs; try (Statement s = con.createStatement()) { rs = s.executeQuery("SELECT * FROM `channels`"); PreparedStatement s2; ResultSet rs2; while (rs.next()) { ci = new ChannelInfo(rs.getString("channel")); ci.setDefaultOptions(); ci.setReactiveChatter( rs.getInt("reactive_chatter_level"), rs.getInt("chatter_name_multiplier")); ci.setRandomChatter(rs.getInt("random_chatter_level")); ci.setTwitterTimers( rs.getFloat("tweet_bucket_max"), rs.getFloat("tweet_bucket_charge_rate")); ci.setWarAutoMuzzle(rs.getBoolean("auto_muzzle_wars")); ci.velociraptorSightings = rs.getInt("velociraptor_sightings"); ci.activeVelociraptors = rs.getInt("active_velociraptors"); ci.deadVelociraptors = rs.getInt("dead_velociraptors"); ci.killedVelociraptors = rs.getInt("killed_velociraptors"); if (rs.getDate("last_sighting_date") != null) { long time = rs.getDate("last_sighting_date").getTime(); ci.lastSighting = new java.util.Date(time); } s2 = con.prepareStatement( "SELECT `setting`, `value` FROM `channel_chatter_settings` WHERE `channel` = ?"); s2.setString(1, rs.getString("channel")); s2.executeQuery(); rs2 = s2.getResultSet(); while (rs2.next()) { ci.addChatterSetting(rs2.getString("setting"), rs2.getBoolean("value")); } s2.close(); rs2.close(); s2 = con.prepareStatement( "SELECT `setting`, `value` FROM `channel_command_settings` WHERE `channel` = ?"); s2.setString(1, rs.getString("channel")); s2.executeQuery(); rs2 = s2.getResultSet(); while (rs2.next()) { ci.addCommandSetting(rs2.getString("setting"), rs2.getBoolean("value")); } s2.close(); rs2.close(); s2 = con.prepareStatement( "SELECT `account` FROM `channel_twitter_feeds` WHERE `channel` = ?"); s2.setString(1, rs.getString("channel")); s2.executeQuery(); rs2 = s2.getResultSet(); while (rs2.next()) { ci.addTwitterAccount(rs2.getString("account")); } s2.close(); rs2.close(); this.channel_data.put(ci.channel, ci); this.saveChannelSettings(ci); } } rs.close(); } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } finally { try { if (con != null) { con.close(); } } catch (SQLException ex) { Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex); } } }
@Override public void readAllordersDB() { Order order; int n = 0; try { // STEP 2: Register JDBC driver // Class.forName("JDBC_DRIVER"); // STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 4: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql; sql = "SELECT id, dateAndTime, addr1, addr2, addr34, distance, orderCost, " + "customerPhone, customerName, carNumber, carDriver, isDone FROM ordersdb"; ResultSet rs = stmt.executeQuery(sql); // STEP 5: Extract data from result set while (rs.next()) { order = new Order(); order.setId(rs.getInt("id")); order.setDateAndTime(rs.getTimestamp("dateAndTime")); order.setAddr1(rs.getString("addr1")); order.setAddr2(rs.getString("addr2")); order.setAddr34(rs.getString("addr34")); order.setDistance(rs.getFloat("distance")); order.setOrderCost(rs.getFloat("orderCost")); order.setCustomerPhone(rs.getString("customerPhone")); order.setCustomerName(rs.getString("customerName")); order.setCarNumber(rs.getString("carNumber")); order.setCarDriver(rs.getString("carDriver")); order.setIsDone(((rs.getInt("isDone")) != 0)); orderDB.orderAdd(order); n++; } // STEP 6: Clean-up environment rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { } // nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("Read operation from DB done. Readed " + n + " Orders."); }
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { // get service code String temp = request.getParameter("service"); int serviceCode = temp == null ? 0 : Integer.parseInt(temp); // establish connection Connection con = null; Statement stm_scrl_r = null; try { Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/gemsdb", "gems", "gems"); stm_scrl_r = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); } catch (ClassNotFoundException e) { serviceCode = 0; e.printStackTrace(); } // sql vars final String RETREIVE_PARTY = "SELECT * FROM gemsdb.party"; final String RETREIVE_ETYPE = "SELECT * FROM gemsdb.expense"; ResultSet rs; // write output PrintWriter out = response.getWriter(); try { // TODO probably bad coding using 'if's ... but practical // Name, value pair for user list // ============================== if (serviceCode == 1) { response.setContentType("text/html;charset=UTF-8"); rs = stm_scrl_r.executeQuery(RETREIVE_PARTY); // out.println("<root>"); while (rs.next()) { int value = rs.getInt("p_id"); String name = rs.getString("p_name"); // out.println("<user name=\"" + name + "\" value=\"" + value + "\"></user>"); out.println("<option value=\"" + value + "\">" + name + "</option>"); } // out.println("</root>"); } // Name, value pair for expense list // ================================= if (serviceCode == 2) { response.setContentType("text/html;charset=UTF-8"); rs = stm_scrl_r.executeQuery(RETREIVE_ETYPE); // out.println("<root>"); while (rs.next()) { int value = rs.getInt("e_id"); String name = rs.getString("e_name"); // out.println("<etype name=\"" + name + "\" value=\"" + value + "\"></etype>"); out.println("<option value=\"" + value + "\">" + name + "</option>"); } // out.println("</root>"); } // Debitor table, Creditor table, User table // ========================================= if (serviceCode == 3) { response.setContentType("text/xml;charset=UTF-8"); // TODO WARNING: Hard-coded value for `world` - Needs better logic int MEDIATOR_USER = 3; int p_id = MEDIATOR_USER; // Integer.parseInt(request.getParameter("user")); long fromDate = Long.parseLong(request.getParameter("fromDate")); long toDate = Long.parseLong(request.getParameter("toDate")); Map<String, Float> accountMap = new HashMap<String, Float>(); final String TABLE_HEADER = "" + "<table class=\"table table-bordered table-condensed \">" + "<thead><tr>" + "<th>User</th>" + "<th>Amount</th>" + "</tr></thead>" + "<tbody>"; final String TABLE_FOOTER = "</tbody></table>"; out.println("<root>"); // Debitor table out.print("<debitor><![CDATA["); rs = stm_scrl_r.executeQuery(getQueryString(p_id, fromDate, toDate, 3)); out.print("<h3>Debit table</h3>"); out.print(TABLE_HEADER); while (rs.next()) { String party = rs.getString("p_name"); float amount = rs.getFloat("total"); out.print("<tr><td>" + party + "</td><td>" + amount + "</td></tr>"); accountMap.put(party, amount); } out.print(TABLE_FOOTER); out.println("]]></debitor>"); // Creditor table out.print("<creditor><![CDATA["); rs = stm_scrl_r.executeQuery(getQueryString(p_id, fromDate, toDate, 4)); out.print("<h3>Credit table</h3>"); out.print(TABLE_HEADER); while (rs.next()) { String party = rs.getString("p_name"); float amount = rs.getFloat("total"); out.print("<tr><td>" + party + "</td><td>" + amount + "</td></tr>"); Float debitObj = accountMap.get(party); float debit = debitObj == null ? 0.0f : debitObj.floatValue(); accountMap.put(party, (debit - amount)); } out.print(TABLE_FOOTER); out.println("]]></creditor>"); out.println("<user><![CDATA["); out.print("<h3>User summary</h3>"); out.print("<table class=\"table table-bordered leade\">"); Set<String> keys = accountMap.keySet(); for (String key : keys) { Float amount = accountMap.get(key); String color = amount < 0 ? "#B94A48" : "#468847"; out.print( "<tr><th>" + key + "</th><th style=\"color:" + color + "\">" + accountMap.get(key) + "</th></tr>"); } out.print("</table>"); out.println("]]></user>"); out.println("</root>"); } // Testing purposes if (serviceCode == 5) { response.setContentType("text/xml;charset=UTF-8"); rs = stm_scrl_r.executeQuery("SELECT * FROM `gemsdb`.`partytransaction`"); ResultSetMetaData rsmd = rs.getMetaData(); int colCount = rsmd.getColumnCount(); out.println("<root><![CDATA["); while (rs.next()) { out.print(rs.getRow() + " : "); for (int i = 1; i <= colCount; i++) { out.print(rs.getString(i)); out.print(","); } out.println(); } out.println("]]></root>"); } // Get the minimum date in transactions or current date // ==================================================== if (serviceCode == 6) { response.setContentType("text/html;charset=UTF-8"); rs = stm_scrl_r.executeQuery("select MIN(`t_date`) from `gemsdb`.`transaction`"); Date minDate = new Date(new java.util.Date().getTime()); if (rs.next()) { minDate = rs.getDate(1); } out.println(minDate.getTime()); } } finally { out.close(); } // close connection con.close(); }