JDBC（一）

1.   JDBC介绍

JDBC(Java DataBase Connectivity)，即Java数据库的连接。JDBC是一种用于执行SQL语句(DML,DDL,DQL)的Java API，可以为多种关系数据库（oracle,mysql,sqlserver）提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

Java 通过JDBC访问数据库，数据库厂家有很多，如Mysql、Oracle、SqlServer等，而JDBC提供了操作这些数据库的统一的接口、基准，让各大厂商去实现这组规范。各大数据库厂商只需要提供对应的JDBC的jar包，使java代码通过不同厂商实现的jar包访问对应的数据库。

2.   JDBC完成CRUD规范步骤

2.1.规范DAO层的实现

DAO层：涉及到开发中的三层架构与MVC层级结构。DAO层叫数据访问层，全称data access object，属于一种比较底层、比较基础的操作，具体作用于某个表、某个实体的增删改查。

DAO层的创建规范：

这里包括界面层传入数据封装成为的域对象——Domain类对象。

2.2. DAO层具体实现

具体代码如下：

DAO层：

接口：

public interface IStudentDao {

    public void insert(Student student);

    public void update(Student student);

    public void delete(Integer id);

    public Student queryOne(Integer id);

    public List<Student> queryAll();
}

实现类Impl：

package cn.yif.dao.impl;

import cn.yif.domain.Student;
import cn.yif.dao.IStudentDao;
import cn.yif.utils.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Administrator
 * @create 2019-09-13-20:33
 */
public class StudentDaoImpl implements IStudentDao {
    @Override
    public void insert(Student student) {
        Connection conn = null;
        Statement statement = null;
        try {
            //①加载（注册）驱动，这部分硬编码可以写也可以不写，jdbc4.0版本自带了
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③获取语句对象：用来执行SQL语句
            statement = conn.createStatement();
            //④执行SQL语句
            String insertSql = "insert into jdbcdemo01 (name, age) values (‘"+student.getName()+"‘, "+student.getAge()+")";
            statement.execute(insertSql);
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(null, statement, conn);
        }
    }

    @Override
    public void update(Student student) {
        Connection conn = null;
        Statement statement = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③获取语句对象：用来执行SQL语句
            statement = conn.createStatement();
            //④执行SQL语句
            String updateSql = "update jdbcdemo01 set name = ‘"+student.getName()+"‘, age = "+student.getAge()+" where id = "+student.getId()+"";
            statement.execute(updateSql);
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(null, statement, conn);
        }
    }

    @Override
    public void delete(Integer id) {
        Connection conn = null;
        Statement statement = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③获取语句对象：用来执行SQL语句
            statement = conn.createStatement();
            //④执行SQL语句
            String deleteSql = "delete from jdbcdemo01 where id = "+ id +"";
            statement.execute(deleteSql);
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(null, statement, conn);
        }
    }

    @Override
    public Student queryOne(Integer id) {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Student student = null;
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③获取语句对象：用来执行SQL语句
            statement = conn.createStatement();
            //④执行SQL语句
            String queryOneSql = "select * from jdbcdemo01 where id = "+ id +"";
            resultSet = statement.executeQuery(queryOneSql);
            if(resultSet.next())
            {
                student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
            }
        }catch (Exception ex){

        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, statement, conn);
        }
        return student;
    }

    @Override
    public List<Student> queryAll() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        Student student = null;
        List<Student> list = new ArrayList<Student>();
        try {
            //①加载（注册）驱动
            //Class.forName("com.mysql.jdbc.Driver");
            //②获取连接
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctest01", "root", "admin");
            conn = JDBCUtil.getInstance().getConnection();
            //③获取语句对象：用来执行SQL语句
            statement = conn.createStatement();
            //④执行SQL语句
            String queryAllSql = "select * from jdbcdemo01";
            resultSet = statement.executeQuery(queryAllSql);
            while (resultSet.next())
            {
                student = new Student();
                student.setId(resultSet.getInt("id"));
                student.setName(resultSet.getString("name"));
                student.setAge(resultSet.getInt("age"));
                list.add(student);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally {
            //⑤释放资源
            JDBCUtil.getInstance().close(resultSet, statement, conn);
        }
        return list;
    }
}

Domain层：

package cn.yif.domain;

/**
 * @author Administrator
 * @create 2019-09-13-20:23
 */
public class Student {
    //ID表示编号
    private Integer id;

    //name表示名字
    private String name;

    //age表示年龄
    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Student() {
    }

    public Student(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name=‘" + name + ‘\‘‘ +
                ", age=" + age +
                ‘}‘;
    }
}

工具类JDBCUtil实现：

package cn.yif.utils;

import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author Administrator
 * @create 2019-09-13-21:21
 */
public class JDBCUtil {
    //使用静态static类构造单例模式
    private JDBCUtil(){ }
    //在静态代码块中创建instance的实例，这里使用static静态变量来保存
    private static JDBCUtil instance = null;
    //每次都要加载驱动，这里定义一个Properties，把资源文件里面的内容读到Properties里面
    private static Properties properties = null;
    //JDBCUtil类加载的时候，就加载注册驱动
    static {
        properties = new Properties();
        try {
            //当前线程类加载器加载获取properties文件
            properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties"));
            try {
                Class.forName(properties.getProperty("driverClassName"));
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        instance = new JDBCUtil();
    }

    public static JDBCUtil getInstance(){
        return instance;
    }

    //抽取获取连接Connection的方法
    public Connection getConnection() throws SQLException {
       return DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
    }

    //抽取JDBC关闭资源的close方法
    public void close(ResultSet resultSet, Statement statement, Connection conn){
        try {
            if(resultSet != null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(statement != null){
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if(conn != null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBC资源配置文件：

driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbctest01
username = root
password = admin

或GitHub：https://github.com/devyf/JavaWorkSpace/tree/master/JDBC_0913_01