java使用jdbc连接oracle，实现自定义sql查询，加载字段注释

第一步、加载驱动

引用包

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.Map;import net.sf.json.JSONArray;import net.sf.json.JSONObject;

声明属性

代码示例

static final String DRIVER = "oracle.jdbc.OracleDriver";static final String URL = "jdbc:oracle:thin:@localhost/orcl";static final String USER = "sott";static final String PWD = "tiger";static Map<String, String> columnCommentMap = new HashMap<String, String>();// key:字段名，value:字段注释

加载字段注释，保存到HashMap

/*** 加载数据库字段注释* * @auther zhuteng* @time 7月26日*/public static void getColumnCommentInfo() throws Exception{StringBuffer sql = new StringBuffer();// sqlsql.append("select a.COLUMN_NAME ,ments ");sql.append("from cols a ");sql.append("left join user_col_comments b ");sql.append("on (a.TABLE_NAME = b.table_name and a.COLUMN_NAME=b.column_name) ");sql.append("left join user_tab_comments c ");sql.append("on (a.TABLE_NAME=c.table_name) ");sql.append("where not exists ");sql.append("(");sql.append("select d.OBJECT_NAME ");sql.append("from user_objects d ");sql.append("where d.OBJECT_TYPE = 'table' ");sql.append("and d.TEMPORARY = 'y' ");sql.append("and d.OBJECT_NAME = a.TABLE_NAME ");sql.append(")");sql.append("and ments is not null ");sql.append("order by a.TABLE_NAME,a.COLUMN_ID");Class.forName(DRIVER); // 加载驱动程序Connection connection = DriverManager.getConnection(URL, USER, PWD);// 连接数据库PreparedStatement pst = connection.prepareStatement(sql.toString());// 创建sql查询对象ResultSet result = pst.executeQuery(); // 执行查询while (result.next()){columnCommentMap.put(result.getString(1), result.getString(2));}// 关闭connection.close();pst.close();}

封装sql查询方法

/*** 根据sql查询数据* * @auther zhuteng* @time 7月25日*/public static JSONObject excuteQuery(String sql) throws Exception{getColumnCommentInfo();//查询字段注释信息，保存到HashMapClass.forName(DRIVER); // 加载驱动程序Connection connection = DriverManager.getConnection(URL, USER, PWD);// 连接数据库PreparedStatement pst = connection.prepareStatement(sql);// 创建sql查询对象ResultSet result = pst.executeQuery(); // 执行查询ResultSetMetaData rsmd = result.getMetaData();JSONArray tableTitle = new JSONArray();// 表格头for (int i = 1; i <= rsmd.getColumnCount(); i++){JSONObject tableTitle_Th = new JSONObject();// 表格头单元格tableTitle_Th.put("columncomment", columnCommentMap.get(rsmd.getColumnName(i)));// 字段名注释tableTitle_Th.put("columnname", rsmd.getColumnName(i));// 字段名tableTitle_Th.put("tablename", rsmd.getTableName(i));// 表名tableTitle_Th.put("columnclassname", rsmd.getColumnClassName(i));// JAVA_数据类型tableTitle_Th.put("columntypename", rsmd.getColumnTypeName(i) + "(" + rsmd.getColumnDisplaySize(i) + ")");// DB_数据类型tableTitle.add(tableTitle_Th);// 保存到数组}JSONObject table = new JSONObject();// 所有查詢的數據JSONArray tableBody = new JSONArray();// 表格内容while (result.next()){JSONArray tableRow = new JSONArray();// 表内容单元格for (int i = 1; i <= rsmd.getColumnCount(); i++){String classname = rsmd.getColumnClassName(i);// 数据类型switch (classname){case "java.math.BigDecimal":{tableRow.add(result.getBigDecimal(i));break;}case "java.lang.Boolean":{tableRow.add(result.getBoolean(i));break;}case "java.lang.Byte":{tableRow.add(result.getByte(i));break;}case "java.util.Date":{Date date = result.getDate(i);String time = "";if (date != null){time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}tableRow.add(time);break;}case "java.sql.Date":{java.sql.Date date = result.getDate(i);String time = "";if (date != null){time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}tableRow.add(time);break;}case "java.sql.Timestamp":{Timestamp date = result.getTimestamp(i);String time = "";if (date != null){time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}tableRow.add(time);break;}case "java.lang.Double":{tableRow.add(result.getDouble(i));break;}case "java.lang.Float":{tableRow.add(result.getFloat(i));break;}case "java.lang.Integer":{tableRow.add(result.getInt(i));break;}case "java.lang.Long":{tableRow.add(result.getLong(i));break;}case "java.lang.String":{tableRow.add(result.getString(i));break;}default:{System.out.println("未识别的数据类型：" + classname);}}}tableBody.add(tableRow);}// 关闭connection.close();pst.close();table.put("tableTitle", tableTitle);table.put("tableBody", tableBody);return table;}

测试

public static void main(String[] args) throws Exception{JSONObject table = excuteQuery("select * from t_h_user");JSONArray tableTitle = table.getJSONArray("tableTitle");JSONArray tableBody = table.getJSONArray("tableBody");System.out.print("sortnum\t");for (int j = 0; j < tableTitle.size(); j++){JSONObject tableTitle_Th = (JSONObject) tableTitle.get(j);System.out.print(tableTitle_Th.get("columnname") + "(" + tableTitle_Th.get("columncomment") + ")" + "\t");}System.out.println("\n------------------------------------------------------------------------------------------------------------------------");for (int i = 0; i < tableBody.size(); i++){JSONArray row = (JSONArray) tableBody.get(i);System.out.print((i + 1) + "\t");for (int j = 0; j < row.size(); j++){System.out.print(row.get(j) + "\t");}System.out.println();}}

完整代码

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.Map;import net.sf.json.JSONArray;import net.sf.json.JSONObject;public class MainTest{static final String DRIVER = "oracle.jdbc.OracleDriver";static final String URL = "jdbc:oracle:thin:@localhost/orcl";static final String USER = "wcy";static final String PWD = "11";static Map<String, String> columnCommentMap = new HashMap<String, String>();// 字段名，字段注释/*** 加载数据库字段注释* * @auther zhuteng* @time 7月26日*/public static void getColumnCommentInfo() throws Exception{StringBuffer sql = new StringBuffer();// sqlsql.append("select a.COLUMN_NAME ,ments ");sql.append("from cols a ");sql.append("left join user_col_comments b ");sql.append("on (a.TABLE_NAME = b.table_name and a.COLUMN_NAME=b.column_name) ");sql.append("left join user_tab_comments c ");sql.append("on (a.TABLE_NAME=c.table_name) ");sql.append("where not exists ");sql.append("(");sql.append("select d.OBJECT_NAME ");sql.append("from user_objects d ");sql.append("where d.OBJECT_TYPE = 'table' ");sql.append("and d.TEMPORARY = 'y' ");sql.append("and d.OBJECT_NAME = a.TABLE_NAME ");sql.append(")");sql.append("and ments is not null ");sql.append("order by a.TABLE_NAME,a.COLUMN_ID");Class.forName(DRIVER); // 加载驱动程序Connection connection = DriverManager.getConnection(URL, USER, PWD);// 连接数据库PreparedStatement pst = connection.prepareStatement(sql.toString());// 创建sql查询对象ResultSet result = pst.executeQuery(); // 执行查询while (result.next()){columnCommentMap.put(result.getString(1), result.getString(2));}// 关闭connection.close();pst.close();}/*** 根据sql查询数据* * @auther zhuteng* @time 7月25日*/public static JSONObject excuteQuery(String sql) throws Exception{getColumnCommentInfo();// 查询字段注释信息，保存到HashMapClass.forName(DRIVER); // 加载驱动程序Connection connection = DriverManager.getConnection(URL, USER, PWD);// 连接数据库PreparedStatement pst = connection.prepareStatement(sql);// 创建sql查询对象ResultSet result = pst.executeQuery(); // 执行查询ResultSetMetaData rsmd = result.getMetaData();JSONArray tableTitle = new JSONArray();// 表格头for (int i = 1; i <= rsmd.getColumnCount(); i++){JSONObject tableTitle_Th = new JSONObject();// 表格头单元格tableTitle_Th.put("columncomment", columnCommentMap.get(rsmd.getColumnName(i)));// 字段名注释tableTitle_Th.put("columnname", rsmd.getColumnName(i));// 字段名tableTitle_Th.put("tablename", rsmd.getTableName(i));// 表名tableTitle_Th.put("columnclassname", rsmd.getColumnClassName(i));// JAVA_数据类型tableTitle_Th.put("columntypename", rsmd.getColumnTypeName(i) + "(" + rsmd.getColumnDisplaySize(i) + ")");// DB_数据类型tableTitle.add(tableTitle_Th);// 保存到数组}JSONObject table = new JSONObject();// 所有查詢的數據JSONArray tableBody = new JSONArray();// 表格内容while (result.next()){JSONArray tableRow = new JSONArray();// 表内容单元格for (int i = 1; i <= rsmd.getColumnCount(); i++){String classname = rsmd.getColumnClassName(i);// 数据类型switch (classname){case "java.math.BigDecimal":{tableRow.add(result.getBigDecimal(i));break;}case "java.lang.Boolean":{tableRow.add(result.getBoolean(i));break;}case "java.lang.Byte":{tableRow.add(result.getByte(i));break;}case "java.util.Date":{Date date = result.getDate(i);String time = "";if (date != null){time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);}tableRow.add(time);break;}case "java.lang.Double":{tableRow.add(result.getDouble(i));break;}case "java.lang.Float":{tableRow.add(result.getFloat(i));break;}case "java.lang.Integer":{tableRow.add(result.getInt(i));break;}case "java.lang.Long":{tableRow.add(result.getLong(i));break;}case "java.lang.String":{tableRow.add(result.getString(i));break;}}}tableBody.add(tableRow);}// 关闭connection.close();pst.close();table.put("tableTitle", tableTitle);table.put("tableBody", tableBody);return table;}public static void main(String[] args) throws Exception{JSONObject table = excuteQuery("select * from t_h_user");JSONArray tableTitle = table.getJSONArray("tableTitle");JSONArray tableBody = table.getJSONArray("tableBody");System.out.print("sortnum\t");for (int j = 0; j < tableTitle.size(); j++){JSONObject tableTitle_Th = (JSONObject) tableTitle.get(j);System.out.print(tableTitle_Th.get("columnname") + "(" + tableTitle_Th.get("columncomment") + ")" + "\t");}System.out.println("\n------------------------------------------------------------------------------------------------------------------------");for (int i = 0; i < tableBody.size(); i++){JSONArray row = (JSONArray) tableBody.get(i);System.out.print((i + 1) + "\t");for (int j = 0; j < row.size(); j++){System.out.print(row.get(j) + "\t");}System.out.println();}}}

与web前端一起使用效果更好。