Ways to Work with SQL

Options for working with SQL from Java code:

Table Class

Using org.labkey.api.data.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

org.labkey.api.data.SQLFragment and org.labkey.api.data.SQLExecutor are good approaches when you need more control over the SQL you're generating. They are also used for operations that work on multiple rows at a time.

Prepared SQL Statements

Use prepared statements (java.sql.PreparedStatement) 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.

Related Topics

Discussion

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand all collapse all