在Java中对数据库数据进行查询

1，使用commonApiDao

当要对两个或者多个表进行联查的时候，此方法比较繁杂，不建议使用

//将数组转换成list集合 List<String> cliniids = Arrays.asList(paramMap.get("ids").split(",")); //提取unitmapEntityList集合中的部分数据 List<String> unitCodeList = unitmapEntityList.stream().collect(Collectors.mapping(TableEntity::getUnitcode, Collectors.toList())); List<TabletEntity> eresultlist = commonApiDao.getScrollData(TableEntity.class, " itemCode=?0 and clinicItemCode=?1 and unitcode in (?2) and cfgcomplete=0 ", new Object[]{paraMap.get("itemCode"), paraMap.get("clinicItemCode"), unitCodeList}, new LinkedHashMap<>()).getResultlist(); //常用查询 TabletEntity tabletEntity= commonApiDao.find(TabletEntity.class,param.get("id")); //注意：条件参数从0开始【?0】，否则报错 commonApiDao.batchInsert(eresultlist); commonApiDao.batchUpdate(eresultlist); //注意：将eresultlist中的集合数据批量操作

2，调用dao层函数**

//controller List<String> pkgIdList = clinicallist.stream().collect(Collectors.mapping(TableEntity::getId, Collectors.toList())); List<TableEntity> pkgmaplist=tableEntityDao.findByPkgIds(pkgIdList); //dao @Query("select s from TableEntity s where s.pkgId in (?1)") List<TableEntity> findByPkgIds(List<String> pkgId); 注意：条件参数从1开始【?1】

3，使用jdbcTemplate

3.1 拼接sql

String sql = "select ID,pkgId,pkgName,pkgCode from table where 1=1 "; if(!StringUtil.isEmptyOrLength0(paramMap.get("unitid"))){ sql+=" and unitid='"+paramMap.get("unitid")+"'"; } if(!StringUtil.isEmptyOrLength0(paramMap.get("clinicalname"))){ sql+=" and pkgName like '%"+paramMap.get("clinicalname")+"%'"; } sql+=" order by pkgCode asc"; //简单的查询 List<Map<String, Object>> list=jdbcTemplate.queryForList(sql);

3.2 jdbcTemplate 设置参数的查询

String tmpsqlks = " select code from table where 1=1 and levelcode2 = ? "; //返回List<String>类型 List<String> kscodeList = jdbcTemplate.queryForList(tmpsqlks, new Object[]{paraMap.get("unitcode")}, String.class); //返回List<YgZixunEntity>类型 List<YgZixunEntity> ygZixunOrderinfoEntityListTmp1 = jdbcTemplate.query( "select * from table where (publishtime>=? and publishtime<=? and zfstatus in(?))", new Object[]{timestampSt,timestampEnd,code}, new BeanPropertyRowMapper<>(YgZixunEntity.class));

3.3 NamedParameterJdbcTemplate 设置参数的查询

方式一：

//需要先载入数据库的连接驱动，如果第一次启动项目的时候npJdbcTemplate=null，则重启项目即可 @Autowired NamedParameterJdbcTemplate npJdbcTemplate; //编写sql，配置参数，执行 String deleteSql=" delete from table where itemCode=:itemCode and unitcode in (:unitcode)"; HashMap<String, Object> paramMap = new HashMap<>(); paramMap.put("unitcode",queryForObject); paramMap.put("itemCode",entity.getItemCode()); npJdbcTemplate.update(deleteSql, paramMap);

方式二：

//编写sql，配置参数，执行 String[] ids =paramMap.get("id").split(","); String sql="delete from table WHERE id in (:id)"; NamedParameterJdbcTemplate namedParameterJdbcTemplate=new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource());//连接数据库 MapSqlParameterSource argsMap=new MapSqlParameterSource(); argsMap.addValue("id", Arrays.asList(ids)); //参数 namedParameterJdbcTemplate.update(sql, argsMap);