JDBC implements the general method of adding deleting modifying and checking base classes
- 2020-12-13 18:58:35
- OfStack
There are many frameworks about data management in java, such as hibernate, mybatis, etc., but I started to learn JDBC, I think JDBC is very good, it let me have a deeper understanding of the operation of data, today I wrote the JDBC basic class again! Deepen the memory!!
Start by pasting the generic augmented implementation class 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;
/**
* Universal add, delete, change, check implementation class
* @author Administrator
*
* @param <E>
*/
public class BaseDAO<E> implements IBaseDAO<E> {
protected static ConfigUtil configUtil;
protected Class<?> cls;
/**
* Gets the package name, class name
*/
public BaseDAO(){
Class<?> clsTemp = this.getClass();
Type type = clsTemp.getGenericSuperclass();
if (type instanceof ParameterizedType) {
Type[] types = ((ParameterizedType) type).getActualTypeArguments();
cls = (Class<?>) types[0];
}
}
/**
* Gets the table name in the database for the corresponding class
*/
static{
configUtil = ConfigUtil.newInstance("/tabORM.properties");
}
/**
* Save method
*/
public boolean saveInfo(E e) {
boolean flag = true;
try {
Class<?> cls = e.getClass();
// To get the name of the table
String tableName = configUtil.getVal(cls.getName());
// Access to the primary key
String prykey = getPrimKey(tableName);
// Record data column
List<String> filedList = new ArrayList<String>();
// To obtain sql statements
String sql = getsavesql(tableName,prykey,filedList);
// perform sql
flag = excuteSQL(sql,e,filedList);
} catch (Exception e1) {
flag = false;
e1.printStackTrace();
}
return flag;
}
/**
* Change the method
*/
public void modifyInfo(E e) {
Class<?> cls = e.getClass();
// To get the name of the table
String tableName = configUtil.getVal(cls.getName());
// Access to the primary key
String prykey = getPrimKey(tableName);
// Record data column
List<String> filedList = new ArrayList<String>();
// To obtain sql statements
String sql = getmodifysql(tableName,prykey,filedList);
// Add the primary key to the collection
filedList.add(prykey);
// perform sql
excuteSQL(sql,e,filedList);
}
/**
* Delete methods
*/
public void deleteInfo(Object id) {
// To get the name of the table
String tableName = configUtil.getVal(cls.getName());
// Access to the primary key
String prykey = getPrimKey(tableName);
// To obtain sql statements
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);
}
}
/**
* Query all methods
*/
public void queryinfo(PageUtil<E> pageUtil) {
E e = pageUtil.getEntity();
// To get the name of the table
String tableName = configUtil.getVal(cls.getName());
// Get query criteria
Map<String,Object> paramMap = getParamMap(e);
// To obtain sql
String sql = getquerySQL(paramMap,tableName);
sql += " limit ?,?";
paramMap.put("pageSize", (pageUtil.getPageSize() - 1)*pageUtil.getPageNum());
paramMap.put("pageNum", pageUtil.getPageNum());
// perform SQL
excutQuery(pageUtil,sql,paramMap,tableName);
}
/**
* Single query method
*/
public E queryById(Object id) {
// To get the name of the table
String tableName = configUtil.getVal(cls.getName());
// Access to the primary key
String prykey = getPrimKey(tableName);
// To obtain sql
String sql = "select * from "+tableName+" where 1 = 1 and "+prykey+" = ?";
// perform 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;
}
/**
* Gets the total number of entries
* @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;
}
/**
* Access to the query 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();
}
/**
* Get query criteria
* @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;
}
/**
* Access to the primary key
* @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;
}
/**
* Save method execution 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);
// The assignment
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;
}
/**
* Gets the method modified 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();
}
/**
* Execute all queries 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);
// Encapsulate query results
pageUtil.setList(list);
// Total number of packages
pageUtil.setPageNumSum(getPagenumsss(paramMap,tableName));
} catch (Exception e) {
e.printStackTrace();
}finally{
JdbcUtil.closeConn(conn);
}
}
/**
* Gets the attributes in the table
* @param entity
* @param columName
* @return
*/
private Object getFieldValue(E entity, String columName) {
Class<?> cls = entity.getClass();
Object value = null;
// Gets all member properties in a class
Field[] fields = cls.getDeclaredFields();
for (Field field : fields) {
// Get the property name
String fieldName = field.getName();
// Determines whether the property name is the same as the column name
if (fieldName.equalsIgnoreCase(columName)) {
// Gets the method name based on the rule
String methodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
try {
// Gets the method object based on the method name
Method method = cls.getMethod(methodName);
// Executes the method and gets the return value
value = method.invoke(entity);
} catch (Exception e) {
e.printStackTrace();
}
break;
}
}
return value;
}
/**
* Save method acquisition 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();
}
/**
* Access to table columns
* @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;
}
/**
* Encapsulate query results
* @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 related profile ES14en. properties content
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dbsda10?characterEncoding=UTF-8
username=root
password=rootroot
temfile=C:\\
image=C:\\images The configuration file reads class ConfigUtil
package com.shude.util;
import java.io.IOException;
import java.util.Properties;
/**
* Read configuration file
* @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;
}
/**
* Gets the value on the right side of the configuration file
* @param key
* @return */
public String getVal(String key){
return pop.getProperty(key);
}
}Before this, the condition is that the field name of the database should correspond to and be the same as the name in the entity class, with the relevant data table name and the configuration file related to the entity class name, the configuration file is as follows:
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