Posted on 2009-05-19 09:42
Gavin.lee 閱讀(318)
評論(0) 編輯 收藏 所屬分類:
JDBC
這個DB Bean 用起來還不錯。挺好的，我在project2中就用這個，感覺很好用。性能也還不錯。比我上一次發布的明顯要好的多：
db.properties：
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;databaseName=bank;
username=sa
password=pass
load db.properties into program：
package commons.util;
import java.util.ResourceBundle;
/** *//**
* @descripte load resource
* @author Gavin.lee
* @date 2009-5-19上午09:49:32
* @version 1.0
*
*/
public class ResourceLoader 
{
private ResourceBundle resBundle;
public ResourceLoader(String resourceName) {
resBundle = ResourceBundle.getBundle(resourceName);
}
public String getString(String key) {
return resBundle.getString(key);
}
}
package commons.util;
import java.sql.*;
/** *//**
* @descripte connection provider
* @author Gavin.lee
* @date 2009-5-19上午09:49:08
* @version 1.0
*
*/
public class ConnectionProvider {
private String driverClassName;
private String url;
private String username;
private String password;
public ConnectionProvider() {
ResourceLoader loader = new ResourceLoader("db");
this.driverClassName = loader.getString("driverClassName");
this.url = loader.getString("url");
this.username = loader.getString("username");
this.password = loader.getString("password");
}
public Connection getConnection() {
return this.getsqlconnection();
}
private Connection getsqlconnection() {
Connection conn = null;
try {
Class.forName(driverClassName);
conn = DriverManager.getConnection(url,username, password);
} catch(Exception e) {
e.printStackTrace();
}
return conn;
}
}
example：
ConnectionProvider cp = new ConnectionProvider();
/** *//**
* 開戶的帳號通過驗證后就通過此方法向表中插入新的記錄。
*
*/
public boolean addAccount(Account account){
String sql = "insert into account(accountID,password,name,sex,idcard,balance) values(?,?,?,?,?,?)";
try{
Connection conn = cp.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,account.getAccountID());
ps.setString(2, account.getPassword());
ps.setString(3,account.getName());
ps.setString(4,account.getSex());
ps.setString(5, account.getIdcard());
ps.setDouble(6, account.getBalance());
ps.execute();
ps.close();
conn.close();
return true;
}catch(Exception e){
e.printStackTrace();
}
return false;
}
/** *//**
* 刪除記錄
*/
public boolean deleteAccount(String accountID){
Connection conn = cp.getConnection();
String sql = "delete account where accountID=?";
try {
PreparedStatement ps = conn.prepareStatement(sql);//PrepareStatement接口： 用戶封裝SQL語句
ps.setString(1,accountID);
if(ps.executeUpdate() != 0){//對于DML語句，executeUpdate返回的是影響的行數
return true;
}
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
/** *//**
* 用于客戶修改密碼，轉賬，存取款，凍結，解凍
*/
public boolean updateAccount(Account account){
String sql = "update account set password=?,balance=?,isfreezed=? where accountID=?";
try{
Connection conn = cp.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, account.getPassword());
ps.setDouble(2, account.getBalance());
/**//* isfreezed */
ps.setInt(3,account.getIsfreezed());
ps.setString(4,account.getAccountID());
ps.execute();
ps.close();
conn.close();
return true;
}catch(Exception e){
e.printStackTrace();
}
return false;
}
//這個在登陸的時候就給初始化bean的信息
public Account findAccountById(String accountID){
Connection conn = cp.getConnection();
Account account = new Account();
String sql = "select * from account where accountID=?";
try{
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,accountID);
ResultSet rs = ps.executeQuery();//rs是滿足條件的查詢結果集
if(rs.next()){//初始狀態指向結果集第一條前
account.setAccountID(rs.getString("accountID"));
account.setPassword(rs.getString("password"));
account.setName(rs.getString("name"));
account.setSex(rs.getString("sex"));
account.setIdcard(rs.getString("idcard"));
account.setBalance(rs.getDouble("balance"));
account.setIsfreezed(rs.getInt("isfreezed"));
return account;
}
rs.close();
ps.close();
conn.close();
//return account;
}catch(Exception e){
e.printStackTrace();
}