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).

id passwrod username email
22222 password2 Luffy [email protected]
55555 password5 Sakuragi Hanamichi [email protected]
11111 password1 Momotaro Tsurugi [email protected]
33333 password3 Tickle [email protected]

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.

Recommended Posts

Talking about the merits of database bind variables ((1) Introduction)
Talk about the merits of database bind variables (② Merit: Prevention of SQL injection)
Summary about the introduction of Device
[Output] About the database
About the handling of Null
About the description of Docker-compose.yml
[Java] I thought about the merits and uses of "interface"
About the behavior of ruby Hash # ==
About the basics of Android development
About the role of the initialize method
Think about the 7 rules of Optional
[Ruby] Display the contents of variables
About the log level of java.util.logging.Logger
About the version of Docker's Node.js image
What is testing? ・ About the importance of testing
Item 57 Minimize the scope of local variables
About the operation of next () and nextLine ()
Item 57: Minimize the scope of local variables
About the initial display of Spring Framework
About the error message Invalid redeclaration of'***'
Output of the book "Introduction to Java"
About the treatment of BigDecimal (with reflection)
About the number of threads of Completable Future
About the mechanism of the Web and HTTP
About the meaning of type variables, E, T, etc. used in generics used in Java
Think about the combination of Servlet and Ajax
About the description order of Java system properties
[Ruby on Rails] Until the introduction of RSpec
About next () and nextLine () of the Scanner class
[Note] About the introduction to Swift practice Chapter 11