?? database.java
字號:
else
{
out = t1;
}
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調(diào)用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
return out;
}
public int makeID_Add1(String table, String field1, String field2,
String field3, String value1, String value2,
boolean type1, boolean type2)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select max(" + field1 + ") as ID from " + table +
" order by " +
field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))
{ //當(dāng)?shù)谝粋€字段不空時，作為條件查詢第二個字段
sql = "select max(" + field2 + ") as ID from " + table +
" where " +
field1 + "=" + value1 + " order by " + field2;
if(!type1)
{ //是字符串時 將type1設(shè)為false
sql = "select max(" + field2 + ") as ID from " + table +
" where " +
field1 + "='" + value1 + "' order by " + field2;
}
}
if(!value2.equals(""))
{ //當(dāng)?shù)诙€字段不空時，作為條件查詢第三個字段
sql = "select max(" + field3 + ") as ID from " + table +
" where " +
field1 + "=" + value1 + " and " + field2 + "=" + value2 +
" order by " + field3;
if(!type2)
{ //是字符串時 將type1設(shè)為false
sql = "select max(" + field3 + ") as ID from " + table +
" where " +
field1 + "='" + value1 + "' and " + field2 + "='" +
value2 +
"' order by " + field3;
}
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
if(rs.next())
{ //有記錄
out = rs.getInt("ID");
}
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調(diào)用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
return out;
}
/**將名稱轉(zhuǎn)換為編號*/
public int toID(String table, String field1, String field2, String value1)
{
int out = -1;
String sql = "";
try
{
sql = "select " + field2 + " from " + table + " where " + field1 +
"='" +
value1 + "'";
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
if(rs.next())
{
out = rs.getInt(field2);
}
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調(diào)用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
return out;
}
/**將編號轉(zhuǎn)換為名稱*/
public String toName(String table, String field1, String field2,
String value1)
{
String out = "";
String sql = "";
try
{
sql = "select " + field2 + " from " + table + " where " + field1 +
"='" +
value1 + "'";
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
if(rs.next())
{
out = (new DealString()).toString(rs.getString(field2));
}
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調(diào)用SQL語句 " + sql + " 時出錯;\r\n錯誤為:" + sqle);
}
return out;
}
/**分頁時取得一頁的數(shù)據(jù)量*/
public Vector getOnePage(String sql, int page, int records)
{
//第一個為總頁數(shù)*/
//第二...個為Hashtable*/
Vector vect = new Vector();
try
{
pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = pstm.executeQuery();
int rows = 0;
while(rs.next())
{
rows++;
}
int sum = rows / records;
if(rows % records != 0 || rows == 0)
{
sum++;
}
vect.add("" + rows);
vect.add("" + sum);
//移到當(dāng)前行
pstm.close();
rs.close();
pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = pstm.executeQuery();
rows = (page - 1) * records;
rs.absolute(rows+1);
DealString ds = new DealString();
//查詢當(dāng)前頁
int j = 0;
do
{
if(rs==null||j == records||rs.getRow()==0)
{
break;
}
j++;
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
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);
}while(rs.next());
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("執(zhí)行SQL語句 " + sql + " 分頁至第 " + page +
" 頁時出錯;錯誤為:" +
sqle);
}
return vect;
}
/**為某一個字段進(jìn)行重新排序*/
public int setSort(String table, String field1, String field2,
String wherestr, String orderstr, boolean b)
{
//寫入序列號,field2為唯一字段*/
try
{
String sql = "select " + field2 + " from " + table;
if(!wherestr.equals(""))
{
sql += " where " + wherestr;
}
sql += " " + orderstr;
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
PreparedStatement pstm_t = null;
int i = 1;
while(rs.next())
{
if(b)
{ //為field2整型
sql = "update " + table + " set " + field1 + "=" + i +
" where " +
field2 + "=" + rs.getString(1);
}
else
{ //為field2字符串
sql = "update " + table + " set " + field1 + "=" + i +
" where " +
field2 + "='" + rs.getString(1) + "'";
}
pstm_t = conn.prepareStatement(sql);
pstm_t.executeUpdate();
i++;
}
pstm_t.close();
rs.close();
pstm.close();
}
catch(SQLException sqle)
{
System.out.println("調(diào)用MyDataBase.setSort()函數(shù)錯誤:\r\n" + sqle);
}
return 0;
}
/**查詢CLOB類型值*/
public String QueryCLOB(String table, String wherestr, String clobfield)
{
String out = "";
try
{
String sqlCommand = "select " + clobfield + " from " + table;
if(!wherestr.equals(""))
{
sqlCommand += " where " + wherestr;
}
pstm = conn.prepareStatement(sqlCommand);
rs = pstm.executeQuery();
if(rs.next())
{
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(clobfield);
if(clob != null)
{
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while(s != null)
{
out += s;
s = br.readLine();
}
}
}
rs.close();
pstm.close();
//out = (new DealString()).Replace(out,"\"","\"\"");
}
catch(SQLException sqle)
{
System.out.println("調(diào)用MyDataBase.QueryCLOB()函數(shù)錯誤:\r\n" + sqle);
}
catch(IOException iosql)
{
System.out.println("調(diào)用MyDataBase.QueryCLOB()函數(shù)錯誤:\r\n" + iosql);
}
return out;
}
/**修改CLOB類型值*/
public int UpdateCLOB(String table, String wherestr, String clobfield,
String clobvalue)
{
try
{
/* 設(shè)定不自動提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
/* 清空原CLOB對象 */
String sqlCommand = "UPDATE " + table + " set " + clobfield +
"=EMPTY_CLOB()";
if(!wherestr.equals(""))
{
sqlCommand += " where " + wherestr;
}
pstm = conn.prepareStatement(sqlCommand);
pstm.executeUpdate();
/* 查詢CLOB對象并鎖定 */
sqlCommand = "select " + clobfield + " 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.CLOB clob = (oracle.sql.CLOB)rs.getClob(clobfield);
clob.putString(1, clobvalue);
sqlCommand = "update " + table + " set " + clobfield + " = ? ";
if(!wherestr.equals(""))
{
sqlCommand += " where " + wherestr;
}
PreparedStatement pstm_t = conn.prepareStatement(sqlCommand);
pstm_t.setClob(1, clob);
pstm_t.executeUpdate();
pstm_t.close();
}
rs.close();
pstm.close();
/* 正式提交 */
conn.commit();
conn.setAutoCommit(defaultCommit);
}
catch(SQLException sqle)
{
System.out.println("調(diào)用MyDataBase.UpdateCLOB()函數(shù)錯誤:\r\n" + sqle);
return sqle.getErrorCode();
}
catch(Exception e)
{
System.out.println("Exception:" + e);
}
return 0;
}
/**查詢BLOB類型值*/
public String QueryBLOB(String table, String wherestr, String blobfield)
{
String out = "";
try
{
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();
}
}
}
rs.close();
pstm.close();
out = (new DealString()).Replace(out, "\"", "\"\"");
}
catch(SQLException sqle)
{
System.out.println("調(diào)用MyDataBase.QueryBLOB()函數(shù)錯誤:\r\n" + sqle);
}
catch(IOException iosql)
{
System.out.println("調(diào)用MyDataBase.QueryBLOB()函數(shù)錯誤:\r\n" + iosql);
}
return out;
}
/**修改BLOB類型值*/
public int UpdateBLOB(String table, String wherestr, String blobfield,
String blobvalue)
{
try
{
/* 設(shè)定不自動提交 */
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);
}
rs.close();
pstm.close();
/* 正式提交 */
conn.commit();
conn.setAutoCommit(defaultCommit);
}
catch(SQLException sqle)
{
System.out.println("調(diào)用MyDataBase.UpdateBLOB()函數(shù)錯誤:\r\n" + sqle);
return sqle.getErrorCode();
}
catch(Exception e)
{
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -