hello,
I am working on a custom module using ExtJS for the UI. I need to do CRUD functions (with transactions) on the database, and I am struggling to understand how best to implement this functionality.
The demoModule employs the Table class and a Person class (bean). Other custom modules, however, use SQLFragments and SQLExecutor, and I've even seen some cases where Prepared Statements are used.
Is there a preferred way to go about this?
Many thanks,
bront |
| |
| jeckels responded: |
2014-09-08 16:31 |
Hi Bront,
All three approaches are reasonable.
The Table.insert()/update()/delete() approach with a simple Java class/bean works well when you want other code to be able to work with the class, and its fields map directly with what you're using in the database. It usually results in the least lines of code to accomplish the goal.
SQLFragment/SQLExecutor is a good approach when you need more control over the SQL you're generating. It's also used for operations that operate on multiple rows at a time.
PreparedStatements are good when you're dealing with many data rows and want the performance gain from being able to reuse the same statement with different values.
However, you might also be able to get away without needing any server-side Java code as well by using the LABKEY.Query.saveRows() or similar APIs from your 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.
Thanks,
Josh |
| |
| bront responded: |
2014-09-09 06:00 |
Josh,
That was exactly the sort of general overview I was hoping for. Thank you for the clarification.
bront |
| |
| bront responded: |
2015-04-24 12:05 |
hi,
I would like to re-open this question and ask for further information on the Table.insert()/update()/delete() approach.
1. Is it possible to return a value (e.g., the newly inserted rowid) from a Table.insert()?
2. Also, is it possible to map an insert()/update() to target multiple tables?
Thanks,
bront |
| |
| Jon (LabKey DevOps) responded: |
2015-04-24 16:48 |
Hi Bront,
I've provided the answers below your original questions:
Question 1. Is it possible to return a value (e.g., the newly inserted rowid) from a Table.insert()?
Answer: According to our developers, yes it can be done. The value is automatically pushed into the object that is returned from Table.insert().
Question 2. Also, is it possible to map an insert()/update() to target multiple tables?
Answer: Not in this case. You would have to make separate calls for each table you want to insert/update or create your own custom SQL code via JDBC connection to pull this off.
Regards,
Jon |
| |
| bront responded: |
2015-04-25 06:55 |
Jon,
Again, thank you so much for your quick answers. Very much appreciated.
bront |
| |
|
|