操作数据库元数据

元数据

元数据就是关于数据的数据(属性)

主要是描述数据属性（property）的信息

1）数据库元数据（DataBaseMetaData）

package metadata;

import java.sql.*;

public class MetaDataDemo {
    public static void databaseMetaData() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?characterEncoding=utf-8","root","");
            //获取数据库元信息
            DatabaseMetaData dbMetaData = conn.getMetaData();
            //数据库名字(Mysql)
            String dbName = dbMetaData.getDatabaseProductName();
            System.out.println(dbName);
            String dbVersion = dbMetaData.getDatabaseProductVersion();
            System.out.println(dbVersion);
            String driverName = dbMetaData.getDriverName();
            String url = dbMetaData.getURL();
            String userName = dbMetaData.getUserName();
            //数据库驱动名
            System.out.println(driverName);
            System.out.println(url);
            System.out.println(userName);

            ResultSet rs = dbMetaData.getPrimaryKeys(null,dbName,"Account");
            while (rs.next()){
                Object tableType = rs.getObject(1);
                System.out.println(tableType);
                Object tableMode = rs.getObject(2);
                System.out.println(tableMode);
                Object tableName = rs.getObject(3);
                System.out.println(tableName);
                Object columnName = rs.getObject(4);
                System.out.println(columnName);
                Object s = rs.getObject(5);
                System.out.println(s);
                Object name = rs.getObject(6);
                System.out.println(name);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        databaseMetaData();
    }
}

2)ParameterMetaData

这个类如果用oracle6的驱动包会有bug(不支持的特性)

mysql报错：Parameter metadata not available for the given statement

要加上url:generateSimpleParameterMetadata=true

mysql驱动默认generateSimpleParameterMetadata=false只有设置为true metadata类型会将每一个参数反射为Varchar类型

package metadata;

import java.sql.*;

public class MetaDataDemo2 {
    public static void parameterMetaData() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?characterEncoding=utf-8&generateSimpleParameterMetadata=true", "root", "");
            System.out.println("**");
            String sql = "SELECT * FROM student WHERE id=? and name=?";
            System.out.println("===");
            PreparedStatement psmt = conn.prepareStatement(sql);
            //通过PreparedStatement获取参数元数据
            ParameterMetaData parameterMetaData = psmt.getParameterMetaData();
            int count = parameterMetaData.getParameterCount();
            System.out.println("参数个数：" + count);
            for (int i = 1; i <= count; i++) {
                String type = parameterMetaData.getParameterTypeName(i);
                System.out.println(type);
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        parameterMetaData();
    }
}

3)ResultSetMetaData

package metadata;

import java.sql.*;

public class MetaDataDemo3 {
    public static void parameterMetaData() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/user?characterEncoding=utf-8", "root", "");
            System.out.println("**");
            String sql = "SELECT * FROM student ";
            System.out.println("===");
            PreparedStatement psmt = conn.prepareStatement(sql);
            ResultSet rs = psmt.executeQuery();
            //通过ResultSet获取元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            System.out.println(count);
            while (rs.next()) {
                for (int i = 1; i <= count; i++) {
                    String name = rsmd.getColumnName(i);
                    String type = rsmd.getColumnTypeName(i);
                    System.out.println(name + ":" + type);
                }
            }
            boolean is = rsmd.isSearchable(1);
            System.out.println(is);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        parameterMetaData();
    }
}