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.

Examples

moduleProperty('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.

Examples

javaConstant('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";
}

discussion

Discussion

Search 

Pages 

previousnext
 
expand all collapse all