← 返回首页
☕

Java JDBC高级特性详解

📂 java ⏱ 4 min 603 words
JavaJDBC连接池RowSet元数据

连接池

HikariCP连接池

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

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

public class HikariCPDemo {
    private static HikariDataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("123456");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);

        dataSource = new HikariDataSource(config);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void close() {
        if (dataSource != null) {
            dataSource.close();
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = getConnection()) {
            System.out.println("从连接池获取连接成功");
            System.out.println("连接URL: " + conn.getMetaData().getURL());
        }

        close();
    }
}

Druid连接池

import com.alibaba.druid.pool.DruidDataSource;

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

public class DruidDemo {
    private static DruidDataSource dataSource;

    static {
        dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(20);
        dataSource.setMaxWait(60000);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void main(String[] args) throws SQLException {
        try (Connection conn = getConnection()) {
            System.out.println("Druid连接池获取连接成功");
        }
    }
}

RowSet

CachedRowSet

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetProvider;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CachedRowSetDemo {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {

            CachedRowSet cachedRowSet = RowSetProvider.newFactory().createCachedRowSet();
            cachedRowSet.populate(rs);

            System.out.println("离线数据集:");
            while (cachedRowSet.next()) {
                System.out.printf("ID: %d, 姓名: %s%n",
                    cachedRowSet.getInt("id"), cachedRowSet.getString("name"));
            }

            cachedRowSet.first();
            cachedRowSet.updateString("name", "修改后的名字");
            cachedRowSet.updateRow();
            System.out.println("更新成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

WebRowSet

import javax.sql.rowset.WebRowSet;
import javax.sql.rowset.RowSetProvider;
import java.io.FileWriter;
import java.sql.*;

public class WebRowSetDemo {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {

            WebRowSet webRowSet = RowSetProvider.newFactory().createWebRowSet();
            webRowSet.populate(rs);

            webRowSet.writeXml(new FileWriter("users.xml"));
            System.out.println("XML写入成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

数据库元数据

DatabaseMetaData

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;

public class DatabaseMetaDataDemo {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456")) {

            DatabaseMetaData meta = conn.getMetaData();

            System.out.println("数据库产品: " + meta.getDatabaseProductName());
            System.out.println("数据库版本: " + meta.getDatabaseProductVersion());
            System.out.println("驱动名称: " + meta.getDriverName());
            System.out.println("驱动版本: " + meta.getDriverVersion());
            System.out.println("最大连接数: " + meta.getMaxConnections());
            System.out.println("支持事务: " + meta.supportsTransactions());

            var tables = meta.getTables(null, null, "%", new String[]{"TABLE"});
            System.out.println("数据库表:");
            while (tables.next()) {
                System.out.println("  - " + tables.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ResultSetMetaData

import java.sql.*;

public class ResultSetMetaDataDemo {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {

            ResultSetMetaData meta = rs.getMetaData();

            System.out.println("列数: " + meta.getColumnCount());
            System.out.println("列信息:");
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                System.out.printf("  列%d: %s (%s)%n",
                    i, meta.getColumnName(i), meta.getColumnTypeName(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

存储过程调用

import java.sql.*;

public class StoredProcedureDemo {
    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456");
             CallableStatement cstmt = conn.prepareCall("{call getUserById(?, ?)}")) {

            cstmt.setInt(1, 1);
            cstmt.registerOutParameter(2, Types.VARCHAR);

            cstmt.execute();

            String name = cstmt.getString(2);
            System.out.println("用户名: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

批量更新优化

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

public class BatchUpdateOptimization {
    public static void main(String[] args) {
        String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb", "root", "123456")) {

            conn.setAutoCommit(false);

            try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                long startTime = System.currentTimeMillis();

                for (int i = 0; i < 10000; i++) {
                    pstmt.setString(1, "用户" + i);
                    pstmt.setString(2, "user" + i + "@example.com");
                    pstmt.setInt(3, 20 + (i % 30));
                    pstmt.addBatch();

                    if (i % 500 == 0) {
                        pstmt.executeBatch();
                    }
                }

                pstmt.executeBatch();
                conn.commit();

                long endTime = System.currentTimeMillis();
                System.out.println("批量插入耗时: " + (endTime - startTime) + "ms");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBC最佳实践总结

  1. 使用连接池提高性能
  2. 合理配置连接池参数
  3. 使用RowSet简化离线数据操作
  4. 利用元数据获取数据库信息
  5. 批量操作时合理设置批次大小

总结

掌握JDBC高级特性能帮助你构建高性能、可维护的数据库应用程序。连接池、RowSet和元数据是企业级应用开发的重要工具。