JDBC 实现通用的增删改查基础类

最新推荐文章于 2023-08-10 18:51:40 发布

outSide~

最新推荐文章于 2023-08-10 18:51:40 发布

阅读量1k

点赞数 1

分类专栏： Java 文章标签： jdbc 数据 java 框架 mysql

本文链接：https://blog.csdn.net/qq_36792069/article/details/78953136

版权

Java 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

java中关于数据的管理有很多的框架，如hibernate、mybatis等，但我最开始学习的就是JDBC，我觉得JDBC还是很不错的，它让我更深层次的了解了数据的操作，今天我将我写过的JDBC基础类再写一遍！加深下记忆！！！

jdbc工具类 JdbcUtil 连接数据库和关闭数据库操作

package com.shude.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 连接数据库和关闭数据库操作
 * @author Administrator
 *
 */
public class JdbcUtil {
	public static ConfigUtil configUtil = ConfigUtil.newInstance(null);
	private static String driver=configUtil.getVal("driver");
	private static String url=configUtil.getVal("url");
	private static String userName=configUtil.getVal("username");
	private static String passWord=configUtil.getVal("password");
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static Connection getConn(){
		Connection conn=null;
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url, userName, passWord);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;	
	}
	/**
	 * 关闭数据库连接
	 * @param conn
	 */
	public static void closeConn(Connection conn){
			try {
				if (conn!=null) 
				   conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}		
	}
}

通用的增查实现类BaseDAO

package com.shude.DAO;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.shude.DAO.im.IBaseDAO;
import com.shude.util.ConfigUtil;
import com.shude.util.JdbcUtil;
import com.shude.util.PageUtil;
/**
 * 通用的增删改查实现类
 * @author Administrator
 *
 * @param <E>
 */
public class BaseDAO<E>  implements IBaseDAO<E> {
	protected static ConfigUtil configUtil;
	protected Class<?> cls;
	/**
	 * 获取包名、类名
	 */
	public BaseDAO(){
		Class<?> clsTemp = this.getClass();
		Type type = clsTemp.getGenericSuperclass();
		if (type instanceof ParameterizedType) {
			Type[] types = ((ParameterizedType) type).getActualTypeArguments();
			cls = (Class<?>) types[0];
		}
	}
	/**
	 * 获取对应类的数据库中的表名
	 */
	static{
		configUtil = ConfigUtil.newInstance("/tabORM.properties");
	}
	
	/**
	 * 保存方法
	 */
	public boolean saveInfo(E e) {
		boolean flag = true;
		try {
			Class<?> cls = e.getClass();
			//获取表名
			String tableName = configUtil.getVal(cls.getName());
			//获取主键
			String prykey = getPrimKey(tableName);
			//记录数据列
			List<String> filedList = new ArrayList<String>();
			//获取sql语句
			String sql = getsavesql(tableName,prykey,filedList);
			//执行sql
			flag = excuteSQL(sql,e,filedList);
		} catch (Exception e1) {
			flag = false;
			e1.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 修改方法
	 */
	public void modifyInfo(E e) {
		Class<?> cls = e.getClass();
		//获取表名
		String tableName = configUtil.getVal(cls.getName());
		//获取主键
		String prykey = getPrimKey(tableName);
		//记录数据列
		List<String> filedList = new ArrayList<String>();
		//获取sql语句
		String sql = getmodifysql(tableName,prykey,filedList);
		//添加主键到集合
		filedList.add(prykey);
		//执行sql
		excuteSQL(sql,e,filedList);
	}
    
	/**
	 * 删除方法
	 */
	public void deleteInfo(Object id) {
		//获取表名
		String tableName = configUtil.getVal(cls.getName());
		//获取主键
		String prykey = getPrimKey(tableName);
		//获取sql语句
		String sql = "update "+tableName+" set status='1' where "+prykey+"=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = JdbcUtil.getConn();
			pstm = conn.prepareStatement(sql);
			pstm.setObject(1, id);
			pstm.execute();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JdbcUtil.closeConn(conn);
		}	
	}

	/**
	 * 查询全部方法
	 */
	public void queryinfo(PageUtil<E> pageUtil) {
		E e = pageUtil.getEntity();
		//获取表名
		String tableName = configUtil.getVal(cls.getName());
		//获取查询条件
		Map<String,Object> paramMap = getParamMap(e);
		//获取sql
		String sql = getquerySQL(paramMap,tableName);
		sql += " limit ?,?";
		paramMap.put("pageSize", (pageUtil.getPageSize() - 1)*pageUtil.getPageNum());
		paramMap.put("pageNum", pageUtil.getPageNum());
		//执行SQL
		excutQuery(pageUtil,sql,paramMap,tableName);
		
	}
	
	/**
	 * 单个查询方法
	 */
	public E queryById(Object id) {
		//获取表名
				String tableName = configUtil.getVal(cls.getName());
				//获取主键
				String prykey = getPrimKey(tableName);
				//获取sql
				String sql = "select * from "+tableName+" where 1 = 1 and "+prykey+" = ?";
				//执行SQL
				Connection conn = null;
				PreparedStatement pstm = null;
				ResultSet rs = null;
				E e = null;
				try {
					conn = JdbcUtil.getConn();
					pstm = conn.prepareStatement(sql);
					pstm.setObject(1, id);
					rs = pstm.executeQuery();
					List<E> list = getEntityList(rs);
					e = list.get(0);		
				} catch (Exception ex) {
					ex.printStackTrace();
				}finally{
					JdbcUtil.closeConn(conn);
				}
				return e;
	}
	
	/**
	 * 获取总条数
	 * @param paramMap
	 * @param tableName
	 * @return
	 */
    private Integer getPagenumsss(Map<String, Object> paramMap, String tableName) {
    	paramMap.remove("pageSize");
		paramMap.remove("pageNum");
		String sql = getquerySQL(paramMap,tableName);
		sql = "select count(*) from ("+sql+") tempTab";
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Integer pagenumsss = 0;
		try {
			conn = JdbcUtil.getConn();
			pstm = conn.prepareStatement(sql);
			int i = 1;
			for (Entry<String,Object> entry : paramMap.entrySet()) {
				Object val = entry.getValue();
				if(val instanceof java.lang.String){
					pstm.setString(i, "%"+val.toString()+"%");
				}else if(val instanceof java.lang.Integer){
					pstm.setInt(i, Integer.parseInt(val.toString()));
				}
				i++;
			}
			rs = pstm.executeQuery();
			while(rs.next()){
				pagenumsss = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeConn(conn);
		}
		return pagenumsss;
	}

	/**
     * 获取查询SQL
     * @param paramMap
     * @param tableName
     * @return
     */
	private String getquerySQL(Map<String, Object> paramMap, String tableName) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ")
		.append(tableName)
		.append(" where 1 = 1 and status='0' ");
		List<String> columlist = getTableColumns(tableName);
		for (Entry<String,Object> entry : paramMap.entrySet()) {
			String columName = entry.getKey();
			for (String colnName : columlist) {
				if(colnName.equalsIgnoreCase(columName)){
					if(entry.getValue() instanceof java.lang.String){
						sql.append(" and ").append(columName).append(" like ?");
					}else{
						sql.append(" and ").append(columName).append("=?");
					}
					break;
				}
			}
		}
		return sql.toString();
	}
    /**
     * 获取查询条件
     * @param e
     * @return
     */
	private Map<String, Object> getParamMap(E e) {
		Map<String,Object> paramMap = new LinkedHashMap<String,Object>();
		Field[] fields = e.getClass().getDeclaredFields();
		for (Field field : fields) {
			try {
				field.setAccessible(true);
				Object val = field.get(e);
				if(val != null && !"".equals(val.toString())){
					paramMap.put(field.getName(), val);
				}
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			
		}
		return paramMap;
	}

	
	/**
     * 获取主键
     * @param tableName
     * @return
     */
	private String getPrimKey(String tableName) {
		Connection conn = null;
		DatabaseMetaData metaData = null;
		ResultSet rs = null;
		String primKeyName = null;
		try {
			conn = JdbcUtil.getConn();
			metaData = conn.getMetaData();
			rs = metaData.getPrimaryKeys(conn.getCatalog(), null, tableName.toUpperCase());
			while (rs.next()) {
				primKeyName = rs.getString("COLUMN_NAME");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeConn(conn);
		}
		return primKeyName;
	}

	/**
	 * 保存方法执行SQL
	 * @param sql
	 * @param e
	 * @param filedList
	 * @return
	 */
	private boolean excuteSQL(String sql, E entity, List<String> filedList) {
		boolean flag = true;
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = JdbcUtil.getConn();
			pstm = conn.prepareStatement(sql);
			//赋值
			int i = 1;
			for (String columName : filedList) {
				Object val = getFieldValue(entity,columName);
				pstm.setObject(i, val);
				i++;
			}
			pstm.execute();
		} catch (SQLException e1) {
			e1.printStackTrace();
			flag = false;
		}finally{
			JdbcUtil.closeConn(conn);
		}
		return flag;
	}

	/**
     * 获取修改方法的SQL
     * @param tableName
     * @param prykey
     * @param filedList
     * @return
     */
	private String getmodifysql(String tableName, String prykey, List<String> filedList) {
		StringBuffer sql = new StringBuffer();
		sql.append("update ").append(tableName).append(" set ");
		List<String> columnList = getTableColumns(tableName);
		for (String columnName : columnList) {
			if (!columnName.equalsIgnoreCase(prykey)) {
				filedList.add(columnName);
				sql.append(columnName).append("=?,");
			}
		}
		if (sql.toString().endsWith(",")) {
			sql = new StringBuffer(sql.substring(0,sql.length()-1));
		}
		sql.append(" where ").append(prykey).append("=?");
		return sql.toString();
	}

    /**
     * 执行查询全部SQL
     * @param pageUtil
     * @param sql
     * @param paramMap
     * @param tableName
     */
	private void excutQuery(PageUtil<E> pageUtil, String sql, Map<String, Object> paramMap, String tableName) {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConn();
			pstm = conn.prepareStatement(sql);
			int i = 1;
			for (Entry<String,Object> entry : paramMap.entrySet()) {
				Object val = entry.getValue();
				if(val instanceof java.lang.String){
					pstm.setString(i, "%"+val.toString()+"%");
				}else if(val instanceof java.lang.Integer){
					pstm.setInt(i, Integer.parseInt(val.toString()));
				}
				i++;
			}
			rs = pstm.executeQuery();
			List<E> list = getEntityList(rs);
			//封装查询结果
 			pageUtil.setList(list);
			//封装总条数
			pageUtil.setPageNumSum(getPagenumsss(paramMap,tableName));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JdbcUtil.closeConn(conn);
		}
	}
	/**
	 * 获取表中属性
	 * @param entity
	 * @param columName
	 * @return
	 */
   private Object getFieldValue(E entity, String columName) {
	   Class<?> cls = entity.getClass();
		Object value = null;
		//获取类中的所有成员属性
		Field[] fields = cls.getDeclaredFields();
		for (Field field : fields) {
			//获取属性名称
			String fieldName = field.getName();
			//判断属性名称是否与列名相同
			if (fieldName.equalsIgnoreCase(columName)) {
				//根据规则获取方法名称
				String methodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
				try {
					//根据方法名称获取方法对象
					Method method = cls.getMethod(methodName);
					//执行方法并获取返回值
					value = method.invoke(entity);
				} catch (Exception e) {
					e.printStackTrace();
				}
				break;
			}
		}
		return value;
	}

/**
    * 保存方法获取SQL
    * @param tableName
    * @param prykey
    * @param filedList
    * @return
    */
	private String getsavesql(String tableName, String prykey, List<String> filedList) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into ").append(tableName).append(" (");
			List<String> columnList = getTableColumns(tableName);
			for (String string : columnList) {
				if (!string.equalsIgnoreCase(prykey)) {
					sql.append(string).append(",");
					filedList.add(string);
				}
			}
			if (sql.toString().endsWith(",")) {
				sql = new StringBuffer(sql.substring(0,sql.length()-1));
			}
			sql.append(") value (");
			for (int i = 0; i <filedList.size(); i++) {
				
				sql.append("?,");
					
			}
			if (sql.toString().endsWith(",")) {
				sql = new StringBuffer(sql.substring(0,sql.length()-1));
			}
			sql.append(")");

		return sql.toString();
	}
    
	/**
	 * 获取表列
	 * @param tableName
	 * @return
	 */
	private List<String> getTableColumns(String tableName) {
		List<String> columnList = new ArrayList<String>();
		Connection conn = null;
		DatabaseMetaData metaData = null;
		ResultSet rs = null;
		conn = JdbcUtil.getConn();
		try {
			metaData = conn.getMetaData();
			rs = metaData.getColumns(conn.getCatalog(),null,tableName.toUpperCase(),null);
			while (rs.next()) {
				String clumnName = rs.getString("COLUMN_NAME");
				columnList.add(clumnName);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			JdbcUtil.closeConn(conn);
		}
		return columnList;
    }
    /**
     * 封装查询结果
     * @param rs
     * @return
     * @throws Exception 
     */
	@SuppressWarnings("unchecked")
	private List<E> getEntityList(ResultSet rs) throws Exception {
		List<E> list = new ArrayList<E>();
		Field[] fields = cls.getDeclaredFields();
		while(rs.next()){
			E e = (E)cls.newInstance();
			for (Field field : fields) {
				try {
					field.setAccessible(true);
					String columName = field.getName();
					String fieldType = field.getType().getSimpleName();
					if("String".equals(fieldType)){
						field.set(e, rs.getString(columName));
					}else if("Integer".equals(fieldType)){
						field.set(e, rs.getInt(columName));
					}
				} catch (Exception e1) {
					e1.printStackTrace();
				}
			}
			list.add(e);
		}
		return list;
	}

}

mysql相关配置文件config.properties内容

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbsda10?characterEncoding=UTF-8
username=root
password=rootroot
temfile=C:\\
image=C:\\images

配置文件读取类ConfigUtil

package com.shude.util;

import java.io.IOException;
import java.util.Properties;
/**
 * 读取配置文件
 * @author Administrator
 *
 */
public class ConfigUtil {
 private static ConfigUtil configUtil;
 private static final String DEFALT_FILE_PATH="/config.properties";
 private static String name;
 private Properties pop;
 private ConfigUtil(){
  init();
 }
 private void init() {
  pop=new Properties();
  try {
   if(name!=null)
    pop.load(ConfigUtil.class.getResourceAsStream(name));
   pop.load(ConfigUtil.class.getResourceAsStream(DEFALT_FILE_PATH));
  } catch (IOException e) {
   e.printStackTrace();
  }
 }
 public static ConfigUtil newInstance(String name){
  ConfigUtil.name=name;
  if(configUtil==null)
   configUtil=new ConfigUtil();
  return configUtil;
 }
 /**
  * 获取配置文件中右边的值
  * @param key
  * @return  */
 public String getVal(String key){
  return pop.getProperty(key);
 }
}

在此之前的条件是数据库的字段名称要与实体类中的名称对应并且相同，有相关的数据表名称及实体类名称所相关的配置文件，配置文件如下：
tabORM.properties

com.shude.entity.UserInfo=user_info
com.shude.entity.RoleInfo=role_info
com.shude.entity.FabricInfo=fabric_info
com.shude.entity.ProductInfo=product_info
com.shude.entity.MateInfo=mate_info
com.shude.entity.ProgramInfo=program_info

下一章将结合此章编写servlet的基础类:servlet通用基础类

此文章所包含的jar包:jdbc+servlet+jsp+mysql相关jar包