1、基本概念与步骤

1.1 初始化JDBC

jdbc定义的是一套接口，每种数据库类型都有各自的实现，我们调用的都是接口，编译器可能并不清楚该采用哪种实例，所以需要先初始化。

Class.forName("com.mysql.jdbc.Driver");

1.2 数据库连接对象(Connection类)

String connectUrl = "jdbc:mysql://example.domain.com:3306/db3?useSSL=false";
Connection conn = DriverManager.getConnection(connectUrl, "user", "password");

1.3 执行SQL(Statement类)

两个重要的方法：

• executeUpdate 执行DML语句和DDL语句，即数据或库表的增删改操作；
• executeQuery 执行DQL语句，即数据库的查询操作；

String sql = "select * from xxx";
Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery(sql);
while (resultSet.next()) {
    String uname = resultSet.getString("column1");
    int balance = resultSet.getInt("column2");
    System.out.println(uname + "--" + balance);
}

1.4 释放资源

stmt.close();
conn.close();

2 一个完整的示例程序

package com.yusian.update;

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

public class UpdateDemo {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        try {
            // 1、注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://example.domain.com:3306/db?useSSL=false";
            // 2、连接数据库
            conn = DriverManager.getConnection(url, "username", "password");
            // 3、获取执行SQL对象
            stmt = conn.createStatement();
            // 4、执行SQL语句并获取执行结果
            final int count = stmt.executeUpdate("update account set balance = 1500 where id = 3");
            if (count == 0) {
                System.out.println("执行失败！");
            } else {
                System.out.println("执行成功！" + count);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 5、释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

3 封装优化

3.1 工具类

package com.yusian.utils;

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

public class Utils {

    /**
     * 静态代码块
     */
    static {
        // 通过类加载器获取文件的绝对路径
        ClassLoader loader = Utils.class.getClassLoader();
        final URL resource = loader.getResource("jdbc.properties");
        final String path = resource.getPath();
        try {
            // 从配置文件中加载数据库相关配置，并赋值给当前类的静态变量方便调用
            Properties pros = new Properties();
            pros.load(new FileReader(path));
            url = pros.getProperty("url");
            user = pros.getProperty("user");
            pass = pros.getProperty("pass");
            driver = pros.getProperty("driver");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 从配置文件加载的相关配置数据
     */
    private static String url;
    private static String user;
    private static String pass;
    private static String driver;

    /**
     * 获取数据库连接
     * @return 数据库连接对象
     * @throws SQLException
     */
    static Connection getConnection() throws SQLException{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();;
        }
        return DriverManager.getConnection(url, user, pass);
    }

    /**
     * 释放查询类操作的相关资源
     * @param conn 连接对象
     * @param stmt SQL执行对象
     * @param retSet 查询结果对象
     */
    public static void releaseQureyObject(Connection conn, Statement stmt, ResultSet retSet) {
        releaseCloseableObject(retSet);
        releaseUpdateObject(conn, stmt);
    }

    /**
     * 释放操作类的相关资源
     * @param conn 连接对象
     * @param stmt SQL执行对象
     */
    public static void releaseUpdateObject(Connection conn, Statement stmt) {
        releaseCloseableObject(stmt);
        releaseCloseableObject(conn);
    }

    /**
     * 释放单个资源的封装，因为都有close方法，可以接收类型可借用AutoCloseable接口类
     * @param obj 释放对象
     */
    private static void releaseCloseableObject(AutoCloseable obj) {
        if (obj == null) return;
        try {
            obj.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3.2 实现

package com.yusian.utils;

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

public class UtilsDemo {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet retSet = null;
        try {
            conn = Utils.getConnection();   // 数据库连接
            stmt = conn.createStatement();  // Sql执行对象
            retSet = stmt.executeQuery("select * from account"); // 执行查询语句
            while (retSet.next()) {
                int id = retSet.getInt("id");
                String uname = retSet.getString("uname");
                double balance = retSet.getDouble("balance");
                System.out.println(id + "---" + uname + "---" + balance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            Utils.releaseQureyObject(conn, stmt, retSet);
        }
    }
}

3.3 配置文件

url=jdbc:mysql://example.domain.com:3306/db?useSSL=false
user=username
pass=password
driver=com.mysql.jdbc.Driver