1、DataSource
@Autowired
@Qualifier("DefaultDataSource")
private DataSource dataSource;
/**
* 身份证转换18-15
*
* @param str
* @return
* @throws ViException
*/
private String trCard_18_15(final String str) throws ViException {
System.out.println("注册---进入身份证的数据18转换15:" + str);
CallableStatement cs = null;
Connection conn = null;
String sql_lang = "{?=call PKG_ID_CARD.Id_Card_18_15(?)}";// 调用的sql
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
cs = conn.prepareCall(sql_lang);
cs.setString(2, str);
cs.registerOutParameter(1, Types.VARCHAR);// 注册输出参数的类型
cs.execute();
conn.commit();
System.out.println("18转换15完成:" + cs.getString(1));
return cs.getString(1);// 获取输出参数的值;
} catch (SQLException e) {
throw new ViException("系统转换数据出错!");
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
}
}
}
2、jdbcTemplate
public List<String> doDeposit(String attId) {
List<String> resultList = (List<String>) jdbcTemplate.execute(new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call PKG_TRAIN.PKG_TRAIN_BLUE_TOOTH_KQ(?,?,?)}";
CallableStatement cs = con.prepareCall(storedProc);
cs.setString(1, attId);
cs.registerOutParameter(2, java.sql.Types.INTEGER);// 注册输出参数 返回类型
cs.registerOutParameter(3, java.sql.Types.VARCHAR);// 注册输出参数 返回信息
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
List<String> result = new ArrayList<>();
cs.execute();
result.add(cs.getString(2));
result.add(cs.getString(3));
return result;
}
});
return resultList;
}
3、jpa的entityManager形式
StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("proKQAttendanceRecord");
query.setParameter("PRM_ID", attId);
query.execute();
List resultList = query.getResultList();
Object code = query.getOutputParameterValue("PRM_APPCODE");
Object msg = query.getOutputParameterValue("PRM_ERRMSGE");
@Getter
@Setter
@Entity
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "proKQAttendanceRecord", procedureName = "PKG_TRAIN.PKG_TRAIN_BLUE_TOOTH_KQ",
resultClasses = {Pro.class}, parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "PRM_ID", type = String.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "PRM_APPCODE", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "PRM_ERRMSGE", type = String.class)})})
class Pro implements Serializable {
@Id
private Integer PRM_APPCODE;
private String PRM_ERRMSGE;
}
注:Entity需要做实体扫描。