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

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

新老客户大回馈,云服务器低至5折


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();
}
}
}
}

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:shiro框架 4种授权方式 说明

下一篇:当Java遇到XML 的邂逅+dom4j