Mybatis---框架搭建---增删查改

下载依赖网址
https://search.maven.org/

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>cn.kgc</groupId>
  <artifactId>mybatis-demo</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>mybatis-demo</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.2</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.17</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.8</version>
    </dependency>
	<dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
      <scope>test</scope>
    </dependency>

  </dependencies>

  <build>
    <pluginManagement>
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>

main目录下新建resources目录,resources目录下新建mappers目录
mappers目录下
UserMapper.xml(映射文件,写sql语句的地方)

<?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="cn.kgc.dao.UserMapper">

    <!--单参-->
    <select id="getUserList" resultType="User">
        select id,userCode,userName,userPassword from smbms_user
    </select>
    <select id="findById" resultType="User">
        select id,userCode,userName,userPassword from smbms_user where id=#{id}
    </select>
    <select id="findByName" resultType="User">
        select id,userCode,userName,userPassword from smbms_user where userName like concat('%',#{userName},'%')
    </select>
    <insert id="insert" parameterType="User">
        INSERT INTO smbms_user(userCode,userName,userPassword)VALUES(#{userCode},#{userName},#{userPassword})
    </insert>
    <update id="update" parameterType="User">
        update smbms_user set userCode = #{userCode},userName = #{userName},userPassword = #{userPassword} where id = #{id}
    </update>
    <delete id="delete" parameterType="Long">
        delete from smbms_user where id=#{id}
    </delete>



<!--对象入参-->
    <!--resultType是返回结果的类型,parameterType是参数传入的类型-->
    <select id="getUserListByUser" resultType="User" parameterType="user">
        select id,userCode,userName,userPassword from smbms_user where userName like concat('%',#{userName},'%')
    </select>
<!--Map入参-->
    <!--uName是测试的时候的键-->
    <select id="getUserListByMap" resultType="User" parameterType="Map">
        select id,userCode,userName,userPassword from smbms_user where userName like concat('%',#{uName},'%')
    </select>


<!--resultMap   字段不一样时-->
    <resultMap id="userList" type="User">
        <id property="id" column="id"></id>
        <result property="userCode" column="userCode"></result>
        <result property="userName" column="userName"></result>
        <result property="userPassword" column="userPassword"></result>
    </resultMap>
    <select id="getUserList2" resultMap="userList">
        select * from smbms_user
    </select>


<!--association   一对一-->
    <resultMap id="userRoleResult" type="User">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="userPassword" column="userPassword"/>
        <result property="userRole" column="userRole"/>

        <association property="role" javaType="Role">
            <id property="id" column="r_id"/>
            <result property="roleCode" column="roleCode"/>
            <result property="roleName" column="roleName"/>
        </association>
    </resultMap>
    <select id="getUserByRoleId" resultMap="userRoleResult" parameterType="Integer">
        select r.roleName,u.* from smbms_user u,smbms_role r where r.id=u.userRole and u.userRole=#{userRole}
    </select>

<!--collection   一对多-->
    <resultMap id="userList2" type="User">
        <id property="id" column="id"/>
        <result property="userCode" column="userCode"/>
        <result property="userName" column="userName"/>
        <result property="userPassword" column="userPassword"/>
        <result property="userRole" column="userRole"/>

        <collection property="list" ofType="Role" resultMap="roleList"></collection>
    </resultMap>
    <resultMap id="roleList" type="Role">
        <id property="id" column="r_id"/>
        <result property="roleCode" column="roleCode"/>
        <result property="roleName" column="roleName"/>
    </resultMap>
    <select id="getUserListByRoleId" resultMap="userList2" parameterType="Integer">
        select r.roleName,u.* from smbms_user u,smbms_role r where r.id=u.userRole and u.userRole=#{userRole}
    </select>

<!--collection   一对多    自动映射后 只需要写主键id-->
    <resultMap id="BillList" type="Bill">
        <id property="id" column="b_id"/>
        <collection property="listProvider" ofType="Provider">
            <id property="id" column="p_id"/>
        </collection>
    </resultMap>
    <select id="findBillByProName" resultMap="BillList" parameterType="String">
      SELECT b.*,p.* FROM smbms_bill b,smbms_provider p WHERE productName LIKE concat('%',#{ProName},'%') AND b.providerId=p.id
    </select>



    <!--collection   一对多    if-trim判断参数-->
    <resultMap id="BillList2" type="Bill">
        <id property="id" column="b_id"/>
        <collection property="listProvider" ofType="Provider">
            <id property="id" column="p_id"/>
        </collection>
    </resultMap>
    <select id="findBillByProName2" resultMap="BillList2">
      SELECT b.*,p.* FROM smbms_bill b,smbms_provider p
      <trim prefix="where" prefixOverrides="and" >
          <if test="productName!=null and productName!=''">
              productName LIKE CONCAT('%',#{productName},'%')
          </if>
          <if test="providerId!=null">
              AND b.providerId=#{providerId} AND b.providerId=p.id
          </if>
      </trim>
    </select>

    <!--set-if-->
    <update id="update2" parameterType="User">
        update smbms_user
        <set>
            <if test="userCode!=null and userCode!=''">userCode = #{userCode},</if>
            <if test="userName!=null and userName!=''">userName = #{userName},</if>
            <if test="userPassword!=null and userPassword!=''">userPassword = #{userPassword},</if>
        </set>
        where id = #{id}

    </update>

<!--foreach -list-->
    <resultMap id="userMapByRoleId" type="User">
        <id property="id" column="id"></id>
    </resultMap>
    <select id="findListByRoleId_foreach_list" resultMap="userMapByRoleId">
        select * from smbms_user where userRole in
          <foreach collection="list" item="userRoleList" open="(" separator="," close=")">
              #{userRoleList}
          </foreach>
    </select>

<!--分页-->
    <select id="getUserListFen" resultMap="userList">
        select u.*,r.roleName from smbms_user u,smbms_role r where u.userRole=r.id
        <if test="userRole !=null">
            and u.userRole=#{userRole}
        </if>
        <if test="userName !=null and userName!=''">
            and u.userName like CONCAT('%',#{userName},'%')
        </if>
        order by creationDate DESC limit #{from},#{pageSize}
    </select>

</mapper>


main根目录下
db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false
username=root
password=123456

log4j.properties

log4j.rootLogger=DEBUG,CONSOLE,file
#log4j.rootLogger=ERROR,ROLLING_FILE
log4j.logger.cn.kgc.dao=debug
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug
log4j.logger.java.sql.ResultSet=debug
log4j.logger.org.tuckey.web.filters.urlrewrite.UrlRewriteFilter=debug

######################################################################################
# Console Appender  \u65e5\u5fd7\u5728\u63a7\u5236\u8f93\u51fa\u914d\u7f6e
######################################################################################
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.Threshold=error
log4j.appender.CONSOLE.Target=System.out
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern= [%p] %d %c - %m%n


######################################################################################
# DailyRolling File  \u6bcf\u5929\u4ea7\u751f\u4e00\u4e2a\u65e5\u5fd7\u6587\u4ef6\uff0c\u6587\u4ef6\u540d\u683c\u5f0f:log2009-09-11
######################################################################################
log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
log4j.appender.file.DatePattern=yyyy-MM-dd
log4j.appender.file.File=log.log
log4j.appender.file.Append=true
log4j.appender.file.Threshold=error
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-M-d HH:mm:ss}%x[%5p](%F:%L) %m%n


log4j.logger.com.opensymphony.xwork2=error

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>
    <properties resource="db.properties"></properties>
    <!--自动映射-->    
    <settings>
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>
    <!--取别名-->
    <typeAliases>
        <package name="cn.kgc.pojo"></package>
    </typeAliases>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>

        </environment>
        <environment id="test">
            <transactionManager type=""></transactionManager>
            <dataSource type=""></dataSource>
        </environment>
    </environments>
    <!--映射文件在哪里-->
    <mappers>
        <mapper resource="mappers/UserMapper.xml"></mapper><!--绝对路径-->
    </mappers>

</configuration>

pojo目录下的类加入(setting–plugins需导入lombok)
@Data
@AllArgsConstructor
@NoArgsConstructor

dao目录下
UserMapper接口

package cn.kgc.dao;
import cn.kgc.pojo.Bill;
import cn.kgc.pojo.Provider;
import cn.kgc.pojo.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    //查询
    List<User> getUserList();
    //根据id查询User对象
    User findById(int id);
    //根据姓名  模糊查询
    List<User> findByName(String userName);
    //增加
    int insert(@Param(value = "userCode")String userCode,@Param(value = "userName")String userName,@Param(value = "userPassword")String userPassword);
    //修改
    int update(@Param(value = "userCode")String userCode,@Param(value = "userName")String userName,@Param(value = "userPassword")String userPassword,@Param(value = "id")long id);
    //增加
    int delete(long id);


    //查询   对象入参
    List<User> getUserListByUser(User user);
    //查询   Map入参
    List<User> getUserListByMap(Map<String,String> userMap);


    //查询
    List<User> getUserList2();
    //association  一对一
    User getUserByRoleId(Integer userRole);
    //collection  一对多
    List<User>getUserListByRoleId(Integer userRole);

    //模糊查询 根据id  一对多查询
    List<Bill> findBillByProName(String ProName);
    //模糊查询 根据id  一对多查询   if-trim判断参数
    List<Bill> findBillByProName2(@Param(value = "productName")String productName,@Param(value = "providerId")int providerId);
    //修改   set---if 判断参数
    int update2(User user);
    //foreach  集合入参
    List<User>findListByRoleId_foreach_list(List<Integer> userRoleList);

    //分页显示
//    int count();
    List<User>getUserListFen(@Param("from")Integer currentPageNo,
                          @Param("pageSize")Integer pageSize,
                          @Param("userName")String userName,
                          @Param("userRole")Integer userRole
                          );
}


until目录下
MybatisUtil (java文件)

package cn.kgc.until;

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 java.io.IOException;
import java.io.InputStream;

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;
    // 静态代码块,sqlSessionFactory只创建一次
    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
//    创建sqlSession    false:关闭自动提交。需手动提交
    public static SqlSession createSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
//关闭sqlSession
    public static void closeSqlSession(SqlSession sqlSession){
        if (null != sqlSession){
            sqlSession.close();
        }
    }
}

测试类

package cn.kgc;

import cn.kgc.dao.UserMapper;
import cn.kgc.pojo.Bill;
import cn.kgc.pojo.Provider;
import cn.kgc.pojo.Role;
import cn.kgc.pojo.User;
import cn.kgc.until.MybatisUtil;

import jdk.nashorn.internal.objects.annotations.Constructor;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.ibatis.annotations.ConstructorArgs;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.junit.Assert.assertTrue;

public class AppTest {
    private  static Logger logger=Logger.getLogger(AppTest.class);
    private SqlSession sqlSession;
    private UserMapper mapper;

    @Test
    public void shouldAnswerWithTrue()
    {
        assertTrue( true );
    }
    @Before
    public void init(){
        sqlSession = MybatisUtil.createSqlSession();
        mapper = sqlSession.getMapper(UserMapper.class);
    }
    @After
    public void closeSqlSession(){
        MybatisUtil.closeSqlSession(sqlSession);
    }

    @Test
    public void getUserListTest(){
        //查询
        List<User> userList = mapper.getUserList();
        for (User user:userList) {
            System.out.println("===="+user.toString());
        }
    }
    @Test
    public void findById(){
        //根据id查询
        User byId = mapper.findById(11);
        System.out.println("测试========"+byId);
    }
    @Test
    public void findByName(){
        //根据姓名模糊查询
        List<User> list = mapper.findByName("少");
        for (User user:list) {
            System.out.println("===="+user.toString());
        }
    }
    @Test
    public void add(){
        //增加
        int j = mapper.insert("123", "少爷", "123");
        System.out.println("j============"+j);
    }
    @Test
    public void update(){
        //修改
        int i = mapper.update("456", "少爷", "456", 16);
        System.out.println("i=============="+i);
    }
    @Test
    public void delete(){
        //删除
        int i = mapper.delete(16);
        System.out.println("i=============="+i);
    }

    @Test
    public void getUserListByUser(){
        //查询   对象入参
        User user=new User();
        user.setUserName("赵");
        List<User> list = mapper.getUserListByUser(user);
        for (User user2:list) {
            System.out.println("===="+user2.toString());
        }
    }
    @Test
    public void getUserListByMap(){
        //查询   Map入参
        Map<String,String> userMap=new HashMap<String,String>();
        userMap.put("uName","赵");

        List<User> list = mapper.getUserListByMap(userMap);
        for (User user:list) {
            System.out.println("===="+user.toString());
        }
    }
    @Test
    public void getUserList2Test(){
        //查询
        List<User> userList = mapper.getUserList2();
        for (User user:userList) {
            System.out.println("===="+user.toString());
        }
    }
    @Test
    public void getUserListByRoled(){
        //查询   association  一对一
        User userListByRoled = mapper.getUserByRoleId(1);
        System.out.println("===="+userListByRoled);
    }
    @Test
    public void getUserListByRoleId(){
        //查询   collection  一对多
        List<User> userListByRoleId = mapper.getUserListByRoleId(2);
        for(User user:userListByRoleId){
            for(Role role:user.getList()){
                System.out.println("===="+user.getUserName()+"  "+role.getRoleName());
            }
        }
    }
    @Test
    public void findBillByProName2(){
        //模糊查询 根据id  查询
        List<Bill> billList = mapper.findBillByProName2("大",3);

        System.out.println("订单编码"+"  "+"商品名称"+"  "+"供应商编码"+"  "+"供应商名称"+"  "+"供应商联系人"+"  "+"联系电话"+"  "+"订单金额"+"  "+"是否付款");
        for(Bill bill:billList){
            for(Provider provider:bill.getListProvider()){
                System.out.println(bill.getBillCode()+bill.getProductName()+provider.getProCode()+provider.getProName()+provider.getProContact()+provider.getProPhone()+bill.getTotalPrice()+bill.getIsPayment());
            }
        }
    }
    @Test
    public void update2(){
        //修改
        User user=new User();
        user.setId(14);
        user.setUserName("公子");
        user.setUserPassword("789");
        int i = mapper.update2(user);
        System.out.println("i=============="+i);
    }
    @Test
    public void findListByRoleId(){
        //foreach  集合入参
        List<User>list=new ArrayList<>();
        List<Integer>roleList=new ArrayList<>();
        roleList.add(2);
        roleList.add(3);
        List<User> listByRoleId = mapper.findListByRoleId_foreach_list(roleList);
        for(User user:listByRoleId){
            System.out.println("集合======="+user.getUserName()+"   "+user.getUserRole());
        }
    }
    @Test
    public void getUserListFen(){
        //分页显示
        String userName="";
        Integer roleId=null;
        Integer pageSize=3;
        Integer currentPageNo=0;
        List<User> userListFen = mapper.getUserListFen(currentPageNo, pageSize, userName, roleId);
        for(User user:userListFen){
            System.out.println("分页显示====="+user.getUserName()+"  "+user.getUserRole());
        }
    }
}


在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值