JdbcTemplate 和 entityManager 使用案例

有关后台处理数据的方法

JdbcTemplate 的查询举例说明

一：返回一个数字

public Integer queryByOid(TbbrpPerson tbbrpPersons) {

     

String sql=" SELECT count(*) FROM tbbrp_person T WHERE   T.OID=?";

      Integer personOid = jdbcTemplate.queryForObject(sql,

      new Object[] {tbbrpPersons.getOid() }, Integer.class);

      return personOid;

   }

知识点：将会返回一个数字：Integer.class指定返回类型

 

二：返回部分属性值

public List<String> queryImageInfo(List<ImageInfo> imageInfos){

   StringBuffer inIds=new StringBuffer();

   final List<String> oidList=new ArrayList<String>();

   try {

      for(ImageInfo imageInfo:imageInfos){

         inIds.append("'"+imageInfo.getOid()+"'"+",");

      }

 

jdbcTemplate.query("SELECT t.oid FROM tbbrp_imageinfo t WHERE t.oid IN("+inIds.toString().substring(0,inIds.length()-1)+")",                  new RowCallbackHandler() {

      @Override

   public void processRow(ResultSet rs) throws SQLException {

   oidList.add(rs.getString("oid").replaceAll("", ""));

//如果字段为多个时，可以将值放在对象里，然后放在集合里

            }

   });

   } catch (Exception e) {

         e.printStackTrace();

   }

      return oidList;

   }

知识点：当要获取表的部分字段记录时，可以从rs集合中获取一条记录后在封装所有记录返回出去；

 

 

三：批量查询返回实体数据

首先创建一个类：

public class TellInfoRowMapper implements RowMapper<TellInfo> {

   @Override

public TellInfo mapRow(ResultSet rs, int index) throws SQLException {   

TellInfo tellInfo = new TellInfo();                                   tellInfo.setInsurer_serial_no(rs.getString("insurer_serial"));  

tellInfo.setOid(rs.getString("oid"));

tellInfo.setTell_code(rs.getString("tell_code"));

tellInfo.setTell_person(rs.getString("tell_person"));

tellInfo.setTell_remark(rs.getString("tell_remark"));

tellInfo.setTell_result(rs.getString("tell_result"));

 

      return tellInfo;

   }

 

}

 

其次具体使用

   @Override

   public List<TellInfo> getTellInfo(List<String> serials) {

      StringBuilder sql = new StringBuilder();

      String separator = "','";

      String inParams = listToStrings(serials, separator);

      inParams = inParams.substring(0, inParams.length() - 1);

      sql.append(QUERY_TELLINFO_SQL);

      sql.append(" insurer_serial_no in ( '");

      sql.append(inParams);

      sql.append(" )");

      List<TellInfo> tellinfos=jdbcTemplate.query(sql.toString(), new  TellInfoRowMapper());

      return tellinfos;

   }

四：使用？占位符的参数进行查询

 

   @SuppressWarnings("unchecked")

   public List queryPersion(Map param) throws Exception {

      final String ssNumber = (String)param.get("ssNumber");

      final String name = (String)param.get("name");

      final Date birthday = (Date)param.get("birthday");

      final String gender =  (String)param.get("gender");

      StringBuffer sql = new StringBuffer();

       sql.append("  SELECT                              ")   

      .append("  custinfo.custid AS SAMEONE              ")

      .append("  FROM                                 ")

      .append("  (                                    ")

      .append(" SELECT                            ")

      .append("    (                              ")   

      .append("       NVL2(CUST_ID, CUST_ID, 'N')  ")      

      .append("    ) custid                       ")

      .append(" FROM                              ")

      .append(" tbbrp_person                      ")

      .append(" WHERE                             ")

      .append(" (                                 ")

      .append("   SSNUMBER = ?        ")

      .append("    AND cust_name = ?           ")

      .append("    AND to_char(birthday, 'yyyy/mm/dd') = ?  ")

      .append(" )                                             ") 

      .append(" OR (                                          ")     

      .append("    SSNUMBER = ?                  ")  

      .append("    AND cust_name = ?                          ")

      .append("    AND GENDER = ?                             ") 

      .append("   )                                           ") 

      .append(" OR (                                          ") 

      .append("   SSNUMBER = ?                  ") 

      .append("    AND to_char(birthday, 'yyyy/mm/dd') = ?  ")

      .append("    AND GENDER = ?                             ") 

      .append("   )                                           ") 

      .append(" OR (                                          ") 

      .append("    cust_name = ?                              ") 

      .append("    AND to_char(birthday, 'yyyy/mm/dd') = ?  ")

      .append("    AND GENDER = ?                             ") 

      .append(" )                                             ")     

      .append(" ) custinfo                                                   ")

      .append(" WHERE                                            ")

      .append(" custid <> 'N'                                                ")

      .append(" AND EXISTS (                                                 ") 

      .append(" SELECT                                           ") 

      .append(" 1                                             ")

      .append(" FROM                                                         ")

      .append(" tbbrp_policy p,                                           ")

      .append(" tbbrp_policy_others po                           ") 

      .append(" WHERE                                                        ")

      .append(" p.insured_id = custinfo.custid                      ")

      .append(" AND p.oid = po.policy_oid                                 ")

      .append(" AND (                                                     ")

      .append(" P.INSURE_STAT = 'OK'                             ") 

      .append(" OR p.insure_stat LIKE '%FF%'                              ")

      .append(" OR po.term_order_status IN ('WFC', 'WFP')            ")

      .append(" )                                                          ")

      .append(" ) order by SAMEONE desc                                                            ");

       Object[] objs = new Object[]{

             ssNumber,name, DateUtil.getStringFromDate(birthday, "yyyy/MM/dd"),   

             ssNumber,name,gender,

             ssNumber,DateUtil.getStringFromDate(birthday, "yyyy/MM/dd"),gender,

             name, DateUtil.getStringFromDate(birthday, "yyyy/MM/dd"),gender

       };

       int[] argTypes = new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,

                            Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR

       };

      

       List list = jdbcTemplate.queryForList(sql.toString(), objs, argTypes);

 

      return list;

   }

 

知识点：一共有多少个？号就在objs 集合里一次对应每个问号取值argTypes 对应取值的类型。

 

 

JdbcTemplate 的插入举例说明

一：批量插入数据

private static final String TELLINFO_SQL="INSERT INTO tbbrp_tellinfo ("

         + "OID,"

         + "INSURER_SERIAL_NO,"

         + "TELL_CODE,"

         + "TELL_RESULT,"

         + "TELL_REMARK,"

         + "TELL_PERSON )"

         + "VALUES"

         + "(?,?,?,?,?,?) ";

   public void saveTellInfo(final List<TellInfo> tellInfos){

      jdbcTemplate.batchUpdate(TELLINFO_SQL,new   BatchPreparedStatementSetter() {

        

         @Override

public void setValues(PreparedStatement ps, int i) throws SQLException {

         TellInfo tellInfo=tellInfos.get(i);

         int index=0;

         ps.setString(++index, tellInfo.getOid());

         ps.setString(++index, tellInfo.getInsurer_serial_no());

         ps.setString(++index, tellInfo.getTell_code());

         ps.setString(++index, tellInfo.getTell_result());

         ps.setString(++index, tellInfo.getTell_remark());

         ps.setString(++index, tellInfo.getTell_person());

         }

 

知识点：批量插入，所以？于index的数量相等

 

 

 

 

 

 

 

JdbcTemplate 的更新举例说明

 

   private static final String PERSON_UPDATE_SQL = "update tbbrp_person set\n" +

         "  cust_id=?,\n" +

         "  cust_name=?,\n" +

         "  nationality=?,\n" +

         "  residence=?,\n" +

         "  gender=?,\n" +

         "  id_type=?,\n" +

         "  ssnumber=?,\n" +

         "  birthday=?,\n" +

         "  work_tel=?,\n" +

         "  home_tel=?,\n" +

         "  mobile_tel=?,\n" +

         "  email=?,\n" +

         "  company=?,\n" +

         "  caste=?,\n" +

         "  mail_address=?,\n" +

         "  mail_zip=?,\n" +

         "  living_address=?,\n" +

         "  living_zip=?,\n" +

         "  height=?,\n" +

         "  weight=?,\n" +

         "  cust_stat=?,\n" +

         "  province_code=?,\n" +

         "  city_code=?,\n" +

         "  district_code=?,\n" +

         "  bnft_insure_rela=?,\n" +

         "  bnft_sequence=?,\n" +

         "  bnft_percentage=?,\n" +

         "  bnft_insurer_serial_no=?,\n" +

         "  mail_province_code=?,\n" +

         "  mail_city_code=?,\n" +

         "  mail_district_code=?,\n" +

         "  offic_province_code=?,\n" +

         "  offic_city_code=?,\n" +

         "  offic_district_code=?,\n" +

         "  offic_address=?,\n" +

         "  offic_zip=?,\n" +

         "  bnft_insure_rela_bank=?,\n" +

         "  bnft_type=?,\n" +

         "  caste_name=?,\n" +

         "  cust_id_in_ff=?,\n" +

         "  salary=?,\n" +

         "  rsdnt_type=?,\n" +

         "  company_type=?,\n" +

         "  marital_status=?,\n" +

         "  id_expiry_dt= to_date(?,'YYYY-MM-DD HH24:MI:SS'),\n" +

         "  work_tel_areacode=?,\n" +

         "  home_tel_areacode=?,\n" +

         "  smoke_years=?,\n" +

         "  day_smoking=?,\n" +

         "  quit_smoke_rsn=?,\n" +

         "  quit_smoke_date=?,\n" +

         "  alcohol_years=?,\n" +

         "  day_alcohol=?,\n" +

         "  alcohol_type=?,\n" +

         "  quit_alcohol_rsn=?,\n" +

         "  quit_alcohol_date=?,\n" +

         "  born_height=?,\n" +

         "  born_weight=?,\n" +

         "  birth_hod=?,\n" +

         "  born_remark=?,\n" +

         "  pregnant_weeks=?,\n" +

         "  medical_remark=?,\n" +

         "  national_code=?,\n" +

         "  mail_national_code=?,\n" +

         "  offic_national_code=?\n" +

         "WHERE oid=?";

 

public void updatePerson(final List<TbbrpPerson> personList) {

      jdbcTemplate.batchUpdate(PERSON_UPDATE_SQL, new BatchPreparedStatementSetter(){

         @Override

         public int getBatchSize() {

            return personList.size();

         }

 

         @Override

         public void setValues(PreparedStatement ps, int i)   throws SQLException {

            TbbrpPerson person = personList.get(i);

            int index = 0;

            ps.setString(++index, person.getCustId());

            ps.setString(++index, person.getCustName());

            ps.setString(++index, person.getNationality());

            ps.setString(++index, person.getResidence());

            ps.setString(++index, person.getGender());

            ps.setString(++index, person.getIdType());

            ps.setString(++index, person.getSsnumber());

            ps.setDate(++index, new java.sql.Date(person.getBirthday().getTime()));

            ps.setString(++index, person.getWorkTel());

            ps.setString(++index, person.getHomeTel());

            ps.setString(++index, person.getMobileTel());

            ps.setString(++index, person.getEmail());

            ps.setString(++index, person.getCompany());

            ps.setString(++index, person.getCaste());

            ps.setString(++index, person.getMailAddress());

            ps.setString(++index, person.getMailZip());

            ps.setString(++index, person.getLivingAddress());

            ps.setString(++index, person.getLivingZip());

            ps.setBigDecimal(++index, person.getHeight());

            ps.setBigDecimal(++index, person.getWeight());

            ps.setString(++index, person.getCustStat());

            ps.setString(++index, person.getProvinceCode());

            ps.setString(++index, person.getCityCode());

            ps.setString(++index, person.getDistrictCode());

            ps.setString(++index, person.getBnftInsureRela());

            ps.setString(++index, person.getBnftSequence());

            ps.setString(++index, person.getBnftPercentage());

            ps.setString(++index,  person.getBnftInsurerSerialNo());

            ps.setString(++index, person.getMailProvinceCode());

            ps.setString(++index, person.getMailCityCode());

            ps.setString(++index, person.getMailDistrictCode());

            ps.setString(++index, person.getOfficProvinceCode());

            ps.setString(++index, person.getOfficCityCode());

            ps.setString(++index, person.getOfficDistrictCode());

            ps.setString(++index, person.getOfficAddress());

            ps.setString(++index, person.getOfficZip());

            ps.setString(++index, person.getBnftInsureRelaBank());

            ps.setString(++index, person.getBnftType());

            ps.setString(++index, person.getCasteName());

            ps.setString(++index, person.getCustId());//cust_id_in_ff

            ps.setString(++index, person.getSalary());

            ps.setString(++index, person.getRsdntType());

            ps.setString(++index, person.getCompanyType());

            ps.setString(++index, person.getMaritalStatus());

            ps.setString(++index, person.getIdExpiryDt() == null ? null : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(person.getIdExpiryDt()));

            ps.setString(++index, person.getWorkTelAreaCode());

            ps.setString(++index, person.getHomeTelAreaCode());

            ps.setString(++index, person.getSmokeYears());

            ps.setString(++index, person.getDaySmoking());

            ps.setString(++index, person.getQuitSmokeRsn());

            ps.setString(++index, person.getQuitSmokeDate());

            ps.setString(++index, person.getAlcoholYears());

            ps.setString(++index, person.getDayAlcohol());

            ps.setString(++index, person.getAlcoholType());

            ps.setString(++index, person.getQuitAlcoholRsn());

            ps.setString(++index, person.getQuitAlcoholDate());

            ps.setBigDecimal(++index, person.getBornHeight());

            ps.setBigDecimal(++index, person.getBornWeight());

            ps.setString(++index, person.getBirthHOD());

            ps.setString(++index, person.getBornRemark());

            ps.setString(++index, person.getPregnantWeeks());

            ps.setString(++index, person.getMedicalRemark());

            ps.setString(++index, person.getNationalCode());

            ps.setString(++index, person.getMailNationalCode());

            ps.setString(++index, person.getOfficNationalCode());

            ps.setString(++index,  person.getOid());

         }

        

      });

   }

 

 

 

 

JdbcTemplate 调用存储过程举例说明

   public Map<String, Object> savePipInfo(Map param) {

      Map<String, Object> result = new HashMap<String, Object>();

      final String policyNumber = (String) param.get("policyNumber");

      final String brp = (String) param.get("brp");

      final String pipType = (String) param.get("pipType");

      jdbcTemplate.execute(new CallableStatementCreator() {

      public CallableStatement createCallableStatement(Connection con) throws SQLException {

      String storedProc = "{call pkg_brp_public.p_write_pip_info(?,?,?,?)}";//

      // 调用的sql

      CallableStatement cs = con.prepareCall(storedProc);

      cs.setString(1, policyNumber);

      cs.setString(2, brp);

      cs.setString(3, pipType);

      cs.setString(4, null);

      return cs;

         }

      },

new CallableStatementCallback() {

public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {

   cs.execute();

   return "";

         }

      });

 

 

entityManager  查询举例

一：查出返回一个集合的数据 query.getResultList();

Query query = entityManager.createNativeQuery(strsql.toString());

         logger.info("checkribao begin toString:查询日报发布时效");

         query.setParameter("dateTime", date + "%");

         query.setParameter("begin", page.getBegin());

         query.setParameter("end", page.getEnd());

         List<Object[]> list = query.getResultList();

 

二：返回一个值的查询方法 query.getSingleResult();

StringBuffer strsql = new StringBuffer();

      strsql.append(" select  ");

      strsql.append(" substr(max( t.data_date),0,4) as maxyeardate  ");

      strsql.append(" from TB_DIT_RULES t ");

      strsql.append("    where t.rs_code in ('A42',      'A23',  'A30',  'A13',   'A28',   'A25',   'A33',   'A38',    'A34',   'A32',   'A26',  'A41') ");

      strsql.append("    and t.issue_frequency not like 'RB' ");

      strsql.append("    and t.sys_type = '1' ");

      Query query = entityManager.createNativeQuery(strsql.toString());

      Object maxYearString = query.getSingleResult();

      return (String) maxYearString;

entityManager  插入数据

String savesqlStr="insert into daybaojytargstateresult(rbdate,smscode,appname,state,messages)" +

      "values(:rbdate,:smscode,:appname,:state,:messages)";

      try {

      Query query=entityManager.createNativeQuery(savesqlStr.toString());

      query.setParameter("rbdate", date);

      query.setParameter("smscode", map.get("smscode"));

      query.setParameter("appname", map.get("appname"));

      query.setParameter("state", map.get("state"));

      query.setParameter("messages", strmess);

      int index=query.executeUpdate();

 

entityManager  修改数据

updasql.append("  update app_column_info t  set t.subname=:subname ,t.subsort=:subsort ,t.editstate=:editstate ,t.username=:username where t.appcode=:appcode and t.subcode=:subcode ");

          Query query=entityManager.createNativeQuery(updasql.toString());

        

          query.setParameter("subname", mapparam.get("subname"));

          query.setParameter("subsort", mapparam.get("subsort"));

          query.setParameter("editstate", mapparam.get("editstate"));

          query.setParameter("username", mapparam.get("username"));

          query.setParameter("appcode",appCode);

          query.setParameter("subcode", mapparam.get("subcode"));

          query.setParameter("subcode", mapparam.get("subcode"));

          int sum=query.executeUpdate();

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

entityManager 用$符号占位拼接sql数据

String strsqls = " select t.NAME_EN,t.NAME_ZH,t.DESCRIPTION,t.editable,to_char(t.CREATE_DATE,'yyyy-mm-dd hh24:mi:ss'),to_char(t.update_date,'yyyy-mm-dd hh24:mi:ss') from  bas_group t where  $NAMEZH$   $COMCODE$";

      try {

      // 获取当前登录用户  获取用户的comcode

      LoginUserDetails userDetails = LoginUserUtil.getLoginUser();

       User user = userService.doFindById(userDetails.getUserId());

         Map<String, Object> params=queryCriteria.getQueryCondition();

         String namezhStr=(String) params.get("nameZh");

         if (StringUtils.isNotEmpty(namezhStr)) {

            strsqls = strsqls.replace("$NAMEZH$",

                   "t.name_zh like :namezh and ");

            strsqls = strsqls.replace("$COMCODE$",

            " t.comcode=:comcode ");

         } else {

            strsqls = strsqls.replace("$NAMEZH$", "");

            strsqls = strsqls.replace("$COMCODE$",

            " t.comcode=:comcode ");

         }

         Query query=em.createNativeQuery(strsqls);

         if(StringUtils.isNotEmpty(namezhStr)){

            query.setParameter("namezh",namezhStr );

         }

         if(StringUtils.isNotEmpty(user.getComCode())){

            query.setParameter("comcode",user.getComCode() );

         }