后端问题集

一.mysql随机查询多条数据

一个栗子:从一张表中随机查询出满足条件的十条数据;
sql:

 SELECT d1.id,d1.title,d1.content,d1.fid,d1.sid,d1.name
 FROM `dream` AS d1 JOIN (SELECT ROUND( RAND () * 
 ((SELECT MAX(fid) FROM `dream`)-(SELECT MIN(fid) FROM `dream`))+
 (SELECT MIN(fid) FROM `dream`)) AS id) AS d2
  WHERE d1.id >= d2.id and title LIKE CONCAT('%',#{title},'%')
  ORDER BY d1.id LIMIT 18;

二.mybatis 实现模糊查询

一个栗子:根据调价查询满足条件的用户;
sql:

SELECT * FROM user
        WHERE is_del=0
        <!-- 如果查询条件不为空,执行模糊查询 -->
        <if test="keyword != null">
            AND (name LIKE CONCAT('%',#{keyword},'%')
            OR phone LIKE CONCAT('%',#{keyword},'%')
            OR email LIKE CONCAT('%',#{keyword},'%'))
        </if>
        ORDER BY id DESC

三.如何将个人电脑(windows)变成服务器,让别人可以直接访问?

实现步骤:

1.下载花生壳客服端;(:点击官网下载)
2.安装花生壳;
3.注册登录:
4.设置映射关系;
具体步骤可参考:https://www.jianshu.com/p/8ff41779463b

四.spring boot处理跨域请求.

实现步骤

@Configuration
public class CorsConfig extends WebMvcConfigurerAdapter {

    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**")
                .allowedOrigins("*")
                .allowCredentials(true)
                .allowedMethods("GET", "POST", "DELETE", "PUT")
                .maxAge(3600);
    }

}

五.xml中的特殊符号(<,&,<=…)

1.小于号

  &lt;		<		小于号

2.大于号

 >		>		大于号

3.和号

&		&		和

4.单引号

 '		'		单引号

5.双引号

 "		"		双引号

六.mybatis中动态if标签判断是否等于一个字符串

1.正确演示

 <if test='keyword == "DAY"'>
      AND TO_DAYS(pay_time)=TO_DAYS(now())
 </if>

2.错误演示

 <if test="keyword == DAY">
      AND TO_DAYS(pay_time)=TO_DAYS(now())
 </if>
 或者
 <if test="keyword == 'DAY'">
      AND TO_DAYS(pay_time)=TO_DAYS(now())
 </if>

注意:先使用单引号再使用双引号(因为 MyBatis是使用的OGNL表达式，’a’会被解析成字符，java是强类型的，char 和 一个string 会导致不等所以if标签中的sql不会被解析)

七.如何在一条sql语句中的某个时间段统计多个数据

需求:统计订单表支付总数量,总金额,微信支付总数量,微信支付总金额,支付宝支付总数量,支付宝支付总金额,其他支付数量,其他支付金额,根据今年,上月,本月,昨天,今天进行统计

具体实现

SELECT 
		COUNT(id) AS countNum,
    	SUM(vip_price) AS sumPrice,
    	COUNT(CASE WHEN pay_type = 1 THEN 1 END) AS weixinCountNum,
  		SUM(CASE WHEN pay_type = 1 THEN vip_price END) AS weixinSumPrice,
        COUNT(CASE WHEN pay_type = 2 THEN 1 END) AS aliCountNum,
        SUM(CASE WHEN pay_type = 2 THEN vip_price END) AS aliSumPrice,
        COUNT(CASE WHEN pay_type > 2 THEN 1 END) AS otherCountNum ,
        SUM(CASE WHEN pay_type >2 THEN vip_price END) AS orherSumPrice
        FROM vip_pay
        WHERE paystate =1
        <if test='keyword == "YEAR"'>
       		 <!-- 今年 -->
            AND DATE_FORMAT(pay_time,'%Y')=DATE_FORMAT(now(), '%Y' )
        </if>
        <if test='keyword == "LASTMONTH"'>
        	<!-- 上个月 -->
            AND PERIOD_DIFF(DATE_FORMAT(now(),'%Y%m'),DATE_FORMAT(pay_time, '%Y%m' ))=1
        </if>
        <if test='keyword == "MONTH"'>
        	<!-- 本月 -->
            AND DATE_FORMAT(pay_time,'%Y%m')=DATE_FORMAT(now(), '%Y%m' )
        </if>
        <if test='keyword == "YESTERDAY"'>
       		 <!-- 昨天 -->
            AND TO_DAYS(now())-TO_DAYS(pay_time) &lt;2
        </if>
        <if test='keyword == "DAY"'>
        	<!-- 今天 -->
            AND TO_DAYS(pay_time)=TO_DAYS(now())
        </if>

八.Spring Boot处理跨域请求

具体实现

@Configuration
public class CorsConfig extends WebMvcConfigurerAdapter {

    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**")
                .allowedOrigins("*")
                .allowCredentials(true)
                .allowedMethods("GET", "POST", "DELETE", "PUT")
                .maxAge(3600);
    }
}

九.RestTemplate 远程调用服务配置

具体实现

@Configuration
public class RestTemplateConfig {

    @Bean
    public RestTemplate restTemplate(){
        return new RestTemplate();
    }
}

常用方法

1.get请求:restTemplate.getForObject(“请求地址”,返回值类型);
例如:

Map<String,Object> map = new HashMap<>();
Map<String,Object> forObject = restTemplate.getForObject("http://localhost:8081/app/list", map.getClass());

2.post请求:restTemplate.postForObject(“请求url”,请求参数,返回值类型);
例如:

 Map<String,Object> map = new HashMap<>();
 map.put("payId","8a9430ae25514aeda0bd90bd2883477");
 AppResult postFor = restTemplate.postForObject("http://localhost:8081/pay/WXOrderquery.do", map, AppResult.class);