📄 database.java
字号:
sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
if(!value2.equals(""))//当第二个字段不空时，作为条件查询第三个字段
{
sql = "select "+field3+" as ID from "+table+" where "+field1+"="+value1+" and "+field2+"="+value2+" order by "+field3;
if(!type2) //是字符串时 将type1设为false
sql = "select "+field3+" as ID from "+table+" where "+field1+"='"+value1+"' and "+field2+"='"+value2+"' order by "+field3;
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
int t1 = 1;
int t2 = 2;
if(rs.next()) //有记录
{
t1 = rs.getInt("ID");
out = t1;
boolean bool = false;
while(rs.next()) //不止一条纪录
{
bool = true;
t2 = rs.getInt("ID");
if((t2-t1)>1)break; //如果t2与t1相差大于1,则跳出去,新编号为t1++（见后面**）
t1 = t2; //否则将t2赋给t1
}
if(!bool) //如果只有一条纪录
{
if(t1>1)t1 = 1; //如果已有纪录的ID号大于1，则新编号设为1
else t1++;
}
else t1++; //**
}
if(out>1)out = 1;
else out = t1;
}catch(SQLException sqle){System.out.println("执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("执行DataBase::makeID(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
}
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(""))//当第一个字段不空时，作为条件查询第二个字段
{
sql = "select max("+field2+") as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串时 将type1设为false
sql = "select max("+field2+") as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
if(!value2.equals(""))//当第二个字段不空时，作为条件查询第三个字段
{
sql = "select max("+field3+") as ID from "+table+" where "+field1+"="+value1+" and "+field2+"="+value2+" order by "+field3;
if(!type2) //是字符串时 将type1设为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");
}
}catch(SQLException sqle){System.out.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("执行DataBase::makeID_Add1(String table,String field1,String field2,String field3,String value1,String value2,boolean type1,boolean type2)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
}
return out;
}
/**将名称转换为编号*/
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);
}
}catch(SQLException sqle){System.out.println("执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("执行DataBase::toID(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
}
return out;
}
/**将编号转换为名称*/
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));
}
}catch(SQLException sqle){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("执行DataBase::toName(String table,String field1,String field2,String value1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
}
return out;
}
/**分页时取得一页的数据量*/
/**分页时取得一页的数据量*/
public Vector getOnePage(String sql, int page, int records)
{
//第一个为总页数*/
//第二...个为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);
int temp = rows;
//移到当前行
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();
//查询当前页
int j = 0;
do
{
if(rs==null||j == records||temp==0||page>sum)
{
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("执行SQL语句 " + sql + " 分页至第 " + page +
" 页时出错;错误为:" +
sqle);
}
return vect;
}
public Vector getOnePage1(String sql,int page,int records)
{
//sql += " where rowid between 1 and 2";
//return getResultSet(ExceuteSQL(sql));
return null;
}
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{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("执行DataBase::getData(String)试图释放rs时出错;\r\n错误为:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("执行DataBase::getData(String)试图释放pstm时出错;\r\n错误为:"+e);}
}
}
return vect;
}
/**为某一个字段进行重新排序*/
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();
}catch(SQLException sqle){System.out.println("调用MyDataBase.setSort()函数错误:\r\n"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("调用MyDataBase.setSort()试图释放rs时出错;\r\n错误为:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("调用MyDataBase.setSort()试图释放pstm时出错;\r\n错误为:"+e);}
}
}
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();
}
}
}
//out = (new DealString()).Replace(out,"\"","\"\"");
}
catch(SQLException sqle){System.out.println("调用MyDataBase.QueryCLOB()函数错误:\r\n"+sqle);}
catch(IOException iosql){System.out.println("调用MyDataBase.QueryCLOB()函数错误:\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;
}
/**修改CLOB类型值*/
public int UpdateCLOB(String table,String wherestr,String clobfield,String clobvalue)
{
try
{
/* 设定不自动提交 */
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();
}
/* 正式提交 */
conn.commit();
conn.setAutoCommit(defaultCommit);
}
catch(SQLException sqle)
{
System.out.println("调用MyDataBase.UpdateCLOB()函数错误:\r\n"+sqle);
return sqle.getErrorCode();
}
catch(Exception e){System.out.println("Exception:"+e);}
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 0;
}
/**查询BLOB类型值*/
public String QueryBLOB(String table,String wherestr,String blobfield)
{
String out = "";
try
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -