Mybatis连接数据库完成CRUD

使用mybatis操作mysql数据库并完成CRUD操作

详情见GitHub传送门

官方文档

连接准备

mybatais连接数据库都属在mybatis的配置文件中完成的：

• url:数据库连接url
• driver：数据库连接驱动
• usname:数据库名
• password：连接密码

上述的这些参数，我们在dp.properits中设置的,(!!!这些配置文件均放在resources下，同一目录)

#数据库连接的配置文件
#注意mysql8.0的数据库连接需要后面的参数，否则连接不上
url=jdbc:mysql://localhost:3306/tumo?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
driver=com.mysql.jdbc.Driver
username=root
password=root

<?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>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <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>
  </environments>
  <mappers>
    <mapper resource="org/mybatis/example/BlogMapper.xml"/>
  </mappers>
</configuration>

创建数据库

create table mybatais
(
    user_id   int unsigned auto_increment
        primary key,
    user_name varchar(20) not null,
    sex       varchar(5)  null,
    age       int         null
);

查找、插入语、更新、删除操作

PO类

package po;

/**
 * Author:lp on 2019/12/19 10:25
 * Param:
 * return:
 * Description:po类，数据库映射的对象
 */
public class User {
    private long user_id;
    private String user_name;
    private String sex;
    private int age;

    public long getUser_id() {
        return user_id;
    }

    public String getSex() {
        return sex;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public void setUser_id(long user_id) {
        this.user_id = user_id;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return user_id + "\t" + user_name + "\t" + sex;
    }
}

mybatais映射文件

<?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="test">
    <!-- 在映射文件中配置很多sql语句，起到sql语句环境隔离 -->
    <!-- 通过select执行数据库查询
    id:标识映射文件中的sql，称为statement的id将sql语句封装到mappedStatement对象中，所以将id称为statement的id
    parameterType:指定输入参数的类型
    #{}标示一个占位符,
     -->
    <select id="findUserById" parameterType="int" resultType="po.User">
        SELECT * FROM  mybatais  WHERE user_id=#{value}
    </select>

    <!-- 
    resultType：指定就是单条记录所映射的java对象类型
    ${}:表示拼接sql串，将接收到参数的内容不加任何修饰拼接在sql中。
    使用${}拼接sql，引起 sql注入
    ${value}：接收输入参数的内容，如果传入类型是简单类型，${}中只能使用value
     -->
    <select id="findUserByName" parameterType="java.lang.String" resultType="po.User">
        SELECT * FROM mybatais WHERE user_name LIKE '%${value}%'
    </select>
    <!--useGeneratedKeys="true"主键自增
        keyProperty="user_id" 自增的主键-->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="user_id" parameterType="po.User">
           INSERT into mybatais(user_name, sex, age) values (#{user_name},#{sex},#{age})

    </insert>

    <update id="updateUser" parameterType="po.User">
        UPDATE mybatais SET user_name=#{user_name},sex=#{sex},age=#{age} where user_id=#{user_id}
    </update>


    <delete id="deleteUser" parameterType="java.lang.String">
        delete from mybatais where user_name=#{user_name}
    </delete>


</mapper>

测试函数

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.Test;
import po.User;

import java.io.InputStream;
import java.util.List;

/**
 * Create by lp on 2019/12/19
 */
public class MybatisTest1 {
    @Test
    public void findById() throws Exception {
        //通过id来查找
        String url = "mybatisConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(url);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("test.findUserById", 1);
        System.out.println(user);
        sqlSession.close();


    }

    @Test
    public void findByLikeName() throws Exception {
        //近似查找
        String url = "mybatisConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(url);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> userlist = sqlSession.selectList("test.findUserByName", "lipeng");
        System.out.println(userlist);
        sqlSession.close();
    }

    @Test
    public void insertUser() throws Exception {
        //插入数据
        String url = "mybatisConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(url);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUser_name("yueyuep");
        user.setSex("man");
        user.setAge(25);
        sqlSession.insert("test.insertUser", user);
        //事务操作
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser() throws Exception {
        //插入数据
        String url = "mybatisConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(url);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUser_id(1);
        user.setUser_name("www.yuyue.group");
        user.setSex("woman");
        user.setAge(1);
        sqlSession.update("test.updateUser", user);
        //事务操作
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser() throws Exception {
        //插入数据
        String url = "mybatisConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(url);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        sqlSession.delete("test.deleteUser", "www.yuyue.group");
        //事务操作
        sqlSession.commit();
        sqlSession.close();
    }
}