通过JDBC进行数据库操作（增删改查）

最新推荐文章于 2024-07-13 13:15:21 发布

鲸叫我照顾大海

最新推荐文章于 2024-07-13 13:15:21 发布

阅读量2.2k

点赞数

分类专栏： JavaWeb 文章标签： java

本文链接：https://blog.csdn.net/gyfd123/article/details/124393170

版权

JavaWeb 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

user表

CREATE table user(

id int  PRIMARY key auto_increment COMMENT'用户ID',
username VARCHAR(55) COMMENT'用户名',
password VARCHAR(55) COMMENT'密码'

);

insert into user value(1,'zs','12345');
insert into user value(2,'ls','123456');

pojo包（实体类User）

//实体类
public class User {
    private int id;
    private String username;
    private String password;

    public User(){

    }
    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

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

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

util工具包（JDBC_Util）

public class JDBC_Util {
    //创建Connection数据库连接对象方法
    public  static Connection connect(){
        Connection connection =null;
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取数据库用户信息和url 协议 端口号 本机名(ip地址)  数据库名 安全连接 useUnicode编码(支持中文编码) 设置中文字符集为utf8  时区
            String url = "jdbc:mysql://localhost:3306/bjsxt01?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String userName = "root";
            String passWord = "123456";
            connection = DriverManager.getConnection(url, userName, passWord);
        } catch (ClassNotFoundException e) {
                e.printStackTrace();
        } catch (SQLException e) {
                e.printStackTrace();
        }
        return connection;
    }
    //创建关闭资源方法
    public static void resource(ResultSet resultSet,PreparedStatement preparedStatement,Connection connection){

        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement!=null){
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Dao包（UserDao接口和UserDaoImpl实现类）

public interface UserDao {

    //查询所有用户    select * from user;
    List<User> selectAll();

    //查询单个用户  select * from user where id=?;
    User selectOne(int id);

    //添加用户  insert into user value(?,?,?);
    int insert(User user);

    //修改用户 update user set id=DEFAULT,username=?,password=? where id=?;
    int update(User user);

    //删除用户  delete form user where id =?
    int delete(int id);

    //查询所有用户 返回值是 list集合 无参数
    //查询单个用户 返回值是 user 参数为id
    //添加 修改 删除 返回值都是 int 参数分别为 user user id
}

//进行数据库操作
public class UserDaoImpl implements UserDao {

    //查看所有用户
    @Override
    public List<User> selectAll() {
        //注意list集合必须分配空间,不能为null
        List<User> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //获得connection对象
            con = JDBC_Util.getConnection();

            //预编译 执行sql
            String sql = "select * from user";
            ps = con.prepareStatement(sql);

            //返回查询结果集
            rs = ps.executeQuery();
            while (rs.next()){
                //根据索引获得每一个字段
                int id = rs.getInt(1);
                String username = rs.getString(2);
                String password = rs.getString(3);

                //将每个字段添加到用户对象中
                User user = new User(id, username, password);

                //将多个用户添加到集合中
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //调用工具类关闭资源
            JDBC_Util.myClose(con,ps,rs);
        }
        return list;
    }

    //查询单个用户
    @Override
    public User selectOne(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            con = JDBC_Util.getConnection();
            ps = con.prepareStatement("select * from user where id=?");

            ps.setInt(1,id);
            rs = ps.executeQuery();

            while (rs.next()){
                //根据字段名获取数据库中的每个字段
                int id1 = rs.getInt("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                //将每一个字段添加到用户对象中
                user = new User(id1, username, password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC_Util.myClose(con,ps,rs);
        }
        return user;
    }

    //添加用户
    @Override
    public int insert(User user) {
        Connection con = null;
        PreparedStatement ps = null;
        int i = 0;
        try {
           con = JDBC_Util.getConnection();

           ps = con.prepareStatement("insert into user values(DEFAULT,?,?)");

           ps.setString(1,user.getUsername());
           ps.setString(2,user.getPassword());

           i = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC_Util.myClose(con,ps,null);
        }
        return i;
    }

    //修改用户
    @Override
    public int update(User user) {
        Connection con = null;
        PreparedStatement ps = null;
        int i1 = 0;
        try {
            con = JDBC_Util.getConnection();
            ps = con.prepareStatement("update user set username=?,password=? where id =?");

            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setInt(3,user.getId());

           i1 = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC_Util.myClose(con,ps,null);
        }
        return i1;
    }

    //删除用户
    @Override
    public int delete(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        int i2 = 0;
        try {
            con = JDBC_Util.getConnection();

            ps = con.prepareStatement("delete from user where id=?");

            ps.setInt(1,id);
            i2 = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC_Util.myClose(con,ps,null);
        }
        return i2;
    }

}

service包（UserService接口和UserServiceImpl实现类）

public interface UserService {

    //查询所有用户
    List<User> selectAll();

    //查询单个用户
    User selectOne(int id);

    //添加用户
    int insert(User user);

    //修改用户
    int update(User user);

    //删除用户
    int delete(int id);
}

//处理业务代码
public class UserServiceImpl implements UserService {

    //声明创建数据库操作对象
    private UserDao userDao = new UserDaoImpl();

    //查询用户
    @Override
    public List<User> selectAll() {

        List<User> list = userDao.selectAll();

        return list;
    }
    //查询单个用户
    @Override
    public User selectOne(int id) {

        User user = userDao.selectOne(id);
        return user;
    }

    //添加用户
    @Override
    public int insert(User user) {

        int i = userDao.insert(user);
        return i;

    }

    //修改用户
    @Override
    public int update(User user) {
        int i = userDao.update(user);

        return i;
    }

    //删除用户
    @Override
    public int delete(int id) {

        int i = userDao.delete(id);

        return i;
    }
}

测试包（TestUser）

//测试类
public class TestUser {

    public static void main(String[] args) {
        //创建业务层对象
        UserServiceImpl userService = new UserServiceImpl();
        System.out.println("欢迎进入用户管理系统");
        Scanner sc = new Scanner(System.in);
        while (true) {
            System.out.println("1.查询所有用户");
            System.out.println("2.查询单个用户");
            System.out.println("3.添加用户");
            System.out.println("4.修改用户");
            System.out.println("5.删除用户");
            int i = sc.nextInt();
            switch (i){
                case 1:
                    //通过返回list集合进行每个用户遍历
                    List<User> list = userService.selectAll();
                    for (User user : list) {
                        System.out.println(user);
                    }
                    break;
                case 2:
                    System.out.println("请输入用户ID");
                    int i1 = sc.nextInt();
                    //通过用户输入的id进行数据库查找
                    User user = userService.selectOne(i1);
                    System.out.println(user);
                    break;
                case 3:
                    System.out.println("请输入用户名");
                    String username = sc.next();
                    System.out.println("请输入密码");
                    String password = sc.next();
                    //将用户输入的字段保存到对象中
                    User user1 = new User(username, password);
                    //获得用户添加的对象
                    int i2 = userService.insert(user1);
                    if (i2>0){
                        System.out.println("添加成功");
                    }else {
                        System.out.println("添加失败");
                    }
                    break;
                case 4:
                    //用户输入id
                    System.out.println("请输入ID");
                    int id = sc.nextInt();
                    //将用户输入的id进行在对象里面查找
                    User user2 = userService.selectOne(id);
                    //打印一下原本的查询对象
                    System.out.println("查询到的用户为:"+user2);
                    if (user2!=null){
                        //输入要修改的字段
                        System.out.println("请新的输入用户名");
                        String username2 = sc.next();
                        System.out.println("请新的输入密码");
                        String password2 = sc.next();
                        //将输入的新字段添加到对象中
                        User user3 = new User(id,username2, password2);
                        //user2.setUsername(username2);  //等价于上面的创建新的对象
                        //user2.setPassword(password2);
                        //获得用户修改的对象
                        int i3 = userService.update(user3);
                        if (i3>0){
                        System.out.println(user3);
                        System.out.println("修改成功");

                        }else {
                            System.out.println("修改失败");
                        }
                    }
                    break;
                case 5:
                    System.out.println("请输入用户ID");
                    int id1 = sc.nextInt();
                    //将用户输入id进行对象中查找
                    User user3 = userService.selectOne(id1);
                    if (user3!=null){
                        int delete = userService.delete(id1);
                        System.out.println("删除成功");
                    }else {
                        System.out.println("删除失败");
                    }
                    break;
            }
        }
    }
}