熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 15:41:07  來源: Oracle 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.