java连接数据库增、删、改、查工具类

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<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中列名部分
  pattern p = pattern.compile("(?i)insert\\s+into.*\\((.*)\\)\\s+values.*");
  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;
 }

 /**
  * 利用正则表达式,获得update sql中的列名, 包括where字句的
  *
  * @param sql
  * @return
  */
 private static list<string> getcolumnsfromupdate(string sql) {
  list<string> colnames = new arraylist<string>();
  // 取出sql中列名部分
  pattern p = pattern.compile("(?i)update(?:.*)set(.*)(?:from.*)*where(.*(and)*.*)");
  matcher m = p.matcher(sql.trim());
  string[] tempa = null;
  if (m.matches()) {
   tempa = m.group(1).split(",");
   if(m.groupcount() > 1){
    string[] tmp = m.group(2).split("and");
    string[] fina = new string[tempa.length + tmp.length];
    system.arraycopy(tempa, 0, fina, 0, tempa.length);
    system.arraycopy(tmp, 0, fina, tempa.length, tmp.length);
    tempa = fina;
   }
  }
  if (tempa == null) {
   return null;
  }
  string p1 = "(?i)(\\w+)(?:\\s*\\=\\s*.*)";
  string p2 = "(?i)(?:\\w+\\.)(\\w+)(?:\\s*\\=\\s*.*)";
  p = pattern.compile(p1 + "||" + p2);
  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;
 }

 /**
  * 为sql添加统计代码
  *
  * @param sql
  * @return
  */
 private static string addcountsql(string sql) {
  stringbuffer sb = new stringbuffer();
  sb.append(" select count(*) as datacount from (");
  sb.append(sql);
  sb.append(") as a");
  return sb.tostring();
 }

 /**
  * 为sql添加分页代码
  *
  * @param sql
  * @param start
  * @param limit
  * @return
  */
 private static string addpagingsql(string sql, int start, int limit) {
  stringbuffer sb = new stringbuffer();
  if ("com.microsoft.jdbc.sqlserver.sqlserverdviver".equals(driver)) {//sqlserver 0.7 2000

  } else if ("com.microsoft.sqlserver.jdbc.sqlserverdriver".equals(driver)) {//sqlserver 2005 2008

  } else if ("com.mysql.jdbc.driver".equals(driver)) {//mysql
   sb.append(sql);
   sb.append(" limit ");
   sb.append(start);
   sb.append(",");
   sb.append(limit);
  } else if ("oracle.jdbc.driver.oracledriver".equals(driver)) {//oracle8/8i/9i/10g数据库（thin模式）
   list<string> list = getcolumnsfromselect(sql);
   sb.append("select ");
   for (string str : list)
    sb.append(str).append(", ");
   sb.deletecharat(sb.lastindexof(","));
   sb.append(" from (").append(sql).append(") as a");
   sb.append(" where rownum between ").append(start == 0 ? 1 : start).append(" and ").append(limit);
  } else if ("com.ibm.db2.jdbc.app.db2driver".equals(driver)) {//db2

  } else if ("com.sybase.jdbc.sybdriver".equals(driver)) {//sybase

  } else if ("com.informix.jdbc.ifxdriver".equals(driver)) {//informix

  } else if ("org.postgresql.driver".equals(driver)) {//postgresql
   sb.append(sql);
   sb.append(" limit ");
   sb.append(limit);
   sb.append(" offset ");
   sb.append(start);
  }
  return sb.tostring();
 }
 /**
  * 将rusultset对象实例化t对象
  *
  * @param <t>
  * @param t
  * @param rs
  * @param sql
  * @return t
  * @throws exception
  */
 private static <t> t instance(class<t> t, resultset rs, string sql) throws exception{
  list<string> columns = getcolumnsfromselect(sql);
  t obj = t.newinstance();
  for (string col : columns) {
   try{
    field f = t.getdeclaredfield(col);
    f.setaccessible(true);
    object v = getvalue(col, f.gettype().getname(), rs);
    f.set(obj, v);
   }catch(nosuchfieldexception e){
    field[] fields = t.getdeclaredfields();
    for (field f : fields) {
     column column = f.getannotation(column.class);
     if(column != null && column.name().equals(col)){
      f.setaccessible(true);
      object v = getvalue(col, f.gettype().getname(), rs);
      f.set(obj, v);
     }
    }
   }
  }

  return obj;
 }

 private static object getvalue(string columnname, string type, resultset rs) throws sqlexception{
  object obj = null;
//  system.out.println("name="+f.getname()+", type="+f.gettype().getname() );
  if("java.lang.integer".equals(type) || "int".equals(type)) {
   obj = rs.getint(columnname);
  }else if("java.lang.long".equals(type) || "long".equals(type)) {
   obj = rs.getlong(columnname);
  }else if("java.lang.short".equals(type)||"short".equals(type)) {
   obj = rs.getshort(columnname);
  }else if("java.lang.float".equals(type)||"float".equals(type)) {
   obj = rs.getfloat(columnname);
  }else if("java.lang.double".equals(type)||"double".equals(type)) {
   obj = rs.getdouble(columnname);
  }else if("java.lang.byte".equals(type)||"byte".equals(type)) {
   obj = rs.getbyte(columnname);
  }else if("java.lang.boolean".equals(type)||"boolean".equals(type)) {
   obj = rs.getboolean(columnname);
  }else if("java.lang.string".equals(type)) {
   obj = rs.getstring(columnname);
  }else {
   obj = rs.getobject(columnname);
  }
//  system.out.println("name="+f.getname() +", type="+f.gettype().getname()+", value="+(obj == null ? "null" : obj.getclass())+",{"+columnname+":"+obj+"}");
  return obj;
 }
 /**
  * 将param中的参数添加到pstate
  *
  * @param pstate
  * @param columns
  * @throws sqlexception
  */
 private static <t> void setparameters(preparedstatement pstate, object... params) throws exception {
  if (params != null && params.length > 0) {
   for (int i = 0; i < params.length; i++) {
    object value = params[i];
    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);
   }
  }

 }
 /**
  * 将param中的参数添加到pstate
  *
  * @param pstate
  * @param columns
  * @param t
  * @throws sqlexception
  */
 private static <t> void setparameters(preparedstatement pstate, list<string> columns, t t) throws exception {
  if (columns != null && columns.size() > 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 <t> int insert(string sql, t t) throws exception {
  connection conn = null;
  preparedstatement pstate = null;
  int updatecount = 0;
  try {
   conn = getconnection();
   list<string> 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 <t> 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 <t> int update(string sql, t t) throws exception {
  connection conn = null;
  preparedstatement pstate = null;
  int updatecount = 0;
  try {
   conn = getconnection();
   list<string> 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 <t> 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<t>
  * @throws exception
  */
 public static <t> list<t> queryplural(class<t> t, string sql, object... param) throws exception {
  connection conn = null;
  preparedstatement stmt = null;
  resultset rs = null;
  list<t> list = new arraylist<t>();
  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 <t> list<t> querypluralforpagging(class<t> t, int start, int limit, string sql, object... param) throws exception {
  connection conn = null;
  preparedstatement stmt = null;
  resultset rs = null;
  list<t> list = new arraylist<t>();
  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> t querysingular(class<t> 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 "";
 } 

}