dboperation.java

数据库封装包

package database;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/*DBOperation class 封装了DBConnection,完成基本的数据库操作功能*/

public class DBOperation {
 private DBConnection dbCon=null;

public DBOperation(){
	dbCon=new DBConnection();	
}

public void executeDelete(String tablename,String condition){
   String sql="delete from "+tablename+" where "+condition;
   System.out.println(sql);
   dbCon.executeUpdateOrDelete(sql);
}

//完成更新操作
public void executeUpdate(String tablename,String columns,String values , String condition){
	String cols[] = columns.split(",");
	String vals[] = values.split(",");
	String sql = "update " + tablename +" "+ "set ";
	
	for(int i=0;i<vals.length;i++){
		sql = sql +cols[i]+" = "+"'"+vals[i]+"'";
		sql = sql + ",";
	}
	sql = sql.substring(0, sql.length()-1);
	for (int j=vals.length;j<cols.length;j++){
		sql = sql +","+cols[j]+" = ''";
	}
	
	sql = sql + " where " +condition;
  System.out.print(sql);
	try{
		dbCon.executeUpdateOrDelete(sql);
		}catch(Exception exc){
		System.err.println(exc);
	}
}

public void executeInsert(String tablename,String columns,String values){
	String cols[] = columns.split(",");
	String vals[] = values.split(",");
	String sql = "INSERT INTO "+tablename+"("+columns+")"+" VALUES(";
	for(int i=0;i<vals.length;i++){
		sql = sql +"'"+vals[i]+"',";
	}
	sql = sql.substring(0, sql.length()-1);
	for (int j=vals.length;j<cols.length;j++){
		sql = sql+",''";
	}
	sql = sql+")";
	System.out.println(sql);
	try{
	    if(dbCon==null) System.out.println("The DBconnection is Null!");
		dbCon.executeUpdateOrDelete(sql);
	}catch(Exception exc){
		System.err.println(exc);	
	}	
}

/* 根据条件,按页返回结果集合*/
public List<String[]> executeQueryByPage(String tablename,String columns,String conditon,int numApage,int numCurPage){
	List<String[]> records = new ArrayList<String[]>();
	ResultSet rs=null;
	String[] restr=null;
	String sql="select "+columns+" from "+tablename+" where "+conditon;
	System.out.println(sql);
	try{
		rs=dbCon.executeQuery(sql);
		ResultSetMetaData metaData = rs.getMetaData();
		int colNum = metaData.getColumnCount();
	  if(rs.next()){
		   rs.absolute(numApage*(numCurPage-1)+1);
		   int i=0;
		    do{
		      restr = new String[colNum]; 
			  for(int j = 1;j<=colNum;j++)
		         restr[j-1] = rs.getString(j);
			  records.add(restr);
			  i++;
            }while(rs.next()&& i<numApage);   
	   }
	}catch(SQLException e){
		e.printStackTrace();
	}
	if(records.size()!=0) return records;
	else  return null;
}

/* 根据每页指定的显示数量,查询表中的满足的条件的记录的总也页数*/
public int  getTotalPage(String tableName,String condition,int aPageNum){
	int pageNum = 0;
	int recordsNum=0;
	List<String[]> list = executeQuery(tableName,"count(*) as total ",condition);
	if (list!=null ){
	  recordsNum  = Integer.parseInt(list.get(0)[0]);
	}
	if (recordsNum!=0){
		if (recordsNum%aPageNum==0)
		   pageNum = recordsNum/aPageNum;
		else
			pageNum = recordsNum/aPageNum+1;
	}
	return pageNum;
}
//完成根据条件,对表查询,返回记录集,支持"*" 运算符
public List<String[]> executeQuery(String tablename,String columns,String conditon){
	List<String[]> records = new ArrayList<String[]>();
	ResultSet rs=null;
	String[] restr=null;
	String sql="select "+columns+" from "+tablename+" where "+conditon;
	System.out.println(sql);
	rs=dbCon.executeQuery(sql);
	try{
		ResultSetMetaData metaData = rs.getMetaData();
		int colNum = metaData.getColumnCount();
		while (rs.next()){
			restr = new String[colNum]; 
			for (int i=1;i<=colNum;i++)
				restr[i-1] = rs.getString(i);
		records.add(restr);
			
		}
	}catch(SQLException e){
		e.printStackTrace();
	}
  if(records.size()!=0)	return records;
   else  return null;
}

public ResultSet getQueryRS(String tablename,String columns,String conditon){
	ResultSet rs=null;
	String sql="select "+columns+" from "+tablename+" where "+conditon;
	System.out.println("sql="+sql);
	try{
	  rs=dbCon.executeQuery(sql);
	}catch(Exception e){
		e.printStackTrace();
	}
	if(rs!=null)
	return rs;
	else return null;
}
}