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前端一起使用效果更好。