Skip to content

Instantly share code, notes, and snippets.

@composite
Last active July 24, 2019 01:52
Show Gist options
  • Select an option

  • Save composite/119827beddc832ca153bc6c415eb2b94 to your computer and use it in GitHub Desktop.

Select an option

Save composite/119827beddc832ca153bc6c415eb2b94 to your computer and use it in GitHub Desktop.
The example of java.util.Map to Struct and send parameter as Oracle Array for Spring 3.x + MyBatis 3.2.x with Oracle. (based in JDK 1.8, not used deprecated STRUCT, ARRAY.)
CREATE OR REPLACE TYPE TO_STUDENT IS OBJECT (
NAME VARCHAR2(20)
,AGE NUMBER(3)
,GENDER VARCHAR2(1)
,SCORE NUMBER(1,2)
,GRADE VARCHAR2(1)
);
CREATE OR REPLACE TYPE TA_STUDENTS IS TABLE OF TO_STUDENT;
CREATE OR REPLACE PROCEDURE SP_AVERAGE
(
P_STUDENTS IN TA_STUDENTS
,O_SCORE OUT NUMBER(1,2)
,O_GRADE OUT VARCHAR2(1)
)
IS
V_SUMSCORE NUMBER(5,2);
BEGIN
V_SUMSCORE = 0;
FOR i IN P_STUDENTS.FIRST .. P_STUDENTS_LAST LOOP
V_SUMSCORE := V_SUMSCORE + P_STUDENTS(i).SCORE;
END LOOP;
V_SUMSCORE := V_SUMSCORE / P_STUDENTS_LAST.COUNT;
O_SCORE := V_SUMSCORE;
SELECT CASE
WHEN V_SUMSCORE > 4 THEN 'A'
WHEN V_SUMSCORE BETWEEN 3 AND 4 THEN 'B'
WHEN V_SUMSCORE BETWEEN 2 AND 3 THEN 'C'
WHEN V_SUMSCORE BETWEEN 1 AND 2 THEN 'D'
ELSE 'F' END
INTO O_GRADE
FROM DUAL;
END SP_AVERAGE;
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="MyMapper">
<update id="getAverage" parameterType="map" statementType="CALLABLE">
{CALL SP_AVERAGE (
#{P_STUDENTS, jdbcType=ARRAY, javaType=object, jdbcTypeName=TA_STUDENTS, typeHandler=StudentsTypeHandler}
,#{O_SCORE, mode=OUT, jdbcType=NUMBER, javaType=double}
,#{O_GRADE, mode=OUT, jdbcType=VARCHAR, javaType=string}
)}
</update>
</mapper>
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor;
import oracle.jdbc.OracleConnection;
public final class StudentsTypeHandler implements TypeHandler<List<Map<String, Object>>> {
private final static String RECORD_TYPE = "TO_STUDENT";
private final static String TABLE_TYPE = "TA_STUDENTS";
@Override
public List<Map<String, Object>> getResult(ResultSet arg0, String arg1) throws SQLException {
// TODO implement later?
return null;
}
@Override
public List<Map<String, Object>> getResult(ResultSet arg0, int arg1) throws SQLException {
// TODO implement later?
return null;
}
@Override
public List<Map<String, Object>> getResult(CallableStatement arg0, int arg1) throws SQLException {
// TODO implement later?
return null;
}
@Override
public void setParameter(PreparedStatement ps, int i, List<Map<String, Object>> param, JdbcType jdbctype) throws SQLException {
Connection conn = ps.getConnection();
//Connection conn = new CommonsDbcpNativeJdbcExtractor().getNativeConnection(ps.getConnection()); // uncomment this intead when you using DBCP.
Object[] rows = new Object[param.size()];
int ri = 0;
// register typemap shown below if not exists.
if(!conn.getTypeMap().containsKey(RECORD_TYPE)) conn.getTypeMap().put(RECORD_TYPE, StudentsSQLData.class);
for(Map<String, Object> row : param){
rows[ri++] = new StudentsSQLData(row);
}
ps.setArray(i, ((OracleConnection) conn).createOracleArray(TABLE_TYPE, rows));
}
private static class StudentsSQLData implements SQLData{
/**
* Struct columns index
*/
private final static String[] COLUMNS = {"NAME","AGE","GENDER","SCORE","GRADE"};
private final Map<String, Object> row;
public StudentsSQLData(final Map<String, Object> row){
this.row = row;
}
@Override
public String getSQLTypeName() throws SQLException {
return RECORD_TYPE;
}
@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
for(String col : COLUMNS) // TODO handle type and write.
stream.writeString(row.get(col) != null ? row.get(col).toString() : null);
}
}
}
@mohammedkassem

Copy link
Copy Markdown

How to implement GetResult for OUT parameters

@composite

Copy link
Copy Markdown
Author

@mohammedkassem Sorry for late reply.

How to implement GetResult for OUT parameters

well, usually, calling procedure is not for <select>. you shouldn't implement them. just don't mind it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment