JAVA JDBC 常規增刪改查簡單封裝,可滿足大多基本要求
作用：
1，?查詢列表是直接返回List對象，不必再遍歷；
2， 單條查詢直接返回對象；
3， 執行sql僅需一個方法搞定；
package com.Main.Tools;
import com.Main.DB.MySql.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description: JDBC輔佐工具類
* @author: Adobe Chow
* @date: 2018/12/29 16:02
* @Copyright: www.winshang.com Inc. All rights reserved.
*/
public class SqlUtils {
private static final String LogName = "SqlUtils----> ";
/**
* 執行sql
* @param conn
* @param sql
* @param args
* @throws SQLException
*/
public static void execute(Connection conn,String sql,Object ...args) throws SQLException {
PreparedStatement ptmt = null;
ptmt = conn.prepareStatement(sql);
String para = setParam(ptmt,args);
System.out.println(LogName+"sql: "+sql);
System.out.println(LogName+"參數: "+para);
ptmt.execute();
ptmt.close();
System.out.println(LogName+"執行成功！");
}
/**
* 查詢列表
* @param conn
* @param sql
* @param args
* @return
* @throws SQLException
*/
public static List> findList(Connection conn,String sql,Object...args) {
List> list = new ArrayList<>();
PreparedStatement ptmt = null;
try {
ptmt = conn.prepareStatement(sql);
System.out.println(LogName+"sql: "+sql);
String para = setParam(ptmt,args);
System.out.println(LogName+"sql: "+sql);
System.out.println(LogName+"參數: "+para);
ResultSet resultSet = ptmt.executeQuery();
ResultSetMetaData rsmd=resultSet.getMetaData();
while (resultSet.next()){
Map map = new HashMap<>();
for(int i=0;i
String columnLabel=rsmd.getColumnLabel(i+1);
Object columnValue=resultSet.getObject(columnLabel);
map.put(columnLabel, columnValue);
}
list.add(map);
}
if (list.size()==0){
return null;
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
ptmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 查詢單條數據
* @param conn
* @param sql
* @param args
* @return
* @throws SQLException
*/
public static Map findFirst(Connection conn,String sql,Object...args) {
List> list = findList(conn,sql+" limit 1",args);
return list==null?null:list.get(0);
}
/**
* 設置參數
* @param ptmt
* @param args
* @return
*/
private static String setParam(PreparedStatement ptmt, Object[] args) throws SQLException {
//加入參數
String parasStr = "";
if (args!=null){
for(int i=0;i
ptmt.setObject(i+1, args[i]);
parasStr=parasStr+" "+args[i];
}
}
return parasStr;
}
public static void main(String[] args) {
//執行sql
String sql = "INSERT INTO `biz_t_error` ( `uid`, `bunkId`, `errType`, `errMsg`, `errUrl`, `addtime`, `errSql`) VALUES (?, '3250', '匹配錯誤', '測試插入啦啦啦！', 'http://data.winshang.com/Pipei_Pp.aspx', ?, 'zdb 測試數據');";
try {
execute(DBUtil.getConnection(),sql,"1314",new Date());
} catch (SQLException e) {
e.printStackTrace();
}
//查詢列表
List> list = findList(DBUtil.getConnection(),"select * from biz_t_error LIMIT 100",null);
for (int i=0;i
System.out.println("多條記錄："+list.get(i).get("errMsg"));
}
//查詢單條記錄
Map map = findFirst(DBUtil.getConnection(),"select * from biz_t_error order by addtime desc",null);
System.out.println( "單條記錄： "+ map.get("uid")+" "+map.get("errMsg") );
}
}
