MySQL 5.0 stored procedure call

Using MySQL stored procedures in hibernate
1. I used the mysql-connector-java-5.0.0-beta-bin.jar (in fact, with the old mysql-connector-java-3.1.8-bin.jar can also call the Stored Procedure) this latest MySQL driver.
2. database I used a mysql-5.0.18-win32 and built a simple data table after installation.
The SQL statement is as follows:

Create Database testprocedure; Use testprocedure; Create Table testtable (ID int (11) auto_increment, content varchar (255), readcount int (11) default 0, primary key (ID )); Desc testtable; (check whether the setting is correct)

3. Create a dedicated user (optional ):

Grant select, delete, update, create, alter, execute on testtable. * To testprocedure @ "localhost" identified by "test ";

The username is testprocedure and the password is test. Note that execute is the permission for executing call procedure. Use this account in your hibernate configuration.
4. Create a stored procedure:
The SQL statement is as follows:

Java code:

Delimiter // (Note // It is the new command Terminator so that we can establish procedure) Create procedure readcountplusone (inputid INT) Begin Update testtable set readcount = readcount + 1 where id = inputid; End // (The storage process has been created) Delimiter; (The recovery command Terminator is ;)

5. Test the stored procedure:

Java code:

Insert into testtable values (null, 'test', 0 ); Select * From testtable; Call readcountplusone (1 ); Select * From testtable;

We should see that the original readcount is 0, the call is changed to 1, and 1 is added for each call.
If the execution fails, delete procedure and recreate it.
The DELETE command is drop procedure readcountplusone;
6. Start to use procedure in our hibernate + spring Support Project:
We will not talk about HBM ing. Named query is not used here. The configuration of hibernate + spring is not mentioned here. Many articles can be found.
My Dao is extends hibernatedao. You can refer to many other articles about spring hibernate support.
Let's create a method that is ugly (just for testing, you can refer to a good method). Suppose the pojo corresponding to testtable is testpojo, and its GETID () returns the value corresponding to the ID:

Java code:

Public void readcountplusone (final testpojo pojo ){ Gethibernatetemplate(cmd.exe cute (New hibernatecallback (){ Public object doinhibernate (session ){ Try { Connection conn = session. Connection (); String SQL = "{call readcountplusone (?)} "; Callablestatement stmt = conn. preparecall (SQL ); Stmt. setlong (1, pojo. GETID (). longvalue ()); Stmt.exe cute (); } Catch (exception e ){ If (log. isdebugenable ){ Log. debug ("Call Dao's readcountplusone () faild, with exception :"); E. printstacktrace (); } } Return NULL; } }); }

7. Then we can call the readcountplusone method in our bussiness to call a simple MySQL stored procedure through hibernate.