JDBC、单元测试、DAO模式

JDBC简介

1、什么是JDBC？

　　JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API（工具）。JDBC是Java访问数据库的标准规范。

　　规范：在java中的直接体现是接口

　　作用：为不同关系型数据库提供统一的访问，由一组用java语言编写的接口和工具类组成，由各大数据库厂商实现对应接口

2、连接数据库时要先加载驱动

　　什么是驱动？

　　两个设备要进行通信时，需要满足一定通信数据格式，数据格式由设备提供商规定，设备提供商为设备提供驱动软件，通过软件可以与该设备进行通信。

　　Java和数据库要想进行链接，必须提前规定一些数据格式，格式由数据库厂商实现。

　　mysql连接工具下载地址：https://dev.mysql.com/downloads/connector/j/

3、JDBC是接口，而JDBC驱动才是接口的实现，没有驱动无法完成数据库连接！每个数据库厂商都有自己的驱动，用来连接自己公司的数据库。

JDBC连接详解

1.通过JDBC连接数据库需要五步

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

public class Demo01 {

    public static void main(String[] args) throws Exception {
        //1、加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、创建连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_test?serverTimezone=GMT%2B8", "root", "root");
        //3、编译sql语句 编译器
        Statement statement = conn.createStatement();
        String sql = "select * from test";
        //4、发送sql 并接受结果
        ResultSet eq = statement.executeQuery(sql);
        //根据列名获取数据 迭代器
        while(eq.next()) {
            int sid = eq.getInt(1);
            String name = eq.getString(2);
            int chinese = eq.getInt("chinese");
            int math = eq.getInt("math");
            System.out.println(sid+"	"+name+"	"+chinese+"	"+math);
        }
        //5、关闭资源 
        eq.close();
        statement.close();
        conn.close();
    }

}

2、详解每一步

　　1、Class.forName("com.mysql.cj.jdbc.Driver"); //这里写下载的连接工具包中的驱动路径

　　　　通过反射，建立驱动的对象

　　2、Connection conn = DriverManager.getConnection(url,username,password);  //获取连接

　　url="jdbc:mysql://localhost:3306/db_test?serverTimezone=GMT%2B8" 

　　协议，有严格书写规范   主协议名:子协议名://主机名:端口号/数据库名　　在连接工具到了6.0之后需要设置时区

　　　　serverTimezone是时区，如果设定serverTimezone=UTC，会比中国时间早8个小时，设定中国则为GMT%2B8

　　username = "root", password =  "root"　　数据库的用户名和密码

　　3、获取发送sql语句的对象

　　Statement statement = conn.createStatement();

　　4、发送sql，执行并接受结果

　　executeQuery(String sql)，只能执行select语句，如果执行insert into,update,delete from 都会报错，会把SQL语句传递给mysql数据库去执行

　　executeUpdate(String sql)，执行更新的SQL语句。只能执行insert into,update,delete from，如果执行select语句会报错，会把SQL语句传递给mysql数据库去执行

　　execute(String sql)　　执行任意语句，执行select 返回true，执行insert into,update,delete from  返回false

　　5、释放资源

　　eq.close();statement.close();conn.close();

3、JDBC的增删改查

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class Demo2 {

    public static void main(String[] args) throws Exception{
        Connection conn=null;
        Scanner sc=new Scanner(System.in);
        PreparedStatement ps =null;
        ResultSet eq =null;
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql:///db_701?serverTimezone=GMT%2B8", "root", "ccc");
        login(conn,sc,eq,ps);
        
    }
    public static void login(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception {
        ps = conn.prepareStatement("select username,pwd from users where id='1'");
        eq = ps.executeQuery();
        int flag=1;
        if(eq.next()) {
            do{
                System.out.println("请输入用户名:");
                String s1 = sc.nextLine();
                String s2 = eq.getString(1);
                System.out.println(s2);
                if(s1.equals(s2)) {
                    System.out.println("请输入密码:");
                    String pwd1 = sc.nextLine();
                    String pwd2 = eq.getString(2);
                    if(pwd1.equals(pwd2)) {
                        System.out.println("登录成功,欢迎使用");
                        flag=0;
                    }else {
                        System.out.println("用户名密码错误 ,请重新输入");
                        continue;
                    }
                }else {
                    System.out.println("用户名不存在，请重新输入");
                    continue;
                }
            }while(flag==1);
            test(conn,sc,eq,ps);
        }
    }
    public static void test(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception{
        System.out.println("请选择功能：1、添加  2、删除  3、修改  4、查询  0、退出");
        int num =Integer.parseInt(sc.nextLine());
        switch(num){
            case 1:
                add(conn,sc,eq,ps);
                break;
            case 2:
                delete(conn,sc,eq,ps);
                break;
            case 3:
                alter(conn,sc,eq,ps);
                break;
            case 4:
                select(conn,sc,eq,ps);
                break;
            case 5 :
                System.out.println("谢谢您的使用,再见");
                after(conn,sc,eq,ps);
                break;
        }
    }
    public static void add(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception {
        ps = conn.prepareStatement("insert into users values(null,?,?)");
        System.out.println("请输入用户名:");
        ps.setString(1, sc.nextLine());
        int flag=1;
        while(flag==1) {
            System.out.println("请输入密码 :");
            String pwd1 = sc.nextLine();
            System.out.println("确认密码:");
            String pwd2 = sc.nextLine();
            if(pwd1.equals(pwd2)) {
                ps.setString(2, pwd2);
                flag=0;
            }else {
                System.out.println("两次密码输入不一致，请重新输入密码 ");
                continue;
            }
        }
        int count=ps.executeUpdate();
        if(count>0) {
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
        test2(conn,sc,eq,ps);    
    }
    public static void delete(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception{
        ps = conn.prepareStatement("delete from users where username=? and pwd=?");
        int flag=1;
        while(flag==1) {
            System.out.println("请输入想要删除的用户名:");
            String s = sc.nextLine();
            ps.setString(1, s);
            System.out.println("请输入此用户的密码 :");
            String pwd1 = sc.nextLine();
            ps.setString(2, pwd1);
            int count=ps.executeUpdate();
            if(count>0) {
                System.out.println("删除成功");
                flag=0;
            }else {
                System.out.println("用户名密码错误，删除失败  1.继续删除  0.返回 ");
                String s1 = sc.nextLine();
                if(s1.equals("1")) {
                    continue;
                }else {
                    flag=0;
                    System.out.println("正在返回上一级");
                }
            }
        }
        test2(conn,sc,eq,ps);    
    }
    public static void alter(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception {
        ps = conn.prepareStatement("update users set pwd=? where username=? and pwd=?");
        int flag=1;
        int flag1=1;
        while(flag==1) {
            System.out.println("请输入想要修改密码的用户名:");
            String s1 = sc.nextLine();
            ps.setString(2, s1);
            System.out.println("请输入原密码 ");
            String pwd = sc.nextLine();
            ps.setString(3, pwd);
            
            while(flag1==1) {
                System.out.println("请输入修改的密码 :");
                String pwd1 = sc.nextLine();
                System.out.println("确认密码:");
                String pwd2 = sc.nextLine();
                if(pwd1.equals(pwd2)) {
                    ps.setString(1, pwd2);
                    flag1=0;
                }else {
                    System.out.println("两次密码输入不一致，请重新输入 ");
                    continue;
                }
            }
            int count=ps.executeUpdate();
            if(count>0) {
                System.out.println("修改成功");
                flag=0;
            }else {
                System.out.println("修改失败");
                System.out.println("用户名密码错误，修改失败  1.继续修改  0.返回 ");
                String s3 = sc.nextLine();
                if(s3.equals("1")) {
                    continue;
                }else {
                    flag=0;
                    System.out.println("正在返回上一级");
                }
                
            }
        }
        test2(conn,sc,eq,ps);
    }
    public static void select(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception {
        System.out.println("1.查询全部  2.指定用户名查询");
        String s = sc.nextLine();
        if(s.equals("2")) {
            ps = conn.prepareStatement("select * from users where username=?");
            System.out.println("请输入要查询的用户名:");
            String s1 = sc.nextLine();
            ps.setString(1, s1);
            eq = ps.executeQuery();
        }else if(s.equals("1")) {
            ps = conn.prepareStatement("select * from users");
            eq = ps.executeQuery();    
        }
        if(eq.next()) {
            do{
                String username = eq.getString(2);
                String pwd = eq.getString(3);
                System.out.println(username+"	"+pwd);
            }while(eq.next()) ;
        }else {
            System.out.println("查询失败");
        }
        test2(conn,sc,eq,ps);
    }
    public static void test2(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception{
        System.out.println("1.继续  0.退出");
        String s3 = sc.nextLine();
        if(s3.equals("1")) {
            test(conn,sc,eq,ps);
        }else {
            System.out.println("谢谢您的使用,再见");
            after(conn,sc,eq,ps);
        }
    }
    public static void after(Connection conn,Scanner sc,ResultSet eq,PreparedStatement ps) throws Exception{
        if(conn!=null) {
            conn.close();
        }
        if(ps!=null) {
            ps.close();
        }
        if(sc!=null) {
            sc.close();
        }
        if(eq !=null) {
            eq.close();
        }
    }
}

4、工具类的抽取

　　将重复的加载驱动和获取连接和关闭资源封装到工具类，方便使用

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

public class JDBCUtils {
    public static final String driver = "com.mysql.cj.jdbc.Driver";
    public static final String url = "jdbc:mysql://localhost:3306/db_test?serverTimezone=GMT%2B8";
    public static final String user = "root";
    public static final String password="root";
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        
    }
    public static Connection getConn() {
        try {
            return DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    public static void closeAll(ResultSet rs,Statement st,Connection conn) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

SQL注入问题

1、在上面的JDBC使用中，存在着一个安全问题，那就是SQL的注入问题

　　这个问题主要发生在编译对象身上，因为statement对象每次都是将字符串拼接完毕之后才发送给数据库执行，因此就产生了安全问题。

　　eg:用户登录　　假设用户名密码必须是root 和root才能登录获得查看结果

　　sql语句为　　String sql = "select * from users where username='"+username+"' and password='"+password+"'";

　　username  和 password需要用户输入

　　注入问题如下：

　　username = a' or 'a'='a;

　　password = a' or 'a'='a;

　　此时sql语句变为 select * from users where username = 'a' or 'a'='a' and password='a' or 'a'='a'

　　虽然用户名密码均不正确，但依然可以登录成功进行查看，这就是SQL注入问题

2、那么该如何解决呢？

　　使用预编译对象 preparedStatement

　　PreparedStatement与Statement的区别：

　　　　前者对sql语句进行了预编译

　　　　PreparedStatement将语句先送回数据库

　　　　使语句的逻辑已经确定为select * from users where username=? and password=?

　　　　此时用户如果输入同样的内容，却无法改变原有的逻辑，会产生语法错误，所以避免了注入问题

　　且预编译对象在使用同一条语句传不同内容时，也只需编译一次，大大提高了效率

3、PreparedStatement的预编译是数据库进行的，编译后的函数key是缓存在PreparedStatement中的，编译后的函数是缓存在数据库服务器中的。预编译前有检查sql语句语法是否正确的操作。只有数据库服务器支持预编译功能时，JDBC驱动才能够使用数据库的预编译功能，否则会报错。

　　jdbc:mysql://localhost:3306/db?useServerPrepStmts=true  可以设置数据库开启预编译

4、使用不同的PreparedStatement对象来执行相同的SQL语句时，还是会出现编译两次的现象，这是因为驱动没有缓存编译后的函数key，导致二次编译。如果希望缓存编译后函数的key，那么就要设置cachePrepStmts参数为true。

　　jdbc:mysql://localhost:3306/db?useServerPrepStmts=true&cachePrepStmts=true

5、PreparedStatement的预编译还有注意的问题，在数据库端存储的函数和在PreparedStatement中存储的key值，都是建立在数据库连接的基础上的，如果当前数据库连接断开了，数据库端的函数会清空，建立在连接上的PreparedStatement里面的函数key也会被清空，各个连接之间的预编译都是互相独立的。

6、PreparedStatement的使用

setInt(int index,int value)	为？占位符，赋予int值
setString(int index,String value)	为？占位符，赋予String值
setObject(int index,Object value)
executeQuery()	执行查询的SQL语句。 只能执行select语句， 如果执行insert into,update,delete from 都会报错 会把SQL语句传递给mysql数据库去执行 返回结果：ResultSet对象---一张二维表格
executeUpdate()	执行更新的SQL语句。 只能执行insert into,update,delete from 如果执行select语句会报错 会把SQL语句传递给mysql数据库去执行 返回结果：int   SQL语句执行后更新了几行数据

上面的代码练习中采用的就是PreparedStatement。

单元测试

1、导包 右击项目-->prooerties-->java build path -->libraries-->add library-->JUint-->next finish

2、给想要测试的方法加上注解（在上方加上 @Test）

3、可以在其他方法上加@Before 这个方法会在测试方法之前执行  用来加载资源

 　　@After 这个方法会在测试方法之后执行   用来关闭资源

DAO模式

Database  Access  Object

把对数据库进行的JDBC操作(增、删、改、查) 都放在一个类中，用不同的方法分别来完成增、删、改、查。

一张数据库表做一个实体类，数据库的列是类的属性，数据库的一行数据是类的一个对象