新公司的ORM框架使用了hibernate,但是我并不会,刚来项目老板催的紧,而且项目还是我独立开发,所以就自己用JDBC完成功能,但是你懂的,jdbc代码的冗余,操作的复杂都是我们初学就很烦的事儿了,所以花了半天时间写了一个BaseDao出来,代码如下:
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.uqiauto.util.ConnectionUtils;
/**
* dao层父类
* 封装了大部分的增删改查代码,但是关键实现都设置为抽象,继承此类需要提供具体实现
* 因为父类try了SQLException,如果声明其他异常可能会导致程序终止
* 所以如果需要限制访问权限,可以在子类实现中声明SQLException或其子类异常
* 例:限制用户添加 throw new SQLException("This table is not allowed to be added");
*/
public abstract class BaseDao<T>{
private final Class<T> entityClass;
private final String entityClassName;
@SuppressWarnings("unchecked")
public BaseDao() {
// 通过范型反射,获取在子类中定义的entityClass.
this.entityClass = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
entityClassName = entityClass.getSimpleName();
}
/**
* 获取实体类对象
*/
public Class<T> getEntityClass() {
return entityClass;
}
/**
* 获取实体类名(不包括包结构)
*/
public String getEntityClassName() {
return entityClassName;
}
/**
* 调用子类方法的入口,子类中只需要提供toObject方法的实现
* 查询无参实现
* @param sql
* @param values
* @return
*/
public T queryOne(String sql){
T data = null;
try {
data = this.entityClass.newInstance();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Connection conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
toObject(rs, data);
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
}
} catch (InstantiationException | IllegalAccessException e1) {
e1.printStackTrace();
}
return data;
}
/**
* 由子类继承,父类中不提供任何实现
* @param rs
* 查询返回的结果集
* @param data
* 查询后保存的对象
* @return
* 参数data
* @throws SQLException
* 必须处理异常
*/
protected abstract void toObject(ResultSet rs, T entity) throws SQLException;
/**
* 查询结果数量
* @param sql
* @return
*/
public int queryCount(String sql){
int count = 0;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Connection conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
count = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
}
return count;
}
/**
* 调用子类方法的入口,子类中只需要提供toObjectOfList方法的实现
* 查询不带参数实现
* @param sql
* @param values
* @return
*/
public List<T> queryList(String sql){
List<T> data = new ArrayList<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Connection conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
toObjectOfList(rs, data);
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
}
return data;
}
/**
* 分页查询,注意调用者必须验证返回值中的success函数
* @param sql
* 查询的SQL语句,不需要limit
* @param curPage
* 当前页码
* @param pageSize
* 每页显示条数
* @return
* 保存分页相关参数的map集合
* totalRows:数据条数
* page:页数
* data:返回数据
* success:验证分页 true->成功 false->失败
*/
public Map<String, Object> queryListByPage(String sql, int curPage, int pageSize){
Map<String, Object> map = new HashMap<>();
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Connection conn = ConnectionUtils.getConnection();
String countSql = sql.replace("*", "count(*)");
pstmt = conn.prepareStatement(countSql);
rs = pstmt.executeQuery();
int count = rs.next() ? rs.getInt(1) : 0;
int pageCount = count%pageSize == 0 ? count/pageSize : count/pageSize+1;
map.put("totalRows", count);
map.put("page", pageCount);
List<T> data = new ArrayList<>();
int begin = (curPage-1)*pageSize+1;
sql = sql.contains("where") ? sql + " limit " : sql + " where 1=1 limit ";
sql = sql + begin + "," + pageSize;
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
toObjectOfList(rs, data);
map.put("data", data);
map.put("success", true);
} catch (SQLException e) {
map.put("success", false);
e.printStackTrace();
}finally {
ConnectionUtils.closeAll(rs, pstmt, Boolean.TRUE);
}
return map;
}
/**
* 可以由子类继承,父类中所提供默认实现的内部是由子类实现的toObject方法
* @param rs
* 查询返回的结果集
* @param data
* 查询后保存的集合
* @return
* 参数data
* @throws SQLException
* 必须处理异常
*/
protected List<T> toObjectOfList(ResultSet rs, List<T> data) throws SQLException{
if(rs != null && data != null){
int rowCount = rs.last() ? rs.getRow() : 0;
rs.beforeFirst();
try {
T t;
for(int i=0; i< rowCount; i++){
t = this.entityClass.newInstance();
toObject(rs, t);
data.add(t);
}
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
}
}
return data;
}
/**
* 添加一条数据方法,参数放到SQL中直接执行
* @param sql 执行SQL
* @return 验证操作成功
*/
public boolean insert(String sql){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
conn.commit();
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
/**
* 添加一条数据方法,参数的传递使用实体的值和实体对应dao层封装的赋值方法
* @param sql 执行SQL
* @param t 带参数的实体对象
* @return 验证操作成功
*/
public boolean insert(String sql, T entity){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
doInsertSetPstmt(pstmt, entity);
pstmt.executeUpdate();
conn.commit();
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
/**
* 批量添加数据,参数的传递使用实体的值和实体对应dao层封装的赋值方法
* @param sql 执行SQL
* @param dataList 保存带参数实体对象的集合
* @return 验证操作成功
*/
public boolean batchInsert(String sql, List<T> dataList){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
int count = 0;
for (T t : dataList) {
doInsertSetPstmt(pstmt, t);
pstmt.addBatch();
if(++count == 500){
pstmt.executeBatch();
count = 0;
}
}
pstmt.executeBatch();
conn.commit();
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
/**
* 由子类继承,父类中不提供任何实现
* @param pstmt
* @param entity
* @throws SQLException
*/
protected abstract void doInsertSetPstmt(PreparedStatement pstmt, T entity) throws SQLException;
/**
* 删除SQL中的数据
* @param sql
* @return
*/
public boolean delete(String sql){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
doDelete(conn);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
/**
* 根据id集合批量删除
* @param sql
* @param ids
* @return
*/
public boolean batchDelete(String sql, List<Integer> ids){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
int count = 0;
for (int i : ids) {
pstmt.setInt(1, i);
pstmt.addBatch();
if(++count == 500){
pstmt.executeBatch();
count = 0;
}
}
pstmt.executeBatch();
doDelete(conn);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
protected abstract void doDelete(Connection conn) throws SQLException;
/**
* 修改SQL中的数据
* @param sql
* @return
*/
public boolean update(String sql){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
conn.commit();
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
/**
* 批量修改集合中的所有数据
* @param sql
* @param dataList
* @return
*/
public boolean batchUpdate(String sql, List<T> dataList){
PreparedStatement pstmt = null;
Connection conn = null;
try {
conn = ConnectionUtils.getConnection();
pstmt = conn.prepareStatement(sql);
conn.setAutoCommit(false);
int count = 0;
for (T t : dataList) {
doUpdateSetPstmt(pstmt, t);
pstmt.addBatch();
if(++count == 500){
pstmt.executeBatch();
count = 0;
}
}
pstmt.executeBatch();
conn.commit();
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return false;
}finally{
ConnectionUtils.closeAll(null, pstmt, Boolean.TRUE);
}
}
protected abstract void doUpdateSetPstmt(PreparedStatement pstmt, T entity) throws SQLException;
public java.sql.Date toSqlDate(Date date){
java.sql.Date sqlDate = new java.sql.Date(new Date().getTime());
if(date != null)
sqlDate = new java.sql.Date(date.getTime());
return sqlDate;
}
public Date toUtilDate(java.sql.Date sqlDate){
Date date = new Date();
if(sqlDate != null)
date = new Date(sqlDate.getTime());
return date;
}
}
连接MySQL数据库的工具类:
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ConnectionUtils {
// 线程单例
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
private static String url;
private static String username;
private static String password;
static {
// 装载驱动参数
try {
ClassLoader classLoader = ConnectionUtils.class.getClassLoader();
InputStream is = classLoader.getResourceAsStream("standard.properties");
Properties props = new Properties();
props.load(is);
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
// 注册驱动
Class.forName(props.getProperty("jdbc.driverName"));
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
Connection con = tl.get();
if (con == null || con.isClosed()) {
con = DriverManager.getConnection(url, username, password);
tl.set(con);
}
return con;
}
public static void closeConnection() {
Connection conn = tl.get();
if (conn == null)
return;
try {
if (!conn.isClosed()) {
//关闭数据库连接
conn.close();
}
} catch (SQLException e) {
System.err.println("#ERROR# :关闭数据库连接发生异常,请检查!\n" + e.getMessage());
}
}
public static void closeAll(ResultSet rs, Statement stmt, boolean closeConn) {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if(closeConn)
closeConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
}
实体类如下:
public class Area{
//----------- object properties
private Integer id;
private String name;
private Integer parent_id;
private Byte sort;
private Byte deep;
private String city_code;
private String region;
private Integer status;
private Integer ad_code;
//------------ database columns
public static final String ID = "AREA_ID";
public static final String NAME = "AREA_NAME";
public static final String PARENT_ID = "AREA_PARENT_ID";
public static final String SORT = "AREA_SORT";
public static final String DEEP = "AREA_DEEP";
public static final String CITY_CODE = "CITY_CODE";
public static final String REGION = "AREA_REGION";
public static final String STATUS = "AREA_STATUS";
public static final String AD_CODE = "AD_CODE";
//------------ get or set ...
dao实现类如下:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.entity.Area;
public class AreaDao extends BaseDao<Area>{
@Override
protected void toObject(ResultSet rs, Area data) throws SQLException{
if(rs.next()){
data.setId(rs.getInt(Area.ID));
data.setName(rs.getString(Area.NAME));
data.setParent_id(rs.getInt(Area.PARENT_ID));
data.setSort(rs.getByte(Area.SORT));
data.setDeep(rs.getByte(Area.DEEP));
data.setCity_code(rs.getString(Area.CITY_CODE));
data.setRegion(rs.getString(Area.REGION));
data.setStatus(rs.getInt(Area.STATUS));
data.setAd_code(rs.getInt(Area.AD_CODE));
}
}
@Override
protected void doInsertSetPstmt(PreparedStatement pstmt, Area t) throws SQLException {
throw new SQLException("This table is not allowed to be added");
}
@Override
protected void doUpdateSetPstmt(PreparedStatement pstmt, Area t) throws SQLException {
throw new SQLException("This form is not allowed to be amended");
}
@Override
protected void doDelete(Connection conn) throws SQLException {
throw new SQLException("This form is not allowed to be deleted");
}
}
我这里只是封装了一些冗余代码,并没有完成属性映射,一开始想着是用反射实现,后来发现时间不够。。。 然后就先写到这里了,写的过程想到我们可以用自定义注解来配置实体和表的映射,后面有时间会研究研究。
然后突然发现ORM框架其实不难(说这句话的时候我还没有研究过人家框架的源码,也许并不像我想的那么简单),也许以后有时间会自己完成一个ORM框架吧,先到这里,去赶项目了。
最后,这只是给像我这样菜鸟看的,大神勿喷[拜谢]