JavaWeb第3篇JDBC

基本概念

JDBC英文名为：Java Data Base Connectivity(Java数据库连接)，官方解释它是Java编程语言和广泛的数据库之间独立于数据库的连接标准的Java API，根本上说JDBC是一种规范，它提供的接口，一套完整的，允许便捷式访问底层数据库。可以用JAVA来写不同类型的可执行文件：JAVA应用程序、JAVA Applets、Java Servlet、JSP等，不同的可执行文件都能通过JDBC访问数据库，又兼备存储的优势。简单说它就是Java与数据库的连接的桥梁或者插件，用Java代码就能操作数据库的增删改查、存储过程、事务等。

连接数据库

需要提前下载mysql-connector包，这个包提供了jdbc的各种基本操作。首先打开maven网站,选择合适的版本。网站:https://mvnrepository.com/artifact/mysql/mysql-connector-java

需要额外注意的是从mysql-connector-java 6开始，连接时需要指定时区serverTimezone。连接数据库的步骤是通过DriverManager的getConnection获取数据库的连接。通过Statement来执行SQL语句。Statement中的excute()是用来执行任意的SQL语句

选择jar包下载

然后新建一个正常的java项目，把jar包拖到项目的src目录下后右键add as lib添加到项目中去。

示例代码

package com.eldpepar;

import java.sql.*;

public class ConnetMain {
    public static void main(String[] args) {
        //1. 通过DriverManager来获得数据库连接
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT", "root", "123456");
             //2. 创建一个用于执行SQL的Statement对象
             Statement statement = connection.createStatement()) {
             //3. 执行SQL语句，并得到结果集
             String sql = "CREATE TABLE student(id int, name VARCHAR(8), sex VARCHAR(2))";
             boolean query = statement.execute(sql);
        } catch (SQLException e) {
            System.out.println("SQLException");
        }
    }
}

mysql-connector6之前需要手动注册驱动
示例代码

//通过配置文件手动注册驱动
package cn.itcast.jdbc1;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JdbcDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        Properties pro = new Properties();
        //获取src路径下文件的方式，ClassLoader类加载器
        ClassLoader classLoader = JdbcDemo1.class.getClassLoader();
        URL res = classLoader.getResource("jdbc.properties");
        String path = res.getPath();
        System.out.println(path);
        try {

            pro.load(new FileReader(path));
            String url = pro.getProperty("url");
            String user = pro.getProperty("user");
            String password = pro.getProperty("password");

            //获取数据库连接对象
            Connection conn = DriverManager.getConnection(url, user, password);

            //定义sql语句
            String sql = "CREATE TABLE student(id int, name VARCHAR(8), sex VARCHAR(2))";

            //获取sql的对象Statement
            Statement stmt = conn.createStatement();

            //执行sql语句
            boolean query = stmt.execute(sql);

            stmt.close();
            conn.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

基本SQL示例

其中如果需要执行多个添加语句，使用的是addBatch方法。Statement调用的是executeBatch方法。
示例代码

package com.eldpepar;

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

public class DMLDemo {
    public static void main(String[] args) {
        //1. 通过DriverManager来获得数据库连接
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT", "root", "123456");
             //2. 创建一个用于执行SQL的Statement对象
             Statement statement = connection.createStatement()) {

            //定义添加语句
            String sql = "INSERT INTO student(id,name,sex) VALUES(2,'王五','男')";

            //调用添加方法
            int result = statement.executeUpdate(sql);
            if (result > 0) {
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

查询示例

示例代码

package com.eldpepar;

import java.sql.*;

public class DMLDemo {
    public static void main(String[] args) {
        //1. 通过DriverManager来获得数据库连接
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT", "root", "123456");
             //2. 创建一个用于执行SQL的Statement对象
             Statement statement = connection.createStatement()) {

            //定义查询语句
            ResultSet set = statement.executeQuery("SELECT * FROM employee");
            while (set.next()) {
                System.out.print(set.getInt(1) + ","+ set.getString(2) + "," +
                                 set.getString(3) +","+ set.getInt(4) + ","+ set.getInt(5));
                System.out.println();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

映射对象

示例代码（正常方法）

package com.eldpepar;

import java.sql.*;
public class BeanDemo {
    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT", "root", "123456");
             //2. 创建一个用于执行SQL的Statement对象
             Statement statement = connection.createStatement()) {

            //定义查询语句
            ResultSet set = statement.executeQuery("SELECT * FROM student");
            while (set.next()){
                Student student = new Student(set.getInt(1), set.getString(2), set.getString(3));
                student.say();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

class Student {
    Integer sid;
    String name;
    String sex;

    public Student(Integer sid, String name, String sex) {
        this.sid = sid;
        this.name = name;
        this.sex = sex;
    }

    public void say(){
        System.out.println("我叫："+name+"，学号为："+sid+"，我的性别是："+sex);
    }
}

示例代码（反射方法）

package com.eldpepar;

import java.lang.reflect.Constructor;
import java.sql.*;

public class BeanDemo {
    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT", "root", "123456");
             //2. 创建一个用于执行SQL的Statement对象
             Statement statement = connection.createStatement()) {

            //定义查询语句
            ResultSet set = statement.executeQuery("SELECT * FROM student");
            while (set.next()) {
                Student student = Student.convert(set, Student.class);
                if (student != null) student.say();
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

class Student {
    Integer sid;
    String name;
    String sex;

    public Student(Integer sid, String name, String sex) {
        this.sid = sid;
        this.name = name;
        this.sex = sex;
    }

    public void say() {
        System.out.println("我叫：" + name + "，学号为：" + sid + "，我的性别是：" + sex);
    }

    static <T> T convert(ResultSet set, Class<T> clazz) {
        try {
            Constructor<T> constructor = clazz.getConstructor(clazz.getConstructors()[0].getParameterTypes());   //默认获取第一个构造方法
            Class<?>[] param = constructor.getParameterTypes();  //获取参数列表
            Object[] object = new Object[param.length];  //存放参数
            for (int i = 0; i < param.length; i++) {   //是从1开始的
                object[i] = set.getObject(i + 1);
                if (object[i].getClass() != param[i])
                    throw new SQLException("错误的类型转换：" + object[i].getClass() + " -> " + param[i]);
            }
            return constructor.newInstance(object);
        } catch (ReflectiveOperationException | SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}

PreparedStatement

使用正常的拼接SQL的方法，如果用户输入的是or 1=1这类必为真的SQL语句时，就存在SQL注入的风险，为了解决这类的问题，引入了PreparedStatement来处理这类的问题。PreparedStatement可以使用‘?’来替换相应的参数，能够有效的避免SQL注入

示例代码

package com.eldpepar;

import java.sql.*;

public class PsDemo {
    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=GMT", "root", "123456");
             PreparedStatement statement = connection.prepareStatement("INSERT INTO student(id,name,sex) VALUES(?,?,?)")) {

            statement.setInt(1,3);
            statement.setString(2, "赵云");
            statement.setString(3, "男");
            System.out.println(statement);    //打印查看一下最终执行的
            int res = statement.executeUpdate();
            if (res > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

连接池

在执行JDBC的增删改查的操作时，如果每一次操作都来一次打开连接，操作，关闭连接，那么创建和销毁JDBC连接的开销就太大了。为了避免频繁地创建和销毁JDBC连接，我们可以通过连接池（Connection Pool）复用已经创建好的连接。这里以德鲁伊连接池为例。使用的版本是druid-1.2.4和mysql-connector-java-8.0.20

配置文件(druid.properties)

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/user?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false
username=root
password=123456
initiolSize=5
maxActive=10
maxWait=3000

工具类

package com.eldpepar;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DruidUtils {
    //定义数据源
    private static DataSource ds;
    static {
        //属性，用于加载配置文件
        Properties pro = new Properties();
        try {
            //加载配置文件
            pro.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));

            //创建数据源
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return  ds.getConnection();
    }

    //释放资源
    public static void close(Statement stmt, Connection conn) {
        close(null, stmt, conn);
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //获取连接池
    public static DataSource getDataSource() {
        return ds;
    }
}   

测试类

package com.eldpepar;

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

public class DruidDemo {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement psmt = null;
        try {
            //1.获取连接
            conn = DruidUtils.getConnection();
            //2.定义sql
            String sql = "insert into student values(?,?,?)";

            //3.获取psmt对象
            psmt = conn.prepareStatement(sql);

            //4.给?赋值
            psmt.setInt(1,4);
            psmt.setString(2,"张三");
            psmt.setString(3,"男");
            int count = psmt.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}