Can we write Stored procedures in Java code & not store them in DB?

Question

I am aware that we have to store Stored Procedures in DB & just need to call them from Java code. But, I am looking for a solution if we can actually write them in java code & simply execute it in DB, just like we do it for normal sql queries.

Answer 1

Hi Anand,

In my opinion it is not feasible, as you mentioned, Stored Procedures are "stored" in the server, my concern is that even if you can do it, it will be troublesome as it will be hard coded in the app, one of the purpose of SP is for simplicity, when you need to edit it you only need to change it in server.

Why you need to do this anyway?

Answer 2

Well that's kinda complicated, but if at all possible I still prefer you to communicate this matter with the DBA team, after all you are all supposed to be a solid team. Especially you mentioned this is for production stage, I strongly recommend you to take this step.

Another feasible solution (a rather complicated then simplifying one) for this would be to create a folder in the client computer, and store text files containing sql queries, and you can code your app to allow access to it.

But then again that is still different with the centralized concept of SP, because they are only sql queries locally stored (not SPs). And you still have to deal with the hassle updating the local sql files. Unless you also code your app to allow downloading/sync the files, which, again, requires another server that roles as a storage for updated sql queries.

Answer 3

You could simply write in Java a Sql query that creates stored procedure then execute it to create on server. Then you could execute the procedure on database level and drop it (delete) once finished. I wouldn't recommend this approach though. It's an overkill and you lose benefits of stored procedures like for example performance. But that said, it's doable. For this to work though you'd need necessary permissions and not many dba's will grant those.

Answer 4

If I could write them in Java code, it's correct that it will be hard coded. But, with this, I can change it as & when required as this is on application end. But, if this is at DB end, there is a need of intervention from DBA team & Support team always apart from Application Dev team.

P.S. My intentions are to use this code for Production code base.