一个JDBC小工具

pom.xml

结构

<properties>
    <maven.compiler.source>8</maven.compiler.source>
    <maven.compiler.target>8</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <mysql5>5.1.44</mysql5>
    <mysql8>8.0.30</mysql8>
    <hikari.version>4.0.3</hikari.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>2.0.7</version>
    </dependency>

    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>${hikari.version}</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>${mysql8}</version>
    </dependency>
</dependencies>

basic

DbInfo.java

就是一个普通的bean，记录jdbc的连接信息

@Data
@Builder
public class DbInfo {
    private String jdbcUrl;
    private String username;
    private String password;
    private String driverClassName;
}

AbstractContainerDatabase.java

生成一个连接池，可用于执行sql

public class AbstractContainerDatabase {
    protected ResultSet performQuery(DbInfo info, String sql) throws SQLException {
        DataSource ds = getDataSource(info);
        Statement statement = ds.getConnection().createStatement();
        statement.execute(sql);
        ResultSet resultSet = statement.getResultSet();

        resultSet.next();
        return resultSet;
    }

    protected DataSource getDataSource(DbInfo info) {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(info.getJdbcUrl());
        hikariConfig.setUsername(info.getUsername());
        hikariConfig.setPassword(info.getPassword());
        hikariConfig.setDriverClassName(info.getDriverClassName());
        return new HikariDataSource(hikariConfig);
    }
}

mysql

MysqlInfo.java

由于各种JDBC使用的驱动不一样。所以构建一个Mysqlnfo类，负责生成MySQL所使用的Dbinfo。由于MySQL版本可能不一样，这里提供了不同版本的dbInfo。

public class MysqlInfo {
    private static final String MYSQL_JDBC_URL_TEMPLATE = "jdbc:mysql://%s:%s/%s";
    private static final String MYSQL_V5_DRIVER_CLASS = "com.mysql.jdbc.Driver";
    private static final String MYSQL_V8_DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";

    public static DbInfo getMysql5Info(String host, int port, String username, String password, String db) {
        return DbInfo.builder()
                .jdbcUrl(String.format(MYSQL_JDBC_URL_TEMPLATE, host, port, db))
                .username(username)
                .password(password)
                .driverClassName(MYSQL_V5_DRIVER_CLASS)
                .build();
    }

    public static DbInfo getMysql8Info(String host, int port, String username, String password, String db) {
        return DbInfo.builder()
                .jdbcUrl(String.format(MYSQL_JDBC_URL_TEMPLATE, host, port, db))
                .username(username)
                .password(password)
                .driverClassName(MYSQL_V8_DRIVER_CLASS)
                .build();
    }
}

MysqlTest.java

测试类，这里集成了前面的AbstractContainerDatabase，直接构造一个DbInfo，然后new一个自己，就可以直接调用performQuery来执行SQL了。

public class MysqlTest extends AbstractContainerDatabase {
    private static final Logger LOG = LoggerFactory.getLogger(MysqlTest.class);

    public static void main(String[] args) throws SQLException {
        DbInfo mysql8Info = MysqlInfo.getMysql8Info("host", 3306, "user", "passwd", "tmp");
        MysqlTest mysqlTest = new MysqlTest();
        ResultSet resultSet = mysqlTest.performQuery(mysql8Info, "select * from test");

        // 输出表结构信息
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        LOG.info("Table Structure is :");
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            String columnType = metaData.getColumnTypeName(i);
            int columnLength = metaData.getColumnDisplaySize(i);
            int nullable = metaData.isNullable(i);
            String columnTypeName = metaData.getColumnTypeName(i);
            String columnClassName = metaData.getColumnClassName(i);
            LOG.info("Column Name: {}", columnName);
            LOG.info("Data Type: {}", columnType);
            LOG.info("Column Length: {}", columnLength);
            LOG.info("Column Nullable: {}", nullable);
            LOG.info("Column typeName: {}", columnTypeName);
            LOG.info("Column Class Name: {}", columnClassName);
            LOG.info("------------------------");
        }

        // 处理查询结果
        while (resultSet.next()) {
            // 获取数据并进行处理
            for (int i = 1; i <= columnCount; i++) {
                Object obj = resultSet.getObject(i);
                LOG.info("Column {} : {}", i, obj);
            }
            LOG.info("------------------------");
        }
    }
}