【JAVA入门】JDBC实现数据库增删改查

 

首先构造一个实体类，构造相关student的属性。

package domain;

public class Student {
    private String name ;
    private int age;
    private String sid;

    public Student() {
    }

    public Student(String name, int age, String sid) {
        this.name = name;
        this.age = age;
        this.sid = sid;
    }

    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 String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", sid='" + sid + '\'' +
                '}';
    }
}

其次实现相关增删改查，

import domain.Student;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class testJDBC {
    //创建一个集合存储数据
    static List<Student> list = new ArrayList();
    //静态代码块实现JDBC连接
    static Connection con;
    static {
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "root");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    public static void main(String[] args) throws ClassCastException, SQLException{
        调用增加数据方法
      // System.out.println(add("wy", 22, "20190584334"));
        调用删除数据方法
       // System.out.println(delete(null, 22, null));
        调用修改数据方法
       // System.out.println(update(new Student(null, 23, null), "20190584334"));
        调用查数据方法
        select(new Student("null",22,"20190584334"));
        输出集合
        System.out.println(list);
    }

    //增
    public static boolean add(String name, int age, String sid) throws SQLException {
        //获取statement对象执行sql语句 
        PreparedStatement pst = con.prepareStatement("insert into students (Name,Age,Sid) values (?,?,?)");
        //添加数据
        pst.setString(1, name);
        pst.setInt(2, age);
        pst.setString(3, sid);
        //封装为student对象添加到集合中
        Student students = new Student(name, age, sid);
        list.add(students);
        //判断是否添加成功
        int i = pst.executeUpdate();
        if (i!=0){
            System.out.println("添加成功");
            return true;
        }else {
            System.out.println("添加失败");
            return false;
        }
    }

    //删
    public static boolean  delete(String name, int age, String sid) throws SQLException {
        int index = 0;
        String sql = "delete from students where";
        if (name != null) {
            sql += " Name=name ";
//            for (int i = 0; i < list.size(); i++) {
//                if (list.get(i).getName() == name) {
//                    index = i;
//                }
//            }
        }
        if (age != -1) {
            sql += " Age=age";
//            for (int i = 0; i < list.size(); i++) {
//                if (list.get(i).getAge() == age) {
//                    index = i;
//                }
//            }
        }
        if (sid != null) {
            sql += " Sid=sid";
//            for (int i = 0; i < list.size(); i++) {
//                if (list.get(i).getSid() == sid) {
//                    index = i;
//                }
//            }
        }

        PreparedStatement pst = con.prepareStatement(sql);
//        pst.executeUpdate();
        int i = pst.executeUpdate();
        if (i!=0){
            System.out.println("删除成功");
            return true;
        }else {
            System.out.println("删除失败");
            return false;
        }
        //list.remove(list.get(index));
    }

    //改
    public static boolean update(Student student, String sid) throws SQLException {
        int index = 0;
        String sql = "update students  set ";
        if (student.getName() != null) {
            sql += " Name="+student.getName()+" where Sid="+sid ;
//            for (int i = 0; i < list.size(); i++) {
//                if (list.get(i).getSid() == sid) {
//                    list.get(i).setName(student.getName());
//                }
//            }
        }
        if (student.getAge() != -1) {
            sql += " Age="+student.getAge()+" where Sid="+sid;
//            for (int i = 0; i < list.size(); i++) {
//                if (list.get(i).getSid() == sid) {
//                    list.get(i).setAge(student.getAge());
//                }
//            }
        }
        if (student.getSid() != null) {
            sql += " Sid="+student.getSid() +"where Sid="+sid;
//            for (int i = 0; i < list.size(); i++) {
//                if (list.get(i).getSid() == sid) {
//                    list.get(i).setSid(student.getSid());
//                }
//            }
        }
PreparedStatement pst = con.prepareStatement(sql);
        //pst.executeUpdate();
        int i = pst.executeUpdate();
        if (i!=0){
            System.out.println("修改成功");
            return true;
        }else {
            System.out.println("修改失败");
            return false;
        }
    }

    //查
    public static void select(Student student) throws SQLException {
        String sql = "select * from students where ";
        if (student.getSid() == null) {
            return;
        }
        if (student.getName()!=null){
           sql+="Name="+student.getName();
        }
        if (student.getAge()!=-1){
            sql+="Age="+student.getAge();
        }
        if (student.getSid()!=null){
            sql+="Sid="+student.getSid();
        }
       // sql = "select * from students where sid = " + student.getSid();
        PreparedStatement psd = con.prepareStatement(sql);
//解析数据集
        ResultSet resultSet = psd.executeQuery();
//循环输出数据集合
        while (resultSet.next()) {
            Student stu = new Student();
            stu.setAge(resultSet.getInt("Age"));
            stu.setName(resultSet.getString("Name"));
            stu.setSid(resultSet.getString("Sid"));
            list.add(stu);
        }
    }


}