3月 092020
 

表现

仅配置单个数据源时,mybatis plus的save/saveBatch接口调用正常
配置多个数据源、动态切换时,mybatis plus的save接口调用正常,saveBatch调用失败,报错如下

org.apache.ibatis.exceptions.PersistenceException: 
### Error flushing statements.  Cause: org.apache.ibatis.executor.BatchExecutorException: com.xxx.survey.mapper.SurveyAnswerMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: 对象名 't_survey_answer' 无效。
### Cause: org.apache.ibatis.executor.BatchExecutorException: com.xxx.survey.mapper.SurveyAnswerMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: 对象名 't_survey_answer' 无效。
 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:255)
 at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.saveBatch(ServiceImpl.java:128)
 at com.baomidou.mybatisplus.extension.service.IService.saveBatch(IService.java:58)
 at com.baomidou.mybatisplus.extension.service.IService$$FastClassBySpringCGLIB$$f8525d18.invoke(<generated>)
 at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
 at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
 at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
 at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
 at com.xxx.survey.service.impl.SurveyAnswerServiceImpl$$EnhancerBySpringCGLIB$$dbeefaf3.saveBatch(<generated>)
 at com.xxx.survey.ServiceTest.testGetAttendanceRecord(ServiceTest.java:91)
 at sun.refl

Continue reading »

3月 032020
 

本文主要关注如何使用mybatis/mybatis plus连接SQL Server数据库,因此将省略其他项目配置、代码。

框架选择

应用框架:spring boot
ORM框架:mybatis plus(对于连接数据库而言,mybatis和mybatis plus其实都一样)
数据库连接池:druid

pom依赖

此处仅给出我的配置,mybatis/druid请依据自己项目的需要进行选择。 Continue reading »

2月 262020
 

各种场景

判断字段是否包含数字

select name from text where name regex '[0-9]'

使用like模糊查询包含某个数字

select * from text where name like '%1%'

可能会筛出各种不适我们想要的,比如包含“10”的字段也会筛选出。 Continue reading »

2月 182020
 

mysql的正则匹配用regexp,而替换字符串用REPLACE(str,from_str,to_str)

例如
UPDATE myTable SET HTML=REPLACE(HTML,'<br>','') WHERE HTML REGEXP '(<br */*>\s*){2,}'

更多例子如下:

为了找出以“d”开头的名字,使用“^”匹配名字的开始:

SELECT * FROM master_data.md_employee WHERE name REGEXP ‘^d’; Continue reading »

2月 182020
 

题外话,前端也可以调用已有的接口获取ip,例如调用搜狐接口 https://pv.sohu.com/cityjson?ie=utf-8

在spring框架中,获取IP接口,则需要获取 HttpServletRequest 对象,该对象中包含了客户端请求的相关信息。

java代码如下:

/** 
 * @Description: 获取客户端IP地址   
 */  
private String getIpAddr(HttpServletRequest request) {   
    String ip = request.getHeader("x-forwarded-for");   
    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {   
        ip = request.getHeader("Proxy-Client-IP");   
    }   
    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {   
        ip = request.getHeader("WL-Proxy-Client-IP");   
    }   
    if(ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {   
        ip = request.getRemoteAddr();   
        if(ip.equals("127.0.0.1")){     
            //根据网卡取本机配置的IP     
            InetAddress inet=null;     
            try {     
                inet = InetAddress.getLocalHost();     
            } catch (Exception e) {     
                e.printStackTrace();     
            }     
            ip= inet.getHostAddress();     
        }  
    }   
    // 多个代理的情况,第一个IP为客户端真实IP,多个IP按照','分割  
    if(ip != null && ip.length() > 15){    
        if(ip.indexOf(",")>0){     
            ip = ip.substring(0,ip.indexOf(","));     
        }     
    }     
    return ip;   
}

Continue reading »

2月 012020
 

找到两种方法:
1、采用mybatis注解的方式
参见:MyBatis Plus 自定义查询语句
DAO层:

@Select("select b.bomName, " +
		"b.bomProductType, b.bomMaterial, " +
		"o.customerID AS bomID, " +
		"o.ordersDataNo AS qrCode, " +
		"s.deliveryDate AS barCode, " +
		"s.mainType AS workshop " +
		"FROM mes_order_bom b " +
		"LEFT JOIN mes_order_ordersdata o ON b.ordersID = o.id " +
		"LEFT JOIN mes_order_soncontract s ON o.sonContractID = s.id " +
		"WHERE o.ordersDataNo IN (#{orderNoList})")
List<MesOrderBom> getBomAndOrderCodeNumber(@Param("orderNoList")List<String> orderNoList);

Continue reading »

2月 012020
 

mybatis-plus select查询语句默认是查全部字段,有两种方法可以指定要查询的字段

假定表结构如下:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `manager_id` bigint(20) DEFAULT NULL COMMENT '直属上级id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

假设目前仅需要查询name,age两个字段。 Continue reading »

1月 162020
 

Java中日期格式化yyyyMMdd和YYYYMMdd的区别

示例代码:

 public static void main(String[] args) {
        //YYYY 是表示:当天所在的周属于的年份,一周从周日开始,周六结束,只要本周跨年,那么这周就算入下一年。
        //2019-12-29至2020-1-4跨年周
        Calendar calendar = Calendar.getInstance();
        //2019-12-28
        calendar.set(2019, Calendar.DECEMBER, 28);  
        Date strDate1 = calendar.getTime(); 
        //2019-12-29
        calendar.set(2019, Calendar.DECEMBER, 29);  
        Date strDate2 = calendar.getTime(); 
        // 2019-12-31  
        calendar.set(2019, Calendar.DECEMBER, 31);  
        Date strDate3 = calendar.getTime();  
        // 2020-01-01  
        calendar.set(2020, Calendar.JANUARY, 1);  
        Date strDate4 = calendar.getTime();  
        
        DateFormat df1 = new SimpleDateFormat("yyyyMMdd");
        DateFormat df2 = new SimpleDateFormat("YYYYMMdd");
        //yyyyMMdd
        System.out.println("yyyyMMdd");
        System.out.println("2019-12-28: " + df1.format(strDate1)); 
        System.out.println("2019-12-29: " + df1.format(strDate2)); 
        System.out.println("2019-12-31: " + df1.format(strDate3));  
        System.out.println("2020-01-01: " + df1.format(strDate4));  
        //YYYYMMdd
        System.out.println("YYYYMMdd");
        System.out.println("2019-12-28: " + df2.format(strDate1));
        System.out.println("2019-12-29: " + df2.format(strDate2));
        System.out.println("2019-12-31: " + df2.format(strDate3));  
        System.out.println("2020-01-01: " + df2.format(strDate4));
    }

输出结果: Continue reading »