Java之JDBC

认识JDBC

JDBC是用于在Java语言编程中与数据库连接的API.JDBC是一个规范，它提供了一整套接口，允许以一种可移植的访问底层数据库API。使用JDBC驱动程序来访问数据库，并用于存储数据到数据库中

[^图片来源于:掘金(侵删)]:

---

使用JDBC(基于maven)

添加驱动

在pom.xml中添加Mysql驱动的坐标

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.20</version>
</dependency>

连接数据库

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

/**
 * @author aurora
 * @version 1.0
 */
public class ConnectionTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1、加载驱动
        // 把com.mysql.jdbc.Driver这份字节码加载进JVM
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/java";
        // 格式:jdbc:数据库厂商://主机地址(ip):端口号(一般都是3306)/数据库名
        String username = "root";
        String password = "root";

        // 创建连接数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        // 验证是否连接成功
        System.out.println(connection == null ? "连接失败" : "连接成功!");
    }
}

操作数据库查询表

单行查询

import java.sql.*;

public class SelectCountTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1、加载驱动
        // 把com.mysql.jdbc.Driver这份字节码加载进JVM
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/java";
        // 格式:jdbc:数据库厂商://主机地址(ip):端口号(一般都是3306)/数据库名
        String username = "root";
        String password = "root";

        // 创建连接数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        // 定义sql
        String sql = "select count(*) count from javas.student";

        // 创建执行静态sql对象
        Statement stmt = connection.createStatement();
        // 执行sql获取数据库结果集
        ResultSet rs = stmt.executeQuery(sql);

        if (rs.next()) {// 游标向前移动一行 如果新的行有数据返回true 否则false
            int count = rs.getInt("count");
            System.out.println("共有" + count + "行数据");
        }
        // 释放资源(否则会造成资源浪费)
        rs.close();
        stmt.close();
        connection.close();
    }
}

多行查询

import java.sql.*;

public class SelectTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        
        String url = "jdbc:mysql://localhost:3306/java";
        String username = "root";
        String password = "root";


        Connection connection = DriverManager.getConnection(url, username, password);
        String sql = "select count(*) count from javas.student";

        Statement stmt = connection.createStatement();
        // 执行sql获取数据库结果集
        ResultSet rs = stmt.executeQuery(sql);

        // 遍历结果集
        while (rs.next()){
            String name = rs.getString("name");
            int age = rs.getInt("age");
            Object obj = rs.getObject("obj");
            //......
        }
        rs.close();
        stmt.close();
        connection.close();
    }
}

操作数据库进行增删改

import java.sql.*;

public class UpdateTest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/java";
        String username = "root";
        String password = "root";

        Connection connection = DriverManager.getConnection(url, username, password);

        // 使用?作为占位符
        String sql = "update javas.student set name=? where id=?";

        // 由于Statement会存在sql注入问题 我们使用PreparedStatement对sql语句尽进行预编译防止sql注入
        PreparedStatement stmt = connection.prepareStatement(sql);
        // 设置参数
        stmt.setString(1,"java");// 第一个参数
        stmt.setInt(2,1);// 第二个参数

        // 执行sql
        int count = stmt.executeUpdate();
        System.out.println("受影响行数:"+count);// 1

        stmt.close();
        connection.close();
    }
}

我们都知道增删改需要提交事务 否则可能发生数据不一致问题

开启事务

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Objects;

public class UpdateTest {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        String url = "jdbc:mysql://localhost:3306/java";
        String username = "root";
        String password = "root";

        Connection connection = null;
        PreparedStatement stmt = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            // 默认情况下事务是自动提交的 此时我们设置为手动提交
            connection.setAutoCommit(false);
            // 使用?作为占位符
            String sql = "update javas.student set name=? where id=?";

            // 由于Statement会存在sql注入问题 我们使用PreparedStatement对sql语句尽进行预编译防止sql注入
            stmt = connection.prepareStatement(sql);
            // 设置参数
            stmt.setString(1, "java");// 第一个参数
            stmt.setInt(2, 1);// 第二个参数

            // 执行sql
            int count = stmt.executeUpdate();

            // 提交事务
            connection.commit();
            System.out.println("受影响行数:" + count);// 1
        } catch (SQLException throwables) {
            // 出现异常时回滚
            try {
                if (Objects.nonNull(connection)) {
                    connection.rollback();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            if (Objects.nonNull(stmt)) {
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (Objects.nonNull(connection)) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

---

由于上面的代码重复度太高了 所以我们需要把公共的代码提取出来封装成工具类

JBDCUtils

public class JdbcUtil {
    private final static String URL;
    private final static String USERNAME;
    private final static String PASSWORD;

    static {
        InputStream resource = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
        Properties pro = new Properties();
        try {
            pro.load(resource);
            Class.forName(pro.getProperty("driver"));
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
        URL = pro.getProperty("url");
        USERNAME = pro.getProperty("username");
        PASSWORD = pro.getProperty("password");
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    public static void close(Connection conn, PreparedStatement stmt, ResultSet rs) {
        if (Objects.nonNull(rs)) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (Objects.nonNull(stmt)) {
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (Objects.nonNull(conn)) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 增删改公共方法
     *
     * @param conn   数据库连接对象
     * @param sql    sql语句
     * @param params sql参数
     * @return 返回影响行数
     */
    public static int executeUpdate(Connection conn, String sql, Object... params) {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            setParams(stmt, params);
            return stmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
    
    /**
     * 查询行数
     *
     * @param conn   数据库连接对象
     * @param sql    sql语句
     * @param params sql参数
     * @return 返回查询行数
     */
    public static int queryForCount(Connection conn, String sql, Object... params) {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            setParams(stmt, params);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
    
    /**
     * 查询指定列单行的值
     *
     * @param conn   数据库连接对象
     * @param sql    sql语句
     * @param params sql参数
     * @return 返回值
     */
    public static Object queryForColumn(Connection conn, String sql, Object... params) {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            setParams(stmt, params);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getObject(1);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 查询单行记录
     *
     * @param conn   数据库连接对象
     * @param sql    sql语句
     * @param clazz  class类
     * @param params sql参数
     * @param <T>    t
     * @return 返回一个实例
     */
    public static <T> T queryForObject(Connection conn, String sql, Class<T> clazz, Object... params) {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            setParams(stmt, params);
            try (ResultSet rs = stmt.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                if (rs.next()) {
                    return getInstance(clazz, rs, metaData);
                }
            } catch (IllegalAccessException | InstantiationException | NoSuchFieldException e) {
                e.printStackTrace();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 查询多行记录
     *
     * @param conn   数据库连接对象
     * @param sql    sql语句
     * @param clazz  class类
     * @param params sql参数
     * @param <T>    t
     * @return 返回实例集合
     */
    public static <T> List<T> queryForList(Connection conn, String sql, Class<T> clazz, Object... params) {
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            setParams(stmt, params);
            try (ResultSet rs = stmt.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                List<T> list = new ArrayList<>();
                while (rs.next()) {
                    list.add(getInstance(clazz, rs, metaData));
                }
                return list;
            } catch (IllegalAccessException | InstantiationException | NoSuchFieldException e) {
                e.printStackTrace();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    
    /**
     * 设置sql参数
     *
     * @param stmt   预编译sql对象
     * @param params sql参数
     * @throws SQLException sql异常
     */
    private static void setParams(PreparedStatement stmt, Object[] params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
    }
    
     /**
     * 封装实例对象
     *
     * @param clazz    class类
     * @param rs       结果集对象
     * @param metaData 获取有关ResultSet对象中列的类型和属性的信息的对象
     * @param <T>      泛型
     * @return 实例
     * @throws InstantiationException 实例化异常
     * @throws IllegalAccessException 非法访问异常
     * @throws SQLException           sql异常
     * @throws NoSuchFieldException   找不到字段异常
     */
    private static <T> T getInstance(Class<T> clazz, ResultSet rs, ResultSetMetaData metaData) throws InstantiationException, IllegalAccessException, SQLException, NoSuchFieldException {
        T instance = clazz.newInstance();
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            // 获取数据库字段名
            String columnName = metaData.getColumnName(i + 1);
            // 根据字段名得到属性
            Field declaredField = clazz.getDeclaredField(columnName);
            // 抑制访问检测
            declaredField.setAccessible(true);
            // 设置属性值
            declaredField.set(instance, rs.getObject(columnName));
        }
        return instance;
    }


}

数据库配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://116.62.124.99:3306/smbms?useSSL=true&useUnicode=true&characterEncoding=utf-8
username=aurora
password=123456

• 数据库库8.0以上版本驱动路径为com.mysql.cj.jdbc.Driver

---

封装JDBC遇到的问题

private static <T> T getInstance(Class<T> clazz, ResultSet rs, ResultSetMetaData metaData) throws InstantiationException, IllegalAccessException, SQLException, NoSuchFieldException {
        T instance = clazz.newInstance();
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            // 获取数据库字段名
            String columnName = metaData.getColumnName(i + 1);
            // 根据字段名得到属性
            Field declaredField = clazz.getDeclaredField(columnName);
            // 抑制访问检测
            declaredField.setAccessible(true);
            // 设置属性值
            declaredField.set(instance, rs.getObject(columnName));
        }
        return instance;
    }

在用反射封装数据库对应的实体类的时候报了一个IllegaLArgumentException异常 非法参数异常 IDEA提示不能设置Integer类型字段为Long类型

看了下数据库并没有Long类型的字段!

然后利用反射得到了bigint对应的java的数据类型 竟然是Long类型

最后把bigint对应的字段都改成了Long 问题得到解决!

---

Java数据类型对应的mysql数据类型

Java类	数据类型	Mysql
java.lang.Byte	byte	TINYINT
java.lang.Short	short	SMALLINT
java.lang.Integer	integer	INGEGER
java.lang.Long	long	BIGINT
java.lang.Float	float	FLOAT
java.lang.Double	double	DOUBLE
java.lang.BigDecimal	big_decimal	NUMERIC
java.lang.Boolean	boolean	BIT
java.lang.String	string	VARCHAR
java.uitl.Date / java.sql.Date	date	DATE
java.sql.Time	time	TIME
java.sql.Timestamp	timestamp	TIMESTAMP
java.uitl.Calendar	celendar	TIMESTAMP