Last active
July 24, 2019 01:52
-
-
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.)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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); | |
| } | |
| } | |
| } |
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
How to implement GetResult for OUT parameters