做Java開發的朋友可能經常要與資料庫打交道,而如果每次都寫一大堆的擷取連接配接,執行sql語句的方法未免太過笨拙,下面提供一種以幫助類的形式解決JDBC連接配接資料庫的問題,可比較輕松地解決基本的CRUD操作
下面是該類的全部程式:
package com.bbs2.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* JDBC的幫助類
* @author yu
*
* @param <T>
*/
public class DBHelp<T> {
private final String DRIVER = "com.mysql.jdbc.Driver"; //指定資料庫驅動
private final String URL = "jdbc:mysql:///bbs2"; //要連接配接的資料庫
/**
* 擷取連接配接
* @return
*/
public Connection getConnection() {
try {
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(URL,"root","root");
return conn;
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
/**
* 擷取自動增長的ID
* @param sql save()語句
* @param args 參數清單
* @return 剛剛插入的ID
*/
public int getLastInsertId(String sql,Object...args){
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
int lastId = 0;
try {
conn = this.getConnection();
stat = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < args.length; i++) { //循環給參數清單指派
stat.setObject(i+1, args[i]);
}
stat.executeUpdate();
rs = stat.getGeneratedKeys(); //擷取自動增長的ID
if(rs.next()){
lastId = rs.getInt(1);
System.out.println("latid:"+lastId);
}
System.out.println("SQL:"+sql);
} catch (SQLException e) {
System.out.println("系統出錯");
} finally{
this.close(rs, stat, conn);
}
return lastId;
}
/**
* 擷取總記錄數
* @param sql 使用聚合函數的語句
* @param args 參數清單
* @return
*/
public int queryForInt(String sql,Object...args){
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
int num = 0;
try {
conn = this.getConnection();
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1, args[i]);
}
rs = stat.executeQuery();
if(rs.next()){
num = rs.getInt(1);
}
System.out.println("SQL:"+sql);
} catch (SQLException e) {
System.out.println("系統出錯");
} finally{
this.close(rs, stat, conn);
}
return num;
}
/**
* 查詢一組對象
* @param sql
* @param mapper 對象的RowMap
* @param args
* @return
*/
public List<T> queryForList(String sql,RowMapper<T> mapper,Object...args) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
List<T> list = new ArrayList<T>();
conn = this.getConnection();
try {
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1, args[i]);
}
rs = stat.executeQuery();
while(rs.next()){
//每調用一次mapper對象的mapRow(rs)方法,會産生一個新的對象
T obj = mapper.mapRow(rs);
list.add(obj);
}
System.out.println("SQL:" + sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close(rs, stat, conn);
}
return list;
}
/**
* 查詢對象
* @param sql
* @param mapper
* @param args
* @return
*/
public T queryForObject(String sql,RowMapper<T> mapper,Object...args) {
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
T obj = null;
conn = this.getConnection();
try {
stat = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
stat.setObject(i+1, args[i]);
}
rs = stat.executeQuery();
if(rs.next()){
//每調用一次mapper對象的mapRow(rs)方法,會産生一個新的對象
obj = mapper.mapRow(rs);
}
System.out.println("SQL:" + sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close(rs, stat, conn);
}
return obj;
}
/**
* 執行insert,update,delete操作
* @param sql
* @param args
* @return
*/
public int executeUpdate(String sql,Object... args){
Connection conn = null;
PreparedStatement state = null;
int rows = 0;
try {
conn = this.getConnection();
state = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
state.setObject(i+1, args[i]);
}
rows = state.executeUpdate();
System.out.println("SQL:"+sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
this.close(state, conn);
}
return rows;
}
/**
* 關閉連接配接
* @param rs
* @param stat
* @param conn
*/
public void close(ResultSet rs,Statement stat,Connection conn) {
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(stat != null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 關閉連接配接
* @param stat
* @param conn
*/
public void close(Statement stat,Connection conn) {
try {
if(stat != null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
其中執行insert。update,delete操作的時候無需過多解釋,PreparedStatement方式的sql,調用executeUpdate()方法即可,執行查詢操作的時候配合下面的接口RowMapper使用,每個實體類,都應該有一個mapper類與之對應,推薦使用内部類的形式,效果更好:
package com.bbs2.util;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 實作實體類的set方法的接口,每個實體類,都應該有一個mapper類與之對應
* 推薦使用内部類的形式
* @author yu
*
* @param <T> 實體類
*/
public interface RowMapper<T> {
T mapRow(ResultSet rs) throws SQLException;
}
例如:登入操作,就可這麼來做:
private DBHelp<User> db = new DBHelp<User>();
public User findByNameAndPwd(String name,String pwd){
String sql = "SELECT id,NAME,PASSWORD,regTime,email,flag,pic FROM t_user where name=? and password=?";
return db.queryForObject(sql, new UserMap(), name,pwd);
}
其中UserMap()是實作了RowMapper接口的内部類:
public User mapRow(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setRegTime(rs.getString("regTime"));
user.setEmail(rs.getString("email"));
user.setFlag(rs.getInt("flag"));
user.setPic(rs.getString("pic"));
return user;
}
可以看到,這裡面就是簡單的給執行個體指派
這麼做可以在很大程度上實作代碼的複用,例如,下面的注冊操作:
public int save(User user) {
String sql = "INSERT INTO t_user(NAME,PASSWORD,email,flag,pic,regTime) VALUES(?,?,?,?,?,?)";
return db.executeUpdate(sql, user.getName(),user.getPassword(),user.getEmail(),user.getFlag(),user.getPic(),user.getRegTime());
}
可以看到,雖然剛開始的配置是有一些複雜,但是,一旦配置完後,每個CRUD操作就是簡單的兩行語句