JavaJDBC学习

JDBC（Java DataBase Connectivity）java数据库连接
● 是一种用于执行SQL语句的Java API，可以为多种关系型数据库提供统一访问，它由一组用Java语言编写的类和接口组成。
● 有了JDBC,java开发人员只需要编写一次程序,就可以访问不同的数据库.

对于JDBC API:

供程序员调用的接口与类，集成在java.sql包中

DriverManager类作用：管理各种不同的jDBC驱动

Connection 接口 与特定数据库的连接

Statement 接口 执行sql

PreparedStatement接口 执行sql

ResultSet接口 接收查询结果

对于JavaJDBC程序的搭建:

第一步:

需要初始化驱动程序，这样就可以打开与数据库的通信信道。

Class.forName(“com.mysql.cj.jdbc.Driver”); //反射实现
or
DriverManager.registerDriver(new Driver());

第二步:

建立与数据库连接：

这需要使用DriverManager.getConnection()方法来创建一个
Connection对象，它代表一个物理连接的数据库.

Connection con = DriverManager.getConnection(URL,USER,PASS);
URL:jdbc:mysql://ip(127.0.0.1):端口(3306)/数据库名?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
USER:本地数据库用户名(root)
PASS:本地数据库密码

第三步:

获得Satement执行sql语句

Statement st = connection.createStatement();

Satement中的方法: Int executeUpdate(String sql) 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行ddl语句返回0
用于执行dml语句返回操作的行数
ResultSet executeQuery(String sql); 用于执行查询语句 返回一个ResultSet 集合

示例 :

 public static void main(String[] args) {
        try {
            //加载mysql驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //连接数据库
            Connection connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
           // System.out.println(connection);
            //发送SQL到数据库
            Statement st= connection.createStatement();
            st.executeUpdate("insert into course (course)values ('php')");//向数据库中插入数据
//中断传输
            st.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

接下来我们开始利用IDEA进行对数据库表中数据的更改

从最基本的利用数据库添加数据开始

public class JDBCDemo1 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo1 jdbcDemo1=new JDBCDemo1();
        jdbcDemo1.student("tom","男","2020-2-2");
    }
    private void student(String name,String sex,String birthday) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate("INSERT INTO t_student(NAME,sex,birthday,reg_time)" +
                    " VALUES('"+name+"','"+sex+"','"+birthday+"',now())");
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }
    }
}

以上的方法适用于初学, 不便于向数据库中添加多条数据,通过自定义Student方法,可以提高添加的方法和效率

获得PrepareStatement执行sql语句

● 在sql语句中参数位置使用占位符,使用setXX方法向sql中设置参数

PrepareStatement ps = connection.prepareStatement(sql);

PrepareStatement中的方法:
Int executeUpdate() 用于执行ddl语句和dml(增,删,改)语句 返回操作的行数
用于执行ddl语句返回0
用于执行dml语句返回操作的行数
ResultSet executeQuery(); 用于执行查询语句 返回一个ResultSet 集合

public class Student {
    private int id;
    private String name;
    private String sex;
    private Date birthday;
    private Date reg_time;
    private int phone;
    private int height;
    private int weight;
    private  int score;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    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(Timestamp birthday) {
        return this.birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public Date getReg_time(Timestamp reg_time) {
        return this.reg_time;
    }
    public void setReg_time(Date reg_time) {
        this.reg_time = reg_time;
    }
    public int getPhone() {
        return phone;
    }
    public void setPhone(int phone) {
        this.phone = phone;
    }
    public int getHeight(int height) {
        return this.height;
    }
    public void setHeight(int height) {
        this.height = height;
    }
    public int getWeight(int weight) {
        return this.weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
    public int getScore(int score) {
        return this.score;
    }
    public void setScore(int score) {
        this.score = score;
    }
    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", reg_time=" + reg_time +
                ", phone=" + phone +
                ", height=" + height +
                ", weight=" + weight +
                ", score=" + score +
                '}';
    }
}

public class JDBCDemo5 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo5 jdbcDemo5=new JDBCDemo5();
        jdbcDemo5.student("tom","男","2000-3-3","2000-3-3");
    }
    private void student(String name, String sex,Object birthday,Object reg_time) throws SQLException {
        Connection connection=null;
        PreparedStatement pt=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("insert into t_student(name,sex,birthday,reg_time)values(?,?,?,?)");//?表示占位符,
            pt.setString(1,name);
            pt.setString(2,sex);
           pt.setObject(3,birthday);
           pt.setObject(4,reg_time);
            pt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            if(connection!=null){
                connection.close();
            }
            if(pt!=null){
                pt.close();
            }
        }
    }
}

其中PreparedStatement的第一次执行消耗是很高的. 它的性能体现在后面的重复执行. 使用PreparedStatement的方式来执行一个针对数据库表的查询. JDBC驱动会发送一个网络请求到数据解析和优化这个查询. 而执行时会产生另一个网络请求. 在JDBC驱动中，减少网络通讯是最终的目的. 如果我的程序在运行期间只需要一次请求, 那么就使用Statement. 对于Statement, 同一个查询只会产生一次网络到数据库的通讯. 这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次,最重要的一点是极大地提高了安全性. 防止sql注入

进行删除操作

public class JDBCDemo2 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo2 jdbcDemo2=new JDBCDemo2();
        jdbcDemo2.student(1);
    }
    private void student(int id) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate("delete from number where Dept_id="+id);
            int row1=st.executeUpdate("delete from dept where id="+id);
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }
    }
}

注意:在进行删除操作时 , 如果有多表关联,需要优先删除与其相关联的从表,否则无法进行删除操作

利用PreparedStatement方法执行操作时

public class JDBCDemo6 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo6 jdbcDemo5=new JDBCDemo6();
        jdbcDemo5.student(10);
    }
    private void student(int id) throws SQLException {
        Connection connection=null;
        PreparedStatement pt=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("delete from t_student where id=?");
          pt.setObject(1,id);
            pt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();}
        finally {
            if(connection!=null){
                connection.close();
            }
            if(pt!=null){
                pt.close();
            }
        }
    }
}

进行数据库消息的更改:

public class JDBCDemo4 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo4 jdbcDemo3=new JDBCDemo4();
        jdbcDemo3.student(8,"jy","女","2000-12-13");
    }
    private void student(int id,String name,String sex,String birthday) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate("update t_student set name='"+name+"',sex='"+sex+"',birthday='"+birthday+"'where id="+id);
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }
    }
}

利用PreparedStatement方法执行删除操作时:

public class JDBCDemo7 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo7 jdbcDemo5=new JDBCDemo7();
        jdbcDemo5.student("1 or 1=1");
    }

    private void student(String id) throws SQLException {
        Connection connection=null;
        PreparedStatement pt=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("delete from t_area where id=?");
          pt.setObject(1,id);
            pt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();}
        finally {
            if(connection!=null){
                connection.close();
            }

            if(pt!=null){
                pt.close();
            }
        }
    }
}

public class JDBCDemo7b {
    public static void main(String[] args) throws SQLException {
        JDBCDemo7b jdbcDemo2=new JDBCDemo7b();
        jdbcDemo2.student("1 or 1=1");
    }
    private void student(String id) throws SQLException {
        Connection connection=null;
        Statement st=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            st=connection.createStatement();
            int row =  st.executeUpdate( "delete from t_area where id = 1 or 1 = 1");
            System.out.println(row);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            st.close();
        }

    }
}

其中JDBCDemo7的线程,他会进行检测SQL攻击,在通过set方法在设置值时,会进行检测,如果传入其他关键字时,就会显示异常

在JDBCDemo7b中他无法对传入的值进行检验,在满足1=1的条件后会执行对数据库的清除操作对数据库整体进行清空,执行完成后会返回 0.

利用PreparedStatement方法执行对数据库查找操作时

public class JDBCDemo8 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo8 jdbcDemo1=new JDBCDemo8();
        jdbcDemo1.student(8);
    }
    private void student(int id) throws SQLException {
        Connection connection=null; //连接数据库
        PreparedStatement pt=null;
Student student=new Student();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("\n" +
                    "SELECT\n" +
                    "  id,\n" +
                    "  NAME,\n" +
                    "  sex,\n" +
                    "  birthday,\n" +
                    "  reg_time,\n" +
                    "  phone,\n" +
                    "  height,\n" +
                    "  weight,\n" +
                    "  score\n" +
                    "FROM\n" +
                    "  t_student\n" +
                    "WHERE id = ?\n");
            pt.setInt(1,id);
            ResultSet res=pt.executeQuery();
            while(res.next()){
                student.setId(res.getInt("id"));
                student.setName(res.getString("name"));
                student.setSex(res.getString("sex"));
                student.getBirthday(res.getTimestamp("birthday"));
                student.getReg_time(res.getTimestamp("reg_time"));
                student.getHeight(res.getInt("height"));
                student.getWeight(res.getInt("weight"));
                student.getScore(res.getInt("score"));
            }
            System.out.println(student);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            pt.close();
        }
    }
}

如果要同时查找多条数据时,我们可以利用集合进行查找:

public class JDBCDemo9 {
    public static void main(String[] args) throws SQLException {
        JDBCDemo9 jdbcDemo1=new JDBCDemo9();
        jdbcDemo1.student("男");
    }
    private ArrayList<Student>student(String sex) throws SQLException {
        Connection connection=null; //连接数据库
        PreparedStatement pt=null;
        ArrayList<Student>list=new ArrayList();
Student student=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/school_db?characterEncoding=utf8&useSSL=false&serverTimezone=UTC","root","root");
            pt=connection.prepareStatement("\n" +
                    "SELECT\n" +
                    "  id,\n" +
                    "  NAME,\n" +
                    "  sex,\n" +
                    "  birthday,\n" +
                    "  reg_time,\n" +
                    "  phone,\n" +
                    "  height,\n" +
                    "  weight,\n" +
                    "  score\n" +
                    "FROM\n" +
                    "  t_student\n" +
                    "WHERE sex = ?\n");
            pt.setString(1,sex);
            ResultSet res=pt.executeQuery();
            while(res.next()){
                student=new Student();
                student.setId(res.getInt("id"));
                student.setName(res.getString("name"));
                student.setSex(res.getString("sex"));
                student.getBirthday(res.getTimestamp("birthday"));
                student.getReg_time(res.getTimestamp("reg_time"));
                student.getHeight(res.getInt("height"));
                student.getWeight(res.getInt("weight"));
                student.getScore(res.getInt("score"));
                list.add(student);
            }
            System.out.println(list);
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
        finally {
            connection.close();
            pt.close();
        }
return list;
    }
}

使用ResultSet中的next()方法获得下一行数据
使用getXXX(String name)方法获得值