DAO模式可以帮助我们少些很多的数据库操作的代码，还能将对象直接进行持久化到数据库中，一个完整的DAO模式包含了五个部分，分别是:
1、数据库连接类，封装了对数据库的操作
2、VO类，对应的数据库中的表，每个bean的属性都是其中的元素
3、DAO接口，定义了数据库操作的接口
4、DAO实现类，实现DAO接口
5、DAO工厂类，用于获取DAO实现类的实例
下面我们来分别进行实现
1、数据库连接类
package com.fan.DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* mysql的连接获取和释放
* @author Administrator
*
*/
public class MysqlConnection {
//数据库的URL
private static String BaseURL = "jdbc:mysql://localhost:3306/testdb?";
//名称
private static String UserName = "root";
//密码
private static String Password = "*******";
//连接
private static Connection connection = null;
static{
try {
try {
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO: handle exception
System.out.println("找不到驱动类");
e.printStackTrace();
}
}
/**
* 获取数据库的连接
* @return
*/
public static Connection getConnection(){
try {
String link = BaseURL +
"user=" + UserName + "&password=" + Password + "&useSSL=false&serverTimezone=GMT";
System.out.println("link:" + link);
connection = DriverManager.getConnection(link);
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("数据库连接失败");
e.printStackTrace();
}
return connection;
}
/**
* 关闭数据库连接
*/
public static void closeConnection(){
if(connection == null){
System.out.println("数据库连接为空，不能进行释放");
return;
}
try {
connection.close();
System.out.println("数据库关闭完成");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 判断数据库连接是否存活
*/
public static boolean isConnectionSuvivor(Connection connection){
return connection != null;
}
}
2、VO类
package com.fan.beans;
/**
* 工程师类，数据库测试的VO
* @author Administrator
*
*/
public class Engineer {
private int age;
private String name;
private int salary;
public Engineer(){}
public Engineer( int age, String name, int salary) {
super();
this.age = age;
this.name = name;
this.salary = salary;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
}
3、DAO接口
package com.fan.DB;
import java.util.List;
import com.fan.beans.PackageClass;
public interface DBHandleInterface<T> {
//创建表
public void createTable(Class<?> clazz) throws Exception;
//删除表
public void deleteTable(Class<?> clazz) throws Exception;
//插入用户
public void insert (PackageClass<T> t,Class<?> clazz) throws Exception;
//更新用户
public void update(PackageClass<T> t,Class<T> clazz) throws Exception;
//删除用户
public void delete(PackageClass<T> t,Class<T> clazz) throws Exception;
//获取用户
public T queryById(PackageClass<T> t,Class<T> clazz) throws Exception;
//获取所有用户
public String queryAll(Class<T> clazz) throws Exception;
}
4、DAO接口的实现类
package com.fan.DB;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
import com.fan.EnumCheck.DBEnum;
import com.fan.Tools.ObjectTools;
import com.fan.beans.PackageClass;
/**
* 数据库操作的实现
* @author Administrator
*
* @param <T>
*/
public class DBHandleInterfaceImpl<T> implements DBHandleInterface<T> {
//SQL语句对象初始长度
private static final int STRING_BUILDER_CAPACITY = 400;
/**
* 插入对象
*/
public void insert(PackageClass<T> t, Class<?> clazz) throws Exception {
//获取定义的方法
Method[] methods = clazz.getDeclaredMethods();
//表名
String TableName = clazz.getSimpleName();
//定义一个获取方法名的变量
String str = null;
//创建一个insert的SQL对象
StringBuilder insertSQL = new StringBuilder(STRING_BUILDER_CAPACITY);
insertSQL.append("INSERT INTO ").append(TableName).append(" values(0,");
//遍历方法
for(Method method : methods){
//获取方法名
str = method.getName();
//只筛选需要的方法
if(!str.contains("throws") && str.contains("get") && !str.contains("Class")){
//获取方法的值
Object object = method.invoke(t.getData());
insertSQL.append((object instanceof String) ? ("\'" + object + "\'") : object).append(",");
}
}
//获取sql长度
int len = insertSQL.length();
insertSQL.delete(len - 1, len).append(");");
String sql = insertSQL.toString();
System.out.println(sql);
//执行
executeUpdateProcess(sql);
}
/**
* 更新数据
*/
public void update(PackageClass<T> t,Class<T> clazz) throws Exception {
//先对对象进行查找
T res = queryById(t, clazz);
if(res != null)//找到先删除
delete(t, clazz);
//再插入
insert(t, clazz);
}
/**
* 删除数据
*/
public void delete(PackageClass<T> t,Class<T> clazz) throws Exception {
//获取表名
String TableName = clazz.getSimpleName();
//创建一个删除的sql
StringBuilder deleteSQL = new StringBuilder(STRING_BUILDER_CAPACITY);
//构建sql语句
deleteSQL.append("DELETE FROM ").append(TableName).append(" WHERE ");
//获取方法
Method[] methods = clazz.getDeclaredMethods();
//
String str = null;
for(Method method : methods){
str = method.getName();
if(!str.contains("throws") && str.contains("get") && !str.contains("Class")){
str = str.replaceFirst("get", "");
Object object = method.invoke(t.getData());
deleteSQL.append(str).append("=").append(!method.getReturnType().getSimpleName().equals("int") ? ("\'" + object + "\'") : object).append(" and ");
}
}
deleteSQL.append(";");
String sql = deleteSQL.toString().replaceAll("and ;", ";");
//执行sql
executeUpdateProcess(sql);
//
}
/**
* 根据id进行查找
*/
public T queryById(PackageClass<T> t,Class<T> clazz) throws Exception {
//获取id
int index = t.getIndex();
//表名
String TableName = clazz.getSimpleName();
//SQL
StringBuilder querySQL = new StringBuilder(STRING_BUILDER_CAPACITY);
//
querySQL.append("SELECT * FROM ").append(TableName).append(" WHERE id = ").append(index).append(";");
//获取连接
Connection conn = MysqlConnection.getConnection();
//执行体
Statement stmt = null;
//结果
ResultSet rs = null;
//
T obj = t.getData();
try{
//创建执行体
stmt = conn.createStatement();
//执行查询
rs = stmt.executeQuery(querySQL.toString());
//
Method[] methods = clazz.getDeclaredMethods();
//
Pattern pattern = Pattern.compile("[a-zA-Z]+");
//遍历结果并返回，在这里，结果只能是一位或者没有
while(rs.next()){
for(Method method : methods){
if(method.getName().startsWith("set")){
String name = method.getName().replaceFirst("set", "");
String res = rs.getString(name);
// System.out.println(res);
method.invoke(obj, pattern.matcher(res).matches() ? res : Integer.parseUnsignedInt(res));
}
}
}
}catch(Exception e){
System.out.println("执行中出现异常");
e.printStackTrace();
}finally{
//释放执行体
freeStateMemt(stmt, rs);
//关闭连接
MysqlConnection.closeConnection();
}
return obj;
}
/**
* 查找所有的数据
*/
public String queryAll(Class<T> clazz) throws Exception {
//表名
String TableName = clazz.getSimpleName();
//查询语句
String queryAll = "SELECT * FROM " + TableName + ";";
//获取连接
Connection conn = MysqlConnection.getConnection();
//执行体
Statement stmt = null;
//结果
ResultSet rs = null;
//
StringBuilder res = new StringBuilder(10000);
//类的方法
Method[] methods = clazz.getDeclaredMethods();
StringBuilder nameBuilder = new StringBuilder(STRING_BUILDER_CAPACITY);
for(Method method : methods){
if(method.getName().startsWith("get")){
String name = method.getName().replaceFirst("get", "");
nameBuilder.append(name).append(",");
}
}
int len = nameBuilder.length();
String[] names = nameBuilder.substring(0, len - 1).split(",");
try{
//获取执行体
stmt = conn.createStatement();
//执行查询
rs = stmt.executeQuery(queryAll);
while(rs.next()){
int nameLen = names.length;
for(int i = 0;i < nameLen;i++){
res.append(names[i]).append(":").append(rs.getString(names[i])).append(" ");
}
res.append("\n");
}
}catch(Exception e){
e.printStackTrace();
}finally{
//关闭执行体
freeStateMemt(stmt, rs);
//关闭连接
MysqlConnection.closeConnection();
}
return res.toString();
}
/**
* 创建表
*/
public void createTable(Class<?> clazz) throws Exception {
//删除表
deleteTable(clazz);
//创建数据库的SQL
StringBuilder createSQL = new StringBuilder(STRING_BUILDER_CAPACITY);
createSQL.append("CREATE TABLE ");
//获取数据库名
String TableName = clazz.getSimpleName();
createSQL.append(TableName + "(id int not null AUTO_INCREMENT primary key,");
//获取属性并拼接成SQL
Method[] methods = clazz.getMethods();
String PropName,PropType;
for(Method method : methods){
String str = method.toString();
if(!str.contains("throws") && str.contains("get") && !str.contains("Class")){
PropName = getPropValue(str,2);
PropType = getPropValue(str,1);
createSQL.append(PropName + " " + getMapString(PropType) + ",");
}
}
// createSQL.append("primary key(index)");
createSQL.deleteCharAt(createSQL.length() - 1);
createSQL.append(")CHARACTER SET utf8;");
//转化为sql并执行
String sql = createSQL.toString();
executeUpdateProcess(sql);
}
/**
* 获取属性名称
* @param str
* @param type
* @return
*/
private String getPropValue(String str,int type){
String[] args = str.split(" ");
int len = args.length;
if(1 == type){
return args[len - 2].substring(args[len - 2].lastIndexOf('.') + 1);
}else if(2 == type){
String str1 = args[len - 1].substring(args[len - 1].lastIndexOf('.') + 1);
return str1.substring(0, str1.lastIndexOf("(")).replaceFirst("get", "");
}else{
return null;
}
}
/**
* 获取映射的字符串
* @return
*/
private String getMapString(String str){
String upstr = str.toUpperCase();
for(DBEnum value : DBEnum.values()){
if(upstr.equalsIgnoreCase(value.name()))
return value.getValue();
}
return null;
}
/**
* 删除表
*/
public void deleteTable(Class<?> clazz) throws Exception {
//获取表
String TableName = clazz.getSimpleName();
//创建执行删除数据库表的SQL
String deleteSQL = "DROP TABLE IF EXISTS " + TableName;
//执行
executeUpdateProcess(deleteSQL);
}
/**
* 释放执行体
* @param stmt
*/
private void freeStateMemt(Statement stmt,ResultSet rs){
if(stmt != null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(rs != null)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 执行更新流程
*/
private void executeUpdateProcess(String sql){
//获取连接
Connection conn = MysqlConnection.getConnection();
//执行体
Statement stmt = null;
try{
//创建执行体
stmt = conn.createStatement();
//执行
stmt.executeLargeUpdate(sql);
}catch(Exception e){
System.out.println("执行出现异常");
e.printStackTrace();
}finally{
//释放执行体
freeStateMemt(stmt,null);
MysqlConnection.closeConnection();
}
}
}
5、DAO工厂类
package com.fan.DB;
/**
* 获取操作
* @author Administrator
*
*/
public class DBHandleFactory{
public static <T> DBHandleInterfaceImpl<T> getInstance(){
return new DBHandleInterfaceImpl<T>();
}
}
