Hibernate调用SQLserver存储过程

首先我们在sqlserver中写一个存储过程，也就是一个简单的查询。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE my_procedure

AS
BEGIN

	SET NOCOUNT ON;


	select * from t_gj_saleorder 
END
GO

根据自己表的情况，修改上面的t_gj_saleorder.

下面是java代码，我的是spring+hibernate框架，所以用获取bean的方式拿到sessionFactory。

package com.smart.oo.service.trigger;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts2.convention.annotation.Results;
import org.apache.xbean.spring.context.ClassPathXmlApplicationContext;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.context.ApplicationContext;

public class MainTest {
	@SuppressWarnings("deprecation")
	public static void main(String[] args) throws Exception {
		ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
//		SyncNationOrderTrigger syncNationOrderTrigger=(SyncNationOrderTrigger) applicationContext.getBean("sessionFactory");
//		syncNationOrderTrigger.syncOrder();
		SessionFactory sessionFactory=(SessionFactory) applicationContext.getBean("sessionFactory");
		Session session=sessionFactory.openSession();
		Connection connection=session.connection();
		String sql="{call my_procedure}";
		CallableStatement cs=connection.prepareCall(sql);
		ResultSet resultSet=cs.executeQuery();
		while(resultSet.next()){
			System.out.println(resultSet.getString(1)+","+resultSet.getString(2));
		}
	}
}

下面我们来稍微做一下修改，做一个可以有传入传出参数的存储过程：

USE [db_bboo]
GO
/****** Object:  StoredProcedure [dbo].[my_procedure]    Script Date: 07/08/2016 17:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[my_procedure](@id varchar(25),@test varchar(20) out)
	-- Add the parameters for the stored procedure here
	--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	--<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from t_gj_saleorder where id=@id
	
	set @test='just_return'
END

然后是java代码：

package com.smart.oo.service.trigger;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts2.convention.annotation.Results;
import org.apache.xbean.spring.context.ClassPathXmlApplicationContext;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.context.ApplicationContext;

public class MainTest {
	@SuppressWarnings("deprecation")
	public static void main(String[] args) throws Exception {
		ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
//		SyncNationOrderTrigger syncNationOrderTrigger=(SyncNationOrderTrigger) applicationContext.getBean("sessionFactory");
//		syncNationOrderTrigger.syncOrder();
		SessionFactory sessionFactory=(SessionFactory) applicationContext.getBean("sessionFactory");
		Session session=sessionFactory.openSession();
		Connection connection=session.connection();
		String sql="{call my_procedure(?,?)}";
		CallableStatement cs=connection.prepareCall(sql);
		cs.setObject(1, "1467871443546000050");
		cs.registerOutParameter(2, java.sql.Types.VARCHAR);//设置第二个参数为输出参数
		ResultSet resultSet=cs.executeQuery();
		while(resultSet.next()){
			System.out.println(resultSet.getString(1)+","+resultSet.getString(2)+","+resultSet.getString(3));
		}
		String param2 = cs.getString(2);//获取输出参数,也就是第二个参数
		System.out.println("--------"+param2+"---------");
	}
}