如何使用Spring和JPA调用存储过程 [英] How can i call stored procedure using spring with jpa

问题描述

是使用JPA技术的SPRING的新手.

Am new to SPRING with JPA techniques.

am尝试调用用mysql 5编写的存储过程.当我尝试使用存储过程获取数据时，将其喷出异常.

am trying to call the stored procedure which is written in mysql 5. when i am trying to get the data using stored procedure call it spewing exception.

org.springframework.dao.InvalidDataAccessApiUsageException:org.hibernate.QueryException:查询必须以SELECT或FROM开头:call [call st_proc_getusers()];嵌套异常为java.lang.IllegalArgumentException:org.hibernate.QueryException:查询必须以SELECT或FROM开头:call [call st_proc_getusers()]

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: query must begin with SELECT or FROM: call [call st_proc_getusers()]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: query must begin with SELECT or FROM: call [call st_proc_getusers()]

我的persistence.xml是

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit  name="SPT3" transaction-type="RESOURCE_LOCAL">
            <mapping-file>META-INF/persistence-query.xml</mapping-file>
            <class>com.spt3.pojo.Users</class>
            <properties>
                    <property name="hibernate.dialect" value=">org.hibernate.dialect.MySQLDialect" />
                    <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/spring_security" />
                    <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
                    <property name="hibernate.connection.username" value="user" />
                    <property name="hibernate.connection.password" value="pass" />
                    <property name="cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
                    <property name="hibernate.max_fetch_depth" value="3"/>
                    <property name="hibernate.query.factory_class" value="org.hibernate.hql.classic.ClassicQueryTranslatorFactory"/>
                    <property name="hibernate.query.substitutions" value="true 1, false 0"/>
                    <property name="hibernate.show_sql" value="true"/>
                    <property name="hibernate.hbm2ddl.auto" value="create"/>
            </properties>
    </persistence-unit>
</persistence>

我尝试过的代码是

package com.spt3.dao;

import com.spt3.pojo.Users;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;
import org.springframework.orm.jpa.JpaCallback;
import org.springframework.orm.jpa.support.JpaDaoSupport;
import org.springframework.transaction.annotation.Transactional;


@Transactional
public class JpaUsersDao extends JpaDaoSupport {

    public void getResultsByStoredProcedure() {       
        List list=(ArrayList)getJpaTemplate().execute(new JpaCallback() {
            public List doInJpa(EntityManager em) throws PersistenceException {
                javax.persistence.Query query=em.createQuery("call st_proc_getusers()"); // Here the procedure call 
                return query.getResultList(); // returning result list
            }
        });        
    }

}

实际上，我不知道如何使用jpa模板调用存储过程.

Actually i don't know how to call the stored procedure using jpa template.

如何从Spring JPA调用存储过程?

How can i call stored procedure from spring JPA?

请提供解决方案以解决此问题.

please give the solution to get out from this issue.

推荐答案

使用

Use EntityManager.createNativeQuery() instead. I don't think it's possible to call a stored procedure through a JPA query.

public List doInJpa(EntityManager em) throws PersistenceException {
  javax.persistence.Query query=em.createNativeQuery("call st_proc_getusers()"); 
  return query.getResultList(); 
}

您还可以使用 @NamedNativeQuery .

You could also use @NamedNativeQuery.

这篇关于如何使用Spring和JPA调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！