Support > Repository > Definition of model items > Database details - SQL expressions
ja | en

You can specify an SQL expression so that you can search for items that are not stored in the database. R7.6

The format of SQL issued by Wagby is as follows.

SELECT item name FROM model ID WHERE item name=...

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.

Definition method

(Part of) the SQL statement can be described in "Model item detail definition> details> database details> SQL expression".

Figure 1 SQL expression setting field

Even if items are not saved in the database, we will retrieve the items using the SQL specified here.

This setting depends on the database you use.The description on this page is SQL for MySQL unless otherwise noted.

Development Tips

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

Fig. 2 staff model and staff 2 model

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.

Setting SQL expression

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.

`ID`

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.

Figure 3 Image of SQL expression setting
In an SQL expression, enclose it with quotes (`) to give an alias corresponding to its table.In this example it is treated as staff.ID.(Staff2.ID is not)

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.

`num1`+1
Figure 4 Calculation by SQL Expression

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.

As for the calc1 item, when the input value num1 is changed on the registration/update screen, the value is immediately incremented by 1 and redrawn. However, the calc2 item must be saved before the value is reflected.In addition, it is possible to input the calc2 item as it is in the state where the automatic calculation formula (of Wagby) is not set, so please set it separately as "read only".Alternatively, you can also specify a calculation expression in the calc2 item.In this case, it will be redrawn instantly with change of num1.

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.

Figure 5 Relationship between parent and child

So we use the following SQL expression for parent item 'number of children'.

(SELECT COUNT(*) FROM child WHERE child.parentid = `id`)
This expression does not work with the built-in database (HSQLDB).I am confirming that it works with MySQL.The character enclosing the column name `is a delimiter that can be used in MySQL.

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.

The parent model isYou may need to disable caching.When a child model is added, always use the calculated value instead of using the cache for detailed display of the parent model.(Do not display old data.)

Using the LIKE operator

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 SQL Server, the character surrounding the column name is described as [column name].

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.

Example of execution

Here is an example of calculating the difference between the input value and the value before input.

Figure 6 Calculation example (1)
Calculation example (2)

Setting method

We prepared the following items.

item Type SQL expression a formula
num1 Numerical value
num2 Numerical value `num1`
num3 Numerical value ${num1}-${num2}

The num2 item is not saved in DB and SQL expression is set.It is also read-only.

Figure 8 Setting SQL expression

The num3 item is saved as DB and calculation formulas are set up.

Figure 9 Calculation formula setting

For SQL Server

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 [column name of target item] IS NULL THEN 1 ELSE 0 END)
This setting also corresponds to "Sort by item by clicking items on the list screen".

SQL depends on the database you use.Please describe the issued SQL while checking it.As for how to confirm SQL "Wagby Developer Network> customization using Java> Confirm issued SQLPlease read.

Can not describe SQL crossing the database

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 such a casescriptYou may want to use.

Handling of inequality sign (until R 7.10.3)

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;".

This restriction has been resolved in Wagby after R7.11.