从数据库读取表名和字段，返回json串，显示为树结构

来源：互联网发布：2016.8.30淘宝开学爆到编辑：程序博客网时间：2024/04/30 02:11
/** *  * File: QueryTableDaoImpl.java * Description: * <<从数据库中查询当前数据库表信息>> * * Notes: * <<查询数据库表结构，在web界面中以tree的形式展示>> * Revision History: * <<Create>> */@Component("queryTableDao")public class QueryTableDaoImpl extends BaseDAOImpl implements QueryTableDao {@Autowired(required=true)@Qualifier("sessionFactory")public void setMySessionFactory(SessionFactory sessionFactory) {super.setSessionFactory(sessionFactory);}@Overridepublic void createTable(List<ParseObject> paseObject,String tablename) {new CreateTable().CreateDatabaseTable(CreateSql.SqlCreate(paseObject, tablename));}Connection conn = null; PreparedStatement pst = null;/** * Description: * <<从数据库中查询当前数据库表的详细信息>> * Notes: * <<已过滤为zq_开头，不显示id，并且表字段转为汉语的表>> */@SuppressWarnings("deprecation")@Overridepublic String queryTablesName() {ResultSet resultset = null;//获取当前数据链接conn = this.getHibernateTemplate().getSessionFactory().getCurrentSession().connection();StringBuffer result = new StringBuffer("[");String columnName = "";String columnNameCN = "";String columnType = "";String columnString = "";try{  //查询表名resultset = conn.getMetaData().getTables(null, null, null, new String[]{ "TABLE" });List<String> tableArr = new ArrayList<String>();while(resultset.next()){String tableName = resultset.getString(3);//过滤表名，只显示zq_表格if (null != tableName && ("zq_").equals(tableName.substring(0,3))){tableArr.add(tableName);}}//zq表存在时if (tableArr.size() != 0){for (int i = 0; i < tableArr.size(); i++) {result.append("{");result.append("\"text\":" + "\"" + ChangeUTF_16.deUnicode(tableArr.get(i).substring(3)) + "\"");//通过表名查询表字段pst = conn.prepareStatement("SELECT * FROM " + tableArr.get(i));ResultSetMetaData rsd = pst.executeQuery().getMetaData();result.append(",\"children\":");result.append(" [");for(int j = 0; j < rsd.getColumnCount() - 1; j++) {columnName = rsd.getColumnName(j + 1);//过滤id并将列名转为汉语if (!"id".equals(columnName)){columnNameCN = ChangeUTF_16.deUnicode(columnName.substring(2));columnType = rsd.getColumnTypeName(j + 1);columnString = columnNameCN + " (" + columnType + ")";result.append("{\"text\":\"" + columnString + "\"},");}}//过滤掉每个表最后一个字段的“，”columnName = rsd.getColumnName(rsd.getColumnCount());columnType = rsd.getColumnTypeName(rsd.getColumnCount());if (!"id".equals(columnName)){columnNameCN = ChangeUTF_16.deUnicode(columnName.substring(2));columnString = columnNameCN + " (" + columnType + ")";result.append("{\"text\":\"" + columnString + "\"}");result.append("]");result.append("},");}}}//zq表不存在else{result.append("{\"text\":\"----You don't have zq table!\"}}");}//过滤掉每个表最后一个表的“，”result = result.deleteCharAt(result.toString().length() - 1);    result.append("]");    }catch(SQLException e) {         throw new RuntimeException(e);  } finally {   if(resultset != null){    try {    resultset.close();} catch (SQLException e) {e.printStackTrace();} finally{resultset = null;}    }    if(pst != null){    try {pst.close();} catch (SQLException e) {e.printStackTrace();} finally{pst = null;}    }    if(conn != null){    try {    conn.close();} catch (SQLException e) {e.printStackTrace();} finally{conn = null;}    }}return result.toString();}}
0 0