JdbcTemplate的通用BaseDao

在实现一个小项目的时候, 通常会使用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);