Retrieve database meta data from JDBC drivers.

JDBCMetaDataTest.scala

import scalikejdbc._

import java.util.Locale.{ ENGLISH => en }

object JDBCMetaDataTest extends Settings {

  GlobalSettings.loggingSQLAndTime = new LoggingSQLAndTimeSettings(
    enabled = true,
    singleLineMode = true,
    logLevel = 'DEBUG
  )

  type RSTraversable = ResultSetTraversable


  def main(args: Array[String]) {
    try {

      DB autoCommit { implicit s =>
        execute(
          """
            create table users (
              id int generated always as identity,
              title varchar(30)  not null,
              primary key(id)
            );
          """, """
            create table users (
              id integer primary key,
              title varchar(30)  not null
            );
               """)

        execute("""
            create table public.meta_members (
              id int generated always as identity,
              title varchar(30)  not null,
              special_day date,
              updated_at timestamp not null,
              primary key(id)
            );
                """, """
            create table public.meta_members (
              id integer primary key,
              title varchar(30)  not null,
              special_day date,
              updated_at timestamp not null
            );
                     """)

        execute("create schema hoge;")


        execute("""
            create table hoge.meta_members (
              id int generated always as identity,
              name varchar(30) default 'foooooooo baaaaaar' not null,
              group_id int,
              description varchar(1000),
              birthday date,
              created_at timestamp not null,
              primary key(id)
            );
                """, """
            create table hoge.meta_members (
              id integer primary key,
              name varchar(30) default 'foooooooo baaaaaar' not null,
              group_id integer,
              description varchar(1000),
              birthday date,
              created_at timestamp not null
            );
                     """)

        execute("""
            create table hoge.meta_groups (
              id int generated always as identity,
              name varchar(30) not null,
              primary key(id)
            );
                """, """
            create table hoge.meta_groups (
              id integer primary key,
              name varchar(30) not null
            );
                     """)
      }

      def tables(schema: String, tableName: String) =
        DB.readOnlyWithConnection { conn =>
          val schema_ = if (schema == null || !url.startsWith("jdbc:h")) schema else schema.toUpperCase(en)
          val tableName_ = if (tableName == null || !url.startsWith("jdbc:h")) tableName else tableName.toUpperCase(en)
          val meta = conn.getMetaData
          new RSTraversable(meta.getTables(null, schema_, tableName_, Array("TABLE", "VIEW")))
            .map { rs =>
            s"${rs.string("TABLE_SCHEM")}.${rs.string("TABLE_NAME")}"
          }
        }

      def columns(schema: String, tableName: String) =
        DB.readOnlyWithConnection { conn =>
          val schema_ = if (schema == null || !url.startsWith("jdbc:h")) schema else schema.toUpperCase(en)
          val tableName_ = if (tableName == null || !url.startsWith("jdbc:h")) tableName else tableName.toUpperCase(en)
          val meta = conn.getMetaData
          new RSTraversable(meta.getColumns(null, schema_, tableName_, "%"))
            .map { rs =>
            s"${rs.string("TABLE_SCHEM")}.${rs.string("TABLE_NAME")}:${rs.string("COLUMN_NAME")}"
          }
        }

      println(url)
      for( schema <- Seq(null, "", "public");
           table <- Seq("users", "meta_members", "meta_groups")
      ) {

        println(s"${schema}.${table}:")
        println(s"  tables: ${tables(schema, table).mkString(", ")}")
        println(s"  column: ${columns(schema, table).mkString(", ")}")
      }

    } finally {
      DB autoCommit { implicit s =>
        execute("drop table if exists users")
        execute("drop table if exists public.meta_members")
        execute("drop table if exists hoge.meta_members")
        execute("drop table if exists hoge.meta_groups")
        execute("drop schema if exists hoge")
      }
    }
  }

  private def execute(sqls: String*)(implicit session: DBSession) {
    for (sql <- sqls) {
      try {
        SQL(sql).execute.apply()
        return
      } catch {
        case e: Exception =>
      }
    }

    throw new RuntimeException("Failed to execute sqls :" + sqls)
  }
}

result.md

JDBC versions

• h2: 1.4.183
• postgresql: 9.3-1102-jdbc41
• hsqldb: 2.3.2

getTables

	h2	hsqldb	postgresql
null.users	PUBLIC.USERS	PUBLIC.USERS	public.users
null.meta_members	HOGE.META_MEMBERS, PUBLIC.META_MEMBERS	PUBLIC.META_MEMBERS, HOGE.META_MEMBERS	hoge.meta_members, public.meta_members
null.meta_groups	HOGE.META_GROUPS	HOGE.META_GROUPS	hoge.meta_groups
.users	PUBLIC.USERS		public.users
.meta_members	PUBLIC.META_MEMBERS		hoge.meta_members, public.meta_members
.meta_groups			hoge.meta_groups
public.users	PUBLIC.USERS	PUBLIC.USERS	public.users
public.meta_members	PUBLIC.META_MEMBERS	PUBLIC.META_MEMBERS	public.meta_members
public.meta_groups

getColumns

	h2	hsqldb	postgresql
null.users	INFORMATION_SCHEMA.USERS:NAME, INFORMATION_SCHEMA.USERS:ADMIN, INFORMATION_SCHEMA.USERS:REMARKS, INFORMATION_SCHEMA.USERS:ID, PUBLIC.USERS:ID, PUBLIC.USERS:TITLE	PUBLIC.USERS:ID, PUBLIC.USERS:TITLE	public.users:id, public.users:title
null.meta_members	HOGE.META_MEMBERS:ID, HOGE.META_MEMBERS:NAME, HOGE.META_MEMBERS:GROUP_ID, HOGE.META_MEMBERS:DESCRIPTION, HOGE.META_MEMBERS:BIRTHDAY, HOGE.META_MEMBERS:CREATED_AT, PUBLIC.META_MEMBERS:ID, PUBLIC.META_MEMBERS:TITLE, PUBLIC.META_MEMBERS:SPECIAL_DAY, PUBLIC.META_MEMBERS:UPDATED_AT	PUBLIC.META_MEMBERS:ID, PUBLIC.META_MEMBERS:TITLE, PUBLIC.META_MEMBERS:SPECIAL_DAY, PUBLIC.META_MEMBERS:UPDATED_AT, HOGE.META_MEMBERS:ID, HOGE.META_MEMBERS:NAME, HOGE.META_MEMBERS:GROUP_ID, HOGE.META_MEMBERS:DESCRIPTION, HOGE.META_MEMBERS:BIRTHDAY, HOGE.META_MEMBERS:CREATED_AT	hoge.meta_members:id, hoge.meta_members:name, hoge.meta_members:group_id, hoge.meta_members:description, hoge.meta_members:birthday, hoge.meta_members:created_at, public.meta_members:id, public.meta_members:title, public.meta_members:special_day, public.meta_members:updated_at
null.meta_groups	HOGE.META_GROUPS:ID, HOGE.META_GROUPS:NAME	HOGE.META_GROUPS:ID, HOGE.META_GROUPS:NAME	hoge.meta_groups:id, hoge.meta_groups:name
.users	PUBLIC.USERS:ID, PUBLIC.USERS:TITLE		public.users:id, public.users:title
.meta_members	PUBLIC.META_MEMBERS:ID, PUBLIC.META_MEMBERS:TITLE, PUBLIC.META_MEMBERS:SPECIAL_DAY, PUBLIC.META_MEMBERS:UPDATED_AT		hoge.meta_members:id, hoge.meta_members:name, hoge.meta_members:group_id, hoge.meta_members:description, hoge.meta_members:birthday, hoge.meta_members:created_at, public.meta_members:id, public.meta_members:title, public.meta_members:special_day, public.meta_members:updated_at
.meta_groups			hoge.meta_groups:id, hoge.meta_groups:name
public.users	PUBLIC.USERS:ID, PUBLIC.USERS:TITLE	PUBLIC.USERS:ID, PUBLIC.USERS:TITLE	public.users:id, public.users:title
public.meta_members	PUBLIC.META_MEMBERS:ID, PUBLIC.META_MEMBERS:TITLE, PUBLIC.META_MEMBERS:SPECIAL_DAY, PUBLIC.META_MEMBERS:UPDATED_AT	PUBLIC.META_MEMBERS:ID, PUBLIC.META_MEMBERS:TITLE, PUBLIC.META_MEMBERS:SPECIAL_DAY, PUBLIC.META_MEMBERS:UPDATED_AT	public.meta_members:id, public.meta_members:title, public.meta_members:special_day, public.meta_members:updated_at
public.meta_groups