溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
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 <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
以上這篇JDBC 實現通用的增刪改查基礎類方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持億速云。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
