JDBC工具类
- 使用JDBC连接数据库时经常会写许多重复的代码,将这些代码封装到一个工具类中,有利于我们提高写代码的效率。
封装一个JDBC工具类
public class JDBCUtils {
private static final Logger Log = Logger.getLogger(JDBCUtils.class);
private static String DRIVER;
private static String URL;
private static String USERNAME;
private static String PASSWORD;
static {
InputStream is = JDBCUtils.class.getResourceAsStream("/jdbc.properties");
Properties prop = new Properties();
try {
prop.load(is);
DRIVER = prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
} catch (Exception e) {
Log.error("配置文件加载异常:" + e);
}
}
private static Connection conn;
public JDBCUtils() {
}
public static Connection getConnection() {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
Log.error("建立链接失败:" + e);
}
return conn;
}
public static void releaseConn() {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
Log.error("链接关闭异常:" + e);
}
}
}
}
- 以上是很常见的JDBC工具类的封装,为我们加载数据库驱动、建立连接、关闭资源提供了一劳永逸的方法,可是你可能觉得还是不够工具化。
- 我们在使用Java代码操作数据库时还是经常能看到这样的重复代码
- 如果将增删改查也封装进工具类中,那么会省去很多需要在
DAO
层做的重复性工作,而增、删、改可以看作是一类的数据库更新方法。所以我们要做的是封装数据库更新
和查询
方法。
增强后的JDBC工具类
public class JDBCUtils {
private static final Logger Log = Logger.getLogger(JDBCUtils.class);
private static String DRIVER;
private static String URL;
private static String USERNAME;
private static String PASSWORD;
private static Connection conn;
private static Statement stmt;
private static PreparedStatement pstmt;
private static ResultSet rs;
static {
InputStream is = JDBCUtils.class.getResourceAsStream("/jdbc.properties");
Properties prop = new Properties();
try {
prop.load(is);
DRIVER = prop.getProperty("jdbc.driver");
URL = prop.getProperty("jdbc.url");
USERNAME = prop.getProperty("jdbc.username");
PASSWORD = prop.getProperty("jdbc.password");
} catch (Exception e) {
Log.error("配置文件加载异常:" + e);
}
conn = getConnection();
}
public static Connection getConnection() {
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (Exception e) {
Log.error("建立链接失败:" + e);
}
return conn;
}
public static void releaseConn() {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
Log.error("代理关闭异常:" + e);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
Log.error("预代理关闭异常:" + e);
}
}
if ( rs != null) {
try {
rs.close();
} catch (SQLException e) {
Log.error("结果集关闭异常:" + e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
Log.error("链接关闭异常:" + e);
}
}
}
public static List<Map<String,Object>> selectMessage(String sql) {
ArrayList<Map<String,Object>> list = new ArrayList<>();
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int len_count = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 0; i < len_count; i++) {
String len_name = metaData.getColumnName(i + 1);
Object len_value = rs.getObject(len_name);
if (len_value == null) {
len_value = "";
}
map.put(len_name,len_value);
}
list.add(map);
}
} catch (SQLException e) {
Log.error("查询数据异常:" + e);
}
return list;
}
public static int updateMessage(String sql) {
int i = 0;
try {
i = getConnection().createStatement().executeUpdate(sql);
} catch (SQLException e) {
Log.error("编辑数据异常:" + e);
}
releaseConn();
return i;
}
public static Object getObj(Map<String,Object> map, Class<User> targetClass) {
Iterator<Map.Entry<String, Object>> iterator = map.entrySet().iterator();
Object target = null;
try {
target = targetClass.newInstance();
while (iterator.hasNext()) {
Map.Entry<String, Object> entry = iterator.next();
String setMethodName = "set" + (entry.getKey().substring(0,1).toUpperCase() + entry.getKey().substring(1));
Field declaredField = targetClass.getDeclaredField(entry.getKey());
Object value = entry.getValue();
Method set_method = targetClass.getMethod(setMethodName, declaredField.getType());
set_method.invoke(target, value);
}
} catch (Exception e) {
Log.error("封装对象出现异常:" + e);
}
return target;
}
}