注：存储过程为sqlserver存储过程
1、存储过程
-- [WEB_Person_Login] @In_PersonNo='XX',@In_PassWord='XX'
-- =============================================
ALTER PROCEDURE [dbo].[WEB_Person_Login]-- Add the parameters for the stored procedure here@In_PersonNo VarChar(50)='',@In_PassWord VarChar(50)='',@Return_Error VarChar(50)='' outAS
它需要传入两个参数，且返回值Return_Error
2、调用存储过程的两种方式
2.1、@NamedStoredProcedureQueries
<该注解下可以调用多个存储过程，如只有一个可以直接@NamedStoredProcedureQuery>
@Data
@Entity
@NamedStoredProcedureQueries({@NamedStoredProcedureQuery(name = "WEBPersonLogin", procedureName = "WEB_Person_Login", parameters = {@StoredProcedureParameter(name = "In_PersonNo", type = String.class, mode = ParameterMode.IN),@StoredProcedureParameter(name = "In_PassWord", type = String.class, mode = ParameterMode.IN),@StoredProcedureParameter(name = "Retrun_Error", type = String.class, mode = ParameterMode.OUT)})
})
public class MyTable implements Serializable {
}
@NamedStoredProcedureQuery 绑定了一个存储过程，name为自己取在代码中调用的别名，procedureName表示调用的存储过程名称；
@StoredProcedureParameter 表示存储过程中传入参数以及输出参数的定义,name表示字段名，type字段类型，mode表示传入或输出
例如：java代码中Boolean布尔类型，存储过程类型为bit，java金额BigDecimal，存储过程money（自己记一下）
@Slf4j
@Service
public class PersonServiceImpl implements IPersonService {@PersistenceContextprivate EntityManager entityManager;@Overridepublic JsonResult OnlineRecharge(String PersonNo，String password) {StoredProcedureQuery store = entityManager.createNamedStoredProcedureQuery("WEBPersonLogin").setParameter("In_PersonNo", PersonNo).setParameter("In_PassWord", password);String rescode = (String) store.getOutputParameterValue("Retrun_Error");return JsonResult.success(rescode);}
entityManager.createNamedStoredProcedureQuery调用实体类以及创建的存储过程名称
setParameter给定义传入的参数赋值
getOutputParameterValue通过定义的输出的参数名获取值
JsonResult是自已定义的一个工具类
2.2、直接调用存储过程
@Service
public class SysUserServiceImpl implements ISysUserService {@PersistenceContextprivate EntityManager entityManager;@Overridepublic String validaLogin(String PersonNo, String passWord) {StoredProcedureQuery store = entityManager.createStoredProcedureQuery("WEB_Person_Login").registerStoredProcedureParameter("In_PersonNo",String.class, ParameterMode.IN).registerStoredProcedureParameter("In_PassWord",String.class, ParameterMode.IN).registerStoredProcedureParameter("Retrun_PersonID",String.class,ParameterMode.OUT).setParameter("In_PersonNo", PersonNo).setParameter("In_PassWord", passWord);String retrunError= (String) store.getOutputParameterValue("Retrun_Error");return retrunError;}
}
3、存储过程返回结果集
3.1、存储过程
ALTER PROCEDURE [dbo].[Door_GetDoors_personno]@In_personid Int
AS
BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;Declare @Sql Varchar(8000)set @Sql='Select [字段名],[字段名],[字段名]From [表名] where personid =@In_personid'exec(@Sql)END
存储过程中没有定义输出参数，但是却查出结果集
3.2、调用存储过程
@Data
@Entity
@NamedStoredProcedureQueries({@NamedStoredProcedureQuery(name = "DoorGetDoorsPersonno", procedureName = "Door_GetDoors_personno", resultClasses = {DoorEntity.class}, parameters = {@StoredProcedureParameter(name = "In_personid", type = int.class, mode = ParameterMode.IN),})})
public class DoorEntity {@Idprivate String XX;private String XX;}
映射实体类的两种方式：
1、resultClasses ，实体类的字段名与数据库查询出的字段名一样，可以直接接收，类名.classs
2、resultSetMappings，例：resultSetMappings = {“DoorInfo”}
@SqlResultSetMapping(name = "DoorInfo", entities = {@EntityResult(entityClass = DoorEntity.class, fields = {@FieldResult(name = "xx", column = "Return_字段"),@FieldResult(name = "XX", column = "Return_字段"),})
})
@Service
public class DoorInfoServiceImpl implements IDoorInfoService {@PersistenceContextprivate EntityManager entityManager;@Overridepublic List<DoorEntity> DoorInfoByPersonId(DoorEntity doorEntity) {StoredProcedureQuery store = entityManager.createNamedStoredProcedureQuery("DoorGetDoorsPersonno").setParameter("In_personid", doorEntity.getPersonID());// 调用多结果集方法store.getResultList()List<DoorEntity> doorEntityList = store.getResultList();// 如果返回单结果集// DoorEntity doorEntity = store.getSingleResult();return doorEntityList ;}
}