几种常见数据库的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));
}