文章目录
- java代码示例
- 方法请求例子
- switch case 语句
- 判断包含多个实体的List中，是实体之间的userid重复
- java8如何用Stream查List对象某属性是否有重复
- JAVA判断字符串是否为null、是否为空
- mybatis条件构造器返回List
- 通过QueryWrapper修改LIst传参
- Map返回前端多元素示例
- jeecgboot 自定义导出excel
- java中如何将2021-04-05T13:08:22+08:00转成date呢
- java时间处理--判断当前时间是否在一个时间区间内
- java获取特定日期Date
- java计算两个日期【字符串类型】之间的时间距离
- java double除法运算保留2位小数
- java将数值（double）类型转化成人民币大写
- 比较两个相等的double值
- Java BigDecimal类型的 加减乘除运算
- Java中BigDecimal比较大小的方法
- Java 根据开始编号和结束编号 计算出之间的编号,支持001-100,不同长度
- 根据每天数据量生成当天档案号
- 获取当前登录用户信息
- Springboot中Bigdecimal以json格式返回前端丢失小数点
- Spring Boot修改内置Tomcat端口号
- mybatis语句示例
- 注解标识实体中该字段不为数据表字段
- select语句
- update语句
- delete语句
- 模糊查询
- if..else的用法
- foreach语句
- CASE WHEN 查询字段返回默认值
- 通过多部门ID查询部门下用户信息
- sql语句片段组合
- MYSQL语句示例
- 查询在一张表不在另外一张表的记录
- 积木报表示例
- 按日期查询2个表的字段
- 示例二（消费汇总时段）
java代码示例
方法请求例子
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21/** * 编辑 * * @param hzOrderPage * @return */ @AutoLog(value = "患者检验项目-编辑") @ApiOperation(value="患者检验项目-编辑", notes="患者检验项目-编辑") //@RequiresPermissions("org.jeecg.modules.demo:hz_order:edit") @RequestMapping(value = "/edit", method = {RequestMethod.PUT,RequestMethod.POST}) public Result<String> edit(@RequestBody HzOrderPage hzOrderPage) { HzOrder hzOrder = new HzOrder(); BeanUtils.copyProperties(hzOrderPage, hzOrder); HzOrder hzOrderEntity = hzOrderService.getById(hzOrder.getId()); if(hzOrderEntity==null) { return Result.error("未找到对应数据"); } hzOrderService.updateMain(hzOrder, hzOrderPage.getOrderxmList()); return Result.OK("编辑成功!"); }
switch case 语句
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22String a = "c"; switch (a) { case "a": System.out.println("匹配成功a"); //break; case "b": System.out.println("匹配成功b"); //break; case "c": System.out.println("匹配成功c"); break; default: System.out.println("hhhh"); break; } ## 控制器获取json参数赋值给实体对象 ```java SysUser user = jsonObject.getObject("user",SysUser.class);
判断包含多个实体的List中，是实体之间的userid重复
复制代码
1
2
3
4
5
6JwAjryxxList jwAjryxxList = jsonObject.getObject("ajryList",JwAjryxxList.class); List<String> collect1 = jwAjryxxList.getAjryList().stream().map(JwAjryxx::getUserid).distinct().collect(Collectors.toList()); if(collect1.size()!=jwAjryxxList.getAjryList().size()){ return Result.error("有重复人员数据，请核对后再提交！"); }
java8如何用Stream查List对象某属性是否有重复
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39@Test public void t2() { List<User> list = new ArrayList<>(); User user1 = new User("zhangsan", "beijing", 30); User user2 = new User("zhangsan", "beijing", 40); User user3 = new User("lisi", "shanghai", 35); User user4 = new User("lisi", "shanghai", 28); User user5 = new User("lisim", "shanghai", 32); list.add(user1); list.add(user2);list.add(user3); list.add(user4);list.add(user5); System.out.println("原始数据："+list); //判断姓名是否有重复,练习使用java8的stream方法 //方法1. distinct, 直接比较大小，只知道是否有重复 List<String> collect1 = list.stream().map(User::getName).distinct().collect(Collectors.toList()); System.out.println(collect1.size()!=list.size()?"方法1-姓名有重复":"无重复"); //方法2.用户姓名计数 Map<Object, Long> collect2 = list.stream().collect( Collectors.groupingBy( User::getName , Collectors.counting() ) ); System.out.println("姓名重复计数情况："+collect2); //筛出有重复的姓名 List<Object> collect3 = collect2.keySet().stream(). filter(key -> collect2.get(key) > 1).collect(Collectors.toList()); //可以知道有哪些姓名有重复 System.out.println("方法2-重复的姓名 ： "+collect3); //方法3，对重复的姓名保留计数 List<Map<String, Long>> collect4 = collect2.keySet().stream(). filter(key -> collect2.get(key) > 1).map(key -> { Map<String, Long> map = new HashMap<>(); map.put((String) key, collect2.get(key)); return map; }).collect(Collectors.toList()); System.out.println("方法3-重复的姓名及计数："+collect4); } 原始数据：[User(name=zhangsan, address=beijing, age=30), User(name=zhangsan, address=beijing, age=40), User(name=lisi, address=shanghai, age=35), User(name=lisi, address=shanghai, age=28), User(name=lisim, address=shanghai, age=32)] 方法1-姓名有重复 姓名重复计数情况：{lisi=2, zhangsan=2, lisim=1} 方法2-重复的姓名 ： [lisi, zhangsan] 方法3-重复的姓名及计数：[{lisi=2}, {zhangsan=2}]
JAVA判断字符串是否为null、是否为空
复制代码
1
2
3
4
5
6
7
8
9
10org.apache.commons.lang3.StringUtils <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.9</version> </dependency> isBlank()在参数为null、""、" "时，值全部为true
mybatis条件构造器返回List
复制代码
1
2
3
4
5
6
7
8JwZd deviceInfo = jwZdService.getOne(new LambdaQueryWrapper<JwZd>().eq(JwZd::getId,deviceList[i])); QueryWrapper<KfdxExamination> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("pid",pid); queryWrapper.ge("remainder",1); List<KfdxExamination> roomlist = kfdxExaminationService.list(queryWrapper);
通过QueryWrapper修改LIst传参
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32public Result<?> queryPageList(JwZzcwh jwZzcwh, @RequestParam(name="pageNo", defaultValue="1") Integer pageNo, @RequestParam(name="pageSize", defaultValue="10") Integer pageSize, HttpServletRequest req) { QueryWrapper<JwZzcwh> queryWrapper; String[] zcfl = req.getParameterMap().get("zcfl"); if(zcfl!=null && zcfl.length>0){ Map<String,String[]> parmetermap = new HashMap(req.getParameterMap()); parmetermap.remove("zcfl"); jwZzcwh.setZcfl(null); queryWrapper = QueryGenerator.initQueryWrapper(jwZzcwh, parmetermap); List<JwZcfl> zcfllist = jwZcflService.list(new LambdaQueryWrapper<JwZcfl>().eq(JwZcfl::getId,zcfl[0]).or().eq(JwZcfl::getPid,zcfl[0])); String[] insql = new String[zcfllist.size()]; for(int i=0;i<zcfllist.size();i++){ insql[i] = zcfllist.get(i).getId(); } queryWrapper.in("zcfl",insql); }else{ queryWrapper = QueryGenerator.initQueryWrapper(jwZzcwh, req.getParameterMap()); } Page<JwZzcwh> page = new Page<JwZzcwh>(pageNo, pageSize); IPage<JwZzcwh> pageList = jwZzcwhService.page(page, queryWrapper); return Result.OK(pageList); }
Map返回前端多元素示例
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20/** * 获取有效考点列表 * @param * @param * @return */ @AutoLog(value = "考场信息表-获取有效考点列表") @ApiOperation(value="考场信息表-获取有效考点列表", notes="考场信息表-获取有效考点列表") @GetMapping(value = "/getKdList") public Result<?> getKdList() { List<KfdxExamination> kdlist = kfdxExaminationService.getKdList(); List<KfdxRecruitDate> datelist = kfdxExaminationService.getDateList(); Map<String,List<?>> result = new HashMap<>(); result.put("kdlist",kdlist); result.put("datelist",datelist); return Result.OK(result); }
jeecgboot 自定义导出excel
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35/** * 消费明细导出 * * * @param * @param * @param * @return */ @RequestMapping(value = "/getXfjlListExcel") public ModelAndView getXfjlListExcel(@RequestParam(name="serialNo",required = false) String serialNo, @RequestParam(name="name",required = false) String name, @RequestParam(name="canting",required = false) String canting, @RequestParam(name="tiemStart",required = false) String tiemStart, @RequestParam(name="tiemEnd",required = false) String tiemEnd) { List<JwXfjlPageListXls> infoList = jwXfjlService.getXfjlListExcel(serialNo,name,canting,tiemStart,tiemEnd); //JwXfjlTotal total = jwXfjlService.getXfjlListTotal(serialNo,name,canting,tiemStart,tiemEnd); ModelAndView mv = new ModelAndView(new JeecgEntityExcelView()); mv.addObject(NormalExcelConstants.FILE_NAME, "餐厅消费明细"); //此处设置的filename无效 ,前端会重更新设置一下 mv.addObject(NormalExcelConstants.CLASS, JwXfjlPageListXls.class); //update-begin--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置-------------------- ExportParams exportParams=new ExportParams("餐厅消费明细" + "报表", "导出人:管理员" , "餐厅消费明细列表"); exportParams.setImageBasePath("D://"); //update-end--Author:liusq Date:20210126 for：图片导出报错，ImageBasePath未设置---------------------- mv.addObject(NormalExcelConstants.PARAMS,exportParams); mv.addObject(NormalExcelConstants.DATA_LIST, infoList); return mv; }
java中如何将2021-04-05T13:08:22+08:00转成date呢
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15java中如何将2021-04-05T13:08:22+08:00转成date呢 public static Date dealDateFormat(String oldDateStr) { DateFormat df = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssXXX"); try { Date date = df.parse(oldDateStr); return date; } catch (ParseException e) { e.printStackTrace(); } return null; } dealDateFormat("2021-04-05T13:08:22+08:00");
java时间处理–判断当前时间是否在一个时间区间内
1）将当前时间转为和需要判断的时间区间相同格式的时间
例如判断当前时间是否在HH:mm的时间区间内（例时间区间0点到6点 [00:06]）
String strStartTime = “0:00”;
String strEndTime = “6:00”;
当前时间需转为HH:mm的时间格式：
SimpleDateFormat sdf = new SimpleDateFormat(“HH:mm”);
String now = sdf.format(new Date());
//HH:mm格式的当前时间
Date nowTime = sdf.parse(now);
//时间区间String转Date
Date startTime = sdf.parse(strStartTime);
Date endTime = sdf.parse(strEndTime);
2）调用isEffectiveDate(Date nowTime, Date startTime, Date endTime) 方法
在区间内返回true ，不在区间内返回false
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51/** * 判断当前时间是否在[startTime, endTime]区间，注意三个参数的时间格式要一致 * @param nowTime * @param startTime * @param endTime * @return 在时间段内返回true，不在返回false */ public static boolean isEffectiveDate(Date nowTime, Date startTime, Date endTime) { if (nowTime.getTime() == startTime.getTime() || nowTime.getTime() == endTime.getTime()) { return true; } Calendar date = Calendar.getInstance(); date.setTime(nowTime); Calendar begin = Calendar.getInstance(); begin.setTime(startTime); Calendar end = Calendar.getInstance(); end.setTime(endTime); return date.after(begin) && date.before(end); } public static void main(String[] args) { /** * 判断当前时间是否在一个时间段内 HH:mm 格式 */ String strStartTime1 = "00:00"; String strEndTime1 = "6:00"; SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); String now = sdf.format(new Date()); Date nowTime; try{ nowTime = sdf.parse(now); Date startTime1 = sdf.parse(strStartTime1); Date endTime1 = sdf.parse(strEndTime1); // 注：也可以通过此方式判断当前时间是否具体时间段内 yyyy-MM-dd HH:mm:ss格式 [2022-03-09 12:00:00,2022-03-10 15:00:00] // 当前时间和时间段的格式保持一致即可判断 if (isEffectiveDate(nowTime, startTime1, endTime1)) { System.out.println("当前时间在时间段内["+strStartTime1+","+strEndTime1+"]"); } else { System.out.println("当前时间不再时间段内["+strStartTime1+","+strEndTime1+"]"); } }catch (Exception e){ e.printStackTrace(); } }
java获取特定日期Date
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37// 昨天16:00 public static Date getStartTime() { Calendar time = Calendar.getInstance(); time.add(Calendar.DATE, -1); time.set(Calendar.HOUR_OF_DAY, 16); time.set(Calendar.MINUTE, 0); time.set(Calendar.SECOND, 0); time.set(Calendar.MILLISECOND, 0); return time.getTime(); } // 当天16:00 public static Date getEndTime() { Calendar time = Calendar.getInstance(); time.set(Calendar.HOUR_OF_DAY, 16); time.set(Calendar.MINUTE, 00); time.set(Calendar.SECOND, 00); time.set(Calendar.MILLISECOND, 999); return time.getTime(); }
java计算两个日期【字符串类型】之间的时间距离
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52public SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); if(timesBetween(dateFormat.format(ajxxkfxxList.get(j).getStartTime()),currentdate)<6){ log.info(kfxx.getFjh()+"客房起始日期："+dateFormat.format(ajxxkfxxList.get(j).getStartTime()) +"距离计费截止日期："+currentdate+"时差：" +timesBetween(dateFormat.format(ajxxkfxxList.get(j).getStartTime()),currentdate) +"个小时，低于6小时，此房间不计费！" ); continue; } /** * @Description: TODO(计算两个日期【字符串类型】之间的时间距离) * @param @param sdate * @param @param bdate * @param @return 设定文件 * @throws */ public static Long timesBetween(String sdate, String bdate) { DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); long day = 0; long hour = 0; long min = 0; long sec = 0; long diff = 0; try { Date startDate= df.parse(sdate); Date bindDate= df.parse(bdate); long stime = startDate.getTime(); long btime = bindDate.getTime(); if(stime>btime){ diff = stime - btime; }else{ diff = btime - stime; } //day = diff/(24*60*60*1000); hour = diff/(60*60*1000) - day*24; //min = diff/(60*1000) - day*24*60 - hour*60; //sec = diff/1000-day*24*60*60-hour*60*60-min*60; } catch (ParseException e) { e.printStackTrace(); } return hour; }
java double除法运算保留2位小数
复制代码
1
2
3DecimalFormat gtmoneydf = new DecimalFormat("0.00"); double gtmoney = Double.valueOf(gtmoneydf.format((float)hjmoney / ajsl));
java将数值（double）类型转化成人民币大写
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29double money = jwAjxxCzjl.getCzje(); double score = new BigDecimal(money).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); //不足两位则补0 DecimalFormat decimalFormat = new DecimalFormat("0.00#"); String zbjebllwxs = decimalFormat.format(score); //数字保留小数点2位补0转换为人民币大写 DoubleToString(new BigDecimal(money).setScale(2, RoundingMode.HALF_UP)) public static String DoubleToString(BigDecimal num) { //将数字变成单纯的字符串 String string = String.valueOf(num).replace(".", ""); // 大写数字 String[] numbers = {"零","壹","贰","叁","肆","伍","陆","柒","捌","玖"}; //数位对应名称 String[] d = {"分","角","元","拾","佰","仟","万","拾","佰","仟","亿"}; StringBuffer sb = new StringBuffer(); char[] c = string.toCharArray();//后面的是分。所以倒叙遍历 int idx = 0; for(int i =c.length-1;i>=0;i-- ) { //我确定我传入的数字是两位小数的，从分开始。所以不做额外判断 //也因为不会超过亿，所以idx也不判断 sb.append(d[idx++]+(numbers[c[i]-'0'])); } //之前是倒叙追加的。现在直接反转 return sb.reverse().toString(); }
比较两个相等的double值
我们可以直接使用sun提供的Double.doubleToLongBits（）方法来判断
Java BigDecimal类型的 加减乘除运算
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45第一种：BigDecimal(double val) 第二种：BigDecimal(String val) //通常采用这种方式 来看看BigDecimal的四则运算： 加法：add 减法：subtract 乘法：multiply 除法：divide 可参考下面代码： BigDecimal bignum1 = new BigDecimal("10"); BigDecimal bignum2 = new BigDecimal("5"); BigDecimal bignum3 = null; //加法 bignum3 = bignum1.add(bignum2); System.out.println("求和：" + bignum3); //减法 bignum3 = bignum1.subtract(bignum2); System.out.println("求差：" + bignum3); //乘法 bignum3 = bignum1.multiply(bignum2); System.out.println("乘法积：" + bignum3); //除法 bignum3 = bignum1.divide(bignum2); System.out.println("除法结果：" + bignum3);
Java中BigDecimal比较大小的方法
复制代码
1
2
3
4
5
6
7
8
9
10BigDecimal比较大小一般用的是BigDecimal的compareTo方法 int result = bigdemical1.compareTo(bigdemical2) result = -1,表示bigdemical1小于bigdemical2； result = 0,表示bigdemical1等于bigdemical2； result = 1,表示bigdemical1大于bigdemical2；
Java 根据开始编号和结束编号 计算出之间的编号,支持001-100,不同长度
001-010
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18String startNo = "001"; String endNo = "010"; System.err.println("开始编号Str:"+startNo); System.err.println("结束编号Str:"+endNo); Integer startInt = Integer.parseInt(startNo); Integer endInt = Integer.parseInt(endNo); System.err.println("开始编号转成Int:"+startInt); System.err.println("结束编号转成Int:"+endInt); for (int i = startInt; i <= endInt; i++){ // 重要 // 0 代表前面补0 字符串长度 代表0+i会补到这个长度 d 代表整数型 String no = String.format("%0"+startNo.length()+"d",i); System.err.println("循环:"+no); }
两个编号不同长度
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18String startNo = "998"; String endNo = "1005"; System.err.println("开始编号Str:"+startNo); System.err.println("结束编号Str:"+endNo); Integer startInt = Integer.parseInt(startNo); Integer endInt = Integer.parseInt(endNo); System.err.println("开始编号转成Int:"+startInt); System.err.println("结束编号转成Int:"+endInt); for (int i = startInt; i <= endInt; i++){ // 重要 // 0 代表前面补0 字符串长度 代表0+i会补到这个长度 d 代表整数型 String no = String.format("%0"+startNo.length()+"d",i); System.err.println("循环:"+no); }
根据每天数据量生成当天档案号
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14SimpleDateFormat format = new SimpleDateFormat("yyMMdd"); Integer jrdaycount = hhxhisAccountInfoService.getJrDayZhrs(); int num = jrdaycount+1; // 需要补零的数字 int len = 6; // 补零之后数字的总长度 StringBuilder dtnumnber = new StringBuilder(); dtnumnber.append(num); int temp = len - dtnumnber.length(); if (temp > 0) while (dtnumnber.length() < len) //若长度不足进行补零 dtnumnber.insert(0, "0"); // 每次都在最前面补零 dah = format.format(new Date())+dtnumnber;
获取当前登录用户信息
复制代码
1
2
3
4
5
6
7LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal(); if(sysUser!=null){ jwDhrkd.setLrr(sysUser.getId()); jwDhrkd.setLrbm(sysUser.getDepartIds()); }
Springboot中Bigdecimal以json格式返回前端丢失小数点
基本原理就是在把数据给前端的时候，把数据转换成string类型，这样就不会丢失小数点后面的（.00）数据。需要注意的是，前端接收到的是string类型数据，如果涉及到数据计算问题，需要前端进行数据转换！
复制代码
1
2
3
4@JsonFormat(shape = JsonFormat.Shape.STRING) private BigDecimal amt;
Spring Boot修改内置Tomcat端口号
复制代码
1
2
3
4Spring Boot默认配置文件为CLASSPATH下的application.properties 在application.properties中添加server.port=8011，可将tomcat默认端口号修改为8011 Spring Boot修改内置Tomcat端口号
mybatis语句示例
注解标识实体中该字段不为数据表字段
复制代码
1
2
3
4
5@TableField(exist = false)：表示该属性不为数据库表字段，但又是必须使用的。 @TableField(exist = false) private java.lang.String sexvalue;
select语句
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25<!-- 根据手机号码、卡号查询 --> <select id="getUserInfoByCardPhone" resultType="org.jeecg.modules.system.entity.SysUser"> select * from sys_user where del_flag = 0 <if test="cardUrl != null and cardUrl != '' and cardUrl != 'null'"> and card_url = #{cardUrl} </if> <if test="phone != null and phone != ''"> and phone = #{phone} </if> <if test="tiemStart != null and tiemStart !=''"> and xfjl.create_time <![CDATA[ >= ]]> #{tiemStart} </if> <if test="tiemEnd != null and tiemEnd !=''"> and xfjl.create_time <![CDATA[ <= ]]> #{tiemEnd} </if> <if test="kssj != null and kssj != ''"> <![CDATA[ AND create_time >= #{kssj}]]> </if> <if test="jssj != null and jssj != ''"> <![CDATA[ AND create_time <= #{jssj}]]> </if> ORDER BY org_code ASC limit 10 </select>
update语句
复制代码
1
2
3
4<update id="upUserStatus"> UPDATE kfdx_user SET status = #{status},remark = #{remark} where id = #{sysUserId} </update>
delete语句
复制代码
1
2
3
4<delete id="deleteLogicDeleted"> DELETE FROM sys_user WHERE del_flag = 1 AND id IN (${userIds}) </delete>
模糊查询
复制代码
1
2
3
4<if test="mobile != null and mobile !=''"> and us.mobile like concat(concat('%',#{mobile}),'%') </if>
if…else的用法
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14<select id="ryListByAjId" resultType="org.jeecg.modules.aj.ajryxx.entity.JwAjryxx"> select id,name from jw_ajryxx where <choose> <when test="ajId != null and ajId !=''"> ajid = #{ajId} </when> <otherwise> ajid = '0' </otherwise> </choose> </select>
foreach语句
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14<select id="queryByDepIds" resultType="org.jeecg.modules.system.entity.SysUser"> select * from sys_user where del_flag = 0 <if test="departIds!=null and departIds.size()>0"> and id in (select user_id from sys_user_depart where dep_id in <foreach collection="departIds" index="index" item="id" open="(" separator="," close=")"> #{id} </foreach> ) </if> <if test="username!=null and username!=''"> and username != #{username} </if> </select>
CASE WHEN 查询字段返回默认值
复制代码
1
2(CASE WHEN sex = '1' THEN '男' WHEN sex = '1' THEN '女' ELSE '未知' END) as sexvalue
通过多部门ID查询部门下用户信息
复制代码
1
2
3
4
5
6
7/** * 根据部门Ids查询 * @param list的部门id * @return list的用户实体 */ List<SysUser> queryByDepIds(List<String> departIds, String username);
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15<!-- 通过多个部门IDS，查询部门下的用户信息 --> <select id="queryByDepIds" resultType="org.jeecg.modules.system.entity.SysUser"> select * from sys_user where del_flag = 0 <if test="departIds!=null and departIds.size()>0"> and id in (select user_id from sys_user_depart where dep_id in <foreach collection="departIds" index="index" item="id" open="(" separator="," close=")"> #{id} </foreach> ) </if> <if test="username!=null and username!=''"> and username != #{username} </if> </select>
sql语句片段组合
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36<!-- SQL片段：getUserByOrgCode 的 FROM 和 WHERE 部分 --> <sql id="getUserByOrgCodeFromSql"> FROM sys_depart INNER JOIN sys_user_depart ON sys_user_depart.dep_id = sys_depart.id INNER JOIN sys_user ON sys_user.id = sys_user_depart.user_id WHERE sys_user.del_flag = 0 AND sys_depart.org_code LIKE '${orgCode}%' <if test="userParams != null"> <if test="userParams.realname != null and userParams.realname != ''"> AND sys_user.realname LIKE concat(concat('%',#{userParams.realname}),'%') </if> <if test="userParams.workNo != null and userParams.workNo != ''"> AND sys_user.work_no LIKE concat(concat('%',#{userParams.workNo}),'%') </if> </if> </sql> <!-- 根据 orgCode 查询用户，包括子部门下的用户 --> <select id="getUserByOrgCode" resultType="org.jeecg.modules.system.model.SysUserSysDepartModel"> SELECT sys_user.id AS id, sys_user.realname AS realname, sys_user.avatar AS avatar, sys_user.work_no AS workNo <include refid="getUserByOrgCodeFromSql"/> ORDER BY sys_depart.org_code ASC </select> <!-- 查询 getUserByOrgCode 的总数--> <select id="getUserByOrgCodeTotal" resultType="java.lang.Integer"> SELECT COUNT(1) <include refid="getUserByOrgCodeFromSql"/> </select>
MYSQL语句示例
查询在一张表不在另外一张表的记录
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31条件：tb1的字段key的值不在tbl2表中 SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id=tb2.id WHERE tb2.id IS NULL; 例子1通过ajid查询所有不在tb2存在out_time和in_time的tb1记录 select t1.* from tb1 as t1 LEFT JOIN tb2 as t2 ON t1.userid = t2.ryid where t1.czsj is null and t1.ajid = '1573501179925041154' and t2.out_time is null and t2.in_time is null <!--例子2查看所有分配谈话室的案件列表 --> <select id="getNoTargetRoomAjxxListByAjztAjlx" resultType="org.jeecg.modules.aj.ajxx.entity.JwAjxx"> select ajxx.* from jw_ajxx as ajxx LEFT JOIN jw_ajxx_kfxx as ajkf ON ajxx.id = ajkf.ajid and ajkf.kfid in (select kf.id from jw_kfxx as kf where kf.fjlx = '1') where ((ajkf.start_time is null and ajkf.end_time is null) or (ajkf.start_time is not null and ajkf.end_time is not null)) <if test="ajzt != null and ajzt != ''"> and ajxx.ajzt = #{ajzt} </if> <if test="ajlx != null and ajlx != ''"> and ajxx.ajlx = #{ajlx} </if> order by ajxx.create_time desc </select>
积木报表示例
按日期查询2个表的字段
复制代码
1
2
3
4
5
6
7
8
9
10select feeDate,SUM(czje) czje,SUM(tfje) tfje,SUM(xfje) xfje from( select DATE_FORMAT(a.create_time,'%Y-%m-%d') as feeDate, if(czlx=1,SUM(a.czje),0) czje,if(czlx=2,SUM(a.czje),0) tfje,0 xfje from jw_czjl a GROUP BY feeDate,czlx union select DATE_FORMAT(x.create_time,'%Y-%m-%d') as feeDate,0 czje,0 tfje,SUM(x.xfje) xfje from jw_xfjl x where x.jlrylx in ("1","2","3","4") and (x.xfstatus=9 or x.xfstatus=1) GROUP BY feeDate ) temp GROUP BY feeDate ORDER BY feeDate desc
示例二（消费汇总时段）
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18select ANY_VALUE(DATE_FORMAT(a.create_time,'%Y-%m-%d')) as feeDate, ANY_VALUE(ct.ctmc) as ctmc, count(case when sdmc='早餐' then a.id end) as zcnum,COALESCE(sum(case when sdmc='早餐' then a.btje end),0) zcbtje, count(case when sdmc='午餐' then a.id end) as zwnum,COALESCE(sum(case when sdmc='午餐' then a.btje end) ,0) zwbtje, count(case when sdmc='晚餐' then a.id end) as wcnum,COALESCE(sum(case when sdmc='晚餐' then a.btje end),0) wcbtje from jw_xfjl a left join jw_xfgz c on a.xfgzid=c.id left join jw_xfbz f on c.xfbzid=f.id left join sys_user b on b.id=a.userid left join sys_user_depart d on b.id=d.user_id left join sys_depart e on e.id=d.dep_id left join jw_zd zd on a.zdid=zd.id left join jw_canting ct on ct.id=zd.ctid where a.jlrylx in ("1","2","3","4") and (a.xfstatus=9 or a.xfstatus=1) and a.create_time between '${startDate}' and '${endDate}' and b.realname like '%${name}%' and e.id like '%${depId}%' and ct.id like '%${ctid}%' GROUP BY feeDate,ct.id order by feeDate desc
最后
以上就是淡淡仙人掌最近收集整理的关于jeecgboot使用例子java代码示例mybatis语句示例MYSQL语句示例的全部内容，更多相关jeecgboot使用例子java代码示例mybatis语句示例MYSQL语句示例内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复