利用EntityManager封装的数据库查询模板-Toy模板网

这篇具有很好参考价值的文章主要介绍了利用EntityManager封装的数据库查询模板。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

RestService.java

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.annotation.Propagation;

import com.alibaba.fastjson.JSON;

import JsonMapper;

@Service
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
public class RestService {

@PersistenceContext
private EntityManager em;

private static Logger log = LoggerFactory.getLogger(RestService.class);

   public String GetStringDemo(int id) {
   String sql = "select content from ** where id=" + id;
   return queryBySql2String(sql, "content");
}

   public int getIntDefaultDemo(int id) {
   String sql = "select intColumn from ** where id=" + id;
   return queryBySql2IntDefault(sql, "intColumn");
}

   public int getIntDemo(int id) {
   String sql = "select count(*) as cnt from ** where id=" + id;
   int count = queryBySql2Int(sql, "cnt");
       return count;
}

   //此处的Object请替换成具体的类
   public Object getEntityDemo(int id) {
   String sql = "select * from ** where id=" + id;
return queryBySql2Entity(sql, null, Object.class);
}

   //此处的Object请替换成具体的类
   public List<Object> getEntityListDemo(int id){
   String sql = "select * from ** where id=" + id;
return (List<Object>)queryBySql2List(sql, null, Object.class);
}


   // 更新数据。
   @Transactional(propagation = Propagation.REQUIRED, readOnly = false, rollbackFor = { Exception.class })
public boolean updateDemo(int id) {
       String sql = "delete from **";
sql += " where id=" + id;
       return updateBySql(sql, null);
}

   //此处的Object请替换成具体的类
   public Object queryBySql2pojoDemo(int id) {
   String sql = "select * from ** where id=" + id;
   List<Object> entities = queryBySql2pojo(sql, null, List.class, Object.class);
   return entities!=null && entities.size()>0?entities.get(0):null;
}

// 通过表名获取最大的ID号
   public int getMaxIdFromMysql(String tablename) {
       String sql = "select max(id) from " + tablename + " ";
       return queryBySql2Int(sql, "max(id)");
   }

   @SuppressWarnings("unchecked")
   public String queryBySql2String(String sql, String key) {
       try {
           Query query = em.createNativeQuery(sql);
           List<Map<String, String>> mapList = query.unwrap(org.hibernate.Query.class)
                   .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
           return mapList != null && mapList.size() > 0 ? mapList.get(0).get(key) : null;
       } catch (Exception ex) {
           ex.printStackTrace();
           log.error("err", ex);
           return null;
       }
   }

   @SuppressWarnings("unchecked")
   public int queryBySql2IntDefault(String sql, String key) {
       try {
       Query query = em.createNativeQuery(sql);
           List<Map<String, BigInteger>> mapList = query.unwrap(org.hibernate.Query.class)
                   .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
           return mapList!=null&&mapList.size()>0 ? (mapList.get(0).get(key)!=null?mapList.get(0).get(key).intValue():-1) : -1;
       }catch(Exception ex) {
           ex.printStackTrace();
           log.error("err", ex);
           return -1;
       }
   }

   @SuppressWarnings("unchecked")
   public int queryBySql2Int(String sql, String key) {
       try {
       Query query = em.createNativeQuery(sql);
           List<Map<String, BigInteger>> mapList = query.unwrap(org.hibernate.Query.class)
                   .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
           return mapList!=null&&mapList.size()>0 ? (mapList.get(0).get(key)!=null?mapList.get(0).get(key).intValue():0) : 0;
       }catch(Exception ex) {
           ex.printStackTrace();
           log.error("err", ex);
           return -1;
       }
   }


   public <T> T queryBySql2Entity(String sql, Map<String, String> params, Class<?> classOfTypeClass) {
       try {
   List<?> entities = (List<?>)queryBySql2List(sql, params, classOfTypeClass);
   return (entities!=null&&entities.size()>0)?(T)entities.get(0):null;
       }catch(Exception ex) {
      ex.printStackTrace();
      log.error("err", ex);
      return null;
       }
   }

   public List<?> queryBySql2List(String sql, Map<String, String> params, Class<?> classOfTypeClass) {
       try {
           Query query = em.createNativeQuery(sql, classOfTypeClass);
           if(params!=null) {
               Iterator<Entry<String, String>> it = params.entrySet().iterator();
               while(it.hasNext()) {
                   Entry<String, String> entry = it.next();
                   query.setParameter(entry.getKey(), entry.getValue());
               }
           }
           List<?> ret = query.getResultList();
           return ret;
       }catch(Exception ex) {
      ex.printStackTrace();
      log.error("err", ex);
      return null;
       }
   }

   public boolean updateBySql(String sql, Map<String, String> params) {
       try {
           Query query = em.createNativeQuery(sql);
           if(params!=null) {
               Iterator<Entry<String, String>> it = params.entrySet().iterator();
               while(it.hasNext()) {
                   Entry<String, String> entry = it.next();
                   query.setParameter(entry.getKey(), entry.getValue());
               }
           }
           int ret = query.executeUpdate();
           return true;
       }
       catch(Exception ex) {
           ex.printStackTrace();
           log.error("update err.", ex);
           return false;
       }
   }

   @SuppressWarnings("unchecked")
   public <T> T queryBySql2pojo(String sql, Map<String, String> params, Class<?> collectionClass, Class<?>... elementClasses) {
       Query query = em.createNativeQuery(sql);
       if (params != null) {
           Iterator<Entry<String, String>> it = params.entrySet().iterator();
           while (it.hasNext()) {
               Entry<String, String> entry = it.next();
               query.setParameter(entry.getKey(), entry.getValue());
           }
       }
       List mapList = query.unwrap(org.hibernate.Query.class)
               .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

       T ret = JsonMapper.fromJson(JSON.toJSONString(mapList), collectionClass, elementClasses);

       return ret;
   }

}

JsonMapper.java

import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fasterxml.jackson.annotation.JsonInclude.Include;
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.JsonParser.Feature;
import com.fasterxml.jackson.databind.DeserializationFeature;
import com.fasterxml.jackson.databind.JavaType;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.SerializerProvider;
import com.fasterxml.jackson.databind.module.SimpleModule;
import com.fasterxml.jackson.databind.util.JSONPObject;
import com.fasterxml.jackson.module.jaxb.JaxbAnnotationModule;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

/**
* 简单封装Jackson，实现JSON String<->Java Object的Mapper.
* 封装不同的输出风格, 使用不同的builder函数创建实例.
*/
public class JsonMapper extends ObjectMapper {

private static final long serialVersionUID = 1L;

private static Logger logger = LoggerFactory.getLogger(JsonMapper.class);

private static JsonMapper mapper;

   public JsonMapper() {
       this(Include.NON_EMPTY);
   }

   public JsonMapper(Include include) {
       // 设置输出时包含属性的风格
       if (include != null) {
           this.setSerializationInclusion(include);
       }
       // 允许单引号、允许不带引号的字段名称
       this.enableSimple();
       // 设置输入时忽略在JSON字符串中存在但Java对象实际没有的属性
       this.disable(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES);
// 空值处理为空串
       this.getSerializerProvider().setNullValueSerializer(new JsonSerializer<Object>(){
           @Override
           public void serialize(Object value, JsonGenerator jgen,
                   SerializerProvider provider) throws IOException,
                   JsonProcessingException {
               jgen.writeString("");
           }
});
       // 进行HTML解码。
       this.registerModule(new SimpleModule().addSerializer(String.class, new JsonSerializer<String>(){
           @Override
           public void serialize(String value, JsonGenerator jgen,
                   SerializerProvider provider) throws IOException,
                   JsonProcessingException {
               jgen.writeString(StringEscapeUtils.unescapeHtml4(value));
           }
}));
       // 设置时区
       this.setTimeZone(TimeZone.getDefault());//getTimeZone("GMT+8:00")
   }

   /**
   * 创建只输出非Null且非Empty(如List.isEmpty)的属性到Json字符串的Mapper,建议在外部接口中使用.
   */
   public static JsonMapper getInstance() {
       if (mapper == null){
           mapper = new JsonMapper().enableSimple();
       }
       return mapper;
   }

   /**
   * 创建只输出初始值被改变的属性到Json字符串的Mapper, 最节约的存储方式，建议在内部接口中使用。
   */
   public static JsonMapper nonDefaultMapper() {
       if (mapper == null){
           mapper = new JsonMapper(Include.NON_DEFAULT);
       }
       return mapper;
   }

   /**
   * Object可以是POJO，也可以是Collection或数组。
   * 如果对象为Null, 返回"null".
   * 如果集合为空集合, 返回"[]".
   */
   public String toJson(Object object) {
       try {
           return this.writeValueAsString(object);
       } catch (IOException e) {
           logger.warn("write to json string error:" + object, e);
           return null;
       }
   }

   /**
   * 反序列化POJO或简单Collection如List<String>.
   *
   * 如果JSON字符串为Null或"null"字符串, 返回Null.
   * 如果JSON字符串为"[]", 返回空集合.
   *
   * 如需反序列化复杂Collection如List<MyBean>, 请使用fromJson(String,JavaType)
   * @see #fromJson(String, JavaType)
   */
   public <T> T fromJson(String jsonString, Class<T> clazz) {
       if (StringUtils.isEmpty(jsonString)) {
           return null;
       }
       try {
           return this.readValue(jsonString, clazz);
       } catch (IOException e) {
           logger.warn("parse json string error:" + jsonString, e);
           return null;
       }
   }

   /**
   * 反序列化复杂Collection如List<Bean>, 先使用函數createCollectionType构造类型,然后调用本函数.
   * @see #createCollectionType(Class, Class...)
   */
   @SuppressWarnings("unchecked")
   public <T> T fromJson(String jsonString, JavaType javaType) {
       if (StringUtils.isEmpty(jsonString)) {
           return null;
       }
       try {
           return (T) this.readValue(jsonString, javaType);
       } catch (IOException e) {
           logger.warn("parse json string error:" + jsonString, e);
           return null;
       }
   }

   /**
   * 構造泛型的Collection Type如:
   * ArrayList<MyBean>, 则调用constructCollectionType(ArrayList.class,MyBean.class)
   * HashMap<String,MyBean>, 则调用(HashMap.class,String.class, MyBean.class)
   */
   public JavaType createCollectionType(Class<?> collectionClass, Class<?>... elementClasses) {
       return this.getTypeFactory().constructParametricType(collectionClass, elementClasses);
   }

   /**
   * 當JSON裡只含有Bean的部分屬性時，更新一個已存在Bean，只覆蓋該部分的屬性.
   */
   @SuppressWarnings("unchecked")
   public <T> T update(String jsonString, T object) {
       try {
           return (T) this.readerForUpdating(object).readValue(jsonString);
       } catch (JsonProcessingException e) {
           logger.warn("update json string:" + jsonString + " to object:" + object + " error.", e);
       }
       return null;
   }

   /**
   * 輸出JSONP格式數據.
   */
   public String toJsonP(String functionName, Object object) {
       return toJson(new JSONPObject(functionName, object));
   }

   /**
   * 設定是否使用Enum的toString函數來讀寫Enum,
   * 為False時時使用Enum的name()函數來讀寫Enum, 默認為False.
   * 注意本函數一定要在Mapper創建後, 所有的讀寫動作之前調用.
   */
   public JsonMapper enableEnumUseToString() {
       this.enable(SerializationFeature.WRITE_ENUMS_USING_TO_STRING);
       this.enable(DeserializationFeature.READ_ENUMS_USING_TO_STRING);
       return this;
   }

   /**
   * 支持使用Jaxb的Annotation，使得POJO上的annotation不用与Jackson耦合。
   * 默认会先查找jaxb的annotation，如果找不到再找jackson的。
   */
   public JsonMapper enableJaxbAnnotation() {
       JaxbAnnotationModule module = new JaxbAnnotationModule();
       this.registerModule(module);
       return this;
   }

   /**
   * 允许单引号
   * 允许不带引号的字段名称
   */
   public JsonMapper enableSimple() {
       this.configure(Feature.ALLOW_SINGLE_QUOTES, true);
       this.configure(Feature.ALLOW_UNQUOTED_FIELD_NAMES, true);
       return this;
   }

   /**
   * 取出Mapper做进一步的设置或使用其他序列化API.
   */
   public ObjectMapper getMapper() {
       return this;
   }

   /**
   * 对象转换为JSON字符串
   * @param object
   * @return
   */
   public static String toJsonString(Object object){
       return JsonMapper.getInstance().toJson(object);
   }

   /**
   * JSON字符串转换为对象
   * @param jsonString
   * @param clazz
   * @return
   */
   public static Object fromJsonString(String jsonString, Class<?> clazz){
       return JsonMapper.getInstance().fromJson(jsonString, clazz);
   }

//   @SuppressWarnings("unchecked")
   public static <T> T fromJson(String json, Class<?> collectionClass, Class<?>... elementClasses) {
       if (json==null||json.trim().isEmpty()) {
           return null;
       }

       try {
           JavaType type = JsonMapper.getInstance().getTypeFactory().constructParametricType(collectionClass, elementClasses);
           return (T) JsonMapper.getInstance().readValue(json, type);
       } catch (IOException e) {
           logger.warn("parse json string error:" + json, e);
           e.printStackTrace();
           return null;
       }
   }

   /**
   * 测试
   */
   public static void main(String[] args) {
       List<Map<String, Object>> list = Lists.newArrayList();
       Map<String, Object> map = Maps.newHashMap();
       map.put("id", 1);
       map.put("pId", -1);
       map.put("name", "根节点");
       list.add(map);
       map = Maps.newHashMap();
       map.put("id", 2);
       map.put("pId", 1);
       map.put("name", "你好");
       map.put("open", true);
       list.add(map);
       String json = JsonMapper.getInstance().toJson(list);
       System.out.println(json);
   }

}
文章来源地址https://www.toymoban.com/news/detail-495557.html