[大数据]搞懂JDBC技术看这一篇就够了

一、JDBC简介

1.JDBC是什么？

2.JDBC编写步骤

二、如何获取数据库连接

前期准备工作：
1.在工程下新建lib文件夹
2.将jar包导入到lib目录下

3.将导入的jar包通过Add as library添加到项目中
4.创建相关包和测试类

5.在数据库中新建数据库

6.重要知识点：

1.获取数据库连接方法一

//获取JDBC连接方法一
public class jdbc_Test {
    public static void main(String[] args) throws SQLException {
        //加载驱动
        Driver driver = new com.mysql.jdbc.Driver();
        //定义url
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        //通过Properties设置账号密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","mysql");
        //通过Connection连接
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
    }
}

2.获取数据库连接方法二

通过反射方法获取driver对象
此方法无第三方API，有助于代码移植以及兼容性

//获取数据库连接方法二
public class jdbc_Test2 {
    public static void main(String[] args) throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        /*
        * 通过反射方法获取driver对象
        * */
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","mysql");
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
    }
}

3.获取数据库连接方法三

通过驱动管理器 DriverManager 代替 Drive 获取连接

//获取数据库连接方法三
public class jdbc_Test3 {
    //用DriverManager 代替 Driver
    public static void main(String[] args) throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //用反射获取driver具有良好的代码移植性
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        //将driver注册到DriverManager中
        DriverManager.registerDriver(driver);
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        String user = "root";
        String password = "mysql";
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }
}

4.获取数据库连接方法四

//获取数据库连接方法四
public class jdbc_Test4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //当com.mysql.jdbc.Driver加载到内存虚拟机中时，在Driver类中有static块会自动将Driver注册到DriverManager中
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        String user = "root";
        String password = "mysql";
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }
}

5.获取数据库连接方法五（推荐）

将配置文件与程序分离开
优点：
1.实现数据与代码的分离，实现了解耦
2.如果要修改可直接修改配置文件，可以避免程序重打包

//获取数据库连接方法五（推荐）
public class jdbc_Test5 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //将配置文件读入
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        //读入数据
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }
}

三、JDBC基本操作

int execute(String sql); //执行insert、update、delete操作
ResultSet executeQuery(String sql); //执行查询操作

1.通过Statement操作和访问数据库

Statement的使用

//通过Statement操作和访问数据库
public class Statement_Test {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        //获取statement对象
        Statement statement = conn.createStatement();
        //拼接sql
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入账户:");
        String username = sc.next();
        System.out.println("请输入密码:");
        String userpassword = sc.next();
        String sql = "insert into user(username,userpassword) values('" + username + "','" + userpassword + "')";
        //执行sql
        statement.execute(sql);
        //关闭资源
        conn.close();
        statement.close();
    }
}

2.通过ResultSet执行select

//通过ResultSet执行select
public class Statement_Test2 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);

        //获取statement对象
        Statement statement = conn.createStatement();
        String sql = "select * from user";
        ResultSet rs = statement.executeQuery(sql);
        while(rs.next()){
            System.out.println(rs.getString("username")+"  "+rs.getString("userpassword"));
        }
        conn.close();
        statement.close();
        rs.close();
    }
}

3.PreparedStatement添加操作

//PreparedStatement添加操作
public class Statement_Test3 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);

        //创建PreparedStatement对象
        String sql = "insert into user(username,userpassword) values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        //填充占位符
        ps.setString(1,"admin6");
        ps.setString(2,"123456");
        //执行添加
        ps.executeUpdate();
        //关闭资源
        ps.close();
        conn.close();
    }
}

4.将获取连接以及释放连接代码封装到JDBCUtils

为了统一对Connection资源的管理及使用，创建JDBCUtils工具类，实现对数据库的连接与释放进行统
一管理。

//将获取连接以及释放连接代码封装到JDBCUtils
public class JDBCUtils {
    //获取连接
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        return conn;
    }
    //关闭资源操作
    public static void close(Connection conn,Statement statement) throws SQLException {
        if (conn != null){
            conn.close();
        }
        if (statement != null){
            statement.close();
        }
    }
}

5.通过PreparedStatement对数据进行修改

//通过PreparedStatement对数据进行修改
public class PreparedStatement_Test2 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        //获取连接并创建PreparedStatement对象
        Connection conn = JDBCUtils.getConnection();
        String sql = "update user set username = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        //填充占位符
        ps.setObject(1,"admintest");
        ps.setInt(2,1);
        //执行修改
        ps.executeUpdate();
        //关闭资源
        JDBCUtils.close(conn,ps);
    }
}

6.PreparedStatement通用增删改方法

//PreparedStatement通用增删改方法
public class Statement_Test4 {
    public static void main(String[] args) throws Exception {
        String sql = "delete from user where id = ? or id = ?";
        update(sql,1,2);
    }

    public static void update(String sql,Object...args) throws Exception {
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i<args.length; i++){
            ps.setObject(i+1,args[i]);
        }
        ps.executeUpdate();
        JDBCUtils.close(conn,ps);
    }
}

7.PreparedStatement通用查询方法

//PreparedStatement通用查询方法
public class PreparedStatement_Test3 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        String sql = "select * from user where id < ?";
        query(sql,6);
    }

    public static void query(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        ResultSet rs = ps.executeQuery();
        //获取ResultSet元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取表格列数
        int columnCount = rsmd.getColumnCount();
        while (rs.next()){
            for (int i =0;i<columnCount;i++){
                System.out.print(rs.getObject(i+1)+" ");
            }
            System.out.println();
        }
        rs.close();
        JDBCUtils.close(conn,ps);
    }
}

8.ORM编程思想

//ORM编程思想
public class PreparedStatement_Test4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, NoSuchFieldException, IllegalAccessException, IOException {
        String sql = "select * from user where id < ?";
        List<User> users = queryUser(sql,6);
        System.out.println();
    }

    public static List<User> queryUser(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException, NoSuchFieldException, IllegalAccessException {
        //获取Connection
        Connection conn = JDBCUtils.getConnection();
        //预处理
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置不定参参数
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args);
        }
        List<User> users = new ArrayList<>();
        //查询操作
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取列数
        int columnCount = rsmd.getColumnCount();
        while (rs.next()){
            User u = new User();
            for (int i=0;i<columnCount;i++){
                //获取列值
                Object columnValue = rs.getObject(i+1);
                //获取列名
                String columnName = rsmd.getColumnName(i+1);
                //通过反射获取属性
                Field field = u.getClass().getDeclaredField(columnName);
                //提升权限
                field.setAccessible(true);
                //赋值
                field.set(u,columnValue);
            }
            //将赋值后的user对象，添加进list
            users.add(u);
        }
        rs.close();
        JDBCUtils.close(conn,ps);
        return users;
    }
}

四、JDBC实现用户登录功能

1.业务介绍

2.登录界面初始化

//用户登录功能_登录界面初始化
public class Login {
    public static void main(String[] args) {
        initUI();
    }

    public static Map<String,String> initUI(){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入账号:");
        String account = sc.next();
        System.out.println("请输入密码:");
        String password = sc.next();
        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("account",account);
        userLoginInfo.put("password",password);
        return userLoginInfo;
    }
}

3.登录实现

Account实体类

//Account实体类
public class Account {
    private Integer userid;
    private String useraccount;
    private String username;
    private String userpassword;

    public Integer getUserid() {
        return userid;
    }

    public void setUserid(Integer userid) {
        this.userid = userid;
    }

    public String getUseraccount() {
        return useraccount;
    }

    public void setUseraccount(String useraccount) {
        this.useraccount = useraccount;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUserpassword() {
        return userpassword;
    }

    public void setUserpassword(String userpassword) {
        this.userpassword = userpassword;
    }
}

针对account表实现通用查询

//针对account表实现通用查询
    public static List<Account> queryAccount(String sql,Object...args) throws Exception{
        List<Account> accounts = new ArrayList<>();
        //获取数据库的连接
        Connection conn = JDBCUtils.getConnection();
        //预编译sql语句，返回PrepareStatement的实例
        PreparedStatement ps = conn.prepareStatement(sql);
        //填充占位符
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //执行
        ResultSet rs = ps.executeQuery();
        //获取结果集元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //通过ResultSetMetaData获取结果集中的列数
        int columncount = rsmd.getColumnCount();
        while (rs.next()){
            Account account = new Account();
            for (int i =0;i<columncount;i++){
                //获取该列的值
                Object columnValue = rs.getObject(i+1);
                //获取该列的名称
                String columnName = rsmd.getColumnName(i+1);
                //获取该字段相关属性
                Field field = account.getClass().getDeclaredField(columnName);
                //提升权限
                field.setAccessible(true);
                //赋值
                field.set(account,columnValue);
            }
            accounts.add(account);
        }
        //资源的关闭
        rs.close();
        JDBCUtils.close(conn,ps);
        return accounts;
    }

登录实现

//登录实现
    public static boolean login(Map<String,String> userLoginInfo) throws Exception{
        //定义sql
        String sql = "select * from account where useraccount = ? and userpassword = ?";
        //获取所有匹配账号密码的account对象
        List<Account> accounts = queryAccount(sql,userLoginInfo.get("account"),userLoginInfo.get("password"));
        //如果该集合为0,代表账号或密码没有匹配，登陆失败
        if (accounts.size() == 0){
            return  false;
        }
        return true;
    }

测试登录功能

public static void main(String[] args) throws Exception {
       Map<String,String> userLoginInfo = initUI();
       System.out.println(login(userLoginInfo)?"登录成功":"登录失败");
    }

四、JDBC高级

1.什么是SQL注入

SQL注入是指利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的
SQL语句段或者命令，从而利用系统的SQL引擎完成恶意行为的做法。
例如当使用Statement实现时：

Statement statement = conn.createStatement();

SQL语句改为:

String sql = "select * from account where useraccount = '" +userLoginInfo.get("account")+ "' and userpassword = '" + userLoginInfo.get("password") +"'";

当你测试时:

以上输入的账号和密码，通过SQL拼接，在执行过程中的SQL实际上是：

select * from account where useraccount = 'zhangsan' and userpassword ='baizhan'or'1=1'

由于1=1永远成立，所以不论账号密码是否正确，都会返回正确。

导致SQL注入的根本原因：
用户输入的信息中包含SQL语句的关键字，并且这些关键字参与SQL语句的编译过程，导致SQL语句的原意被扭曲，进而达到SQL注入的目的。

2.如何解决SQL注入

只要用户提供的信息不参与SQL语句的编译过程，即使用户提供的信息中包含SQL语句的关键字，但是
没有参与编译，仍然不起作用。

PreparedStatement可以将信息参数化，仍然用PreparedStatement实现登录功能：

        PreparedStatement ps = conn.prepareStatement(sql);

以上输入的账号和密码，通过PreparedStatement预编译，将baizhan’or’1=1作为一个整体的字符串参
数设置到SQL当中，在执行过程中的SQL实际上是：

select * from account where useraccount = 'zhangsan' and userpassword ="baizhan'or'1=1"

3.批量插入数据

利用三种不同的方法插入数据，并测试其用时。
首先通过Navicat创建一张物品空表：

CREATE TABLE goods(id int PRIMARY key auto_increment,goodsname VARCHAR(25))

依次用三种方法向表中插入2000条数据：
方法一、通过Statement + for循环方式批量插入数据，计算执行时间：

//1. 通过Statement + for循环方式批量插入数据，计算执行时间
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        Statement statement = conn.createStatement();
        //获取起始时间
        Long start = System.currentTimeMillis();
        for (int i=0;i<2000;i++){
            String sql = "insert into goods(goodsname)values('name_"+i+"')";
            statement.execute(sql);
        }
        //获取结束时间
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,statement);
        System.out.println("插入总用时为:"+(end-start));

由于此方法使用的是statement，所以每次需要重新生成sql字符串。结果如下:

方法二、通过PreparedStatement + for循环方式批量插入数据，计算执行时间:

    //2. 通过PreparedStatement + for循环方式批量插入数据，计算执行时间
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods(goodsname)value(?)";
        PreparedStatement psmt = conn.prepareStatement(sql);
        Long start = System.currentTimeMillis();
        for (int i=0;i<2000;i++){
            psmt.setObject(1,"name_"+ i);
            psmt.executeUpdate();
        }
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,psmt);
        System.out.println("插入总用时为:"+(end-start));
    }

方法二使用的是PreparedStatement，PreparedStatement是预编译模式，DBServer的编译器编
译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数
直接传入就可以执行。结果如下：

方法三、通过PreparedStatement的addBatch()和executeBatch()进行批量插入数据

1. addBatch()把若干SQL语句装载到一起，然后一次性传送到数据库执行，即是批量处理sql数
据的。
2. executeBatch()会将装载到一起的SQL语句执行。
3. clearBatch()清除缓存

注：MySql默认情况下是不支持批处理的但从5.1.13开始添加了一个rewriteBatchStatement的参数，让MySql支持批处理。在加载url时设置该参数：rewriteBatchedStatements=true

url=jdbc:mysql://localhost:3306/baizhan?useSSL=false&rewriteBatchedStatements=true

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods(goodsname)value(?)";
        PreparedStatement psmt = conn.prepareStatement(sql);
        Long start = System.currentTimeMillis();
        for (int i=0;i<=2000;i++){
            psmt.setObject(1,"name_"+i);
            //缓存sql
            psmt.addBatch();
            //每500条缓存执行一次
            if (i%500==0){
                //批量执行sql
                psmt.executeBatch();
                //清除缓存
                psmt.clearBatch();
            }
        }
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,psmt);
        System.out.println("插入总用时为: "+(end - start));
    }

结果: