Skip to content

Instantly share code, notes, and snippets.

@namutaka

namutaka/JDBCMetaDataTest.scala

Created December 15, 2014 11:13
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save namutaka/4d91fae9364673a48e39 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save namutaka/4d91fae9364673a48e39 to your computer and use it in GitHub Desktop.
Download ZIP
Retrieve database meta data from JDBC drivers.
Raw
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)
}
}
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment