JDBC例子?

[ruanwxh]

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package com.soft.database;
import java.sql.*;
import java.util.*;
import java.io.*;
/**
*
* @author Administrator
*/
public class AccessDB {
private String driverName = null;
private String dbUrl = null;
private String userId = null;
private String userPwd = null;
private Connection dbConn = null;
private Statement stmt = null;
private ResultSet rset = null;
private Enumeration enumer;
private int rsColNum;
private int rsRowNum;
private int rsTotalRowNum;
private String rowStr[];
private Vector vctr;
private Hashtable hshtbl;

public AccessDB(String file)
{
try{
Properties prop = new Properties();
FileInputStream fis = new FileInputStream(file);
prop.load(fis);
this.driverName = prop.getProperty("ora.driver");
this.dbUrl = prop.getProperty("ora.url");
this.userId = prop.getProperty("ora.username");
this.userPwd = prop.getProperty("ora.password");
}catch(Exception e){
System.out.println("AccessDB()." + e.getMessage());
}
}

public Connection getConn() throws Exception
{
try {
releaseConn();
Class.forName(driverName);
dbConn = DriverManager.getConnection(dbUrl,userId,userPwd);
dbConn.setAutoCommit(true);
} catch (Exception e) {
System.out.println("get connection fail." + e);
dbConn=null;
}
return dbConn;
}

public void releaseConn()
{
try {
if(rset != null) {
rset.close();
rset = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch (Exception e) {
System.out.println("close ResultSet or Statement fail." + e.getMessage());
}
try {
if(dbConn != null) {
dbConn.close();
dbConn = null;
}
} catch (Exception e) {
System.out.println("release Conn fail." + e.getMessage());
}
}

public int executeUpdate(String sql) throws Exception
{
try {
dbConn=getConn();
if(dbConn == null)
return -1;
if(stmt != null) {
stmt.close();
stmt = null;
}
stmt = dbConn.createStatement();
if(stmt == null)
return -2;
stmt.executeUpdate(sql);
return 0;
} catch(Exception e) {
System.out.println("executeUpdate["+ sql +"] fail." + e.getMessage());
return -99;
} finally {
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch(Exception e) {
System.out.println("Err: executeUpdate() close stmt fail.");
return -88;
}
}
}

public int executeQuery(String sql) throws Exception
{
try {
dbConn=getConn();
if(dbConn == null)
return -1;
if(rset != null) {
rset.close();
rset = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
stmt = dbConn.createStatement();
if(stmt == null)
return -2;
rset = stmt.executeQuery(sql);
return 0;
} catch (Exception e) {
System.out.println("executeQuery["+ sql +"] fail." + e.getMessage());
return -99;
}
}

public int executeQuery(String sql,int i,int j) throws Exception//分页查询
{
try {
dbConn=getConn();
if(dbConn == null)
return -1;
if(rset != null) {
rset.close();
rset = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
stmt = dbConn.createStatement();
if(stmt == null)
return -2;
rset = stmt.executeQuery(sql);
getResult(rset, i, j);
return 0;
} catch (Exception e) {
System.out.println("executeQuery["+ sql +"] fail." + e.getMessage());
return -99;
}
}
private int getResult(ResultSet resultset, int i, int j)throws Exception
{
Object obj = null;
Object obj1 = null;
enumer = null;
rowStr = null;
vctr.clear();
hshtbl.clear();
rsColNum = 0;
rsRowNum = 0;
rsTotalRowNum = 0;
if(resultset == null)
return -1;
ResultSetMetaData resultsetmetadata;
resultsetmetadata = resultset.getMetaData();
if(resultsetmetadata == null)
return -1;
try
{
rsColNum = resultsetmetadata.getColumnCount();
for(int k = 1; k <= (i - 1) * j && resultset.next(); k++)
rsTotalRowNum++;
for(int l = 0; l < j && resultset.next(); l++)
{
String as[] = new String[rsColNum + 1];
for(int j1 = 1; j1 <= rsColNum; j1++)
{
as[j1] = resultset.getString(j1);
if(as[j1] == null)
as[j1] = new String("");
}
vctr.add(as);
rsRowNum++;
rsTotalRowNum++;
}
while(resultset.next())
rsTotalRowNum++;
enumer = vctr.elements();
String as1[] = new String[rsColNum + 1];
for(int i1 = 1; i1 <= rsColNum; i1++)
{
as1[i1] = resultsetmetadata.getColumnName(i1).toUpperCase();
hshtbl.put(as1[i1], new Integer(i1));
}
return 0;
}
catch(Exception exception)
{
enumer = null;
}
return -1;
}

public int getInt(int index) throws Exception
{
try {
if(dbConn == null)
throw new Exception("Err: getInt Fail. ");
if(rset == null) {
throw new Exception("Err: getInt Fail. ");
}
return rset.getInt(index);
} catch (Exception e) {
System.out.println("Err:getInt fail." + e.getMessage());
throw new Exception("Err: getInt Fail. ");
}
}

public String getString(int index) throws Exception
{
try {
if(dbConn == null)
throw new Exception("Err: getString Fail. ");
if(rset == null) {
throw new Exception("Err: ggetString Fail. ");
}
return rset.getString(index);
} catch (Exception e) {
System.out.println("Err:getString fail." + e.getMessage());
throw new Exception("Err: getString Fail. ");
}
}
public int getRowNum()
{
return rsRowNum;
}
public int getTotalRowNum()
{
return rsTotalRowNum;
}
public int getColNum()
{
return rsColNum;
}

public boolean next() throws Exception
{
try {
if(dbConn == null)
return false;
if(rset == null) {
return false;
}
return rset.next();
} catch (Exception e) {
System.out.println("Err:getString fail." + e.getMessage());
return false;
}
}
}

写了一个JDBC例子,在细节上(特别是资源释放上,异常处理等方面)肯定有很多错误.请各位高手大大们指出,哪些需要改进?分页查询函数executeQuery(String sql,int i,int j)不是自己写的,是反编绎过来,其中可能有错.也请高手指出.随便讲下为什么?好像是用ResultSet、（游标？）进行分页，具体不是很懂

[ruanwxh]

代码格式不是很好，不知道为什么，复制过来就变成这样，实在不好意思

[yanliang_xt]

在整理代码的同时也学习了下，希望指出代码的不足之处，可以让大家学到更多的知识。

package   com.soft.database; 

import   java.sql.*; 
import   java.util.*; 
import   java.io.*; 
//数据库的操作类
public   class   AccessDB   { 
	private   String driverName         =   null; 
	private   String dbUrl              =   null; 
	private   String userId             =   null; 
	private   String userPwd            =   null; 
	private   Connection dbConn         =   null; 
	private   Statement stmt            =   null; 
	private   ResultSet rset            =   null; 
	private   Enumeration enumer;    //最终Vector里保存的数据放到了枚举中
	private   int   rsColNum;        //
	private   int   rsRowNum;        
	private   int   rsTotalRowNum;   //记录总数
	private   String   rowStr [];     // 这个没有用到过。。可删除
	private   Vector   vctr;         //用来保存数据,里面全是一个一个字符串数组封装的记录
	private   Hashtable   hshtbl;   //保存数据表中的字段的名称

	public   AccessDB(String   file) 
	{ 
		//通过加载属性文件进行相关初始化
		try{ 
			  Properties  prop  =  new  Properties(); 
			  FileInputStream  fis  =  new  FileInputStream(file); 
			  prop.load(fis); 
			  this.driverName  =  prop.getProperty( "ora.driver "); 
			  this.dbUrl  =  prop.getProperty( "ora.url "); 
			  this.userId  =  prop.getProperty( "ora.username "); 
			  this.userPwd  =  prop.getProperty( "ora.password "); 
		}catch(Exception  e){ 
			System.out.println( "AccessDB(). "  +  e.getMessage()); 
		} 
	} 
	//产生数据连接
	public  Connection  getConn()  throws  Exception 
	{ 
		try{ 
			releaseConn();  //先释放
			Class.forName(driverName); 
			dbConn  =  DriverManager.getConnection(dbUrl,userId,userPwd); 
			dbConn.setAutoCommit(true);  //设置自动提交
		}catch(Exception   e)   { 
			System.out.println( "get connection fail. " + e); 
			dbConn=null; 
		} 
		return dbConn; 
	} 
	//关闭连接，释放资源
	public void releaseConn() 
	{ 
		try{ 
			if( rset != null ) {  //ResultSet close
				rset.close() ; 
				rset = null ; 
			} 
			if(stmt != null) {    //Statement close
				stmt.close() ; 
				stmt = null ; 
			 } 
		} catch(Exception e) { 
			System.out.println( "close ResultSet or Statement fail. " + e.getMessage()) ; 
		} 
		try { 
			if(dbConn != null)   { 
				dbConn.close() ;  //Connection close
				dbConn = null ; 
			} 
		}catch(Exception e) { 
			System.out.println( "release Conn fail. " + e.getMessage()) ; 
		} 
	} 
	//执行sql语句的更新操作如增加，删除。成功返回"0"，
	public int executeUpdate(String sql) throws Exception 
	{ 
		try{ 
			dbConn = getConn() ;   //get connection
			if( dbConn == null) 
				return -1; 
			if( stmt != null )   { 
				stmt.close() ; 
				stmt = null ; 
			} 
			stmt= dbConn.createStatement();  //get statement
			if(stmt == null) 
				return -2; 
			stmt.executeUpdate(sql);    //execute
			return 0; 
		} catch(Exception e) { 
			System.out.println( "executeUpdate[ "+ sql + "] fail. " + e.getMessage() ); 
			return -99; 
		} finally { 
			try { 
				if(stmt!=null){ 
					stmt.close(); 
					stmt = null; 
				} 
			}catch(Exception e) { 
				System.out.println( "Err: executeUpdate() close stmt fail. ") ; 
				return -88; 
			} 
		} 
	} 
	//执行sql的查询操作 成功返回"0"
	public int executeQuery(String sql) throws Exception 
	{ 
		try { 
			dbConn = getConn() ;   //--
			if( dbConn == null ) 
				return  -1 ; 
			if( rset != null ){    //--
				rset.close() ; 
				rset = null ; 
			} 
			if(stmt != null) {     //--
				stmt.close(); 
				stmt = null; 
			} 
			stmt = dbConn.createStatement(); 
			if(stmt == null) 
				return -2; 
			rset = stmt.executeQuery(sql);  //--
			return 0; 
		}  catch (Exception  e) { 
			System.out.println( "executeQuery[ "+ sql + "] fail. " + e.getMessage()); 
			return -99; 
		}   
	} 
	//用来获取指定页数的数据。参数i是当前的页数， j 是每页的记录条数,成功返回"0"
	public int executeQuery(String sql,int i,int j) throws Exception//分页查询 
	{ 
		try { 
			dbConn = getConn() ;     
			if( dbConn == null ) 
				return -1; 
			if( rset != null )   { 
				rset.close(); 
				rset = null; 
			} 
			if(stmt != null)   { 
				stmt.close(); 
				stmt = null; 
			} 
			stmt = dbConn.createStatement() ; 
			if(stmt == null) 
				return -2; 
			rset = stmt.executeQuery(sql); 
			getResult(rset, i, j); 
			return 0; 
		}   catch (Exception e) { 
			System.out.println( "executeQuery[ "+ sql + "] fail. " + e.getMessage() ) ; 
			return  -99; 
		} 
	} 
	// 参数i是当前的页数 j 是每页的记录条数
	private int getResult(ResultSet resultset, int i, int j)throws Exception 
	{ 
			Object   obj    =   null; 
			Object   obj1   =   null; 
			enumer   =   null;      //用来保存数据,里面全是一个一个字符串数组封装的记录
			rowStr   =   null;      //没有用到过。。。可删除
			vctr.clear(); 
			hshtbl.clear(); 
			rsColNum  =  0; 
			rsRowNum  =  0; 
			rsTotalRowNum  =  0; 
			if(resultset  ==  null) 
					return  -1; 
			ResultSetMetaData   resultsetmetadata; 
			resultsetmetadata  = resultset.getMetaData(); 
			if(resultsetmetadata  ==  null) 
					return  -1; 
			try 
			{ 
					rsColNum  =  resultsetmetadata.getColumnCount();  //返回ResultSet中的列数
					for(int k = 1; k <= (i - 1) * j && resultset.next(); k++) //让游标定位到第i页第一条数据之前
							rsTotalRowNum++;  //统计跨越了多少条记录
					for(int l = 0; l < j && resultset.next(); l++) //只要数据足够，就取j条数据
					{ 
							String as[] = new String[rsColNum + 1]; //每一个字符串数据就封装了一条记录，索引从1....rsColNum
							for(int j1 = 1; j1 <= rsColNum; j1++) //取出当前行的每一列数据 
																	//并保存到String数组中.
							{ 
								as[j1] = resultset.getString(j1);  
								if(as[j1] == null)               //注意null情况
									as[j1] = new String(" ");  //为空就保存为空串
							} 
							vctr.add(as);     //添加到Vector中去.
							rsRowNum++;       //每完成一行数据的装载就使当前标识行的数量加1
							rsTotalRowNum++;  //同时总行数也加1
					} 

					while(resultset.next()) //所有记录条数统计完成
							rsTotalRowNum++;  //统计剩余的记录条数，完善总行数
					enumer = vctr.elements(); //把数据放到枚举里
					String   as1[] = new String[rsColNum + 1];  //从1开始方便存取
					for(int i1 = 1; i1 <= rsColNum; i1++) 
					{		//把所有的列名都保存在一个HashTable，大写形式.
							as1[i1] = resultsetmetadata.getColumnName(i1).toUpperCase(); 
							hshtbl.put(as1[i1], new Integer(i1)); 
					} 
					return  0; 
			} 
			catch(Exception exception) 
			{ 
				enumer  = null; 
			} 
			return  -1; 
	} 
	//
	public int getInt(int index) throws Exception 
	{ 
		try { 
			if(dbConn == null) 
				throw new  Exception( "Err: getInt Fail. ") ; 
			if(rset == null) { 
				throw  new  Exception( "Err:   getInt   Fail. ") ; 
			} 
			return  rset.getInt(index); 
		}   catch (Exception   e)   { 
			System.out.println( "Err:getInt  fail. " + e.getMessage()); 
			throw  new  Exception( "Err:  getInt  Fail.  "); 
		} 
	} 
	//
	public String  getString(int index) throws Exception 
	{ 
		try { 
			if(dbConn  ==  null) 
				throw  new  Exception( "Err:   getString   Fail.   "); 
			if(rset  ==  null) { 
				throw  new  Exception( "Err:   ggetString   Fail.   "); 
			} 
			return  rset.getString(index); 
		} catch (Exception  e) { 
			System.out.println( "Err:getString   fail. "  +  e.getMessage()); 
			throw  new  Exception( "Err:   getString   Fail. "); 
		} 
	} 

	public int getRowNum() 
	{ 
			return  rsRowNum; 
	} 
	public int getTotalRowNum() 
	{ 
			return rsTotalRowNum; 
	} 
	public int getColNum() 
	{ 
			return rsColNum; 
	} 

    public boolean next() throws Exception 
	{ 
		try { 
			if(dbConn == null) 
				return   false; 
			if(rset == null)   { 
				return false; 
			} 
        return  rset.next(); 
		}catch (Exception  e) { 
			System.out.println( "Err:getString   fail. "   +   e.getMessage()); 
             return false; 
        } 
	} 
} 

[yanliang_xt]

感觉这是一个比较老的数据操作类。。。

[ouyangyh]

好像太复杂了
不同的数据库,有不同的分页方法的
不建议写一个通用的方法

[yanliang_xt]

专业代码整理工人：

package   com.soft.database; 
import   java.sql.*; 
import   java.util.*; 
import   java.io.*; 

public   class   AccessDB   { 
	private   String     driverName     =   null; 
	private   String     dbUrl          =   null; 
	private   String     userId         =   null; 
	private   String     userPwd        =   null; 
	private   Connection   dbConn       =   null; 
	private   Statement     stmt        =   null; 
	private   ResultSet     rset        =   null; 
	private   Enumeration   enumer; 
	private   int   rsColNum; 
	private   int   rsRowNum; 
	private   int   rsTotalRowNum; 
	private   String   rowStr[]; 
	private   Vector   vctr; 
	private   Hashtable   hshtbl; 

	public   AccessDB(String   file) 
	{ 
		try{ 
			  Properties   prop   =   new   Properties(); 
			  FileInputStream   fis   =   new   FileInputStream(file); 
			  prop.load(fis); 
			  this.driverName   =   prop.getProperty( "ora.driver "); 
			  this.dbUrl   =   prop.getProperty( "ora.url "); 
			  this.userId   =   prop.getProperty( "ora.username "); 
			  this.userPwd   =   prop.getProperty( "ora.password "); 
		}catch(Exception   e){ 
			System.out.println( "AccessDB(). "   +   e.getMessage()); 
		} 
	} 

	public   Connection   getConn()   throws   Exception 
	{ 
		try{ 
			releaseConn(); 
			Class.forName(driverName); 
			dbConn   =   DriverManager.getConnection(dbUrl,userId,userPwd); 
			dbConn.setAutoCommit(true); 
		}catch(Exception   e)   { 
			System.out.println( "get   connection   fail. "   +   e); 
			dbConn=null; 
		} 
		return dbConn; 
	} 

	public   void   releaseConn() 
	{ 
		try{ 
			if(rset   !=   null)   { 
				rset.close(); 
				rset   =   null; 
			} 
			if(stmt   !=   null)   { 
				stmt.close(); 
				stmt   =   null; 
			 } 
		} catch(Exception   e)   { 
			System.out.println( "close   ResultSet   or   Statement   fail. "   +   e.getMessage()); 
		} 
		try { 
			if(dbConn   !=   null)   { 
				dbConn.close(); 
				dbConn   =   null; 
			} 
		}catch(Exception   e)   { 
			System.out.println( "release   Conn   fail. "   +   e.getMessage()); 
		} 
	} 

	public   int   executeUpdate(String   sql)   throws   Exception 
	{ 
		try{ 
			dbConn=getConn(); 
			if(dbConn == null) 
				return -1; 
			if(stmt != null)   { 
				stmt.close(); 
				stmt = null; 
			} 
			stmt= dbConn.createStatement(); 
			if(stmt == null) 
				return -2; 
			stmt.executeUpdate(sql); 
			return 0; 
		} catch(Exception e) { 
			System.out.println( "executeUpdate[ "+   sql   + "]   fail. "   +   e.getMessage()); 
			return -99; 
		} finally { 
			try { 
				if(stmt!=null){ 
					stmt.close(); 
					stmt = null; 
				} 
			}catch(Exception e)   { 
				System.out.println( "Err:   executeUpdate()   close   stmt   fail. "); 
				return   -88; 
			} 
		} 
	} 

	public   int   executeQuery(String   sql)   throws   Exception 
	{ 
		try   { 
			dbConn=getConn(); 
			if(dbConn   ==   null) 
				return   -1; 
			if(rset   !=   null)   { 
				rset.close(); 
				rset   =   null; 
			} 
			if(stmt   !=   null)   { 
				stmt.close(); 
				stmt   =   null; 
			} 
			stmt   =   dbConn.createStatement(); 
			if(stmt   ==   null) 
				return   -2; 
			rset   =   stmt.executeQuery(sql); 
			return   0; 
		}  catch   (Exception   e)   { 
			System.out.println( "executeQuery[ "+   sql   + "]   fail. "   +   e.getMessage()); 
			return   -99; 
		}   
	} 

	public     int   executeQuery(String   sql,int   i,int   j)   throws   Exception//分页查询 
	{ 
		try   { 
			dbConn=getConn(); 
			if(dbConn   ==   null) 
				return   -1; 
			if(rset   !=   null)   { 
				rset.close(); 
				rset   =   null; 
			} 
			if(stmt   !=   null)   { 
				stmt.close(); 
				stmt   =   null; 
			} 
			stmt   =   dbConn.createStatement(); 
			if(stmt   ==   null) 
				return   -2; 
			rset   =   stmt.executeQuery(sql); 
			getResult(rset,   i,   j); 
			return   0; 
		}   catch   (Exception   e)   { 
			System.out.println( "executeQuery[ "+   sql   + "]   fail. "   +   e.getMessage()); 
			return   -99; 
		} 
	} 
	private   int   getResult(ResultSet   resultset,   int   i,   int   j)throws   Exception 
	{ 
			Object   obj   =   null; 
			Object   obj1   =   null; 
			enumer   =   null; 
			rowStr   =   null; 
			vctr.clear(); 
			hshtbl.clear(); 
			rsColNum   =   0; 
			rsRowNum   =   0; 
			rsTotalRowNum   =   0; 
			if(resultset   ==   null) 
					return   -1; 
			ResultSetMetaData   resultsetmetadata; 
			resultsetmetadata   =   resultset.getMetaData(); 
			if(resultsetmetadata   ==   null) 
					return   -1; 
			try 
			{ 
					rsColNum   =   resultsetmetadata.getColumnCount(); 
					for(int k = 1; k <= (i - 1) * j && resultset.next(); k++) 
							rsTotalRowNum++; 
					for(int l = 0; l < j && resultset.next(); l++) 
					{ 
							String as[] = new String[rsColNum   +   1]; 
							for(int j1 = 1; j1 <= rsColNum; j1++) 
							{ 
									as[j1] = resultset.getString(j1); 
									if(as[j1] == null) 
											as[j1] = new String( " "); 
							} 
							vctr.add(as); 
							rsRowNum++; 
							rsTotalRowNum++; 
					} 
					while(resultset.next()) 
							rsTotalRowNum++; 
					enumer = vctr.elements(); 
					String   as1[] = new String[rsColNum + 1]; 
					for(int i1 = 1; i1 <= rsColNum; i1++) 
					{ 
							as1[i1] = resultsetmetadata.getColumnName(i1).toUpperCase(); 
							hshtbl.put(as1[i1], new Integer(i1)); 
					} 
					return   0; 
			} 
			catch(Exception   exception) 
			{ 
					enumer   =   null; 
			} 
			return   -1; 
	} 

	public   int   getInt(int   index)   throws   Exception 
	{ 
		try   { 
			if(dbConn   ==   null) 
				throw   new   Exception( "Err:   getInt   Fail.   "); 
			if(rset   ==   null)   { 
				throw   new   Exception( "Err:   getInt   Fail.   "); 
			} 
			return   rset.getInt(index); 
		}   catch   (Exception   e)   { 
			System.out.println( "Err:getInt   fail. "   +   e.getMessage()); 
			throw   new   Exception( "Err:   getInt   Fail.   "); 
		} 
	} 

	public   String   getString(int   index)   throws   Exception 
	{ 
		try   { 
			if(dbConn   ==   null) 
				throw   new   Exception( "Err:   getString   Fail.   "); 
			if(rset   ==   null)   { 
				throw   new   Exception( "Err:   ggetString   Fail.   "); 
			} 
			return   rset.getString(index); 
		}   catch   (Exception   e)   { 
			System.out.println( "Err:getString   fail. "   +   e.getMessage()); 
			throw   new   Exception( "Err:   getString   Fail.   "); 
		} 
	} 

	public int getRowNum() 
	{ 
			return  rsRowNum; 
	} 
	public int getTotalRowNum() 
	{ 
			return rsTotalRowNum; 
	} 
	public int getColNum() 
	{ 
			return rsColNum; 
	} 

    public boolean next() throws Exception 
	{ 
		try   { 
			if(dbConn == null) 
				return   false; 
			if(rset == null)   { 
				return false; 
			} 
        return   rset.next(); 
		}catch (Exception   e)   { 
			System.out.println( "Err:getString   fail. "   +   e.getMessage()); 
             return false; 
        } 
	} 
} 

[windforcecn]

在数据库操作完了再做一次释放connnect，加一段finally

一般dbConn.setAutoCommit(false);

成功了commit，出错了再rollback.

其实现在这个东西一般没人用了，基本上都是Spring或者hibernate把它包了一层，放入连接池里了，不用关心connnect是否关闭什么的，
ResultSet就是结果集合，ResultSet.next()就是游标滚动到下一个。

分页查询主要是数据库端的操作，比如Oralce有个隐藏RowNum的字段，在取出第二页时，可以限制Where RowNum < ? and RowNum > ?这类条件.