JAVA操作大数据字段——存储过程方式

背景

现有项目需要在后端增加公告的管理，实现CRUD功能，公告存储在数据库中使用clob字段。因为oracle的限制，sql语句的字符数不能超过4000个，所以不论是直接操作sql还是存储过程都会出现异常。

解决方法

按照公司的编码风格，除了查询，其他操作都必须使用存储过程调用。

PROCEDURE add(v_title IN VARCHAR2,
              v_content IN CLOB,
              v_classify IN CHAR,
              v_contact IN VARCHAR2,
              v_memo IN VARCHAR2,
              v_opentime IN VARCHAR2,
              v_mobile IN VARCHAR2,
              v_mail IN VARCHAR2,
              v_scope IN CHAR,
              v_type IN CHAR,
              v_area IN VARCHAR2,
              v_oper IN VARCHAR2,
              v_ret      OUT NUMBER,
              v_desc     OUT VARCHAR2);

因为使用10g的数据库，可以在存储过程内部把clob字段当作普通字段，数据库会自动处理，所以这里不写过程体。

 

@Override
    public Map<String, Object> addCallBid(final Map<String, Object> map) {
        final Map<String, Object> result = new HashMap<String, Object>();
//          PROCEDURE add(v_title    IN VARCHAR2,
//                    v_content  IN CLOB,
//                    v_classify IN CHAR,
//                    v_contact  IN VARCHAR2,
//                    v_memo     IN VARCHAR2,
//                    v_opentime IN VARCHAR2,
//                    v_mobile   IN VARCHAR2,
//                    v_mail     IN VARCHAR2,
//                    v_scope    IN CHAR,
//                    v_type     IN CHAR,
//                    v_area     IN VARCHAR2,
//                    v_oper     IN VARCHAR2,
//                    v_ret      OUT NUMBER,
//                    v_desc     OUT VARCHAR2)
        String callString = "{call pkg_operannouncement.add(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)}";
        getJdbcTemplate().execute(callString, new CallableStatementCallback<Object>() {

            @Override
            public Object doInCallableStatement(CallableStatement cs)
                    throws SQLException, DataAccessException {
                String title = map.containsKey("title")?map.get("title").toString():"";
                String content = map.containsKey("content")?map.get("content").toString():"";
                String classify = map.containsKey("classify")?map.get("classify").toString():"";
                String contact = map.containsKey("contact")?map.get("contact").toString():"";
                String memo = map.containsKey("memo")?map.get("memo").toString():"";
                String opentime = map.containsKey("opentime")?map.get("opentime").toString():"";
                String mobile = map.containsKey("mobile")?map.get("mobile").toString():"";
                String mail = map.containsKey("mail")?map.get("mail").toString():"";
                String scope = map.containsKey("scope")?map.get("scope").toString():"";
                String type = map.containsKey("type")?map.get("type").toString():"";
                String area = map.containsKey("area")?map.get("area").toString():"";
                String oper = map.containsKey("oper")?map.get("oper").toString():"";

                cs.setString(1, title);
                Reader reader = new StringReader(content);
                cs.setCharacterStream(2, reader, content.length());
                cs.setString(3, classify);
                cs.setString(4, contact);
                cs.setString(5, memo);
                cs.setString(6, opentime);
                cs.setString(7, mobile);
                cs.setString(8, mail);
                cs.setString(9, scope);
                cs.setString(10, type);
                cs.setString(11, area);
                cs.setString(12, oper);
                cs.registerOutParameter(13, Types.NUMERIC);
                cs.registerOutParameter(14, Types.VARCHAR);
                
			
				Reader reader = new StringReader(content);

cs.setCharacterStream(2, reader, content.length());
                cs.execute();
                int ret = cs.getInt(13);
                String desc = cs.getString(14);
                result.put("ret", ret);
                result.put("desc", desc);
                return null;
            }
        });
        return result;
    }

 

主要是

Reader reader = new StringReader(content);

cs.setCharacterStream(2, reader, content.length());