check functions in dao

Question

0.00/5 (No votes)

See more:

hey guys,

im developing an online library. in here i want to borrow a book. before borrow need to get total amount of pending loans and fines. when i execute this function it says

java.sql.SQLException: Column 'l_amt' not found

table name is correct. can anyone one tell me whats wrong with this code.

Java

@Override
    public double checkLoan(reader r) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        System.out.println("Inside checkLoan");
        double b_result = 0.00;
        Connection conn= null;
        PreparedStatement ptmt= null;
        ResultSet rset = null;
        try{
            conn= getConnection();             
            String queryString = "SELECT SUM(l_amt) FROM loan WHERE l_status='true' AND r_id=? ";
            ptmt = conn.prepareStatement(queryString);
            ptmt.setString(1, r.getR_id());
            System.out.println(r.getR_id());
            rset = ptmt.executeQuery();
            while (rset.next()){
                System.out.println(rset.next());
                b_result = rset.getDouble("l_amt");   
                                        
                System.out.println("-.-.-."+b_result);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally{
            try{
                ptmt.close();
                    conn.close();
            }catch(SQLException ex){
                ex.printStackTrace();
            }
        }return b_result;
    }

    @Override
    public double checkFine(reader r) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        System.out.println("Inside checkFine");
        double b_result = 0.00;
        Connection conn= null;
        PreparedStatement ptmt= null;
        ResultSet rset = null;
        try{
            conn= getConnection();             
            String queryString = "SELECT SUM(f_amt) AS sum_fine_amt FROM fine WHERE f_status='true' AND r_id=?";
            ptmt = conn.prepareStatement(queryString);
            ptmt.setString(1, r.getR_id());
            System.out.println(r.getR_id());
            rset = ptmt.executeQuery();
            while (rset.next()){
                System.out.println(rset.next());
                b_result = rset.getDouble("sum_fine_amt");           
                         
                System.out.println("-.-.-."+b_result);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally{
            try{
                ptmt.close();
                 conn.close();
            }catch(SQLException ex){
                ex.printStackTrace();
            }
        }return b_result;
    }

    @Override
    public void borrow(book b, reader r) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        System.out.println("Inside borrow");
        Connection conn=null;
        PreparedStatement psmt=null;
        try{
            conn = getConnection();
            String queryString = "INSERT INTO book_borrow(b_id,rid) VALUES(?,?)";
            psmt = conn.prepareStatement(queryString);

            psmt.setString(1,b.getB_id());            
            psmt.setString(2,r.getR_id());
            
            int result = psmt.executeUpdate();
            System.out.println("result :" + result);
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            try {
                 psmt.close();
                 conn.close();
             } catch (SQLException ex) {
                 ex.printStackTrace();
             }
        }
    }

Posted 8-Sep-13 19:20pm

mali_angel

Add a Solution

Comments

mali_angel 11-Sep-13 3:28am

Thanx everybody it works!

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shubhashish_Mandal · Accepted Answer · 2013-09-09T03:20:00

Solution 1

Remove the following lines

Java

System.out.println(rset.next());

Posted 9-Sep-13 3:20am

Shubhashish_Mandal

Updated 9-Sep-13 3:29am

v2

phil.o · Accepted Answer · 2013-09-09T03:34:00

Solution 2

If you want the l_amt column in your result set, then you have to include the l_amt column in your WHERE clause ; moreover, if you want to SUM, you have to add a GROUP BY clause also.

C#

String queryString = "SELECT l_amt, SUM(f_amt) AS sum_fine_amt FROM fine WHERE f_status='true' AND r_id=? GROUP BY ?";

Here you have to give a column name used for groupment (ie. replace the '?' after GROUP BY with a column name).

Posted 9-Sep-13 3:34am

phil.o

Comments

Shubhashish_Mandal 10-Sep-13 5:45am

"SELECT SUM(l_amt) FROM loan WHERE l_status='true' AND r_id=12" , this query execute fine in my case. without adding the group by .

phil.o 10-Sep-13 5:55am

Except you are not the OP, and his issue could not be yours...
Moreover, he has to sum on column f_amt, not l_amt. Thus the GROUP BY statement.

Shubhashish_Mandal 10-Sep-13 6:21am

you post different query rather than OP. OP's first query is on the l_amt