几种常见数据库的jdbc连接及简单查询

1、Hive

1）driver

org.apache.hive.jdbc.HiveDriver

2）url

jdbc:hive2://<ip>:<端口>/<数据库>

3）查询所有库

show databases

4）查询所有表

show tables

2、MySQL

1）driver

com.mysql.jdbc.Driver
//8.0以上版本
com.mysql.cj.jdbc.Driver

2）url

jdbc:mysql://<ip>:<端口>/<数据库>

3）查询所有表

show tables

4）查询所有字段

select COLUMN_NAME from INFORMATION_SCHEMA.Columns where table_name='<表名>'

3、Oracle

1）driver

oracle.jdbc.driver.OracleDriver

2）url

jdbc:oracle:thin:@<ip>:<端口>:<数据库>

3）查询所有schema

select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA from dual

4）查询所有表

select table_name item_name from all_tables where owner = '<schema>' union all select view_name item_name from all_views where owner = '<schema>' order by item_name

5）查询所有字段

select COLUMN_NAME from sys.all_tab_columns where table_name='<表名>' and owner = '<schema>' ORDER BY COLUMN_ID

4、SQL Server

1）driver

com.microsoft.sqlserver.jdbc.SQLServerDriver

2）url

jdbc:sqlserver://<ip>:<端口>;DatabaseName=<数据库>

3）查询所有表

SELECT name FROM SysObjects Where Type='U'

4）查询所有字段

SELECT Name from syscolumns WHERE id=Object_Id('<表名>') order by colid

5、达梦

1）driver

dm.jdbc.driver.DmDriver

2）url

jdbc:dm://<ip>:<端口>/<数据库>

3）查询所有schema

select distinct object_name TABLE_SCHEMA from all_objects where object_type = 'SCH'

4）查询所有表

select table_name from dba_tables where owner='<schema>'

5）查询所有字段

select column_name from all_tab_columns where owner='<schema>' and Table_Name='<表名>'

6、Kingbase

1）driver

com.kingbase8.Driver

2）url

jdbc:kingbase8://<ip>:<端口>/<数据库>

3）查询所有schema

select nspname from sys_namespace a left join sys_user b on a.nspowner = b.usesysid where b.usename = '<用户名>' and  nspacl is not null

4）查询所有表

SELECT table_name FROM information_schema.tables WHERE table_schema = '<schema>'

5）查询所有字段

SELECT column_name FROM information_schema.columns WHERE table_schema = '<schema>' and table_name='<表名>'

7、Sqlite

1）driver

org.sqlite.JDBC

2）url

jdbc:sqlite:<数据库文件所在>

3）查询所有表

select tbl_name from sqlite_master where type='table'

4）查询所有字段

select * from '<表名>'

附加

//ResultSet中有一个getMetaData方法可以获取读取表的表头以及字段类型信息
ResultSetMetaData rsMetaData = rs.getMetaData();
int columnCount = rsMetaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
   System.out.println(rsMetaData.getColumnName(i)+" "+rsMetaData.getColumnTypeName(i));
}