元数据

简介：

使用元数据编写更通用的jdbc代码

什么是元数据？

    1.链接数据库

        知道连接的是哪个数据库，版本号，使用数据库的元数据对象（DatabaseMetaData）

package com.lpy.jdbc.metadata;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
public class Demo1 {
    public static void main(String[] args) throws Exception{
        ComboPooledDataSource ds=new ComboPooledDataSource();
        Connection conn=ds.getConnection();
        DatabaseMetaData dbmd=conn.getMetaData();
        System.out.println(dbmd.getDatabaseProductName());
        System.out.println(dbmd.getDatabaseMajorVersion());
        System.out.println(dbmd.getDatabaseMinorVersion());
        System.out.println(dbmd.getDriverMajorVersion());
        System.out.println(dbmd.getDriverName());
    }
}

    2.预编译statement执行sql

        预编译sql之后，知道预编译的sql有几个参数，使用参数元数据对象（ParameterMetaData）

 private static void t1() throws SQLException {
        ComboPooledDataSource ds = new ComboPooledDataSource();
        Connection conn = ds.getConnection();
        String sql = "insert into employee(id,username,gender,birthday,email,age)VALUES (?,?,?,?,?,?)";
        PreparedStatement stmt = conn.prepareStatement(sql);
       /* stmt.setInt(1,1);
        stmt.setString(2,"lpy");
        stmt.setString(3,"男");
        stmt.setDate(4,new Date(new java.util.Date().getTime()));
        stmt.setString(5,"123");
        stmt.setInt(6,25);*/
        //参数元数据
        ParameterMetaData pmd = stmt.getParameterMetaData();
        //把参数放入数组
        Object[] values = new Object[]{2, "lpy", "男", new Date(new java.util.Date().getTime()), "@qq", 26};
        //得到sql中几个参数
        int count = pmd.getParameterCount();
        for (int i = 1; i <= count; i++) {
            stmt.setObject(i, values[i - 1]);
        }
        stmt.executeUpdate();
    }

    3.执行查询sql，返回结果集

        ResultSet rs ，知道表的字段数和字段名称，使用结果集元数据对象（ResultSetMetaData）

  private static void t1() throws SQLException {
        ComboPooledDataSource ds = new ComboPooledDataSource();
        Connection conn = ds.getConnection();
        String sql = "SELECT *FROM stu where id=?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ParameterMetaData pm = stmt.getParameterMetaData();
        int count = pm.getParameterCount();
        Object[] values = new Object[]{2};
        for (int i = 1; i <= count; i++) {
            stmt.setObject(i, values[i - 1]);
        }
        ResultSet rs = stmt.executeQuery();
        ResultSetMetaData rsm = rs.getMetaData();
        int colcount = rsm.getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= colcount; i++) {
                Object value = rs.getObject(i);
                System.out.println(value);
            }
        }
    }

整理一个查询和更新的工具类

package com.lpy.jdbc.metadata;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.beanutils.BeanUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SQLUtil {
    public static void update(String sql, Object[] values) {
        try {
            ComboPooledDataSource ds = new ComboPooledDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            ParameterMetaData pm = stmt.getParameterMetaData();
            int count = pm.getParameterCount();
            for (int i = 1; i <= count; i++) {
                stmt.setObject(i, values[i - 1]);
            }
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static <T>List<T> Query(String sql, Object[] values, Class<T> clazz) {
        try {
            List<T> list = new ArrayList<>();
            ComboPooledDataSource ds = new ComboPooledDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            ParameterMetaData pm = stmt.getParameterMetaData();
            int count = pm.getParameterCount();
            if (values != null) {
                for (int i = 1; i <= count; i++) {
                    stmt.setObject(i, values[i - 1]);
                }
            }
            ResultSet rs = stmt.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData();
            int colcount = rsm.getColumnCount();
            while (rs.next()) {
                T obj = clazz.newInstance();
                for (int i = 1; i <= colcount; i++) {
                    Object value = rs.getObject(i);
                    String name = rsm.getColumnName(i);
                    BeanUtils.copyProperty(obj, name, value);
                }
                list.add(obj);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }

    }
}