package com.cmos.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* 数据库连接工具类
*
* @author Wx
*
*/
public class JDBCUtil {
private static Properties ps = new Properties();
static {
// 读入配置文件
InputStream is = JDBCUtil.class.getResourceAsStream("jdbc.properties");
try {
ps.load(is);
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 建立连接方法
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
Connection conn = null;
if (conn == null) {
//加载驱动
Class.forName(ps.getProperty("driver"));
conn = DriverManager.getConnection(ps.getProperty("url"), ps.getProperty("username"),
ps.getProperty("password"));
return conn;
} else {
return conn;
}
}
/**
* 关闭连接
* @param conn
* @param pre
* @param rs
*/
public static void close(Connection conn, PreparedStatement pre, ResultSet rs) {
if (rs != null)
try {
rs.close();
} catch (SQLException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
if (pre != null)
try {
pre.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 关闭连接方法
* @param conn
* @param pre
*/
public static void close(Connection conn, PreparedStatement pre) {
if (pre != null)
try {
pre.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC-util 封装了jdbc并封装dbutil方法，查询直接返回List<map<String,Object>>，将一大堆代码放在一块是真的不好看
代码如下：
DButil
package com.cmos.util;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class DBUtil {
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
/**
* 增删改操作
* @param sql
* @param paramsValue
* @return
*/
public static int execute(String sql, Object[] paramsValue) {
int result = 0;
try {
// 1. 数据库连接
conn = JDBCUtil.getConnection();
// 2. 获取PreparedStatement
pstmt = conn.prepareStatement(sql);
// 3. 得到参数元数据个数
int count = pstmt.getParameterMetaData().getParameterCount();
// 4. 利用参数元数据给SQL语句的占位符需要的参数赋值
if (paramsValue != null && paramsValue.length > 0) {
for (int i = 0; i < count; i++) {
// 循环结束，可以给SQL语句完整赋值
pstmt.setObject(i + 1, paramsValue[i]);
}
}
// 5. 执行
result = pstmt.executeUpdate();
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.close(conn, pstmt);
}
return result;
}
/**
* 查询操作
* @param sql
* @param params
* @return
*/
public static List<Map<String, Object>> executeQuery(String sql, Object[] params) {
List<Map<String, Object>> list = new ArrayList<>();
try {
conn = JDBCUtil.getConnection();
pstmt = conn.prepareStatement(sql);
// 设置参数
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
rs = pstmt.executeQuery();
// 得到ResultSetMetaData对象，这个对象包括列的信息(列的名称和类型等等)
ResultSetMetaData metaData = rs.getMetaData();
// 得到结果集中列的个数
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 1; i <= columnCount; i++) {
// 得到指定的列名的别名
String name = metaData.getColumnLabel(i);
Object value = rs.getObject(i);
map.put(name, value);
}
list.add(map);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(conn, pstmt, rs);
}
return list;
}
}
