MetaData元数据

浏览数：26 / 时间：2015年06月09日

一、基础

    定义：数据库、表、列相关信息的描述。
   何时使用：想取得对数据库的信息时使用。
   一】数据库元数据——通过DataBaseMetaData
           DataBaseMetaData dbmd = conn.getDataBaseMetaData();
       code:

           Connection conn = C3P0Utils.getMySqlConnection();
            //取得数据库元数据 ———— DatabaseMetaData
            DatabaseMetaData dbmd = conn.getMetaData();
            String driver = dbmd.getDriverName();
            System.out.println("driverName : " + driver);//MySQL-AB JDBC Driver
            String url = dbmd.getURL();
            System.out.println("url : " + url);//jdbc:mysql://127.0.0.1:3306/jdbc
            int level = dbmd.getDefaultTransactionIsolation();//得到事务级别,值所对应的级别可有Connectin类的字段值来查看
            System.out.println("level : " + level);//2
            String productName = dbmd.getDatabaseProductName();
            System.out.println("productName : " + productName);//MySQL

   二】参数元数据——通过ParameterMetaData
           ParameterMetaData pmd = pstmt.getParameterMetaData;
       code:

           Connection conn = C3P0Utils.getMySqlConnection();
            PreparedStatement pstmt = conn.prepareStatement(SqlMapping.QUERY_INF);//SELECT * FROM jdbc.test_batch WHERE id = ?
            //得到参数元数据
            ParameterMetaData pmd = pstmt.getParameterMetaData();
            int cnt = pmd.getParameterCount();
            System.out.println("共有" + cnt + "个参数");//公有的参数，即为SQL语句中的 ?  的个数，这里为1个

   三】结果集数据元——通过ResultSetMetaData
           ResultSetMetaData rsmd = rs.getMetaData();
       code:

            String SQL = " SELECT * FROM jdbc.test_batch ";
            Connection conn = C3P0Utils.getMySqlConnection();
            PreparedStatement pstmt = conn.prepareStatement(SQL);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int cnt = rsmd.getColumnCount();// 结果集的个数
            for (int i = 1; i <= cnt; i++) {
                String colName = rsmd.getColumnName(i);//取得每列的名字
                System.out.println("colName = " + colName);//colName = id colName = name colName = age
            }

   总结：
       核心规律如下：
       ResultSetMetaData ResultSet.getMetaData();
       ParameterMetaData psmt.getMetaData();
       DataBaseMetaData conn.getMetaData();

二、使用MetaData元数据优化CUD操作和R操作(封装成工具方便使用)
一】CUD操作
code:

        // CUD操作
        public static int update(String sql, Object[] params) throws SQLException {
            Connection conn = C3P0Utils.getMySqlConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql);// INSERT INTO meta.user(username,salary) VALUES(?, ?)
            // 取得参数元数据
            ParameterMetaData pmd = pstmt.getParameterMetaData();
            // 取得参数个数
            int size = pmd.getParameterCount();
            // 循环绑定对象的值
            for (int i = 0; i < size; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            int rows = pstmt.executeUpdate();
            C3P0Utils.close(conn);
            return rows;
        }

二】R操作
code:

        //R操作(要求：javaBean的名称必须要和数据库字段的名称相同)
        public static <T> List<T> query(String sql, Object[] params, Class clazz) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException {
            Connection conn = C3P0Utils.getMySqlConnection();
            List<T> list = new ArrayList<T>();
            
            PreparedStatement pstmt = conn.prepareStatement(sql);//SELECT * FROM meta.user WHERE id = ?
            ParameterMetaData pmd = pstmt.getParameterMetaData();
            int cnt = pmd.getParameterCount();
            for(int i = 0; i < cnt; i++) {
                pstmt.setObject(i+1, params[i]);
            }
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int column = rsmd.getColumnCount();
                T t = (T) clazz.newInstance();
                for (int i = 0; i < column; i++) {
                    String colName = rsmd.getColumnName(i+1);
                    BeanUtils.setProperty(t, colName, rs.getObject(colName));
                }
                list.add(t);
            }
      
            C3P0Utils.closeAll(conn, pstmt, rs);
            
            return list;
        }