关于数据库的一些知识点

开发中常用的关于数据库的知识点

文章目录

开发中常用的关于数据库的知识点

mybatis-Plus之Wrapper的使用建表SQL，对应有注释动态SQL+模糊查询nvl和as的使用UNIOn 和 UNIOn ALL在XML中使用动态sql查询一个时间段内的

在Controller中使用apply、between、to_char等实现一个时间段内的查询 在XML中使用动态sql遍历数组在XML中使用动态sql遍历字符串在XML中使用动态sql判断前端选择的是月还是年当一个列有多种结果，分别求和使用mybatis时，xml层不能直接写大小于号分组查询中，select显示的列只能是分组依据的列或者是聚合函数列Group By，Having，Where，Order by的使用动态表的查询经纬度范围内的sqlIN 和 EXISTS的使用和区别

`结论:主表比从表数据多,使用in的效率比exists高``结论:从表比主表数据多,使用exists的效率比in高` order by的使用删除表数据查询字段长度小于7的临沂项目手机端echart要用的类型数据sql泰安项目综合查询

对应的xml 威海项目企业工作室申办事项查询 数据库报错

java.SQLException: ORA-00936:缺失表达式org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)Error querying database. Cause: java.sql.SQLException: ORA-00909: 参数个数无效Error querying database. Cause: java.sql.SQLException: ORA-00907: 缺失右括号org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)Error querying database. Cause: java.sql.SQLException: ORA-00909: 参数个数无效Error querying database. Cause: java.sql.SQLException: ORA-00907: 缺失右括号

mybatis-Plus之Wrapper的使用

修改

UpdateWrapper wrapper = new UpdateWrapper<>();
wrapper.eq("id",list.get(0).getId());
wrapper.set("evaluation_level","A");
iYwSpaqjgDjService.update(wrapper);

查询

实体类的方式查询

LambdaQueryWrapper lqw = new LambdaQueryWrapper();
lqw.eq(YwQydaglJgbq::getLabelName, ywQydaglJgbq.getLabelName());
lqw.eq(YwQydaglJgbq::getExplain, ywQydaglJgbq.getExplain());
List list = iYwQydaglJgbqService.list(lqw);

使用Map的方式查询

Map map = new HashMap(2);
map.put("jgbq_recid",recid);
map.put("result_label",resultLabel);
List results = iYwQydaglJgbqResultService.listByMap(map);

不写xml文件，直接从controller到mapper层的方式查询

//Controller
@GetMapping("/listAndZtxx")
public TableDataInfo listAndZtxx(YwSpaqjgDj ywSpaqjgDj)
{
    QueryWrapper qw = new QueryWrapper();
    if(StringUtils.isNotBlank(ywSpaqjgDj.getEntname())){
        qw.like("ztdj.entname",ywSpaqjgDj.getEntname());
    }
    if(StringUtils.isNotBlank(ywSpaqjgDj.getUniscid())){
        qw.like("ztdj.uniscid",ywSpaqjgDj.getUniscid());
    }
    startPage();
    List list = iYwSpaqjgDjService.listAndZtxx(qw);
    return getDataTable(list);
}

//Service
 List listAndZtxx(QueryWrapper qw);
@Override
public List listAndZtxx(QueryWrapper qw) {
    return this.getbaseMapper().selectAndZtxx(qw);
}

//Mapper
@Select("select * from table "+
        "${ew.customSqlSegment}")
List selectAndZtxx(@Param(Constants.WRAPPER) QueryWrapper qw);

根据需求写不同的方法，如eq、like、and、or、isNull、isNotNull、ne是不等于、likeRight

if (StringUtils.isNotBlank(ywSpaqjgDj.getevaluationYear())){
    qw.eq("dj.evaluation_year",ywSpaqjgDj.getevaluationYear());
}else {
    Calendar date = Calendar.getInstance();
    String ancheyear = String.valueOf(date.get(Calendar.YEAR));//年报年度
    qw.and(i->{
           i.eq("dj.evaluation_year",ancheyear);
           i.or();
           i.isNull("dj.evaluation_year");
    });
}

框架中常常用到的监管范围

//监管范围查询条件  如果范围太大，所太多，用in就很慢
List inList = DeptUtils.getDateScopeByDeptCode(ywSpaqjgDj.getJggss());
qw.in("qyda.jggss",inList);

SysUser user = SecurityUtils.getLoginUser().getUser();
if(DeptConstants.DEPT_LEVEL_OFFICE.equals(user.getDept().getDeptLevel())){
    if(StringUtils.isNotBlank(user.getGrid())){
        qw.and(i->{
            i.eq("qyda.jggrid",user.getGrid())
                .or()
                .isNull("qyda.jggrid");
        });
    }
}

SysUser user = SecurityUtils.getLoginUser().getUser();//监管范围查询条件
if(DeptConstants.DEPT_LEVEL_OFFICE.equals(user.getDept().getDeptLevel())){
    lqw.eq(YjzhCzsbJbxx::getVehicledept, user.getDeptCode());//所属部门
}else if(DeptConstants.DEPT_LEVEL_DISTRICT.equals(user.getDept().getDeptLevel())){
    
    SysDept dept = DeptUtils.getOrganByDept(user.getDept());
    lqw.eq(YjzhCzsbJbxx::getVehicleorg, dept.getDeptCode());//所属单位
}else if(DeptConstants.DEPT_LEVEL_CITY.equals(user.getDept().getDeptLevel())){
    SysDept dept = DeptUtils.getOrganByDept(user.getDept());//所属单位
    lqw.like(YjzhCzsbJbxx::getVehicleorg, dept.getDeptCode().substring(0,4));
}

在controller层查询的时候，实体类的为日期类型是年月日，数据库中的日期类型信息是年月日时分秒

//1、起始日期手动加上00:00:00截至日期手动加上23:59:59
//2、查询的时候使用to_char和to_date函数把日期做成一致的年月日格式
if(gsgsWfsxZs.getAbntime() != null){
    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    lqw.apply("to_char(abntime,'YYYY-MM-DD')={0}",simpleDateFormat.format(gsgsWfsxZs.getAbntime()));
}

建表SQL，对应有注释

CREATE TABLE "YW_CPZL_JDCC" (
"RECID" NUMBER(20) NOT NULL ,
"REPORT_NO" VARCHAR2(255 BYTE) NULL ,
"SAMPLE_NAME" VARCHAR2(255 BYTE) NULL ,
"PRIPID" VARCHAR2(255 BYTE) NULL ,
"PRODUCT_CATEGORY" VARCHAR2(255 BYTE) NULL ,
"SCDATE" DATE NULL ,
"GGXH" VARCHAR2(255 BYTE) NULL ,
"TRADEMARK" VARCHAR2(255 BYTE) NULL ,
"CCRESULT" VARCHAR2(255 BYTE) NULL ,
"CYDATE" DATE NULL ,
"CJORGANIZATION" VARCHAR2(255 BYTE) NULL ,
"UPLOAD_BY" VARCHAR2(64 BYTE) NULL ,
"UPLOAD_TIME" DATE NULL ,
"CREATE_TIME" DATE NULL ,
"CREATE_BY" VARCHAR2(64 BYTE) NULL ,
"UPDATE_TIME" DATE NULL ,
"UPDATE_BY" VARCHAR2(64 BYTE) NULL 
)
;
COMMENT ON TABLE "SCJGVUE"."YW_CPZL_JDCC" IS '产品质量监督抽查';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."RECID" IS '主键';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."REPORT_NO" IS '报告编号';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."SAMPLE_NAME" IS '样品名称';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."PRIPID" IS '受检单位';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."PRODUCT_CATEGORY" IS '产品类别';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."SCDATE" IS '生产日期';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."GGXH" IS '规格型号';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."TRADEMARK" IS '商标';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."CCRESULT" IS '抽查结果';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."CYDATE" IS '抽样日期';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."CJORGANIZATION" IS '承检机构';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."UPLOAD_BY" IS '导入人';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."UPLOAD_TIME" IS '导入时间';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."CREATE_BY" IS '创建者';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."UPDATE_TIME" IS '更新时间';
COMMENT ON COLUMN "SCJGVUE"."YW_CPZL_JDCC"."UPDATE_BY" IS '更新者';

动态SQL+模糊查询

mapper层

List listJgbqs(@Param(value = "ywQydaglJgbq") YwQydaglJgbq ywQydaglJgbq);

xml层

    select
    jgbq.recid as "recid",
    jgbq.label_name as "label_name",
    jgbq.explain as "explain",
    jgbq.org_code as "org_code",
    jgbq.dep_code as "dep_code",
    result.result_code as "result_code",
    result.result_label as "result_label",
    result.sort_num as "sort_num"
    from yw_qydagl_jgbq jgbq
    right join yw_qydagl_jgbq_result result
    on jgbq.recid = result.jgbq_recid

 
  and jgbq.label_name like concat(concat('%',#{ywQydaglJgbq.labelName}),'%')
 
 
   and jgbq.explain like concat(concat('%',#{ywQydaglJgbq.explain}),'%')
 
 
  and result.result_code like concat(concat('%',#{ywQydaglJgbq.resultCode}),'%')
 
 
and result.result_label like concat(concat('%',#{ywQydaglJgbq.resultLabel}),'%')
 


   
只是后模糊
 
   
 

	and dict_value like concat(#{dictValue},'%')

   
 
nvl和as的使用 
 
 
 
nvl：如果WLJYPT.COUNTWLJYPT为空，就将他赋值为0
  
as：别名，并且返回前端时，名字为小写的
 
   
 
nvl(WLJYPT.COUNTWLJYPT,0) as "countwljypt"
   
 
UNIOn 和 UNIOn ALL 
 
 
UNIOn 操作符用于合并两个或多个 SELECT 语句的结果集。UNIOn 结果集中的列名总是等于 UNIOn 中第一个 SELECT 语句中的列名UNIOn 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。简单地说就是UNIOn去重，UNIOn ALL是直接拼接 
 
 
 
 
例子
 
   
 
这个是Employees_China表
  
     
E_ID
E_NAME
01
Zhang, Hua
02
Wang, Wei
03
Carter, Thomas
04
Yang, Ming
 
这个是Employees_USA表
  
     
E_ID
E_Name
01
Adams, John
02
Bush, George
03
Carter, Thomas
04
Gates, Bill
 
使用UNIOn查询
 
     
 
SELECT E_Name FROM Employees_China
UNIOn
SELECt E_Name FROM Employees_USA
   
使用UNIOn ALL查询
 
     
 
SELECt E_Name FROM Employees_China
UNIOn ALL
SELECt E_Name FROM Employees_USA
   
     
使用UNIOn查询结果
使用UNIOn ALL查询结果
 
     
E_Name
E_Name
Zhang, Hua
Zhang, Hua
Wang, Wei
Wang, Wei
Carter, Thomas
Carter, Thomas
Yang, Ming
Yang, Ming
Adams, John
Adams, John
Bush, George
Bush, George
Gates, Bill
Carter, Thomas
Gates, Bill
 
上面的例子可以看出UNIOn去重了，UNIOn ALL没去重
   
 
在XML中使用动态sql查询一个时间段内的 

     and to_char(estdate,'yyyy-MM-dd') 
     between #{qysltjbbParam.startTime} and #{qysltjbbParam.endTime}

 
在Controller中使用apply、between、to_char等实现一个时间段内的查询 
实体类和数据库对应的字段都是date类型
 
if (caseCfbaseinfo.getPunishDateStartTime() != null && caseCfbaseinfo.getPunishDateEntTime() != null){
	qw.between("irre.punish_date",
				caseCfbaseinfo.getPunishDateStartTime(),
				caseCfbaseinfo.getPunishDateEntTime());
}
 
if (caseCfbaseinfo.getPublicitydateZjStartTime() != null && caseCfbaseinfo.getPublicitydateZjEntTime() != null){
    qw.apply("to_char(base.publicitydate_zj,'yyyy-MM-dd') >= 				'"+caseCfbaseinfo.getPublicitydateZjStartTime()+"'");
    qw.apply("to_char(base.publicitydate_zj,'yyyy-MM-dd') <= '"+caseCfbaseinfo.getPublicitydateZjEntTime()+"'");
}
 
if (caseCfbaseinfo.getPunishDateStartTime() != null && caseCfbaseinfo.getPunishDateEntTime() != null){
    qw.gt("irre.punish_date",caseCfbaseinfo.getPunishDateStartTime());
    qw.lt("irre.punish_date",caseCfbaseinfo.getPunishDateEntTime());
}
 
在XML中使用动态sql遍历数组 

   and industryphy in
   
                #{industryphy}
   

 
在XML中使用动态sql遍历字符串 

    
    	and irr.illegacttype like concat(concat('%',#{illone}),'%')
    

 
在XML中使用动态sql判断前端选择的是月还是年 

    and to_char(estdate,'yyyy') = #{ztxxParam.dateTime}


    and to_char(estdate,'yyyy-MM') = #{ztxxParam.dateTime}

 
当一个列有多种结果，分别求和 
sum(case when problem_type=1 then 1 else 0 end) as "qfzcsb",
sum(case when problem_type=2 then 1 else 0 end) as "bfhcpsm"
 
使用mybatis时，xml层不能直接写大小于号 
       <       <=        >        >=       &         '         "
 	<    <=     >     >=    &     '    "
 
分组查询中，select显示的列只能是分组依据的列或者是聚合函数列 
意思就是，加一个要查询的字段，就要在group by中添加对应的字段
 
select jl.entname,zl.pxtitle,zl.fbdate,"COUNT"(zl.pxtitle),"COUNT"(jl.entname) from YW_ZQHD_ZXPX_PXJL jl 
left JOIN (select recid,pxtitle,fbdate from yw_zqhd_zxpx_pxzl) zl 
ON ZL.RECID = jl.pxid
GROUP BY zl.pxtitle,jl.entname,zl.pxtitle,zl.fbdate
 
 
Group By，Having，Where，Order by的使用 
动态表的查询 
 
 
 
Mapper
 
   
 
@Mapper
public interface YwSjtbSjgxMapper extends baseMapper {
                                      
    Date selectZjTime(@Param(value = "tbname") String tbname, @Param(value = "timesign") String timesign);
}
   
xml中的sql
 
   
 

     select max(t.${timesign}) from ${tbname} t

经纬度范围内的sql

select * from ZTDJ_JBXX_ZS where sqrt(  
    (  
     ((120.565787-lon)*ACOS(-1)*12656*cos(((120.565787+lon)/2)*ACOS(-1)/180)/180)  
     *  
     ((120.565787-lon)*ACOS(-1)*12656*cos (((120.565787+lon)/2)*ACOS(-1)/180)/180)  
    )  
    +  
    (  
     ((36.349884-lat)*ACOS(-1)*12656/180)  
     *  
     ((36.349884-lat)*ACOS(-1)*12656/180)  
    )  
)<5

IN 和 EXISTS的使用和区别

说明场景

ztdj_jbxx_zs：几十万条数据app_employees：只有几条数据两个表有一个共同的字段：pripid

查询主表数据比从表数据多

select * from ztdj_jbxx_zs where 
exists(select 1 from app_employees where ztdj_jbxx_zs.pripid=app_employees.pripid)

select * from ztdj_jbxx_zs where 
ztdj_jbxx_zs.pripid in (select pripid from app_employees)

结论:主表比从表数据多,使用in的效率比exists高

查询从表数据比主表数据多

select * from app_employees where 
exists(select 1 from ztdj_jbxx_zs where ztdj_jbxx_zs.pripid=app_employees.pripid)

select * from app_employees where 
app_employees.pripid in (select pripid from ztdj_jbxx_zs)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GBBNbjAa-1646038657717)(https://raw.githubusercontent.com/huanteng-my/workimg/main/20210723115318.png)] 结论:从表比主表数据多,使用exists的效率比in高

order by的使用

select * from yjzh_zfry_jbxx order by personorg asc

删除表数据

TRUNCATE TABLE tableName

delete * from tableName where a = ‘’

查询字段长度小于7的

select *from sys_dept where length(dept_code) <7

临沂项目手机端echart要用的类型数据sql

--根据district进行分组，并将plan_money求和
select sum(plan_money),district,(select dept_name from sys_dept where dept_code = district) as dept_name from prj_info_manage group by district

--根据大类进行分别统计数量
select count(prjtype_b) as count,prjtype_b,
(select dict_label from sys_dict_data where dict_type = 'PRJTYPE_B' and dict_value = prjtype_b) as hjdl from prj_info_manage group by prjtype_b

泰安项目综合查询

//controller
@GetMapping("/getComQueryList")
public TableDataInfo getComQueryList(CaseCfbaseinfo caseCfbaseinfo)
{
    startPage();
    List list = iCaseCfbaseinfoService.getComQueryList(caseCfbaseinfo);
    return getDataTable(list);
}

//domain

@Data
@ToString
@EqualsAndHashCode
@NoArgsConstructor
@Accessors(chain = true)
@TableName("case_cf_baseinfo")
public class CaseCfbaseinfo implements Serializable {

private static final long serialVersionUID=1L;

    
    @TableField(exist = false)
    private String objType;

    
    @TableField(exist = false)
    private String lerep;

    
    @TableField(exist = false)
    private String state;

    
    @TableField(exist = false)
    @DateTimeFormat(pattern = "yyyy-MM-dd")//json转实体类
    @JsonFormat(pattern = "yyyy-MM-dd")
    private Date enforceClosedate;

    @TableField(exist = false)
    private CaseCfPartyinfo caseCfPartyinfo;

    @TableField(exist = false)
    private CaseCfIrregpunishinfo caseCfIrregpunishinfo;
}

//service

public interface ICaseCfbaseinfoService extends IService {

    List getComQueryList(CaseCfbaseinfo caseCfbaseinfo);

}

//serviceImpl
@Service
public class CaseCfbaseinfoServiceImpl extends ServiceImpl implements ICaseCfbaseinfoService {

    @Autowired
    private CaseCfbaseinfoMapper caseCfbaseinfoMapper;

    @Override
    public List getComQueryList(CaseCfbaseinfo caseCfbaseinfo) {
        return caseCfbaseinfoMapper.getComQueryList(caseCfbaseinfo);
    }
}

//mapper
public interface CaseCfbaseinfoMapper extends baseMapper {
    
    List getComQueryList(@Param(value = "caseCfbaseinfo") CaseCfbaseinfo caseCfbaseinfo);
}

对应的xml

    
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
            
            
            
            
            
            
            
            
            
            
            
            
        
        
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
        
    

    
        select base.caseid,base.case_name,base.source_kind,base.case_addr,base.case_no,base.case_regiedate,
        base.enforcer_name,base.enforcer_id,base.second_enforcer_name,base.second_enforcer_id,
        base.case_content,base.caseval,base.directorycode,base.directoryname,base.region_code,
        base.punish_valid_date,base.publicitydate_zj,base.publicitydate_ta,
        party.casepartyid,party.obj_type,party.partytype,party.obj_addr,party.uniscid,party.lerep,
        party.cerno,party.entrust_name,party.entrust_cerno,party.entrust_tel,party.unitname,
        irr.illegactid,irr.pen_rrotype,irr.state,irr.dc_finishtime,irr.legal_review_date,
        irr.legal_review_options,irr.illegacttype,irr.penbasis,irr.pentype,irr.pencontent,
        irr.penam,irr.forfam,irr.forfamgoods,irr.penam_forfam,
        irr.revoke_lice_name,irr.revoke_lice_no,irr.punish_ref_no,irr.punish_date,irr.send_date,
        irr.file_name,irr.finish_type,irr.enforce_closedate
        from case_cf_baseinfo base
        left join case_cf_partyinfo party on party.caseid = base.caseid
        left join case_cf_irregpunishinfo irr on irr.caseid = base.caseid
        
            
                and base.case_name like concat(concat('%',#{caseCfbaseinfo.caseName}),'%')
            
            
                and base.source_kind = #{caseCfbaseinfo.sourceKind}
            
            
                and base.case_no like concat(concat('%',#{caseCfbaseinfo.caseNo}),'%')
            
            
                and party.obj_type = #{caseCfbaseinfo.objType}
            
            
                and party.lerep like concat(concat('%',#{caseCfbaseinfo.lerep}),'%')
            
            
                and irr.state = #{caseCfbaseinfo.state}
            
            
                and irr.enforce_closedate = #{caseCfbaseinfo.enforceClosedate}
            
        
    

威海项目企业工作室申办事项查询

--专利转让发布
select
	recid as recid,
	'0' as sb_type,
	patent_name as name,
	--submit_status as tjzt,
	case when submit_status='N' then '保存' else '提交' end as tjzt,
	to_char(update_time,'yyyy-MM-dd') as tjsj,
	--status as slzt,
	case 
	when status='0' then '暂存'
	when status='1' then '待初审'
	when status='2' then '已初审'
	when status='3' then '已退回'
	when status='4' then '已发布'
	else '已删除' end 
	as slzt,
 	to_char(o.slsj,'yyyy-MM-dd') as slsj
from
	yw_zscq_market m 
	left join 
	(select max(operate_time) as slsj,parent_recid as parent_recid 
	from yw_zscq_market_operation b 
	group by parent_recid) as o
	on m.recid= o.parent_recid
 --where yw_fwjg_recid = '829092huklfhkllhds9e' 
			
union

 --质押融资申报 --知识产权-质押融资服务-申报  
select 
	b.recid as recid,
	'1' as sb_type,
	concat(sxmc,concat('[',concat(sxfw,']'))) as name,
	--iszc as tjzt,
	case when iszc='N' then '保存' else '提交' end as tjzt,
	to_char(b.update_time,'yyyy-MM-dd') as tjsj,
	--b.status as slzt,
	case 
	when b.status='0' then '待初审'
	when b.status='1' then '已初审'
	when b.status='2' then '已终审'
	when b.status='3' then '已退回'
	else '撤回' end 
	as slzt,
	to_char(o.slsj,'yyyy-MM-dd') as slsj
from  
yw_zscq_sxgl a 
inner join 
yw_zscq_zyrzfwsb b 
on b.sxgl_recid = a.recid
left join 
(select max(operate_time) as slsj,parent_recid as parent_recid 
from yw_zscq_zyrzfwsb_operation b 
group by parent_recid) as o
on b.recid= o.parent_recid
--where b.yw_fwjg_recid='829092huklfhkllhds9e'
    
union 

--招聘信息发布  --小微企业-企业招聘信息
select 
	recid as recid,
	'2' as sb_type,
	post_name as name,
	--is_zc as tjzt,
	case when is_zc='N' then '保存' else '提交' end as tjzt,
	to_char(update_time,'yyyy-MM-dd') as tjsj,
	--sh_type as slzt,
	case 
	when sh_type='1' then '待初审'
	when sh_type='2' then '已初审'
	when sh_type='3' then '已发布'
	else '已退回' end 
	as slzt,
 	to_char(o.slsj,'yyyy-MM-dd') as slsj
from yw_xwqy_rcdj_qyzpxx m 
left join 
(select max(operate_time) as slsj,parent_recid as parent_recid 
from yw_xwqy_rcdj_qyzpxx_operation b 
group by parent_recid) as o
on m.recid= o.parent_recid
--where yw_fwjg_recid ='829092huklfhkllhds9e'
	
union

---小微企业融资服务需求申请
select 
	recid as recid,
	'3' as sb_type,
	'' as name,
	--is_submit as tjzt,
	case when is_submit='N' then '保存' else '提交' end as tjzt,
	to_char(updatetime,'yyyy-MM-dd') as tjsj,
	--status as slzt,
	case 
	when status='0' then '待初审'
	when status='1' then '已初审'
	when status='2' then '已终审'
	when status='3' then '已退回'
	else '撤回' end 
	as slzt,
 	to_char(o.slsj,'yyyy-MM-dd') as slsj
from yw_xwqy_rzsqsx m  
left join 
(select max(operate_time) as slsj,parent_recid as parent_recid 
from yw_xwqy_rzsqsx_operation b 
group by parent_recid) as o
on m.recid= o.parent_recid
-- where a.yw_fwjg_recid='829092huklfhkllhds9e'	

union

--产品质量检测在线申请
select 
	ya.recid as recid,
	'4' as sb_type,
	ya.sample_name as name,
	case when is_temstorage='N' then '保存' else '提交' end as tjzt,
	to_char(ya.update_time,'yyyy-MM-dd') as tjsj,
	case 
	when ya.sh_type='1' then '待初审'
	when ya.sh_type='2' then '已初审'
	when ya.sh_type='3' then '已发布'
	else '已退回' end 
	as slzt,
 	to_char(yo.accept_date,'yyyy-MM-dd') as slsj
from yw_jyjcfw_cpzljc_ywzxsq ya 
left join  
(select max(accept_date) as accept_date,parent_recid as parent_recid 
from yw_jyjcfw_cpzljc_ywzxsq_operation b 
group by parent_recid) as yo
on ya.recid = yo.parent_recid
-- where a.yw_fwjg_recid='829092huklfhkllhds9e'

数据库报错 java.SQLException: ORA-00936:缺失表达式

新生成代码，放在对应的位置，会发现此错误

首先，将sql语句在navicat中执行一下，看是什么原因

ORA-00936: missing expression，发现是表中的字段和oracle数据库的关键字冲突了，会报错。

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)

即在mybatis中dao接口与mapper配置文件在做映射绑定的时候出现问题，简单说，就是接口与xml要么是找不到，要么是找到了却匹配不到。

检查xml文件所在的package名称是否和Mapper interface所在的包名一致

Mapper的方法在xml中没有，执行Mapper的方法，会报错xml中select元素没有正确配置ResultMap看一下xml和Mapper的路径是否一致

Error querying database. Cause: java.sql.SQLException: ORA-00909: 参数个数无效

报错

写的sql

oracle的CONCAT关键字只支持拼接两个字符串，改为如下，用多一个CONCAT即可

Error querying database. Cause: java.sql.SQLException: ORA-00907: 缺失右括号

只要报错缺失右括号，基本上都是因为where前面没有空格

-DB96m2hh-1646038657718)]

ORA-00936: missing expression，发现是表中的字段和oracle数据库的关键字冲突了，会报错。

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found)

即在mybatis中dao接口与mapper配置文件在做映射绑定的时候出现问题，简单说，就是接口与xml要么是找不到，要么是找到了却匹配不到。

检查xml文件所在的package名称是否和Mapper interface所在的包名一致

[外链图片转存中…(img-juFeMXaD-1646038657718)]

Mapper的方法在xml中没有，执行Mapper的方法，会报错xml中select元素没有正确配置ResultMap看一下xml和Mapper的路径是否一致

Error querying database. Cause: java.sql.SQLException: ORA-00909: 参数个数无效

报错

[外链图片转存中…(img-rcKWnee0-1646038657719)]

写的sql

[外链图片转存中…(img-WbufEBvh-1646038657719)]

oracle的CONCAT关键字只支持拼接两个字符串，改为如下，用多一个CONCAT即可

[外链图片转存中…(img-w47YLlEt-1646038657720)]

Error querying database. Cause: java.sql.SQLException: ORA-00907: 缺失右括号

只要报错缺失右括号，基本上都是因为where前面没有空格

[外链图片转存中…(img-dsFm9L3A-1646038657720)]