java jdbc元数据_JDBC元数据详解

JDBC到了版本3以后加入元数据的功能。JDBC元数据就可以很方便的获取数据库相关的信息。

DatabaseMetaData案例：

package com.what21.jdbc.demo07;

import java.sql.Connection;

import java.sql.DatabaseMetaData;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JDBCDemo {

/**

* @return

*/

public static Connection createConnection(){

Connection connection = null;

try {

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/demo","root","123124");

} catch (Exception e) {

e.printStackTrace();

}

return connection;

}

/**

* 获取最基本的数据库信息

*

* @param connection

* @throws SQLException

*/

public static void showDatabase(Connection connection) throws SQLException {

//获取数据库连接的元数据

DatabaseMetaData meta = connection.getMetaData();

//查询连接的所有Table(如果需要查询视图等,可以在最后的数组中添加VIEW...,依此类推)

ResultSet rsTable = meta.getTables(null, null, null, new String[] { "TABLE" });

//获取到的数据是以ResultSet形式返回

while (rsTable.next()) {

//第一列是Database名称

System.out.print(rsTable.getString(1) + " | ");

//第二列是用户名称(有的表可能没有对应的用户)

System.out.print(rsTable.getString(2) + " | ");

//第三列就是表名称

System.out.println(rsTable.getString(3));

}

}

/**

* 获取一个表的所有主键列的名称列表

*

* @param connection

* @throws SQLException

*/

public static void primaryKeys(Connection connection) throws SQLException {

DatabaseMetaData meta = connection.getMetaData();

//获取制定表的主键列信息

ResultSet rsKey = meta.getPrimaryKeys(null, null, "users");

while (rsKey.next()){

System.out.println(rsKey.getString(4));

}

close(rsKey);

}

/**

* 获取一个表中是外键列的名称列表

*

* @param connection

* @throws SQLException

*/

public static void importedKeys(Connection connection) throws SQLException {

DatabaseMetaData meta = connection.getMetaData();

ResultSet rsKey = meta.getImportedKeys(null, null, "users");

while (rsKey.next()){

System.out.println(rsKey.getString(8));

}

close(rsKey);

}

/**

* 获取一个表中是其他表的外键的列的名称列表

*

* @param connection

* @throws SQLException

*/

public static void exportedKeys(Connection connection) throws SQLException {

DatabaseMetaData meta = connection.getMetaData();

ResultSet rsKey = meta.getExportedKeys(null, null, "users");

while (rsKey.next()){

System.out.println(rsKey.getString(4));

}

close(rsKey);

}

/**

* @param connection

*/

public static void close(Connection connection){

if(connection!=null){

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

connection=null;

}

}

/**

* @param rs

*/

public static void close(ResultSet rs){

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

rs=null;

}

}

/**

* @param args

*/

public static void main(String[] args) {

Connection connection = createConnection();

try {

// 获取最基本的数据库信息

showDatabase(connection);

// 获取一个表的所有主键列的名称列表

primaryKeys(connection);

// 获取一个表中是外键列的名称列表

importedKeys(connection);

// 获取一个表中是其他表的外键的列的名称列表

exportedKeys(connection);

} catch (SQLException e) {

e.printStackTrace();

}

close(connection);

}

}

ParameterMetaData案例：

package com.what21.jdbc.demo07;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ParameterMetaData;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JDBCDemo2 {

/**

* @return

*/

public static Connection createConnection(){

Connection connection = null;

try {

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/demo","root","123124");

} catch (Exception e) {

e.printStackTrace();

}

return connection;

}

/**

* @param connection

* @throws SQLException

*/

public static void insert(Connection connection)throws SQLException {

String sql = "insert into users(name,email,phone,mobile) value(?,?,?,?)";

PreparedStatement pstat = connection.prepareStatement(sql);

pstat.setString(1, "_name");

pstat.setString(2, "_email");

pstat.setString(3, "_phone");

pstat.setString(4, "_mobile");

ParameterMetaData meta= pstat.getParameterMetaData();

System.out.println("参数个数: " + meta.getParameterCount());

// 获取预编译语句的参数个数

int cols = meta.getParameterCount();

for (int i = 1; i <= cols; i++) {

System.out.print("java数据类型: " + meta.getParameterClassName(i) + " | ");

System.out.print("sql数据类型: " + meta.getParameterTypeName(i));

System.out.println();

}

pstat.execute();

close(pstat);

}

/**

* @param connection

*/

public static void close(Connection connection){

if(connection!=null){

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

connection=null;

}

}

/**

* @param stat

*/

public static void close(Statement stat){

if(stat!=null){

try {

stat.close();

} catch (SQLException e) {

e.printStackTrace();

}

stat=null;

}

}

/**

* @param rs

*/

public static void close(ResultSet rs){

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

rs=null;

}

}

/**

* @param args

*/

public static void main(String[] args) {

Connection connection = createConnection();

try {

insert(connection);

} catch (SQLException e) {

e.printStackTrace();

}

close(connection);

}

}

ResultSetMetaData案例：

package com.what21.jdbc.demo07;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

public class JDBCDemo3 {

/**

* @return

*/

public static Connection createConnection(){

Connection connection = null;

try {

Class.forName("com.mysql.jdbc.Driver");

connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/demo","root","123124");

} catch (Exception e) {

e.printStackTrace();

}

return connection;

}

/**

* @param connection

* @throws SQLException

*/

public static void select(Connection connection)throws SQLException {

Statement stat = connection.createStatement();

String sql = "select * from users";

ResultSet rs = stat.executeQuery(sql);

ResultSetMetaData meta = rs.getMetaData();

int cols = meta.getColumnCount(); // 获取查询结果的列数

for (int i = 1; i <= cols; i++) {

System.out.print("名称:" + meta.getColumnName(i) + " | ");

System.out.print("java类型名称:" + meta.getColumnClassName(i) + " | ");

System.out.print("sql类型名称:" + meta.getColumnTypeName(i) + " | ");

System.out.println("显示需要的最大长度" +meta.getColumnDisplaySize(i));

}

while(rs.next()){

System.out.print("id=" + rs.getInt(1));

System.out.print(",name=" + rs.getString(2));

System.out.print(",email=" + rs.getString(3));

System.out.print(",phone=" + rs.getString(4));

System.out.println(",mobile=" + rs.getString(5));

}

close(rs);

close(stat);

}

/**

* @param connection

*/

public static void close(Connection connection){

if(connection!=null){

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

connection=null;

}

}

/**

* @param stat

*/

public static void close(Statement stat){

if(stat!=null){

try {

stat.close();

} catch (SQLException e) {

e.printStackTrace();

}

stat=null;

}

}

/**

* @param rs

*/

public static void close(ResultSet rs){

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

rs=null;

}

}

/**

* @param args

*/

public static void main(String[] args) {

Connection connection = createConnection();

try {

select(connection);

} catch (SQLException e) {

e.printStackTrace();

}

close(connection);

}

}