jdbc BaseDao

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

public class BaseDao {
    
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/chaoshiguanli";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";
    //统一的加载驱动获得连接方法
    public Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    //统一的关闭方法
    public void closeResource(Connection conn,Statement stmt,ResultSet rs){
        try {
            if(rs!=null)
                rs.close();
            if(stmt!=null)
                stmt.close();
            if(conn!=null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //统一的增删改方法
    public int executeUpdate(String sql,Object[] objs){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if(objs!=null){
                for(int i=0;i<objs.length;i++){
                    if(objs[i] instanceof java.util.Date){
                        objs[i] = new Timestamp(((java.util.Date)objs[i]).getTime());
                    }
                    ps.setObject(i+1, objs[i]);
                }
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }finally{
            closeResource(conn, ps, null);
        }
    }

}

查询

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pb.entity.Users;

public class UsersDaoImpl extends BaseDao implements UsersDao {

    @Override
    public List<Users> findAll() {
        List<Users> list = new ArrayList<Users>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "select * from users";
        try{
            conn = super.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while(rs.next()){
                Users u = new Users();
                u.setUserId(rs.getInt("userid"));
                u.setUserName(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setSex(rs.getInt("sex"));
                u.setAge(rs.getInt("age"));                
                u.setPhoneNumber(rs.getNString("phoneNumber"));
                u.setAddress(rs.getNString("address"));
                u.setRole(rs.getInt("role"));
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            super.closeResource(conn, stmt, rs);
        }
        return list;
    }


    @Override
    public Users findById(int id) {
        Users u = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from users where userid=?";
        try{
            conn = super.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if(rs.next()){
                 u = new Users();
                u.setUserId(rs.getInt("userid"));
                u.setUserName(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setSex(rs.getInt("sex"));
                u.setAge(rs.getInt("age"));                
                u.setPhoneNumber(rs.getNString("phoneNumber"));
                u.setAddress(rs.getNString("address"));
                u.setRole(rs.getInt("role"));
                
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            super.closeResource(conn, ps, rs);
        }
        return u;
    }