spring基础mybatis增删改查

新建maven项目

 

导入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.3.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>4.3.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.6.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>aopalliance</groupId>
            <artifactId>aopalliance</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version> 1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.3</version>
        </dependency>
    </dependencies>

pom开启过滤

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

User.java

package com.shrimpking.pojo;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/11 9:55
 */
public class User
{
    private Integer id;
    private String username;
    private String jobs;
    private String phone;

    public User()
    {
    }

    public User(String username, String jobs, String phone)
    {
        this.username = username;
        this.jobs = jobs;
        this.phone = phone;
    }

    public Integer getId()
    {
        return id;
    }

    public void setId(Integer id)
    {
        this.id = id;
    }

    public String getUsername()
    {
        return username;
    }

    public void setUsername(String username)
    {
        this.username = username;
    }

    public String getJobs()
    {
        return jobs;
    }

    public void setJobs(String jobs)
    {
        this.jobs = jobs;
    }

    public String getPhone()
    {
        return phone;
    }

    public void setPhone(String phone)
    {
        this.phone = phone;
    }

    @Override
    public String toString()
    {
        return "User{" + "id=" + id + ", username='" + username + '\'' + ", jobs='" + jobs + '\'' + ", phone='" + phone + '\'' + '}';
    }
}

UserMapper.java

package com.shrimpking.mapper;

import com.shrimpking.pojo.User;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/11 10:01
 */
public interface UserMapper
{
    public User findUserById(Integer id);

    public List<User> findUserByName(String name);

    public int addUser(User user);

    public int updateUser(User user);

    public int deleteUser(int id);
}

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.shrimpking.mapper.UserMapper">

    <!-- 根据id查询   -->
    <select id="findUserById" parameterType="integer" resultType="com.shrimpking.pojo.User">
        select id,username,jobs,phone from a_user where id = #{id}
    </select>

    <!-- 根据用户名查询   -->
    <select id="findUserByName" parameterType="String" resultType="com.shrimpking.pojo.User">
        select id,username,jobs,phone from a_user where username like concat('%',#{username},'%')
    </select>

    <!--  插入  -->
    <insert id="addUser" parameterType="com.shrimpking.pojo.User">
        insert into a_user(username,jobs,phone)
        values (#{username},#{jobs},#{phone})
    </insert>

    <!-- 更新   -->
    <update id="updateUser" parameterType="com.shrimpking.pojo.User">
        update a_user set
        username = #{username},
        jobs = #{jobs},
        phone = #{phone}
        where id = #{id}
    </update>

    <!--  删除  -->
    <delete id="deleteUser" parameterType="integer">
        delete from a_user where id = #{id}
    </delete>

</mapper>

log4j.properties

log4j.rootLogger=ERROR,stdout
log4j.logger.com.shrimpking=DEBUG
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;serverTimeZone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="mysql123"/>
            </dataSource>
        </environment>
    </environments>

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

MyTest.java

import com.shrimpking.pojo.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 *
 * @Author : Shrimpking
 * @create 2023/7/11 10:10
 */
public class MyTest
{

    @Test
    public void test() throws Exception
    {
        //根据id查询
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("com.shrimpking.mapper.UserMapper.findUserById", 1);
        System.out.println(user.toString());
        sqlSession.close();
    }

    @Test
    public void test2() throws Exception
    {
        //根据用户名查询
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> users = sqlSession.selectList("com.shrimpking.mapper.UserMapper.findUserByName", "a");
        for (User user : users)
        {
            System.out.println(user);
        }

    }

    @Test
    public void test3() throws Exception
    {
        //插入
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("zhao");
        user.setJobs("worker");
        user.setPhone("187");
        int rows = sqlSession.insert("com.shrimpking.mapper.UserMapper.addUser", user);
        if(rows > 0)
        {
            System.out.println("成功添加" + rows + "条记录");
        }
        else
        {
            System.out.println("添加数据失败");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test4() throws  Exception
    {
        //更新
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("zhou");
        user.setJobs("teacher");
        user.setPhone("199");
        user.setId(4);
        int rows = sqlSession.update("com.shrimpking.mapper.UserMapper.updateUser", user);
        if(rows > 0)
        {
            System.out.println("成功修改了" + rows + "条数据");
        }
        else
        {
            System.out.println("修改数据失败");
        }
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test5() throws IOException
    {
        //删除
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        int rows = sqlSession.delete("com.shrimpking.mapper.UserMapper.deleteUser", 4);
        if (rows > 0)
        {
            System.out.println("成功删除了" + rows + "条数据");
        }
        else
        {
            System.out.println("删除数据失败");
        }
        sqlSession.commit();
        sqlSession.close();
    }
}