Spring+SpringMVC+Mybatis（开发必备技能）02、完整DML&DQL语句demo示例

​

Spring SpringMVC Mybatis（开发必备技能）

02、完整DML&DQL语句demo示例

承接【Spring SpringMVC Mybatis（开发必备技能）01、基础idea环境配置】项目

---

1、修改【resources/mapper/UsersMapper.xml】文件

代码语言：javascript复制

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.item.dao.UsersMapper">
    <!-- 用作基础查询测试 -->
    <select id="GetInfo" resultType="Users">
        select * from users
    </select>
    <!-- 用做传递参数测试 -->
    <select id="SelectName" resultType="Users">
        select * from users where nickName like "%${nickName}%"
    </select>
    <!-- id精准查询 -->
    <select id="UsersSelectById" resultType="Users">
        select * from users where id=#{id}
    </select>
    <!-- 增加 -->
    <insert id="UsersAddInfo">
        insert into users values(0,"${userName}","${pwd}","${nickName}");
    </insert>
    <!-- 修改 -->
    <update id="UsersUpdateInfo">
        update users set pwd="${pwd}" where id=#{id}
    </update>
    <!-- 删除 -->
    <delete id="UsersDeleteById">
        delete from users where id=#{id}
    </delete>
</mapper>

2、修改【dao层UsersMapper.java】文件

代码语言：javascript复制

package com.item.dao;
 
import com.item.model.Users;
import org.apache.ibatis.annotations.Param;
 
import java.util.List;
 
public interface UsersMapper {
    List<Users> GetInfo();
    List<Users> SelectName(@Param("nickName") String nickName);
    Users UsersSelectById(@Param("id") int id);
    int UsersAddInfo(
            @Param("userName") String userName,
            @Param("pwd") String pwd,
            @Param("nickName") String nickName
            );
    int UsersUpdateInfo(
            @Param("pwd") String pwd,
            @Param("id") int id
    );
    int UsersDeleteById(@Param("id") int id);
}

3、修改【service/UsersService.java】文件

代码语言：javascript复制

package com.item.service;
 
import com.item.model.Users;
import org.apache.ibatis.annotations.Param;

import java.util.List;
 
public interface UsersService {
    /**
     * 获取所有
     * @return
     */
    List<Users> GetInfo();

    /**
     * 获取nickName的模糊查询结果集
     * @param nickName
     * @return
     */
    List<Users> SelectName(String nickName);

    /**
     * id精准查询
     * @param id
     * @return
     */
    Users UsersSelectById(int id);

    /**
     * 添加信息
     * @param userName
     * @param pwd
     * @param nickName
     * @return
     */
    int UsersAddInfo(
            String userName,
            String pwd,
            String nickName
    );

    /**
     * 修改密码
     * @param pwd
     * @param id
     * @return
     */
    int UsersUpdateInfo(
            String pwd,
            int id
    );

    /**
     * 删除信息
     * @param id
     * @return
     */
    int UsersDeleteById(int id);
}

4、修改【serviceimpl/UsersServiceImpl.java】

代码语言：javascript复制

package com.item.serviceimpl;

import com.item.dao.UsersMapper;
import com.item.model.Users;
import com.item.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.DigestUtils;

import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.List;

@Service
public class UsersServiceImpl implements UsersService {
    @Autowired
    private UsersMapper usersMapper;

    @Override
    public List<Users> GetInfo() {
        return usersMapper.GetInfo();
    }

    @Override
    public List<Users> SelectName(String nickName) {
        return usersMapper.SelectName(nickName);
    }

    @Override
    public Users UsersSelectById(int id) {
        return usersMapper.UsersSelectById(id);
    }

    @Override
    public int UsersAddInfo(String userName, String pwd, String nickName) {
        pwd=encrypt(pwd);
        return usersMapper.UsersAddInfo(userName, pwd, nickName);
    }

    @Override
    public int UsersUpdateInfo(String pwd, int id) {
        pwd=encrypt(pwd);
        return usersMapper.UsersUpdateInfo(pwd, id);
    }

    /**
     * MD5加密
     * @param password
     * @return
     */
    private String encrypt(String password) {
        String passwordMd5 = null;
        try {
            MessageDigest md5 = MessageDigest.getInstance("MD5");
            byte[] bytes = md5.digest(password.getBytes("utf-8"));
            passwordMd5 = toHex(bytes);
        } catch (NoSuchAlgorithmException | UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return passwordMd5;
    }

    private static String toHex(byte[] bytes) {
        final char[] HEX_DIGITS = "0123456789ABCDEF".toCharArray();
        StringBuilder ret = new StringBuilder(bytes.length * 2);
        for (int i = 0; i < bytes.length; i  ) {
            ret.append(HEX_DIGITS[(bytes[i] >> 4) & 0x0f]);
            ret.append(HEX_DIGITS[bytes[i] & 0x0f]);
        }
        return ret.toString();
    }

    @Override
    public int UsersDeleteById(int id) {
        return usersMapper.UsersDeleteById(id);
    }
}

5、修改【controller/UsersController.java】文件

代码语言：javascript复制

package com.item.controller;
 
import com.item.model.Users;
import com.item.service.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import java.util.List;
 
@Controller
@CrossOrigin
public class UsersController {
    @Autowired
    private UsersService usersService;
 
    @RequestMapping("/GetInfoApi")
    @ResponseBody
    public Object GetInfoApi(){
        List<Users> list=usersService.GetInfo();
        System.out.println(list);
        return list;
    }
    @RequestMapping("/GetInfo")
    public String GetInfo(HttpServletRequest request,Model model){
        List<Users> list=usersService.GetInfo();
        model.addAttribute("lists",list);
        return "GetInfo";
    }
 
    @RequestMapping("/GetName")
    public String GetName(HttpServletRequest request,Model model){
        String nickName = request.getParameter("nickName");
        List<Users> list=usersService.SelectName(nickName);
        model.addAttribute("lists",list);
        return "GetInfo";
    }

    /**
     * 添加页面
     * @param request
     * @param model
     * @return
     */
    @GetMapping("/UserAddInfo")
    public String UserAddInfo(HttpServletRequest request,Model model){
        return "UserAddInfo";
    }

    /**
     * 添加接口，访问方式post
     * @param request
     * @param model
     * @return
     */
    @PostMapping(value = "/UserAddInfoApi")
    public String UserAddInfoApi(HttpServletRequest request,Model model){
        String userName = request.getParameter("userName");
        String pwd = request.getParameter("pwd");
        String nickName = request.getParameter("nickName");
        usersService.UsersAddInfo(userName, pwd, nickName);
        return "redirect:/GetInfo";
    }

    /**
     * 修改页面
     * @param request
     * @param model
     * @return
     */
    @GetMapping("/UserUpdateInfo")
    public String UserUpdateInfo(HttpServletRequest request,Model model){
        String id = request.getParameter("id");
        Users users = usersService.UsersSelectById(Integer.parseInt(id));
        model.addAttribute("users",users);
        return "UserUpdateInfo";
    }

    /**
     * 修改api
     * @param request
     * @param model
     * @return
     */
    @PostMapping(value = "/UserUpdateInfoApi")
    public String UserUpdateInfoApi(HttpServletRequest request,Model model){
        String id = request.getParameter("id");
        String pwd = request.getParameter("pwd");
        usersService.UsersUpdateInfo(pwd,Integer.parseInt(id));
        return "redirect:/GetInfo";
    }

    /**
     * 删除api
     * @param request
     * @param model
     * @return
     */
    @GetMapping(value = "/UsersDeleteById")
    public String UsersDeleteById(HttpServletRequest request,Model model){
        String id = request.getParameter("id");
        usersService.UsersDeleteById(Integer.parseInt(id));
        return "redirect:/GetInfo";
    }
}

6、修改【/views/GetInfo.jsp】文件

代码语言：javascript复制

<%@ page import="java.util.List" %>
<%@ page import="com.item.model.Users" %><%--
<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2022/5/24 0024
  Time: 22:08
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>显示页面·Get请求</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<form action="/GetName" method="get">
    <input type="text" placeholder="输入昵称查询" name="nickName"/>
    <input type="submit" value="提交" class="btn btn-primary"/>
</form>
<% List<Users> lists = (List<Users>) request.getAttribute("lists");%>
<table class="table table-bordered table-hover table-striped">
    <tr class="info">
        <th>编号</th>
        <th>账号</th>
        <th>密码</th>
        <th>昵称</th>
        <th>操作</th>
    </tr>
    <%
        for (Users u : lists) {
    %>
    <tr>
        <td><%=u.getId()%></td>
        <td><%=u.getUserName()%></td>
        <td><%=u.getPwd()%></td>
        <td><%=u.getNickName()%></td>
        <td>
            <a href="/UserUpdateInfo?id=<%=u.getId()%>" class="btn btn-primary">修改</a>
            <a href="/UsersDeleteById?id=<%=u.getId()%>"
               onclick="return confirm('是否删除此行?')"
               class="btn btn-primary">删除</a>
        </td>
    </tr>
    <%
        }
    %>
    <tr>
        <td colspan="10">
            <a href="/UserAddInfo" class="btn btn-block btn-primary">添加</a>
        </td>
    </tr>
</table>
</body>
</html>

7、添加【/views/UserAddInfo.jsp】文件

代码语言：javascript复制

<%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2022/5/25 0025
  Time: 19:06
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>添加信息</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<form action="/UserAddInfoApi" method="post">
    <p>
        <input type="text" name="userName" placeholder="请输入用户名" class="form form-control"/>
    </p>
    <p>
        <input type="password" name="pwd" placeholder="请输入用户密码" class="form form-control"/>
    </p>
    <p>
        <input type="text" name="nickName" placeholder="请输入用户昵称" class="form form-control"/>
    </p>
    <p>
        <input type="submit" value="添加数据" class="btn btn-primary btn-block"/>
    </p>
</form>
</body>
</html>

8、添加【/views/UserUpdateInfo.jsp】文件

代码语言：javascript复制

<%@ page import="com.item.model.Users" %><%--
  Created by IntelliJ IDEA.
  User: Administrator
  Date: 2022/5/25 0025
  Time: 19:20
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>修改密码</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
</head>
<body>
<% Users users=(Users)request.getAttribute("users");%>
<form action="/UserUpdateInfoApi" method="post">
    <p>
        编号：<input type="text" name="id" value="<%=users.getId()%>" readonly/>
    </p>
    <p>
        用户名：<%=users.getUserName()%>
    </p>
    <p>
        <input type="password" name="pwd" value="<%=users.getPwd()%>" placeholder="请输入用户密码" class="form form-control"/>
    </p>
    <p>
        昵称：<%=users.getNickName()%>
    </p>
    <p>
        <input type="submit" value="修改数据" class="btn btn-primary btn-block"/>
    </p>
</form>
</body>
</html>

9、MD5加密32位大写方法备注：

代码语言：javascript复制

package test;
 
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
 
public class common {
    public static void main(String[] args) {
    	//输出结果:96E79218965EB72C92A549DD5A330112
        System.out.println(encrypt("111111"));
    }
    /**
     * 32位大写加密
     * @param password
     * @return
     */
    private static String encrypt(String password) {
        String passwordMd5 = null;
        try {
            MessageDigest md5 = MessageDigest.getInstance("MD5");
            byte[] bytes = md5.digest(password.getBytes("utf-8"));
            passwordMd5 = toHex(bytes);
        } catch (NoSuchAlgorithmException | UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return passwordMd5;
    }
 
    private static String toHex(byte[] bytes) {
        final char[] HEX_DIGITS = "0123456789ABCDEF".toCharArray();
        StringBuilder ret = new StringBuilder(bytes.length * 2);
        for (int i = 0; i < bytes.length; i  ) {
            ret.append(HEX_DIGITS[(bytes[i] >> 4) & 0x0f]);
            ret.append(HEX_DIGITS[bytes[i] & 0x0f]);
        }
        return ret.toString();
    }
}

10、执行测试

执行效果

添加数据：

​

修改密码：

(修改账号为韦文静的用户)

修改成功：

删除测试：

删除测试数据

删除成功

DML语句执行测试完毕，认真练习哦。

java jsp mybatis spring 腾讯云测试服务

0 人点赞