数据库查询表以及表中的字段名和类型

2018-06-29 06:17:11来源：博客园阅读 ()


import org.springframework.stereotype.Service;
import sailingdscg_ws.sailingdscg_ws.entity.TableField;
import sailingdscg_ws.sailingdscg_ws.service.IOracleTablesUtils;

import javax.servlet.ServletException;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class OracleTablesUtils implements IOracleTablesUtils {

    private static final long serialVersionUID = 1L;
    Connection conn = null;
    Statement st = null;


    //获取conn
    @Override
    public Boolean init(String url, String username, String password) throws ServletException {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = java.sql.DriverManager.getConnection(url, username, password);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (conn != null) {
            return true;
        } else {
            return false;
        }
    }

    @Override
    public Map<String, List<TableField>> getTables() throws ServletException, IOException {
        // 1、获取数据库所有表
        List<String> tables = new ArrayList<String>();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet rs = dbMetaData.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // 2、遍历数据库表，获取各表的字段等信息
        Map<String, List<TableField>> map = new HashMap<>();
        for (String tableName : tables) {
            List<TableField> tablesMessage = new ArrayList<TableField>();
            String sql = "select * from " + tableName;
            try {
                PreparedStatement ps = conn.prepareStatement(sql);
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData meta = rs.getMetaData();
                int columeCount = meta.getColumnCount();
                for (int i = 1; i < columeCount + 1; i++) {
                    TableField tableField = new TableField();
                    tableField.setName(meta.getColumnName(i));
                    tableField.setType(Integer.toString(meta.getColumnType(i)));
                    tablesMessage.add(tableField);
                }
                map.put(tableName, tablesMessage);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    //释放conn
    @Override
    public void destroy() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}