欅坂46
前言
有時(shí)候要需要寫(xiě)個(gè)小程序之類的络拌，懶得去搭什么框架。直接用jdbc多好回溺。
更具不同的業(yè)務(wù)需求春贸，具體操作不同混萝。
方案一、jdbc基本連接與數(shù)據(jù)請(qǐng)求
一般不涉及多線程萍恕，高并發(fā)時(shí)逸嘀，次工具類基本夠用了
package com.security.common.util;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* JDBC工具類
* Created by Administrator on 2017/1/10.
*/
public class JdbcUtil {
// 定義數(shù)據(jù)庫(kù)的鏈接
private Connection conn;
// 定義sql語(yǔ)句的執(zhí)行對(duì)象
private PreparedStatement pstmt;
// 定義查詢返回的結(jié)果集合
private ResultSet rs;
// 初始化
public JdbcUtil(String driver, String url, String username, String password) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
System.out.println("數(shù)據(jù)庫(kù)連接成功");
} catch (Exception e) {
e.printStackTrace();
}
}
// 更新/刪除數(shù)據(jù)
/**
* @parm params List<String> 類型
**/
public boolean updateByParams(String sql, List params) throws SQLException {
// 影響行數(shù)
int result = -1;
pstmt = conn.prepareStatement(sql);
int index = 1;
// 填充sql語(yǔ)句中的占位符
if (null != params && !params.isEmpty()) {
for (int i = 0; i < params.size(); i ++) {
pstmt.setObject(index ++, params.get(i));
}
}
result = pstmt.executeUpdate();
return result > 0 ? true : false;
}
// 查詢多條記錄
public List<Map> selectByParams(String sql, List params) throws SQLException {
List<Map> list = new ArrayList<> ();
int index = 1;
pstmt = conn.prepareStatement(sql);
if (null != params && !params.isEmpty()) {
for (int i = 0; i < params.size(); i ++) {
pstmt.setObject(index++, params.get(i));
}
}
rs = pstmt.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int cols_len = metaData.getColumnCount();
while (rs.next()) {
Map map = new HashMap();
for (int i = 0; i < cols_len; i ++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = rs.getObject(cols_name);
if (null == cols_value) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
// 釋放連接
public void release() {
try {
if (null != rs) rs.close();
if (null != pstmt) pstmt.close();
if (null != conn) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("釋放數(shù)據(jù)庫(kù)連接");
}
}
方案二、將工具類封裝處理
稍微多了點(diǎn)內(nèi)容允粤，但是也沒(méi)有復(fù)雜的數(shù)據(jù)處理
數(shù)據(jù)庫(kù)配置文件讀取方法
package com.iflytek.jdbcdemo;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* 數(shù)據(jù)庫(kù)配置文件讀取方法
* @author administrator
*
*/
public class DbConfig {
private String driver;
private String url;
private String userName;
private String password;
public DbConfig() {
InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("com/iflytek/jdbcdemo/dbConfig.properties");
Properties p=new Properties();
try {
p.load(inputStream);
this.driver=p.getProperty("driver");
this.url=p.getProperty("url");
this.userName=p.getProperty("username");
this.password=p.getProperty("passwrod");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public String getDriver() {
return driver;
}
public String getUrl() {
return url;
}
public String getUserName() {
return userName;
}
public String getPassword() {
return password;
}
}
**jdbc工具類 **
package com.iflytek.jdbcdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* jdbc工具類
*
* @author administrator
*
*/
public final class JdbcUnits {
/**
* 數(shù)據(jù)庫(kù)連接地址
*/
private static String url ;
/**
* 用戶名
*/
private static String userName ;
/**
* 密碼
*/
private static String password;
private static String driver;
/**
* 裝載驅(qū)動(dòng)
*/
static {
DbConfig config=new DbConfig();
url=config.getUrl();
userName=config.getUserName();
password=config.getPassword();
driver=config.getDriver();
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* 建立數(shù)據(jù)庫(kù)連接
*
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
conn = DriverManager.getConnection(url, userName, password);
return conn;
}
/**
* 釋放連接
* @param conn
*/
private static void freeConnection(Connection conn) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 釋放statement
* @param statement
*/
private static void freeStatement(Statement statement) {
try {
statement.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 釋放resultset
* @param rs
*/
private static void freeResultSet(ResultSet rs) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 釋放資源
*
* @param conn
* @param statement
* @param rs
*/
public static void free(Connection conn, Statement statement, ResultSet rs) {
if (rs != null) {
freeResultSet(rs);
}
if (statement != null) {
freeStatement(statement);
}
if (conn != null) {
freeConnection(conn);
}
}
}
**數(shù)據(jù)庫(kù)訪問(wèn)幫助類 **
package com.iflytek.jdbcdemo;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 數(shù)據(jù)庫(kù)訪問(wèn)幫助類
*
* @author administrator
*
*/
public class JdbcHelper {
private static Connection conn = null;
private static PreparedStatement preparedStatement = null;
private static CallableStatement callableStatement = null;
/**
* 用于查詢厘熟，返回結(jié)果集
*
* @param sql
* sql語(yǔ)句
* @return 結(jié)果集
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List query(String sql) throws SQLException {
ResultSet rs = null;
try {
getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 用于帶參數(shù)的查詢，返回結(jié)果集
*
* @param sql
* sql語(yǔ)句
* @param paramters
* 參數(shù)集合
* @return 結(jié)果集
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List query(String sql, Object... paramters)
throws SQLException {
ResultSet rs = null;
try {
getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 返回單個(gè)結(jié)果的值维哈，如count\min\max等等
*
* @param sql
* sql語(yǔ)句
* @return 結(jié)果集
* @throws SQLException
*/
public static Object getSingle(String sql) throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 返回單個(gè)結(jié)果值绳姨，如count\min\max等
*
* @param sql
* sql語(yǔ)句
* @param paramters
* 參數(shù)列表
* @return 結(jié)果
* @throws SQLException
*/
public static Object getSingle(String sql, Object... paramters)
throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 用于增刪改
*
* @param sql
* sql語(yǔ)句
* @return 影響行數(shù)
* @throws SQLException
*/
public static int update(String sql) throws SQLException {
try {
getPreparedStatement(sql);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 用于增刪改（帶參數(shù)）
*
* @param sql
* sql語(yǔ)句
* @param paramters
* sql語(yǔ)句
* @return 影響行數(shù)
* @throws SQLException
*/
public static int update(String sql, Object... paramters)
throws SQLException {
try {
getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 插入值后返回主鍵值
*
* @param sql
* 插入sql語(yǔ)句
* @return 返回結(jié)果
* @throws Exception
*/
public static Object insertWithReturnPrimeKey(String sql)
throws SQLException {
ResultSet rs = null;
Object result = null;
try {
conn = JdbcUnits.getConnection();
preparedStatement = conn.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.execute();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
}
}
/**
* 插入值后返回主鍵值
*
* @param sql
* 插入sql語(yǔ)句
* @param paramters
* 參數(shù)列表
* @return 返回結(jié)果
* @throws SQLException
*/
public static Object insertWithReturnPrimeKey(String sql,
Object... paramters) throws SQLException {
ResultSet rs = null;
Object result = null;
try {
conn = JdbcUnits.getConnection();
preparedStatement = conn.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
preparedStatement.execute();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
}
}
/**
* 調(diào)用存儲(chǔ)過(guò)程執(zhí)行查詢
*
* @param procedureSql
* 存儲(chǔ)過(guò)程
* @return
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List callableQuery(String procedureSql) throws SQLException {
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
rs = callableStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 調(diào)用存儲(chǔ)過(guò)程（帶參數(shù)）,執(zhí)行查詢
*
* @param procedureSql
* 存儲(chǔ)過(guò)程
* @param paramters
* 參數(shù)表
* @return
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List callableQuery(String procedureSql, Object... paramters)
throws SQLException {
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
for (int i = 0; i < paramters.length; i++) {
callableStatement.setObject(i + 1, paramters[i]);
}
rs = callableStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 調(diào)用存儲(chǔ)過(guò)程，查詢單個(gè)值
*
* @param procedureSql
* @return
* @throws SQLException
*/
public static Object callableGetSingle(String procedureSql)
throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
rs = callableStatement.executeQuery();
while (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
/**
* 調(diào)用存儲(chǔ)過(guò)程(帶參數(shù))阔挠，查詢單個(gè)值
*
* @param procedureSql
* @param parameters
* @return
* @throws SQLException
*/
public static Object callableGetSingle(String procedureSql,
Object... paramters) throws SQLException {
Object result = null;
ResultSet rs = null;
try {
getCallableStatement(procedureSql);
for (int i = 0; i < paramters.length; i++) {
callableStatement.setObject(i + 1, paramters[i]);
}
rs = callableStatement.executeQuery();
while (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free(rs);
}
}
public static Object callableWithParamters(String procedureSql)
throws SQLException {
try {
getCallableStatement(procedureSql);
callableStatement.registerOutParameter(0, Types.OTHER);
callableStatement.execute();
return callableStatement.getObject(0);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 調(diào)用存儲(chǔ)過(guò)程飘庄，執(zhí)行增刪改
*
* @param procedureSql
* 存儲(chǔ)過(guò)程
* @return 影響行數(shù)
* @throws SQLException
*/
public static int callableUpdate(String procedureSql) throws SQLException {
try {
getCallableStatement(procedureSql);
return callableStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 調(diào)用存儲(chǔ)過(guò)程（帶參數(shù)），執(zhí)行增刪改
*
* @param procedureSql
* 存儲(chǔ)過(guò)程
* @param parameters
* @return 影響行數(shù)
* @throws SQLException
*/
public static int callableUpdate(String procedureSql, Object... parameters)
throws SQLException {
try {
getCallableStatement(procedureSql);
for (int i = 0; i < parameters.length; i++) {
callableStatement.setObject(i + 1, parameters[i]);
}
return callableStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
free();
}
}
/**
* 批量更新數(shù)據(jù)
*
* @param sqlList
* 一組sql
* @return
*/
public static int[] batchUpdate(List<String> sqlList) {
int[] result = new int[] {};
Statement statenent = null;
try {
conn = JdbcUnits.getConnection();
conn.setAutoCommit(false);
statenent = conn.createStatement();
for (String sql : sqlList) {
statenent.addBatch(sql);
}
result = statenent.executeBatch();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
throw new ExceptionInInitializerError(e1);
}
throw new ExceptionInInitializerError(e);
} finally {
free(statenent, null);
}
return result;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
private static List ResultToListMap(ResultSet rs) throws SQLException {
List list = new ArrayList();
while (rs.next()) {
ResultSetMetaData md = rs.getMetaData();
Map map = new HashMap();
for (int i = 1; i < md.getColumnCount(); i++) {
map.put(md.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
}
return list;
}
/**
* 獲取PreparedStatement
*
* @param sql
* @throws SQLException
*/
private static void getPreparedStatement(String sql) throws SQLException {
conn = JdbcUnits.getConnection();
preparedStatement = conn.prepareStatement(sql);
}
/**
* 獲取CallableStatement
*
* @param procedureSql
* @throws SQLException
*/
private static void getCallableStatement(String procedureSql)
throws SQLException {
conn = JdbcUnits.getConnection();
callableStatement = conn.prepareCall(procedureSql);
}
/**
* 釋放資源
*
* @param rs
* 結(jié)果集
*/
public static void free(ResultSet rs) {
JdbcUnits.free(conn, preparedStatement, rs);
}
/**
* 釋放資源
*
* @param statement
* @param rs
*/
public static void free(Statement statement, ResultSet rs) {
JdbcUnits.free(conn, statement, rs);
}
/**
* 釋放資源
*/
public static void free() {
free(null);
}
}
