Posted on 2009-05-19 09:42
Gavin.lee 阅读(308)
评论(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();
}