jdbc实现对数据的增删改查练习

数据表（Student表）

 主函数测试部分

    public static void main(String[] args) throws ParseException {
        //数据插入
//        Student student = new Student("s2000","jdbc","123456","男","110","西安",new Date(),"jdbc@126.com",1);
//        saveStu(student);
        //数据删除
//        deleteStu("4");
        //查询所有数据
//        List<Student> all = getAll();
//        for (Student student : all){
//            System.out.println(student);
//        }
        //输入指定studentno查找学生信息
        System.out.println(findOne("s2000"));
    }

主体部分：

1：辅助方法（根据输入的stuentno查找记录返回找到的记录个数）

 //（辅助方法）通过id查询是否存在该学生（若返回1，存在，否则不存在）
    private  static  int getResultCountByStuid(String id){
        int count = -1;
        JDBCUtils jdbcUtils = new JDBCUtils();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = jdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement("select count(1) from student where StudentNo = ?");
            preparedStatement.setString(1,id);
           resultSet =  preparedStatement.executeQuery();
           while (resultSet.next()){
               count = resultSet.getInt(1);
           }
        }catch (Exception e){
            e.printStackTrace();
        }
        return count;
    }

2：数据插入

代码段：

 //数据插入
    public  static  void saveStu(Student student){
        String sql="insert into student values(?,?,?,?,?,?,?,?,?)";
        JDBCUtils jdbcUtils = new JDBCUtils();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
           connection =   jdbcUtils.getConnection();
           preparedStatement =  connection.prepareStatement(sql);
           preparedStatement.setString(1,student.getStudentno());
           preparedStatement.setString(2,student.getStudentname());
           preparedStatement.setString(3,student.getLoginpassword());
           preparedStatement.setString(4,student.getSex());
            preparedStatement.setInt(5,student.getGradeid());
           preparedStatement.setString(6,student.getPhone());
           preparedStatement.setString(7,student.getAddress());
           preparedStatement.setDate(8,new java.sql.Date(student.getBorn().getTime()));
           preparedStatement.setString(9,student.getEmail());

           int rows = preparedStatement.executeUpdate();
            System.out.println(rows);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(connection,preparedStatement,null);
        }
    }

测试效果：

输出数字为1，表示影响一行结果。

 这里看到表中成功添加学号为s2000的记录。

3：数据删除

代码段：

//数据删除
    public  static  void deleteStu(String id){
        JDBCUtils jdbcUtils = new JDBCUtils();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if(getResultCountByStuid(id) == 1) {
                connection = jdbcUtils.getConnection();
                preparedStatement = connection.prepareStatement("delete from student where StudentNo = ?");
                preparedStatement.setString(1,id);
                int rows = preparedStatement.executeUpdate();
                if(rows == 1){
                    System.out.println("删除成功！");
                }else {
                    System.out.println("删除失败！");
                }
            }else {
                System.out.println("查无此人！！！！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }

测试效果：

表中没有studentno为1的，所以显示查无此人

 改变方法参数id为4，表中有studentno为4的，显示删除成功； 

数据表中studentno为4的已经删除成功。 

 4:查找所有学生信息

代码段：

//获取所有学生信息
    public static List<Student>  getAll(){
        JDBCUtils jdbcUtils = new JDBCUtils();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> list = new ArrayList<>();
        try {
            connection = jdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement( "select * from student ");
            resultSet =  preparedStatement.executeQuery();
            while (resultSet.next()){
               String sno = resultSet.getString("StudentNo");
               String sadd = resultSet.getString("Address");
               String spwd = resultSet.getString("LoginPwd");
               String sname = resultSet.getString("StudentName");
               String ssex = resultSet.getString("Sex");
               String sphone = resultSet.getString("Phone");
               int id = resultSet.getInt("GradeId");
               Date sdata = resultSet.getDate("BornDate");
               String semail = resultSet.getString("Email");
                Student student = new Student(sno,sname,spwd,ssex,sphone,sadd,sdata,semail,id);
               list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(connection,preparedStatement,null);
        }
        return list;
    }

测试结果：

输出表中所有学生信息

4：根据指定studentno查找该学生信息

代码段：

 //根据指定id查找学生信息
    public  static  Student findOne(String id){
     Student student = null;
        JDBCUtils jdbcUtils = new JDBCUtils();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = jdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement( "select * from student where studentno=?");
            preparedStatement.setString(1,id);
            resultSet =  preparedStatement.executeQuery();
            while (resultSet.next()){
                String sno = resultSet.getString("StudentNo");
                String sadd = resultSet.getString("Address");
                String spwd = resultSet.getString("LoginPwd");
                String sname = resultSet.getString("StudentName");
                String ssex = resultSet.getString("Sex");
                String sphone = resultSet.getString("Phone");
                int Gid = resultSet.getInt("GradeId");
                Date sdata = resultSet.getDate("BornDate");
                String semail = resultSet.getString("Email");
                student = new Student(sno,sname,spwd,ssex,sphone,sadd,sdata,semail,Gid);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(connection,preparedStatement,null);
        }
      return student;
    }

 测试结果：

输出指定学号学生信息