Can there be what other to saved methods, secure and fast in addition to saved procs. i understand only Hibernate. Can there be every other technologies like this?
Saved methods really are a spot to put code (SQL) which executes around the database, and so i comprehend the question to mean
"can there be every other method to package in the code which works on the database?"
You will find several solutions:
- There's little else that's quite just like a saved procedure, but you will find options that you consider.
- You can write all of your SQL as strings within your client code (java or whatever)
- It has various problems (lack of encapsulation, tight coupling -> harder maintenance), however, and it is not recommended.
- You could utilize an ORM for example NHibernate, which card inserts a layer involving the client logic and also the database. The ORM creates SQL to complete around the database. By having an ORM, it's harder to convey complex business logic compared to a saved procedure (sweeping generalisation!).
- A type of midway home is to define your personal data access layer (DAL) in java (or watever you are using) and it outside of the primary body of client code (separate classes / namespaces / etc.), to ensure that the consumer makes calls towards the DAL, and also the DAL translates these and transmits SQL towards the database, coming back the outcomes in the database to the customer.
Yes. you should use dynamic sql, however i personally like saved methods better.
1) If you are using MS SQL Server, it'll produce a query plan that ought to let the saved procedure to complete faster than simple dynamic sql.
2) It may be simpler an more efficient to repair a bug inside a saved procedure, expecially in case your application calls that procedure in a number of spots.
3) I've found it's nice to encapsulate database logic within the database instead of in embedded sql or application config file.
4) Creating saved procedure in to the database allows sql server to complete some syntax, and validation inspections at design time.
Hibernate is definitely an object/relational persistence service.
Saved procedure is really a subroutine in the relational database system.
If you would like option to Hibernate, you should check for iBatis for Spring
That you can do dynamic SQL as secure and fast as saved methods could be, you just need some work. Obviously, it requires some try to make saved methods secure and fast also.
A saved procedure is really a subroutine open to programs being able to access a relational database system. Saved methods (sometimes known as a proc, sproc, StoPro, or SP) are really saved within the database data dictionary.
Typical ways to use saved methods include data validation (integrated in to the database) or access control systems. In addition, saved methods are utilized to consolidate and centralize logic which was initially implemented in programs. Large or complex processing that may require the execution of countless SQL claims is moved into saved methods, and all sorts of programs call the methods only.
Saved methods act like user-defined functions (UDFs). The major difference is the fact that UDFs could be used like every other expression within SQL claims, whereas saved methods should be invoked while using CALL statement
I want to see this short article and reframe your question. Hibernate is not related to saved procs.
If you're your .Internet envorionment take a look at SubSonic.
Easy to use and very effective.
@Jeremy I have no idea what idiot chosen you lower. +1 from me
@Poster. Parameterized SQL (it may be Dynamic although not always) will execute with similar safety and efficiency as saved Procs.