MySQLSyntaxErrorException in JAVA

Question

Somebody please help me.I am doing things right but i am getting an error.It is a JAVA application linked to MYSQL wamp server.

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 'Chege' at line 1

MY CODE:

public class MyQuery {

    public Connection getConnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://"
                    + "localhost:3306/employee_certificate", "root", "");
        } catch (SQLException ex) {
            Logger.getLogger(Query.class.getName())
                    .log(Level.SEVERE, null, ex);
        }
        return con;
    }

    public ArrayList<Item> getData(String EmpName) {
        ArrayList<Item> list = new ArrayList<Item>();
        Connection con = getConnection();
        Statement st;
        ResultSet rs;
        try {
            st = con.createStatement();
            rs = st.executeQuery("SELECT Emp_Id, Emp_Name, Department "
                    + "FROM staff WHERE Emp_Name = " + EmpName + " ");
            Item I;
            while (rs.next()) {
                I = new Item(
                        rs.getString("Emp_Id"),
                        rs.getString("Emp_Name"),
                        rs.getString("Department"));
                list.add(I);
            }
        } catch (SQLException ex) {
            Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
    }
}

Put the value of EmpName in quotes or even better use Prepared Statements — juergen d
– juergen d, Commented Jul 17, 2016 at 12:12

romanvintonyak · Accepted Answer · 2016-09-22 19:55:34Z

3

Your query string is not correct. Should be something like the following:

rs=st.executeQuery("SELECT Emp_Id, Emp_Name, Department "
          + "FROM staff WHERE Emp_Name = '"+EmpName+"'");

But I'd recommend to use a PreparedStatement object for sending SQL statements to the database.

String query = "SELECT Emp_Id, Emp_Name, Department FROM staff WHERE Emp_Name = ?";
PreparedStatement preStatement = conn.prepareStatement(query);
preStatement.setString(1, EmpName);
ResultSet result = preStatement.executeQuery();

This approach is safer and more convenient.

edited Sep 22, 2016 at 19:55

answered Jul 17, 2016 at 12:21

romanvintonyak

3511 gold badge3 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Youcef LAIDANI · Accepted Answer · 2016-07-17 12:22:33Z

You have a little problem in your query:

try {
    st = con.createStatement();
    //Add quotes 'YourString'
    rs = st.executeQuery("SELECT Emp_Id, Emp_Name, Department "
            + "FROM staff WHERE Emp_Name = '" + EmpName + "' ");
    Item I;
    while (rs.next()) {
        I = new Item(rs.getString("Emp_Id"), rs.getString("Emp_Name"), rs.getString("Department"));
        list.add(I);
    }
} catch (SQLException ex) {
    Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
}

or for a safety Query use prepared statement:

try {
    PreparedStatement ps = connection.prepareStatement("SELECT Emp_Id, Emp_Name, Department FROM staff WHERE Emp_Name = ?");
    ps.setString(1, EmpName);
    rs = ps.executeUpdate();
    Item I;
    while (rs.next()) {
        I = new Item(rs.getString("Emp_Id"), rs.getString("Emp_Name"), rs.getString("Department"));
        list.add(I);
    }
} catch (SQLException ex) {
    Logger.getLogger(Query.class.getName()).log(Level.SEVERE, null, ex);
}

Collectives™ on Stack Overflow

MySQLSyntaxErrorException in JAVA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related