jdbc连接数据库进行增删改查操作

1.jdbc的概念
        JDBC（Java DataBase Connectivity：java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它是由一组用Java语言编写的类和接口组成的。
JDBC的作用：可以通过java代码操作数据库

 
2.jdbc的本质
        其实就是java官方提供的一套规范(接口)。用于帮助开发人员快速实现不同关系型数据库的连接

3.jdbc实际意义

        目前大多数公司都在使用ssm框架以及springboot等技术，jdbc作为做古老的技术渐渐消失在代码中，不过所有新的技术最底层都逃不过Java原生技术，掌握好底层更有利于新技术的学习

4.jdbc的快速入门程序

建立jdbc的utils包，命名类BaseCon：

public static Connection getConn(){
   
       Connection connection = null;   
       try {
        //加载驱动类
          Class.forName("com.mysql.cj.jdbc.Driver");
          connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/（数据库名称）","用户名","连接数据库密码");
    } catch (Exception e) {
          e.printStackTrace();
    }

        return connection;
}

创建实体类，这里拿Teacher举例（tid编号，tname姓名，tpwd密码）

public class Teacher {

    private int tid;
    private String tname;
    private int tpwd;

    public Teacher() {
    }

    public Teacher(int tid, String tname, int tpwd) {
        this.tid = tid;
        this.tname = tname;
        this.tpwd = tpwd;
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public int getTpwd() {
        return tpwd;
    }

    public void setTpwd(int tpwd) {
        this.tpwd = tpwd;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "tid=" + tid +
                ", tname='" + tname + '\'' +
                ", tpwd=" + tpwd +
                '}';
    }
}

（也可以直接导入依赖lombok，直接自动生成构造方法、getter/setter、tostring）

dao层里进行数据的增删改查

增加：

public int add(Teacher teacher){

    int a = 0;

    //创建连接
    Connection conn = BaseConn.getConn();
    PreparedStatement ps = null;
    String sql = "insert into t_teacher values(0,?,?)";

       try {
    //给占位符赋值
         ps = conn.prepareStatement(sql);
         ps.setString(1, teacher.getTname());
         ps.setInt(2, teacher.getTpwd());


         a = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return a;
}

删除：

public int remove(int tid){

    int a = 0;

    //创建连接
    Connection conn = BaseConn.getConn();
    PreparedStatement ps = null;
    String sql = "delete from t_teacher where tid=?";

       try {
          //给占位符赋值
          ps = conn.prepareStatement(sql);
          ps.setInt(1,tid);
          ps.executeUpdate();
       } catch (SQLException e) {
          throw new RuntimeException(e);
       }
    return a;
}

修改：

第一步：需要根据tid查询到用户的所有信息呈现在页面上

根据tid查询信息：

public Teacher showById(int tid){

    Teacher teacher = null;

    //创建连接
    Connection conn = BaseConn.getConn();
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "select * from t_teacher where tid=?";

      try {
        //给占位符赋值
          ps = conn.prepareStatement(sql);
          ps.setInt(1, tid);
          rs = ps.executeQuery();
          while (rs.next()){
              teacher = new Teacher();
              teacher.setTid(rs.getInt("tid"));
              teacher.setTname(rs.getString("tname"));
              teacher.setTpwd(rs.getInt("tpwd"));
          }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return teacher;
}

第二步：跳转到修改页面进行修改

修改：

public int set(Teacher teacher){

int a = 0;

        //创建连接
        Connection conn = BaseConn.getConn();
        PreparedStatement ps = null;
        String sql = "update t_teacher set tname=?, tpwd=? where tid=?";

       try {
        //给占位符赋值
          ps = conn.prepareStatement(sql);
          ps.setString(1, teacher.getTname());
          ps.setInt(2, teacher.getTpwd());
          ps.setInt(3, teacher.getTid());
          a = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return a;
}

查询所有信息：

public List<Teacher> show(){

    List<Teacher> list = new ArrayList<Teacher>();
    //创建连接
    Connection conn = BaseConn.getConn();
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "select * from t_teacher";
       try {
           ps = conn.prepareStatement(sql);
           rs = ps.executeQuery();
           while (rs.next()){
              Teacher teacher = new Teacher();
              teacher.setTid(rs.getInt("tid"));
              teacher.setTname(rs.getString("tname"));
              teacher.setTpwd(rs.getInt("tpwd"));
              list.add(teacher);
          }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
}

以上是jdbc的增删改查，下面是测试方法：

public static void main(String[] args) {

        TeacherDao teacherDao = new TeacherDao();

       //添加
       Teacher teacher = new Teacher(1, "张三", 123);
       teacherDao.add(teacher);

       //查询
       List<Teacher> list = teacherDao.show();
       for (Teacher teacher : list) {
          System.out.println(teacher);        //循环遍历打印
       }


       //修改
       Teacher teacher = teacherDao.showById(4);
       System.out.println(teacher);
       teacher.setTpwd(7890);
       teacherDao.set(teacher);
       List<Teacher> list = teacherDao.show();
       for (Teacher teacher1 : list) {
          System.out.println(teacher1);        //循环遍历打印
        }

       //删除
       teacherDao.remove(9);
       List<Teacher> list = teacherDao.show();

  

}