#实用#jdbcTemplate 获取数据库的表名 和 信息

1. 获取数据库的所有表的名字
   

  /**
     * 获取数据库的所有表的名字
     */
    @Test
    public void sss(){
        // DataSource dataSource;
        JdbcTemplate jd = new JdbcTemplate(getDataSource());

        List<Map<String, Object>> ll = jd.queryForList("select table_name from information_schema.tables where table_schema='blog'");
        ll.forEach(t -> System.out.println(t));


    }

获取表中 的列名字 和 类型

 @Test
    public void getMetaData() {
        // 设置数据库信息
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog?useUnicode=true&characterEncoding=UTF-8");
        dataSource.setUsername("root");
        dataSource.setPassword("422525");

        // 创建jdbcTemplate模板对象,设置数据源
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select * from "+ "tbl_article  limit 0";
        RowCountCallbackHandler rcch = new RowCountCallbackHandler();
        jdbcTemplate.query(sql, rcch);
        String[] coloumnName = rcch.getColumnNames();
        int[] coloumnType = rcch.getColumnTypes();
        System.out.println(Arrays.toString(coloumnName));
        System.out.println(Arrays.toString(coloumnType));

        // java.sql.Types.
        // rcch.get
    }

    public DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/blog?useUnicode=true&characterEncoding=UTF-8");
        dataSource.setUsername("root");
        dataSource.setPassword("422525");
        return dataSource;

    }

   @Test
    public void selectFullField() {
        // DataSource dataSource;
        JdbcTemplate jd = new JdbcTemplate(getDataSource());
        jd.queryForList("show full fields from tbl_user")
                .forEach(f -> System.out.println(f));

    }

jdbcTemplate 使用映射的方式

  @Test
    public void map() {
        // DataSource dataSource;
        JdbcTemplate jd = new JdbcTemplate(getDataSource());
        // RowMapper mapper = new ColumnMapRowMapper();
        List<Map> list = jd.query("show full fields from tbl_user", new RowMapper<Map>() {
            @Override
            public Map mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                Map map = new HashMap();
                map.put("field",resultSet.getString("Field"));
                map.put("type",resultSet.getString("Type"));
                return map;

            }
        });
        System.out.println(list);

    }

获取数据库表的注释信息

总结一下有用的sql语句

select table_name, create_time,table_comment from information_schema.tables where table_schema='blog'

获取表的注释详细 和 创建时间

    @Test
    public void mapTable() {
        String sql = "select `table_name`, `create_time`,`table_comment` from information_schema.tables where table_schema='blog'";
        JdbcTemplate jd = new JdbcTemplate(getDataSource());
        // RowMapper mapper = new ColumnMapRowMapper();
        List<Map<String, Object>> list = jd.queryForList(sql);
        System.out.println(list);
    }


    @Test
    public void mapTable2() {
        String sql = "select `table_name`, `create_time`,`table_comment` from information_schema.tables where table_schema='blog'";
        JdbcTemplate jd = new JdbcTemplate(getDataSource());
        // RowMapper mapper = new ColumnMapRowMapper();
        List<Map<String, Object>> list = jd.query(sql, new RowMapper() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> map = new HashMap();
                map.put("table_name",resultSet.getString("table_name"));
                map.put("table_comment",resultSet.getString("table_comment"));
                map.put("createDate",resultSet.getString("create_time"));
                return map;
            }
        });
        System.out.println(list);
    }