java中使用存储过程

create database Personnel_training_db
go
use Personnel_training_db
go

create table dept(

deptid int primary key identity(1,1),
deptname varchar(20) not null,

remark varchar(20) not null

)
go

insert into dept values('教职部','')

--1.创建存储过程 查询

create proc proc_dept
@deptname varchar(20)
as
select * from dept where deptname=@deptname
go
--执行存储过程
exec proc_dept '教职部'

--2.存储过程 查询
create proc proc_dept2
@deptname varchar(20)
as
exec('select * from dept where deptname='''+@deptname+'''')
go

proc_dept2 '教职部'


--存储过程 添加
create proc proc_add_dept
@deptname varchar(20),
@remark varchar(20)
as
insert into dept values(@deptname,@remark)
go

exec proc_add_dept '学术部','这个不能还有点意思'

--存储过程删除
create proc proc_del_deptid
@deptid varchar(20)
as
exec('delete from dept where deptid in('+@deptid+')')
go

proc_del_deptid '1,2'


package com.cn.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.concurrent.ExecutionException;

public class ProcJDBC {

static {
try {
//加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
//用户名 密码
public static String name="sa";
public static String password="123456";
public static String url="jdbc:sqlserver://localhost:1433;databaseName=Personnel_training_db";

//获取链接
public static Connection connection() throws SQLException{
Connection conn=null;
conn=DriverManager.getConnection(url, name, password);
return conn;
}
//利用传统的jdbc 调用存储过程 查询 参数 传递
public static void main1(String[] args) throws Exception {
//获取链接
Connection conn=ProcJDBC.connection();
PreparedStatement psment=conn.prepareStatement("{call proc_dept(?)}");
psment.setString(1, "教职部");
ResultSet re =psment.executeQuery();
while(re.next()){
System.out.println(re.getString("deptname"));
}
re.close();
conn.close();
psment.close();
}
//添加
public static void main2(String[] args) throws Exception {
//获取链接
Connection conn=ProcJDBC.connection();
CallableStatement psment=conn.prepareCall("{call proc_add_dept(?,?)}");
psment.setString(1, "问问");
psment.setString(2, "这个坑爹的部门");
boolean bool=psment.execute();
if(bool==false){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
conn.close();
psment.close();
}
//删除
public static void main(String[] args) throws Exception {
//获取链接
Connection conn=ProcJDBC.connection();
CallableStatement psment=conn.prepareCall("{call proc_del_deptid(?)}");
psment.setString(1, "3,4,5");
boolean bool=psment.execute();
if(bool==false){
System.out.println("delete success");
}else{
System.out.println("delete error");
}
conn.close();
psment.close();
}
}


Hibernate中使用存储过程
public class TestMain {

public static void main(String[] args) {
Configuration config = new Configuration().configure("hibernate.cfg.xml");
SessionFactory factory = config.buildSessionFactory();
Session session = factory.openSession();
//Query query = session.getNamedQuery("select_dept");
//使用本地sql
Query query = session.createSQLQuery("{call proc_dept(?)}");
query.setString(0,"学术部");
List<Object[]> list=query.list();
for(Object[] obj:list){
System.out.println("deptid："+obj[0]+"deptname:"+obj[1]+"deptremak:"+obj[2]);
}
session.close();
}
}

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="com.ssw.entity.Dept" table="dept" schema="dbo" catalog="Personnel_training_db">
<id name="deptid" type="java.lang.Integer">
<column name="deptid" />
<generator class="identity" />
</id>
<property name="deptname" type="java.lang.String">
<column name="deptname" length="20"/>
</property>
<property name="remark" type="java.lang.String">
<column name="remark" length="20" />
</property>
</class>
<!-- 外部配置查询 存储过程-->
<sql-query name="select_dept" callable="true">
<return alias="dept" class="com.ssw.entity.Dept">
<return-property name="deptid" column="deptid"/>
<return-property name="deptname" column="deptname"/>
<return-property name="deptremark" column="remark"/>
</return>
</sql-query>
</hibernate-mapping>