与数据库连接:JDBC

JDBC概述

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

有了JDBC,java开发人员只需要编写一次程序,就可以访问不同的数据库.

JDBC搭建

jdbc搭建步骤
1.导入数据库开发商提供的数据库连接实现类的jar文件
2.加载驱动
3.建立与数据库的连接
4.向数据库发送信息
5.执行完毕后,关闭与数据库的连接

package javajdbc;

import java.sql.*;
import java.util.Properties;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo1 {

    public static void main(String[] args) {
        //java反射机制,动态加载类
       /* Class.forName("com.mysql.cj.jdbc.Driver");
        DriverManager.registerDriver(new Driver());*/
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
        try {
            //建立与数据库的连接,返回connection对象
            Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
            //statement用于向数据库发送sql语句
            Statement st = connection.createStatement();
            st.executeUpdate("insert into grade (name) value('四年级')");

            st.close();
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

 public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
         Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
        try {
            //获取statement
            Statement st = connection.createStatement();
            //executeUpdate发送sql到数据库,返回所操作的行数
            //int res = st.executeUpdate("create table test(id int)");//可执行ddl,dml
            //int res = st.executeUpdate("insert into test(id) value (1)");//可执行ddl,dml
            int res = st.executeUpdate("delete from test where id");//可执行ddl,dml
            System.out.println(res);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

PreparedStatement和Statement

1.代码的可读性和可维护性.

Statement：
1. 静态SQL执行，没操作一次向数据库编译发送一次，效率低
2.将变量以字符串形式拼接进去，书写麻烦
3.不能防止sql注入，安全性低

PreparedStatement：
1.预先将sql语句编译到PreparedStatement对象中可以重复使用，效率高
2.使用set方法向占位符处进行设置值，书写方便
3.可以防止SQL注入，安全性高

statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo3 {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
        Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
        try {
            //获取statement
            Statement st = connection.createStatement();

            String name = "jim";
            String sex = "女";
            String birthday = "1999-05-23";
            int grade = 4;
            int score = 90;
            String phone = "15333333333";
            String adress = "西安";

            st.executeUpdate("insert into student(name,sex,birthday,grade,score,phone,address,reg_time) "
                            +"values('"+name+"','"+sex+"','"+birthday+"',"+grade+","+score+",'"+phone+"','"+adress+"',new DATE ())");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

PreparedStatement

import java.sql.*;
import java.util.Date;

public class Demo4 {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
        Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
        try {
            //获取statement
            Statement st = connection.createStatement();

            String name = "tom";
            String sex = "女";
            String birthday = "1999-05-23";
            int grade = 4;
            int score = 90;
            String phone = "15333333333";
            String adress = "西安";

            String sql = "insert into student(name,sex,birthday,grade,score,phone,address,reg_time) "
                    + "values(?,?,?,?,?,?,?,?)";//?表示占位符，表示一个值的位置
            //预先将sql语句编译到PreparedStatement对象中
            PreparedStatement ps = connection.prepareStatement(sql);

            //向sql中占位符进行赋值操作
            ps.setString(1, name);
            ps.setString(2, sex);
            ps.setString(3, birthday);
            ps.setInt(4, grade);
            ps.setInt(5, score);
            ps.setString(6, phone);
            ps.setString(7, adress);
            ps.setObject(8, new Date());
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

2、最重要的一点是极大地提高了安全性. 防止sql注入

statement

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
       Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
        try {
            //获取statement
            Statement st = connection.createStatement();

            String name = "'陕西省' or 1=1";//暴力的sql注入，直接将值拼接到字符串中的，没有做任何处理，不能防止sql注入
            int res = st.executeUpdate("delete from t_area where name = " + name);
            System.out.println(res);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

PreparedStatement

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
        Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");
        try {
            //获取statement
            Statement st = connection.createStatement();

            String name = "'陕西省' or 1=1";//暴力的sql注入，直接将值拼接到字符串中的，没有做任何处理，不能防止sql注入
            PreparedStatement ps = connection.prepareStatement("delete from t_area where name = ?");
            ps.setString(1, name);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

结果集处理

这里是引用PreparedStatement和Statement中的executeQuery()方法中会返回一个ResultSet对象,查询结果就封装在此对象中.
使用ResultSet中的next()方法获得下一行数据
使用getXXX(String name)方法获得值

创建Student类

package com.ff.javajdbc;

import java.util.Date;

public class Student {

    private int num;
    private String name;
    private String sex;
    private Date birthday;
    private int grade;
    private String phone;
    private String address;
    private Date reg_time;

    public int getNum() {
        return num;
    }

    public void setNum(int num) {
        this.num = num;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

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

    public Date getBirthday() {
        return birthday;
    }

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

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Date getReg_time() {
        return reg_time;
    }

    public void setReg_time(Date reg_time) {
        this.reg_time = reg_time;
    }

    @Override
    public String toString() {
        return "Student{" +
                "num=" + num +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", grade=" + grade +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", reg_time=" + reg_time +
                '}';
    }
}

ResultSet中的next()方法获得下一行数据

public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
        try {
           Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");

            int num = 3 ;
            String sql = "select num,name,sex,birthday,grade,phone,address,reg_time from student where num = ?";

            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1,num);

            //executeQuery()用于执行查询语句，将查询到的结果封装到ResultSet对象中
            ResultSet rs = ps.executeQuery();
            //可以将ResultSet中的对象，获取并转化为我们自己定义对象中
            Student student = new Student();

            //next() 判断结果集中是否还包含数据，如果包含返回true，将指针指向下一个数据
            while(rs.next()){
                //将结果集中获得数据,设置到student对象
                /* 1对应的查询数据的第一列
                student.setNum(rs.getInt(1));
                student.setName(rs.getString(2));*/
                student.setNum(rs.getInt("num"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setBirthday(rs.getDate("birthday"));
                student.setGrade(rs.getInt("grade"));
                student.setPhone(rs.getString("phone"));
                student.setAddress(rs.getString("address"));
                student.setReg_time(rs.getTimestamp("reg_time"));


            }
            System.out.println(student);


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

获得一个表中的所有数据

public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&serverTimezone=UTC";
        //与数据库键连接
        try {
            Connection connection = DriverManager.getConnection(url,"数据库用户名","密码");

            String sql = "select num,name,sex,birthday,grade,phone,address,reg_time from student ";

            PreparedStatement ps = connection.prepareStatement(sql);

            //executeQuery()用于执行查询语句，将查询到的结果封装到ResultSet对象中
            ResultSet rs = ps.executeQuery();

            ArrayList<Student> arrayList = new ArrayList<>();
            //可以将ResultSet中的对象，获取并转化为我们自己定义对象中
            //next() 判断结果集中是否还包含数据，如果包含返回true，将指针指向下一个数据
            while (rs.next()) {
                Student student = new Student();
                student.setNum(rs.getInt("num"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setBirthday(rs.getDate("birthday"));
                student.setGrade(rs.getInt("grade"));
                student.setPhone(rs.getString("phone"));
                student.setAddress(rs.getString("address"));
                student.setReg_time(rs.getTimestamp("reg_time"));
                arrayList.add(student);
            }

            for (Student student: arrayList) {
                System.out.println(student);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }