CODEWITHSUNDEEP
javamysqljdbcprepared-statement
fost
fost

Reputation: 63

JDBC How can I use a place holder in a prepared statement with a where clause?

I'm having a hard time understanding why this wont work, if I type the exact same thing straight into a MySQL console it accepts it but when ever I try to run it, it reports a syntax error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '6'' at line 1

All I'm trying to do is receive the data in the row with the member_id value of whatever the user inputs. For testing purposes the value is always 6, I have tried parsing it as int instead of a string, which gave the same error, and I tried just adding the ID variable onto the end of the string instead of using a place holder but it didn't like that much either.

Here is the code:

public class MemberDAO {

public PreparedStatement ps = null;
public Connection dbConnection = null;

public List<Member> getMembersDetails(String ID) throws SQLException{

    List<Member> membersDetails = new ArrayList();

    String getMembershipDetails = "SELECT first_name, last_name, phone_number, email, over_18, date_joined, date_expire, fines FROM members"
            + "WHERE member_id = ?";

    try {
        DBConnection mc = new DBConnection();
        dbConnection = mc.getConnection();
        ps = dbConnection.prepareStatement(getMembershipDetails);

        ps.setString(1, ID);
        ps.executeQuery();

        ResultSet rs = ps.executeQuery(getMembershipDetails);


        String firstName = rs.getString("first_name");
        String lastName = rs.getString("last_name");
        String phoneNumber = rs.getString("phone_number");
        String email = rs.getString("email");
        String over18 = rs.getString("over_18");
        String dateJoined = rs.getString("date_joined");
        String dateExpired = rs.getString("date_expire");
        String fines = rs.getString("fines");

        Member m;
        m = new Member(firstName, lastName, phoneNumber, email, over18, dateJoined, dateExpired, fines);
        membersDetails.add(m);


    }  catch (SQLException ex){
        System.err.println(ex);
        System.out.println("Failed to get Membership Details.");
        return null;
    } finally{
        if (ps != null){
            ps.close();
        }
        if (dbConnection != null){
            dbConnection.close();
        }
    } return membersDetails;
}

This is what's calling it:

private void btnChangeCustomerActionPerformed(java.awt.event.ActionEvent evt) {                                                  

    customerID = JOptionPane.showInputDialog(null, "Enter Customer ID.");

    MemberDAO member = new MemberDAO();
    try {
        List membersDetails = member.getMembersDetails(customerID);

        txtFullName.setText(membersDetails.get(0) + " " + membersDetails.get(1));
    } catch (SQLException ex) {
        System.err.println(ex);
        System.out.println("Failed to get Details.");
        JOptionPane.showMessageDialog(null, "Failed to retrieve data.");
    }
}

Any input is appreciated.

Upvotes: 0

Views: 1525

Answers (2)

Jimmysnn
Jimmysnn

Reputation: 593

your query is:

SELECT first_name, last_name, phone_number, email, over_18, date_joined, date_expire, fines FROM 

members WHERE member_id = ?

So between member and where you need a blank character

SELECT first_name, last_name, phone_number, email, over_18, date_joined, date_expire, fines FROM members WHERE member_id = ?

Upvotes: 0

beny23
beny23

Reputation: 35008

Your query is missing a space:

...fines FROM members"
        + "WHERE...

Will result in

 FROM membersWHERE

Which is invalid SQL

Change it to

 + " WHERE....

Upvotes: 1

Related Questions