我试图使用SimpleJDBCCall执行oracle存储过程,所有表和存储过程都在餐馆架构中,表格如下:
CREATE TABLE STAFF
(
STAFF_ID NUMBER(5),
STAFF_FIRST_NAME VARCHAR2(10 BYTE) NOT NULL,
STAFF_LAST_NAME VARCHAR2(20 BYTE) NOT NULL,
STAFF_ROLE VARCHAR2(20 BYTE) NOT NULL,
STAFF_OTHER_DETAILS VARCHAR2(50 BYTE)
);
我的类型包:
CREATE OR REPLACE PACKAGE Staff_Types
AS
TYPE Staff_Collection IS TABLE OF Staff%ROWTYPE;
END Staff_Types;
我的访问包:
CREATE OR REPLACE PACKAGE Staff_TAPI
AS
FUNCTION getAllStaff RETURN Staff_Types.Staff_Collection;
END Staff_TAPI;
CREATE OR REPLACE PACKAGE BODY Staff_Tapi
AS
FUNCTION getAllStaff
RETURN Staff_Types.Staff_Collection
IS
all_staff Staff_Types.Staff_Collection;
BEGIN
SELECT *
BULK COLLECT INTO all_staff
FROM Staff;
RETURN all_staff;
END;
END Staff_Tapi;
Java Access:
@Component
@Qualifier("staffJdbcDAO")
public class StaffJDBCDAO implements StaffDAO {
JdbcTemplate jdbcTemplate;
SimpleJdbcCall getAllMembersSP;
@Autowired
@Qualifier("dataSource")
DataSource dataSource;
@Autowired
@Qualifier("jdbcTemplate")
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
initializeStoredProceduresCalls();
}
private void initializeStoredProceduresCalls() {
getAllMembersSP = new SimpleJdbcCall(jdbcTemplate);
getAllMembersSP.withCatalogName("Staff_Tapi");
getAllMembersSP.withFunctionName("getAllStaff");
getAllMembersSP.declareParameters(
new SqlOutParameter("return",
Types.OTHER,
"Staff_Types.Staff_Collection",
new SqlReturnStructArray<>( new StaffMapper() )
)
);
getAllMembersSP.compile();
}
@Override
public List<Staff> getAllMembers() {
Staff[] staff = getAllMembersSP.executeFunction(Staff[].class,new HashMap<String,Object>() );
return Arrays.asList(staff);
}
}
映射类:
public class StaffMapper implements StructMapper<Staff> {
@Override
public STRUCT toStruct(Staff staff, Connection connection, String typeName) throws SQLException {
StructDescriptor descriptor = StructDescriptor.createDescriptor(typeName, connection);
Object[] attributes = new Object[5];
attributes[0] = new Integer( staff.getId() );
attributes[1] = new String("STAFF_FIRST_NAME");
attributes[2] = new String("STAFF_LAST_NAME");
attributes[3] = new String("STAFF_ROLE");
attributes[4] = new String("STAFF_OTHER_DETAILS");
Struct staffStruct = connection.createStruct(typeName,attributes);
return new STRUCT(descriptor,connection,attributes);
}
@Override
public Staff fromStruct(STRUCT struct) throws SQLException {
StructDescriptor descriptor = struct.getDescriptor();
ResultSetMetaData metaData = descriptor.getMetaData();
Object[] attributes = struct.getAttributes();
Map<String,Object> attributeMap = new HashMap<>();
int idx = 1;
for ( Object attribute : attributes )
attributeMap.put( metaData.getColumnName(idx++),attribute );
int id = ((Integer)attributeMap.get("STAFF_ID")).intValue();
String firstName = (String) attributeMap.get("STAFF_FIRST_NAME");
String lastName = (String) attributeMap.get("STAFF_LAST_NAME");
String staffRole = (String) attributeMap.get("STAFF_ROLE");
String otherDetails = (String) attributeMap.get("STAFF_OTHER_DETAILS");
return new Staff(id,firstName,lastName,staffRole,otherDetails);
}
}
和员工:
public class Staff {
private int id;
private String firstName;
private String lastName;
private String profession;
private String otherDetails;
public Staff(int id, String firstName, String lastName, String profession, String otherDetails) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
this.profession = profession;
this.otherDetails = otherDetails;
}
public int getId() {
return id;
}
public int setId(int id) {
this.id = id;
}
// and others getters and setters
}
当我从StaffDAO执行getAllMembers我一直在获取:CallableStatementCallback;未分类SQL的SQLException [{? = call STAFF_TAPI.GETALLSTAFF()}]; SQL状态[99999];错误代码[17004];列类型无效:1111;
当我将返回类型参数更改为Types.Array时,我得到:CallableStatementCallback;未分类SQL的SQLException [{? = call STAFF_TAPI.GETALLSTAFF()}]; SQL状态[99999];错误代码[17074];
无效的名称模式:restaurant.Staff_Types.Staff_Collection;
我尝试了两种方式,模式“Staff_Types.Staf_collection”得到相同的结果,我试图这样做近2天,不知道我该怎么办,如果有人有任何建议,我会很有帮助 .
3 回答
您无法通过JDBC从存储过程加载PL / SQL
record
. 实际上,您甚至无法从Oracle SQL加载此类型 . 有关详细信息,请参阅此问题:您只能通过JDBC加载SQL类型(而不是PL / SQL类型) . 举个例子,你需要写:
然后:
为了轻松集成您的PL / SQL调用,并且因为它已经构建为一个函数:您是否考虑过这样的事情?
这样,您可以作为常规JDBC查询轻松执行它 . 完成后,只需处理
ResultSet
,它返回fromStruct
方法的一些小变体,以便将List<Staff>
列表返回到您拥有的任何业务逻辑上 . 希望你觉得这个有用!您可能希望在Java代码中大写自定义类型