IQL is another SQL generator for Java
A few years ago, I started developing one large PHP project, which only did what it wrote, read, and updated tons of data in SQL. Immediately, it was decided to write a SQL query designer for this project, which then made my life much easier and to this day helps me and my colleagues in their daily work. The fact is that I cannot put this constructor in public access, because the project does not belong to me, and this constructor is strongly tied to our framework.
In this connection, I decided to develop its Java equivalent, in the hope that it will serve someone in good stead, since not everyone needs ORM, and JOOQ seems too complicated
Benefits
disadvantages
')
A little bit about how it works and how it is better PreparedStatement
In PreparedStatement, in my opinion, one drawback is not quite convenient data insertion via setType (index, value), and the IQL class uses for this type reduction when declaring fields that will be used for value substitution. Used abbreviations of data types:
An example of using type abbrevs when inserting data:
Task:
Create a query to insert multiple rows into the users table, name (VARCHAR), birth_date (INTEGER) and level (INTEGER) fields
Decision
As a result of executing this code, ps will contain the following pending code:
Data update works similarly:
Which will generate the following SQL code:
It should be particularly noted that if the update does not specify conditions (by calling the where () or whereId () method), then an InsecureOperationException will be thrown. The same fate will wait for delete without a condition.
Not without a virtual operation UPSERT, which updates the data if at least one condition is specified and adds a line if there are no conditions. It works like update and insert:
Will create the following code:
And if you add to the same code where () or whereId (), then the query changes completely:
The request will look like this:
Sampling and combining tables
Simple sample:
Generates the simplest:
We can complicate the selection by adding selectable fields and combining several tables:
As I wrote above, thanks to the scheduling and free order of commands, the same code can be written as follows:
However, you need to use setTable () here to set the active table. In the first case, a variant of the addTable () method with the addition of only one table automatically sets it active.
This code will generate the following query:
Types% s,% t, StringFilter interface and additional string handling
For additional string processing, the IQL class provides the StringFilter functional interface and static IQL class methods:
Suppose we are faced with the task of replacing HTML tags with HTML sequences, for example, replacing <with <lt ;.
For this we can use the following construction:
After that, all% s strings inserted in the query will be processed in this way:
The resulting SQL code:
In general, that's all. I would be glad if this class seemed useful to someone, and I would also appreciate constructive criticism.
More examples and more complete documentation describing all the methods can be obtained from the wiki on github.
→ GitHub
UPD: Corrected incorrect method names
In this connection, I decided to develop its Java equivalent, in the hope that it will serve someone in good stead, since not everyone needs ORM, and JOOQ seems too complicated
Benefits
- Eliminate syntax errors when creating a query
- Simplify multiline data insertion - no need to concatenate queries - just call the insert () method for each new line
- Throw an exception if DELETE or UPDATE are executed without WHERE
- A UPSERT operation that inserts or updates an entry if WHERE is specified.
- Possibility of additional processing of lines before insertion (using a special functional interface)
- Automatic date parsing from int, from String, or from Date object
- The order of the commands does not matter, for example GROUP BY can be specified after LIMIT, WHERE after ORDER BY, and JOIN before SELECT
- Simplification of work with input data - you do not need to use the setString (), setInt () methods and other data entry methods from PreparedStatment, you just need to enter the corresponding variables
- Syntax highlighting when using IDE (which, as a rule, does not directly record SQL queries)
- Each query has a similar structure and begins with the input of tables, for example, for a SELECT query, the necessary tables are first added using the addTable () method; the same should be done for UPDATE, INSERT, DELETE queries
- May return generated SQL, as opposed to PreparedStatement
- Insertion and update queries have a similar structure, unlike SQL, where working with INSERT and UPDATE statements is completely different.
disadvantages
')
- Suitable mainly for CRUD without complex logic
- Does not support all data types.
- You cannot use functions and the usual selection of fields in one request (I’ll fix it in the future)
A little bit about how it works and how it is better PreparedStatement
In PreparedStatement, in my opinion, one drawback is not quite convenient data insertion via setType (index, value), and the IQL class uses for this type reduction when declaring fields that will be used for value substitution. Used abbreviations of data types:
| IQL type | Type when creating the table | Type when inserted into query |
| % s | VARCHAR (255) | String, additionally processed before insertion with the help of the StringFilter functional interface specified by the static setStringFilte () method |
| % v | VARCHAR (255) | String without additional processing (Only escaping a single quote character) |
| % t | TEXT | String, with additional processing before insertion using the StringFilter functional interface, specified by the static setTextFilter () method |
| % i | INTEGER | int, without additional processing |
| % d | INTEGER | int, Date, or String. In the latter case, the String will be processed using SimpleDateFormat and formatting string specified by the static method setDateFormat () |
| % f | Float | float or String |
| % b | Bool | boolean or String |
An example of using type abbrevs when inserting data:
Task:
Create a query to insert multiple rows into the users table, name (VARCHAR), birth_date (INTEGER) and level (INTEGER) fields
Decision
Connection con = DriverManager.getConnection("url", "login", "pass"); IQL iql = new IQL(con); // IQL iql. addTable("users"). // users setInsertFields("name %s", "birth_date %d", "level %i"); // /* ( getStatement getSQL) this, , */ iql.insert("User1", "12.12.2012", 4); // iql.insert("User2", 1494968400, 3); // PreparedStatement ps = iql.getStatement(); // PreparedStatement As a result of executing this code, ps will contain the following pending code:
INSERT INTO `users`(`name`, `birth_date`, `level`) VALUES ('User1', 1355256000, 4), ('User2', 1494968400, 3) Data update works similarly:
IQL iql = new IQL(con); iql. addTable("users"). // setUpdateFields("name %s", "birth_date %d"). // update("admin", "11.04.1986"). // where("level %i", IQL.EQUAL, 3); // String SQL = iql.getSQL(); // sql- Which will generate the following SQL code:
UPDATE `users` SET `name` = 'admin', `birth_date` = 513547200 WHERE `users`.`level` = 3 It should be particularly noted that if the update does not specify conditions (by calling the where () or whereId () method), then an InsecureOperationException will be thrown. The same fate will wait for delete without a condition.
Not without a virtual operation UPSERT, which updates the data if at least one condition is specified and adds a line if there are no conditions. It works like update and insert:
IQL iql = new IQL(con); iql.addTable("users"). setUpsertFields("name %s", "birth_date %d"). // upsert("guest", "12.12.2012"); // String SQL = iql.getSQL(); Will create the following code:
INSERT INTO `users`(`name`, `birth_date`) VALUES ('guest', 1355256000) And if you add to the same code where () or whereId (), then the query changes completely:
IQL iql = new IQL(con); iql.addTable("users"). setUpsertFields("name %s", "birth_date %d"). // upsert("guest", "12.12.2012"). // whereId(122); // String SQL = iql.getSQL(); The request will look like this:
UPDATE `users` SET `name` = 'guest', `birth_date` = 1355256000 WHERE `users`.`id` = 122 Sampling and combining tables
Simple sample:
String SQL = new IQL(con).addTable("table1").select().getSQL(); Generates the simplest:
SELECT * FROM `table1` We can complicate the selection by adding selectable fields and combining several tables:
IQL iql = new IQL(con); iql. // domains, subdomain domain, domain NULL addTable("domains").select("subdomain", "domain").where("domain %s", IQL.ISNTNULL). // orgs, org_name name, org_address address addTable("orgs").select("org_name name", "org_address address"). // org_name orgs LIKE "%%" where("org_name %s", IQL.LIKE, "%%"); // 2 (orgs) 1 (domains) orgs.id = domains.org_id iql.join(2, "id", 1, "org_id"); String SQL = iql.getSQL(); As I wrote above, thanks to the scheduling and free order of commands, the same code can be written as follows:
IQL iql = new IQL(con); iql.addTable("domains", "orgs"). join(2, "id", 1, "org_id"). setTable(1).select("subdomain", "domain"). setTable(2).select("org_name name", "org_address address"). setTable(1).where("domain %s", IQL.ISNTNULL). setTable(2).where("org_name %s", IQL.LIKE, "%%"); String SQL = iql.getSQL(); However, you need to use setTable () here to set the active table. In the first case, a variant of the addTable () method with the addition of only one table automatically sets it active.
This code will generate the following query:
SELECT `domains`.`subdomain` AS `domains_subdomain`, `domains`.`domain` AS `domains_domain`, `orgs`.`org_name` AS `name`, `orgs`.`org_address` AS `address` FROM `orgs` JOIN `domains` ON `orgs`.`id` = `domains`.`org_id` WHERE `domains`.`domain` IS NOT NULL AND `orgs`.`org_name` LIKE '%%' Types% s,% t, StringFilter interface and additional string handling
For additional string processing, the IQL class provides the StringFilter functional interface and static IQL class methods:
| setStringFilter (StringFilter sf) | filter string% s before inserting |
| setTextFilter (StringFilter sf) | filter string% t before inserting |
Suppose we are faced with the task of replacing HTML tags with HTML sequences, for example, replacing <with <lt ;.
For this we can use the following construction:
IQL.setStringFilter( (s) -> s.replace("&","&").replace("<", "<").replace(">",">") ); After that, all% s strings inserted in the query will be processed in this way:
IQL iql = new IQL(con); iql.addTable("myvals"); iql.setInsertFields("price %f", "name %s"); iql.insert(3.22, "<h1></h1>"); String SQL = iql.getSQL(); The resulting SQL code:
INSERT INTO `myvals`(`price`, `name`) VALUES (3.22, '<h1></h1>') In general, that's all. I would be glad if this class seemed useful to someone, and I would also appreciate constructive criticism.
More examples and more complete documentation describing all the methods can be obtained from the wiki on github.
→ GitHub
UPD: Corrected incorrect method names
Source: https://habr.com/ru/post/329280/
All Articles