Hibernate连接postgresql 数据库慢 PgDatabaseMetaData 问题的解决

问题描述

2024-07-30 18:26:43.949 INFO 4525 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect
在撰写本公司项目的时候，博主发现了一个问题。此项目的数据库选用的是基于 PostgreSQL ，其中包含几千张数据库表。然而，在使用 Hibernate 启动并连接 PostgreSQL 时，速度极其缓慢，竟然需要约半小时之久。于是便一步步追踪代码，最终跟踪到 org/hibernate/engine/jdbc/spi/TypeInfo.class:48：

进入PgDatabaseMetaData发现

 /*
 * Copyright (c) 2004, PostgreSQL Global Development Group
 * See the LICENSE file in the project root for more information.
 */
 
package org.postgresql.jdbc;
 
import org.postgresql.core.BaseStatement;
import org.postgresql.core.Field;
import org.postgresql.core.Oid;
import org.postgresql.core.ServerVersion;
import org.postgresql.util.GT;
import org.postgresql.util.JdbcBlackHole;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
 
import java.sql.Array;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.RowIdLifetime;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
 
public class PgDatabaseMetaData implements DatabaseMetaData {
   
   
 
  public PgDatabaseMetaData(PgConnection conn) {
   
   
    this.connection = conn;
  }
 
  private static final String keywords = "abort,acl,add,aggregate,append,archive,"
      + "arch_store,backward,binary,boolean,change,cluster,"
      + "copy,database,delimiter,delimiters,do,extend,"
      + "explain,forward,heavy,index,inherits,isnull,"
      + "light,listen,load,merge,nothing,notify,"
      + "notnull,oids,purge,rename,replace,retrieve,"
      + "returns,rule,recipe,setof,stdin,stdout,store,"
      + "vacuum,verbose,version";
 
  protected final PgConnection connection; // The connection association
 
  private int NAMEDATALEN = 0; // length for name datatype
  private int INDEX_MAX_KEYS = 0; // maximum number of keys in an index.
 
  public ResultSet getTypeInfo() throws SQLException {
   
   
 
    Field f[] = new Field[18];
    List<byte[][]> v = new ArrayList<byte[][]>(); // The new ResultSet tuple stuff
 
    f[0] = new Field("TYPE_NAME", Oid.VARCHAR);
    f[1] = new Field("DATA_TYPE", Oid.INT2);
    f[2] = new Field("PRECISION", Oid.INT4);
    f[3] = new Field("LITERAL_PREFIX", Oid.VARCHAR);
    f[4] = new Field("LITERAL_SUFFIX", Oid.VARCHAR);
    f[5] = new Field("CREATE_PARAMS", Oid.VARCHAR);
    f[6] = new Field("NULLABLE", Oid.INT2);
    f[7] = new Field("CASE_SENSITIVE", Oid.BOOL);
    f[8] = new Field("SEARCHABLE", Oid.INT2);
    f[9] = new Field("UNSIGNED_ATTRIBUTE", Oid.BOOL);
    f[10] = new Field("FIXED_PREC_SCALE", Oid.BOOL);
    f[11] = new Field("AUTO_INCREMENT", Oid.BOOL);
    f[12] = new Field("LOCAL_TYPE_NAME", Oid.VARCHAR);
    f[13] = new Field("MINIMUM_SCALE", Oid.INT2);
    f[14] = new Field("MAXIMUM_SCALE", Oid.INT2);
    f[15] = new Field("SQL_DATA_TYPE", Oid.INT4);
    f[16] = new Field("SQL_DATETIME_SUB", Oid.INT4);
    f[17] = new Field("NUM_PREC_RADIX", Oid.INT4);
 
    String sql;
    sql = "SELECT t.typname,t.oid FROM pg_catalog.pg_type t"
          + " JOIN pg_catalog.pg_namespace n ON (t.typnamespace = n.oid) "
          + " WHERE n.nspname  != 'pg_toast'";
 
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery(sql);
 
    byte bZero[] = connection.encodeString("0");
    byte b10[] = connection.encodeString("10");
    byte bf[] = connection.encodeString("f");
    byte bt[] = connection.encodeString("t");
    byte bliteral[] = connection.encodeString("'");
    byte bNullable[] =
        connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeNullable));
    byte bSearchable[] =
        connection.encodeString(Integer.toString(java.sql.DatabaseMetaData.typeSearchable));
 
    while (rs.next()) {
   
   
      byte[][] tuple = new byte[18][];
      String typname = rs.getString(1);
      int typeOid = (int) rs.getLong(2);
 
      tuple[0] = connection.encodeString(typname);
      int sqlType = connection.getTypeInfo().getSQLType(typname);
      //主要是上面的哪句话 每次都去查询
      tuple[1]