【java】实用Hibernate SqlMaker类 以及 增删查改实例

public class SqlMaker {
	private StringBuilder sql;  //sql语句
	private Map<String, Object> values;

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private SqlMaker() {
		this.sql = new StringBuilder(200);  //默认是200
		this.values = new HashMap();
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private SqlMaker(int capacity) {
		this.sql = new StringBuilder(capacity);
		this.values = new HashMap();
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private SqlMaker(String sql) {
		this.sql = new StringBuilder(sql);  //初始化sql语句
		this.values = new HashMap();
	}

	public static SqlMaker getInstance() {  //单例模式创建实例 默认capacity 200
		return new SqlMaker();  
	}

	public static SqlMaker getInstance(int capacity) {
		return new SqlMaker(capacity);
	}

	public static SqlMaker getInstance(String sql) {
		return new SqlMaker(sql);
	}

	public SqlMaker eq(String column, Object paramValue) {   //等于 AND A = :A
		return eq(column, column, paramValue);
	}

	public SqlMaker eq(String column, String paramKey, Object paramValue) {
		return pop(column, paramKey, paramValue, " = ");
	}

	public SqlMaker ne(String column, Object paramValue) { //不等于<>
		return ne(column, column, paramValue);
	}

	public SqlMaker ne(String column, String paramKey, Object paramValue) { 
		return pop(column, paramKey, paramValue, " <> ");
	}

	public SqlMaker like(String column, String paramValue) { //like 相似
		return like(column, column, paramValue);
	}

	public SqlMaker like(String column, String paramValue, MatchMode matchMode) {
		return like(column, column, paramValue, matchMode);
	}

	public SqlMaker like(String column, String paramKey, String paramValue) {
		return like(column, paramKey, paramValue, MatchMode.ANYWHERE);
	}

	public SqlMaker like(String column, String paramKey, String paramValue, //匹配模式
			MatchMode matchMode) {
		if (StringUtils.isBlank(paramValue)) {
			return this;
		}
		SqlMaker maker = pop(column, paramKey,
				matchMode.toMatchString(paramValue), " like ");

		maker.getSql().append(matchMode.getEscapeSql());
		return maker;
	}

	public SqlMaker gt(String column, Object paramValue) { //大于
		return gt(column, column, paramValue);
	}

	public SqlMaker gt(String column, String paramKey, Object paramValue) {
		return pop(column, paramKey, paramValue, " > ");
	}

	public SqlMaker lt(String column, Object paramValue) { //小于
		return lt(column, column, paramValue);
	}

	public SqlMaker lt(String column, String paramKey, Object paramValue) {
		return pop(column, paramKey, paramValue, " < ");
	}

	public SqlMaker le(String column, Object paramValue) { //小于等于
		return le(column, column, paramValue);
	}

	public SqlMaker le(String column, String paramKey, Object paramValue) {
		return pop(column, paramKey, paramValue, " <= ");
	}

	public SqlMaker ge(String column, Object paramValue) { //大于等于
		return ge(column, column, paramValue);
	}

	public SqlMaker ge(String column, String paramKey, Object paramValue) {
		return pop(column, paramKey, paramValue, " >= ");
	}

	public SqlMaker in(String column, Object[] values) {  //in
		return in(column, column, values);
	}

	public SqlMaker in(String column, String paramKey, Object[] values) {
		if ((values != null) && (values.length > 0)) {
			getSql().append(" and ").append(column).append(" in(:")
					.append(paramKey).append(")");

			getValues().put(paramKey, values);
		}
		return this;
	}

	public SqlMaker in(String column, Collection<?> values) {
		return in(column, column, values);
	}

	public SqlMaker in(String column, String paramKey, Collection<?> values) {
		if ((values != null) && (values.size() > 0)) {
			getSql().append(" and ").append(column).append(" in(:")
					.append(paramKey).append(")");

			getValues().put(paramKey, values.toArray());
		}
		return this;
	}

	public StringBuilder append(String sql) {  //拼接字符串
		return getSql().append(sql);
	}

	public StringBuilder append(Object obj) {
		return getSql().append(obj);
	}

	public Map<String, Object> put(String key, Object value) {  //设置值
		getValues().put(key, value);
		return getValues();
	}

	public StringBuilder getSql() {
		return this.sql;
	}

	public void setSql(StringBuilder sql) {
		this.sql = sql;
	}

	public String getSqlString() {
		return this.sql.toString();
	}

	public void setSql(String sql) {
		this.sql = new StringBuilder();
		this.sql.append(sql);
	}

	public Map<String, Object> getValues() {
		return this.values;
	}

	public void setValues(Map<String, Object> values) {
		this.values = values;
	}

	private SqlMaker pop(String column, String paramKey, Object paramValue,  //核心
			String option) {
		if (((paramValue instanceof String))
				&& (StringUtils.isNotBlank((String) paramValue))) {
			getSql().append(" and ").append(column).append(option).append(":")
					.append(paramKey).append(" ");
			getValues().put(paramKey, ((String) paramValue).trim());
		} else if ((!(paramValue instanceof String)) && (paramValue != null)) {
			getSql().append(" and ").append(column).append(option).append(":")
					.append(paramKey).append(" ");
			getValues().put(paramKey, paramValue);
		}
		return this;
	}
}

查询

分页列表

   public Page<PlanDTO> queryPageList(Page<PlanDTO> page, PlanDTO dto) {
	   SqlMaker maker = SqlMaker.getInstance(300);
	   maker.append("SELECT p.*,a.account_name,tid.name as user_name,c.bank_name,c.bank_code,c.card_no,pf.pay_name as plate_form_name from t_plan p ");
	   maker.append(" where 1 =1 ");  //where 1=1 的用处是拼接后面的条件
	
	   if (StringUtils.isNoneBlank(dto.getAccountId())) {  //查询条件
	       maker.eq("p.account_id", dto.getAccountId());
	   }
	   maker.append(" order by p.create_time desc "); 
	   return findSqlPage(page, maker.getSqlString(), PlanDTO.class, maker.getValues());
}

获取实体类

public PlanEntity findById(String id) {
    SqlMaker maker = SqlMaker.getInstance(300);
    maker.append(" SELECT * from t_plan where 1=1 ");
    maker.eq("id", id);
    return (PlanEntity) this.createSqlQuery(maker.getSqlString(), maker.getValues()).
            setResultTransformer(ResultTransformers.aliasToBean(PlanEntity.class)).uniqueResult();
}

返回list

 public List<HomeMenuDto> queryHomeMenuList() {
    SqlMaker sqlMaker = SqlMaker.getInstance(300);
    sqlMaker.append("select * from t_home_menu where 1=1 ");
    return (List<HomeMenuDto>) this.createSqlQuery(sqlMaker.getSqlString(), sqlMaker.getValues()).
            setResultTransformer(ResultTransformers.aliasToBean(HomeMenuDto.class)).list();
}

改

public void confirmPlan(String planId) {
    String sql = "update t_plan set status = '1' where id= :planId and status='0' ";
    HashMap<String, Object> values = new HashMap<>();
    values.put("planId", planId);
    this.createSqlQuery(sql, values).executeUpdate();
}

删

 public void deleteHomeMenuById(String id) {
   SqlMaker sqlMaker = SqlMaker.getInstance(300);
   sqlMaker.append("delete from t_home_menu where 1=1 ");
   sqlMaker.eq("id", id);
   createSqlQuery(sqlMaker.getSqlString(), sqlMaker.getValues()).executeUpdate();
}

增

添加在serviceImp中

public void addHomeMenu(HomeMenuDto homeMenuDto){
    HomeMenuEntity homeMenuEntity = new HomeMenuEntity();
    BeanUtil.copyCommonProperties(homeMenuDto,homeMenuEntity);
    homeMenuEntity.setCreateTime(new Date());
    this.homeMenuDao.save(homeMenuEntity);
}