This topic includes several options for working with SQL from Java code. These approaches can be used to run raw SQL to the database.
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.
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
