📄 database.java
字号:
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)+"");
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);
}
Statement stmt = rs.getStatement();
rs.close();
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);}
return vect;
}
/**取得数据集内容*/
public Vector getResultSetData(ResultSet rs)
{
Vector vect = new Vector();
try
{
//取得列数和列名
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
while(rs.next())
{
Hashtable hash = new Hashtable();
for(int i=1;i<=cols;i++)
{
DealString ds = new DealString();
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.getResultSetData()函数错误:\r\n"+sqle);}
return vect;
}
/**创建申明对象*/
public void prepareStatement(String sql)
{
try
{
pstm = conn.prepareStatement(sql);
}
catch(SQLException sqle){System.out.println("调用DataBase.prepareStatement()函数错误:\r\n"+sqle);}
}
/**执行查询*/
public void executeQuery()
{
try
{
rs = pstm.executeQuery();
}
catch(SQLException sqle){System.out.println("调用DataBase.executeQuery()函数错误:\r\n"+sqle);}
}
/**转向下一条*/
public boolean next()
{
try
{
return rs.next();
}
catch(SQLException sqle){System.out.println("调用DataBase.next()函数错误:\r\n"+sqle);}
return true;
}
/**取得数据并根据数据类型转化为字符串*/
public String getObject(String field,String sqlType)
{
try
{
if(rs==null)return "";
if(sqlType.equals("BINARY")||sqlType.equals("VARBINARY"))//字节型
{
byte b[] = rs.getBytes(field);
return new String(b);
}
else if(sqlType.equals("LONGVARBINARY")||sqlType.equals("BLOB"))//未编码大字节型
{
InputStream is = rs.getBinaryStream(field);
return (new DealFile()).readCHStr(is);
}
else if(sqlType.equals("LONGVARCHAR")||sqlType.equals("CLOB"))//编码大字节型
{
InputStream is = rs.getAsciiStream(field);
return (new DealFile()).readCHStr(is);
}
else//字符串型
{
return rs.getString(field);
}
}
catch(Exception sqle){System.out.println("调用DataBase.getObject()函数错误:\r\n"+sqle);}
return "";
}
/**根据数据类型保存到数据库*/
public void setObject(int index,String value,String sqlType)
{
try
{
if(sqlType.equals("ARRAY"))//数组型?????
{
}
else if(sqlType.equals("BIGINT"))//64位的有符号整数
{
Long l = new Long(value);
pstm.setObject(index,l);
}
else if(sqlType.equals("BINARY")||sqlType.equals("VARBINARY"))//字节型
{
byte b[] = value.getBytes();
pstm.setObject(index,b);
}
else if(sqlType.equals("BIT"))//布尔型
{
Boolean b = new Boolean("true");
if(value.equals("0"))b = new Boolean("false");
pstm.setObject(index,b);
}
else if(sqlType.equals("BLOB")||sqlType.equals("LONGVARBINARY"))//未编码流类型
{
FileInputStream fis = (new DealFile()).toInputStream(value);
pstm.setBinaryStream(index,fis,(int)(new File("tmp.txt")).length());
}
else if(sqlType.equals("CLOB")||sqlType.equals("LONGVARCHAR"))//编码流类型
{
FileInputStream fis = (new DealFile()).toInputStream(value);
pstm.setAsciiStream(index,fis,(int)(new File("tmp.txt")).length());
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -