Sql查询主键名称以及类型

﹏ヽ暗。殇╰゛Y 2022-05-22 10:57 663阅读 0赞

### JDBC查询表主键名称 ###

**适用oracle,mysql**

public static void getTablePk(Dbconfig dbconfig){
            Connection connection = getConnection(dbconfig);
            try {
                ResultSet rs = connection.getMetaData().getPrimaryKeys(connection.getCatalog().toUpperCase(),
                        connection.getMetaData().getUserName().toUpperCase(), dbconfig.getTableName().toUpperCase());
                while (rs.next()) {
                    System.out.println(rs.getString("COLUMN_NAME"));
                }
            }catch (SQLException e){
                e.printStackTrace();
            }finally {
                if (connection != null) {
                    closeConnection(connection);
                    connection = null;
                }
            }
        }
    
    public static void main(String[] args) {
            Dbconfig dbconfig = new Dbconfig();
            dbconfig.setDburl("10.0.6.230");
            dbconfig.setUsername("root");
            dbconfig.setPassword("Uhope123");
            dbconfig.setDbtypeId("com.mysql.jdbc.Driver");
            dbconfig.setConnectionName("text");
            dbconfig.setPort(3306);
            dbconfig.setDbname("uip");
            dbconfig.setTableName("sm_menu");
            getTablePk(dbconfig);
        }
    
    public static Connection getConnection(Dbconfig dbConfig) {
            String usingPassword = "using password";
            String unknownDatabase = "Unknown database";
            String linkFailure = "link failure";
            try {
                Class.forName(dbConfig.getDbtypeId());
                String url = "jdbc:mysql://" + dbConfig.getDburl() + ":" + dbConfig.getPort() + "/" + dbConfig.getDbname() + "?connectTimeout=5000&socketTimeout=60000&useUnicode=true&characterEncoding=UTF-8&useSSL=false";
                logger.info("正在连接[" + url + "]...");
                Connection connection = DriverManager.getConnection(url, dbConfig.getUsername(), dbConfig.getPassword());
                return connection;
    
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                logger.error("MYSQL" + "数据库连接失败[" + e.getMessage() + "]");
                //自定义异常
                throw new SqlErrorException("连接失败,请检查数据库驱动类型是否正确！");
            } catch (SQLException e) {
                if (e.getMessage().contains(usingPassword)) {
                    throw new SqlErrorException();
                }
                if (e.getMessage().contains(unknownDatabase)) {
                    throw new SqlErrorException("找不到数据库名" + e.getMessage().substring((e.getMessage().indexOf("'")) + 1, e.getMessage().lastIndexOf("'")));
                }
                if (e.getMessage().contains(linkFailure)) {
                    throw new SqlErrorException("数据库连接失败，请检查配置是否正确！");
                }
                logger.error("MYSQL" + "数据库连接失败[" + e.getMessage() + "]");
                return null;
            }
        }
    
    public static void closeConnection(Connection connection) {
            try {
                connection.close();
                if (connection != null) {
                    connection = null;
                }
                logger.info("数据库关闭连接");
            } catch (SQLException e) {
                e.printStackTrace();
                logger.info("关闭出错[" + e.getMessage() + "]");
            }
        }

### 获取通过sql查询数据库的表，字段，主键，自增，字段类型等信息 ###

**1. 查询所有表，以及表的备注信息**  
Oracle数据库

select t.table_name tableName, cmts.comments descr from user_tables t left join user_tab_comments cmts on t.table_name = cmts.table_name where t.table_name like '%'

SQL Server

Select d.Name tableName, isnull(e.value,'') descr From SysObjects d left join sys.extended_properties e on d.id = e.major_id and e.minor_id=0 Where d.XType='U' and d.name like ? order By d.Name

MySQL

SELECT table_name tableName, TABLE_COMMENT descr FROM information_schema.tables WHERE table_schema = ? and table_name like ? ORDER BY table_name DESC

**2.查询指定表的 字段名称，是否主键，是否自增，数据类型，字段注释信息。**  
Oracle

select c.column_name columnName, case when cu.column_name is null then 'false' else 'true' end as pkColumn,'false' as autoAdd , c.data_type jdbcType , cmts.comments descr from user_tab_columns c left join user_constraints au on c.table_name = au.table_name and au.constraint_type = 'P' left join user_cons_columns cu on cu.constraint_name = au.constraint_name and c.column_name = cu.column_name left join user_col_comments cmts on cmts.table_name = c.table_name and cmts.column_name = c.column_name where c.table_name = UPPER(?)

SQL Server

SELECT t1.name columnName,case when t4.id is null then 'false' else 'true' end as pkColumn, case when COLUMNPROPERTY( t1.id,t1.name,'IsIdentity') = 1 then 'true' else 'false' end as autoAdd ,t5.name jdbcType ,isnull(t6.value,'') descr FROM SYSCOLUMNS t1 left join SYSOBJECTS t2 on t2.parent_obj = t1.id AND t2.xtype = 'PK' left join SYSINDEXES t3 on t3.id = t1.id and t2.name = t3.name left join SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indid left join systypes t5 on t1.xtype=t5.xtype left join sys.extended_properties t6 on t1.id=t6.major_id and t1.colid=t6.minor_id

MySQL

SELECT a.column_Name AS columnName ,CASE WHEN p.column_Name IS NULL THEN 'false' ELSE 'true' END AS pkColumn ,CASE WHEN a.extra = 'auto_increment' THEN 'true' ELSE 'false' END AS autoAdd,a.data_type jdbcType, column_COMMENT descr FROM information_schema.COLUMNS a LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS p ON a.table_schema = p.table_schema AND a.table_name = p.TABLE_NAME AND a.COLUMN_NAME = p.COLUMN_NAME AND p.constraint_name='PRIMARY' WHERE a.table_schema = ? AND a.table_name = ? ORDER BY a.ordinal_position

单单获取ID这一栏的语句，多添加一个判断条件