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

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 2019年7月26日
	 */
	public static void getColumnCommentInfo() throws Exception
	{
		StringBuffer sql = new StringBuffer();// sql
		sql.append("select a.COLUMN_NAME ,b.comments ");
		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 b.comments 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 2019年7月25日
	 */
	public static JSONObject excuteQuery(String sql) throws Exception
	{
		getColumnCommentInfo();//查询字段注释信息，保存到HashMap
		Class.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 2019年7月26日
	 */
	public static void getColumnCommentInfo() throws Exception
	{
		StringBuffer sql = new StringBuffer();// sql
		sql.append("select a.COLUMN_NAME ,b.comments ");
		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 b.comments 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 2019年7月25日
	 */
	public static JSONObject excuteQuery(String sql) throws Exception
	{
		getColumnCommentInfo();// 查询字段注释信息，保存到HashMap
		Class.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前端一起使用效果更好。