JDBC存取Oralce數據庫的注意幾點事項

　　Be careful with date type column
　　
　　Just like YK when you try to give a value to a column which type is date you should determine the year area with YYYY not YY
　　
　　Suppose there is a user Named Bob Male borned on Jan st
　　
　　Insert into test (user_name user_birthday sex) values(Bob to_date(YYMMDDM);
　　
　　Ok now lets check Bobs birthday:
　　
　　Select to_char(birthdayYYYYMMDD) as birthday from test where user_name=Bob;
　　
　　Birthday
　　
　　
　　
　　Ok its st century now dont forget it
　　
　　How to operate on Clob:
　　
　　Clob type is to save singlebyte character data up to gigabytes
　　
　　If you are a newer to use clob you must find its not so easy to operate on it! Ok take it easy from now on Ill give an example to insert update retrieve clob type values from test
　　
　　→ Insert new value for clob type column:
　　
　　Because it is now allowed to insert string directly into Clob type column we must insert an Empty_clob into the table
　　
　　Suppose we have established a connection to database and the reference of the Connection object it conn
　　
　　String sql = insert into test (user_name user_birthday sex biography) values(Bob to_date(yyyymmdd) MEMPTY_CLOB());
　　
　　PreparedStatement stmt = connprepareStatement(sql);
　　
　　Stmtexecute();
　　
　　Now an Empty_Clob object has be signed to biography
　　
　　To give real value of biography column use the following code fragment:
　　
　　sql = select biography from test where user_name=Bob for update;
　　
　　stmt = connprepareStatement(sql);
　　
　　ResultSet rs = stmtexecuteQuery();
　　
　　Please remembered the for update clause its very important This particular clause means you told the database to prepare the biography of Bob for update
　　
　　oraclesqlCLOB clob = (oraclesqlCLOB)((oraclejdbcOracleResultSet)rs)getCLOB(biography);
　　
　　javaioOutputStream clobWriter = clobgetAsciiOutputStream();
　　
　　byte[] temp = currentBiographygetBytes();
　　
　　(supposed currentBiography is the value to be update to database)
　　
　　clobWriterwrite(temp);
　　
　　clobWriterflush();
　　
　　clobWriterclose();
　　
　　rsclose();
　　
　　stmtclose();
　　
　　mit();
　　
　　
　　Ok the biography has been updated
　　
　　→ When you retrieve clob from database remember to use InputStream
　　
　　oraclesqlCLOB clob = (oraclesqlCLOB)rsgetClob(biography);
　　
　　javaioInputStream asciiStream = clobgetAsciiStream();
　　
　　byte[] tempBody = new byte[]; //set to K;
　　
　　int readLen = asciiStreamread(tempBody);
　　
　　String tempString = (new String(tempBody))substring(readLen);
　　
　　Be careful with PreparedStatement
　　When you use PreparedStatement to precompile and execute SQL String you must close it after transaction ending Otherwise youll got an exception named ORA : maximum cursors exceeded!
　　
　　Im not sure about cursor there is a parameter in the initora file which will used by oracle database In this file max cursors is defined
　　
　　Actually when try to prepare statement and execute in a loop block You may also cause ORA exception
　　
　　So only prepare statement once for particular sql strings set Use set XXX(int index XXX value) method to set column values

From:http://tw.wingwit.com/Article/program/Oracle/201311/17137.html