在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);