jdbc获取数据库元数据,获取数据库列表,获取数据库基本信息,获取指定数据库中的表信息,获取指定表中的字段信息

                                      jdbc获取数据库元数据

package cn.itcast.metadata.test;

import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Properties;

/**
 * 测试数据库元数据
 */
public class DataBaseMetaDataTest {

    private Connection connection;

    @Before
    public void init() throws Exception {

        String driver ="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306/ihrm?useUnicode=true&characterEncoding=utf8";
        String username="root";
        String password="111111";

        Properties props = new Properties();
        props.put("remarksReporting","true");//获取数据库的备注信息
        props.put("user",username);
        props.put("password",password);

        //1.获取连接
        Class.forName(driver);//注册驱动
        connection = DriverManager.getConnection(url,props);
    }
	/**  
	//test01 test02
	    @Before
    public void init() throws Exception {

        String driver ="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306?useUnicode=true&characterEncoding=utf8";
        String username="root";
        String password="111111";

        Properties props = new Properties();
        props.put("remarksReporting","true");//获取数据库的备注信息
        props.put("user",username);
        props.put("password",password);

        //1.获取连接
        Class.forName(driver);//注册驱动
        connection = DriverManager.getConnection(url,props);
    }
*/

    //获取数据库基本信息
    @Test
    public void test01() throws Exception {
        //2.获取元数据
        DatabaseMetaData metaData = connection.getMetaData();

        //3.获取数据库基本信息
        System.out.println(metaData.getUserName());

        System.out.println(metaData.supportsTransactions());//是否支持事务

        System.out.println(metaData.getDatabaseProductName());
    }


    //获取数据库列表
    @Test
    public void test02() throws SQLException {
        //1.获取元数据
        DatabaseMetaData metaData = connection.getMetaData();
        //2.获取所有数据库列表
        ResultSet rs = metaData.getCatalogs();
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        rs.close();
        connection.close();
    }

    //获取指定数据库中的表信息
    @Test
    public void test03() throws Exception {
        //1.获取元数据
        DatabaseMetaData metaData = connection.getMetaData();

        //2.获取数据库中表信息
        /**
         * String catalog, String schemaPattern,String tableNamePattern, String types[]
         *
         *  catalog:当前操作的数据库
         *      mysql:
         *          :ihrm
         *          catalog
         *      oralce:
         *          xxx:1521:orcl
         *          catalog
         *   schema：
         *      mysql：
         *          ：null
         *      oracle：
         *          ：用户名（大写）
         *
         *    tableNamePattern：
         *      null：查询所有表
         *      为空：查询目标表
         *
         *    types：类型
         *      TABLE：表
         *      VIEW：视图
         *
         */
        ResultSet rs = metaData.getTables(null, null, null, new String[]{"TABLE"});


        while (rs.next()) {
            System.out.println(rs.getString("TABLE_NAME"));
        }
    }


    //获取指定表中的字段信息
    @Test
    public void test04() throws Exception {
        DatabaseMetaData metaData = connection.getMetaData();

        //获取表中的字段信息
        /**
         *  catalog
         *  schema
         *  tableName
         *  columnName
         */
        ResultSet rs = metaData.getColumns(null, null, "bs_user", null);

        while (rs.next()) {
            System.out.println(rs.getString("COLUMN_NAME"));
        }
    }

}
           

参数集

package cn.itcast.metadata.test;

import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Properties;

/**
 * 测试参数元数据（ParameterMetaData）
 *      通过PreparedStatement获取
 *      获取sql参数中的属性信息
 */
public class ParameterMetaDataTest {

    private Connection connection;

    @Before
    public void init() throws Exception {

        String driver ="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306/ihrm?useUnicode=true&characterEncoding=utf8";
        String username="root";
        String password="111111";

        Properties props = new Properties();
        props.put("remarksReporting","true");//获取数据库的备注信息
        props.put("user",username);
        props.put("password",password);

        //1.获取连接
        Class.forName(driver);//注册驱动
        connection = DriverManager.getConnection(url,props);
    }

    //sql = SELECT * FROM bs_user WHERE id="1063705482939731968"
    @Test
    public void test() throws Exception {
        String sql = "select * from bs_user where id=?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, "1063705482939731968");

        //获取参数元数据
        ParameterMetaData metaData = pstmt.getParameterMetaData();

        int count = metaData.getParameterCount();

        System.out.println(count);
    }
}
           

返回集

package cn.itcast.metadata.test;

import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Properties;

/**
 * 测试结果集元数据（ResultSetMetaData）
 *      通过ResultSet获取
 *      获取查询结果的信息
 */
public class ResultSetMetaDataTest {

    private Connection connection;

    @Before
    public void init() throws Exception {

        String driver ="com.mysql.jdbc.Driver";
        String url="jdbc:mysql://127.0.0.1:3306/ihrm?useUnicode=true&characterEncoding=utf8";
        String username="root";
        String password="111111";

        Properties props = new Properties();
        props.put("remarksReporting","true");//获取数据库的备注信息
        props.put("user",username);
        props.put("password",password);

        //1.获取连接
        Class.forName(driver);//注册驱动
        connection = DriverManager.getConnection(url,props);
    }


    @Test
    public void test() throws Exception {
        //sql
        String sql = "select * from bs_user where id=?";
        //PreparedStatement
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, "1063705482939731968");
        //查询
        ResultSet rs = pstmt.executeQuery();

        //获取结果集元数据
        ResultSetMetaData metaData = rs.getMetaData();

        //获取查询字段个数
        int count = metaData.getColumnCount();

        for(int i =1;i<=count ;i++) {
            //获取列名
            String columnName = metaData.getColumnName(i);
            //获取字段类型 sql类型
            String columnType = metaData.getColumnTypeName(i);
            //获取java类型
            String columnClassName = metaData.getColumnClassName(i);
            System.out.println(columnName+"--"+columnType+"---"+columnClassName);
        }
    }

}