How does Java PreparedStatement work?

Question

I plan to replace re-executed Statement objects with PreparedStatement objects to improve performance. I use arguments such as the MySQL now() function and string variables.

Most of the PreparedStatement queries I saw contained constant values ​​(like 10 and strings like "New York" ) as arguments used for ? in requests. How can I use functions like now() , and variables as arguments? Do I need to use ? in queries instead of actual values? I am completely confused.

Answer 1

If you have variables use '?'

 int temp = 75; PreparedStatement pstmt = con.prepareStatement( "UPDATE test SET num = ?, due = now() "); pstmt.setInt(1, temp); pstmt.executeUpdate(): 

Creates an sql statute that looks like this:

 UPDATE test SET num = 75, due = now(); 

Answer 2

If you have a variable coming from user input, is it important that you use? not concatenate strings. Users can enter a string maliciously, and if you put the string directly in SQL, it can execute a command that you did not intend to.

I understand that this is too used, but he says perfectly:

Answer 3

You do not need to use placeholders in PreparedStatement. Sort of:

 PreparedStatement stmt = con.prepareStatement("select sysdate from dual"); 

will work fine. However, you cannot use a placeholder and then bind a function call to it. Something like this cannot be used to call the sysdate function:

 PreparedStatement stmt = con.prepareStatement("select ? from dual"); stmt.setSomethingOrOther(1, "sysdate"); 

Answer 4

If you call the built-in functions of your SQL server, use PreparedStatement .

If you call stored procedures loaded on your SQL server, use CallableStatement .

Use question marks as placeholders for the parameters of the function / procedure that you pass and the return values ​​of the function that you receive.

Answer 5

I developed a function that allows you to use named parameters in your SQL queries:

 private PreparedStatement generatePreparedStatement(String query, Map<String, Object> parameters) throws DatabaseException { String paramKey = ""; Object paramValue = null; PreparedStatement statement = null; Pattern paramRegex = null; Matcher paramMatcher = null; int paramIndex = 1; try { //Create the condition paramRegex = Pattern.compile("(:[\\d\\w_-]+)", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE); paramMatcher = paramRegex.matcher(query); statement = this.m_Connection.prepareStatement(paramMatcher.replaceAll("?"), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); //Check if there are parameters paramMatcher = paramRegex.matcher(query); while (paramMatcher.find()) { paramKey = paramMatcher.group().substring(1); if(parameters != null && parameters.containsKey(paramKey)) { //Add the parameter paramValue = parameters.get(paramKey); if (paramValue instanceof Date) { statement.setDate(paramIndex, (java.sql.Date)paramValue); } else if (paramValue instanceof Double) { statement.setDouble(paramIndex, (Double)paramValue); } else if (paramValue instanceof Long) { statement.setLong(paramIndex, (Long)paramValue); } else if (paramValue instanceof Integer) { statement.setInt(paramIndex, (Integer)paramValue); } else if (paramValue instanceof Boolean) { statement.setBoolean(paramIndex, (Boolean)paramValue); } else { statement.setString(paramIndex, paramValue.toString()); } } else { throw new DatabaseException("The parameter '" + paramKey + "' doesn't exists in the filter '" + query + "'"); } paramIndex++; } } catch (SQLException l_ex) { throw new DatabaseException(tag.lib.common.ExceptionUtils.getFullMessage(l_ex)); } return statement; } 

You can use it as follows:

 Map<String, Object> pars = new HashMap<>(); pars.put("name", "O'Really"); String sql = "SELECT * FROM TABLE WHERE NAME = :name";