背景:
用惯了Mybatis,接收一个老项目使用Hibernate,特别不习惯。新的功能需要系统后台定时执行任务,顾使用JDBC封装工具类执行
源代码
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import com.ytforever.listener.ResourceManager;/**
* 使用JDBC连接数据库,处理系统自动执行任务等 特点: 直接执行sql,返回List<Map<字段名,值>>
*
* @author ZhangLi
* @date 2018年3月2日 下午2:57:58
*/
public class JDBCUtil {
private static String sql; public static List<Map<String, Object>> selectRS(String sql) {
JDBCUtil.sql = sql;
return selectRS();
} public static List<Map<String, Object>> selectRS() {
ArrayList<Map<String, Object>> rsList = new ArrayList<Map<String, Object>>();
Map<String, Object> rsMap = null;
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection("detection1", "detection1");
conn.setAutoCommit(false);
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();// 取得数据库的列名
int numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
rsMap = new HashMap<String, Object>(numberOfColumns);
for (int i = 1; i < numberOfColumns + 1; i++) {
rsMap.put(rsmd.getColumnName(i), rs.getObject(i));
}
rsList.add(rsMap);
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} }
return rsList;
}
/**
* 连接Oracle数据库
*/
public static Connection getConnection(String user, String password) {
Connection con = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521/orcl", user, password);
return con;
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
} public static void main(String[] args) {
String sql = String.format("select * from V_ITEM t where t.product_code='%s' and t.procedure_code='%s' and form_id is not null","YQ","2");
List<Map<String, Object>> selectRS = JDBCUtil.selectRS(sql);
for (Map<String, Object> map : selectRS) {
map.get("FORM_ID");
}
}}
特点:
1、特别方便
2、习惯写Sql的程序员看着特别亲切
3、转换为List之后,返回前台Json,前台人员也很舒服
于2018-03-02 16:05完成于西安逸翠园二期2号楼2单元
沫沫金
QQ:976465996
欢迎同仁联系,拍砖。