MyBatis--注解增删改查和级联查询

桃扇骨 2021-08-13 18:56 431阅读 0赞

### Mybatis注解的使用实例 ###

**创建Maven工程及配置依赖**

<?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>pers.zhang</groupId>
      <artifactId>MyBatisPro</artifactId>
      <version>1.0-SNAPSHOT</version>
      <packaging>war</packaging>
    
      <name>MyBatisPro Maven Webapp</name>
      
      <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>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
          <groupId>log4j</groupId>
          <artifactId>log4j</artifactId>
          <version>1.2.17</version>
        </dependency>
        <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.12</version>
          <scope>compile</scope>
        </dependency>
    
        
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.35</version>
        </dependency>
      </dependencies>
    
      <build>
        
        <resources>
          <resource>
            <directory>src/main/java</directory>
            <includes>
              <include>**/*.xml</include>
            </includes>
          </resource>
        </resources>
    
        <finalName>MyBatisPro</finalName>
        <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-war-plugin</artifactId>
              <version>3.2.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>
          </plugins>
        </pluginManagement>
      </build>
    </project>

**Pojo**

public class AdminInfo { 
        private int id;
        private String name;
    
        //关联属性
        private List<Functions> fs;
    	//省略getter和setter
    }
    
    public class Functions { 
        private int id;
        private String name;
        //...
    }
    
    public class Idcard { 
        private int id;
        private String cno;
        //...
    }
    
    public class Person { 
        private int id;
        private String name;
        private int age;
        private  String sex;
        //关联属性
        private  Idcard idcard;
        //...
    }
    
    public class ProductInfo { 
        private int id;
        private String code;
        private String name;
        //关联属性
        private Type type;
        //...
    }
    
    public class Type { 
        private int id;
        private String name;
    
        //关联属性集合
        private List<ProductInfo> pis;
        //...
    }
    
    public class UserInfo { 
        private int id;
        private String userName;
        private String password;
        private String realName;
        private String sex;
        private String address;
        private String emial;
        private String regDate;
        private int status;
        //...
    }

**单表增删改查：UserInfoMapper**

package pers.zhang.mapper;
    
    import org.apache.ibatis.annotations.Delete;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Select;
    import org.apache.ibatis.annotations.Update;
    import pers.zhang.pojo.UserInfo;
    
    import java.util.List;
    
    public interface UserInfoMapper { 
    
        @Select("select * from user_info where id=#{id}")
        public UserInfo findUserInfoById(int id);
    
        @Select("select * from user_info where userName like CONCAT(CONCAT('%',#{userName}),'%')")
        public List<UserInfo> findUserInfoByUserName(String userName);
    
        @Insert("insert into user_info(userName, password)" +
                "values(#{userName}, #{password})")
        public int addUserInfo(UserInfo userInfo);
    
        @Update("update user_info set userName = #{userName}, password=#{password} where id = #{id}")
        public int updateUserInfo(UserInfo userInfo);
    
        @Delete("delete from user_info where id=#{id}")
        public int deleteUserInfo(int id);
    }

**一对一关联映射：IdcardMapper、PersonMapper**

package pers.zhang.mapper;
    
    import org.apache.ibatis.annotations.Select;
    import pers.zhang.pojo.Idcard;
    
    public interface IdcardMapper { 
        //根据id查询身份证信息
        @Select("select * from idcard where id=#{id}")
        public Idcard findIdcardById(int id);
    }

package pers.zhang.mapper;
    
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import pers.zhang.pojo.Person;
    
    /** * @author zhang * @date 2019/11/30 - 10:54 */
    public interface PersonMapper { 
        //根据id查询个人信息
        @Select("select * from person where id=#{id}")
        @Results({ 
                @Result(property = "idcard", column = "cid", one = @One(select = "pers.zhang.mapper.IdcardMapper.findIdcardById"))
        })
        public Person findPersonById(int id);
    }

**一对多关联映射：ProductInfoMapper、TypeMapper**

package pers.zhang.mapper;
    
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import pers.zhang.pojo.ProductInfo;
    
    import java.util.List;
    
    public interface ProductInfoMapper { 
        //根据类型编号查询所有信息
        @Select("select * from product_info where tid = #{tid}")
        List<ProductInfo> findProductInfoByTid(int tid);
    
        //根据商品编号获取商品信息
        @Select("select * from product_info where id = #{id}")
        @Results({ 
                @Result(property = "type", column = "tid", one = @One(select = "pers.zhang.mapper.TypeMapper.findTypeByTd"))
        })
        ProductInfo findProductInfoById(int id);
    }

package pers.zhang.mapper;
    
    import org.apache.ibatis.annotations.Many;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import pers.zhang.pojo.Type;
    
    public interface TypeMapper { 
        //根据商品类型编号查询商品类型信息
        @Select("select * from type where id = #{id}")
        @Results({ 
                @Result(id = true, property = "id", column = "id"),
                @Result(property = "name", column = "name"),
                @Result(property = "pis", column = "id", many = @Many(select = "pers.zhang.mapper.ProductInfoMapper.findProductInfoByTid"))
        })
        Type findTypeById(int id);
    }

**多对多映射关联：FunctionsMapper、AdminInfoMapper**

package pers.zhang.mapper;
    
    import org.apache.ibatis.annotations.Select;
    import pers.zhang.pojo.Functions;
    
    import java.util.List;
    
    public interface FunctionsMapper { 
        //根据管理员id查询管理员信息
        @Select("select * from functions where id in" +
                "(select fid from powers where aid = #{id})")
        List<Functions> findFunctionsByAid(int aid);
    }

package pers.zhang.mapper;
    
    
    import org.apache.ibatis.annotations.Many;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import pers.zhang.pojo.AdminInfo;
    
    public interface AdminInfoMapper { 
        //根据管理员id查询管理员信息
        @Select("select * from admin_info where id = #{id}")
        @Results({ 
                @Result(property = "id", column = "id", id = true),
                @Result(property = "name", column = "name"),
                @Result(property = "fs", column = "id", many = @Many(select = "pers.zhang.mapper.FunctionsMapper.findFunctionsByAid"))
        })
        public AdminInfo findAdminInfoById(int id);
    }

**配置Mybatis**

<?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"/>
    	
    	<typeAliases>
    		<package name="pers.zhang.pojo" />
    	</typeAliases>
    	
    	<environments default="development">
    		
    		<environment id="development">
    			
    			<transactionManager type="JDBC" />
    			
    			<dataSource type="POOLED">
    				<property name="driver" value="${jdbc.driver}" />
    				<property name="url" value="${jdbc.url}" />
    				<property name="username" value="${jdbc.username}" />
    				<property name="password" value="${jdbc.password}" />
    			</dataSource>
    		</environment>
    	</environments>
    	
    	<mappers>
    		<mapper class="pers.zhang.mapper.UserInfoMapper"/>
    		<mapper class="pers.zhang.mapper.PersonMapper"/>
    		<mapper class="pers.zhang.mapper.IdcardMapper"/>
    		<mapper class="pers.zhang.mapper.TypeMapper"/>
    		<mapper class="pers.zhang.mapper.ProductInfoMapper"/>
    		<mapper class="pers.zhang.mapper.AdminInfoMapper"/>
    		<mapper class="pers.zhang.mapper.FunctionsMapper"/>
    	</mappers>
    </configuration>

**测试类MyTest：**

package pers.zhang.test;
    
    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.After;
    import org.junit.Before;
    import org.junit.Test;
    import org.junit.experimental.theories.suppliers.TestedOn;
    import org.omg.PortableInterceptor.AdapterManagerIdHelper;
    import pers.zhang.mapper.AdminInfoMapper;
    import pers.zhang.mapper.PersonMapper;
    import pers.zhang.mapper.TypeMapper;
    import pers.zhang.mapper.UserInfoMapper;
    import pers.zhang.pojo.AdminInfo;
    import pers.zhang.pojo.Person;
    import pers.zhang.pojo.Type;
    import pers.zhang.pojo.UserInfo;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    /** * @author zhang * @date 2019/11/30 - 10:10 */
    public class MyTest { 
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
    
        @Before
        public void init(){ 
            //读取mybatis配置文件
            String resource = "mybatis-config.xml";
            InputStream inputStream;
            try { 
                //得到配置文件流
                InputStream in = Resources.getResourceAsStream(resource);
                //根据配置文件创建session工厂
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
                sqlSession = sqlSessionFactory.openSession();
            }catch (IOException e){ 
                e.printStackTrace();
            }
        }
    
    
        @After
        public void destroy(){ 
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        }
    
    
        //根据id查询
        @Test
        public void testFindUserInfoById(){ 
            UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
            UserInfo userInfo = mapper.findUserInfoById(1);
            System.out.println(userInfo);
        }
    
        //根据用户名模糊查询
        @Test
        public void testFindUserInfoByUserName(){ 
            List<UserInfo> list = sqlSession.getMapper(UserInfoMapper.class).findUserInfoByUserName("m");
            for(UserInfo userInfo : list){ 
                System.out.println(userInfo);
            }
        }
    
        //添加用户
        @Test
        public void testAddUserInfo(){ 
            UserInfo userInfo = new UserInfo();
            userInfo.setUserName("mybatis1");
            userInfo.setPassword("1123456");
            int result = sqlSession.getMapper(UserInfoMapper.class).addUserInfo(userInfo);
            if (result > 0)
                System.out.println("插入成功");
            else
                System.out.println("插入失败！");
        }
    
        //修改用户
        @Test
        public void testUpdateUserInfo(){ 
            UserInfo userInfo = sqlSession.getMapper(UserInfoMapper.class).findUserInfoById(8);
            userInfo.setPassword("123123");
            int result = sqlSession.getMapper(UserInfoMapper.class).updateUserInfo(userInfo);
            if(result > 0){ 
                System.out.println("更新成功！");
                System.out.println(userInfo);
            }else{ 
                System.out.println("更新失败！");
            }
        }
    
    
        //删除用户
        @Test
        public void testDeleteUserInfo(){ 
            int result = sqlSession.getMapper(UserInfoMapper.class).deleteUserInfo(8);
            if(result > 0)
                System.out.println("成功删除了" + result + "条记录。");
            else{ 
                System.out.println("删除失败！");
            }
        }
    
    
        //测试一对一映射
        @Test
        public void testOne2One(){ 
            Person person = sqlSession.getMapper(PersonMapper.class).findPersonById(1);
            System.out.println(person);
        }
    
        //测试一对多关联映射
        @Test
        public void testOne2Many(){ 
            Type type = sqlSession.getMapper(TypeMapper.class).findTypeById(1);
            System.out.println(type);
        }
    
        //测试多对多关联映射
        @Test
        public void testMany2Many(){ 
            AdminInfo adminInfo = sqlSession.getMapper(AdminInfoMapper.class).findAdminInfoById(1);
            System.out.println(adminInfo);
        }
    }