Java 数据库数据查询

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