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

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

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

先将通用的增查实现类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

*/

public class BaseDAO implements IBaseDAO {

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 filedList = new ArrayList();

//获取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 filedList = new ArrayList();

//获取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 pageUtil) {

E e = pageUtil.getEntity();

//获取表名

String tableName = configUtil.getVal(cls.getName());

//获取查询条件

Map 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 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 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 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 paramMap, String tableName) {

StringBuffer sql = new StringBuffer();

sql.append("select * from ")

.append(tableName)

.append(" where 1 = 1 and status='0' ");

List columlist = getTableColumns(tableName);

for (Entry 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 getParamMap(E e) {

Map paramMap = new LinkedHashMap();

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 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

* @zuPWoyDiaparam prykey

* @param filedList

* @return

*/

private String getmodifysql(String tableName, String prykey, List filedList) {

StringBuffer sql = new StringBuffer();

sql.append("update ").append(tableName).append(" set ");

List 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 pageUtil, String sql, Map 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 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 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 filedList) {

StringBuffer sql = new StringBuffer();

sql.append("insert into ").append(tableName).append(" (");

List 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()-1http://));

}

sql.append(") value (");

for (int i = 0; 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 getTableColumns(String tableName) {

List columnList = new ArrayList();

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 getEntityList(ResultSet rs) throws Exception {

List list = new ArrayList();

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

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 getTableColumns(String tableName) {

List columnList = new ArrayList();

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 getEntityList(ResultSet rs) throws Exception {

List list = new ArrayList();

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。