JDBC简单使用

• 查

public ArrayList<Student> findAll() {
        ArrayList<Student> all = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "123");

            statement = connection.createStatement();

            String sql = "SELECT * FROM student";

            resultSet = statement.executeQuery(sql);

            while(resultSet.next()){
                Integer id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");

                Student stu = new Student(id,name,age,birthday);
                all.add(stu);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection != null){
                try{
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            if(statement != null){
                try{
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

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

        return all;
    }

• 条件查

public Student findById(Integer sid) {
        Student stu = null;
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "123");

            statement = connection.createStatement();

            String sql = "SELECT * FROM student WHERE id = '"+sid+"'";

            resultSet = statement.executeQuery(sql);

            while(resultSet.next()){
                Integer id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                Integer age = resultSet.getInt("age");
                Date birthday = resultSet.getDate("birthday");

                stu = new Student(id,name,age,birthday);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection != null){
                try{
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            if(statement != null){
                try{
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

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

        return stu;
    }

• 增

public int insert(Student stu) {
        Connection connection = null;
        Statement statement = null;
        int result = 0;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "123");

            statement = connection.createStatement();

            Date birthday = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birth = sdf.format(birthday);

            String sql = "INSERT INTO student VALUES('"+stu.getId()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birth+"')";

            result = statement.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection != null){
                try{
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            if(statement != null){
                try{
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

        return result;
    }

• 改

public int update(Student stu) {
        Connection connection = null;
        Statement statement = null;
        int result = 0;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "123");

            statement = connection.createStatement();

            Date birthday = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birth = sdf.format(birthday);

            String sql = "UPDATE student SET id = '"+stu.getId()+"',name = '"+stu.getName()+"',age = '"+stu.getAge()+"',birthday = '"+birth+"' WHERE id = '"+stu.getId()+"'";

            result = statement.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection != null){
                try{
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            if(statement != null){
                try{
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

        return result;
    }

• 删

public int delete(Integer id) {
        Connection connection = null;
        Statement statement = null;
        int result = 0;

        try{
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db02", "root", "123");

            statement = connection.createStatement();

            String sql = "DELETE FROM student WHERE id = '"+id+"'";

            result = statement.executeUpdate(sql);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(connection != null){
                try{
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }

            if(statement != null){
                try{
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }

        return result;
    }