JAVA mybatis 实例以及防SQL注入方法

src/main/resources/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>
    <!--使用dev环境-->
    <environments default="dev">
        <!--dev环境-->
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <!--使用连接池中的数据源
            url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=2
            -->
            <dataSource type="POOLED">
<!--                <property name="driver" value="com.mysql.jdbc.Driver"/>-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;serverTimezone=Asia/Shanghai"/>
                <property name="username" value="root"/>
                <property name="password" value="2"/>
            </dataSource>
        </environment>

    </environments>
    <!-- 扫描映射文件 -->
    <mappers>
        <mapper resource="com/by/dao/UserDao.xml"/>
    </mappers>

</configuration>

src/main/java/com/by/dao/UserDao.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">
<!--namespace：唯一，接口全类名-->
<mapper namespace="com.by.dao.UserDao">
    <!--
        id：和接口方法名保持一致
        resultType：和接口返回类型保持一致
    -->
    <select id="findAll" resultType="com.by.pojo.User">
        select * from user
    </select>

    <select id="UserGetByid" parameterType="java.lang.Integer" resultType="com.by.pojo.User">
        select * from user where id=#{id}
    </select>

    <select id="UserGet" resultType="com.by.pojo.User">
        <!--select * from user where id=#{arg0} and username=#{arg1}--><!-- arg0 arg1-->
        select * from user where id=#{param1} and username=#{param2}
    </select>

    <select id="getUser3" resultType="com.by.pojo.User">
        select * from user where id=#{id} and username=#{username}
    </select>

    <select id="getUser4" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
        select * from user where id=#{id} and username=#{username}
    </select>

    <select id="getUser5" parameterType="java.util.Map" resultType="com.by.pojo.User">
        select * from user where id=#{id} and username=#{username}
    </select>

    <select id="getUserByUserName" parameterType="java.lang.String" resultType="com.by.pojo.User">
        <!-- select * from user where username like '%${value}%' -->
        select * from user where username like concat('%',#{value},'%')
    </select>

    <select id="login" parameterType="com.by.pojo.User" resultType="com.by.pojo.User">
        <!-- select * from user where username='${username}' and password='${password}' -->

        select * from user where
        <if test="username != null and username != ''">
            username= '${username}'
        </if>
        <if test="password != null and password != ''">
            and password='${Password}'
        </if>


    </select>

    <delete id="deleteUserByUserId" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>

    <update id="updateUserById" parameterType="com.by.pojo.User">
        UPDATE user
        SET username=#{username}, password=#{password}, birthday=#{birthday}, sex=#{sex}, address=#{address}
        WHERE id=#{id}
    </update>

    <insert id="addUser" useGeneratedKeys="true" keyProperty="id" parameterType="com.by.pojo.User">
        <!--
            逐渐回填(返回自增id)：插入记录后返回自增的id到参数
            keyProperty="id"：回填到实体类的哪个属性
            order="AFTER"：先执行插入，再主键回填
            resultType="java.lang.Integer"：主键的类型

        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>
        -->
        INSERT INTO user(username,password,birthday,sex,address) VALUES(#{username},#{password},#{birthday},#{sex},#{address})
    </insert>








</mapper>

src/main/java/com/by/dao/UserDao.java

package com.by.dao;

import com.by.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface UserDao {
    List<User> findAll();

    User UserGetByid(Integer id);

    User UserGet(Integer id, String username);

    User getUser3(@Param("id") Integer id, @Param("username") String username);

    User getUser4(User user);

    User getUser5(Map<String, Object> map);

    List<User> getUserByUserName(String name);

    User login(User userInfo);

    void deleteUserByUserId(Integer id);

    void updateUserById(User user);

    void addUser(User user);



}

src/test/java/com.by.text/MyBatisTest.java

package com.by.test;

import com.by.dao.UserDao;
import com.by.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.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyBatisTest {
    private SqlSession sqlSession;
    private InputStream inputStream;

    @Before
    public void init(){
        try {
            //加载配置文件
            String resource = "mybatis-config.xml";
            inputStream = Resources.getResourceAsStream(resource);
            //创建sqlSessionFactory
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            //获得数据的会话实例
            sqlSession = sessionFactory.openSession();
        }catch (Exception e){System.out.println(e.toString());}
    }

    @After
    public void close(){
        try {
            sqlSession.close();
            inputStream.close();
        }catch (Exception e){System.out.println(e.toString());}
    }

    @Test
    public void testFindAll() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        List<User> userList = userDao.findAll();
        for (User user : userList) {
            System.out.println(user);
        }
        System.out.println(userDao);
    }

    @Test
    public void testuser1() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User userList = userDao.UserGetByid(41);
        System.out.println(userList);
    }

    @Test
    public void testuser2() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User userList = userDao.UserGet(41,"张三丰");
        System.out.println(userList);
    }

    @Test
    public void testuser3() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User userList = userDao.getUser3(41,"张三丰");
        System.out.println(userList);
    }

    @Test
    public void testuser4() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        User userParam=new User();
        userParam.setId(41);
        userParam.setUsername("张三丰");

        User userList = userDao.getUser4(userParam);
        System.out.println(userList);
    }

    @Test
    public void testuser5() throws IOException {
        //返回接口的代理类
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Map<String,Object> mapx=new HashMap<>();
        mapx.put("id",43);
        mapx.put("username", "张三丰");

        User userList = userDao.getUser5(mapx);
        System.out.println(userList);
    }

    @Test
    public void testGetUserByUserName() throws IOException {
        UserDao userMapper = sqlSession.getMapper(UserDao.class);
        List<User> userList = userMapper.getUserByUserName("张");
        for (User user : userList) {
            System.out.println(user);
        }
    }

    @Test
    public void testLogin() throws IOException {
        UserDao userMapper = sqlSession.getMapper(UserDao.class);
        User userInfo = new User();
        userInfo.setUsername("张三丰' #");
        userInfo.setPassword("2222");
        User user = userMapper.login(userInfo);
        System.out.println(user);
    }


    @Test
    public void testDeleteUserById() throws IOException {
        UserDao userMapper = sqlSession.getMapper(UserDao.class);
        userMapper.deleteUserByUserId(43);
        sqlSession.commit();
    }

    @Test
    public void testUpdateUserById(){
        UserDao userMapper = sqlSession.getMapper(UserDao.class);
        User user = new User();
        user.setUsername("郭襄");
        user.setPassword("111");
        user.setBirthday(new Date());
        user.setSex("女");
        user.setAddress("峨眉山");
        user.setId(42);
        userMapper.updateUserById(user);

        sqlSession.commit();
    }

    @Test
    public void testAddUser(){
        UserDao userMapper = sqlSession.getMapper(UserDao.class);

        User user = new User();
        user.setUsername("周芷若");
        user.setPassword("111");
        user.setBirthday(new Date());
        user.setSex("女");
        user.setAddress("峨眉山");

        userMapper.addUser(user);
        System.out.println("返回自增id："+user.getId());
        sqlSession.commit();
    }


}