使用jdbc工具类实现对数据库的增删改查的案例

一.创建mysql数据库表mystaff

二、配置包和数据库连接信息

 三、编写程序

1.首先创建一个jdbc工具类JDBCUTILS，实现与mysql数据库连接。

public class JDBCUTILS {
    private static String user;
    private static String password;
    private static String url;
    static {
        InputStream stream1 = 
          JDBCUTILS.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();//该类继承与HashTable
        try {
            properties.load(stream1);
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                stream1.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    //通过此方法获得Connection对象
    public static Connection getConnection(){
        //创建createStatement对象---运输来往数据
        Connection connection = null;
        try {
           connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关闭资源--把资源给我了 才能去关闭资源
    public static void closeAll(Connection con, PreparedStatement statement1, ResultSet ste1){
        if (con != null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement1 != null){
            try {
                statement1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ste1 != null){
            try {
                ste1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
    //重载
    public static void closeAll(Connection con, PreparedStatement statement1){
        if (con != null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement1 != null){
            try {
                statement1.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.创建实现类接口

public class Staff {
    private int id;
    private String name;
    private int age;
    private double salary;

    public Staff() {
    }

    public Staff(int id, String name, int age, double salary) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.salary = salary;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    @Override
    public String toString() {
        return "Staff{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", salary=" + salary +
                '}';
    }
}

3.利用工具类实现登录数据库

public class SongIn {

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String user = scanner.nextLine();
        System.out.println("请输入密码");
        String password = scanner.nextLine();
        boolean b = singIn(user, password);
        System.out.println(b);


    }
    //实现控制台登录到我们的数据库
    public static boolean singIn(String user,String password){
        Connection conn = JDBCUTILS.getConnection();//调用我们jdbc工具类getConnection()方法
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql="select * from mystaff where name= ? and id = ?";//getConnection()方法书写我们的sql语句
        try {
            statement = conn.prepareStatement(sql);//为了防止sql语句的注入，我们使用prepareStatement
            statement.setString(1,user);
            statement.setString(2,password);
            resultSet = statement.executeQuery();//执行sql
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUTILS.closeAll(conn,statement,resultSet);//调用工具类关闭流
        }
        return false;
    }
}

4.实现查询所有数据库表mastaff的信息

public class DemoSelect {
    //查询数据库表的数据
    public static void main(String[] args) {
        Connection connection = JDBCUTILS.getConnection();//调用jdbc工具类连接数据库
        List<Staff> list = new ArrayList<>();//创建ArrayList对象
        String sql="select * from mystaff";
        PreparedStatement statement =null;
        ResultSet res =null;
        try {
            statement = connection.prepareStatement(sql);
            res = statement.executeQuery();
            System.out.println(res);
            while (res.next()){
                Staff staff = new Staff();
                staff.setId(res.getInt("id"));
                staff.setName(res.getString("name"));
                staff.setAge(res.getInt("age"));
                staff.setSalary(res.getDouble("salary"));
                list.add(staff);
            }
            for (Staff staff : list) {//遍历list
                System.out.println(staff);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUTILS.closeAll(connection,statement,res);
        }
    }
}

5.实现控制台添加数据到表mastaff中

public class DemoInsert {
    public static void main(String[] args) {

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入Id");
        int id = scanner.nextInt();
        System.out.println("请输入name");
        String name = scanner.next();
        System.out.println("请输入age");
        int age = scanner.nextInt();
        System.out.println("请输入salary");
        Double salary = scanner.nextDouble();
        Staff staff = new Staff(id,name,age,salary);
        demoInsert(staff);
    }
    //创建方法利用控制台输入添加数据到数据库
    public  static void   demoInsert(Staff staff){
        Connection connection = JDBCUTILS.getConnection();//调用我们jdbc工具类getConnection()方法
        PreparedStatement statement=null;
        String sql = "insert into mystaff(id,name,age,salary) values(?,?,?,?)";
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, staff.getId());
            statement.setString(2,staff.getName() );
            statement.setInt(3, staff.getAge());
            statement.setDouble(4, staff.getSalary());
            int i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUTILS.closeAll(connection,statement);
        }
        System.out.println("添加成功");
    }
}

6.实现控制删除表mastaff中数据

public class DemoDelete {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入id");
        int id = scanner.nextInt();
        delete(id);
    }
    public static int delete(int id){
        Connection connection = JDBCUTILS.getConnection();
        String sql = "delete from mystaff where id= '"+id+"'";
        int i = 0;
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("成功删除数据");
        return i;
    }
}

7.修改

public class DemoUpdate {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入Id");
        int id = scanner.nextInt();
        System.out.println("请输入你要修改的用户名");
        String  name= scanner.next();
        System.out.println("请输入要修改的年龄");
        Integer age = scanner.nextInt();
        System.out.println("请输入你要修改的工资");
        double salary = scanner.nextDouble();
        Staff staff = new Staff(id,name,age,salary);
        update(staff);
        System.out.println();

    }
    public static void update(Staff staff){
        Connection connection = JDBCUTILS.getConnection();
        PreparedStatement statement=null;
        String sql="update mystaff set name=?,age=?,salary= ? where id = ?";
        try {
            statement = connection.prepareStatement(sql);
            statement.setString(1,staff.getName());
            statement.setInt(2,staff.getAge());
            statement.setDouble(3,staff.getSalary());
            statement.setInt(4,staff.getId());
            System.out.println(sql);
            int i = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUTILS.closeAll(connection,statement);
        }
        System.out.println("修改成功");
    }
}