【javaweb】sql报错Unknown column ‘ ‘ in ‘where clause‘

〇、前情提要

写了条语句结果遇到了Unknown column ’ ’ in 'where clause’报错。
参考:

  1. 数据库 —— 查询避免 Unknown column ‘xxx’ in ‘where clause’ 错误
    https://blog.csdn.net/qq_19865749/article/details/76883891

一、问题分析

原sql语句

public List<media> findAllMedia(User user) {
		int sysType = user.getSysType();
		String ucomp = user.getSysComp();
		
		//1.获取连接
        String sql = null;
        if( sysType == 11 ){
            sql = "select * from media";
        }
        else {
            sql = "select * from media where ava = 1 AND comp = "+ ucomp;
        }

console报错

在这里插入图片描述
翻译:列名不存在的结论


二、解决

参考

参考:
数据库 —— 查询避免 Unknown column ‘xxx’ in ‘where clause’ 错误
https://blog.csdn.net/qq_19865749/article/details/76883891

单从字面理解,我们很容易得出列名不存在的结论,但是,很多时候起始并不是由于列名出错造成的。而是由于拼凑sql语句时对字符类型数据没有用引号引起来造成的。

例如:一个hql语句:final String hql = "from ActiveCert ac where ac.issuerName="+issueName";
设置issueName值为ysy,则错误如下:
Unknown column ‘ysy′ in ‘where clause’
sql中如果issueName是整型的倒不会出现什么错误,而如果sql中字符串类型必须要包含在引号内。
所以修改hql为:final String hql = "from ActiveCert ac where ac.issuerName='"+issueName+"'";
则错误消失。

分析

我想写的是comp该列要与ucomp相符,但在拼凑sql语句时,对字符类型数据要用引号引起来。

纠正

sql = "select * from media where ava = 1 AND comp = '"+ ucomp +"'";

三、测试

成功查询到符合条件的记录。


已标记关键词 清除标记
_现在遇到个状况,如下代码 select rela.member_paper_id,rela.member_id,paper_type,rela.paper_id,rela.create_time,rela.update_time,rela.start_time,rela.end_time,me.member_name,pa.paper_name,concat (rela.paper_score ,'分' ) as paper_score,concat (pa.total_score ,'分' ) as total_score, cast(( select count(1) from training_member_paper_question_relation qrela left join training_member_paper_relation mpr on qrela.member_paper_id = mpr.member_paper_id where qrela.member_paper_id = rela.member_paper_id and is_correct = '0' ) as char(5))as error_number, cast(( select SUM(d.question_score) from (select qrela.question_score from training_member_paper_question_relation qrela left join training_paper_question que on qrela.question_id = que.question_id where qrela.member_paper_id = '2017110900011225' and (que.question_type = 'single' or que.question_type = 'order' or que.question_type = 'multiple') )as d ) as char(5)) as subjective_score, cast(( select COUNT(1) from training_member_paper_question_relation qrela left join training_paper_question que on qrela.question_id = que.question_id where qrela.member_paper_id = rela.member_paper_id and (que.question_type = 'single' or que.question_type = 'order' or que.question_type = 'multiple') ) as char(5))as subjective_item from training_member_paper_relation rela left join training_member me on rela.member_id = me.member_id left join training_paper pa on rela.paper_id = pa.paper_id WHERE rela.member_paper_id = '2017110900011225' 在这种情况下是可以查出member_paper_id = '2017110900011225'的数据,这个没问题,但我的WHERE条件后的rela.member_paper_id 在mapper是动态的(= #{memberPaperId,jdbcType=CHAR}) ,那么在上面 qrela.member_paper_id 就不可能为= '2017110900011225'不变的,但是我把它改成像下面一个字段的方式: qrela.member_paper_id = rela.member_paper_id 直接报错[Err] 1054 - Unknown column 'rela.member_paper_id' in 'where clause', subjective_score字段的查询是先查出条件下的结果,再把结果求和,再把这个作为一个字段去查询,下面一个 subjective_item字段查询是直接查出数量,再作为一个字段查询,这个是没有问题的,请问大神这个有没有什么办法啊_**__
©️2020 CSDN 皮肤主题: 1024 设计师:上身试试 返回首页