ibatis 调用oracle 带自定义数组参数的存储过程,oracle存储过程接收 自定义 table 数组类型参数...

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

2016年10月29日 09:53:23 ntuloser 阅读数：1092 标签： oraclejava存储mybatis 更多

个人分类： 数据库

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/ntuloser/article/details/52962988

在使用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模型转换成数据库模型。

创建数据库类型和java数据模型对应create or replace type user_type as object(

age number,

username nvarchar2(20),

phone nvarchar2(20),

email nvarchar2(50)

)

1

2

3

4

5

6

注意这里字符串类型一定要定义为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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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

1

typeHandler="com.jy.typeHandler.UserListHandler"/>

1

2

3

4

然后写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 list = (ArrayList) 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 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;

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

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

call user_insert_pro(?)

1

2

3

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

调用如下：

@org.junit.Test

public void test()

{

SqlSession session=sqlSessionFactory.openSession();

List list=new ArrayList();

for(int i=0;i<1000;i++)

{

list.add(new User("jy", 25, "oracle.com", "110"));

}

HashMap params=new HashMap();

params.put("userList", list);

session.insert("com.jy.dao.UserDao.userInsert", params);

session.close();

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

全剧终！

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