Select * From java with preparedStatement

user_16508788 0
Viewed 143

I faced this problem today with my select SQL. This method is supposed to show data from database in tex tfields. I changed it from statement to preparedStatement, but I faced a problem.

 public Entreprise loadDataModify(String id) { 
    Entreprise e = new Entreprise();
    PreparedStatement stmt;

    try {
        String sql = "SELECT * FROM user WHERE mail=?";
        stmt = cnx.prepareStatement(sql);
        stmt.setString(1, id);
        

        ResultSet rst = stmt.executeQuery(sql);
        while (rst.next()) {
            stmt.setString(2, e.getNom());
            stmt.setString(3, e.getEmail());
            stmt.setString(4, e.getTel());
            stmt.setString(5, e.getOffre());

        }

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());

    }
    return e;

}

It shows i have problem with syntax and the output is " nu

prepared-statement jdbc sql java
3 Answers

You're calling the wrong method. Unlike Statement, when you're using a PreperedStatement you should first set the values for the parameters, and after you can call on that instance executeQuery() method. Also, it's a best practice to use try-with-resources, because a Statement or PreparedStament object is a Resource (a resource is a class that implements AutoCloseable interface) and you have to close it. Using try-with-resources, it's done automatically. The ResultSet instance is also a resource, but it's closed when the statement object is closed, so you don't have to close it explicitly. So, the best way to solve your problem will be:

 String selectAllByMail = "SELECT * FROM user WHERE mail=?";
    try (PreparedStatement prpStatement = connection.prepareStatement(selectAllByMail)) {
        // use prpStatement
        prpStatement.setString(1, id);
        ResultSet resultSet = prpStatement.executeQuery();
        while (resultSet.next()) {
            // process resultSet

        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
user_149734080

Your call to executeQuery() should not be passing the query string. Use this version:

String sql = "SELECT * FROM user WHERE mail=?";
stmt = cnx.prepareStatement(sql);
stmt.setString(1, id);

ResultSet rst = stmt.executeQuery();
while (rst.next()) {
    // process result set
}

Your current code is actually calling some overloaded Statement#executeQuery() method, which is not the version of the method which you want to be calling.

user_18632290

You are not filling your Enterprise object. And you are not using executeQuery() function correctly. As seen below, the parameter inside the brackets has been removed. PreparedStatements first of all need the values of the parameters (your ? in the query) and then the formed query has to be executed. If you give a String parameter to executeQuery() then the query in the brackets will be executed.

And the part where Enterprise is being filled could be seen below.

This would be the correct way:

public Entreprise loadDataModify(String id) { 
    Entreprise e = new Entreprise();
    PreparedStatement stmt;

    try {
        String sql = "SELECT * FROM user WHERE mail=?";
        stmt = cnx.prepareStatement(sql);
        stmt.setString(1, id);
        

        ResultSet rst = stmt.executeQuery();
        while (rst.next()) 
        {
            // rst keeps the data, so you have to traverse it and get the data from it in this way.
            e.setNom( rst.getString("HERE EITHER THE COLUMN NAME OR INDEX"));
            e.setEmail( rst.getString("HERE EITHER THE COLUMN NAME OR INDEX"));
            e.setTel( rst.getString("HERE EITHER THE COLUMN NAME OR INDEX"));
            e.setOffre( rst.getString("HERE EITHER THE COLUMN NAME OR INDEX"));

        }

    } catch (SQLException ex) {
        System.out.println(ex.getMessage());

    }
    return e;

}
user_79540210
Related