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