Ways to Work with SQL
Options for working with SQL from Java code:
Table Class
Using Table.insert()/update()/delete() with a simple Java class/bean works well when you want other code to be able to work with the class, and the class fields map directly with what you're using in the database. This approach usually results in the least lines of code to accomplish the goal. See the
demoModule for an example of this approach.
SQLFragment/SQLExecutor
SQLFragment/SQLExecutor is a good approach when you need more control over the SQL you're generating. It's also used for operations that work on multiple rows at a time.
Prepared SQL Statements
Use prepared statements when you're dealing with many data rows and want the performance gain from being able to reuse the same statement with different values.
Client-Side Options
You can also develop SQL applications without needing any server-side Java code by using the LABKEY.Query.saveRows() and related APIs from JavaScript code in the client. In this scenario, you'd expose your table as part of a schema, and rely on the default server implementation. This approach gives you the least control over the SQL that's actually used.
Utility Functions
LabKey Server provides a number of SQL function extensions to help Java module developers:
- access various properties
- keep Java code and SQL queries in sync
moduleProperty(MODULE_NAME, PROPERTY_NAME)
Returns a module property, based on the module and property names. Arguments are strings, so use single quotes not double.
ExamplesmoduleProperty('EHR','EHRStudyContainer')You can use the virtual "Site" schema to specify a full container path, such as '/home/someSubfolder' or '/Shared':
SELECT *
FROM Site.{substitutePath moduleProperty('EHR','EHRStudyContainer')}.study.myQuery
javaConstant(FULLY_QUALIFIED_CLASS_AND_FIELD_NAME)
Provides access to public static final variable values. The argument value should be a string.
Fields must be either be on classes in the java.lang package, or tagged with the org.labkey.api.query.Queryable annotation to indicate they allow access through this mechanism. Other fields types are not supported.
ExamplesjavaConstant('java.lang.Integer.MAX_VALUE')
javaConstant('org.labkey.mymodule.MyConstants.MYFIELD')To allow access to MYFIELD, tag the field with the annotation @Queryable:
public class MyConstants
{
@Queryable
public static final String MYFIELD = "some value";
}
