关于jdbc:JavaWeb中jdbc增删查改

jdbc 增删查改

前言：

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

1. 创立 Directory 包 > 取名为 lib

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

3. 创立数据库（不然连贯什么）

   // 根据实体类创立数据库字段和类型 

4. 创立实体类 (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;
    }

}

7. 测试

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("失败");
        }
      

    }
}