📄 database.java
字号:
pstm = conn.prepareStatement(sql);
pstm.executeUpdate();
conn.commit();
}
catch(SQLException sqle)
{
//System.out.println("执行DataBase::ExecuteSQL(String)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);
return sqle.getErrorCode();
}
finally{
try{
pstm.close();
}catch(SQLException sqle){System.out.println("执行DataBase::ExecuteSQL(String)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+sqle);}
}
return 0;
}
/**产生唯一编号*/
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(""))//当第一个字段不空时，作为条件查询第二个字段
{
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("执行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("执行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("执行DataBase::makeID(String table,String field1,String field2,String value1,boolean type1)调用SQL语句 "+sql+" 时出错;\r\n错误为:"+e);}
}
}
return out;
}
/**产生唯一编号*/
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(""))//当第一个字段不空时，作为条件查询第二个字段
{
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("执行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("执行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("执行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(""))//当第一个字段不空时，作为条件查询第二个字段
{
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(""))//当第二个字段不空时，作为条件查询第三个字段
{
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)
{
return getOnePage(sql,page,records,true);
}
/**分页时取得一页的数据量*/
public Vector getOnePage(String sql,int page,int records,boolean useDic)
{
//第一个为总页数*/
//第二...个为Hashtable*/
Vector vect = new Vector();
int zdrecords = records;
try
{
if(useDic){
String strsql = "select XMMC from CODE_ZDB where trim(ZDMC)='每页显示记录条数'";
pstm = conn.prepareStatement(strsql);
rs = pstm.executeQuery();
if(rs.next())
{
zdrecords = Integer.parseInt(rs.getString("XMMC"));
}
rs.close();
pstm.close();
}
//查询总页数
// 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)执行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();
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -