?? database.java
字號:
System.out.println("Exception:" + 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 Vector getData(String sql)
{
Vector vect = new Vector();
try
{
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
DealString ds = new DealString();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
while(rs.next())
{
Hashtable hash = new Hashtable();
for(int i=1;i<=cols;i++)
{
String field = ds.toString(rsmd.getColumnName(i));
String value = ds.toString(rs.getString(i));
hash.put(field,value);
}
vect.add(hash);
}
}catch(SQLException sqle){System.out.println("執行DataBase.getData(String)執行SQL語句 "+sql+" 時出錯;錯誤為:"+sqle);}
finally
{
closeRs();
closePstm();
}
return vect;
}
/**數據庫信息*/
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) + "");
hash.put("是否可為空", rm.isNullable(1) + "");
hash.put("是否數字", rm.isSigned(1) + "");
hash.put("列定義大小", rm.getColumnDisplaySize(1) + "");
hash.put("列精確度", rs.getString("NUM_PREC_RADIX") + "");
r.close();
Statement stst = r.getStatement();
if(stst != null)
{
stst.close();
}
vect.add(hash);
}
rs.close();
Statement stmt = rs.getStatement();
if(stmt != null)
{
stmt.close();
}
System.out.println("____" + vect);
}
catch(SQLException sqle)
{
System.out.println("調用DataBase.getTableStruct()函數錯誤:\r\n" + sqle);
}
catch(AbstractMethodError e)
{
System.out.println("調用DataBase.getTableStruct()函數錯誤:\r\n" + e);
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -