Talking about the merits of database bind variables ((1) Introduction)

2018/7/14 Full revision

====================================================================

** 0. Introduction **

It is zd6ir7. It will be first post. Thank you.

As the subject says, I would like to share with you the benefits of using database bind variables. I would like to mention in advance, but please note that some details have been omitted for the sake of clarity.

** 1. What is a "bind variable"? ** **

First of all, what is a "bind variable"? I will talk about (* 1). Some SQL statements issued to the DBMS vary depending on the value given from the outside. For example, consider a USER TABLE that holds the following user information (* 2).

Suppose you have SQL that retrieves information for that table based on your user ID and password.

SELECT * FROM USERTABLE WHERE ID='XXX' AND PASSWORD='YYY'; (XXX and YYY are tentative values.)

Of course, the user ID and password change depending on the user, and the value given to the column ID (XXX) and the value given to the column PASSWORD (YYY) naturally change.

Therefore, prepare a box for ID and a box for PASSWORD so that various values can be stored. Those "boxes" are called bind variables. It means preparing a box, that is, declaring a bind variable.

** 2. Declaration of "bind variable" **

So what exactly is it like to declare a bind variable? The code that issues the above SQL in a Java application is shown as an example.

//Declare SQL. For column id and password,?By inserting ", these are declared to be bind variables.
String sql = "select * from app.usertable where id = ? and password = ?";
//Prepare PreparedStatement to issue SQL that stores bind variables.
preparedStatement = connection.prepareStatement(sql);
//Assign a value for id. This "1" refers to the first bind variable id.
preparedStatement.setInt(1, 11111);
//Assign a value for password. This "2" refers to the second bind variable password.
preparedStatement.setString(2, "password1");
//Get the result.
resultSet = preparedStatement.executeQuery();

You can also issue SQL without declaring bind variables. Also in Java, the code example is shown below (* 3).

//Prepare a Statement.
statement = connection.createStatement();
//Declare SQL. Configure SQL with variable id and password.
String sql = "select * from app.usertable where id = 11111 and password = 'password1'";
//Get the result.
resultSet = statement.executeQuery(sql);

Why do I need to declare a bind variable when there is a way to not use a bind variable like this? From the next time, I would like to introduce two merits.

footnote

(※1) Also, regarding "bind variables", please refer to the following URL for easy-to-understand explanations. http://wa3.i-3-i.info/word12449.html

"Bind variables" are called by this name in Oracle, DB2, etc., but in MySQL, "user-defined variables" (https://dev.mysql.com/doc/refman/5.6/ja/user-variables. It seems that it is called html). Please refer to the manual for how the DBMS you are using is called.

(※2) The password is stored in the table as it is, but please forgive it as an example. Normally it is encoded and stored.

(※3) When issuing SQL with bind variables, Java uses the PreparedStatement class. On the other hand, if you don't, use the Statement class. The following literature points out that the former class is superior in terms of performance in that it can pool and reuse information about the executed SQL.

O'Reilly Japan --Java Performance https://www.oreilly.co.jp/books/9784873117188/

OCP: Oracle Certified Professional Java SE 8 Programmer II Study Guide: Exam 1Z0-809 http://as.wiley.com/WileyCDA/WileyTitle/productCd-1119067901,miniSiteCd-SYBEX.html

In addition, when posting the code example this time, I referred to the above document.