Stroring data taken from database as a variable
Hi there I;m having a problem and I was wondering if anyone can help me?
I am trying to extract data from a MySQL database and store the variables so I can use them in another bean...
so far I am getting a SQL exception error but I dont know what Im doing wrong.......
My bean code is :
public int getAge(int age) throws SQLException, Exception{
if(con != null){
try{
String userName = null;
ResultSet rs;
Statement stmt = con.createStatement();
rs = stmt.executeQuery("SELECT (age) FROM gymuser WHERE Username='" +userName+ "'");
age = rs.getInt("age");
return age;
} catch(SQLException sqle){
error = "SQLException: could not search gymuser";
throw new SQLException(error);
}
catch(Exception e){
error = "An exception occured while searching gymuser";
throw new Exception(error);
}
} else {
error = "Exception: connection to database was lost";
throw new Exception(error);
}
}
The JSP that calls this is :
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="java.sql.*, java.io.*, java.util.*;"%>
<jsp:useBean id="details" scope="session" class="fitness.DBQuery" />
<jsp:useBean id="conn" class="fitness.DBConnect" />
<jsp:useBean id="currentuser" scope="session" class="fitness.currentuserBean" />
--%>
<%--
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
--%>
<!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>GetDetails</title>
</head>
<body>
<h1>GetDetails</h1>
<%
int age = 0;
String userName = currentuser.getName();
conn.connect();
details.setCon(conn.getCon());
details.getAge(age);
conn.disconnect();
%>
</body>
</html>
Any help you have to offer would be greatly appreciated as Im desperate!! :-)
[2274 byte] By [
Hegtica] at [2007-11-27 2:23:56]
# 1
> so far I am getting a SQL exception error but I dont> know what Im doing wrong.......> What is the full stack track of the SQL error?
# 2
} catch(SQLException sqle){
error = "SQLException: could not search gymuser";
throw new SQLException(error);
}
Please, don't suppress and ignore exceptions. Now we know nothing about the real cause but only your own interpretation which is incomplete.
Change it to at least:
} catch(SQLException sqle){
sqle.printStackTrace();
error = "SQLException: could not search gymuser";
throw new SQLException(error);
}
Or
} catch(SQLException sqle){
error = "SQLException: could not search gymuser";
throw new SQLException(error, sqle);
}
Or preferably
} catch(SQLException sqle){
error = "SQLException: could not search gymuser";
throw new YourOwnException(error, sqle);
}
# 3
Thank you for your responses
I got the method running properly....
I realised I was missingwhile (rs.next())
However it still doesn't work properly
In the JSP oage I am trying to get the property "age"
using a <jsp:getProperty name="details" property="age" />
But the server is telling me: Cannot find any information on property 'age' in a bean of type 'fitness.DBQuery'
I realise that the method is not actually in a bean as such...there are other methods in the class..but I have a get and set Method in there and I thought that would return the value to me....
How would I store that value taken from the database in a simple bean with just get and set methods for age?
Many thanks....
Heg
# 4
Basically:
1) create a DTO which represents one full table row.
2) Prepare a List<DTO>.
3) Map the ResultSet to List<DTO>
4) Pass the List<DTO> from the data-tier to the business-tier.
Pseudocode:public class GymUser {
private int age; // + getter + setter
// implement other properties here, also with getters and setters
}
List<GymUser> gymUsers = new ArrayList<GymUser>();
while (resultSet.next()) {
GymUser gymUser = new GymUser();
gymUser.setAge(resultSet.getInt("age"));
gymUsers.add(gymUser);
}
return gymUsers;
# 5
Hegtic,
BalusC is right..... there is a need to refactor/ redesign the code according to the principles of MVC and DAO - basically the idea is to avoid writing Java code directly in JSPs this practice is highly discouraged these days.
Instead it is much cleaner and neater to write Data Access related code in the data access layer of the application , and View/ Display code in JSPs while using Servlets to control JSPs and JavaBeans to access data from the application layer.
# 6
Thanks for your help, greatly appreciated....
I havent ever had to use DTO's before as this is the first time in my project where I need t use databasa data as variables in other classes.....so I should have a classthat looks like this ?: /*
* GymUser.java
*
* Created on 26 April 2007, 13:02
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
package fitness;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.util.Date;
import java.io.Serializable;
/**
*
* @author Hegstatic
*/
public class GymUser implements Serializable{
/** Creates a new instance of gymUser */
private String error;
private String userName;
private int age; // + getter + setter
private double weight;
private String gender;
public int getAge(){
return age;
}
public void setAge(int i)
{
age = i;
}
public double getWeight()
{
return weight;
}
public void setWeight(double d)
{
weight = d;
}
public String getGender()
{
return gender;
}
public void setGender(String string)
{
gender = string;
}
Connection con;
private String Frankie;
public GymUser getGymUser()
{
if(con != null){
try{
ResultSet rs;
Statement stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM gymuser WHERE Username='" +Frankie+ "'");
List<GymUser> gymUsers = new ArrayList<GymUser>();
while (rs.next()) {
GymUser gymUser = new GymUser();
gymUser.setAge(rs.getInt("age"));
gymUser.setWeight(rs.getDouble("weight"));
gymUser.setGender(rs.getString("gender"));
gymUsers.add(gymUser);
}
return gymUsers;
} catch(SQLException sqle){
error = "SQLException: could not search gymuser";
throw new Exception(error, sqle);
}
catch(Exception e){
error = "An exception occured while searching gymuser";
throw new Exception(error);
}
} else {
error = "Exception: connection to database was lost";
throw new Exception(error);
}
}
}
The IDE is telling me that the value I have for
return gymUsers;
is wrong...
Was just wondering if I am going in the right direction and how would I call the variables age, weight, gender in a JSP page?
Again Thank you !!
Hegtic
# 7
DTO's should wrap the data only. Do not implement DAO stuff in it. Create a separate DAO class where you retrieve the results and map it to List<DTO>. Basically: move getGymUser to GymUserDAO#getGymUsers() which returns List<GymUser>.
