ssm两表增删改

---

实体类

实体类DtsAdmin

package pojo;

import lombok.Data;

@Data
public class DtsAdmin {

  private long id;
  private String username;//姓名
  private String password;//密码
  private long roleIds;
  private String tel;//手机号码

  private DtsRole dtsRole;

  public DtsAdmin(){}

  public DtsAdmin(String username, String password, long roleIds, String tel) {
    this.username = username;
    this.password = password;
    this.roleIds = roleIds;
    this.tel = tel;
  }

  public DtsAdmin(long id, String username, String password, long roleIds, String tel) {
    this.id = id;
    this.username = username;
    this.password = password;
    this.roleIds = roleIds;
    this.tel = tel;
  }

  public DtsAdmin(String username) {
    this.username = username;
  }

  public DtsAdmin(String username, long roleIds) {
    this.username = username;
    this.roleIds = roleIds;
  }
}

实体类role

package pojo;

import lombok.Data;

@Data
public class DtsRole {

  private long id;
  private String name;


}

mapper

package mapper;

import pojo.DtsAdmin;

import java.util.List;

/**
 * @author ROY
 * @version 1.0
 * @description: TODO
 * @date 2023-08-24 9:16
 */
public interface ConvenientMapper {
    /***
     * @description: 查询所有
            * @param:
            * @return: java.util.List<pojo.DtsAdmin>
            * @author ROY
            * @date: 2023-08-24 9:57
     */
    List<DtsAdmin> getAllCon();

    /***
     * @description: 根据id查询
            * @param: id
            * @return: pojo.DtsAdmin
            * @author ROY
            * @date: 2023-08-24 10:57
     */
    DtsAdmin getByIdCon(Integer id);

    /***
     * @description: 根据id删除
            * @param: id
            * @return: java.lang.Integer
            * @author ROY
            * @date: 2023-08-24 11:10
     */
    int deleteCon(Integer id);

    /***
     * @description: 添加
            * @param: dtsAdmin
            * @return: int
            * @author ROY
            * @date: 2023-08-24 11:25
     */
    int insertCon(DtsAdmin dtsAdmin);

    /***
     * @description: 修改
            * @param: dtsAdmin
            * @return: int
            * @author ROY
            * @date: 2023-08-24 11:40
     */
    int updateCon(DtsAdmin dtsAdmin);

    /***
     * @description: 根据姓名与角色查询
            * @param: dtsAdmin
            * @return: pojo.DtsAdmin
            * @author ROY
            * @date: 2023-08-24 11:55
     */
    List<DtsAdmin> getByNameCon(DtsAdmin dtsAdmin);
}

mapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.ConvenientMapper">

    <sql id="convenientsql">
        a.id, a.username, r.name as role_name, a.tel, a.password
    </sql>

    <!-- 定义查询SQL语句 -->
    <select id="getAllCon" resultMap="adminRoleResultMap">
        SELECT <include refid="convenientsql"/>
        FROM dts_role r
        LEFT JOIN dts_admin a ON r.id = a.roleIds
    </select>

    <!-- 定义结果映射 -->
    <resultMap id="adminRoleResultMap" type="DtsAdmin">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="tel" column="tel"/>
        <result property="password" column="password"/>
        <result property="roleIds" column="roleIds"/>
        <association property="dtsRole" >
            <result property="name" column="role_name"/>
        </association>
    </resultMap>

    <select id="getByIdCon" parameterType="java.lang.Integer" resultType="DtsAdmin">
        select  <include refid="convenientsql"/>
        FROM dts_role r
        LEFT JOIN dts_admin a ON r.id = a.roleIds
        where a.id=#{a.id}
    </select>


    <select id="getByNameCon" parameterType="DtsAdmin" resultType="DtsAdmin">
        select  <include refid="convenientsql"/>
        FROM dts_role r
        LEFT JOIN dts_admin a ON r.id = a.roleIds
        where 1=1
        <if test="username!=null">
            and a.username like concat('%',#{username},'%')
        </if>
        <if test="roleIds!=null">
            and roleIds=#{roleIds}
        </if>
    </select>




    <delete id="deleteCon" parameterType="java.lang.Integer">
        delete from
        dts_admin
        where id=#{id}
    </delete>


    <insert id="insertCon" parameterType="DtsAdmin" >
        INSERT INTO dts_admin
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                username,
            </if>
            <if test="roleIds!=null">
                roleIds,
            </if>
            <if test="tel!=null">
                tel,
            </if>
            <if test="password!=null">
                password,
            </if>
        </trim>
        <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                #{username},
            </if>
            <if test="roleIds!=null">
                #{roleIds},
            </if>
            <if test="tel!=null">
                #{tel},
            </if>
            <if test="password!=null">
                #{password},
            </if>
        </trim>
    </insert>


    <update id="updateCon" parameterType="DtsAdmin">
        UPDATE `dts_admin`
        <trim prefix="SET"  suffixOverrides=",">
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="roleIds!=null">
                roleIds=#{roleIds},
            </if>
            <if test="tel!=null">
                tel=#{tel},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
        </trim>
        where id=#{id}
    </update>

</mapper>

service接口

package service;

import pojo.DtsAdmin;

import java.util.List;

/**
 * @author ROY
 * @version 1.0
 * @description: TODO
 * @date 2023-08-24 9:17
 */
public interface ConvenientService {
    /***
     * @description: 查询所有
     * @param:
     * @return: java.util.List<pojo.DtsAdmin>
     * @author ROY
     * @date: 2023-08-24 9:57
     */
    List<DtsAdmin> getAllCon();

    /***
     * @description: 根据id查询
     * @param: id
     * @return: pojo.DtsAdmin
     * @author ROY
     * @date: 2023-08-24 10:57
     */
    DtsAdmin getByIdCon(Integer id);

    /***
     * @description: 根据id删除
     * @param: id
     * @return: java.lang.Integer
     * @author ROY
     * @date: 2023-08-24 11:10
     */
    int deleteCon(Integer id);

    /***
     * @description: 添加
     * @param: dtsAdmin
     * @return: int
     * @author ROY
     * @date: 2023-08-24 11:25
     */
    int insertCon(DtsAdmin dtsAdmin);

    /***
     * @description: 修改
     * @param: dtsAdmin
     * @return: int
     * @author ROY
     * @date: 2023-08-24 11:40
     */
    int updateCon(DtsAdmin dtsAdmin);

    /***
     * @description: 根据姓名与角色查询
     * @param: dtsAdmin
     * @return: pojo.DtsAdmin
     * @author ROY
     * @date: 2023-08-24 11:55
     */
    List<DtsAdmin> getByNameCon(DtsAdmin dtsAdmin);
}

serviceImpl

package service.impl;

import mapper.ConvenientMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import pojo.DtsAdmin;
import service.ConvenientService;

import java.util.List;

/**
 * @author ROY
 * @version 1.0
 * @description: TODO
 * @date 2023-08-24 9:18
 */
@Service("convenientService")
public class ConvenientServiceImpl implements ConvenientService {

    @Autowired
    private ConvenientMapper convenientMapper;

    @Override
    public List<DtsAdmin> getAllCon() {
        return convenientMapper.getAllCon();
    }

    @Override
    public DtsAdmin getByIdCon(Integer id) {
        return convenientMapper.getByIdCon(id);
    }

    @Override
    public int deleteCon(Integer id) {
        return convenientMapper.deleteCon(id);
    }

    @Override
    public int insertCon(DtsAdmin dtsAdmin) {
        return convenientMapper.insertCon(dtsAdmin);
    }

    @Override
    public int updateCon(DtsAdmin dtsAdmin) {
        return convenientMapper.updateCon(dtsAdmin);
    }

    @Override
    public List<DtsAdmin> getByNameCon(DtsAdmin dtsAdmin) {
        return convenientMapper.getByNameCon(dtsAdmin);
    }
}

test类

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import pojo.User;
import service.UserService;

import java.util.List;

public class StudentTest {
    ApplicationContext context=new ClassPathXmlApplicationContext("ApplicationContext.xml");
    UserService userService = (UserService)context.getBean("studentService");

    @Test
    public void test(){
        List<User> userList= userService.getUserList();
        for (User s:userList) {
            System.out.println(s.toString());
        }
    }

    @Test
    public void testAllByName(){
        int name=1;
        User user = userService.getByName(name);
        System.out.println(user.getPhone());
    }

    @Test
    public void testInsert(){
        User user=new User();
        user.setUserName("三");
        user.setPhone("12332232112");
        user.setPwd("123456");
        user.setRoleid(1);
        int count = userService.getUserAdd(user);
        if(count>0){
            System.out.println("添加成功！");
        }else{
            System.out.println("添加失败！");
        }
    }

    @Test
    public void testDelet(){
        int count= userService.delUser(81);
        if(count>0){
            System.out.println("删除成功！");
        }else{
            System.out.println("删除失败！");
        }
    }

    @Test
    public void testupdate(){
        User user=new User();
        user.setUserName("a");
        user.setPhone("1000000000");
        user.setId(46);
        int count= userService.updateUser(user);
        if(count>0){
            System.out.println("修改成功！");
        }else{
            System.out.println("修改失败！");
        }
    }

    @Test
    public void insertTest(){
        //调用没有发生异常的方法添加成功 方式异常的没有添加成功  insertTest1添加成功  insertTest2失败
        User student=new User(80,"同意","1231","1242415325325");
        System.out.println("insertTest1"+ userService.insertTest1(student));
        User student1=new User(80,"法律","1231","4123215");
        System.out.println("insertTest2"+ userService.insertTest2(student1));


        //如果这个方法中有一个方法发生异常，两个方法都不会成功
        /*studentService.getUserList();*/
    }

    /*** 
     * @description: 转账业务
            * @param: 
            * @return: void
            * @author ROY
            * @date: 2023-08-19 10:53
     */ 
    @Test
    public void updateTra(){
       userService.getUserList();
    }
}

页面

index.jsp

<%--
  Created by IntelliJ IDEA.
  User: ROY
  Date: 2023-08-16
  Time: 18:41
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<% String basePath =
        request.getScheme() + "://"+ request.getServerName() + ":" + request.getServerPort() + request.getContextPath() + "/";%>
<html>
<head>
    <title>主页面</title>
</head>
<body>
<form action="<%=basePath%>getByNameCon" method="get">
姓名：<input type="text" name="username"  placeholder="请输入名称" >
    <select id="role" name="roleIds" style="width: 200px">
        <option value=null>请选择</option>
        <option value="1">店员</option>
        <option value="2">店长</option>
    </select>
<input type="submit" value="查询" >
<table border="1" >
    <tr>
        <th>编号</th>
        <th>姓名</th>
        <th>密码</th>
        <th>手机号码</th>
        <th>角色</th>
        <th>操作</th>
    </tr>
    <c:forEach var="con" items="${conList}">
        <tr>
            <td>${con.id}</td>
            <td>${con.username}</td>
            <td>${con.password}</td>
            <td>${con.tel}</td>
            <td>${con.dtsRole.name}</td>
            <td>
                <a href="<%=basePath%>/deleteCon?id=${con.id}">删除</a>
                <a href="<%=basePath%>getByIdCon?id=${con.id}">修改</a>
            </td>
        </tr>
    </c:forEach>
</table>
<a href="<%=basePath%>convenient/convenient_insert.jsp">新增</a>
</form>
</body>
</html>

add

<%--
  Created by IntelliJ IDEA.
  User: ROY
  Date: 2023-08-16
  Time: 18:43
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<% String basePath =
        request.getScheme() + "://"+ request.getServerName() + ":" + request.getServerPort() + request.getContextPath() + "/";%>
<html>
<head>
    <title>添加页面</title>
</head>
<body>
<h1>添加信息</h1>
<form action="<%=basePath%>insertCon" method="get">
    姓名: <input type="text" name="username"><br>
    密码: <input type="text" name="password"><br>
    手机号码: <input type="text" name="tel"><br>
    角色: <select id="role" name="roleIds" style="width: 200px">
            <option value="请选择">请选择</option>
            <option value="1">店员</option>
            <option value="2">店长</option>
         </select>
    <%--<input type="text" name="roleIds">--%>
    <input type="submit">
</form>
</body>
</html>

update

<%--
  Created by IntelliJ IDEA.
  User: ROY
  Date: 2023-08-16
  Time: 18:37
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<% String basePath =
        request.getScheme() + "://"+ request.getServerName() + ":" + request.getServerPort() + request.getContextPath() + "/";%>
<html>
<head>
    <title>修改页面</title>
</head>
<body>
<h1>修改用户信息</h1>
<form action="<%=basePath%>updateCon" method="get">
    <input type="text" name="id" value="${dtsAdmin.id}" hidden>
    姓名: <input type="text" name="username" value="${dtsAdmin.username}"><br>
    密码: <input type="text" name="password" value="${dtsAdmin.password}"><br>
    手机号码: <input type="text" name="tel" value="${dtsAdmin.tel}"><br>
    角色: <select id="role" name="roleIds" value="${dtsAdmin.dtsRole}" style="width: 200px">
            <option value="请选择">请选择</option>
            <option value="1">店员</option>
            <option value="2">店长</option>
        </select>
    <input type="submit">
</form>
</body>
</html>