How to build a little tricky with dynamic SQL query generation

background

I was reviewing the code of a newcomer (PG history about 3 weeks?), And I was generating a slightly interesting SQL query, so share the story. I'm talking about using some kind of OR mapper or Doma, but leave that out.

Premise

What I want to do is that there are multiple input boxes on the search screen, and the items entered by the user are added to the SQL search conditions.

Unless you're using an Active Record-like OR mapper, you're not using a 2-way-SQL library like Doma.

If you feel like it, you may cut out SQL to an external file, but I think that the usual idea for beginners is to dynamically generate strings and build SQL.

Therefore, you would normally generate a SQL query with string concatenation in your code.

** This schema: ** http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05780-02/scripts.htm#Cihgfecd

Example: A query when you want to search for "Sales are included in the job category" and "Salary is 6 million yen or more" from the employee table. Let me omit the escape setting in the Like clause.

SQL you want to generate


SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || ? || '%';     --I want to add it to the condition if specified by the user
AND jb.SALARY >= ?                         --I want to add it to the condition if specified by the user
;

Usually, the presence or absence of string concatenation is often branched from the presence or absence of a value. Aside from using StringBuilder, I don't know.

python


String sql = ~ ; //abridgement. SQL assumptions up to the first line of the WHERE clause.

if (jobTitle != null && jobTitle.length >= 0) {
  sql += "AND jb.JOB_TITLE LIKE '%' || ? || '%'"
}

if (minSalary != null && minSalaly.length >= 0) {
  sql += "AND jb.SALARY >= ?"
}

However, a newcomer was doing this.

jobTitleComment = "";
minSalaryComment = "";
if (jobTitle != null && jobTitle.length >= 0) {
 jobTitleComment = "--";
}

if (minSalary != null && minSalaly.length >= 0) {
 minSalaryComment = "--";
}

String sql = "SELECT"
+ " em.EMPLOYEE_ID"
+ ", em.FIRST_NAME"
+ ", em.LAST_NAME"
+ "FROM"
+ "  EMPLOYEES em"
+ ", INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)"
+ ", INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)"
+ "WHERE 1 = 1"
+ "AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE"
+ jobTitleComment + "AND jb.JOB_TITLE LIKE '%' || ? || '%'"  //point
+ minSalaryComment + "AND jb.SALARY >= ?"                     //point
;

I couldn't understand the intention when I looked at Pat, and it got stuck for a few seconds, but when I looked closely, I couldn't understand it. I'm trying to switch whether to use SQL search conditions with "-" indicating a comment.

For example, an example of SQL generation after execution when neither condition is set is as follows.

When the search condition is not set


SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
-- AND jb.JOB_TITLE LIKE '%' || ? || '%'  /*★ Both will be commented out*/
-- AND jb.SALARY >= ?                                          /*★ Both will be commented out*/
;

Since the search condition is empty, the refinement condition of the Where clause is commented out and invalidated.

Next is an example of generation when only the job name is set.

When only the job name is set


SELECT
 em.EMPLOYEE_ID
, em.FIRST_NAME
, em.LAST_NAME
FROM
  EMPLOYEES em
, INNER JOIN JOB_HISTORY jh on (em.EMPLOYEE_ID = jh.EMPLOYEE_ID)
, INNER JOIN JOBS jb on (jh.JOB_ID = jb.JOB_ID)
WHERE 1 = 1
AND CURRENT_DATE BETWEEN jh.START_DATE AND jh.END_DATE
AND jb.JOB_TITLE LIKE '%' || 'sales' || '%'  /*This is valid*/
-- AND jb.SALARY >= '??'                      /*This remains commented out*/
;

If the search condition exists, the corresponding refinement condition in the Where clause is valid. I thought it was surprisingly easy to see.

Summary

Recommended Posts

How to build a little tricky with dynamic SQL query generation
How to build Rails 6 environment with Docker
How to build a Ruby on Rails development environment with Docker (Rails 6.x)
How to build a Ruby on Rails development environment with Docker (Rails 5.x)
Create a simple search app with Spring Boot
[Rails] How to search by multiple values ​​with LIKE
Create a playground with Xcode 12
I tried to implement flexible OR mapping with MyBatis Dynamic SQL
Create a restaurant search app with IBM Watson + Gurunavi API (with source)
How to build a little tricky with dynamic SQL query generation
How to build API with GraphQL and Rails
[Rails] How to build an environment with Docker
How to build a Pytorch environment on Ubuntu
How to make a factory with a model with polymorphic association
How to jump from Eclipse Java to a SQL file
How to build docker environment with Gradle for intelliJ
[Rails] [Docker] Copy and paste is OK! How to build a Rails development environment with Docker
How to delete a new_record object built with Rails
How to manually generate a JWT with Rails Knock
[How to insert a video in haml with Rails]
How to quit Docker for Mac and build a Docker development environment with Ubuntu + Vagrant
How to build Java development environment with VS Code
How to query Array in jsonb with Rails + postgres
How to build android-midi-lib
How to get started with creating a Rails app
[Java] How to start a new line with StringBuilder
How to reduce the load on the program even a little when combining characters with JAVA
How to take a screenshot with the Android Studio emulator
How to request a CSV file as JSON with jMeter
[Ruby] How to split each GraphQL query into a file
How to divide a two-dimensional array into four with ruby
How to use a foreign key with FactoryBot ~ Another solution
How to build [TypeScript + Vue + Express + MySQL] environment with Docker ~ Express ~
Learning Ruby with AtCoder 13 How to make a two-dimensional array
How to test a private method with RSpec for yourself
How to move another class with a button action of another class.
A memo to build Jitsi Meet on Azure with docker-compose
How to build Rails, Postgres, ElasticSearch development environment with Docker
Build a Node-RED environment with Docker to move and understand
Mapping to a class with a value object in How to MyBatis
How to join a table without using DBFlute and sql
How to set up a proxy with authentication in Feign
How to register as a customer with Square using Tomcat
How to leave a comment
How to number (number) with html.erb
How to update with activerecord-import
How to insert a video
How to create a method
How to build an environment with Docker, which is the minimum required to start a Rails application
How to cancel cell merging within a specified range with POI
How to make a jar file with no dependencies in Maven
[Rails 6] How to create a dynamic form input screen using cocoon
How to build [TypeScript + Vue + Express + MySQL] environment with Docker ~ MySQL edition ~
I tried to implement flexible OR mapping with MyBatis Dynamic SQL
Try to make a cross-platform application with JRuby (jar file generation)
How to run a job with docker login in AWS batch
How to rename a model with foreign key constraints in Rails
How to open a script file from Ubuntu with VS code
How to build Rails + Vue + MySQL environment with Docker [2020/09 latest version]
How to build [TypeScript + Vue + Express + MySQL] environment with Docker ~ Sequelize ~
You cannot change the project facet version from a dynamic web module to x.x. How to deal with
How to scroll horizontally with ScrollView
Build a Java project with Gradle
How to add columns to a table
Build a Node.js environment with Docker
How to get started with slim