Home Tags Posts tagged with "Mybatis增删改查"

Mybatis增删改查

0 53

开始着手实现Mybatis的增删改查,特此记录;

 

一、对Mybatis操作的熟悉和理解

Myabtis操作形成框架后,方便对数据库的操作,简化代码,优雅编程;

 

二、学习思路

  • 搭建环境
    • 数据库编写User类
  • 导入依赖和jar包
    •  Maven导入org.mybtais依赖和junit依赖
    • Mysql8.0jar包
    • 重点:build中配置resources,防止资源导出失败!(约定大于配置,配置文件可能不生效)
  • 编写代码
    • 实体类User
    • 工具类MybatisUtil类
    • Mapper(UserMapper.xml)
    • 接口实现类UserMapper.xml
  • Junit测试
    • //查询所有用户
      List<User> getUserList() ;
    • //通过ID查找用户
      User getUserById(int id);
    • //增加用户
      int addUser(User user);
    • //修改用户
      int updateUser(User user);
    • //删除用户
      int deleteUser(int id);

2.1搭建环境导入依赖及Jar包

Mybatis依赖:

<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>

Build配置:

<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>

Junit依赖:

<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>

Mybatis核心配置文件mybatis-config.xml:

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">


<configuration>
    <environments default="mysqlOk">
        <environment id="mysqlOk">

            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mysql?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456789"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="com/yang/dao/UserMapper.xml"></mapper>
    </mappers>
</configuration>

2.2代码编写

项目结构:

2.2.1实体类User

  • 属性名对应数据库字段名
  • 设置GetSet方法(或者使用lambok@Data)

2.2.2接口Dao(面向接口编程)

2.2.3接口实现类UserMapper.xml配置文件(具体完成的sql操作)

2.2.4MybatisUtils类完成sqlSessionFactory生产–>sqlSession,返回sqlSession给测试类

2.2.5测试类UserMapperTest

2.2.6Mybatis操作记录代码模板

UserMapperTest:

public class UserMapperTest {


    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        try {
            //方式一:getMapper
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<User> userList = mapper.getUserList();
            //方式二:
//          List<User> userList1 = sqlSession.selectList("com.yang.dao.UserMapper.getUserList");
            for (User user : userList) {
                System.out.println("ID:" + user.getId() + "\tname:" + user.getName() + "\tpwd:" + user.getPwd());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void getUserById(){
        //获取执行sql的对象
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        //获得接口并返回;
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        //通过Id获取对象
        User user = mapper.getUserById(1);
        System.out.println(user.getName());
        //关闭对象
        sqlSession.close();
    }

    @Test
    public void addUser(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.addUser(new User(4,"哈哈","123456"));
        //判断是否成功插入
        if(res == 0){
            System.out.println("插入成功");
        }
        //增删改需要提交事务,否则无法成功
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(4,"呵呵","123123"));

        System.out.println("修改成功!");
        //增删改需要提交事务,否则无法成功
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser() {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteUser(4);

        System.out.println("删除成功!");
        //增删改需要提交事务,否则无法成功
        sqlSession.commit();
        sqlSession.close();
    }
}

MybatisUtil工具类:
package utils;

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;
    static{
        try{
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

三、学习总结:

1.坑点:

1.Mapper配置文件中的大小写写错不会显式报错,但是无法生效;

2.增删改操作必须提交事务,添加sqlSession.commit();

2.注意点:

1.添加新的SQL操作,只需要在UserMapper接口中定义新方法,同时在UserMapper配置文件中添加新的SQL操作,无需改动其他地方;

2.所有的mapper必须在mybatis-config文件下的<mappers>中进行资源加载

3.select标签详解:

  • id:就是对应的namespace(UserMapper接口)中的方法名;
  • resultType:Sql语句执行的返回值,Class,int;
  • parameterType:方法传递的参数;
  • <select id="getUserList" resultType="PoJo.User">
        select * from mysql.mybatisuser;
    </select>

4.Insert

  • <insert id="addUser" parameterType="PoJo.User">
        insert into mysql.mybatisuser (id,name,pwd) values (#{id},#{name},#{pwd});
    </insert>

5.update

  • <update id="updateUser" parameterType="PoJo.User">
        update mysql.mybatisuser set name = #{name},pwd = #{pwd} where id = #{id};
    </update>

6.Delete

  • <delete id="deleteUser" parameterType="int">
        delete from mysql.mybatisuser where id = #{id};
    </delete>

 

7.测试类中方法固定语句(相当于业务层中的方法)

@Test
public void updateUser() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(new User(4,”呵呵”,”123123″));
System.out.println(“修改成功!”);
//增删改需要提交事务,否则无法成功
sqlSession.commit();
sqlSession.close();
}

8.简化操作

  • 写完接口,专注于写Sql,不用再写JDBC代码;
  • 一个方法对应一个Sql语句;

 

四、错误排查总结

1.UserMapper配置文件中命名空间必须为com.yang.dao格式

2.Sql标签和操作不一致(不会报错,但无法成功操作)

3.mybatis-config文件里<mappers resource>格式写错(这里必须是/而不能是.)

4.mybatis-config文件不能有一丝一毫的差错

5.NUllPointerException,没有注册到资源

6.输出的xml文件中存在乱码问题

7.Maven开启build