oracle存储过程接收自定义 table 数组类型参数

青旅半醒 2022-04-02 11:58 446阅读 0赞

# oracle存储过程接收自定义数组类型参数 #

2016年10月29日 09:53:23 [ntuloser][] 阅读数：1092 标签： [oracle][][java][][存储][Link 1][mybatis][] 更多

个人分类： [数据库][Link 2]

在使用oracle进行批量插入的时，如果数据量较小可以使用insert all的语法进行批量插入。如果数据量较大，再使用insert all的语句插入就会发生错误，因为这个语法会受到限制，oracle不允许一次性插入的列数乘以行数>1000，这时就可以考虑使用存储过程批量插入了。   
使用存储过程批量插入很显然要接收一个数组当参数，而且这个数组里的元素类型应该和java里自定义的数据模型对应。我项目里数据层使用的是MyBatis，就整理整理MyBatis的实现过程。当然不管是MyBatis还是Hibernatge、JDBC都是一个道理，关键的一步都是将java数据模型转换成数据库数据模型。   
以user表为例，user表字段:id、username、age、phone、email，对user表进行批量插入。思考一下，java里有个user类，批量插入，那么传入数据类型应该是List,但是数据库可不认识这个数据模型，所以为了让数据库认识，需要将java模型转换成数据库模型。

1.  创建数据库类型和java数据模型对应

create or replace type user_type as object(
           age number,
           username nvarchar2(20),
           phone nvarchar2(20),
           email nvarchar2(50)
    )

注意这里字符串类型一定要定义为nvarchar，否则字符串类型在转换时会乱码无法插入数据库

2.数据模型有了，还得有个能存放此类型的数组

create or replace type user_type_arr as table of user_type

*  1

3.接着可以创建存储过程了

create or replace procedure user_insert_pro(userList in user_type_arr) as
    begin
    
      for i in 1 .. userList.count loop 
        insert into users(
                           id,
                           username,
                           age,
                           phone,
                           email
                          )
                          values
                          (
                           user_seq.nextval,
                           userList(i).username,
                           userList(i).age,
                           userList(i).phone,
                           userList(i).email
                          );
      end loop;
    end user_insert_pro;

数据库的准备工作就完成了，下面是java代码的工作。我使用的是MyBatis，要在Mapper里配置一个parameterMap

*  1

然后写mybatis里写存储过程调用时的参数类型就定义为这个parameterMap ，这样的意义就是配置的typeHandler会将java里传入的ArrayList转换成对应的数据库类型。UserListHandler代码如下

package com.jy.typeHandler;
    
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.apache.ibatis.type.BaseTypeHandler;
    import org.apache.ibatis.type.JdbcType;
    
    import com.jy.model.User;
    
    import oracle.sql.ARRAY;
    import oracle.sql.ArrayDescriptor;
    import oracle.sql.STRUCT;
    import oracle.sql.StructDescriptor;
    
    @SuppressWarnings("rawtypes")
    public class UserListHandler extends BaseTypeHandler
    {
    
        @Override
        public Object getNullableResult(ResultSet arg0, String arg1) throws SQLException
        {
            return null;
        }
    
        @Override
        public Object getNullableResult(ResultSet arg0, int arg1) throws SQLException
        {
            return null;
        }
    
        @Override
        public Object getNullableResult(CallableStatement arg0, int arg1) throws SQLException
        {
            return null;
        }
    
        @SuppressWarnings("unchecked")
        @Override
        public void setNonNullParameter(PreparedStatement preparedStatement, int i, Object obj, JdbcType arg3)
                throws SQLException
        {
            Connection conn = null;
            try
            {
                if (null != obj)
                {
                    List<User> list = (ArrayList<User>) obj;
                    conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "root", "root");
                    // 数据库的type名必须大写
                    ARRAY array = getArray(conn, "USER_TYPE", "USER_TYPE_ARR", list);
                    preparedStatement.setArray(i, array);
                }
            } catch (Exception e)
            {
                e.printStackTrace();
            } finally
            {
                if (null != conn)
                {
                    conn.close();
                }
            }
        }
    
        private ARRAY getArray(Connection con, String OracleObj, String Oraclelist, List<User> data) throws Exception
        {
            ARRAY array = null;
            ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con);
            STRUCT[] structs = new STRUCT[data.size()];
            StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
            for (int i = 0; i < data.size(); i++)
            {
                Object[] result = { 
                                    data.get(i).getAge(), 
                                    data.get(i).getUsername(), 
                                    data.get(i).getPhone(),
                                    data.get(i).getEmail() 
                                };
                structs[i] = new STRUCT(structdesc, con, result);
            }
            array = new ARRAY(desc, con, structs);
            return array;
        }
    
    }

再再Mapper里定义存储过程调用的语句

<insert id="userInsert" useGeneratedKeys="false" parameterMap="user_map" statementType="CALLABLE">
            call user_insert_pro(?) 
        </insert>

最后可以在java代码里用MyBatis调用存储过程了，MyBatis会基于刚才的配置，将java模型转换成数据库模型。   
调用如下：

@org.junit.Test
        public void test()
        {
            SqlSession session=sqlSessionFactory.openSession();
            List<User> list=new ArrayList<User>();
            for(int i=0;i<1000;i++)
            {
                list.add(new User("jy", 25, "oracle.com", "110"));
            }
            HashMap<String, Object> params=new HashMap<String,Object>();
            params.put("userList", list);
            session.insert("com.jy.dao.UserDao.userInsert", params);
            session.close();
        }

全剧终！

不管什么框架，主要找到java模型和数据库模型转换的方式就行了

[https://blog.csdn.net/ntuloser/article/details/52962988][https_blog.csdn.net_ntuloser_article_details_52962988]

[ntuloser]: https://me.csdn.net/ntuloser
[oracle]: http://so.csdn.net/so/search/s.do?q=oracle&t=blog
[java]: http://so.csdn.net/so/search/s.do?q=java&t=blog
[Link 1]: http://so.csdn.net/so/search/s.do?q=%E5%AD%98%E5%82%A8&t=blog
[mybatis]: http://so.csdn.net/so/search/s.do?q=mybatis&t=blog
[Link 2]: https://blog.csdn.net/ntuloser/article/category/6489016
[https_blog.csdn.net_ntuloser_article_details_52962988]: https://blog.csdn.net/ntuloser/article/details/52962988