今天看了一个写的不错的数据库工具类
建议把它过过,思路捋一捋
package com.mytest;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.naming.NamingException;
import javax.sql.DataSource;
/**
* 数据库查询工具类
* 使用预编译的sql
*
* @author XueLiang
*
*/
public class DBUtil {
private static String driver;
private static DataSource ds = null;
private static String url = "jdbc:postgresql://192.168.56.101/db";
private static String user = "test";
private static String password = "12345678";
static {
try {
Class.forName("org.postgresql.Driver");
//ds = (DataSource)SpringContextUtil.getBean("dataSource");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 建立连接
*
* @return con Connection
* @throws Exception
*/
private static Connection getConnection() throws Exception {
Connection conn = DriverManager.getConnection(url, user, password);
// Connection conn = ds.getConnection();
Driver d = DriverManager.getDriver(conn.getMetaData().getURL());
driver = d.getClass().getName();
return conn;
}
/**
* 关闭连接
*
* @param conn
* @param stmt
* @param preStmt
* @param rs
* @throws SQLException
*/
private static void replease(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
}
/**
* 利用正则表达式,获得SELECT SQL中的列名
*
* @param sql
* @return
*/
private static List<String> getColumnsFromSelect(String sql) {
List<String> colNames = new ArrayList<String>();
// 取出sql中列名部分
Pattern p = Pattern.compile("(?i)select\\s(.*?)\\sfrom.*");
Matcher m = p.matcher(sql.trim());
String[] tempA = null;
if (m.matches()) {
tempA = m.group(1).split(",");
}
if (tempA == null) {
return null;
}
String p1 = "(\\w+)";
String p2 = "(?:\\w+\\s(\\w+))";
String p3 = "(?:\\w+\\sas\\s(\\w+))";
String p4 = "(?:\\w+\\.(\\w+))";
String p5 = "(?:\\w+\\.\\w+\\s(\\w+))";
String p6 = "(?:\\w+\\.\\w+\\sas\\s(\\w+))";
String p7 = "(?:.+\\s(\\w+))";
String p8 = "(?:.+\\sas\\s(\\w+))";
p = Pattern.compile("(?:" + p1 + "||" + p2 + "||" + p3 + "||" + p4
+ "||" + p5 + "||" + p6 + "||" + p7 + "||" + p8 + ")");
for (String temp : tempA) {
m = p.matcher(temp.trim());
if (!m.matches()) {
continue;
}
for (int i = 1; i <= m.groupCount(); i++) {
if (m.group(i) == null || "".equals(m.group(i))) {
continue;
}
colNames.add(m.group(i));
}
}
return colNames;
}
/**
* 利用正则表达式,获得INSERT SQL中的列名
*
* @param sql
* @return
*/
private static List<String> getColumnsFromInsert(String sql) {
List<String> colNames = new ArrayList<String>();
// 取出sql中列名部分
P