期末考试题解1

表一：User

点击查看代码

CREATE TABLE tb_user(
    UserID  VARCHAR(8) primary key,
    Username VARCHAR(10),
    Usercategory INT,
    Groupno int ,
    Password VARCHAR(16)
    );

对应的pojo

点击查看代码

package com.QixunQiu.pojo;
public class User {
    String UserID    ;
    String Username ;
    String Password ;
    int Usercategory;
    int Groupno ;




    @Override
    public String toString() {
        return "User{" +
                "UserID='" + UserID + '\'' +
                ", Username='" + Username + '\'' +
                ", Password='" + Password + '\'' +
                ", Usercategory=" + Usercategory +
                ", Groupno=" + Groupno +
                '}';
    }

    public int getGroupno() {
        return Groupno;
    }

    public void setGroupno(int groupno) {
        Groupno = groupno;
    }

    public int getUsercategory() {
        return Usercategory;
    }

    public void setUsercategory(int usercategory) {
        Usercategory = usercategory;
    }

    public String getUserID() {
        return UserID;
    }

    public void setUserID(String userID) {
        UserID = userID;
    }

    public String getUsername() {
        return Username;
    }

    public void setUsername(String username) {
        Username = username;
    }

    public String getPassword() {
        return Password;
    }

    public void setPassword(String password) {
        Password = password;
    }

}

UserMapper

点击查看代码

package com.QixunQiu.mapper;

import com.QixunQiu.pojo.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {

    @Select("select * from tb_user where UserID = #{UserID} and Password = #{Password}")
    User select(@Param("UserID") String UserID, @Param("Password")  String Password);

    int updatePassword(User user);

    void addUser(User user);

    List<User> selectALL();

    @Select("select * from tb_user where UserID = #{UserID} ")
    User selectByUserID(@Param("UserID") String UserID);

    void deleteByUserID(String UserID);
}

UserMapper.xml

点击查看代码

<?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.QixunQiu.mapper.UserMapper">
    <insert id="addUser">
        insert into tb_user (UserID, Username,Password)
        values (#{UserID}, #{Username}, #{Password});
    </insert>


    <update id="updatePassword" >
        update tb_user
        <set>
            <if test="Password != null and Password != ''">
                Password = #{Password},
            </if>
        </set>
        where UserID = #{UserID};
    </update>

    <delete id="deleteByUserID">
        delete from tb_user where UserID = #{UserID}
    </delete>

    <select id="selectALL" resultType="com.QixunQiu.pojo.User">
        select *
        from tb_user ;
    </select>
</mapper>

UserService

点击查看代码

package com.QixunQiu.service;

import com.QixunQiu.mapper.UserMapper;
import com.QixunQiu.pojo.User;
import com.QixunQiu.util.SqlSessionFactoryUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class UserService {
    SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtils.getSqlSessionFactory();
    public User selectUser(String UserID, String Password) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.select(UserID, Password);
        sqlSession.close();
        return user;
    }

    public User selectUserByID(String UserID) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.selectByUserID(UserID);
        sqlSession.close();
        return user;
    }

    public List<User> selectAllUser() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users=userMapper.selectALL();
        sqlSession.close();
        return users;
    }

    public void deleteUser(String UserID) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(UserID);
        userMapper.deleteByUserID(UserID);
        sqlSession.commit();
        sqlSession.close();
    }

    public void  updatePassword(User user) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        System.out.println("465652");
        userMapper.updatePassword(user);
        sqlSession.commit();
        sqlSession.close();
    }

    public void addUser(User user) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.addUser(user);
        sqlSession.commit();
        sqlSession.close();
    }
}