gpt4 book ai didi

java - MyBatis - 使用自定义对象调用存储过程

转载 作者:行者123 更新时间:2023-12-02 10:51:11 35 4
gpt4 key购买 nike

我想在 Spring-boot 应用程序中使用 Mybatis 3 调用具有自定义对象的 Oracle 存储过程。我没有任何关于如何做到这一点的示例。我已经有一个使用标准 JDBC 调用过程的方法,我想将其转换为 MyBatis。

public void perform() throws DialectException {
PreparedStatement ps=null;
ResultSet rs=null;
UnitBean unitBean;
unitList = new ArrayList();
CallableStatement cs=null;
Connection oraConn;
try {
oraConn = ((PooledConnection)conn).getPhysicalConnection();
cs = oraConn.prepareCall(sqlSvc.getSqlStatement("GIB_INTERFACE.list"));
StructDescriptor structDescStdUntTyp = StructDescriptor.createDescriptor("STD_UNT_TYP", oraConn);
StructDescriptor structDescAdvUntTyp = StructDescriptor.createDescriptor("ADV_UNT_TYP", oraConn);
ArrayDescriptor descriptorVarcharVarrayType = ArrayDescriptor.createDescriptor("VARCHAR_VARRAY_TYPE", oraConn);
Object[] attributesStdUntTyp = new Object[9];
Object[] attributesAdvUntTyp = new Object[15];
ARRAY tecArray = null;
ARRAY geSerialNumberArray = null;
ARRAY oemSerialNumberArray = null;
ARRAY jobNumberArray = null;
ARRAY unitStatusArray = null;
ARRAY equipmentArray = null;
ARRAY contractualStatusArray = null;
ARRAY trainServiceTypeArray = null;
ARRAY fuelTypeArray = null;
ARRAY combustionSystemArray = null;
ARRAY equipmentLocationArray = null;

tecArray = populateUnitDataSet(filterUnitBean, "getTechnologyInput", "getTechnologyInput", oraConn, descriptorVarcharVarrayType);

geSerialNumberArray = populateUnitDataSet(filterUnitBean, "getGeSerialInput", "getGeSerialOutput", oraConn, descriptorVarcharVarrayType);

oemSerialNumberArray = populateUnitDataSet(filterUnitBean, "getOemSerialNumberInput", "getOemSerialNumberOutput", oraConn, descriptorVarcharVarrayType);

jobNumberArray = populateUnitDataSet(filterUnitBean, "getJobNumberInput", "getJobNumberOutput", oraConn, descriptorVarcharVarrayType);

unitStatusArray = populateUnitDataSet(filterUnitBean, "getUnitStatusInput", "getUnitStatusInput", oraConn, descriptorVarcharVarrayType);

equipmentArray = populateUnitDataSet(filterUnitBean, "getEquipmentInput", "getEquipmentInput", oraConn, descriptorVarcharVarrayType);

contractualStatusArray = populateUnitDataSet(filterUnitBean, "getContractualStatusInput", "getContractualStatusInput", oraConn, descriptorVarcharVarrayType);

trainServiceTypeArray = populateUnitDataSet(filterUnitBean, "getTrainServiceTypeInput", "getTrainServiceTypeInput", oraConn, descriptorVarcharVarrayType);

fuelTypeArray = populateUnitDataSet(filterUnitBean, "getFuelTypeInput", "getFuelTypeInput", oraConn, descriptorVarcharVarrayType);

combustionSystemArray = populateUnitDataSet(filterUnitBean, "getCombustionSystemInput", "getCombustionSystemInput", oraConn, descriptorVarcharVarrayType);

equipmentLocationArray = populateUnitDataSet(filterUnitBean, "getEquipmentLocationInput", "getEquipmentLocationInput", oraConn, descriptorVarcharVarrayType);


STRUCT standardUnit;
attributesStdUntTyp[0] = geSerialNumberArray;
attributesStdUntTyp[1] = oemSerialNumberArray;
attributesStdUntTyp[2] = Utility.resolveNull(filterUnitBean.getCustomer());
attributesStdUntTyp[3] = Utility.resolveNull(filterUnitBean.getSiteName());
attributesStdUntTyp[4] = jobNumberArray;
attributesStdUntTyp[5] = unitStatusArray;
attributesStdUntTyp[6] = equipmentArray;
attributesStdUntTyp[7] = tecArray;
attributesStdUntTyp[8] = Utility.resolveNull(filterUnitBean.getEquipmentName());

standardUnit = new STRUCT(structDescStdUntTyp,oraConn,attributesStdUntTyp);

STRUCT advancedUnit;
attributesAdvUntTyp[0] = Utility.resolveNull(filterUnitBean.getRelatedMachines());
attributesAdvUntTyp[1] = Utility.resolveNull(filterUnitBean.getGlobalCustomer());
attributesAdvUntTyp[2] = contractualStatusArray;
attributesAdvUntTyp[3] = Utility.resolveNull(filterUnitBean.getWarranty());
attributesAdvUntTyp[4] = Utility.resolveNull(filterUnitBean.getWhru());
attributesAdvUntTyp[5] = Utility.resolveNull(filterUnitBean.getRmdAvailable());
attributesAdvUntTyp[6] = Utility.resolveNull(null);
attributesAdvUntTyp[7] = Utility.resolveNull(filterUnitBean.getPilotAvailable());
attributesAdvUntTyp[8] = Utility.resolveNull(filterUnitBean.getExtendorKit());
attributesAdvUntTyp[9] = null;
attributesAdvUntTyp[10] = trainServiceTypeArray;
attributesAdvUntTyp[11] = fuelTypeArray;
attributesAdvUntTyp[12] = combustionSystemArray;
attributesAdvUntTyp[13] = equipmentLocationArray;
attributesAdvUntTyp[14] = Utility.resolveNull(filterUnitBean.getRelatedOem());

advancedUnit = new STRUCT(structDescAdvUntTyp,oraConn,attributesAdvUntTyp);

cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.setObject(2,standardUnit);
cs.setObject(3,advancedUnit);

cs.setInt(4,Integer.parseInt((lowerBound!=null)?lowerBound:"0")+Integer.parseInt(maxPageItems));
cs.setInt(5,Integer.parseInt((lowerBound!=null)?lowerBound:"0"));
cs.registerOutParameter(6,OracleTypes.NUMBER);
cs.execute();

rs = (ResultSet) cs.getObject(1);

int count = cs.getInt(6);
itemsCount = String.valueOf(count);

while(rs.next()){
unitBean = new UnitBean();

unitBean.setGibSerialNumber(Utility.resolveNull(rs.getString("GIB_SERIAL_NUMBER")));
unitBean.setOemSerialNumber(Utility.resolveNull(rs.getString("OEM_SERIAL_NUMBER")));
unitBean.setSiteCustomerDuns(Utility.resolveNull(rs.getString("SITE_CUSTOMER_DUNS")));
unitBean.setSiteCustomerName(Utility.resolveNull(rs.getString("SITE_CUSTOMER_NAME")));
unitBean.setSiteCustomerCountry(Utility.resolveNull(rs.getString("SITE_CUSTOMER_COUNTRY")));
unitBean.setSiteNameAlias(Utility.resolveNull(rs.getString("SITE_NAME_ALIAS")));
unitBean.setGloCustomerDuns(Utility.resolveNull(rs.getString("GLO_CUSTOMER_DUNS")));
unitBean.setGloCustomerName(Utility.resolveNull(rs.getString("GLO_CUSTOMER_NAME")));
unitBean.setGloCustomerCountry(Utility.resolveNull(rs.getString("GLO_CUSTOMER_COUNTRY")));
unitBean.setTechnologyCode(rs.getString("TECHNOLOGY_CODE_OG")); //GIB Remediation Changes
unitBean.setTechnologyDesc(Utility.resolveNull(rs.getString("TECHNOLOGY_DESC")));
unitBean.setTechnologyDescOg(Utility.resolveNull(rs.getString("TECHNOLOGY_DESC_OG")));
unitBean.setEquipmentCode(Utility.resolveNull(rs.getString("EQUIPMENT_CODE")));
unitBean.setEquipmentEngDesc(Utility.resolveNull(rs.getString("EQUIPMENT_ENG_DESC")));
unitBean.setUnitCustomerName(Utility.resolveNull(rs.getString("UNIT_CUSTOMER_NAME")));
unitBean.setEngProjectRef(Utility.resolveNull(rs.getString("ENG_PROJECT_REF")));
unitBean.setOemLocationDesc(Utility.resolveNull(rs.getString("OEM_LOCATION_DESC")));

unitBean.setUnitStatusDesc(Utility.resolveNull(rs.getString("UNIT_STATUS_DESC")));
unitBean.setUnitShipDate(Utility.dateToString(rs.getDate("UNIT_SHIP_DATE")));
unitBean.setUnitCodDate(Utility.dateToString(rs.getDate("UNIT_COD_DATE")));
unitBean.setUnitRetireDate(Utility.dateToString(rs.getDate("UNIT_RETIRE_DATE")));
unitBean.setServiceRelationCode(Utility.resolveNull(rs.getString("SERVICE_RELATION_CODE")));
unitBean.setServiceRelationDesc(Utility.resolveNull(rs.getString("SERVICE_RELATION_DESC")));
unitBean.setMainWarrantyActive(Utility.resolveNull(rs.getString("MAIN_WARRANTY_ACTIVE")));
unitBean.setServiceWarrantyActive(Utility.resolveNull(rs.getString("SERVICE_WARRANTY_ACTIVE")));

unitBean.setCsaEndDate(Utility.dateToString(rs.getDate("CSA_END_DATE")));
unitBean.setOgSalesRegion(Utility.resolveNull(rs.getString("OG_SALES_REGION")));
unitBean.setSanctionedUnitFlag(Utility.resolveNull(rs.getString("SANCTIONED_UNIT_FLAG")));
unitBean.setUnitRating(Utility.resolveNull(rs.getString("UNIT_RATING")));
unitBean.setUnitRatingUom(Utility.resolveNull(rs.getString("UNIT_RATING_UOM")));

unitBean.setControlSystemDesc(Utility.resolveNull(rs.getString("CONTROL_SYSTEM_DESC")));

unitBean.setServiceTypeDesc(Utility.resolveNull(rs.getString("SERVICE_TYPE_DESC")));

unitBean.setDrivenEquipmentDesc(Utility.resolveNull(rs.getString("DRIVEN_EQUIPMENT_DESC")));

unitBean.setCombustionSystemDesc(Utility.resolveNull(rs.getString("COMBUSTION_SYSTEM_DESC")));

unitBean.setPrimaryFuelTypeDesc(Utility.resolveNull(rs.getString("PRIMARY_FUEL_TYPE_DESC")));
unitBean.setExtendorKitInstalled(Utility.resolveNull(rs.getString("EXTENDOR_KIT_INSTALLED")));
unitBean.setWhruFlag(Utility.resolveNull(rs.getString("WHRU_FLAG")));
unitBean.setRmdServiceFlag(Utility.resolveNull(rs.getString("RMD_SERVICE_FLAG")));
unitBean.setPilotServiceFlag(Utility.resolveNull(rs.getString("PILOT_SERVICE_FLAG")));
unitBean.setLineupServiceDescription(Utility.resolveNull(rs.getString("LINEUP_SERVICE_DESC")));
unitBean.setEquipmentLocationDescription(Utility.resolveNull(rs.getString("EQUIP_LOCATION_DESC")));
unitBean.setLastUpdateDate(Utility.dateToString(rs.getDate("LAST_UPDATE_DATE")));
unitBean.setComments(Utility.resolveNull(rs.getString("COMMENTS")));


unitList.add(unitBean);
}
} catch (SQLException e) {
throw new DialectException(e.getMessage());
}finally{
DBUtility.close(ps, rs);
DBUtility.close(cs);
}

}

此外,当我使用 ojdbc7.jar 时,StructDescriptor 类和 ARRAY 类显示为已弃用。还有其他更好的方法来实现这一目标吗?任何帮助将不胜感激。提前致谢。

最佳答案

弃用是为了阻止完成整个事情:使用 Oracle 数组和结构,但无论如何它都会起作用。

简而言之,从 java 类型到 Oracle 自定义类型的映射需要使用自定义 Mybatis TypeHandler。类型处理程序内的代码基本上是 JDBC。当涉及到操作数组和结构时,它强烈依赖于数据库供应商的驱动程序 API(根本不标准)。

设置Mybatis的内容非常多,这里就具体介绍一下。

以下抽象类允许将 java 数组/集合映射到 Oracle 数组/表类型。具体实现中只需在 Oracle 端指定类型名称即可:例如:List <--> TYPE ARRAY_INT AS TABLE OF NUMBER。

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Locale;
import java.util.ResourceBundle;

import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.log4j.Logger;
import org.jboss.jca.adapters.jdbc.WrappedConnection; // in case when the connection is managed by the container (jboss in this case)

public abstract class AbstractArrayTypeHandler<T> extends BaseTypeHandler<Object> {

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

protected static final ResourceBundle CONFIG = ResourceBundle.getBundle("config", Locale.ENGLISH);

protected static final String SCHEMA_NAME = CONFIG.getString("schema.name");

protected static final ResourceBundle DB_STRUCTURE = ResourceBundle.getBundle("dbStructure", Locale.ENGLISH);

protected static final String TYPE_PACKAGE_NAME = DB_STRUCTURE.getString("type.package.name");

protected abstract String getSqlType();

@SuppressWarnings("rawtypes")
@Override
public void setNonNullParameter(final PreparedStatement stmt, final int index, final Object parameter,
final JdbcType jdbcType) throws SQLException {
Object[] javaArray;
if (null == parameter) {
throw new IllegalArgumentException("Parameter must not be null");
} else {
if (parameter.getClass().isArray()) {
javaArray = (Object[]) parameter;
} else if (parameter instanceof Collection) {
javaArray = ((Collection) parameter).toArray();
} else {
throw new IllegalArgumentException("Parameter must be array or collection");
}
final Connection statementConnection = stmt.getConnection();
Connection underlyingConnection = statementConnection;
if (statementConnection instanceof WrappedConnection) { // unwrap the managed connection when necessary
final WrappedConnection wrapper = (WrappedConnection) statementConnection;
LOGGER.debug("Wrapped connection type: " + wrapper.getClass().getName());
underlyingConnection = wrapper.getUnderlyingConnection();
}
LOGGER.debug("Underlying connection type: " + underlyingConnection.getClass().getName());
final OracleConnection oracleConnection = (OracleConnection) underlyingConnection;
/* java.sqlConnection.createArrayOf is not supported by Oracle Driver */
final String type = String.format("%s.%s.%s", SCHEMA_NAME, TYPE_PACKAGE_NAME, this.getSqlType());
final Array array = createArray(oracleConnection, type, javaArray);
LOGGER.debug(String.format("ARRAY type '%s' of %d elements created", type, javaArray.length));
stmt.setArray(index, array);
LOGGER.debug("statement array Set");
}
}

protected ARRAY createArray(final OracleConnection oracleConnection, final String type, final Object[] javaArray) throws SQLException {
return oracleConnection.createARRAY(type, javaArray);
}

@Override
public Object getNullableResult(final ResultSet resultSet, final String columnName) throws SQLException {
LOGGER.debug("getNullableResult - resultSet/columnName");
final Array array = resultSet.getArray(columnName);
return array.getArray();
}

@Override
public Object getNullableResult(final ResultSet resultSet, final int columnIndex) throws SQLException {
LOGGER.debug("getNullableResult - resultSet/columnIndex");
final Array array = resultSet.getArray(columnIndex);
return array.getArray();
}

@Override
public Object getNullableResult(final CallableStatement stmt, final int columnIndex) throws SQLException {
LOGGER.debug("getNullableResult - callableStatement/columnIndex");
final Array array = stmt.getArray(columnIndex);
return array.getArray();
}

}

此处映射自定义 Oracle 类型/结构的数组:

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.apache.log4j.Logger;


@SuppressWarnings({"deprecation"})
public abstract class AbstractObjectArrayTypeHandler<T> extends AbstractArrayTypeHandler<T> {

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

public AbstractObjectArrayTypeHandler() {
super();
}

protected abstract String getOracleObjectType();

protected abstract Class<T> arrayElementClass();

protected abstract Object[] buildStructAttributes(Object object);

@Override
protected ARRAY createArray(OracleConnection oracleConnection, String oracleArrayType, Object[] javaArray) throws SQLException {
StructDescriptor itemDescriptor = createDescriptor(oracleConnection);
List<Struct> structList = new ArrayList<Struct>(javaArray.length);
Class<T> arrayElementClass = arrayElementClass();
for (Object object : javaArray) {
if (null != object && arrayElementClass.isAssignableFrom(object.getClass())) {
Object[] structAttributes = buildStructAttributes(object);
structList.add(new STRUCT(itemDescriptor, oracleConnection, structAttributes));
} else throw new IllegalArgumentException("javaArray element must be instance of " + arrayElementClass.getName() + "but is: " + (null == object ? "null" : object.getClass().getName()));
}
return super.createArray(oracleConnection, oracleArrayType, structList.toArray());
}

private StructDescriptor createDescriptor(OracleConnection oracleConnection) throws SQLException {
final String typeName = typeFullQualifiedName();
StructDescriptor descriptor = StructDescriptor.createDescriptor(typeName, oracleConnection);
LOGGER.debug(String.format("Object descriptor for type '%s' created", typeName));
return descriptor;
}

private String typeFullQualifiedName() {
return String.format("%s.%s", SCHEMA_NAME, this.getOracleObjectType());
}

@Override
public Object getNullableResult(final ResultSet resultSet, final String columnName) throws SQLException {
final Array array = resultSet.getArray(columnName);
return readOracleStructList(array);
}

@Override
public Object getNullableResult(final ResultSet resultSet, final int columnIndex) throws SQLException {
final Array array = resultSet.getArray(columnIndex);
return readOracleStructList(array);
}

@Override
public Object getNullableResult(final CallableStatement stmt, final int columnIndex) throws SQLException {
final Array array = stmt.getArray(columnIndex);
return readOracleStructList(array);
}

protected List<T> readOracleStructList(Array sqlArray) throws SQLException {
if (null == sqlArray)
return null;
Object object = sqlArray.getArray();
Object[] structObjectArray;
return null == object ? null : readNotNullStructList(object);
}

private List<T> readNotNullStructList(Object object) throws SQLException {
if (object.getClass().isArray())
return readArrayStructList((Object[]) object);
else throw new IllegalArgumentException("Returned value is not an array");
}

private List<T> readArrayStructList(Object[] structObjectArray) throws SQLException {
List<T> list = new ArrayList<T>(structObjectArray.length);
for (Object structObject : structObjectArray) {
if (structObject instanceof Struct) {
Struct struct = (Struct) structObject;
Object[] attributes = struct.getAttributes();
T javaObject = buildJavaObject(attributes);
list.add(javaObject);
} else throw new IllegalArgumentException("Expected array element of type Struct, but got: " + structObjectArray.getClass());
}
return list;
}


protected abstract T buildJavaObject(Object[] attributes);

}

具体类型处理的示例:

import java.math.BigDecimal;

import java.util.Collection;

import org.apache.ibatis.type.MappedTypes;

import com.example.CustomBean;


@MappedTypes({CustomBean[].class,Collection.class})
public class ListCustomBeanTypeHandler extends AbstractObjectArrayTypeHandler<CustomBean> {


@Override
protected final String getSqlType() {
return DB_STRUCTURE.getString("type.array.customBean"); // replace with full qualified name of array type declared in oracle
}

protected final String getOracleObjectType() {
return DB_STRUCTURE.getString("type.object.customBean"); // replace with full qualified name of struct/object type declared in oracle
}

protected final Class<CustomBean> arrayElementClass() {
return CustomBean.class;
}

protected final Object[] buildStructAttributes(Object object) {
CustomBean bean = (CustomBean) object;
Object[] structAttributes = new Object[] {bean.getProperty1(), bean.getProperty2(), bean.getProperty3(), null /* N/A for input */};
return structAttributes;
}

protected CustomBean buildJavaObject(Object[] attributes) {
CustomBean bean = new CustomBean();
int i = 0;

BigDecimal property1 = (BigDecimal) attributes[i++];
if (property1 != null)
bean.setProperty1(property1.intValue());

BigDecimal property2 = (BigDecimal) attributes[i++];
if (property2 != null)
bean.setProperty2(property2.longValue());

bean.setProperty3(((BigDecimal) attributes[i++]).intValue());
bean.setReturnCode(((BigDecimal) attributes[i++]).intValue());
bean.setReturnMessage((String) attributes[i++]);
return bean;
}
}

使用Mapper API调用Mybatis

@Update("{ CALL ${schema.name}.theProcedure("
+ "#{beanList, mode=IN, typeHandler=com.example.ListCustomerBeanTypeHandler}, "
+ "#{resultContainer.resultList, mode=OUT, jdbcType=ARRAY, typeHandler=com.example.ListCustomBeanTypeHandler, jdbcTypeName=${schema.name}.${type.package.name}.${type.array.customBean}}, "
+ "#{resultContainer.returnCode, mode=OUT, jdbcType=INTEGER}, "
+ "#{resultContainer.returnMessage, mode=OUT, jdbcType=VARCHAR} "
+ ")}")
@Options(statementType = StatementType.CALLABLE)
void runTheProcedure(@Param("beanList") List<CustomBean> beanList, @Param("resultContainer") ResultContainer<CustomBean> resultContainer);

仅供引用:

public class ResultContainer<T> {
private Integer returnCode;
private List<T> resultList;
private String returnMessage;
}

关于java - MyBatis - 使用自定义对象调用存储过程,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52169548/

35 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com