Scene reappearance

There is a table. Now we want to query whether a character field in the table contains some keywords.
Example: there is a field tag in the table; now it is to determine whether the tag contains keywords, the number of keywords is uncertain, so we need to use dynamic sql statements to splice, and screen out tags that contain at least one keyword.

Needs understanding

Set the value of the tag field to'abcdef admin root'
Keyword set: {abc','admin','mysql','fuzz'}


Results:'abcdef admin root'satisfied the screening conditions.
Analysis: The value of the tag field contains `abc'and `admin' in a given keyword set.

code implementation

The related table structure is as follows:

create table `conferenceinfo` (
  `id` bigint(20) not null auto_increment,
  `cnname` varchar(128) default null,
  `enname` varchar(128) default null,
  `tag` varchar(250) default null,
  `location` varchar(64) default null,
  `sponsor` varchar(128) default null,
  `startdate` date default null,
  `enddate` date default null,
  `deadline` date default null,
  `acceptance` date default null,
  `website` varchar(128) default null,
  `isclassify` int(11) default '0',
  primary key (`id`)
) engine=innodb auto_increment=1238 default charset=utf8

The project uses mybatis automatic scanning configuration to load the dao interface. The sql statements in the configuration file are as follows:

<!-- Usage dynamics sql Statement splicing, locate()Function lookup tag Does it contain keywords?-->
    <select id="queryLatestConcernedConferenceInfo" resultType="ConferenceInfo">
        select id, cnName, enName, location, sponsor, startdate, enddate,
            website, deadline, acceptance, tag
        from ConferenceInfo
        <where>
            <![CDATA[ startdate >= #{time} and startdate <= #{endtime}]]>
            <if test="#{tags}.size > 0">
                and (
                <!-- 
                    //Note: The map is used to pass sql parameters, and the service layer uses map.put("tags",list) to put the list into the map. 
                         foreach Label collection The value of map Put in list Of key Same value
                -->
                <foreach collection="tags" item="t" separator=" ">
                    <![CDATA[ locate(#{t},tag) > 0 or ]]>
                </foreach>
                 0 )
            </if>
        </where>

        order by startdate
        <if test=" #{offset}!=null and #{number} != null">
            limit #{offset}, #{number}
        </if>
    </select>

Running sql statements directly from the command line can find the data, but no data can be found in the project. Looking at the mysql execution log, we find that the original sql statements are as follows:

select id, cnName, enName, location, sponsor, startdate, enddate,
       website, deadline, acceptance, tag

from ConferenceInfo
WHERE startdate >= '2017-10-28 09:50:04.88' 
and startdate <= '2017-10-30 09:50:04.88'
and ( locate(' Computing & Technology',tag) > 0 
            or locate('?????',tag) > 0 
            or 0 
        ) 
order by startdate
limit 0, 8

The first parameter, Computing & Technology, is the query parameter, and the second parameter is a Chinese string, but now it has become????????? Probably because of the Chinese scramble code, but the project added a character encoding filter, the web layer should not appear Chinese scramble code, then the scramble code may appear in the database. Layer, searched and found that the problem actually appeared in the database layer. Reference link

Look at the database connection configuration as follows:

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/pythondb
jdbc.user = root
jdbc.password = ********

The problem arises on jdbc.url. After specifying the character set and encoding for the url, the operation of mybatis database will not cause the problem of Chinese random code. It is necessary to add? UseUnicode = true & characterEncoding = UTF-8 after the url.

The modified properties configuration file is as follows:

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/pythondb?useUnicode=true&characterEncoding=utf-8
jdbc.user = root
jdbc.password = ********

After executing the query statement, check the mysql execution log as follows:

select id, cnName, enName, location, sponsor, startdate, enddate,
            website, deadline, acceptance, tag
from ConferenceInfo
WHERE startdate >= '2017-10-28 10:02:55.099' 
and startdate <= '2017-10-30 10:02:55.102'
and (   locate('Computer Application Technology',tag) > 0 or 
            locate('Computer software',tag) > 0 or 
            locate('Electronic communication',tag) > 0 or 
        0 ) 
order by startdate
limit 0, 8