You can specify an SQL expression so that you can search for items that are not stored in the database.
The format of SQL issued by Wagby is as follows.
SELECT 項目名 FROM モデルID WHERE 項目名=...
The "SQL expression" introduced here is to replace the above "item name" part with a specific expression.
As for the assumed usage method,When this "item name" is not stored in the database (it does not exist in the table), Executing SQL by applying a specific expression to the item name part.
(Part of) the SQL statement can be described in "Model item detail definition> details> database details> SQL expression".
Even if items are not saved in the database, we will retrieve the items using the SQL specified here.
The expression written by the developer is reflected in the formula element in the configuration file (hbm file) of O/R mapper software called Hibernate that is included in Wagby.[Hibernate manual ...]
Specifically, it is output to the formula element of the configuration file wagbyapp \ webapps \ [project name] \ WEB - INF \ classes \ [model name]. Hbm.xml.Here is an example.
<property name="item3_"> <formula>`num1`+1</formula> </property>
By modifying this configuration file, the developer can confirm the operation immediately by restarting the Wagby application.This method is recommended when trying expressions.
Once you have created a working SQL expression, it will be reflected in the settings in Designer.(Please do not build this without forgetting it.This configuration file will be recreated at build time.)
Suppose you have a staff model and a staff 2 model.Although it is a different model, if you have the same ID, we will refer to the same staff.(For example, if ID = "1000", it refers to the same person "Yamada".)
Here, there is a requirement to use the "address" item (reference linking item) of the staff model as a search.However, the "staff 2 ID" which is the source of the reference linkage item is not saved in the database and can not be searched.
In the "ID of staff 2" field, let's refer to the id field as an automatic calculation formula.Your display will be your ID.(It is assumed that the same value will be included in the id item of the staff model and the id item of the staff 2 model this time.)
In addition, make the following settings in the same SQL expression.
Interpret this expression when searching.Even if it is not stored in the database, it will use this value, so the search will succeed in this case.
We introduce an example of calculating with SQL expression without using Wagby's automatic calculation formula.
The model counttest has items calc1 and calc2, which is obtained by adding 1 to the value of the input value num1.We do not store any of them in the database.
Calc1 can not be used as a search item (because it is not stored in the database).However calc2 can be used as a search item.It is an item derived by calculation and you want to use it as a search condition, but do not want to save it in the databaseYou can use SQL expressions if you say.
Assume parent model parent and child model child.On the parent side, we prepare items that hold the number of child models (COUNT), but do not save them in the database.
So we use the following SQL expression for parent item 'number of children'.
(SELECT COUNT(*) FROM child WHERE child.parentid = `id`)
Since `id` in the SQL statement wants to use the column of the parent table, enclose it in quotes (`).This will give an alias corresponding to the parent table.
Child and child.parentid point to the parentid column of child table and child table respectively, so do not enclose it in quotes.
In the above SQL, use the LIKE operator to make a partial match search on the part of child.parentid = `id`.(We assume id is a string type.)
How to write the LIKE operator depends on the database to be used. For example, for SQL Server, it is as follows.
(SELECT COUNT(*) FROM child WHER child.parentid LIKE '%'+[parentid]+'%')
In the registration/update processing, the information before pressing the save button is stored in the database.By pressing the save button, the database value is updated.
So, by preparing items that hold database values, we will show you how to use the values before saving.
Here is an example of calculating the difference between the input value and the value before input.
We prepared the following items.
|item||Type||SQL expression||a formula|
The num2 item is not saved in DB and SQL expression is set.It is also read-only.
The num3 item is saved as DB and calculation formulas are set up.
In SQL Server, if Null value is included in the sort target item, NULL is treated as the smallest value, so it is displayed at the top of the list display.Here's how to change this and display the line containing NULL at the end (in the list display).
Prepare an item with the following values in the SQL expression and make this item the first in the sorting order.Secondly specify the target item.
(CASE WHEN [対象項目の列名] IS NULL THEN 1 ELSE 0 END)
SQL depends on the database you use.Please describe the issued SQL while checking it. For details on checking SQL, please refer to "Wagby Developer Network> Customizing with Java> Checking SQL to be issued".
When using a sub database, it is not possible to describe an SQL expression that crosses the main database and sub database.(Because the sub database is a separate database from the main database, it can not be handled in one SQL statement.In this case, you should use scripts.
In Wagby up to R7.10.3 you can not specify "<", ">" directly in SQL statement.In this case, an error "Caused by: org.hibernate.InvalidMappingException: Unable to read XML" occurs at run time. To specify the above characters, use the escaped characters "& lt;", "& gt;".