I've figured out how to pull the username and display it in the categories.jsp but I just can't figure out how to display the result of the costQuery in the UserDAO.java to display in the categories.jsp where the empty
<%= %>
tags are.
the costQuery is querying the database as follows:
select sum(cost) from stocks where username=
The username is being passed into the query.
I've tried so many things now with no luck I'm not even sure what to do next. My code is below.
Any help would be GREATLY appreciated.
index.jsp (my login form)
<%@page contentType="text/html" pageEncoding="UTF-8" language="java"%>
<%@page language="java"%>
<%@page import="java.util.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=windows-1256">
<title>Login Page</title>
</head>
<body>
<form action="LoginServlet">
<table border="5">
<tr>
<th colspan="2">Login</th>
</tr>
<tr>
<td>Username:</td>
<td><input type="text" name="un"/></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" name="pw"/></td>
</tr>
<tr>
<th colspan="2"><input type="submit" value="Submit" name="Submit"/><input type="Reset" value="Reset" /></th>
</tr>
<tr>
<td colspan="2"><input type="checkbox" value="remeber" /> Remember Me on this Computer<br /></td>
</tr>
</table>
<p><a href="register">New users click here to register</a></p>
</form>
</body>
</html>
categories.jsp
<%@page language="java" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@page import="HWpackage.UserBean"%>
<%@page import="HWpackage.StockBean"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<table border='5'>
<tr>
<th>Welcome <%UserBean currentUser = (UserBean) (session.getAttribute("currentSessionUser"));%><%= currentUser.getUsername()%></th>
</tr>
<tr>
<td><p style="text-align:center">You currently have a total of **<%= %>** invested.</p></td>
</tr>
<tr>
<th>Please select from the list of stock categories below.</th>
</tr>
</table>
<p></p>
<table border ="5">
<tr>
<th>Type of stock</th>
<th>Invested in category</th>
</tr>
<tr>
<td><a href="tech"><p style="text-align:center">Technology Stocks</p></a></td>
<td><p style="text-align:center">$$$$$</p></td>
</tr>
<tr>
<td><a href="utility"><p style="text-align:center">Utility Stocks</p></a></td>
<td><p style="text-align:center">$$$$$$$$</p></td>
</tr>
<th colspan="2"><a href="index"><p style="text-align:right">[Log Out]</p></a></th>
<tr>
</table>
</body>
</html>
UserBean.java
package HWpackage;
public class UserBean {
private String username;
private String password;
public boolean valid;
private String stockname;
private String stocksym;
private String stockcat;
private String action;
private String pricepershare;
private String quantity;
private String actnick;
private String acthold;
private String actnum;
private String actroute;
private String actbal;
private String cost;
private String totalcost;
private String costQuery;
public String getPassword() {
return password;
}
public void setPassword(String newPassword) {
password = newPassword;
}
public String getUsername() {
return username;
}
public void setUserName(String newUsername) {
username = newUsername;
}
public boolean isValid() {
return valid;
}
public void setValid(boolean newValid) {
valid = newValid;
}
public String getStockname() {
return stockname;
}
public void setStockname(String stockname) {
this.stockname = stockname;
}
public String getStocksym() {
return stocksym;
}
public void setStocksym(String stocksym) {
this.stocksym = stocksym;
}
public String getStockcat() {
return stockcat;
}
public void setStockcat(String stockcat) {
this.stockcat = stockcat;
}
public String getAction() {
return action;
}
public void setAction(String action) {
this.action = action;
}
public String getPricepershare() {
return pricepershare;
}
public void setPricepershare(String pricepershare) {
this.pricepershare = pricepershare;
}
public String getQuantity() {
return quantity;
}
public void setQuantity(String quantity) {
this.quantity = quantity;
}
public String getActnick() {
return actnick;
}
public void setActnick(String actnick) {
this.actnick = actnick;
}
public String getActhold() {
return acthold;
}
public void setActhold(String acthold) {
this.acthold = acthold;
}
public String getActnum() {
return actnum;
}
public void setActnum(String actnum) {
this.actnum = actnum;
}
public String getActroute() {
return actroute;
}
public void setActroute(String actroute) {
this.actroute = actroute;
}
public String getActbal() {
return actbal;
}
public void setActbal(String actbal) {
this.actbal = actbal;
}
public String getCost(){
return cost;
}
public void setCost (String cost) {
this.cost = cost;
}
public String getTotalcost(){
return totalcost;
}
public void setTotalcost (String totalcost) {
this.totalcost = totalcost;
}
public String getCostQuery(){
return costQuery;
}
public void setCostQuery (String costQuery) {
this.costQuery = costQuery;
}
}
UserDAO.java
package HWpackage;
import java.text.*;
import java.util.*;
import java.sql.*;
public class UserDAO {
static Connection currentCon = null;
static ResultSet rs = null;
static ResultSet rsTech = null;
static ResultSet rsUtil = null;
static ResultSet rsCost = null;
public static UserBean login(UserBean bean) {
//preparing some objects for connection
Statement stmt = null;
String username = bean.getUsername();
String password = bean.getPassword();
String stockname = bean.getStockname();
String stocksym = bean.getStocksym();
String stockcat = bean.getStockcat();
String action = bean.getAction();
String pricepershare = bean.getPricepershare();
String quantity = bean.getQuantity();
String actnick = bean.getActnick();
String acthold = bean.getActhold();
String actnum = bean.getActnum();
String actroute = bean.getActroute();
String actbal = bean.getActbal();
String cost = bean.getCost();
String totalcost = bean.getTotalcost();
String costQuery = bean.getCostQuery();
String searchQuery =
"select * from users where username='"
+ username
+ "' AND password='"
+ password
+ "'";
// "System.out.println" prints in the console; Normally used to trace the process
System.out.println("Your user name is " + username);
System.out.println("Your password is " + password);
System.out.println("Query: "+searchQuery);
String techQuery =
"select sum(cost) from stocks where username='"
+ username
+ "' AND stockcat='Tech'";
System.out.println("Your stock category is Tech");
System.out.println("Query: "+techQuery);
String utilQuery =
"select sum(cost) from stocks where username='"
+ username
+ "' AND stockcat='Util'";
System.out.println("Your stock category is Utilities");
System.out.println("Query: "+utilQuery);
costQuery =
"select sum(cost) from stocks where username='"
+ username
+ "'";
System.out.println("This is for all stocks");
System.out.println("Query: "+costQuery);
try
{
//connect to DB
currentCon = ConnectionManager.getConnection();
stmt=currentCon.createStatement();
rs = stmt.executeQuery(searchQuery);
boolean more = rs.next();
// if user does not exist set the isValid variable to false
if (!more)
{
System.out.println("Sorry, you are not a registered user! Please sign up first");
bean.setValid(false);
}
//if user exists set the isValid variable to true else if (more)
else if (more)
{
System.out.println("Welcome " + username);
bean.setValid(true);
}
}
catch (Exception ex)
{
System.out.println("Log In failed: An Exception has occurred! " + ex);
}
//some exception handling
finally
{
if (rs != null) {
try {
rs.close();
} catch (Exception e) {}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
stmt = null;
}
if (currentCon != null) {
try {
currentCon.close();
} catch (Exception e) {
}
currentCon = null;
}
}
return bean;
}
}
