?? database.java
字號:
catch(SQLException sqle)
{
System.out.println("執(zhí)行DataBase::ExecuteSQL(String)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);
return sqle.getErrorCode();
}
finally{
try{
pstm.close();
}catch(SQLException sqle){System.out.println("執(zhí)行DataBase::ExecuteSQL(String)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
}
return 0;
}
/**產(chǎn)生唯一編號*/
public int makeID(String table,String field1,String field2,String value1,boolean type1)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select "+field1+" as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當?shù)谝粋€字段不空時，作為條件查詢第二個字段
{
sql = "select "+field2+" as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
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("執(zhí)行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("執(zhí)行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("執(zhí)行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
}
return out;
}
/**產(chǎn)生唯一編號*/
public int makeID_Add1(String table,String field1,String field2,String value1,boolean type1)
{
int out = -1;
String sql = "";
try
{
//只有唯一主鍵field1
sql = "select max("+field1+")+1 as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當?shù)谝粋€字段不空時，作為條件查詢第二個字段
{
sql = "select ("+field2+")+1 as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select ("+field2+")+1 as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
pstm = conn.prepareStatement(sql);
rs = pstm.executeQuery();
if(rs.next()) //有記錄
{
out = rs.getInt(1);
}
}catch(SQLException sqle){System.out.println("執(zhí)行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("執(zhí)行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("執(zhí)行DataBase::makeID_Add1(String table,String field1,String field2,String value1,boolean type1)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
}
return out;
}
public int makeID(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 "+field1+" as ID from "+table+" order by "+field1;
//有兩個主鍵field1、field2
if(!value1.equals(""))//當?shù)谝粋€字段不空時，作為條件查詢第二個字段
{
sql = "select "+field2+" as ID from "+table+" where "+field1+"="+value1+" order by "+field2;
if(!type1) //是字符串時 將type1設為false
sql = "select "+field2+" as ID from "+table+" where "+field1+"='"+value1+"' order by "+field2;
}
if(!value2.equals(""))//當?shù)诙€字段不空時，作為條件查詢第三個字段
{
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("執(zhí)行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("執(zhí)行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("執(zhí)行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(""))//當?shù)谝粋€字段不空時，作為條件查詢第二個字段
{
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(""))//當?shù)诙€字段不空時，作為條件查詢第三個字段
{
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("執(zhí)行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("執(zhí)行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("執(zhí)行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("執(zhí)行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("執(zhí)行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("執(zhí)行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("執(zhí)行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("執(zhí)行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("執(zhí)行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)
{
return getOnePage(sql,page,records,false);
}
/**分頁時取得一頁的數(shù)據(jù)量*/
public Vector getOnePage(String sql,int page,int records,boolean useDic)
{
//第一個為總頁數(shù)*/
//第二...個為Hashtable*/
Vector vect = new Vector();
int zdrecords = records;
try
{
if(useDic){
String strsql = "select XMMC from CODE_ZDB where trim(ZDMC)='每頁顯示記錄條數(shù)'";
pstm = conn.prepareStatement(strsql);
rs = pstm.executeQuery();
if(rs.next())
{
zdrecords = Integer.parseInt(rs.getString("XMMC"));
}
rs.close();
pstm.close();
}
//查詢總頁數(shù)
// pstm.clearBatch();
pstm = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = pstm.executeQuery();
int rows = 0;
while(rs.next())
{
rows++;
}
int sum = rows/zdrecords;
if(rows%zdrecords!=0||rows==0)sum++;
vect.add(""+sum);
rs.close();pstm.close();
//移到當前行
// pstm.clearBatch();
pstm = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = pstm.executeQuery();
rows = (page-1)*zdrecords;
rs.absolute(rows+1);
rs.previous();
DealString ds = new DealString();
//查詢當前頁
int j=0;
while(rs.next())
{
if(j==zdrecords)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);
}
}catch(SQLException sqle){System.out.println("DataBase::getOnePage(String,int,int)執(zhí)行SQL語句 "+sql+" 分頁至第 "+page+" 頁時出錯;錯誤為:"+sqle);}
finally{
if(rs!=null){
try{
rs.close();
}catch(SQLException e){System.out.println("DataBase::getOnePage(String,int,int)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
if(pstm!=null){
try{
pstm.close();
}catch(SQLException e){System.out.println("DataBase::getOnePage(String,int,int)調用SQL語句 "+sql+" 時出錯;\r\n錯誤為:"+e);}
}
}
return vect;
}
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));
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -