Java学习：代码与SQL实战及Idea配置-优快云博客

本文介绍了Java学习中关于 Dao 层和 Api 层的代码实践，讲解了如何进行 SQL 查询，包括按年龄和时间排序，分页查询以及查询7天内的数据。同时，提到了在 Idea 中配置 Web 项目链接的方法。最后，通过数学实例强调了每天进步一点点的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

代码

Dao层

public Result userxfz() {
    String sql = "select u.username,IFNULL(sum(o.money),0)\n" +
            "from `user` u LEFT JOIN `order` o\n" +
            "ON u.userid  = o.userid\n" +
            "GROUP BY  u.username ";
    List<Map<String, Object>> listmap = DB.getListMap(sql);
    return new Result(listmap);
}

public Result usermoney(double money) {
    String sql = "select * from ";
    if (checkNotEmpty(money)) {
        sql += " `user` u INNER JOIN `order` o \n" +
                "ON u.userid =o.userid GROUP BY u.username HAVING sum(o.money) > " + money;
    }
    System.out.println(sql);
    List<Map<String, Object>> listmap = DB.getListMap(sql);
    return new Result(listmap);
}

public Result userpage(int pagesize ,int pagenum) {
    String sql = "SELECT * FROM (select * from user ORDER BY regTime DESC ) n ORDER BY age DESC ";
    if (checkNotEmpty(pagenum)&&checkNotEmpty(pagesize)) {
        sql += "  limit "+((pagenum-1)*pagesize)+"," + pagesize;
    }
    System.out.println(sql);
    List<Map<String, Object>> listmap = DB.getListMap(sql);
    return new Result(listmap);
}
public  Result orderdays(int days) {
    String sql = "select * from `order` where 1 = 1 ";
    if (checkNotEmpty(days)) {
        sql += " and TO_DAYS(NOW()) - TO_DAYS(addTime) <= " + days;
    }

    List<Map<String, Object>> listmap = DB.getListMap(sql, 1);
    return new Result(listmap);
}

Api层

@Ann(path = "admin/company/my/userxfz", title = "用户消费总金额")
public Result userxfz(ServerApi api){
    return new UserDAO().userxfz();
}
@Ann(path = "admin/company/my/usersmoney", title = "用户金额表")
public Result userlist(ServerApi api,
                       @Par(nec = false, ds = "用户消费金额") double money
) {
    return new UserDAO().usermoney(money);
}

@Ann(path = "admin/company/my/userspage", title = "用户分页")
public Result userpage(ServerApi api,
                       @Par(nec = true, ds = "分页大小") int pagesize,
                       @Par(nec = true, ds = "第几页") int pagenum
) {
    return new UserDAO().userpage(pagesize,pagenum);
}
@Ann(path = "admin/company/my/ordersday", title = "n天内的订单")
public Result orderday(ServerApi api,
                        @Par(nec = false, ds = "天数") int days

) {
    return new OrderDAO().orderdays(days);
}

sql

升序年龄，降序时间
– SELECT * FROM (select * from user ORDER BY age DESC) n ORDER BY regTime ASC
– select * from order limit (第n+1页面-1)*每页大小,每页大小
– SELECT * FROM order WHERE TO_DAYS(NOW()) - TO_DAYS(addTime) <= 7; 查询7天内的数据

统计金额，为空的用户补为0
– select u.username,IFNULL(sum(o.money),0)
– from user u RIGHT JOIN order o
– ON u.userid = o.userid
– GROUP BY u.username