File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Unsure how to do subtraction using joined table values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Unsure how to do subtraction using joined table values" Watch "Unsure how to do subtraction using joined table values" New topic
Author

Unsure how to do subtraction using joined table values

vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
This is what i need to acheive. I have one value "Totalleave" in one table. The second value is "Leavetaken" in another table. I need to take "totalleave" minus "Leavetaken" and get the value. Primary key connecting both is "employeeID". I need help retriving values from both tables and then doing the subtraction function. Thank you.


This is my draft select statement.

String userName = (String) session.getAttribute("User");
if (null == userName) {
request.setAttribute("Error", "Session has ended. Please login.");
RequestDispatcher rd = request.getRequestDispatcher("login.jsp");
rd.forward(request, response);
}


("select Totalleave from tb_employee where employeeID = '" + userName + "'");


Your help in this matter is greatly appreciated.
[ June 12, 2006: Message edited by: Bear Bibeault ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

SELECT (A.TOTALLEAVE - B.LEAVETAKEN) AS BALANCE_LEAVE
FROM TB_EMPLOYEE A, ANOTHER_TABLE B WHERE A.EMPLOYEEID=B.EMPLOYEEID
AND A.EMPLOYEEID = ?


Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
Thank you for your reply. But i get the following error. What might be wrong. Your help is greatly appreciated.



Generated servlet error:
C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:134: ';' expected.
String query1 = SELECT (tb_employee.Leave_Entitlement - tb_leavemc.Duration) AS BALANCE_LEAVE




String query1 = SELECT (tb_employee.Leave_Entitlement - tb_leavemc.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?

PreparedStatement statement = connection.prepareStatement( query1 );
statement.setString(1, username);
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

My first tip would be that check the sql on your database without java.

Tell me if it works correct,also tell me what database are you using

and write your query as

String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?";


Shailesh
[ June 12, 2006: Message edited by: Shailesh Chandra ]
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
Thank you very much. It works now without any errors. But if i want to display the value of "BALANCE_LEAVE" what is the coding i should write. Thank you.
vanan saravanan
Ranch Hand

Joined: Jun 02, 2006
Posts: 95
I tried the following but it does not seem to work.

out.println("BALANCE_LEAVE");

out.println('BALANCE_LEAVE');

<%=rst.getString("BALANCE_LEAVE") %>

Could someone please help... Thank you.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by vanan saravanan:
I tried the following but it does not seem to work.

out.println("BALANCE_LEAVE");

out.println('BALANCE_LEAVE');

<%=rst.getString("BALANCE_LEAVE") %>

Could someone please help... Thank you.

  • <%=rst.getString("BALANCE_LEAVE")%>
  • <%=rst.getInt("BALANCE_LEAVE")%>
  • <%=rst.getString(1)%>
  • <%=rst.getInt(1)%>

  • OR
  • out.println(rst.getString("BALANCE_LEAVE"));
  • out.println(rst.getInt("BALANCE_LEAVE"));
  • out.println(rst.getString(1));
  • out.println(rst.getInt(1));



  • Shailesh
    [ June 13, 2006: Message edited by: Shailesh Chandra ]
    vanan saravanan
    Ranch Hand

    Joined: Jun 02, 2006
    Posts: 95
    Thank you for your reply. But i got errors as follows. I tried each one seperately.

    1)<%=rst.getString("BALANCE_LEAVE")%>
    javax.servlet.ServletException: Column 'BALANCE_LEAVE' not found.

    2)<%=rst.getInt("BALANCE_LEAVE");%>
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:179: ')' expected.
    out.print(rst.getInt("BALANCE_LEAVE"););

    3)<%=rst.getString(1)%>
    prints out the ´┐Żusername´┐Ż variable

    4)<%=rst.getInt(1);%>
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:179: ')' expected.
    out.print(rst.getInt(1););


    OR

    1)out.println(rst.getString("BALANCE_LEAVE"));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getString("BALANCE_LEAVE"));

    2)out.println(rst.getInt("BALANCE_LEAVE"));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getInt("BALANCE_LEAVE"));

    3)out.println(rst.getString(1));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getString(1));

    4)out.println(rst.getInt(1));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getInt(1));
    vanan saravanan
    Ranch Hand

    Joined: Jun 02, 2006
    Posts: 95
    This is my exact code. database is mysql and works fine.

    <%//*********************************************************************************************************
    //Check if user login sucessfully
    //**********************************************************************************************************%>

    <%@ page import="java.util.*, java.sql.*, java.text.*" %>
    <title>leave page</title>
    <%

    response.setHeader("Cache-Control","no-cache"); //forces caches to obtain a new copy of the page from the origin server
    response.setHeader("Cache-Control","no-store"); //directs caches not to store the page under any circumstance
    response.setDateHeader("Expires", 0); //causes the proxy cache to see the page as "stale"
    response.setHeader("Pragma","no-cache"); //HTTP 1.0 backward compatibility

    String userName = (String) session.getAttribute("User");
    if (null == userName) {
    request.setAttribute("Error", "Session has ended. Please login.");
    RequestDispatcher rd = request.getRequestDispatcher("login.jsp");
    rd.forward(request, response);
    }
    %>

    <%//*********************************************************************************************************
    //Database Coonection Code
    //**********************************************************************************************************%>

    <%
    Connection con = null;



    String dbURL = "jdbc:mysql://localhost/***";

    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection(dbURL, "***", "***");

    %>


    <%//*********************************************************************************************************
    //Start of JSP(SQL) code
    //**********************************************************************************************************%>


    <%
    Statement stm=null;
    ResultSet rst=null;
    stm= con.createStatement();
    String query = ("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'");
    rst = stm.executeQuery(query);


    String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?";



    PreparedStatement statement = con.prepareStatement( query1 );
    statement.setString(1, userName);




    %>



    <%//*********************************************************************************************************
    //Start of Displaying Results code
    //**********************************************************************************************************%>

    <%

    while (rst.next()){ %>

    <%=rst.getString("Name") %>
    <%=userName%>
    out.println("BALANCE_LEAVE"); //<--------Problem lies here
    <%=rst.getString("Leave_Entitlement") %>
    <%}

    %>


    <%//*********************************************************************************************************
    //End of connection code
    //***********************************************************************************************************%>

    <%

    rst.close();
    stm.close();
    con.close();

    %>


    <p> </p>
    <p> </p>
    <p> </p>
    <p> </p>
    <p> </p>
    <p><a href="secure1.jsp">Secure 1</a>
    <br/>
    <a href="secure2.jsp">Secure 2</a>
    <br/>
    <a href="logout.jsp">Logout</a>
    <br/>
    </p>
    <p>
    This is Secure1 page. This page is pretended to contain secure information.
    </p>
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    vanan,

    Sorry for the runtimes errors , actually I copied some of your code with semi colons which has caused runtimes error.

    Now back to your problem

    look at this code of yours



    In the above code you are creating the prepared statement and not executing same.

    Now look at your this code


    as per your comment you have problem at code out.println("BALANCE_LEAVE"); and its only a statement which to print BALANCE_LEAVE also it is not enclosed in any scriptlets (<% %>)


    Probably you need to work little more with your code


    Shailesh
    [ June 13, 2006: Message edited by: Shailesh Chandra ]
    vanan saravanan
    Ranch Hand

    Joined: Jun 02, 2006
    Posts: 95
    In the above code you are creating the prepared statement and not executing same.

    I dont understand the above comment. The prepared statement works fine. The problem lies with printing the value of BALANCE_LEAVE.


    <%

    while (rst.next()){ %>

    <%=rst.getString("Name") %>
    <%=userName%>

    <%=rst.getString("BALANCE_LEAVE")%> // Tried each one individually but got error
    <%=rst.getString("BALANCE_LEAVE")%>// Tried each one individually but got error
    <%=rst.getInt("BALANCE_LEAVE")%>// Tried each one individually but got error
    <%=rst.getString(1)%>// Tried each one individually but got error
    <%=rst.getInt(1)%>// Tried each one individually but got error
    out.println(rst.getString("BALANCE_LEAVE"));// Tried each one individually but got error
    out.println(rst.getInt("BALANCE_LEAVE"));// Tried each one individually but got error
    out.println(rst.getString(1));// Tried each one individually but got error
    out.println(rst.getInt(1));// Tried each one individually but got error

    <%=rst.getString("Leave_Entitlement") %>
    <%}

    %>
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    Originally posted by vanan saravanan:

    I dont understand the above comment. The prepared statement works fine.


    ResultSet rst is output of query hold by variable query(("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'")but

    but where is the output of sql defined in variable query1("SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?";

    Actually I am looing for a line like
    ResultSet some_variable = statement.executeQuery()


    Shailesh



    )
    vanan saravanan
    Ranch Hand

    Joined: Jun 02, 2006
    Posts: 95
    I missed out the output for ResultSet for query1. Have included it in. But i get the error javax.servlet.ServletException: Column 'BALANCE_LEAVE' not found.

    <%//*********************************************************************************************************
    //Start of JSP(SQL) code
    //**********************************************************************************************************%>


    <%
    Statement stm=null;
    Statement stm1=null;
    ResultSet rst=null;
    ResultSet rst1=null;
    stm1= con.createStatement();
    stm= con.createStatement();
    String query = ("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'");
    rst = stm.executeQuery(query);


    String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";


    rst1 = stm1.executeQuery(query1);


    %>



    <%//*********************************************************************************************************
    //Start of Displaying Results code
    //**********************************************************************************************************%>

    <%

    while (rst.next()){ %>

    <%=rst.getString("Name") %>
    <%=userName%>

    <%=rst.getString("Leave_Entitlement") %>
    <%}



    while (rst1.next()){ %>

    <%=rst.getString("BALANCE_LEAVE")%>

    <%}





    %>
    [ June 13, 2006: Message edited by: vanan saravanan ]
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    I am not very sure about aliases in mysql as I have never worked om mysql try index of column instead, and use the correct resultset rst1

    <%=rst1.getString(1)%>


    Shailesh
    [ June 13, 2006: Message edited by: Shailesh Chandra ]
    vanan saravanan
    Ranch Hand

    Joined: Jun 02, 2006
    Posts: 95
    Thank you so much for your patience in helping me with the problem. It is now working fine. Just one last thing.

    String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";

    In my database, duration appears a few times. For example there is a record of '1' day. After a few other records, there is another record of '2' days for the same username. How do i modify the code above so that, Leave_Entitlement - total number of duration for that particular username.

    Thank you for your kind assistance.
    vanan saravanan
    Ranch Hand

    Joined: Jun 02, 2006
    Posts: 95
    Managed to figure it out myself

    String query1 = "SELECT (A.Leave_Entitlement - sum(B.Duration)) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";


    Thank you for your help.
    Shailesh Chandra
    Ranch Hand

    Joined: Aug 13, 2004
    Posts: 1081

    cheers

    Shailesh
     
     
    subject: Unsure how to do subtraction using joined table values
     
    Similar Threads
    Delete doubt
    Members are not poppulated?
    get table values from jsp to servlet
    Can a class be mapped to a table and be Embeddable at the same time?
    Need help with Mapping(Annotation) and Composition