DAO和增删改查通用方法————BasicDao

先分析一个问题

 

将来在实际开发中，一张表（比如说actor）对应一个ActorDao，而每一张表都跟java里的类有映射关系。各种Dao又有共有的部分，比如说连接数据库，关闭资源，这些肯定是相同的，把共有部分提到BasicDao里去，然后让其他的Dao继承它，里面还会用到泛型。根据业务需求，使用对应Dao  

---

BaseDao的简单实例

 

---

BasicDao.java

package godairo.dao_.dao;

import godairo.jdbc.datasource.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author GodAiro
 * Creation Time: 2022/7/16 20:10.
 * 开发BasicDao,是其他Dao的父类
 */
public class BasicDao<T>  { //泛型指定具体类型
   
   private QueryRunner queryRunner = new QueryRunner();
   
   //开发通用的DML方法,针对任意的表
   public int update(String sql,Object... parameters){

	  Connection connection = null;
	  try {
		 Connection connetion = JDBCUtilsByDruid.getConnetion();
		 int update = queryRunner.update(connection, sql, parameters);
		 return update;
	  } catch (SQLException e) {
		 throw new RuntimeException(e); //把编译异常转成运行异常抛出，方便调用者使用
	  } finally {
	     JDBCUtilsByDruid.close(null,null,connection);
	  }

   }

   /**
	* 
	* @param sql sql语句，可以有 ?
	* @param clazz 传入一个类的Class对象，因为底层在进行封装到集合，要知道类里面有哪些属性，通过反射来创建JavaBean对象，需要传入Class对象，比如Actor.class
	* @param parameters	传入 ? 的具体的值，可以是多个
	* @return	根据Actor.class 对应的ArrayList集合
	*/
   //返回多个对象(即查询的结果是多行)，针对任意表
   public List<T> queryMulti(String sql,Class<T> clazz,Object... parameters){
      Connection connection = null;
	  try {
		 Connection connetion = JDBCUtilsByDruid.getConnetion();
		 return queryRunner.query(connection,sql,new BeanListHandler<T>(clazz),parameters);

	  } catch (SQLException e) {
		 throw new RuntimeException(e); //把编译异常转成运行异常抛出，方便调用者使用
	  } finally {
		 JDBCUtilsByDruid.close(null,null,connection);
	  }
	  
   }
   
   //查询单行结果的通用方法
   public T querySingle(String sql,Class<T> clazz,Object... parameters){
	  Connection connection = null;
	  try {
		 Connection connetion = JDBCUtilsByDruid.getConnetion();
		 return queryRunner.query(connection,sql,new BeanHandler<T>(clazz));

	  } catch (SQLException e) {
		 throw new RuntimeException(e); //把编译异常转成运行异常抛出，方便调用者使用
	  } finally {
		 JDBCUtilsByDruid.close(null,null,connection);
	  }
   }
   
   //查询单行单列的方法,即返回单值的方法
   public Object queryScalar(String sql,Object... parameters){
	  Connection connection = null;
	  try {
		 Connection connetion = JDBCUtilsByDruid.getConnetion();
		 return queryRunner.query(connection,sql,new ScalarHandler(),parameters);

	  } catch (SQLException e) {
		 throw new RuntimeException(e); //把编译异常转成运行异常抛出，方便调用者
	  } finally {
		 JDBCUtilsByDruid.close(null,null,connection);
	  }
   }
   

}

ActorDao.java  

package godairo.dao_.dao;

import godairo.dao_.domain.Actor;

/**
 * @author GodAiro
 * Creation Time: 2022/7/16 20:35.
 */
public class ActorDao extends BasicDao<Actor> {
   
   //1.就有了BasicDao的所有方法
   //2.根据业务需求，可以编写特有的方法。
   
}

 Actor.java

package godairo.dao_.domain;

import java.util.Date;

/**
 * @author GodAiro
 * Creation Time: 2022/7/16 15:55.
 * Actor对象和Actor表记录对应
 */
public class Actor {
   private Integer id;
   private String name;
   private String sex;
   private Date borndate;
   private String phone;

   //一定要给一个无参构造器，在底层可能会用到反射
   public Actor() {
   }
   
   public Actor(Integer id, String name, String sex, Date borndate, String phone) {
	  this.id = id;
	  this.name = name;
	  this.sex = sex;
	  this.borndate = borndate;
	  this.phone = phone;
   }

   public Integer getId() {
	  return id;
   }

   public void setId(Integer id) {
	  this.id = id;
   }

   public String getName() {
	  return name;
   }

   public void setName(String name) {
	  this.name = name;
   }

   public String getSex() {
	  return sex;
   }

   public void setSex(String sex) {
	  this.sex = sex;
   }

   public Date getBorndate() {
	  return borndate;
   }

   public void setBorndate(Date borndate) {
	  this.borndate = borndate;
   }

   public String getPhone() {
	  return phone;
   }

   public void setPhone(String phone) {
	  this.phone = phone;
   }

   @Override
   public String toString() {
	  return "\nActor{" +
			  "id=" + id +
			  ", name='" + name + '\'' +
			  ", sex='" + sex + '\'' +
			  ", borndate=" + borndate +
			  ", phone='" + phone + '\'' +
			  '}';
   }
}

JDBCUtilsByDruid.java  

package godairo.dao_.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @author 吴志超
 * 基于Druid数据库连接池的工具类
 */
public class JDBCUtilsByDruid {

   private static DataSource dataSource;

   //在静态代码块完成dataSource初始化
   static {
	  Properties properties = new Properties();
	  try {
		 properties.load(new FileInputStream("src\\main\\resources\\druid.properties"));
		 dataSource = DruidDataSourceFactory.createDataSource(properties);
	  } catch (Exception e) {
		 e.printStackTrace();
	  }
   }

   //编写getConnection方法
   public static Connection getConnetion() throws SQLException {
	  return dataSource.getConnection();
   }
    
   //关闭连接，这里不是真正的关闭，而是把连接放回连接池
   public static void close(ResultSet resultSet, Statement statement, Connection connection) {
	  try {
		 if (resultSet != null) {
			resultSet.close();
		 }
		 if (statement != null) {
			statement.close();
		 }
		 if (connection != null) {
			connection.close();
		 }
	  } catch (SQLException e) {
		 throw new RuntimeException(e);
	  }
   }

}

---

TestDao.java

package godairo.dao_.test;

import godairo.dao_.dao.ActorDao;
import godairo.dao_.domain.Actor;
import org.junit.jupiter.api.Test;

import java.util.List;

/**
 * @author 吴志超
 * Creation Time: 2022/7/16 20:36.
 */
public class TestDao {
   
   //测试ActorDao对actor表的CRUD操作
   @Test
   public void testActorDao(){
	  ActorDao actorDao = new ActorDao();
	  //1.查询
	  List<Actor> actors = actorDao.queryMulti("select * from actor where id>=?", Actor.class, 1);
	  System.out.println("————查询结果————");
	  for (Actor actor : actors) {
		 System.out.println(actor);
	  }


	  //2.查询单行记录
	  Actor actor = actorDao.querySingle("select * from actor where id=?", Actor.class, 6);
	  System.out.println("————查询单行结果————");
	  System.out.println(actor);
	  
	  //3.查询单行单列
	  Object o = actorDao.queryScalar("select name from actor where id=?", 6);
	  System.out.println("————查询单行单列————");
	  System.out.println(o);
	  
	  //4.DML操作
	  int update = actorDao.update("insert into actor values(null,?,?,?,?)", "测试名字2", "男", "1999-5-8", "15951456320");
	  System.out.println(update>0?"执行成功":"执行没有影响到表");
   }
}

---

运行结果