mysql - 如何使用hibernate获取Mysql存储过程的输出参数？

Question

我有一个 MySql 存储过程，它包含两个输入参数(用户名和密码)和一个输出参数(用户状态)。我无法使用 Hibernate 4 获取输出参数，我引用以下链接 http://www.mkyong.com/hibernate/how-to-call-store-procedure-in-hibernate/

hibernate 代码

Session session = sessionFactory.openSession();  
Query query = session.createSQLQuery("CALL SP_Login_Authentication(:u_name,:p_word,:output)");
query.setParameter("u_name", username);  
query.setParameter("p_word", pwd);  
List return1 = query.list();

存储过程

DELIMITER $$
USE `CP`$$
DROP PROCEDURE IF EXISTS `SP_Login_Authentication`$$
CREATE PROCEDURE `SP_Login_Authentication`(IN u_name VARCHAR(255),IN p_word VARCHAR(255),OUT output INT )
BEGIN 
DECLARE user1 INT DEFAULT 0;
DECLARE password1 INT DEFAULT 0;
DECLARE status1 INT ;
SELECT COUNT(*) INTO user1
FROM users WHERE user_name=u_name;
SELECT COUNT(DISTINCT PASSWORD) INTO password1
FROM users WHERE PASSWORD=p_word;
SET status1=(SELECT STATUS FROM users WHERE PASSWORD=p_word AND user_name=u_name);
IF(user1 = 0) THEN
SET output = -3;
ELSEIF (user1 = 1 AND password1 = 0) THEN
SET output = -2;
ELSEIF (user1 = 1 AND password1 = 1 AND status1=1 ) THEN
SET output = 1;
ELSEIF (user1 = 1 AND password1 = 1 AND status1=0 ) THEN
SET output = 0;
ELSEIF (user1 = 1 AND password1 = 1 AND status1=-1 ) THEN
SET output =-1;
END IF;
END$$
DELIMITER ; 

Answer 1

您可以使用 datasource-proxy 来做到这一点，正如我在 this post 中描述的那样.

假设您的应用程序需要一个“dataSource”@Resource，这就是您可以配置 ttddyy 代理的方式:

<bean id="testDataSource" class="bitronix.tm.resource.jdbc.PoolingDataSource" init-method="init"
  destroy-method="close">
    <property name="className" value="bitronix.tm.resource.jdbc.lrc.LrcXADataSource"/>
    <property name="uniqueName" value="testDataSource"/>
    <property name="minPoolSize" value="0"/>
    <property name="maxPoolSize" value="5"/>
    <property name="allowLocalTransactions" value="false" />
    <property name="driverProperties">
        <props>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
            <prop key="url">${jdbc.url}</prop>
            <prop key="driverClassName">${jdbc.driverClassName}</prop>
        </props>
    </property>
</bean>

<bean id="proxyDataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
    <property name="dataSource" ref="testDataSource"/>
    <property name="listener">
        <bean class="net.ttddyy.dsproxy.listener.ChainListener">
            <property name="listeners">
                <list>
                    <bean class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener">
                        <property name="logLevel" value="INFO"/>
                    </bean>
                    <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
                </list>
            </property>
        </bean>
    </property>
</bean>

<alias name="proxyDataSource" alias="dataSource"/>

现在 Hibernate 输出与数据源代理:

INFO  [main]: o.v.s.i.WarehouseProductInfoServiceImpl - newWarehouseProductInfo
Hibernate: select company0_.id as id1_6_, company0_.name as name2_6_ from Company company0_
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:1, Num:1, Query:{[select company0_.id as id1_6_, company0_.name as name2_6_ from Company company0_][]}
Hibernate: insert into WarehouseProductInfo (id, quantity) values (default, ?)
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into WarehouseProductInfo (id, quantity) values (default, ?)][19]}
Hibernate: insert into Product (id, code, company_id, importer_id, name, version) values (default, ?, ?, ?, ?, ?)
INFO  [main]: n.t.d.l.CommonsQueryLoggingListener - Name:, Time:0, Num:1, Query:{[insert into Product (id, code, company_id, importer_id, name, version) values (default, ?, ?, ?, ?, ?)][phoneCode,1,-5,Phone,0]}

数据源代理查询包含参数值，您甚至可以在默认日志记录拦截器旁边添加自定义 JDBC 语句拦截器。

这适用于从 SELECT 语句到存储过程的任何 SQL 查询。