MyBatis操作数据库实现增删改查

创建数据库

语句要分别执行

CREATE DATABASE mybatis;


USE mybatis;

CREATE TABLE `user`(
    id INT(10) NOT NULL PRIMARY KEY,
    `name` VARCHAR(20) DEFAULT NULL,
   


INSERT INTO `user`(id,`name`,pwd)  VALUES
(1,'张三','123456'),
(2,'李四','121212'),
(3,'王五','1314520')

搭配环境

1、在pojo包创建实体类User

2、创建一个工具类utils，用来获取SqlSession实例

3、在resources资源下面创建一个mybatis-config.xml文件，并绑定UserMapper.xml或UserMapper接口

4、在dao包下创建UserMapper接口和创建UserMapper.xml文件

实体类User，使用注解需要导入lombok的依赖包

package com.qing.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;

}

工具类MyUtils

package com.qing.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 MyUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try{
            String resources="mybatis-config.xml";
            InputStream inputStream= Resources.getResourceAsStream(resources);
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

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="test">
        <environment id="test">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;characterEcoding=uft-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="com.qing.dao.UserMapper"></mapper>
    </mappers>
</configuration>

会遇到资源过滤问题，需要将build导入在pom.xml中

  <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>

增加数据

UserMapper接口

package com.qing.dao;

import com.qing.pojo.User;

public interface UserMapper {
  int addUser(User user);

}

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="com.qing.dao.UserMapper">
        <insert id="addUser">
            insert into user(id,name,pwd) values (#{id},#{name},#{pwd})
        </insert>
</mapper>

测试，测试使用注解需要导入Junit依赖

package com.qing;

import com.qing.dao.UserMapper;
import com.qing.pojo.User;
import com.qing.utils.MyUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class MyBatisTest {
    @Test

    public void test(){
        SqlSession sqlSession = MyUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(4, "安安", "238421"));
        if(i>0){
            System.out.println("插入成功");
        }

       sqlSession.commit();
        sqlSession.close();
    }

}

删除数据

UserMapper接口

package com.qing.dao;

public interface UserMapper {
  int deleteUser(int user);

}

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="com.qing.dao.UserMapper">
       <delete id="deleteUser">
           delete from user where id=#{id}
       </delete>
</mapper>

测试

package com.qing;

import com.qing.dao.UserMapper;
import com.qing.utils.MyUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class MyBatisTest {
    @Test

    public void test(){
        SqlSession sqlSession = MyUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteUser(4);
        if(i>0){
            System.out.println("删除成功");
        }

       sqlSession.commit();
        sqlSession.close();
    }

}

修改数据

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="com.qing.dao.UserMapper">
       <update id="updateUser">
           update user set name=#{name},pwd=#{pwd} where id=#{id}
       </update>
</mapper>

UserMapper接口

package com.qing.dao;

import com.qing.pojo.User;

public interface UserMapper {
  int updateUser(User user);

}

测试

package com.qing;

import com.qing.dao.UserMapper;
import com.qing.pojo.User;
import com.qing.utils.MyUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class MyBatisTest {
    @Test

    public void test(){
        SqlSession sqlSession = MyUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.updateUser(new User(4,"hh","82390"));
        if(i>0){
            System.out.println("修改成功");
        }

       sqlSession.commit();
        sqlSession.close();
    }

}

查询数据

UserMapper接口，查询所有数据

package com.qing.dao;

import com.qing.pojo.User;

import java.util.List;

public interface UserMapper {
    List<User> queryAllUser();

}

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="com.qing.dao.UserMapper">
    <select id="queryAllUser" resultType="com.qing.pojo.User">
        select * from user
    </select>
</mapper>

测试

package com.qing;

import com.qing.dao.UserMapper;
import com.qing.pojo.User;
import com.qing.utils.MyUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyBatisTest {
    @Test

    public void test(){
        SqlSession sqlSession = MyUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.queryAllUser();
        for (User user : userList) {
            System.out.println(user);
        }
    }

}

如果需要查询一条语句，给点一个条件即可，参数传入主键id，并在UserMapper.xml中的select语句中加上where  id=#{id}

总结：

1、查询所以数据，插入数据，修改数据，参数是一个对象；

2、根据id查询，删除数据，参数是一个基本类型

3、查询不需要提交事务，增删改查需要提交数据，否则，数据不会成功更新到数据库

4、查询的数据类型是一个User，增删改数据类型是int，因为查询查的是数据，一条数据就是一个对象，增删改执行成功就会返回"受影响行数为1"

原理：获取sqlSession实例，通过getMapper反射到UserMapper接口，就可以获取接口中的方法，接口中的方法又和UserMapper.xml绑定，就可以查询数据库语句；数据库的连接交给了mybatis-config.xml配置文件，获取sqlSession实例交给了MyBatis工具类