检查dao中的功能 [英] check functions in dao

问题描述

大家好，

我正在开发一个在线图书馆.在这里我想借一本书.借贷之前需要获得未偿贷款和罚款的总额.当我执行此功能时，它说

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.

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

推荐答案

删除以下几行

Remove the following lines

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

如果要在结果集中使用l_amt列，则可以在您的WHERE子句中包含l_amt列;此外，如果要求和，还必须添加GROUP BY子句.

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.

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

在这里，您必须提供用于分组的列名(即用列名替换GROUP BY之后的?").

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

这篇关于检查dao中的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！