mysql jdbc dao_基于JDBC的一个简单的DAO实例

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 {

//创建表

public void createTable(Class> clazz) throws Exception;

//删除表

public void deleteTable(Class> clazz) throws Exception;

//插入用户

public void insert (PackageClass t,Class> clazz) throws Exception;

//更新用户

public void update(PackageClass t,Class clazz) throws Exception;

//删除用户

public void delete(PackageClass t,Class clazz) throws Exception;

//获取用户

public T queryById(PackageClass t,Class clazz) throws Exception;

//获取所有用户

public String queryAll(Class 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

*/

public class DBHandleInterfaceImpl implements DBHandleInterface {

//SQL语句对象初始长度

private static final int STRING_BUILDER_CAPACITY = 400;

/**

* 插入对象

*/

public void insert(PackageClass 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,Class clazz) throws Exception {

//先对对象进行查找

T res = queryById(t, clazz);

if(res != null)//找到先删除

delete(t, clazz);

//再插入

insert(t, clazz);

}

/**

* 删除数据

*/

public void delete(PackageClass t,Class 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,Class 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 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 DBHandleInterfaceImpl getInstance(){

return new DBHandleInterfaceImpl();

}

}