实验主题
在mysql中,
1. 创建一个user表,有两个字段,一个uid(主键,自增长),另一个uname;
2. 然后编写一个存储过程实现insert100条数据
3. 最后探索如何在Spring MVC中执行存储过程
开始
MySQL
创建User表
mysql
create table if not EXISTS `User`(
`uid` int auto_increment PRIMARY KEY,
`uname` VARCHAR(30) not null
)
编写存储过程
这里为了加深Mysql的函数语言理解,用三种循环方式分别实现
while do end while
mysql
create procedure insertUser()
BEGIN
declare i int default 0;
while i < 10 DO
insert into user(uname) VALUES(CONCAT("hello",i));
set i=i+1;
end while;
END
REPEAT……END REPEAT
mysql
create procedure insertUser2()
BEGIN
DECLARE i int default 0;
REPEAT
insert into user(uname) VALUES(CONCAT("hello",i));
set i=i+1;
UNTIL i > 10
END REPEAT;
END
loop……end loop
mysql
create procedure insertUser3()
BEGIN
DECLARE i int DEFAULT 0;
loop_label:LOOP
if i = 1 then
set i = i + 1;
iterate loop_label;
insert into user(uname) VALUES(CONCAT("hello",i));
set i = i + 1;
if i > 100 then
leave loop_label;
end if;
end loop;
END
那么 ,如何在Mysql里执行存储过程呢?
mysql
call insertUser3();
hibernate中执行存储过程
java
@Transactional
public void hello() throws HibernateException, SQLException{
String hql = "{Call insertUser3()}";
CallableStatement call = hibernateTemplate.getSessionFactory().openSession().connection().prepareCall(hql);
call.execute();
}