CGB2105第二阶段-day05 JDBC

一、JDBC

1、什么是JDBC？

专门用来通过java程序，连接数据库。是一套标准，本质上是用jar包里的各种工具。

数据库实现了数据的持久化，但我们最终要在程序里处理数据啊，那java代码中怎么去访问数据库读写数据呢？

1. 用到sun公司设定的一套数据库标准JDBC（Java Database Connectivity）。
2. 但它只是规范，不做具体实现。
3. 数据库厂商又根据JDBC标准，实现自家的驱动Driver。如：mysql驱动com.mysql.cj.jdbc.Driver，Oracle的驱动oracle.jdbc.OracleDriver。有了这套解决方案，java就可以访问数据库中的数据了。

Java中提倡面向接口开发，而最经典的接口设计莫过于JDBC数据库接口。

Connection链接、Statement语句、PreparedStatement预处理语句、CallableStatement存储过程、ResultSet结果集。

调用方式有三种：Statement语句、PreparedStatement预处理语句、CallableStatement存储过程，推荐使用第二种PreparedStatement，防止SQL注入，其也是预编译性能高。

public interface Connection extends Wrapper, AutoCloseable {}

public interface Statement extends Wrapper, AutoCloseable {}

public interface PreparedStatement extends Statement {}

public interface CallableStatement extends PreparedStatement {}

public interface ResultSet extends Wrapper, AutoCloseable {}

2、使用步骤

1. 导入jar包
2. 连接数据库
3. 发起SQL
4. 处理结果

3、导入jar包

1）创建工程：File-New-Project-选java-next-next-输入工程名-finish
2）创建lib文件夹：选中工程-右键-new-Directory-起个名字lib-finish
3）拷贝jar包：从磁盘里找到下载好的jar包，复制粘贴到lib文件夹里

4）编译jar包：

5）查看是否有Driver类

二、JDBC的使用

1.入门案例:查询指定表的记录

package cn.tedu.test;

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

/*测试jdbc入门案例
* 需求：查询指定表的内容*/
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
         Class.forName("com.mysql.cj.jdbc.Driver");//,高版本jar包的路径，向上抛出
         //Class.forName("com.mysql.jdbc.Driver");//低版本jar包，向上抛出
        //2.连接数据库(用户名 密码 IP地址 端口号)
        //String url = "使用的协议://IP地址:端口号/数据库名";
        String url = "jdbc:mysql://localhost:3306/cgb2105";//连接哪个数据库(库名)
        String user = "root";//连接数据库的用户名
        String pwd = "root";//连接数据库的密码
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3.获取传输器
        Statement st = conn.createStatement();
        //4.执行SQL--查询部门表的所有数据
        String sql = "select * from dept";
        //5.executeQuery()方法执行查询sql语句,并且把结果封装给ResultSet结果集
        ResultSet rs = st.executeQuery(sql);
        //6.解析结果集
        //next()判断有没有下一条数据，有返回true，没有返回false
        //next()提供指针，获取数据后往下移判断有没有下一条数据
        while(rs.next()){
            //7.获取数据：可以根据 列 的编号 或者 列名
//          int num = rs.getInt(1);//获取第一列的数据
//          String name = rs.getString(2);//获取第二列的数据
//          String  loc = rs.getString(3);//获取第三列的数据

            int num = rs.getInt("deptno");//获取deptno列的数据
            String name = rs.getString("dname");//获取dname列的数据
            String  loc = rs.getString("loc");//获取loc列的数据

            System.out.println(num +"\t"+ name+"\t" + loc);
        }
        //8.释放资源
        rs.close();//释放结果集
        st.close();//释放传输器
        conn.close();//释放连接
    }
}

2、向数据库指定表中增添记录

package cn.tedu.test;

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

/*利用jdbc向dept表中插入数据*/
public class Test2 {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接数据库
        String url = "jdbc:mysql://localhost:3306/cgb2105";
        String user = "root";
        String pwd = "root";
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3.获取传输器
        Statement st = conn.createStatement();
        //4.执行SQL
        //利用jdbc向dept表插入数据
        String s = "insert into dept values(5,'java开发部1','合肥')";
        //executeUpdate()用来执行增删改的SQL语句，返回影响行数
        int rows = st.executeUpdate(s);
        //5.解析结果集,提示成功
        System.out.println("数据插入成功");
        //6.释放资源
        st.close();
        conn.close();
    }
}

3、使用IDEA给指定数据库创建表并添加表记录

package cn.tedu.test;

import org.junit.Test;

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

/*练习如何给指定数据库创建表，并输入内容*/
public class TestCreateInsert {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接数据库--用户名 密码 IP地址 端口号
       //指定数据库的格式：jdbc.mysql://localhost:端口号/数据库名
        // String url = "jdbc:mysql://localhost:3306/cgb2105";//指定数据库
        String url = "jdbc:mysql://localhost:3306/cgb2105";//指定数据库
        String user = "root";//设置用户名
        String pwd = "root";//设置密码
        Connection conn = DriverManager.getConnection(url, user, pwd);

        //3.获取传输器--createStatement()
        Statement st = conn.createStatement();

        //4.创建表--SQL语句
        // 创建表并使用传输器st.execute()连接到数据库cgb2105
        //如果有同名的表或者表建错了，都可使用"drop table if exists+表名"删除
        String delSQL = "drop table if exists user1";
        
        String crSQL = "create table user1(" +
                "id int(10) primary key auto_increment," +
                "name varchar(16) default null," +
                "password varchar(12) default null" +
                ")";
        boolean del = st.execute(delSQL);
        boolean cr = st.execute(crSQL);
        //提醒
        System.out.println("创建user1表成功！");

        //5.添加表记录-SQL语句insert into+表名+values(字段值,...)
        //使用st.execute()传输
        //插入的优化方式，批量提交
        String inSQL = "insert into user1 values(1,'张三','123'),(2,'李四','456'),(3,'王二','789')";
        int i = st.executeUpdate(inSQL);
        //提醒
        System.out.println("用户记录登记成功");
        //6.释放资源
        st.close();
        conn.close();
    }
    @Test
    public void getSelect() throws Exception {
        Class<?> clazz = Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/cgb2105";//指定数据库
        String user = "root";//设置用户名
        String pwd = "root";//设置密码
        Connection conn = DriverManager.getConnection(url, user, pwd);

        Statement st = conn.createStatement();

        String s = "select * from user1";
        ResultSet rt = st.executeQuery(s);

        while(rt.next()){
            String id = rt.getString("id");
            String name = rt.getString("name");
            String password = rt.getString("password");
            //通过拼接打印查看
            System.out.println(id+"\t"+ name + "\t" + password);
        }
        //释放资源
        st.close();
        conn.close();
    }
}

4、测试修改表内容

注意：单元测试的操作也会造成实际的效果

格式：update 表名 set(字段名=字段值) where 对应的主键字段名=主键值

其中：可以给经常用到的一部分代码创建一个类包，以后调用方便

比如：

package cn.tedu.SQLTool;

import java.sql.Connection;

public class ConnectionMethod {
    public static java.sql.Connection getConnection() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//,高版本jar包的路径，向上抛出
        //Class.forName("com.mysql.jdbc.Driver");//低版本jar包，向上抛出

        //2.连接数据库(用户名 密码 IP地址 端口号)
        //String url = "使用的协议://IP地址:端口号/数据库名";
        String url = "jdbc:mysql://localhost:3306/cgb2105";//指定连接哪个数据库
        String user = "root";//连接数据库的用户名
        String pwd = "root";//连接数据库的密码
        Connection conn = java.sql.DriverManager.getConnection(url, user, pwd);
        return conn;
    }
}

然后：

package cn.tedu.test;
public class TestCreateInsert {
 @Test
    public void getUpdate()  {
        //调用SQLTool包的类方法
        try {
            Connection conn = ConnectionMethod.getConnection();
            //获取传输器
            Statement st = conn.createStatement();
            //修改表内容
            String update = "update user1 set password = '147' where id = 3";
            int i = st.executeUpdate(update);
            System.out.println("修改成功");
        } catch (Exception e) {
            System.out.println("修改失败");
            e.printStackTrace();
        }

    }
}

5.测试用户登录

****SQL注入的问题以及如何解决：

1）SQL 攻击(注入)发生的现象是：用户输入了一些SQL中的特殊字符，#表示注释，用户名+'#即可登录  或者用户名输入正确，但是密码输入12345' or '1'='1  会被判为正确
2）Statement工具：无法避免SQL注入问题，而且SQL复杂需要自己拼接参数，低效
3）PreparedStatement工具：避免了SQL攻击的问题，SQL简单，高效
----SQL简单，先把SQL骨架发给数据库，再把参数发给数据库。用?代替参数的位置叫占位符

package cn.tedu.test;
/*测试用户登录*/
import java.sql.*;
import java.util.Scanner;
//create table user(
//        id int primary key auto_increment,
//        name varchar(20) ,
//        password varchar(20)
//        );
//        insert into user(id,name,password) values(null,'jack','123'),(null,'rose','123');


public class Test {
    public static void main(String[] args) throws Exception {
       // method();//固定用户名和密码
       // method2();//接收用户输入用户名和密码
        method3();
    }

    private static void method3() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/sqlreview?characterEncoding=utf8";
        //characterEncoding=utf8字符集限制，否则乱码
        //localhost:3306,端口号必须是3306才能简写(省略不写)
        //String url = "jdbc:mysql:///sqlreview?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");

        //3.获取传输器--传输SQL语句并返回结果
        //Statement st = conn.createStatement();
        System.out.println("分别输入用户名和密码：");
        String name1 =new Scanner(System.in).nextLine();
        String pwd =new Scanner(System.in).nextLine();

        String sql= "select * from user where name =? and password =?";
        /*3.1使用新的传输器prepareStatement()把SQL骨架和参数分开发送给数据库
        *解决了SQL语句攻击问题：jack'#
        * ？叫作占位符，占位符避免了拼接容易出错的问题
        * 顺序发什么改变：先占位，后传输数据*/
        PreparedStatement ps = conn.prepareStatement(sql);
        //3.2单独给SQL设置参数：给第几个问号，赋什么值
        ps.setString(1,name1);
        ps.setString(2,pwd);
        //4.执行SQL,根据传输的内容去查库
        ResultSet rs = ps.executeQuery();
        //5.解析结果集
        //next()判断是否还要下一个数据，查看到返回true，登录成功，否则失败
        if(rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }

        //6.释放资源的顺序：结果集-->传输器-->连接器，先开后放
        rs.close();
        ps.close();
        conn.close();
    }

    private static void method2() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        //String url = "jdbc:mysql://localhost:3306/sqlreview?characterEncoding=utf8";
        //characterEncoding=utf8字符集限制，否则乱码
        //localhost:3306,端口号必须是3306才能简写(省略不写)
        String url = "jdbc:mysql:///sqlreview?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");

        //3.获取传输器--传输SQL语句并返回结果
        Statement st = conn.createStatement();
        //4.执行SQL,根据用户名和密码查库所以使用where过虑数据，and表示两个数据必须同时满足
        System.out.println("分别输入用户名和密码：");
        String name1 =new Scanner(System.in).nextLine();
        String pwd =new Scanner(System.in).nextLine();
        //String sql= "select * from user where name=? and password =?";
        //接收用户输入，动态拼接
        /*SQL攻击：SQL注入问题，本质是因为用户输入的特殊符号造成SQL语义发生了改变。
        *举例：sql= "select * from user where name='"+name1+"'# and password ='"+pwd+"'";
        *加#号，在sql语句中属于注释,用户只输入：rose'#就可登录成功
         */
        String sql= "select * from user where name='"+name1+"'# and password ='"+pwd+"'";
        ResultSet rs = st.executeQuery(sql);
        //5.解析结果集
        //next()判断是否还要下一个数据，查看到返回true，登录成功，否则失败
        if(rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }

        //6.释放资源的顺序：结果集-->传输器-->连接器，先开后放
        rs.close();
        st.close();
        conn.close();

    }

    //模拟登录：根据用户名和密码查询user表
    private static void method() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        //String url = "jdbc:mysql://localhost:3306/sqlreview?characterEncoding=utf8";
        //characterEncoding=utf8字符集限制，否则乱码
        //localhost:3306,端口号必须是3306才能简写(省略不写)
        String url = "jdbc:mysql:///sqlreview?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");

        //3.获取传输器--传输SQL语句并返回结果
        Statement st = conn.createStatement();
        //4.执行SQL,根据用户名和密码查库所以使用where过虑数据，and表示两个数据必须同时满足
        //String sql= "select * from user where name=? and password =?";

        System.out.println("分别输入用户名和密码：");
        String name1 =new Scanner(System.in).nextLine();
        String pwd =new Scanner(System.in).nextLine();
        //结果太固定
       String sql= "select * from user where name='jack' and password ='123'";
       //String sql= "select * from user where name='jack'# and password ='123'";
        //5.解析结果集
        ResultSet rs = st.executeQuery(sql);
        //next()判断是否还要下一个数据，查看到返回true，登录成功，否则失败
        if(rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }

        //6.释放资源的顺序：结果集-->传输器-->连接器，先开后放
        rs.close();
        st.close();
        conn.close();

    }

}

6、提取jdbc重复的代码，作为工具类，提高复用性

package cn.tedu.jdbctool;

import java.sql.Connection;
import java.sql.DriverManager;
/*提取jdbc重复的代码。提高复用性*/
public class JDBCUtils {
    /**
     * 获取数据库的连接
     * @return
     * @throws Exception
     */
    public static  Connection getConnection() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.连接数据库（数据库，用户名，密码）
        String url = "jdbc:mysql://localhost:3306/sqlreview?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");
        return conn;
    }

/**
*释放资源
*@param rs 结果集
*@param ps 传输器
*@paramconn 数据库的连接*/

public static void close(ResultSet rs ,PreparedStatement ps,Connection conn ){
 if(rs !=null) { //防止空指针异常
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
  if(ps !=null) { //防止空指针异常
                try {
                    ps.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

  if(conn !=null) {//防止空指针异常
                try {
                    conn.close();
                    //System.out.println("执行成功");
                } catch (SQLException throwables) {
                    //System.out.println("执行失败");//项目上线后使用
                    throwables.printStackTrace();//项目测试阶段捕获异常
                }
          }
}

}

7.练习删除用户信息

需求：删除id= 1的用户信息

package cn.tedu.test;

import cn.tedu.jdbctool.JDBCUtils;

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

public class TestDelete1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps =null;
        try {
            //1.调用JDBC工具类
             conn = JDBCUtils.getConnection();

            //3.获取传输器，利用高级工具类执行SQL语句
            // 3.1先执行传输SQL骨架
            String sql = "delete from user where id =?";
            /*使用安全的传输器，？是占位符，下面需要设置值*/
             ps = conn.prepareStatement(sql);
            //3.2给第一个占位符？设置值为1
            ps.setInt(1, 1);
            //4.执行SQL增删改
            int rows = ps.executeUpdate();
            System.out.println("删除成功");
        }catch(Exception e){
            System.out.println("删除失败");
        }finally {//最终一定会被执行的放在finally中
    
            //5.释放资源建立工具类进行调用--要参数就写，不要就写null
            JDBCUtils.close(null,ps,conn);
            }
        }
    }
}

)