BaseDao工具类一般包含以下操作：
1.获取连接
2.创建stmt
3.执行sql
a)更新executeUpdate
b)查询executeQuery
4.关闭/异常
BaseDao接口代码：
package com.blog.dao;
import java.util.List;
public interface BaseDao {
int add(String sql,Object obj);
void delete(String sql,int[] ids);
int update(String sql,Object[] params);//可选
int update(String sql,Object obj);
<T> T query(String sql,Object[] params,Class<T> clazz);
<T> List<T> queryAll(String sql,Object[] params,Class<T> clazz);
}
BaseDaoImpl实现类代码：
其中BeanUtil类存在于commons-beanutils 这个apache提供的jar包中，用途：通过反射给javabean实体对象填充值
package com.blog.daoimpl;
import com.blog.bean.Blog;
import com.blog.bean.User;
import com.blog.dao.BaseDao;
import com.blog.util.JdbcUtil;
import com.blog.util.MStringUtil;
import org.apache.commons.beanutils.BeanUtils;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
public class BaseDaoImpl implements BaseDao {
private Connection conn = null;
private PreparedStatement ps;
private ResultSet rs;
public BaseDaoImpl() {
this.conn = JdbcUtil.getConnection();
}
/**
* 插入sql语句
* @param sql
* @param obj java bean实体对象
* @return
*/
@Override
public int add(String sql, Object obj) {
int flag = 0;
//正则匹配String sql="insert into t_comment(article_id,nickname,content,time,star,diss) values(?,?,?,?,?,?)"; 语句中的括号内的所有参数
Matcher m = Pattern.compile("\\(.+?\\)").matcher(sql);
String params = null;
if (m.find()) {//仅返回插入参数
System.out.println(m.group());
params = m.group();
}
params = params.substring(1, params.length() - 1);
System.out.println("params:" + params);
//(article_id,nickname,content,time,star,diss)通过逗号分割获取参数数组
String[] arr = params.split(",");
//通过反射获取参数obj （javabean）实体的所有get方法,方便获取对应参数的值
Class clazz = obj.getClass();
Method[] methods = clazz.getMethods();
List methodsList = Arrays.asList(methods);
//获取实体类的所有get方法,
methodsList = (List) methodsList.stream().filter(e -> e.toString().contains("get")).collect(Collectors.toList());
methodsList.forEach(m1 -> System.out.println(m1));
//创建与输入参数map存放instance中的具体参数
Map<String, Object[]> preParams = new HashMap<>();
try {
//获取传入对象instance的具体值
for (int i = 0; i < arr.length; i++) {
String columnStr = MStringUtil.toUpperCaseFirstOne(arr[i]);
for (Object o : methodsList) {
if (o.toString().contains(columnStr)) {//数据库表中字段名跟javabean中get方法要一一对应方便调用，如：comment.getContent();
Object tempResult = ((Method) o).invoke(obj);
System.out.println("反射调用get：" + tempResult);
if (null != tempResult) {
//index:0 存放对应数据库字段名的值，1存放该字段在参数中的索引
//然后put进入map中
Object[] tem = new Object[2];
tem[0] = tempResult;
tem[1] = i + 1;
preParams.put(arr[i], tem);
}
}
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
if (preParams != null) {
try {
ps = conn.prepareStatement(sql);
int count = ps.getParameterMetaData().getParameterCount();
for (int i = 0; i < count; i++) {
if (preParams.size() > 0) {
//遍历map中sql的参数 和 值，然后ps.setObject()
for (Map.Entry<String, Object[]> entry : preParams.entrySet()) {
System.out.println("entry:" + entry.getKey() + " entryvalue:" + entry.getValue());
if ((Integer) entry.getValue()[1] == i + 1) {
ps.setObject((Integer) entry.getValue()[1], entry.getValue()[0]);
break;
} else {
ps.setObject(i + 1, "");
}
}
}
}
System.out.println("ps toString:" + ps.toString());
flag = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(conn,ps);
}
}
return flag;
}
@Override
public void delete(String sql, int[] ids) {
int[] flag;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
if (ids.length>0){
for (int i = 0; i <ids.length ; i++) {
ps.setInt(1,ids[i]);
ps.addBatch();
}
}
flag = ps.executeBatch();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(conn,ps);
}
}
@Override
public int update(String sql, Object[] params) {
int flag = 0;
try {
ps = conn.prepareStatement(sql);
int count = ps.getParameterMetaData().getParameterCount();
if (params!=null&¶ms.length > 0) {
for (int i = 0; i < count; i++) {
ps.setObject(i + 1, params[i]);
}
}
flag = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeResource(conn,ps);
}
return flag;
}
@Override
public int update(String sql, Object obj) {
int flag = 0; //执行更新操作后的返回的影响行数
//获取sql语句中的参数
String[] paramsArr = MStringUtil.getParamsArr(sql);
//存放paramsArr中参数对应的值，通过反射代码获取
Object[] paramsValue = new Object[paramsArr.length];
int paramValuIndex = 0;
Class clazz = obj.getClass();
Method[] methods = clazz.getMethods();
methods = Arrays.stream(methods).filter(method -> method.toString().contains("get")).toArray(Method[]::new);
for (int i = 0; i < paramsArr.length; i++) {
for (Method method : methods) {
//将参数的首字母转换成大写：user->User ,
String methodName = MStringUtil.toUpperCaseFirstOne(paramsArr[i].toLowerCase());
//判断javabean中user.getUser() 是否存在“User”字符串,如果存在则调用getUser方法获取对象中的参数值
if (method.toString().contains(methodName)) {
try {
Object result = method.invoke(obj);
//将参数值存入数组中
paramsValue[paramValuIndex] = result;
paramValuIndex++;
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
if (paramsValue != null) {
flag = this.update(sql, paramsValue);
}
return flag;
}
@Override
public <T> T query(String sql, Object[] params, Class<T> clazz) {
T t = null;
try {
ps = conn.prepareStatement(sql);
int count = ps.getParameterMetaData().getParameterCount();
if (params!=null&¶ms.length > 0) {
for (int i = 0; i < count; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
int countMetaData = rsMetaData.getColumnCount();
if (rs.next()) {
t = clazz.newInstance();
for (int i = 0; i < countMetaData; i++) {
String columnName = rsMetaData.getColumnName(i + 1);
Object columnValue = rs.getObject(columnName);
//BeanUtils.setProperty(t,columnName,columnValue);
BeanUtils.copyProperty(t, columnName, columnValue);
}
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}finally {
try {
JdbcUtil.closeResource(conn,rs,ps);
} catch (SQLException e) {
e.printStackTrace();
}
}
return t;
}
@Override
public <T> List<T> queryAll(String sql, Object[] params, Class<T> clazz) {
T t = null;
List listT=new ArrayList();
try {
ps = conn.prepareStatement(sql);
int count = ps.getParameterMetaData().getParameterCount();
if (params!=null&¶ms.length > 0) {
for (int i = 0; i < count; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsMetaData = rs.getMetaData();
int countMetaData = rsMetaData.getColumnCount();
while (rs.next()) {
t = clazz.newInstance();
for (int i = 0; i < countMetaData; i++) {
String columnName = rsMetaData.getColumnName(i + 1);
Object columnValue = rs.getObject(columnName);
//BeanUtils.setProperty(t,columnName,columnValue);
BeanUtils.copyProperty(t, columnName, columnValue);
}
listT.add(t);
}
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}finally {
try {
JdbcUtil.closeResource(conn,rs,ps);
} catch (SQLException e) {
e.printStackTrace();
}
}
return listT;
}
}
JDBCUtil类:
package com.blog.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
public static String DRIVERNAME = null;
public static String URL = null;
public static String USER = null;
public static String PASSWORD = null;
public static Connection conn = null;
static {
try {
Properties props = new Properties();
//Reader in = new FileReader("db.properties");
InputStream in = JdbcUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
props.load(in);
DRIVERNAME = props.getProperty("drivername");
URL = props.getProperty("url");
USER = props.getProperty("user");
PASSWORD = props.getProperty("password");
} catch (Exception e) {
throw new RuntimeException(e);
}
}
//
public static Connection getConnection() {
try {
if (conn == null || conn.isClosed()) {
Class.forName(DRIVERNAME);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeResource(Connection conn, PreparedStatement st) {
try {
if (st != null) {
st.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeResource(Connection conn, ResultSet rs, PreparedStatement st) throws SQLException {
try {
if(rs!=null){
rs.close();
}
if (st != null) {
st.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
具体类BlogDaoImpl.class 继承BaseDaoImpl类：
package com.blog.daoimpl;
import com.blog.bean.Blog;
import com.blog.dao.BlogDao;
import java.util.List;
public class BlogDaoImpl extends BaseDaoImpl implements BlogDao {
@Override
public int addBlog(Blog blog) {
String sql = "insert into t_article(title,author,sort,time,star,comment,visit,content) values (?,?,?,?,?,?,?,?)";
return super.add(sql, blog);
}
@Override
public int updateBlog(Blog blog) {
String sql = "update t_article set title=?,author=? where id=?";
return super.update(sql, blog);
}
@Override
public int deleteBlog(int[] ids) {
String sql="delete from t_article where id=?";
super.delete(sql,ids);
return 0;
}
@Override
public Blog getBlogById(int blogId) {
Object[] params={blogId};
String sql="select * from t_article where id=?";
return super.query(sql,params,Blog.class);
}
@Override
public List<Blog> searchAllBlogs() {
String sql="select * from t_article";
return super.queryAll(sql,null,Blog.class);
}
}
测试代码:
@Test
public void testDeletBlog(){
int[] ids={13,14};
blogDao.deleteBlog(ids);
}
@Test
public void testQueryAllBlogs(){
List<Blog> blogs=blogDao.searchAllBlogs();
blogs.forEach(e-> System.out.println(e));
}
@Test
public void testQueryBlogById(){
Blog blog=blogDao.getBlogById(33);
System.out.println(blog);
}
@Test
public void testCommitComment(){
//article_id, String nick_name, String content, Date time, int star, int diss
LocalDateTime localDateTime=LocalDateTime.now();
ZoneId zone = ZoneId.systemDefault();
Instant instant = localDateTime.atZone(zone).toInstant();
Comment comment=new Comment(33,"Nick","fake conetne", Date.from(instant),0,0);
CommentService commentService=new CommentServiceImpl();
commentService.comment(comment);
}
测试结果:
1.测试查询全部评论Comment：
2.测试插入Comment：
网友评论