dbaccess.java

java swing源码 欢迎下载 有问题请联系 我一定负责到底

package com.sinosoft.common;

import java.sql.*;
import java.util.*;

public class DBAccess{
	
	public int COUNT = 0;
	public boolean EOF = false;
	
	public IndexMap init(String sql,String pk,HashSet hs){
		DbBean dc = new DbBean();
		IndexMap co = new IndexMap();
		try{
			dc.prepareStatement(sql);
			dc.setString(1,pk);
			dc.rs = dc.executeQuery();
			if(dc.rs.next()){
				Iterator it = hs.iterator();
				while(it.hasNext()){
					String col = it.next().toString();
					co.put(col,dc.rs.getString(col.toUpperCase()));
				}
			}
			return co;
		}catch(Exception e){
			System.out.println("DBAccess.init():" + e.getMessage());
			e.printStackTrace();
			return null;
		}finally{
			dc.close();
			dc = null;
		}
	}

	public boolean execute(String sql,IndexMap co){
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			for(int i=0;i<co.size();i++){
				String colType = co.get(i).getClass().getName();
				if(("java.sql.Date").equals(colType))
					dc.setDate(i+1,java.sql.Date.valueOf(co.get(i).toString()));
				else if(("java.sql.Timestamp").equals(colType))
					dc.setTimestamp(i+1,java.sql.Timestamp.valueOf(co.get(i).toString()));
				else
					dc.setString(i+1,co.get(i).toString());
			}
			return dc.execute();
		}catch(Exception e){
			System.out.println("DBAccess.execute(String,IM):" + e.getMessage());
			e.printStackTrace();
			return false;
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public String executeQuery(String sql,IndexMap co,int pageSize,int pageIndex){
		StringBuffer RESULT = new StringBuffer();
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			//参数设定***********************
			dc.prepstmt.setFetchSize(50);
			//end
			int j = 1;
			for(int i=0;i<co.size();i++){
				if(Data.hasValue(co.get(i).toString())){
					dc.setString(j,co.get(i).toString());
					j++;
				}
			}
			dc.rs=dc.executeQuery();	
			if(dc.rs.next()){
				dc.rs.last();
				this.COUNT = dc.rs.getRow();
			}
			if(pageSize==0) pageSize = this.COUNT;
			if (this.COUNT>0){
				dc.rs.absolute((pageIndex-1)*pageSize+1);
				int i=0;
				do{
					if (i==0)
						RESULT.append(dc.rs.getString(1));
					else{
						RESULT.append(",");
						RESULT.append(dc.rs.getString(1));
					}
					i++;
				}while (dc.rs.next() && (i<pageSize));
				if (!dc.rs.isAfterLast())
					this.EOF=false;
				else
					this.EOF=true;
			}
			return RESULT.toString();
		}catch(Exception e){
			System.out.println("DBAccess.executeQuery():" + e.getMessage());
			e.printStackTrace();
			return "";
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public boolean executeDelete(String sql,String pk){
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			dc.setString(1,pk);
			return dc.execute();
		}catch(Exception e){
			System.out.println("DBAccess.executeDelete():" + e.getMessage());
			e.printStackTrace();
			return false;
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public boolean mulExecute(String sql,List l){
		DbBean dc = new DbBean();
		try{
			dc.setAutoCommit(false);
			dc.prepareStatement(sql);
			for(int i=0;i<l.size();i++){
				String[] a = (String[])l.get(i);
				for(int j=0;j<a.length;j++){
					dc.setString(j+1,a[j]);
				}			
				dc.addBatch();
			}
			dc.setAutoCommit(true);
			dc.executeBatch();
			return true;
		}catch(Exception e){
			System.out.println("DBAccess.mulExecute():" + e.getMessage());
			e.printStackTrace();
			try{
				dc.rollback();
				return false;
			}catch(Exception ee){
				ee.printStackTrace();
				System.out.println("rollback failed");
				return false;
			}finally{
				dc.close();
				dc = null;
			}
		}
	}
	
	public boolean execute(String sql){
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			return dc.execute();
		}catch(Exception e){
			System.out.println("DBAccess.execute():" + e.getMessage());
			e.printStackTrace();
			return false;
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public boolean executeUpdate(String sql,List l){
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			for(int i=0;i<l.size();i++){
				String colType = l.get(i).getClass().getName();
				if(("java.sql.Date").equals(colType))
					dc.setDate(i+1,java.sql.Date.valueOf(l.get(i).toString()));
				else if(("java.sql.Timestamp").equals(colType))
					dc.setTimestamp(i+1,java.sql.Timestamp.valueOf(l.get(i).toString()));
				else
					dc.setString(i+1,l.get(i).toString());
			}
			return dc.execute();
		}catch(Exception e){
			System.out.println("DBAccess.executeUpdate():" + e.getMessage());
			return false;
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public List parseSQL(String sql,List l){
		List RESULT = new ArrayList();
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			for(int i=0;i<l.size();i++){
				dc.setString(i+1,l.get(i).toString());
			}
			dc.rs = dc.prepstmt.executeQuery();
			while(dc.rs.next()){
				ResultSetMetaData md = dc.rs.getMetaData();
				int length = md.getColumnCount();
				for(int i=0;i<length;i++){
					RESULT.add(dc.rs.getString(i+1));
				}
			}
			dc.rs.close();
			dc.rs = null;
			return RESULT;
		}catch(Exception e){
			System.out.println("DBAccess.parseSQL():" + e.getMessage());
			e.printStackTrace();
			return null;
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public List mulparseSQL(String sql,List l){
		List RESULT = new ArrayList();
		IndexMap hm;
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			for(int i=0;i<l.size();i++){
				dc.setString(i+1,l.get(i).toString());
			}
			dc.rs = dc.prepstmt.executeQuery();
			ResultSetMetaData md = dc.rs.getMetaData();
			int length = md.getColumnCount();
			while(dc.rs.next()){
				hm = new IndexMap();
				for(int i=0;i<length;i++){
					String ColName = md.getColumnName(i+1);
					hm.put(ColName,dc.rs.getString(ColName));
				}
				RESULT.add(hm);
			}
			dc.rs.close();
			dc.rs = null;
			return RESULT;
		}catch(Exception e){
			System.out.println("DBAccess.mulparseSQL():" + e.getMessage());
			e.printStackTrace();
			return null;
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	/*
	 * 大数据量分页函数（for oracle）
	 *sql/条件/页大小/页数/主键
	 *不支持取所有记录集
	 */
	public String pagination(String sql,IndexMap co,int pageSize,int pageIndex,String pk){
		int sign = (pageIndex-1)*pageSize+1; //游标的目标位置
		int resCount = pageSize*pageIndex;	//设置需要取得的最多记录数
		//如果游标超出提取范围，则返回失败
		if(sign>resCount)
			return "";
		StringBuffer RESULT = new StringBuffer();
		DbBean dc = new DbBean();
		try{
			//将普通SQL语句转换成查询count的语句，replace方法的参数注意大小写
			String countSql = sql.replaceAll("SELECT "+pk,"select count("+pk+")");
			this.getCount(countSql,co);
			if(this.COUNT>0&&sign<=this.COUNT){//如果游标超出实际记录集范围，则返回失败
				dc.prepareStatement(sql);
				//开始设置参数
				dc.prepstmt.setFetchSize(50);
				dc.prepstmt.setMaxRows(resCount);
				//end参数设置
				int j = 1;
				for(int i=0;i<co.size();i++){//传入查询条件
					if(Data.hasValue(co.get(i).toString())){
						dc.setString(j,co.get(i).toString());
						j++;
					}
				}
				dc.rs=dc.executeQuery();
				if(dc.rs.next()){
					dc.rs.absolute(sign);
					int i=0;
					do{
						if (i==0)
							RESULT.append(dc.rs.getString(1));
						else{
							RESULT.append(",");
							RESULT.append(dc.rs.getString(1));
						}
						i++;
					}while (dc.rs.next());
					if (this.COUNT>resCount*pageIndex)
						this.EOF=false;
					else
						this.EOF=true;
				}
			}
		}catch(Exception e){
			System.out.println("DBAccess.pagination():" + e.getMessage());
			e.printStackTrace();
			return "";
		}finally{
			dc.close();
			dc = null;
		}
		return RESULT.toString();
	}
	
	/*
	 * 取查询记录数
	 */
	private void getCount(String sql,IndexMap co){
		DbBean dc = new DbBean();
		try{
			dc.prepareStatement(sql);
			int j = 1;
			for(int i=0;i<co.size();i++){
				if(Data.hasValue(co.get(i).toString())){
					dc.setString(j,co.get(i).toString());
					j++;
				}
			}
			dc.rs=dc.executeQuery();
			dc.rs.next();
			this.COUNT = dc.rs.getInt(1);
			if(this.COUNT>10000) this.COUNT=1000;//设置最大数据量
			dc.rs.close();
			dc.rs = null;
		}catch(Exception e){
			System.out.println("DBAccess.getCount():" + e.getMessage());
		}finally{
			dc.close();
			dc = null;
		}
	}
	
	public static void main(String[] arg){
		DBAccess d = new DBAccess();
		String sql = "SELECT IDNumber from members where Sex=? order by IDNumber";
		IndexMap im=new IndexMap();
		im.put("sex","0");
		System.out.println(d.pagination(sql,im,20000,1,"IDNumber"));
		System.out.println(d.EOF);
	}
}