普通方法:
CommonDao.java
public class CommonDao {
/**
* 此方法是公共方法,专门获取数据库连接
* @return
*/
public static Connection getConnection(){
Connection con=null;
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb","root","0118");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
/**
* 此方法是公共方法,专门用来关闭数据库资源
* @param rs
* @param pstmt
* @param con
*/
public static void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con){
try {
if(rs!=null){
rs.close();
}if(pstmt!=null){
pstmt.close();
}if(con!=null){
con.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
UserDao.java
public class UserDao implements UserDao{
@Override
public User login(String userName, String userPassword) throws SQLException {
// TODO Auto-generated method stub
User user = null;
Connection con = CommonDao.getConnection();
String sql="select id from t_user where username=? and password=?";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1, userName);
pstmt.setString(2,userPassword);
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
user = new User();
user.setUserId(rs.getInt("user_id"));
user.setUserName(rs.getString("user_name"));
user.setUserPassword(rs.getString("user_password"));
}
CommonDao.closeAll(rs, pstmt, con);
return user;
}
@Override
public int addUser(User user) throws SQLException {
// TODO Auto-generated method stub
int rowAffect = 0;
Connection con = CommonDao.getConnection();
String sql="insert into t_user(username,password) values(?,?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getUserPassword());
rowAffect = pstmt.executeUpdate(sql);
CommonDao.closeAll(null, pstmt, con);
return rowAffect ;
}
优化:策略模式
UserDao中存在大量的重复代码,比如数据库的连接与关闭,PrepareStatement的数据填充操作,不同的是sql语句和对ResultSet结果集的操作,所以可以使用策略模式对代码进行优化
策略模式:http://www.runoob.com/design-pattern/strategy-pattern.html
RowMapper.java
public interface RowMapper<T> {
public T mapRow(ResultSet rs) throws SQLException;
}
CommonDao.java
public class CommonDao {
/**
* 此方法是公共方法,专门获取数据库连接
* @return
*/
public static Connection getConnection(){
Connection con=null;
try {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb","root","0118");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
/**
* 此方法是公共方法,专门用来关闭数据库资源
* @param rs
* @param pstmt
* @param con
*/
public static void closeAll(ResultSet rs,PreparedStatement pstmt,Connection con){
try {
if(rs!=null){
rs.close();
}if(pstmt!=null){
pstmt.close();
}if(con!=null){
con.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
/**
* 通用更新
* @param sql DML语句
* @param params 参数
* @return
* @throws SQLException
*/
public static int executeUpdate(String sql,Object...params) throws SQLException{
int rowAffect = 0;
Connection con = getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
if(params!=null){
for(int i=0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
rowAffect = pstmt.executeUpdate();
closeAll(null, pstmt, con);
return rowAffect;
}
/**
* 通用查询
* @param sql
* @param params
* @param rm
* @return
* @throws SQLException
*/
public static <T> List<T> executeQuery(String sql,RowMapper<T> rm,Object...params) throws SQLException{
List<T> list = new ArrayList<T>();
Connection con = getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
if(params!=null){
for(int i=0;i<params.length;i++){
pstmt.setObject(i+1, params[i]);
}
}
ResultSet rs =