JavaWeb中jdbc增删查改

jdbc增删查改

Java数据库连接（Java Database Connectivity,J 简称JDBC）,简单来说就是 使用Java执行sql语句的，面向关系型数据库的java_API

1. 创建 Directory包 > 取名为 lib

1. 后导入 MySQL-connector-java-5.1.37 文件

2. 创建数据库（不然连接什么）

   // 依据实体类创建数据库字段和类型 

1. 创建实体类(BankModel.java)

package com.kjzz;

public class  Teacher{
       // 成员变量 
    private int SID;  // id
    private String SName; // name
    private String birthday; // birthday 生日

    
   // 无参构造 / 有参构造 
   public Teacher(){
       
   } 
    
   public Teacher(int SID, String SName,String birthday) {
        this.SID = SID;
        this.SName = SName;
        this.birthday = birthday;
    }
    
    // get / set 方法
    public int getSID() {
        return SID;
    }

    public void setSID(int SID) {
        this.SID = SID;
    }

    public String getSName() {
        return SName;
    }

    public void setSName(String SName) {
        this.SName = SName;
    }


    public String getBirthday(){
        return birthday;
    }

    public void setBirthday(String birthday){
        this.birthday = birthday;
    }

}

5.创建数据库驱动类（DBUtill.java）

public class DBConn{
    // 驱动 , 路径 , 用户名 密码
    // DRIVER--URL---USER-PWD
    
    public static String DRIVER = "com.mysql.jdbc.Driver";
    // jdbc:数据库名:// localhost:端口号/连接的数据库 
    public static String URL = "jdbc:mysql://localhost:3306/test";
    public static String USER = "root";
    public static String PWD = "root";
    
     // 获取链接,为了处理找不到这个类，所以要异常处理
    static{
        try{
            Class.forName(DRIVER); // 通过这个驱动名去找这个类
        } catch(ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    // 连接 
    public static Connection getConnection(){ // 要导入sql的包 不要带jdbc的
        
        Connection conn = null;
        
        try{
            // 获取链接用的
            conn = DriverManager.getConnection(URL,USER,PWD)
            // 路劲 用户名 密码
        } catch(SQLException e){
            e.printStackTrace();
        }
     
    }
    
    /** 关闭连接
    当数据库使用后必须关闭,如果没有关闭数据库,
    数据库接口有限，下次不能连接
    */
    public static void CloseConn(Conncetion conn,PerpredStatement prep,ResultSet rs){
    
        try{
            if(rs != null){ // 不等于空 说明在使用
                rs.close();
            }
            if(perp != null){
                prep.close();
            }
            if(coon !=  null){
                coon.close();
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        
}
    
    
    
    // 测试 
     public static void main(String[] args) {
      Connection conn =  getConnectionV();
        System.out.println("ConnDB测试" + conn);
    }
   // 输出结果 ConnDB测试 com.mysql.jdbc.JDBC4Connection@289d1c02
    
}

6.创建数据库操作类

public class ZSGC {

    public static Connection conn = null; // 连接对象
    public static PreparedStatement prep = null; // 处理语句
    public static ResultSet rs = null; // 查询后返回的结果集
    
    /*    查询方法 实现思路
    1. 加载数据库驱动(已经写好了 连接)
    2. 获取数据库连接
    3. 通过Conncetion 实列获取 Statement 对象
    4. 通过 Statement 实列执行 SQL 语句 
    5. 处理 ResultSet 结果集
    6. 回收数据库资源 

    */
    public List<Teacher> ChaXun(){
        List<Teacher> list = new ArrayList<>();
        String SelectSql = "select * from teacher"; // 表

        try {
            conn = Conn.getConnection(); // 1. 连接
            prep = conn.prepareStatement(SelectSql); // 2. 处理sql
            rs = prep.executeQuery(); // 3. 查询返回的结果集
            
            while(rs.next()){ //rs 有内容,不为空
                Teacher t = new Teacher();
                int ID = rs.getInt("sid");
                String NAME = rs.getString("sname");
                String BIRTHDAY = rs.getString("birthday");
                t  = new  Teacher(ID,NAME,BIRTHDAY);
                list.add(t); list.add(stu); // 都要加进list集合
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            Conn.CloseConn(conn,prep,rs);
        }
        return list;
    }

    // 添加
    public static boolean Insert(Teacher teacher){
        int num = 0;
        String InsertSql = "INSERT INTO teacher values(?,?,?)";
        try {
            conn = Conn.getConnection();
            prep = conn.prepareStatement(InsertSql);
            prep.setInt(1,teacher.getSID());
            prep.setString(2,teacher.getSName());
            prep.setString(3,teacher.getBirthday());
            num = prep.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Conn.CloseConn(conn,prep,rs);
        }
        return num > 0;
    }

    // 删除
    public static boolean Delete(int key){
        int num = 0;
        String DeleteSql = "Delete from teacher where id = ?";

        try {
            conn = Conn.getConnection();
            // 执行sql语句
            prep = conn.prepareStatement(DeleteSql);
            prep.setInt(1,key);
            num = prep.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            Conn.CloseConn(conn,prep,rs); 
        }
        return num > 0;
    }
    // 修改
    public static boolean Update(Teacher teacher){
        int num = 0; // 定义影响的行数
        String UpdateSql = "update teacher set sid = ?,sname = ? where sid = ? ";

        try {
            conn = Conn.getConnection();
            prep = conn.prepareStatement(UpdateSql);
            prep.setInt(1,teacher.getSID());
            prep.setString(2,teacher.getSName());
            prep.setInt(3,teacher.getSID());
            num = prep.executeUpdate(); // 增删改都是这个,然会INT类型
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Conn.CloseConn(conn, prep, rs);
        }
        return num > 0;
    }

}

public class Text1 {
    public static void main(String[] args) {
    
        Conn conn = new Conn();
        System.out.println("conn = " + conn);

        CRUD crud = new CRUD();
        List<Teacher> list = crud.ChaXun();
        for (Teacher t : list) {
            System.out.println(t.getSID());
            System.out.println(t.getSName());
            System.out.println(t.getBirthday());
        }
        // 添加
        Teacher t1 = new Teacher(3,"白居易","1001-11-11");
        boolean insert = CRUD.Insert(t1);
        if (insert == true){
            System.out.println("添加成功");
        } else{
            System.out.println("添加失败");
        }
        // 修改
        boolean delete = CRUD.Delete(2);
        if (delete == true){
            System.out.println("删除成功");
        } else {
            System.out.println("报错咯");
        }
        // 修改
        Teacher t2 = new Teacher();
        t2.setSID(1);
        t2.setSName("张三");
        t2.setSID(1);

        boolean update = CRUD.Update(t2);
        if (update == true){
            System.out.println("1号名字已被修改为张三");
        } else {
            System.out.println("失败");
        }
      

    }
}