Spring data jpa 调用存储过程

1,先保证升级相关的类库

<org.springframework-version>4.1.7.RELEASE</org.springframework-version>

<spring.data.jpa.version>1.9.6.RELEASE</spring.data.jpa.version>

<org.hibernate-version>5.0.8.Final</org.hibernate-version>

2，create stored procedure

PROCEDURE xxSearch_member(xPara in nvarchar2, xcur_ret out nvarchar2) is

  xkey1                 varchar2(100);
  xkey2                 varchar2(100);
  xname                 varchar2(100);
begin

    xkey1 := xxXml_child_text(xPara,'key1');
    xkey2 := xxXml_child_text(xPara,'key2');
    xName := xxXml_child_text(xPara,'name');
   
   xcur_ret :='<users><user><userId>1</userId><userName>Tom</userName></user><user><userId>2</userId><userName>Jos</userName></user></users>';

end xxSearch_member;

3,修改PO

@Entity
@Table(name="W_USER")
@NamedQuery(name="UserId.findAll", query="SELECT u FROM UserId u")
@NamedStoredProcedureQueries(value = {
    @NamedStoredProcedureQuery(name="UserId.searchMember", procedureName = "TRAN_PROC.xxSearch_member",parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, type = String.class) })
,
@NamedStoredProcedureQuery(name = "UserId.testPro", procedureName = "TRAN_PROC.testPro", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, type = Integer.class)
})
})

public class UserId implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private long id;

 

4,在repository里面增加

 

@Procedure(name="UserId.searchMember", procedureName = "TRAN_PROC.xxSearch_member")
String searchMember(String xPara);

@Procedure(name="UserId.testPro", procedureName = "TRAN_PROC.testPro")
Integer testPro(Integer x);

 

5,service 增加方法，使用dom4j解析xml

 

public List<UserUI2> searchMember(String paras){
List<UserUI2> l=new ArrayList<UserUI2>();
String xml=this.repository.searchMember(paras);
Document document = null;
UserUI2 vo = null;
try {
document = DocumentHelper.parseText(xml);
Element root = document.getRootElement();//users

// iterate through child elements of root
for ( Iterator k = root.elementIterator(); k.hasNext(); ) {
Element element = (Element) k.next();//user
vo = new UserUI2();
for ( int i = 0, size = element.nodeCount(); i < size; i++ ) {
Node node = element.node(i); 
if ( node instanceof Element ) {
Element e=( (Element) node );
if(e.getName().equals("userId"))
vo.setUserId(e.getTextTrim());
else if(e.getName().equals("userName")){
vo.setUserName(e.getTextTrim());
}
//this.logger.info("e.getText()="+e.getText()+" e.getStringValue()="+e.getStringValue()+" e.getName()="+e.getName());
} 
}
l.add(vo);
}
} catch (Exception e) {
this.logger.error("Parse xml error:",e);
}

return l;
}