CODEWITHSUNDEEP
javasql
Doncarlito87
Doncarlito87

Reputation: 459

How can I only get 1 element from a SQL Database?

I have a small problem. I wrote a method in which I have an SQL query that should output a correct string after 2 parameters. When debugging, however, the result is not the right element. I don't know why this happens.

public static String findRightTemplate(String user_name, int template_id)
  throws Exception {


        Connection conn = DriverManager.getConnection(
                "xxx", "xxx", "xxx");
        Statement st = conn.createStatement();

        st = conn.createStatement();
        ResultSet rs = st.executeQuery(
          "SELECT template FROM templates " +
          "where template_id=template_id AND user_name=user_name"
          );

        String temp="";
        while(rs.next())
        {
            temp=rs.getString("template");
        }


        rs.close();
        st.close();
        conn.close();

I ask for the username and template_id and I just want to get an element out of the template column. The SQL query is correct. I've already tested that. But it seems that the query runs through all elements with the same username. As a result, I only get the last element and not the right one.

UPDATE

enter image description here

Upvotes: 0

Views: 559

Answers (1)

stevecross
stevecross

Reputation: 5684

Currently you do not use the method parameters inside your query. As already suggested you should use a PreparedStatement to fix that. You should basically do the following:

public static String findRightTemplate(String userName, int templateId) throws SQLException {
    try (final Connection connection = DriverManager.getConnection("...")) {
        final PreparedStatement preparedStatement = connection.prepareStatement(
                "SELECT template " +
                "FROM templates " +
                "WHERE user_name = ? " +
                "AND template_id = ? " +
                "LIMIT 1"
        );

        preparedStatement.setString(1, userName);
        preparedStatement.setInt(2, templateId);

        final ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            return resultSet.getString(1);
        }
    }

    return null;
}

If you do not use a PreparedStatement and build the query manually as suggested in the comments your application could be vulnerable to SQL injection attacks.

Upvotes: 3

Related Questions