阶段复习之jdbc连接数据库（四）——封装法（代码详细完整）

在优化篇中还有什么问题##

• 阶段复习之jdbc连接数据库（三）——优化篇https://blog.csdn.net/IManiy/article/details/82382165
• jdbDriver等常量写死在java代码中，编译后无法修改
• 没有写全面，如查询多条数据、数据总条数等

本文详细介绍最终版本jdbc连接数据库#

• 先看项目结构（记得到添加mysql的jar）

数据库文件

CREATE TABLE `user` (
  `userId` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `sex` int(1) DEFAULT '1' COMMENT '1表示男，0表示女',
  `flag` int(1) DEFAULT '1' COMMENT '1表示正常，0表示不正常',
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

实体类

public class User {
    private int userId;
    private String username;
    private String password;
    private int sex;
    private int flag;
    public int getUserId() {
        return userId;
    }
    public void setUserId(int userId) {
        this.userId = userId;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public int getSex() {
        return sex;
    }
    public void setSex(int sex) {
        this.sex = sex;
    }
    public int getFlag() {
        return flag;
    }
    public void setFlag(int flag) {
        this.flag = flag;
    }
    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", sex=" + sex +
                ", flag=" + flag +
                '}';
    }
}

jdbc.properties

jdbDriver=com.mysql.jdbc.Driver
jdbcURL=jdbc:mysql://localhost:3306/jdbctest?characterEncoding=utf-8
jdbcUser=root
jdbcPassword=123456

工具类 DBConnectionUtil

public class DBConnectionUtil {
    private static String  jdbcDriver= "";
    private static String  jdbcURL="";
    private static String  jdbcUser="";
    private static String  jdbcPassword="";
    //静态初始化块只会在类加载的时候执行一次
    static {
        //读取jdbc.properties文件
        InputStream is=DBConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties p=new Properties();
        try {
            p.load(is);

            jdbcDriver =p.getProperty("jdbDriver");
            jdbcURL=p.getProperty("jdbcURL");
            jdbcUser=p.getProperty("jdbcUser");
            jdbcPassword=p.getProperty("jdbcPassword");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //创建
    public static Connection CreateConnection(){
        //获取连接
        Connection conn=null;
        //产生执行sql语句的对象
        PreparedStatement ps=null;
        //加载驱动
        try {
            Class.forName(jdbcDriver);
            conn= DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPassword);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //关闭
    public static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){
        //关闭资源，先产生的后关闭
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                if(ps !=null){
                    try {
                        ps.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }finally {
                        if (conn !=null){
                            try {
                                conn.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }

}

dao层 Userdao

public class UserDao {
    //保存user
    public boolean saveUser(User user) {
        boolean flag = false;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionUtil.CreateConnection();
            String sql = "INSERT INTO user (username,password) VALUES (?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            int rows = ps.executeUpdate();
            if (rows > 0) {
                flag = true;
                System.out.println("插入数据成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.closeAll(null, ps, conn);
        }
        return flag;
    }
    //通过id查询user
    public User selectUserByUserId(int userId) {
        User user = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBConnectionUtil.CreateConnection();
            String sql = "SELECT * FROM user WHERE userId=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUserId(rs.getInt("userId"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setSex(rs.getInt("sex"));
                user.setFlag(rs.getInt("flag"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.closeAll(rs, ps, conn);
        }
        return user;
    }
    //查询所有user
    public List<User> getAllUserList(){
        List<User> userList =new ArrayList<User>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=DBConnectionUtil.CreateConnection();
            String sql="select * from user";
            ps=conn.prepareStatement(sql);
            rs =ps.executeQuery();
            while (rs.next()){
                User user=new User();
                user.setUserId(rs.getInt("userId"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setSex(rs.getInt("sex"));
                user.setFlag(rs.getInt("flag"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBConnectionUtil.closeAll(rs,ps,conn);
        }
        return userList;
    }
    //获取总条数
    public int getAllUserCount() {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBConnectionUtil.CreateConnection();
        String sql = "select COUNT(*) 数量 from user";//取别名
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt("数量");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.closeAll(rs, ps, conn);
        }
        return count;
    }
    //通过id删除user
    public boolean deleteByUserId(int userId) {
        boolean flag = false;
        //获取连接
        Connection conn = null;
        //产生执行sql语句的对象
        PreparedStatement ps = null;
        //加载驱动
        try {
            conn = DBConnectionUtil.CreateConnection();
            System.out.println(conn);
            //删除一条记录
            String sql="DELETE from `user` where userId=?";
            ps = conn.prepareStatement(sql);
            //参数赋值
            ps.setInt(1,userId);
            int rows = ps.executeUpdate();
            if (rows > 0) {
                flag=true;
                System.out.println("删除数据成功！");
            }
        }  catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源，先产生的后关闭
            DBConnectionUtil.closeAll(null,ps,conn);
        }
        return true;
    }
    //通过id修改password
    public boolean updateUser(User user){
        boolean flag = false;
        Connection conn =null;
        PreparedStatement ps = null;
        try {
            conn = DBConnectionUtil.CreateConnection();
            String sql ="update user set password =? where  userId=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,user.getPassword());
            ps.setInt(2,user.getUserId());
            int rows = ps.executeUpdate();
            if (rows > 0) {
                flag=true;
                System.out.println("修改数据成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBConnectionUtil.closeAll(null,ps,conn);
        }
        return  flag;
    }
}

进行测试

public class Test {
    public static void main(String[] args) {
        //查询
        UserDao userDao=new UserDao();
        System.out.println(userDao.selectUserByUserId(6));
        //添加
        User user=new User();
        user.setUsername("cool");
        user.setPassword("123456");
        userDao.saveUser(user);
        //删除
        userDao.deleteByUserId(7);
        //通过id修改password
        User user2=new User();
        user2.setUserId(8);
        user2.setPassword("12345678");
        userDao.updateUser(user2);

        System.out.println(userDao.getAllUserCount());
    }
}