java連接數(shù)據(jù)庫(kù)增、刪、改、查工具類
java連接數(shù)據(jù)庫(kù)增、刪、改、查工具類
數(shù)據(jù)庫(kù)操作工具類，因?yàn)楦鲝S家數(shù)據(jù)庫(kù)的分頁(yè)條件不同，目前支持Mysql、Oracle、Postgresql的分頁(yè)查詢
在Postgresql環(huán)境測(cè)試過(guò)了，其他數(shù)據(jù)庫(kù)未測(cè)試。
sql語(yǔ)句需要使用預(yù)編譯形式的
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;
/**
* 數(shù)據(jù)庫(kù)查詢工具類
* 使用預(yù)編譯的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;
}
/**
* 關(guān)閉連接
*
* @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;
}
}
/**
* 利用正則表達(dá)式,獲得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;
}
/**
* 利用正則表達(dá)式,獲得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;
}
/**
* 利用正則表達(dá)式,獲得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添加統(tǒng)計(jì)代碼
*
* @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添加分頁(yè)代碼
*
* @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數(shù)據(jù)庫(kù)（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對(duì)象實(shí)例化T對(duì)象
*
* @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中的參數(shù)添加到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中的參數(shù)添加到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);
}
}
}
/**
* 執(zhí)行insert操作
*
* @param sql 預(yù)編譯的sql語(yǔ)句
* @param t sql中的參數(shù)
* @return 執(zhí)行行數(shù)
* @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;
}
/**
* 執(zhí)行insert操作
*
* @param sql 預(yù)編譯的sql語(yǔ)句
* @param param 參數(shù)
* @return 執(zhí)行行數(shù)
* @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;
}
/**
* 執(zhí)行update操作
*
* @param sql 預(yù)編譯的sql語(yǔ)句
* @param t sql中的參數(shù)
* @return 執(zhí)行行數(shù)
* @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;
}
/**
* 執(zhí)行update操作
*
* @param sql
* @param param 參數(shù)
* @return 執(zhí)行行數(shù)
* @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;
}
/**
* 查詢復(fù)數(shù)的對(duì)象
*
* @param t 查詢結(jié)果封裝的對(duì)象類型
* @param sql 預(yù)編譯的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;
}
/**
* 分頁(yè)查詢復(fù)數(shù)的對(duì)象
*
* @param t 查詢結(jié)果封裝的對(duì)象類型
* @param start 開始頁(yè)
* @param limit 頁(yè)大小
* @param sql 預(yù)編譯的sql語(yǔ)句
* @param param 查詢參數(shù)
* @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();
//添加分頁(yè)代碼
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;
}
/**
* 查詢單個(gè)的對(duì)象
*
* @param t 查詢結(jié)果對(duì)象
* @param sql 預(yù)編譯的sql
* @param param 查詢參數(shù)
* @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;
}
/**
* 查詢數(shù)據(jù)量
*
* @param param 查詢參數(shù)
* @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;
}
/**
* 屬性字段的注釋，用于標(biāo)記該屬性對(duì)應(yīng)的數(shù)據(jù)庫(kù)字段
* 例如：
* @Column(name="user_name");
* String userName;
* 表示userName這個(gè)屬性對(duì)應(yīng)的數(shù)據(jù)庫(kù)字段是user_name
*
* 如果屬性和數(shù)據(jù)庫(kù)字段完全一致，則不必標(biāo)注
* @author xueliang
*/
@Target({ ElementType.FIELD })
@Retention(RetentionPolicy.RUNTIME)
public @interface Column{
String name() default "";
}
}
相關(guān)文章
SpringCloud使用AOP統(tǒng)一處理Web請(qǐng)求日志實(shí)現(xiàn)步驟
這篇文章主要為大家介紹了SpringCloud使用AOP統(tǒng)一處理Web請(qǐng)求日志實(shí)現(xiàn)步驟,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-08-08
本篇文章主要介紹了Java復(fù)習(xí)之集合框架總結(jié)，小編覺(jué)得挺不錯(cuò)的，現(xiàn)在分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-09-09
這篇文章主要介紹了mybatis如何批量更新list對(duì)象問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
java使用PDFRenderer實(shí)現(xiàn)預(yù)覽PDF功能
這篇文章主要為大家詳細(xì)介紹了java使用PDFRenderer實(shí)現(xiàn)預(yù)覽PDF功能，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-12-12
Java?代碼本地設(shè)置Hadoop用戶名密碼的方法
在Hadoop環(huán)境中,通常使用Kerberos進(jìn)行身份驗(yàn)證,這篇文章主要介紹了Java?代碼本地設(shè)置Hadoop用戶名密碼的方法,需要的朋友可以參考下2024-08-08
Spring Cloud 優(yōu)雅下線以及灰度發(fā)布實(shí)現(xiàn)
這篇文章主要介紹了Spring Cloud 優(yōu)雅下線以及灰度發(fā)布實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
這篇文章主要介紹了如何在Spring Boot中使用MQTT，幫助大家更好的理解和學(xué)習(xí)使用Spring Boot，感興趣的朋友可以了解下2021-04-04
在項(xiàng)目開發(fā)地過(guò)程中經(jīng)常會(huì)用到字典存儲(chǔ)某些可配置的值，但相應(yīng)的也會(huì)給開發(fā)帶來(lái)復(fù)雜度，比如數(shù)據(jù)庫(kù)存儲(chǔ)的值為字典值：0，1,2,3這種類型的值，但是前端頁(yè)面卻需要展示為 啟動(dòng)、禁用、刪除 等中文狀態(tài)，下面我們來(lái)看兩種解決方案2022-06-06
Java中Spock框架Mock對(duì)象的方法經(jīng)驗(yàn)總結(jié)
這篇文章主要分享了Spock框架Mock對(duì)象的方法經(jīng)驗(yàn)總結(jié)，下文分享一些常用項(xiàng)目實(shí)戰(zhàn)說(shuō)明以及代碼，供大家項(xiàng)目中參考，也具有一的的參考價(jià)值，需要的小伙伴可以參考一下2022-02-02
Java如何獲取Json中的數(shù)據(jù)實(shí)例代碼
這篇文章主要給大家介紹了關(guān)于Java如何獲取Json中數(shù)據(jù)的相關(guān)資料,我們?cè)谌粘ｉ_發(fā)中少不了和JSON數(shù)據(jù)打交道,那么我們來(lái)看看JAVA中常用的JSON獲取方式,需要的朋友可以參考下2023-09-09