idea实现对数据库的增删改查功能

前言

java连接数据库，对数据库进行增删改查的功能

# 一、idea的一些快捷键提示
 /*
    * 如果对某一行代码进行注释ctrl+?
    * 如果希望在某个位置生成注释，使用ctrl+shift+?
    *
    * java数组长度固定，js长度不固定
    *
    *alt+ins得到getter和setter
    * alt+回车 处理异常的方法
    * sout输出快捷键
    * pswm快速建立主函数
    * */

二、增删改查

1.查询数据

根据自己的数据库和自己的需求来判定需要遍历多少数据

 try {
            /*1.加载驱动包*/
            Class.forName("com.mysql.jdbc.Driver");
            /*2.创建数据库连接*/
            try {
                Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/movie-manager?useUnicode=true&characterEncoding=utf8","root","root");
                /*3.创建sql语句*/
                String sql="select * from mm_userinfo";
                /*4.获取执行器*/
                PreparedStatement pstm=conn.prepareStatement(sql);
                /*5.执行sql并接收结果*/
                ResultSet rs=pstm.executeQuery();/*executeQuery是查询专用方法*/
                /*6.处理结果*/
                while(rs.next()){
                    System.out.println("编号:"+rs.getInt("id"));
                    System.out.println("用户名:"+rs.getString("username"));
                    System.out.println("昵称:"+rs.getObject("nickname"));
                    System.out.println("创建时间:"+rs.getDate("create_time"));
                    System.out.println("===================================");
                }
                /*7.关闭资源*/
                /*一定要按顺序，顺序不能乱,类似于析构函数*/
                rs.close();
                pstm.close();
                conn.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

2.添加数据

/*初始化用户输入工具类:*/
        Scanner scanner=new Scanner(System.in);
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/movie-manager?useUnicode=true&characterEncoding=utf8","root","root");
            String sql="insert into mm_userinfo values(null,?,?,?,?,1,?,?,null,null)";
            System.out.println("请输入用户名:");
            String username=scanner.next();
            System.out.println("请输入密码:");
            String password=scanner.next();
            System.out.println("请输入昵称:");
            String nickname=scanner.next();
            System.out.println("请输入头像:");
            String avatar=scanner.next();
            Integer createAdmin=1;
            Date createDate=new Date();
                 PreparedStatement pstm=conn.prepareStatement(sql);
            pstm.setObject(1,username);
            pstm.setObject(2,password);
            pstm.setObject(3,nickname);
            pstm.setObject(4,avatar);
            pstm.setObject(5,createAdmin);
            pstm.setObject(6,createDate);
            int result=pstm.executeUpdate();
            if(result>0){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

3.修改数据

 Scanner scanner=new Scanner(System.in);
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/movie-manager?useUnicode=true&characterEncoding=utf8","root","root");
            String sql="update mm_userinfo set nickname=?,avatar=?,update_admin=?,update_time=? where id=?";
            PreparedStatement pstm=conn.prepareStatement(sql);
            System.out.println("请输入要修改的编号:");
            Integer id=scanner.nextInt();
            System.out.println("请输入要修改的昵称");
            String nickname=scanner.next();
            System.out.println("请输入要修改的头像");
            String avatar=scanner.next();
            Integer updateAdmin=1;
            Date updateTime=new Date();
            pstm.setObject(1,nickname);
            pstm.setObject(2,avatar);
            pstm.setObject(3,updateAdmin);
            pstm.setObject(4,updateTime);
            pstm.setObject(5,id);
            int result= pstm.executeUpdate();
            if(result>0){
                System.out.println("修改成功");
                System.out.println("当前最新数据如下:");
                String sql2="select * from mm_userinfo";
                PreparedStatement pstm2=conn.prepareStatement(sql2);
               ResultSet rs= pstm2.executeQuery();
               while(rs.next()){
                   System.out.println("编号:"+rs.getObject("id"));
                   System.out.println("昵称:"+rs.getObject("nickname"));
                   System.out.println("头像:"+rs.getObject("avatar"));
               }
               rs.close();
               pstm2.close();
            }else{
                System.out.println("修改失败");
            }
            pstm.close();
            conn.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

4. 删除数据

Scanner scanner=new Scanner(System.in);
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/movie-manager?useUnicode=true&characterEncoding=utf8","root","root");
            String sql="delete from mm_userinfo where id=?";
            PreparedStatement pstm=conn.prepareStatement(sql);


            System.out.println("请输入要删除的id");
            Integer id= scanner.nextInt();
            pstm.setObject(1,id);
            int result= pstm.executeUpdate();
            if(result>0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
              pstm.close();
            conn.close();

            } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

总结

只是将增删改查每个功能的代码进行了展示。有的波浪线的地方是因为变量命名的关系，可以自己命名。
不难发现，增删改查的四个功能代码中有很多冗余重复的代码，我们可以进行优化，让代码更简洁。