java连接数据库增、删、改、查工具类
本文导语: java连接数据库增、删、改、查工具类 数据库操作工具类,因为各厂家数据库的分页条件不同,目前支持Mysql、Oracle、Postgresql的分页查询在Postgresql环境测试过了,其他数据库未测试。sql语句需要使用预编译形式的 代码如下:pack...
java连接数据库增、删、改、查工具类
数据库操作工具类,因为各厂家数据库的分页条件不同,目前支持Mysql、Oracle、Postgresql的分页查询
在Postgresql环境测试过了,其他数据库未测试。
sql语句需要使用预编译形式的
package db;
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 getColumnsFromSelect(String sql) {
List colNames = new ArrayList();
// 取出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 0) {
for (int i = 0; i < columns.size(); i++) {
String attr = columns.get(i);
Object value = null;
Class c = t.getClass();
try{
Field f = c.getDeclaredField(attr);
value = f.get(t);
} catch (NoSuchFieldException e){
Field[] fields = c.getDeclaredFields();
for (Field f : fields) {
Column column = f.getAnnotation(Column.class);
if(column != null && column.name().equals(attr))
value = f.get(t);
}
}
int j = i + 1;
if (value == null)
pstate.setString(j, "");
if (value instanceof String)
pstate.setString(j, (String) value);
else if (value instanceof Boolean)
pstate.setBoolean(j, (Boolean) value);
else if (value instanceof Date)
pstate.setDate(j, (Date) value);
else if (value instanceof Double)
pstate.setDouble(j, (Double) value);
else if (value instanceof Float)
pstate.setFloat(j, (Float) value);
else if (value instanceof Integer)
pstate.setInt(j, (Integer) value);
else if (value instanceof Long)
pstate.setLong(j, (Long) value);
else if (value instanceof Short)
pstate.setShort(j, (Short) value);
else if (value instanceof Time)
pstate.setTime(j, (Time) value);
else if (value instanceof Timestamp)
pstate.setTimestamp(j, (Timestamp) value);
else
pstate.setObject(j, value);
}
}
}
/**
* 执行insert操作
*
* @param sql 预编译的sql语句
* @param t sql中的参数
* @return 执行行数
* @throws Exception
*/
public static int insert(String sql, T t) throws Exception {
Connection conn = null;
PreparedStatement pstate = null;
int updateCount = 0;
try {
conn = getConnection();
List columns = getColumnsFromInsert(sql);
pstate = conn.prepareStatement(sql);
setParameters(pstate, columns, t);
updateCount = pstate.executeUpdate();
} finally {
replease(conn, pstate, null);
}
return updateCount;
}
/**
* 执行insert操作
*
* @param sql 预编译的sql语句
* @param param 参数
* @return 执行行数
* @throws Exception
*/
public static int insert(String sql, Object... param) throws Exception {
Connection conn = null;
PreparedStatement pstate = null;
int updateCount = 0;
try {
conn = getConnection();
pstate = conn.prepareStatement(sql);
setParameters(pstate, param);
updateCount = pstate.executeUpdate();
} finally {
replease(conn, pstate, null);
}
return updateCount;
}
/**
* 执行update操作
*
* @param sql 预编译的sql语句
* @param t sql中的参数
* @return 执行行数
* @throws Exception
*/
public static int update(String sql, T t) throws Exception {
Connection conn = null;
PreparedStatement pstate = null;
int updateCount = 0;
try {
conn = getConnection();
List columns = getColumnsFromUpdate(sql);
pstate = conn.prepareStatement(sql);
setParameters(pstate, columns, t);
updateCount = pstate.executeUpdate();
} finally {
replease(conn, pstate, null);
}
return updateCount;
}
/**
* 执行update操作
*
* @param sql
* @param param 参数
* @return 执行行数
* @throws Exception
*/
public static int update(String sql, Object... param) throws Exception {
Connection conn = null;
PreparedStatement pstate = null;
int updateCount = 0;
try {
conn = getConnection();
pstate = conn.prepareStatement(sql);
setParameters(pstate, param);
updateCount = pstate.executeUpdate();
} finally {
replease(conn, pstate, null);
}
return updateCount;
}
/**
* 查询复数的对象
*
* @param t 查询结果封装的对象类型
* @param sql 预编译的sql
* @param param 查询条件
* @return List
* @throws Exception
*/
public static List queryPlural(Class t, String sql, Object... param) throws Exception {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List list = new ArrayList();
try {
conn = getConnection();
stmt = conn.prepareStatement(sql);
setParameters(stmt, param);
rs = stmt.executeQuery();
while (rs.next()) {
list.add(instance(t, rs, sql));
}
} finally {
replease(conn, stmt, rs);
}
return list;
}
/**
* 分页查询复数的对象
*
* @param t 查询结果封装的对象类型
* @param start 开始页
* @param limit 页大小
* @param sql 预编译的sql语句
* @param param 查询参数
* @throws Exception
*/
public static List queryPluralForPagging(Class t, int start, int limit, String sql, Object... param) throws Exception {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List list = new ArrayList();
try {
conn = getConnection();
//添加分页代码
sql = addPagingSQL(sql, start, limit);
stmt = conn.prepareStatement(sql);
setParameters(stmt, param);
rs = stmt.executeQuery();
while (rs.next()) {
list.add(instance(t, rs, sql));
}
} finally {
replease(conn, stmt, rs);
}
return list;
}
/**
* 查询单个的对象
*
* @param t 查询结果对象
* @param sql 预编译的sql
* @param param 查询参数
* @return T
* @throws Exception
*/
public static T querySingular(Class t, String sql, Object... param) throws Exception {
T obj = null;
ResultSet rs = null;
Connection conn = null;
PreparedStatement pstate = null;
try {
conn = getConnection();
pstate = conn.prepareStatement(sql);
setParameters(pstate, param);
rs = pstate.executeQuery();
if (rs.next()) {
obj = instance(t, rs, sql);
}
} finally {
replease(conn, pstate, rs);
}
return obj;
}
/**
* 查询数据量
*
* @param param 查询参数
* @param sql
* @return
* @throws SQLException
* @throws NamingException
*/
public static int queryDataCount(String sql, Object... param)
throws Exception {
int dataCount = 0;
Connection conn = null;
PreparedStatement pstate = null;
ResultSet rs = null;
try {
conn = getConnection();
sql = addCountSQL(sql);
pstate = conn.prepareStatement(sql);
setParameters(pstate, param);
rs = pstate.executeQuery();
if (rs.next()) {
dataCount = rs.getInt("dataCount");
}
} finally {
replease(conn, pstate, rs);
}
return dataCount;
}
/**
* 属性字段的注释,用于标记该属性对应的数据库字段
* 例如:
* @Column(name="user_name");
* String userName;
* 表示userName这个属性对应的数据库字段是user_name
*
* 如果属性和数据库字段完全一致,则不必标注
* @author xueliang
*/
@Target({ ElementType.FIELD })
@Retention(RetentionPolicy.RUNTIME)
public @interface Column{
String name() default "";
}
}