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

亦凉 2022-05-14 07:51 204阅读 0赞

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

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

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

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

![这里写图片描述][70]

## 数据库文件 ##

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());
        }
    }

[70]: /images/20220514/76b3d44dd74746b195d924359803c828.png