% Sign in Java PreparedStatement

Short answer: Yes, if you correct the quote, then the two should give the same results. The percent sign will not be "stripped" of the prepared statement, more than any other symbol.

Longer answer: The question of prepared statement and one-time statement can be tricky. If you are only going to execute it once, the prepared statement will take longer because the database engine has to do all the settings for the prepared statement and then insert the values ​​and then turn it into the cache until the engine decides to flush it. In addition, the optimizer often cannot process the prepared statement as efficiently. The whole point of a prepared statement is that the optimizer parses the query and designs the query plan once. Suppose you say something like "pick a customer_name from a customer where customer_type =? And customer_zip =?". You have indexes for both type and zip. With a one-off statement (with real values ​​filled in,not question marks, of course) the query optimizer in many database engines can look at the distribution statistics for two fields and choose an index that yields a smaller set of records, then read it all sequentially and exclude records that fail the second test. With a prepared statement, it must select an index before knowing what values ​​will be supplied, so it can choose a less efficient index.what values ​​will be provided, so it can choose a less efficient index.what values ​​will be provided, so it can choose a less efficient index.

You should never ever feel the pain of death, ever write code that simply clicks quotes around an unknown value and inserts it into an SQL statement. Either use prepared statements or write a function that properly escapes any inline quotes. This function is trivial to write. I don't understand why JDBC doesn't include it, so you have to write it yourself and include it in every application. (This is especially true given that some SQL dialects have characters other than single quote that must be escaped.)

Here's an example of such a function in Java:

public static String q(String s)
{
  if (s==null)
    return "null";
  if (s.indexOf('\'')<0)
    return "'"+s+"'";
  int sl=s.length();
  char[] c2=new char[sl*2+2];
  c2[0]='\''; 
  int p2=1;
  for (int p=0;p<sl;++p)
  {
    char c=s.charAt(p);
    if (c=='\'')
      c2[p2++]=c;
    c2[p2++]=c;
  }
  c2[p2++]='\'';
  return new String(c2,0,p2);
}

      

        
        
        
      

    

(Note: I just edited this function from the version I pulled from my code to exclude some special cases that are not relevant here. Sorry if I brought up some minor bugs while doing this.)

I usually give it a very short name like "q", so I can just write:

String sql="select customer_name from customer where customer_type="+q(custType)
  +" and customer_zip="+q(custZip);

      

        
        
        
      

    

or something quick and easy. This is a violation of "give functions full and meaningful names", but I think it stands here where I can use the same function ten times in a single statement.

Then I overload it to accept dates and numbers and other special types and handle them accordingly.