?? database.java
字號:
String sqlCommand = "select "+blobfield+" from "+table;
if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
pstm = conn.prepareStatement(sqlCommand);
rs = pstm.executeQuery();
if(rs.next())
{
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(blobfield);
if (blob != null)
{
InputStream is = blob.getBinaryStream();
InputStreamReader isw = new InputStreamReader(is);
BufferedReader br = new BufferedReader(isw);
String s = br.readLine();
while (s != null)
{
out += s;
s = br.readLine();
}
}
}
out = (new DealString()).Replace(out,"\"","\"\"");
}
catch(SQLException sqle){System.out.println("調用MyDataBase.QueryBLOB()函數錯誤:\r\n"+sqle);}
catch(IOException iosql){System.out.println("調用MyDataBase.QueryBLOB()函數錯誤:\r\n"+iosql);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("試圖釋放rs時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("試圖釋放pstm時出錯;\r\n錯誤為:"+e);}
}
}
return out;
}
/**修改BLOB類型值*/
public int UpdateBLOB(String table,String wherestr,String blobfield,String blobvalue)
{
try
{
/* 設定不自動提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
/* 清空原CLOB對象 */
String sqlCommand = "UPDATE "+table+" set "+blobfield+"=EMPTY_BLOB()";
if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
pstm = conn.prepareStatement(sqlCommand);
pstm.executeUpdate();
/* 查詢CLOB對象并鎖定 */
sqlCommand = "select "+blobfield+" from "+table;
if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
sqlCommand += " for update";
pstm.clearBatch();
pstm.addBatch(sqlCommand);
rs = pstm.executeQuery();
while(rs.next())
{
/* 獲取此CLOB對象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(blobfield);
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
byte b[] = blobvalue.getBytes();
out.write(b,0,b.length);
}
/* 正式提交 */
conn.commit();
conn.setAutoCommit(defaultCommit);
}
catch(SQLException sqle)
{
System.out.println("調用MyDataBase.UpdateBLOB()函數錯誤:\r\n"+sqle);
return sqle.getErrorCode();
}
catch(Exception e){System.out.println("調用MyDataBase.UpdateBLOB()出錯:"+e);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("調用MyDataBase.UpdateBLOB()試圖釋放rs時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("調用MyDataBase.UpdateBLOB()試圖釋放pstm時出錯;\r\n錯誤為:"+e);}
}
}
return 0;
}
/**查詢BLOB類型值*/
public String QueryBLOB_JNDI(String table,String wherestr,String blobfield)
{
String sql = "select "+blobfield+" from "+table;
if(!wherestr.equals(""))sql += " where "+wherestr;
prepareStatement(sql);
executeQuery();
next();
//InputStream is = rs.getBinaryStream("ziduan");
//return (new DealFile()).readCHStr(is);
String out = getObject(blobfield,"BLOB");
closeRs();
closePstm();
return out;
}
/**修改BLOB類型值*/
public int UpdateBLOB_JNDI(String table,String wherestr,String blobfield,String blobvalue)
{
String sql = "UPDATE "+table+" set "+blobfield+"=?";
if(!wherestr.equals(""))sql += " where "+wherestr;
prepareStatement(sql);
setObject(1,blobvalue,"BLOB");
executeUpdate();
closePstm();
return 0;
}
/**往數據庫中插入一個新的CLOB對象*/
public int clobInsert(String sql,String table,String wherestr,String clobfield,String infile)
{
int out = ExecuteSQL(sql);//Insert語句
out = clobModify(table,wherestr,clobfield,infile);
return out;
}
/**修改CLOB對象（是在原CLOB對象基礎上進行覆蓋式的修改）*/
public int clobModify(String table,String wherestr,String clobfield,String infile)
{
try
{
/* 設定不自動提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
/* 查詢此CLOB對象并鎖定 */
String sqlCommand = "select "+clobfield+" from "+table;
if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
sqlCommand += " for update ";
pstm = conn.prepareStatement(sqlCommand);
rs = pstm.executeQuery();
while (rs.next())
{
/* 取出此CLOB對象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(clobfield);
/* 進行覆蓋式修改 */
BufferedReader in = new BufferedReader(new FileReader(infile));
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
int c;
while ((c=in.read())!=-1)
{
out.write(c);
}
in.close();
out.close();
}
rs.close();
pstm.close();
/* 正式提交 */
conn.commit();
/* 恢復原提交狀態 */
conn.setAutoCommit(defaultCommit);
}
catch(SQLException sqle)
{
System.out.println("調用DataBase.clobModify()函數錯誤:\r\n"+sqle);
return sqle.getErrorCode();
}
catch(IOException iosql){System.out.println("調用DataBase.clobModify()函數錯誤:\r\n"+iosql);}
return 0;
}
/**替換CLOB對象（將原CLOB對象清除，換成一個全新的CLOB對象）*/
public int clobReplace(String table,String wherestr,String clobfield,String infile)
{
int out = 0;
try
{
/* 設定不自動提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
/* 清空原CLOB對象 */
String sqlCommand = "update "+table+" set "+clobfield+"=EMPTY_CLOB()";
if(!sqlCommand.equals(""))sqlCommand += " where "+wherestr;
pstm = conn.prepareStatement(sqlCommand);
pstm.executeUpdate();
pstm.close();
/* 正式提交 */
conn.commit();
/* 恢復原提交狀態 */
conn.setAutoCommit(defaultCommit);
out = clobModify(table,wherestr,clobfield,infile);
}
catch(SQLException sqle)
{
System.out.println("調用DataBase.clobReplace()函數錯誤:\r\n"+sqle);
return sqle.getErrorCode();
}
return out;
}
/**向數據庫中插入一個新的BLOB對象*/
public int blobInsert(String sql,String table,String wherestr,String blobfield,String infile)
{
int out = ExecuteSQL(sql);//Insert語句
out = clobModify(table,wherestr,blobfield,infile);
return out;
}
/**修改BLOB對象（是在原BLOB對象基礎上進行覆蓋式的修改）*/
public int blobModify(String table,String wherestr,String blobfield,String infile)
{
try
{
/* 設定不自動提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
/* 查詢此BLOB對象并鎖定 */
String sqlCommand = "select "+blobfield+" from "+table;
if(!wherestr.equals(""))sqlCommand += " where "+wherestr;
sqlCommand += " for update ";
pstm = conn.prepareStatement(sqlCommand);
rs = pstm.executeQuery();
while (rs.next())
{
/* 取出此BLOB對象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(blobfield);
/* 進行覆蓋式修改 */
BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile));
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
int c;
while ((c=in.read())!=-1)
{
out.write(c);
}
in.close();
out.close();
}
rs.close();
pstm.close();
/* 正式提交 */
conn.commit();
/* 恢復原提交狀態 */
conn.setAutoCommit(defaultCommit);
}
catch(SQLException sqle)
{
System.out.println("調用DataBase.blobModify()函數錯誤:\r\n"+sqle);
return sqle.getErrorCode();
}
catch(IOException iosql){System.out.println("調用DataBase.blobModify()函數錯誤:\r\n"+iosql);}
return 0;
}
/**替換BLOB對象（將原BLOB對象清除，換成一個全新的CLOB對象）*/
public int blobReplace(String table,String wherestr,String blobfield,String infile)
{
int out = 0;
try
{
/* 設定不自動提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
/* 清空原CLOB對象 */
String sqlCommand = "update "+table+" set "+blobfield+"=EMPTY_BLOB()";
if(!sqlCommand.equals(""))sqlCommand += " where "+wherestr;
pstm = conn.prepareStatement(sqlCommand);
pstm.executeUpdate();
pstm.close();
/* 正式提交 */
conn.commit();
/* 恢復原提交狀態 */
conn.setAutoCommit(defaultCommit);
out = blobModify(table,wherestr,blobfield,infile);
}
catch(SQLException sqle)
{
System.out.println("調用DataBase.blobReplace()函數錯誤:\r\n"+sqle);
return sqle.getErrorCode();
}
return out;
}
/**數據庫信息*/
public Hashtable getDataBaseInfo()
{
Hashtable hash = new Hashtable();
try
{
DatabaseMetaData dmd = conn.getMetaData();
hash.put("1",dmd.getCatalogSeparator());
hash.put("2",dmd.getCatalogTerm());
hash.put("數據庫類型名稱",dmd.getDatabaseProductName());
hash.put("數據庫版本",dmd.getDatabaseProductVersion());
hash.put("5",dmd.getDefaultTransactionIsolation()+"");
hash.put("驅動版本(最大)",dmd.getDriverMajorVersion()+"");
hash.put("驅動版本(最小)",dmd.getDriverMinorVersion()+"");
hash.put("驅動名",dmd.getDriverName());
hash.put("驅動版本",dmd.getDriverVersion());
hash.put("10",dmd.getExtraNameCharacters());
hash.put("11",dmd.getIdentifierQuoteString());
hash.put("12",dmd.getMaxBinaryLiteralLength()+"");
hash.put("最大行限定",dmd.getMaxRowSize()+"");
hash.put("方案",dmd.getSchemaTerm());
hash.put("日期函數",dmd.getTimeDateFunctions());
hash.put("連接地址",dmd.getURL());
hash.put("用戶名",dmd.getUserName());
}
catch(SQLException sqle){System.out.println("調用DataBase.getDataBaseInfo()函數錯誤:\r\n"+sqle);}
catch(AbstractMethodError e){System.out.println("調用DataBase.getDataBaseInfo()函數錯誤:\r\n"+e);}
return hash;
}
/**數據表列表*/
public Vector getTableList()
{
Vector vect = new Vector();
try
{
if(ParentBean.DBType.equals("Access"))
{
//ExecuteSQL("create table tab(name char)");
//ExecuteSQL("insert into tab(name) select name from MsysObjects");
rs = QuerySQL("select name as TABLE_NAME from tab");
//rs = QuerySQL("SELECT MSysObjects.Name as TABLE_NAME FROM MsysObjects WHERE (Left$([Name],1)<>'~') AND (Left$([Name],4) <> 'Msys') AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name");
}
else
{
DatabaseMetaData dmd = conn.getMetaData();
String[] types = new String[1];
types[0] = "TABLE";
//types[1] = "VIEW"
rs = dmd.getTables(null,ParentBean.DBName.toUpperCase(), "%", types);
}
System.out.println("000"+getResultSetData(rs));
while(rs.next())
{
vect.add((new DealString()).toString(rs.getString("TABLE_NAME")));
}
rs.close();
}
catch(SQLException sqle){System.out.println("調用DataBase.getTableList()函數錯誤:\r\n"+sqle+sqle.getErrorCode());}
catch(AbstractMethodError e){System.out.println("調用DataBase.getTableList()函數錯誤:\r\n"+e);}
return vect;
}
/**數據表的結構*/
public Vector getTableStruct(String table)
{
Vector vect = new Vector();
try
{
/*
rs = QuerySQL("select * from "+table);
ResultSetMetaData rmd = rs.getMetaData();
int cols = rmd.getColumnCount();
for(int i=1;i<=cols;i++)
{
Hashtable hash = new Hashtable();
//hash.put("目錄名",rmd.getCatalogName(i));
//hash.put("列返回值類型名",rmd.getColumnClassName(i));
hash.put("列定義大小",rmd.getColumnDisplaySize(i)+"");
//hash.put("列標簽",rmd.getColumnLabel(i));
hash.put("字段名",rmd.getColumnName(i));
hash.put("列類型編號",rmd.getColumnType(i)+"");
hash.put("列標準類型名",rmd.getColumnTypeName(i));
hash.put("列精確度",rmd.getPrecision(i)+"");
//hash.put("10",rmd.getScale(i)+"");
//hash.put("11",rmd.getSchemaName(i));
//hash.put("表名",rmd.getTableName(i));
//hash.put("13",rmd.isAutoIncrement(i)+"");
//hash.put("大小寫敏感",rmd.isCaseSensitive(i)+"");
//hash.put("是否為金額",rmd.isCurrency(i)+"");
//hash.put("是否可寫",rmd.isDefinitelyWritable(i)+"");
hash.put("是否可為空",rmd.isNullable(i)+"");
//hash.put("是否只讀",rmd.isReadOnly(i)+"");
//hash.put("是否可查詢",rmd.isSearchable(i)+"");
hash.put("是否數字",rmd.isSigned(i)+"");
//hash.put("是否可寫",rmd.isWritable(i)+"");
vect.add(hash);
}
*/
DatabaseMetaData dmd = conn.getMetaData();
rs = dmd.getColumns(null,ParentBean.DBName.toUpperCase(),table.toUpperCase(),null);
rs=dmd.getColumns(null,"HG","TEST",null);
ResultSetMetaData rmd = rs.getMetaData();
int cols = rmd.getColumnCount();
System.out.println(cols+"gggHHH");
System.out.println("resultSET"+getResultSetData(rs));
while(rs.next())
{
System.out.println("TTTTT");
Hashtable hash = new Hashtable();
hash.put("列定義大小",rs.getString("CHAR_OCTET_LENGTH")+"");
String f = rs.getString("COLUMN_NAME");
ResultSet r = QuerySQL("select "+f+" from "+table);
ResultSetMetaData rm = r.getMetaData();
hash.put("字段名",f+"");
hash.put("列類型編號",rm.getColumnType(1)+"");
hash.put("列標準類型名",rm.getColumnTypeName(1)+"");
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -