Mybatis简单应用（会员后台管理）

这是一个应用了mybatis+servlet+vue.js+elementUI的后台会员管理系统的开发。简单的前后端分离的应用，实现了简单的增删改查功能。

下面是后端的mybatis部分

数据库用的是Oracle数据库，数据库表：

主配置文件

Mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL"/>
                <property name="username" value="book_club"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/ustl/dao/MemberMapper.xml"/>
    </mappers>
</configuration>

Member.java

package com.ustl.entity;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * (Member)实体类
 *
 * @author makejava
 * @since 2021-01-01 20:41:03
 */
public class Member {

    private String memberId;

    private String name;

    private String sex;

    private String phone;

    private double balance;

    private int points;

    private Date birthday;


    public String getMemberId() {
        return memberId;
    }

    public void setMemberId(String memberId) {
        this.memberId = memberId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public int getPoints() {
        return points;
    }

    public void setPoints(int points) {
        this.points = points;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = StringToTimeStamp(birthday);
    }


    public Timestamp StringToTimeStamp(String time) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        time = df.format(new Date());
        Timestamp ts = Timestamp.valueOf(time);
        return ts;
    }

    @Override
    public String toString() {
        return "Member{" +
                "memberId='" + memberId + '\'' +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", phone='" + phone + '\'' +
                ", balance=" + balance +
                ", points=" + points +
                ", birthday=" + birthday +
                '}';
    }
}

Mapper部分

MemberMapper.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.ustl.dao.MemberMapper">
    <resultMap id="memberMap" type="com.ustl.entity.Member">
        <id property="memberId" column="member_id"/>
        <result property="memberId" column="member_id"/>
        <result property="birthday" column="birthday" jdbcType="TIMESTAMP"/>
    </resultMap>
    <select id="getAllMemberInfo" resultMap="memberMap">
        select * from member
    </select>
    <select id="getMemberInfoById" resultMap="memberMap">
        select * from member where member_id=#{memberId}
    </select>
    <select id="getMemberInfoByCondition" resultMap="memberMap">
        select * from member limit #{offset},#{rows}
    </select>
    <update id="updateInfo" parameterType="com.ustl.entity.Member">
        update member set member_id=#{memberId},name=#{name},sex=#{sex},phone=#{phone},birthday=#{birthday},
        balance=#{balance},points=#{points} where member_id=#{memberId}
    </update>
    <delete id="delMemberInfoById" parameterType="String">
        delete from member where member_Id=#{id}
    </delete>
    <insert id="addMemberInfo" parameterType="com.ustl.entity.Member">
        insert into member values(#{memberId},#{name},#{sex},#{phone},#{birthday},#{balance},#{points})
    </insert>


</mapper>

MemberMapper.java

package com.ustl.dao;

import com.ustl.entity.Member;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface MemberMapper {
    public List<Member> getAllMemberInfo();
    public Member getMemberInfoById(String MemberId);
    public List<Member> getMemberInfoByCondition(@Param("offset") long offset, @Param("rows") int rows);
    public int updateInfo(Member member);
    public int delMemberInfoById(String memberId);
    public int addMemberInfo(Member member);
}

Utils部分

MybatisUtils.java

package com.ustl.utils;

import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        String resource = "Mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }
    public static SqlSessionFactory sqlSessionFactoryInstance() {
        return sqlSessionFactory;
    }

}

Mybatis简单的测试

Mybatis.java

package com.ustl.test;

import com.ustl.dao.MemberMapper;
import com.ustl.entity.Member;
import com.ustl.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class MybatisTest {
    public static void main(String[] args) {

        SqlSession sqlSession = MybatisUtils.sqlSessionFactoryInstance().openSession();
        MemberMapper memberMapper =sqlSession.getMapper(MemberMapper.class);
        List<Member> members =memberMapper.getAllMemberInfo();

        Member member= new Member();
        member.setMemberId("0001");
        member.setName("1234");
        member.setSex("男");
        member.setPhone("123456");
        member.setBirthday("2020-01-03");
        member.setBalance(121);
        member.setPoints(234);

        int i = memberMapper.updateInfo(member);
        sqlSession.commit();
        System.out.println(i);
        for(Object object:members){
            System.out.println(object);
        }
        sqlSession.close();

    }
}

以下也提供了一些mybatis的仔细的完整测试代码，也可以用来调试mybatis是否正常使用

package com.ustl.test;

import static org.junit.Assert.*;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.ustl.dao.MemberMapper;
import com.ustl.entity.Member;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class MybatisTest {

    @Test
    public void testConnDB() {

        Connection conn = null;

        try {
            conn = DriverManager
                    .getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL");
            PreparedStatement ps = conn.prepareStatement("select * from member");
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                System.out.println(rs.getString("memberId"));
            }

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
    }
    @Test
    public void testMybatisConn() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        try(SqlSession session = sqlSessionFactory.openSession()){
            MemberMapper mapper = session.getMapper(MemberMapper.class);
            System.out.println(mapper.getMemberInfoById(1l).getMemberId());
        }
    }
    @Test
    public void testMybatisPagination() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        try(SqlSession session = sqlSessionFactory.openSession()){
            NewsMapper mapper = session.getMapper(MemberMapper.class);
            List<Member> membersList = mapper. getMemberInfoByCondition(1, 1);
            System.out.println(membersList.size());
            System.out.println(membersList.get(0).getTitle());
        }
    }

}

mybatis部分这里就全部完成了

posted @ 2021-01-07 23:04 TherCode 阅读(128) 评论(0) 收藏举报

TherCode