sqlaccess.java

主要考察的是资源池的设计和实现

package ServerImpl;
import java.sql.*;
import java.util.Random;
import java.text.DecimalFormat;
public class SqlAccess {
    private static String strurl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=Banking.mdb";
    private Connection conn = null;
    private PreparedStatement pre = null;
    private ResultSet rs = null;
    
    public void Release()throws Exception
    {
        if(pre != null) pre.close();
        if(conn != null) conn.close();
    }
    
    public void InitDB()throws Exception
    {
        Random random = new Random();
        DecimalFormat formatter = (DecimalFormat)DecimalFormat.getInstance();
        formatter.applyPattern("0000000000");
        String id,name;
        double balance;
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(strurl);
            pre = conn.prepareStatement("insert into [Accounts] (_id,_name,_balance) values(?,?,?)");
            for(int count = 0; count < 10000; ++count)
            {
                id = formatter.format(count);
                name = id.substring(6);
                balance = random.nextInt(10000000) / 100.0;
                pre.clearParameters();
                pre.setString(1,id);
                pre.setString(2,name);
                pre.setDouble(3,balance);
                pre.execute();
            }    
        }catch(SQLException e){
            throw e;
        }catch(Exception e){
            throw e;
        }finally{
            Release();
        }
    }
    
    public boolean Account_get(AccountImpl account) throws Exception{
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(strurl);
            pre = conn.prepareStatement("select [_name],[_balance] from [Accounts] where [_id]=? ");
            rs = pre.executeQuery();
            if(rs.next()){
                account.setName(rs.getString(1));
                account.setBalance(rs.getDouble(2));
                return true;
            }
            else 
                return false;
        }catch(SQLException e){
            throw e;
        }catch(Exception e){
            throw e;
        }finally{
            Release();
        }
    } 
    
    public void Account_update(AccountImpl account) throws Exception{
        try{
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            conn = DriverManager.getConnection(strurl);
            pre = conn.prepareStatement("update [Accounts] set [_balance]=? where [_id]=? ");
            pre.setDouble(1,account.getBalance());
            pre.setString(2,account.getID());
            pre.execute();
        }catch(SQLException e){
            throw e;
        }catch(Exception e){
            throw e;
        }finally{
            Release();
        }
    }  
}