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();
}
