MyBatis框架（2019-09-23）

百度百科：https://baike.baidu.com/item/MyBatis/2824918?fr=aladdin

1.创建数据库

image.png

2.创建数据库order表-执行语句

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '下单用户id',
  `number` varchar(32) NOT NULL COMMENT '订单号',
  `createtime` date NOT NULL COMMENT '创建订单时间',
  `note` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

3.order表插入数据-执行语句

INSERT INTO `order` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `order` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `order` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);

4.创建数据库user表-执行语句

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

5.user表插入数据-执行语句

INSERT INTO `user` VALUES ('1', '王五', null, '2', null);
INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市');
INSERT INTO `user` VALUES ('16', '张小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('22', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('24', '张三丰', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('25', '陈小明', null, '1', '河南郑州');
INSERT INTO `user` VALUES ('26', '王五', null, null, null);

6.数据库表执行结果

7.Eclipse中创建Maven工程

image.png

8.添加compiler包

image.png

9.编辑pom.xml配置文件

image.png

10.创建model层User类

image.png

User类

package com.neuedu.mybatis1.model;

import java.util.Date;

public class User {
        private int id;
        private String username;
        private Date birthday;
        private String sex;
        private String address;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getUsername() {
            return username;
        }
        public void setUsername(String username) {
            this.username = username;
        }
        public Date getBirthday() {
            return birthday;
        }
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
        public String getSex() {
            return sex;
        }
        public void setSex(String sex) {
            this.sex = sex;
        }
        public String getAddress() {
            return address;
        }
        public void setAddress(String address) {
            this.address = address;
        }
        @Override
        public String toString() {
            return "User [id=" + id + ", username=" + username + ", birthday=" + birthday + ", sex=" + sex
                    + ", address=" + address + "]";
        }       
}

11.增加mysql包

image.png

12.创建SqlMapConfig.xml文件

image.png

13.导入MyBatis包

image.png

14.编辑SqlMapConfig.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(环境)-->
<configuration>
    <environments default="development">
    <!-- 运行环境 -->
    <environment id="development">
    <!-- 事务管理器 -->
    <transactionManager type="JDBC" />
    <!-- 数据库连接池 dataSource(数据源)  property(属性) -->
    <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </dataSource>
</environment>
</environments>
    <!-- 加载映射文件 -->
    <mappers>
        <!-- 加载User表的增删改查等处理方法 -->
        <mapper resource="config/UserMapper.xml"/>
    </mappers>
</configuration>

15.创建config文件夹

config文件夹

16.创建log4j.properties日志文件

日志文件

17.编辑log4j.properties日志文件

# Global logging configuration 
log4j.rootLogger=DEBUG, stdout 
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
日志文件

18.在config文件夹内创建UserMapper.xml文件

创建UserMapper.xml文件

19.编辑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="user">
    <!-- parameterType:入参类型 -->
    <select id="getById" parameterType="int" resultType="com.neuedu.mybatis1.model.User">
        <!-- #{}:相当于占位符? -->
        select * from user where id = #{id}
    </select>
    
    <!-- 根据用户名做模糊查询 -->
    <!-- resultType:集合中元素的类型 -->
    <select id="getByUserName" parameterType="String" resultType="com.neuedu.mybatis1.model.User">
        <!-- username参数的格式:%变量% -->
        <!-- select * from user where username like #{username} -->
        <!-- 拼接字符串${}，值必须是value -->
        select * from user where username like '%${value}%'
    </select>
    <!-- 插入 
    useGeneratedKeys="true"需要返回主键
    keyProperty="id"要把主键对应的java对象的哪个属性上(id)
    适用于主键自动生成，比如mysql，主键自增
    -->
    <insert id="save" parameterType="com.neuedu.mybatis1.model.User"
    useGeneratedKeys="true" keyProperty="id">
        <!-- pojo对象的属性值  #{属性名} -->
        insert into user values(null,#{username},#{birthday},#{sex},#{address})
    </insert>
    
    <!-- 第二种方法，较为常用，因为mysql中的id大多数为自动增长 -->
    <insert id="save2" parameterType="com.neuedu.mybatis1.model.User">
        <!-- order什么时候去执行 ，两种情况先执行和后执行-->
        <selectKey keyProperty="id" order="AFTER" resultType="int">
        <!-- 查询主键 -->
            select max(id) from user
        </selectKey>
        
        insert into user values(null,#{username},#{birthday},#{sex},#{address})
    </insert>
    
    <!-- 删除语句 -->
    <delete id="delete" parameterType="int">
        delete from user where id = #{id}
    </delete>
    <!-- 修改语句 -->
    <update id="update" parameterType="com.neuedu.mybatis1.model.User">
        update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id = #{id}
    </update>       
</mapper>

20.创建junit包

创建junit包

22.新建SessionFactoryUtil类

image.png

23.编辑SessionFactoryUtil文件

  • 重点:单例模式
package com.neuedu.mybatis1.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 SessionFactoryUtil {
    //SqlSessionFactory是一个单例模式
    //取得一个单例的SqlSessionFactory
    public static SqlSessionFactory sf = null;
    //静态代码块---加载类的时候执行的静态代码块
    static{
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        InputStream is = null;
        try {
            is = Resources.getResourceAsStream("sqlMapConfig.xml");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        sf = builder.build(is);
    }
    public static SqlSessionFactory getSqlSessionFactory(){
        return sf;  
    }   
}

24.创建junit类型的文件UTest

image.png

25.编辑UTest文件

  • 重点：方法名上方必须加@Test
  • 创建SessionFactoryUtil文件之后，使用单例模式，在UTest里可以直接调用
  • SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
  • SqlSession sqlSession = factory.openSession();
  • 注意最后需要进行提交事务和关闭
  • sqlSession.commit(); 提交事务
  • sqlSession.close();关闭
package test;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

import com.neuedu.mybatis1.model.User;
import com.neuedu.mybatis1.utils.SessionFactoryUtil;

public class UTest {

    @Test
    //保存测试用例：根据用户ID查询
    public void testGetById() throws IOException {
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
            //从工厂中创建连接
            SqlSession sqlSession = factory.openSession();
            User user = sqlSession.selectOne("user.getById",1);
            System.out.println(user);
            sqlSession.close();

}
    @Test
    public void testGetByUserName() throws IOException {
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        //从工厂中创建连接
        SqlSession sqlSession = factory.openSession();
        List<User> list = sqlSession.selectList("user.getByUserName","小");
        //关闭连接资源(放到连接池中)
        for(User u:list)
        System.out.println(u);
        sqlSession.close();
}
    
    @Test
    public void testsave() throws IOException {
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        //从工厂中创建连接
        SqlSession sqlSession = factory.openSession();
        User user = new User();
        user.setUsername("小王");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("辽宁铁岭");
        sqlSession.insert("user.save",user);
        //提交事务，默认是不提交       
        sqlSession.commit();
        System.out.println(user);
        sqlSession.close();
}
    
    @Test
    public void testsave2() throws IOException {
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        //从工厂中创建连接
        SqlSession sqlSession = factory.openSession();
        User user = new User();
        user.setUsername("小王");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("辽宁铁岭");
        sqlSession.insert("user.save2",user);       
        //提交事务，默认是不提交
        sqlSession.commit();
        System.out.println(user);
        sqlSession.close();
}
    @Test
    public void testdelete() throws IOException {
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        SqlSession sqlSession = factory.openSession();
        sqlSession.delete("user.delete",43);
        sqlSession.commit();
        sqlSession.close();
}
    @Test
    public void testupdate() throws IOException {
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        //从工厂中创建连接
        SqlSession sqlSession = factory.openSession();
        User user = sqlSession.selectOne("user.getById",24);
        user.setUsername("小李");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setAddress("辽宁沈阳");
        sqlSession.update("user.update",user);      
        //提交事务，默认是不提交
        sqlSession.commit();
        sqlSession.close();
}
}

26.创建OrderMapper.xml文件，编辑OrderMapper.xml文件。创建OTest文件，编辑OTest文件。在SqlMapConfig.xml添加xml文件路径(原理同User)

  • 注意：因为Order是在数据库中是关键字(Order by)，所以在进行查询数据库中的order表时，应该将order加上单引号，注意是键盘Tab键的上方的单引号
  • OrderMapper.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="or">
    
    <!-- 根据id查询用户信息 -->
    <select id="select" parameterType="int" resultType="com.neuedu.mybatis1.model.Order">
        select * from `order` where id = #{id}
    </select>
    
    <!-- 根据user_id查询用户信息集合 -->
    <select id="selectList" parameterType="int" resultType="com.neuedu.mybatis1.model.Order">
        select * from `order` where user_id = #{user_id}
    </select>

    <!-- 插入 -->
    <insert id="insert" parameterType="com.neuedu.mybatis1.model.Order">
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            select max(id) from `order`
        </selectKey>    
        insert into `order` values(null,#{user_id},#{number},#{createtime},#{note})
    </insert>
    
    <!-- 删除语句 -->
    <delete id="delete" parameterType="int">
        delete from `order` where id = #{id}
    </delete>
    <!-- 修改语句 -->
    <update id="update" parameterType="com.neuedu.mybatis1.model.Order">
        update `order` set user_id=#{user_id},number=#{number},createtime=#{createtime},note=#{note} where id = #{id}
    </update>       
</mapper>
  • OTest
package test;

import java.io.IOException;
import java.util.Date;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import com.neuedu.mybatis1.model.Order;
import com.neuedu.mybatis1.utils.SessionFactoryUtil;

public class OTest {
    
    //增
    @Test
    public void insert() throws IOException{
        //1.调用uil
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        //2.创建连接
        SqlSession sqlSession = factory.openSession();      
        //3.创建对象
        Order order = new Order();      
        //4.放入值
        order.setUser_id(10);
        order.setNumber("101101");
        order.setCreatetime(new Date());
        order.setNote("2544");
        //5.调用方法
        sqlSession.insert("or.insert",order);
        //6.提交
        sqlSession.commit();
        //7.关闭
        sqlSession.close();
    }
    
    //删
    @Test
    public void delete() throws IOException{
    SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
    SqlSession sqlSession = factory.openSession();
    sqlSession.delete("or.delete",3);
    sqlSession.commit();
    sqlSession.close(); 
    }
    
    //改
    @Test
    public void update() throws IOException{
    SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
    SqlSession sqlSession = factory.openSession();
    Order order = sqlSession.selectOne("or.select",4);
    order.setUser_id(26);
    order.setNumber("10145401");
    order.setCreatetime(new Date());
    order.setNote("2554");
    //5.调用方法
    sqlSession.update("or.update",order);
    //6.提交
    sqlSession.commit();
    //7.关闭
    sqlSession.close();
    }
    
    //查
    @Test
    public void select() throws IOException{
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        SqlSession sqlSession = factory.openSession();
        Order order = sqlSession.selectOne("or.select",4);
        System.out.println(order);
        sqlSession.close(); 
        }
    
    @Test
    //根据user_id查集合
    public void selectList() throws IOException{
        SqlSessionFactory factory = SessionFactoryUtil.getSqlSessionFactory();
        SqlSession sqlSession = factory.openSession();
        List<Order> list = sqlSession.selectList("or.selectList",10);
        for(Order o:list){
        System.out.println(o);
        }
        sqlSession.close(); 
        }
    }
  • 在SqlMapConfig.xml新增xml文件路径
</environments>
    <!-- 加载映射文件 -->
    <mappers>
        <!-- 加载User表的增删改查等处理方法 -->
        <mapper resource="config/UserMapper.xml"/>
        <mapper resource="config/OrderMapper.xml"/>
    </mappers>
</configuration>
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容