你用什么字段,最好用BLOB,没有2K限制,我用过没有问题。
解决方案 »
- 中文数字转为阿拉伯数字的工具类
- 又是一个面试题:不经过机器调试,你能在5分钟之内写出下面代码输出的正确结果吗?
- NullPointerException 的问题
- 异常捕捉的问题,请各位帮忙看看!
- 发现一个比较有意思的问题。
- 我自己改的一个程序不知那出错了!!请改正一下!!
- 关于用UltraEdit转换页面里中的编码问题?(急!!!)
- 关于jdom的一个问题。十万火急。
- 为什么同一个程序执行几次后就出现不同的效果???
- 新手在线等解决,急!Exception in thread "main" java.lang.NullPointerException
- javadoc的问题
- 怎样将EditPlus2作为Java的编写及运行工具?
1. Connection
To open a connection using thin driver
To open a connection using OCI driver
Closing a connection
To set auto commit on
To set the batch size to 100
2. Statements
To create a statement
To create a prepared Statement
To Create a callable statement
To execute a SQL that returns a QUERY
To execute a DML that returns the no of rows affected
To execute a DDL
To Bind an IN variable
To Bind an OUT variable
To set the batch size to 100
To explicitly send the row to the server in batch mode
3. ResultSets
Fetch the next row
Retrieve data from column i based on column no
Based on Column Name
Close the resultset
4. Streams: Long columns in JDBC are streamed
To set a long (Stream) column
To retrieve a long column
5. Objects
How to create a STRUCT type descriptor
How to create a STRUCT object
How to create a SQL to Java type map entry
How to retrieve a STRUCT column
How to retrieve STRUCT attributes
How to create a ARRAY type descriptor
How to create a ARRAY object
How to retrieve a ARRAY column
How to retrieve ARRAY elements
How to retrieve ARRAY elements as a ResultSet which holding the elements
How to retrieve a REF column
To access the value which a REF points to
To change the value which a REF points to
6. LOBS
To read a piece of a LOB
To read the LOB content as a stream
To write a specified amount of data into a LOB
To replace the LOB content from a stream
To get LOB length
1. Connection
*. To open a connection using thin driver :
DriverManager.getConnection("jdbc:oracle:thin:@<mc-name>:<port-no>:<sid>",
"scott", "tiger"); OR DriverManager.getConnection("jdbc:oracle:thin:@(description=(address=(host=<mc-name>)(protocol=tcp)(port=<port-no>))(connect_data=(sid=<sid>)))",
"scott","tiger");
*. To open a connection using OCI driver. To use the default connection.
DriverManager.getConnection("jdbc:oracle:oci8:@", "scott","tiger"); Or DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host=<mc-name>)(protocol=tcp)(port=<port-no>))(connect_data=(sid=<sid>)))",
"scott","tiger"); Or DriverManager.getConnection("jdbc:oracle:oci8:@<tns-name>", "scott","tiger");
*. Closing a connection.
conn.close();
*. To set auto commit on.
conn.setAutoCommit(true)
*. To set the batch size to 100
((OracleConnection)conn).setDefaultExecuteBatch (100);
2. Statements
*. To create a statement
Statement stmt = conn.createStatement();
*. To create a prepared Statement
PreparedStatement pstmt =
conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");
*. To Create a callable statement.
CallableStatement procnone = conn.prepareCall ("begin procnone; end;"
*. To execute a SQL that returns a QUERY
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");
*. To execute a DML that returns the no of rows affected
PreparedStatement pstmt =
conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (123, 'John')");
int rows = pstmt.executeUpdate (); Ps. execute and executeQuery can also be called on a PreparedStatement.
They return a boolean and resultSet respectively. PreparedStatement pstmt =
conn.prepareStatement ("select ENAME from EMP where EMPNO = ?");
pstmt.setInt(1,123);
ResultSet rset = pstmt.executeQuery();
*. To execute a DDL.
boolean status = stmt.execute("create table temp(col1 int)");
*. To Bind an IN variable.
PreparedStatement pstmt =
conn.prepareStatement ("select ENAME from EMP where EMPNO = ?");
pstmt.setInt(1,123);
ResultSet rset = pstmt.executeQuery();
* To Bind an OUT variable
CallableStatement funcin = conn.prepareCall ("begin ? := funcout (?); end;");
funcout.registerOutParameter (1, Types.CHAR);
funcout.registerOutParameter (2, Types.CHAR); Where funcout is,
create or replace function funcout (y out char)
return char is
begin
y := 'tested';
return 'returned';
end;
* To set the batch size to 100
((OracleStatement)stmt).setRowPrefetch (100);
* To explicitly send the row to the server in batch mode.
int rows = ((OracleStatement)stmt).sendBatch();
3. ResultSets
* Fetch the next row
rset.next();
* Retrieve data from column i based on column no
rset.getInt(i);
* Based on Column Name
rset.getInt("EMPNO");
* Close the resultset
rset.close();
4. Streams: Long columns in JDBC are streamed.
* To set a long (Stream) column
pstmt.setAsciiStream (1, <input-stream>, <input-stream-length>); If the string data is in Unicode format, then use setUnicodeStream.
pstmt.setUnicodeStream (1, <input-stream>, <input-stream-length>); For long raw columns, use setBinaryStream
pstmt.setBinaryStream (1, <input-stream>, <input-stream-length>);
create table streamexample (data long) PreparedStatement pstmt =
conn.prepareStatement ("insert into streamexample values (?)");
InputStream is = new FileInputStream ("notes.txt");
File file = new File ("notes.txt");
pstmt.setAsciiStream (1, is, (int)file.length ());
* To retrieve a long column
ResultSet rset =
stmt.executeQuery ("select * from streamexample");
InputStream ascii_data = rset.getAsciiStream (1); // Loop, reading from the gif stream and writing to the file
int c;
while ((c = ascii_data.read ()) != -1)
Systemm.out.print(c);
5. Objects
* How to create a STRUCT type descriptor StructDescriptor type_descriptor =
StructDescriptor.createDescriptor (<type name>, connection);
* How to create a STRUCT object Object[] attributes = { ... };
STRUCT obj = new STRUCT (<type descriptor>, connection, attributes };
* How to create a SQL to Java type map entry.
map.put (<SQL Type Name>, <Java class which implements SQLData interface>);
* How to retrieve a STRUCT column. 1. As a SQLData object. For example, to map SQL type "PERSON" to Java class "PersonObj.java"
which implements oracle.jdbc2.SQLData interface:
java.util.Dictionary map = new Hashtable (10);
map.put ("PERSON", Class.forName ("PersonObj"));
PersonObj obj = (PersonObj) rset.getObject (1, map); 2. As a CustomDatum object
CustomDatum obj = rset.getCustomDatum (1, <CustomDatum factory>); 3. As a oracle.sql.STRUCT object STRUCT obj = (STRUCT) rset.getObject (1);
* How to retrieve STRUCT attributes. 1. With Connection default type map. STRUCT struct = ...
Object[] attributes = struct.getAttributes (); 2. With the requested type map. java.util.Dictionary map = ...
STRUCT struct = ...
Object[] attributes = struct.getAttributes (map); 3. Without map, return as oracle Datums. Datum[] attributes = struct.getOracleAttributes ();
* How to create a ARRAY type descriptor ArrayDescriptor type_descriptor =
ArrayDescriptor.createDescriptor (<type name>, connection);
* How to create a ARRAY object Object[] elements = { ... };
ARRAY obj = new ARRAY (<type descriptor>, connection, elements };
* How to retrieve a ARRAY column. ARRAY obj = ((OracleResultSet) rset).getARRAY (1);
* How to retrieve ARRAY elements. ARRAY array = (ARRAY) rset.getObject (1); Object[] elements = array.getArray (); or Object[] elements = array.getArray (<type map for elements>); or Object[] elements = array.getArray (<begin index>, <count>); or Object[] elements = array.getArray (<begin index>, <count>, <type map>);
* How to retrieve ARRAY elements as a ResultSet which holding the elements. ARRAY array = (ARRAY) rset.getObject (1); ResultSet array_rset = array.getResultSet (); or ResultSet array_rset = array.getResultSet (<type map for elements>); or ResultSet array_rset = array.getResultSet (<begin index>, <count>); or ResultSet array_rset = array.getResultSet (<begin index>, <count>, <type map>);
* How to retrieve a REF column. REF obj = ((OracleResultSet) rset).getREF (1);
* To access the value which a REF points to. REF ref = ((OracleResultSet)rset).getREF (1);
Object value = ref.getValue (); // use connection default map or REF ref = ((OracleResultSet) rset).getREF (1);
Object value = ref.getValue (<type map>); // use specified map
* To change the value which a REF points to. REF ref = ((OracleResultSet) rset).getREF (1);
Object newValue = ...;
ref.setValue (newValue);
6. LOBS
* To read a piece of a LOB. BLOB blob = ((OracleResultSet) rset).getBLOB (1);
byte[] bytes = blob.getBytes (<begin index>, <length>); CLOB clob = ((OracleResultSet) rset).getCLOB (2);
String str = clob.getSubString (<begin index>, <length>); BFILE bfile = ((OracleResultSet) rset).getBFILE (3);
byte[] bytes = bfile.getBytes (<begin index>, <length>);
* To read the LOB content as a stream. BLOB blob = ((OracleResultSet) rset).getBLOB (1);
InputStream input_stream = blob.getBinaryStream ();
input_stream.read (...); CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
Reader char_stream = Clob.getCharacterStream ();
char_stream.read (...); CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
InputStream input_stream = Clob.getAsciiStream ();
input_stream.read (...); BFILE bfile = ((OracleResultSet) rset).getBFILE (1);
InputStream input_stream = bfile.getBinaryStream ();
input_stream.read (...);
* To write a specified amount of data into a LOB. BLOB blob = ((OracleResultSet) rset).getBLOB (1);
byte[] data = ...
int amount_written = blob.putBytes (<begin index>, data); CLOB clob = ((OracleResultSet) rset).getCLOB (1);
String data = ...
int amount_written = clob.putString (<begin index>, data);
* To replace the LOB content from a stream. BLOB blob = ((OracleResultSet) rset).getBLOB (1);
OutputStream output_stream = blob.getBinaryOutputStream ();
output_stream.write (...); CLOB clob = ((OracleResultSet) rset).getCLOB (1);
Writer char_stream = Clob.getCharacterOutputStream ();
char_stream.write (...); CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
OutputStream output_stream = Clob.getAsciiOutputStream ();
output_stream.write (...);
* To get LOB length. long length = blob.length ();
long length = clob.length (); long length = bfile.length ();