mysql
DELIMITER $$
CREATE
PROCEDURE `student`.`ceshi`(n INT,j CHAR(1))
BEGIN
IF j = 'h' THEN
SELECT * FROM blog WHERE id > n;
ELSE
SELECT * FROM blog ;
END IF;
END$$
DELIMITER ;
call ceshi(43,"a")
sqlserver
create proc Stu
@sname varchar(100)
as
begin
select name,age from student where name=@sname
end
go
exec Stu 'tom'
jfinal中 连接sqlserver
C3p0Plugin cp = new C3p0Plugin(getProperty("url"), getProperty("user"), getProperty("kwd"), getProperty("driverclass"));
me.add(cp);
// 配置ActiveRecord插件
ActiveRecordPlugin arp = new ActiveRecordPlugin(cp);
me.add(arp);
arp.setDialect(new SqlServerDialect());
arp.setContainerFactory(new CaseInsensitiveContainerFactory());
arp.addMapping("student", Student.class);
使用java下的jre,而不是jdk中的jre,使用tomcat,不使用jetty
create proc Stu
@sname varchar(100)
as
begin
select name,age from student where name=@sname
end
go
public class Exec implements ICallback{
@Override
public Object call(Connection conn) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("{call dbo.Stu(?)}");
stmt.setString(1, "tom");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("age"));
}
rs.close();
stmt.close();
return null;
}
}
在controller中
public void index()
{
Db.execute(new Exec());
}
create proc CheckAge
@sname varchar(100)
as
begin
if ((select age from student where name=@sname)>20)
return 1
else
return 0
end
go
public class ReturnState implements ICallback {
@Override
public Object call(Connection conn) throws SQLException {
// TODO Auto-generated method stub
CallableStatement csmt = conn.prepareCall("{?=call dbo.CheckAge(?)}");
csmt.registerOutParameter(1, java.sql.Types.INTEGER);
csmt.setString(2, "tom");
csmt.execute();
System.out.println(csmt.getInt(1));
return null;
}
}
create proc IsExist
@sname varchar(100),
@IsRight int output
as
begin
if exists (select name,age from student where name=@sname)
set @IsRight=1
else
set @IsRight=0
end
go
public class ReturnCallBack implements ICallback{
@Override
public Object call(Connection conn) throws SQLException {
CallableStatement callableStatement = conn.prepareCall("{call dbo.IsExist(?,?)}");
callableStatement.setString(1, "tom");
callableStatement.registerOutParameter(2, java.sql.Types.INTEGER);
callableStatement.execute();
System.out.println(callableStatement.getInt(2));
return null;
}
}
sqlserver 触发器
create trigger bbq
on tb_Money
after update
as begin
select * from tb_Money
end
sqlserver中使用事务
CREATE PROC ONETWOCCC
AS
BEGIN TRAN
update tb_Money set MyMoney = MyMoney - 30 where name = N'刘备'
update tb_Money set MyMoney = MyMoney + 30 where name = N'关羽'
if @@error<>0
BEGIN
ROLLBACK TRAN
PRINT '有错误,回滚';
END
ELSE
BEGIN COMMIT TRAN
PRINT '执行成功'
END
GO
EXEC ONETWOCCC
jfinal中使用事务
public void roll()
{
boolean succeed = Db. tx(new IAtom(){
public boolean run() throws SQLException {
int count = Db. update("update student set sex = sex+1 where id=2");
int count2 = Db. update("update student set sex = sex+3 where id=31");
return count == 1 && count2 == 1;
}});
}
@Before(Tx.class)
public void as()
{
int i = Db.update("update student set sex = sex+1 where id=2");
int s = Db.update("update student set name = null where id=31");
renderText(s+"");
}
个人更推荐第一种