JDBC工具类模版

package JavaEndWork;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;

import org.slf4j.LoggerFactory;
import org.slf4j.Logger;
import com.alibaba.druid.pool.DruidDataSourceFactory;
/**
* @author 周琨
*JDBC助手类
*/
public class JdbcHelper {
private static DataSource dataSource;
private static final Logger log = LoggerFactory.getLogger(JdbcHelper.class);
private static JdbcHelper instance;
/*
* 初始化数据库
*/
static {
try {
Properties prop = new Properties();
prop.load(JdbcHelper.class.getClassLoader().getResourceAsStream("java.properties"));
System.out.println(prop.getProperty("url"));
dataSource = DruidDataSourceFactory.createDataSource(prop);
}catch (Exception e) {
log.error(e.getMessage(),e);
}
}

/*
* 构造方法私有化,外部不允许创建该实例
*/
private JdbcHelper() {

}

/*
* 获取数据库连接
* {@link Connection}
* @throws SQLException
*/

public Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
log.error(e.getMessage(),e);
throw new RuntimeException(e);
}
}

/**
* 释放资源
* @param rs 结果集
* @param stat 语句对象
* @param conn 连接对象
*/

public void free(ResultSet rs, Statement stat, Connection conn) {
try {
if(rs != null)
rs.close();
} catch (SQLException e) {
log.error(e.getMessage(), e);
} finally {
try {
if(stat != null)
((Connection) stat).close();
} catch (SQLException e) {
log.error(e.getMessage(), e);
} finally {
try {
if(conn != null)
conn.close();
} catch (SQLException e) {
log.error(e.getMessage(), e);
}
}
}
}

/**
* 获取该类的唯一实例
* @return 唯一实例
*/
public static JdbcHelper getInstance() {
if(instance == null) {
synchronized (JdbcHelper.class) {
if(instance == null)
instance = new JdbcHelper();
}
}
return instance;
}

/**
* 获取数据源
* @return 数据源
*/
public static DataSource getDataSource() {
return dataSource;
}
}

 

 

 

package JavaEndWork;

import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.RowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
* @author 周琨
*
*/
public class memberDaoHelp {
private JdbcHelper helper = JdbcHelper.getInstance();
private static final Logger log = LoggerFactory.getLogger(memberDaoHelp.class);
private static memberDaoHelp user = new memberDaoHelp();
private memberDaoHelp() {};

public int executeUpdate(String sql,Object...parameters) {
//获取数据库连接
var conn = helper.getConnection();
try {
//预编译语句对象
var ptst = conn.prepareStatement(sql);
//对预编译语句对象中的？参数设值
for(var i = 0; parameters != null && i < parameters.length;i ++) {
ptst.setObject(i + 1, parameters[i]);
}
return ptst.executeUpdate();
} catch (SQLException e) {
log.error(e.getMessage(), e);
}finally {
helper.free(null, null, conn);
}
return 0;
}

public static memberDaoHelp getmember() {
return user;
}

/**
* insert操作,返回自增主键
* @param sql insert语句
* @param parameters sql语句中?参数列表值
* @return 自增的主键
*/

public Integer insert(String sql,Object...parameters) {
Integer result = null;
//获取数据库连接
var conn = helper.getConnection();
try {
//预编译语句对象
var ptst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
//对预编译语句对象中的？设值
for(var i = 0; parameters != null && i < parameters.length;i ++) {
ptst.setObject(i + 1, parameters[i]);
}
//执行dml语句
ptst.executeUpdate();
//从结果中获取自增主键
var rs = ptst.getGeneratedKeys();
if(rs.next())
return rs.getInt(1);
} catch (SQLException e) {
log.error(e.getMessage(), e);
}finally {
helper.free(null, null, conn);
}
return result;
}
/**
* 采用模板方法模式封装dql语句的处理逻辑
* @param sql select语句
* @param parameters sql语句中?参数列表值
* @return 查询结果集
*/
public RowSet executeQuery(String sql,Object...parameters) {
var conn = helper.getConnection();
try {
var ptst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
for(var i = 0; parameters != null && i < parameters.length; i ++) {
ptst.setObject(i +1, parameters[i]);
}
var rs = ptst.executeQuery();
RowSetFactory factory = RowSetProvider.newFactory();
//通过RowSetFactory把应用程序和RowSet实现类分离开
var rowset = factory.createCachedRowSet();
//将结果集中的内容转装载进RoeSet中
rowset.populate(rs);
return rowset;
} catch (SQLException e) {
log.error(e.getMessage(), e);
return null;
}finally {
helper.free(null, null, conn);
}
}
}