JavaSElect_day24JDBCJava数据库连接

JDBCJava数据库连接

1.基本概念

java 数据库连接, 是java提供的一套api接口，以一种一致的方式，访问不同的数据库(mysql,oracle,sqlserver)

2. 数据库连接完成只需要5步走

1.加载驱动（在新版本的jdbc中可以省略此步骤）
Class.forName("com.mysql.jdbc.Driver);//这个是MySQL5.1版本的驱动
Class.forName("com.mysql.cj.jdbc.Driver");//这个是最新版本的驱动MySQL8.8的（一般MySQL比较新的用第二个）。
2.创建连接
String url="jdbc:mysql://localhost:3306/pand?serverTimezone=GMT%2B8&useSSL=false";
String username="root";
String password="panda"; //这是自己数据库的密码，不要弄错。
DriverManager.getConnecton(url,username,passsword);
【注意】 jdbc：mysql：这是连接协议
localhost:mysql的服务器的ip地址（这个是本机的ip）
3306：连接端口号
panda：连接数据库的名称
3.创建Statement对象
Statement state=connection.createStatement();
4.执行sql语句（增删改查）
增：insert into student（sname,birthday,sex)values('陈小帅','1997-07-19','男');
由于sid那一列是自增的，所以可以不用设置。
删：delete from student where sid=1001;
改：update student set sname='老王' where sid= 1001;
查：select * from student；
具体代码如下：

    java    45行

public class TestJDBC1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1) 加载驱动 （在新版的jdbc中可以省略此步骤）只需要执行一次即可
//        Class.forName("com.mysql.jdbc.Driver");// mysql 5.1
        Class.forName("com.mysql.cj.jdbc.Driver"); // mysql 8.8
//        2) 创建连接，创建Connection对象
        // jdbc:mysql: 称为连接协议
        // localhost： mysql服务器的ip地址
        // 3306： 连接端口号
        // test3: 数据库名称
        // serverTimezone=GMT%2B8 设置连接时区与数据库服务器一致 (连接8.0 mysql时新增 )
        String url = "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false"; // 数据库的连接字符串
        String username="root"; // 数据库用户名
        String password="root"; // 数据库的密码
        Connection conn = DriverManager.getConnection(url, username, password);
//        3) 创建Statement 对象
        Statement stmt = conn.createStatement();
//        4) 执行sql语句（执行增删改或查询）
//        stmt.executeUpdate(sql) // 用来执行 insert, update, delete
//        stmt.executeQuery(sql)  // 用来执行 select
//        stmt.execute(sql)
        // executeUpdate方法返回的整数，代表增删改影响的记录行数
//        int i = stmt.executeUpdate(
                             "insert into dept(deptno,dname,loc) 
                                   values(60, '教学部', '西安'),(70, '市场部', '西安')");
//        System.out.println("影响行数是：" +i);
        // 结果集对象
        ResultSet rs = stmt.executeQuery("select deptno,dname,loc from dept");
        // rs.next() 返回值是一个boolean 表示是否有下一条记录
        while(rs.next()) {
//            int deptno = rs.getInt(1);// getXXX方法的参数，代表第几列,从1开始
//            String dname = rs.getString(2);
//            String loc = rs.getString(3);
            int deptno = rs.getInt("deptno");
            String dname = rs.getString("dname");
            String loc = rs.getString("loc");
            System.out.println(deptno + "\t" + dname + "\t" + loc);
        }
//        5) 关闭释放资源, 先打开的资源最后关闭
        rs.close();
        stmt.close();
        conn.close();
    }
}

3. execute 和executeQuery的区别

上边的代码在查询的过程中使用结果集
ResultSet rs = stmt.executeQuery("select deptno,dname,loc from dept");
那么现在用代码来展示下 使用execute和使用execute.Queryde区别：

    java    27行

public class TestJDBC2 {
    static final String URL= "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false";
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(URL, "root", "root");
        Statement stmt = conn.createStatement();
        // 如果执行了增删改，返回false， 如果执行的是查询，那么返回true, Statement.RETURN_GENERATED_KEYS代表返回自增的id值
        boolean r = stmt.execute("insert into student(sname,birthday,sex) VALUES ('宋三','2001-9-10','女')",
                Statement.RETURN_GENERATED_KEYS);
//        boolean r = stmt.execute("select sid,sname,birthday,sex from student");
//        if(r) {
//            ResultSet rs = stmt.getResultSet();
//            while(rs.next()) {
//                System.out.println( rs.getInt("sid") + " " + rs.getString("sname"));
//            }
//            rs.close();
//        }
        // 获取自增列的值
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        int sid = rs.getInt(1);
        System.out.println(sid);
        stmt.close();
        conn.close();
    }
}

使用executede时返回的结果，当做一个条件去判断，如果是增删改，那么就会返回一个false，将不会进入if条件语句，如果是查询，则返回true，进入if条件语句，将获取查询到的每一个属性。

4.createStatement和prepareStatement的区别

接下来的问题是注入攻击问题，本来用户只有通过正确的用户名和密码才能进去你的账号，但是由于字符串的拼接导致不需要正确的密码就能进入账户，下了就一起来看一看。
使用createStatement：

    java    37行

// sql 注入攻击问题
public class TestJDBC3 {
    static final String URL = "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false";
    static final String USERNAME = "root";
    static final String PASSWORD = "root";
    // 用户登录操作(检查用户名密码是否正确)
    /*
     create table user(
        username varchar(20) primary key,
        password varchar(20) not null
     );
     insert into user(username,password) values('zhangsan', '123');
     */
    // 用拼接字符串的方法生成sql会导致安全漏洞，SQL注入攻击漏洞
    public static boolean login(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        Statement stmt = conn.createStatement();
        // 拼接字符串的办法生成了sql语句
        String sql = "select * from user where username='" +username+"' and password='"+password+"'";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);
        // 看他是否有下一条记录
        boolean r = rs.next();
        rs.close();
        stmt.close();
        conn.close();
        // 如果有下一个记录，表示找到了，用户名密码都正确
        return r;
    }
     public static void main(String[] args) throws SQLException {
        String username = "lisi";
        String password = " 123";
        boolean login = login2(username, password);
        System.out.println(login);
    }
}

如果按上述代码执行的话，最后的用户名是不正确的，所以登陆会失败，如果将密码改成“ ‘or’ 1=1”，那么返回的结果就是true，也就是说会登陆成功：代码如下

    java    49行

 public static void main(String[] args) throws SQLException {
        String username = "lisi";
        String password = "' or '1=1";
        boolean login = login2(username, password);
        System.out.println(login);
    }
    ```
    因为上边的username和password是字符串拼接的，所以给的”‘or’ 1=1“，系统不会判断到底是or前边的对还是or后边的对，or后边1=1永远成立的。
    那么现在来看一下使用prepareStatement：
    ```java
    // sql 注入攻击问题
public class TestJDBC3 {
    static final String URL = "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false";
    static final String USERNAME = "root";
    static final String PASSWORD = "root";
    // 用户登录操作(检查用户名密码是否正确)
    /*
     create table user(
        username varchar(20) primary key,
        password varchar(20) not null
     );
     insert into user(username,password) values('zhangsan', '123');
     */
    // PreparedStatement 预编译的Statement ，可以避免SQL注入攻击漏洞
    // 让代码的可读性更好
    public static boolean login2(String username, String password) throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        // sql语句是预先准备好的, 用？作为值的占位符

        String sql  = "select * from USER where username=? and password=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        // 给sql语句中的? 赋值
        stmt.setString(1, username); // 将username变量赋值给sql语句中第一个?， ?下标从1开始
        stmt.setString(2, password); // 不会把其中的关键字，如or进行解析了，把它当做普通值
        ResultSet rs = stmt.executeQuery();
        boolean r = rs.next();
        rs.close();
        stmt.close();
        conn.close();
        return r;
    }
    public static void main(String[] args) throws SQLException {
        String username = "lisi";
        String password = "' or '1=1";
        boolean login = login2(username, password);
        System.out.println(login);
    }
}

此时就算在使用“‘or’ 1=1”也不会验证成功，是因为prepareStatement方法具有原子性，将其看做一个整体。
既然Java中使用的是面向对象的思想，那么就可以将关闭资源和连接抽取为一个方法，在使用的时候直接调用就可以了。也体现了Java的思想

    java    38行

public class Utils {
    static final String URL = "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT%2B8&useSSL=false";
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

    public static Connection getConnection() throws SQLException {
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        return conn;
    }

    public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(PreparedStatement stmt, Connection conn) {
        close(null, stmt, conn);
    }
}

以上便是抽取的方法，Utils是工具类，那么接下来在使用增删改查的过程中就可

    java    32行

public class TestJDBC4 {
//适用于查的方法
    public static void aaa(String[] args) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            stmt = conn.prepareStatement("select * from Student");
            rs = stmt.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(rs, stmt, conn);
        }
    }
//以下代码针对使用增删改的方法
    public static void bbb(String[] args) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            stmt = conn.prepareStatement("insert * from Student");
            stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }
    }
}

5.Data Access Object 数据访问对象 （包含CRUD方法）

先创建一个学生类，这个学生类对象数据库的学生表
student类<---------------->student表
sid sid int primary key auto_increment
sname sname String not null
birthday birthday String not null
sex sex String

    java    50行

public class Student {

    private int sid; // 学生编号
    private String sname; // 学生姓名
    private Date birthday; // 生日
    private String sex; // 性别

    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 Date getBirthday() {
        return birthday;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                '}';
    }
}

    java    145行

public class StudentDAO {

    // 新增方法
    public void insert(Student stu){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            String sql = "insert into student(sname,birthday,sex)values(?,?,?)";
            System.out.println(sql);
            stmt = conn.prepareStatement(sql);
            // 用 set方法给sql语句中的？占位符赋值
            stmt.setString(1, stu.getSname());
            Date birthday = stu.getBirthday();
            stmt.setDate(2, new java.sql.Date(birthday.getTime()));
            stmt.setString(3, stu.getSex());
            stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            Utils.close(stmt,conn);
        }
    }
    // 修改方法
    public void update(Student stu){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            String sql = "update student set sname=?, birthday=?, sex=? where sid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, stu.getSname());
            stmt.setDate(2, new java.sql.Date(stu.getBirthday().getTime()));
            stmt.setString(3, stu.getSex());
            stmt.setInt(4, stu.getSid());
            stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }
    }
    // 删除方法
    public void delete(int sid) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = Utils.getConnection();
            String sql = "delete from student where sid=?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, sid);
            stmt.executeUpdate();
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            Utils.close(stmt, conn);
        }
    }
    // 按id查询
    public Student findById(int sid) { // sid 1012
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            String sql = "select * from student where sid = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, sid);
            rs = stmt.executeQuery();
            // 因为sid是唯一的，因此要么查到一条记录，要么没有
            if(rs.next()) { // 查到了
                // 这些数据保存起来, 将各个列的值存入学生对象的属性中
                Student s = new Student();
                s.setSid( rs.getInt("sid") );
                s.setSname( rs.getString("sname"));
                s.setBirthday(rs.getDate("birthday"));
                s.setSex(rs.getString("sex"));
                return s;
            } else { // 没查到
                return null;
            }
        } catch(Exception e) {
            throw new RuntimeException(e);
        } finally {
            Utils.close(rs, stmt, conn);
        }
    }

    // 查询所有
    public List<Student> findAll() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            String sql = "select * from student";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            List<Student> list = new ArrayList<>();
            while(rs.next()) {
                Student s = new Student();
                s.setSid(rs.getInt("sid"));
                s.setSname(rs.getString("sname"));
                s.setBirthday(rs.getDate("birthday"));
                s.setSex(rs.getString("sex"));
                list.add(s);
            }
            return list;
        } catch(Exception e) {
            throw new RuntimeException(e);
        } finally {
            Utils.close(rs, stmt, conn);
        }
    }




    public static void main(String[] args) {
        StudentDAO dao = new StudentDAO();

        // 新增
        /*Student s = new Student();
        s.setSname("老三");
        s.setBirthday(new Date());
        s.setSex("男");
        dao.insert(s);*/
        // 查询所有
        /*List<Student> list = dao.findAll();
        for(Student s:list) {
            System.out.println(s);
        }*/
        // 按id查询
        /*Student stu = dao.findById(1006);
        System.out.println(stu);

        // 修改
        stu.setSex("女");
        dao.update(stu);*/

        // 删除
        dao.delete(1011);
    }
}

要求SQL语句中的值使用?占位符来占位，然后通过一系列的setXXX
方法来给?赋值，XXX根据值的类型决定，例如?为字符串调用setString,?为整数调用setInt
setXXX(？下标, 值） 下标也是从1开始

6.编码规范

像连接字符串，数据库用户名密码都可以定义为静态常量，静态常量名大写，如果有多个单词中间用下划线分隔