解决字符串插入不能大于2000.(用的数据库环境为Oracle 9i; 10g不能大于4000)

来源：互联网发布：js调起android应用编辑：程序博客网时间：2024/05/21 11:37

//: CharacterStreamTest.java
// author: yangwm    3/1 2008

import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class CharacterStreamTest {
    public static void main( String args[] ){
        Connection conn = null ;
        PreparedStatement pstm = null;
        Statement stm = null ;
        ResultSet rs = null;
        try{
            StringBuilder message = new StringBuilder("12345");
            for (int i = 0; i < 152; i++) {
                message.append("abcdefghijklmnopqrstuvwxyz");
            }
            message.append("Oracle Database 10g Express Edition 入门");
            byte[] messageBytes = message.toString().getBytes();
            int messageBytesLength = messageBytes.length;
            System.out.println("messageBytesLength=" + messageBytesLength);
            System.out.println("messageLength=" + message.toString().length());

            InputStreamReader isr = new InputStreamReader(new java.io.ByteArrayInputStream(messageBytes));

            // 注册 Driver .
            Class.forName( "oracle.jdbc.driver.OracleDriver" );

            // 连接数据库 ---> Connection
            String url = "jdbc:oracle:thin:@localhost:1521:XE" ;
            conn = DriverManager.getConnection(url , "system","123");

            // 执行 SQL ------> PreparedStatement
            String sqlUpdate = "update users set phone=?,message=? where id=?";
            pstm = conn.prepareStatement(sqlUpdate);
            pstm.setString(1, "800-800-888");
            /*
             使用该语句 pstm.setString(2, message.toString()); 所插入的字符串不能大于2000(用的数据库环境为Oracle 9i,如果用的数据库环境为Oracle 10g不能大于4000).
            如果大于2000,可能会抛出java.sql.SQLException: Data size bigger than max size for this type: 2001
            如果使用该语句 pstm.setCharacterStream(2, isr, messageLength);
            */
            pstm.setCharacterStream(2, isr, 0);
            pstm.setLong(3, 1);

            int r = pstm.executeUpdate();
            System.out.println( r + " rows effected" );

            // 执行 SQL ------> Statement
            stm = conn.createStatement();
            String sqlSelect = "select message from users where id=1" ;
            rs = stm.executeQuery( sqlSelect );
            if ( rs.next() ){
                String mess = rs.getString( 1 );
                System.out.println( "=======================" );
                 System.out.println( "message " + mess );
                 System.out.println( "=======================" );
                 System.out.println( "message.length() " + mess.length() );
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
                //5, 释放数据资源.
                if( rs != null ) try{ rs.close(); }catch(Exception e ){}
                if( stm != null ) try{ stm.close(); }catch(Exception e ){}
                if( pstm != null ) try{ pstm.close(); }catch(Exception e ){}
                if( conn != null ) try{ conn.close(); }catch( Exception e ){}
        }
    }
}

/*
create table users (
    id            number primary key ,
    username    varchar2(50),
    password    varchar2(20),
    phone        varchar2(20),
    email        varchar2(50),
    message        varchar2(4000)
);

create sequence users_seq;
insert into users(id,username,password,phone,email,message) values(users_seq.nextval,'yangwm','12345','12345678','jxfzywm@163.com','Oracle Database 10g Express Edition 入门');
*/