jpa调用mysql存储过程_jpa里调用存储过程

本人要调用的存储过程有返回值，如下：

create or replace procedure p_bakuplog(flag in out varchar2)

is

----------------------------------------------------------------------------------------------

-- 作 者:mengpaopao

-- 日 期:2016/07/07

-- 功能描述:备份清理日志

-- 参数注释:

-- 返 回 值：flag 0：正常；1：未做操作；2：异常

-- 备 注:

-- 修改历史:

-- [时 间] 内容 修改人

----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------

type v_collect is table of varchar2(300) index by pls_integer;

v_backup_time varchar2(8):= to_char(sysdate,'yyyymmdd');

v_tab v_tables:=v_tables(

'ENTRY_LOG',

'JOBCHAN_LOG',

'JOB_LOG',

'STEP_LOG',

'TRANS_LOG'

);

begin

  略...end;

首先需要依附一个表定义存储过程；

package com.xxxx.core.main.etl.bean;

import java.util.Date;

import javax.persistence.*;

/**

*JOBCHAN日志表

*@AUTO

*/

@Entity(name = "jobchan_log")

@NamedStoredProcedureQuery(name = "p_bakuplog", procedureName = "p_bakuplog", parameters = {

@StoredProcedureParameter(mode = ParameterMode.INOUT, name = "flag", type = String.class)

})

public class JobchanLog {

// 作业批次id

@Id

@Column(name="id_batch",updatable=false)

private Integer idBatch;

// 日志频道id

@Column(name="channel_id")

private String channelId;

// 父jobid

@Column(name="log_date")

private Date logDate;

// job名称

@Column(name="logging_object_type")

private String loggingObjectType;

// 读取行数

@Column(name="object_name")

private String objectName;

// 写入行数

@Column(name="object_copy")

private String objectCopy;

// 更新行数

@Column(name="repository_directory")

private String repositoryDirectory;

// 输入行数

@Column(name="filename")

private String filename;

// 输出行数

@Column(name="object_id")

private String objectId;

// 拒绝行数

@Column(name="object_revision")

private String objectRevision;

// 错误

@Column(name="parent_channel_id")

private String parentChannelId;

// 结果集

@Column(name="root_channel_id")

private String rootChannelId;

public Integer getIdBatch() {

return idBatch;

}

public void setIdBatch(Integer idBatch) {

this.idBatch = idBatch;

}

public String getChannelId() {

return channelId;

}

public void setChannelId(String channelId) {

this.channelId = channelId;

}

public Date getLogDate() {

return logDate;

}

public void setLogDate(Date logDate) {

this.logDate = logDate;

}

public String getLoggingObjectType() {

return loggingObjectType;

}

public void setLoggingObjectType(String loggingObjectType) {

this.loggingObjectType = loggingObjectType;

}

public String getObjectName() {

return objectName;

}

public void setObjectName(String objectName) {

this.objectName = objectName;

}

public String getObjectCopy() {

return objectCopy;

}

public void setObjectCopy(String objectCopy) {

this.objectCopy = objectCopy;

}

public String getRepositoryDirectory() {

return repositoryDirectory;

}

public void setRepositoryDirectory(String repositoryDirectory) {

this.repositoryDirectory = repositoryDirectory;

}

public String getFilename() {

return filename;

}

public void setFilename(String filename) {

this.filename = filename;

}

public String getObjectId() {

return objectId;

}

public void setObjectId(String objectId) {

this.objectId = objectId;

}

public String getObjectRevision() {

return objectRevision;

}

public void setObjectRevision(String objectRevision) {

this.objectRevision = objectRevision;

}

public String getParentChannelId() {

return parentChannelId;

}

public void setParentChannelId(String parentChannelId) {

this.parentChannelId = parentChannelId;

}

public String getRootChannelId() {

return rootChannelId;

}

public void setRootChannelId(String rootChannelId) {

this.rootChannelId = rootChannelId;

}

}

然后，在Repository中写存储过程调用：

package com.xxxx.core.main.etl.repository;

import java.util.Date;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.query.Procedure;

import org.springframework.data.repository.query.Param;

public interface JobchanLogRepository extends JpaRepository{

List findByLogDateGreaterThan(Date logDate);

@Procedure(name = "p_bakuplog")

String pBakuplog(@Param("flag") String flag);

}

最后在服务层调用上述函数即可：

package com.xxxx.core.main.etl.service;

import java.net.InetAddress;

import java.net.UnknownHostException;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

/**

* Log处理类

*

* @author mengpaopao

* @date 20160603

*/

@Service

public class LogService {

@Autowired

JobchanLogRepository jobchanLogRepository;

Map map = new HashMap();

private static final Logger LOGGER = Logger.getLogger(LogService.class);

/**

* @Description:备份并清理日志

* @date:2016年07月06日

* @author:mengpaopao

* @param

* @return

*/

public String backupLog(){

String flag = jobchanLogRepository.pBakuplog("1");

if("0".equals(flag)){

LOGGER.info("备份完成！");

}else if("1".equals(flag)){

LOGGER.info("已经备份过了！");

}else if("2".equals(flag)){

LOGGER.info("备份异常！");

}

return flag;

}

}

其他略。