JdbcTemplate的通用BaseDao

迷南。 2022-03-10 07:38 164阅读 0赞

> 在实现一个小项目的时候, 通常会使用jdbcTemplate来作为数据库连接, 记录下项目中所用的baseDao代码块, 以后直接可引用. 这个例子是在spring 项目中的应用, 由spring框架帮我们实例化,并管理JdbcTemplate, 如果是在基本的maven 项目中, 直接手动配置JdbcTemplate的参数就行.

import javax.sql.DataSource;
    import java.lang.reflect.Field;
    import java.lang.reflect.ParameterizedType;
    import java.sql.*;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    /** * @author dingguanghui722 * @date 2019-1-19 */
    @Repository
    public abstract class BaseDao<T> implements RowMapper<T> { 
        private static final Logger log = LoggerFactory.getLogger(BaseDao.class);
    
        @Autowired
        protected JdbcTemplate jdbcTemplate;
        @Autowired
        private DataSource dataSource;
    
        public Connection getConnection() throws SQLException { 
            return dataSource.getConnection();
        }
    	 /** * 手动赋值链接属性 * BaseDao() { * super(); * DriverManagerDataSource driverDataSource = new DriverManagerDataSource(); * driverDataSource.setDriverClassName(MysqlConf.DRIVER_NAME); * driverDataSource.setUrl(MysqlConf.ZETTA_DATABASE_URL); * driverDataSource.setUsername(MysqlConf.USERNAME); * driverDataSource.setPassword(MysqlConf.PASSWORD); * this.dataSource = driverDataSource; * this.jdbcTemplate = new JdbcTemplate(driverDataSource); * } * */
    	
        @Override
        public T mapRow(ResultSet rs, int rowNum) throws SQLException { 
            Object instance = null;
            Class<?> clz = getTrueClass();
            try { 
                instance = clz.newInstance();
                Field[] fields = clz.getDeclaredFields();
                for (Field field : fields) { 
                    field.setAccessible(true);
                    field.set(instance, rs.getObject(this.fieldToDbForMat(field.getName())));
                }
            } catch (InstantiationException | IllegalAccessException e) { 
                log.error("auto schedule plugin create jdbc result set error, the class name :{}", clz.getName());
            }
            return (T) instance;
        }
    
        public List<T> list(String sql, Object... args) { 
            try { 
                return jdbcTemplate.query(sql, this, args);
            } catch (Exception ex) { 
                return null;
            }
        }
    
        public T find(String sql, Object... args) { 
            try { 
                jdbcTemplate.setQueryTimeout(1000);
                T t = jdbcTemplate.queryForObject(sql, this, args);
                return t;
            } catch (Exception e) { 
                return null;
            }
        }
    
        public void save(T object) { 
            String sql = this.getSaveSql(object);
            log.info(sql);
            jdbcTemplate.execute(sql);
        }
    
        public String getSaveSql(T object) { 
            String sql = "insert into t_" + this.classToDbForMat(object.getClass().getSimpleName());
            Field[] fields = object.getClass().getDeclaredFields();
            List<String> f = new ArrayList<String>();
            List<String> v = new ArrayList<String>();
            SimpleDateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            for (Field field : fields) { 
    
                field.setAccessible(true);
                try { 
                    Object value = field.get(object);
                    if (value != null) { 
                        f.add(this.fieldToDbForMat(field.getName()));
                        if (value instanceof Date) { 
                            v.add("'" + sqlFormat.format(value) + "'");
                        } else { 
                            v.add("'" + value.toString() + "'");
                        }
                    }
                } catch (IllegalArgumentException | IllegalAccessException e) { 
                    log.error("auto schedule plugin save domain error ...");
                }
            }
            sql += "(" + StringUtils.join(f, ",") + ") values(" + StringUtils.join(v, ",") + ")";
            log.info("save sql is:{}",sql);
            return sql;
        }
    
        public void delete(T object) { 
            String sql = "delete from t_" + this.classToDbForMat(object.getClass().getSimpleName()) + " where 1=1";
            Field[] fields = object.getClass().getDeclaredFields();
            sql = getFieldName(object, sql, fields);
            log.info(sql);
            jdbcTemplate.execute(sql);
        }
    
        public int update(T object) { 
            String sql = "update t_" + this.classToDbForMat(object.getClass().getSimpleName());
            String id = "";
            List<String> list = new ArrayList<>();
            SimpleDateFormat sqlFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Field[] fields = object.getClass().getDeclaredFields();
            for (Field field : fields) { 
                field.setAccessible(true);
                try { 
                    Object value = field.get(object);
                    if ("id".equals(field.getName())) { 
                        id = value.toString();
                    } else { 
                        if (value instanceof Date) { 
                            list.add(this.fieldToDbForMat(field.getName()) + " = " + (value == null ? "NULL" : "'" + sqlFormat.format(value) + "'"));
                        } else { 
                            list.add(this.fieldToDbForMat(field.getName()) + " = " + (value == null ? "NULL" : "'" + value.toString() + "'"));
                        }
    
                    }
                } catch (IllegalArgumentException | IllegalAccessException e) { 
                }
            }
            sql += " set " + StringUtils.join(list, ",") + " where id=" + id;
            log.info(sql);
            return jdbcTemplate.update(sql);
        }
    
        private String getFieldName(T object, String sql, Field[] fields) { 
            for (Field field : fields) { 
                field.setAccessible(true);
                try { 
                    Object value = field.get(object);
                    if (value != null) { 
                        if (value instanceof String && ((String) value).contains("%")) { 
                            sql += " and " + this.fieldToDbForMat(field.getName()) + " like '" + value.toString() + "'";
                        } else { 
                            sql += " and " + this.fieldToDbForMat(field.getName()) + " = '" + value.toString() + "'";
                        }
                    }
                } catch (IllegalArgumentException | IllegalAccessException e) { 
                    StackTraceElement element = e.getStackTrace()[0];
                    log.error("unknown exception,method name :{},line number:{}", element.getMethodName(), element.getLineNumber());
                }
            }
            return sql;
        }
    
        public Long saveSingleAndGetId(String sql) { 
            try (Connection connection = getConnection();
                 PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) { 
                int i = 0;
                try { 
                    i = pstmt.executeUpdate();
                } catch (Exception ex) { 
                    log.error("save exception...");
                }
    
                if (i == 1) { 
                    ResultSet generatedKeys = pstmt.getGeneratedKeys();
                    if (generatedKeys.next()) { 
                        long aLong = generatedKeys.getLong(1);
                        log.info("insert trigger to db success,trigger id is :{}", aLong);
                        return aLong;
                    }
                }
            } catch (Exception e) { 
                log.info("save single and get id error ,error message:{}", e.getMessage());
            }
            return null;
        }
    
    
        private Class<T> getTrueClass() { 
            ParameterizedType type = (ParameterizedType) this.getClass().getGenericSuperclass();
            Class<T> clazz = (Class<T>) type.getActualTypeArguments()[0];
            return clazz;
        }
    
        private String fieldToDbForMat(String fieldName) { 
            return CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldName);
        }
    
        private String classToDbForMat(String className) { 
            return CaseFormat.UPPER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, className);
        }
    
        public void executeSql(String sql) { 
            jdbcTemplate.execute(sql);
        }
    
        public void executeUpdateSql(String sql, Object[] params) { 
            jdbcTemplate.update(sql, params);
        }
    
    }

> 附上一段手动实例JdbcTemplate的代码. 根据实际运用,可做调整

//获取dataSource连接
    	DriverManagerDataSource dataSource  = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(dbUsername);
        dataSource.setPassword(dbPassword);
        //实例化jdbcTemplate
        JdbcTempalte jdbcTempalte = new JdbcTemplate(dataSource);