Exception occcured while finding Account via ID

2

gave this error when trying to execute a class

error sef.module13.activity.AccountDAOException: Exception occcured while finding Account via ID

this is the class

package sef.module13.activity;
// Complete Code
public class AccountDAOException extends Exception{


    private static final long serialVersionUID = -2397012533045245997L;
    public static final String ERROR_FIND_ID="Exception occcured while finding Account via ID";
    public static final String ERROR_FIND_NAME="Exception occcured while finding Account via Name";
    public static final String ERROR_INSERT_ACCOUNT="Exception occured while inserting new Account";

    public AccountDAOException(String message, Throwable cause){
        super(message, cause);
    }

}

was attempting to execute this class

package sef.module13.activity;


//Needs to be completed

public class AccountDAOClient {

    public static void main(String[] args) {
        AccountDAO obj = new AccountDAOImpl();

        try {

            obj.findAccount("John");
            // Test1 - Type code to test findAccount("1")

            obj.findAccount("Jane", "Doe");
            // Test2 - Type code to test findAccount("J","D"). How many records
            // do you get?

            // Test3 - Type code to test
            // insertAccount("6","Sasha","Kohli","[email protected]",90000)

            obj.insertAccount("6", "Sasha", "Kohli", "[email protected]",90000);

            // Test4 - Type code to test deposit("1",2000)
            obj.deposit("1", 2000);

            // Test5 - Type code to test deposit("2",3000)
            obj.deposit("2", 3000);

            // Test6 - Type code to test deleteAccount("6")

        } catch (Exception e) {
            System.out.println("erro " + e);
        }

    }
}

Any suggestions?

I use the following interface

package sef.module13.activity;
// Complete Code
import java.util.List;

public interface AccountDAO {

    /**
     * Returns a list of accounts that with first names and last names that contain
     * the specified first name and last name.  The result list will be ordered via ID
     * in an ascending manner
     * 
     * @param firstName the first name of the account to search
     * @param lastName the last name of the account to search
     * @return list of accounts that match the criteria
     * 
     * @throws AccountDAOException when a problem occurs during search
     */
    public List findAccount(String firstName, String lastName) throws AccountDAOException;

    public Account findAccount(String id) throws AccountDAOException;

    public boolean insertAccount(String id, String firstName, String lastName, String email, float balance ) throws AccountDAOException;

    public boolean deposit(String id, float amount ) throws AccountDAOException;

    public boolean withdraw(String id, float amount ) throws AccountDAOException;

    public boolean deleteAccount(String id) throws AccountDAOException;
}

They are being implemented here the methods

package sef.module13.activity;

//Needs to be completed
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

public class AccountDAOImpl implements AccountDAO {

    private Connection conn;

    public AccountDAOImpl() {


        String url = "jdbc:mysql://localhost/activity";
        String user = "root";
        String pass = "123";
        try{
        Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
        JOptionPane.showMessageDialog(null, "impossível carregar o Driver.");
        System.exit(0);
        }

        try {
            Connection cn = DriverManager.getConnection(url, user, pass);
            this.conn = cn;
            conn.setAutoCommit(false);
        } catch (SQLException sqle) {
        JOptionPane.showMessageDialog(null, "Problema ao conectar!");
        System.exit(0);
        }






//      String url = "jdbc:mysql://localhost/activity";
//      String user = "root";
//      String pass = "123";
//      
//      
//
//      try {
//          // 1 - Load the driver
//
//          // 2 - Obtain a connection using DriverManager class
//          Connection cn = DriverManager.getConnection(url, user, pass);
//          this.conn = cn;
//          conn.setAutoCommit(false);
//      } catch (SQLException e) {
//          // TODO Auto-generated catch block
//          e.printStackTrace();
//      }

        // } catch (ClassNotFoundException e) {
        // // TODO Auto-generated catch block
        // e.printStackTrace();
        // } catch (SQLException e) {
        // // TODO Auto-generated catch block
        // e.printStackTrace();
        // }

    }

    // 3 - implement findAccount method which returns a list of accounts
    // matching the first name and last name combination
    public List findAccount(String firstName, String lastName)
            throws AccountDAOException {
        List<Account> results = new ArrayList<Account>();
        try {
            PreparedStatement pStmt = conn.prepareStatement("'select first_name from accout where first_name like  ?'");
            pStmt.setString(1, '%' + firstName.toUpperCase() + '%');
            pStmt.setString(2, '%' + lastName.toUpperCase() + '%');

            ResultSet rs = pStmt.executeQuery();
            while (rs.next()) {
                results.add(new AccountImpl(rs.getString(1), rs.getString(2),
                        rs.getString(3), rs.getString(4), rs.getFloat(5)));
            }

        } catch (SQLException e) {
            throw new AccountDAOException(AccountDAOException.ERROR_FIND_NAME,
                    e);
        }
        return results;
    }

    // 4 - Complete implementation for findAccount method
    public Account findAccount(String id) throws AccountDAOException {

        try {
            PreparedStatement pStmt = conn.prepareStatement("");
            pStmt.setString(1, id);
            ResultSet rs = pStmt.executeQuery();
            if (rs.next()) {
                return new AccountImpl(rs.getString(1), rs.getString(2),
                        rs.getString(3), rs.getString(4), rs.getFloat(5));
            }

        } catch (SQLException e) {
            throw new AccountDAOException(AccountDAOException.ERROR_FIND_ID, e);
        }

        return null;
    }

    // 5 - Complete implementation for insertAccount
    public boolean insertAccount(String id, String firstName, String lastName,
            String email, float balance) throws AccountDAOException {

        return false;
    }

    // 6 - Complete implementation for deposit. It should ensure that the
    // account balance is increased by the amount deposited.
    public boolean deposit(String id, float amount) throws AccountDAOException {
        return false;
    }

    // 7 - Complete implementation for withdraw. It should ensure that the
    // account balance is reduced by the amount deposited.
    public boolean withdraw(String id, float amount) throws AccountDAOException {
        return false;
    }

    // 8 - Complete implementation for deleteAccount
    public boolean deleteAccount(String id) throws AccountDAOException {
        return false;
    }

}

Following the suggestion

I changed and put this line of code

try {

    obj.findAccount("John");
    // Test1 - Type code to test findAccount("1")

    obj.findAccount("Jane", "Doe");
    // Test2 - Type code to test findAccount("J","D"). How many records
    // do you get?

    // Test3 - Type code to test
    // insertAccount("6","Sasha","Kohli","[email protected]",90000)

    obj.insertAccount("6", "Sasha", "Kohli", "[email protected]",90000);

    // Test4 - Type code to test deposit("1",2000)
    obj.deposit("1", 2000);

    // Test5 - Type code to test deposit("2",3000)
    obj.deposit("2", 3000);

    // Test6 - Type code to test deleteAccount("6")

} catch (Exception e) {
    e.printStackTrace();/*essa linha >>>>>>>>>>>>>>*/
}

there he generated this error

sef.module13.activity.AccountDAOException: Exception occcured while finding Account via ID     at sef.module13.activity.AccountDAOImpl.findAccount (AccountDAOImpl.java:110)     at sef.module13.activity.AccountDAOClient.main (AccountDAOClient.java:13) Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).     at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1094)     at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:997)     at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:983)     at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:928)     at com.mysql.jdbc.PreparedStatement.checkBounds (PreparedStatement.java:3688)     at com.mysql.jdbc.PreparedStatement.setInternal (PreparedStatement.java:3670)     at com.mysql.jdbc.PreparedStatement.setString (PreparedStatement.java:4491)     at sef.module13.activity.AccountDAOImpl.findAccount (AccountDAOImpl.java:102)     ... 1 more

You have indicated these lines

AccountAOImpl.java:110)

public Account findAccount(String id) throws AccountDAOException {

        try {
            PreparedStatement pStmt = conn.prepareStatement("'select first_name from accout where first_name like  ?'");
            pStmt.setString(1, id);                           
            ResultSet rs = pStmt.executeQuery();
            if (rs.next()) {
                return new AccountImpl(rs.getString(1), rs.getString(2),
                        rs.getString(3), rs.getString(4), rs.getFloat(5));
            }

        } catch (SQLException e) {
            throw new AccountDAOException(AccountDAOException.ERROR_FIND_ID, e);/*é essa linha>>>>>>>>>>>>>>>>>>>>>*/
        }

        return null;
    }

this line AccountDAOClient.java:13)

try {

            obj.findAccount("John");/*essa linha>>>>>>>>>>>>>>>>>> */
            // Test1 - Type code to test findAccount("1")

            obj.findAccount("Jane", "Doe");
            // Test2 - Type code to test findAccount("J","D"). How many records
            // do you get?

            // Test3 - Type code to test
            // insertAccount("6","Sasha","Kohli","[email protected]",90000)

            obj.insertAccount("6", "Sasha", "Kohli", "[email protected]",90000);

            // Test4 - Type code to test deposit("1",2000)
            obj.deposit("1", 2000);

            // Test5 - Type code to test deposit("2",3000)
            obj.deposit("2", 3000);

            // Test6 - Type code to test deleteAccount("6")

        } catch (Exception e) {
            e.printStackTrace();
        }

this line (AccountDAOImpl.java:102)

public Account findAccount(String id) throws AccountDAOException {

        try {
            PreparedStatement pStmt = conn.prepareStatement("'select first_name from accout where first_name like  ?'");
            pStmt.setString(1, id);/*essa linha>>>>>>>>>>>>>>>>>>>>>*/                           
            ResultSet rs = pStmt.executeQuery();
            if (rs.next()) {
                return new AccountImpl(rs.getString(1), rs.getString(2),
                        rs.getString(3), rs.getString(4), rs.getFloat(5));
            }

        } catch (SQLException e) {
            throw new AccountDAOException(AccountDAOException.ERROR_FIND_ID, e);
        }

        return null;
    }
    
asked by anonymous 12.09.2014 / 02:10

1 answer

2

You should remove the single quotation marks from your SQL query:

PreparedStatement pStmt = 
    conn.prepareStatement("'select first_name from accout where first_name like  ?'");

Should be:

PreparedStatement pStmt = 
    conn.prepareStatement("select first_name from accout where first_name like  ?");

If you put quotes, the statement does not understand your query, regardless of whether it has parameters or not.

    
12.09.2014 / 13:01