学习基础开发技能_MyBatis

20240114，

1.反引号`：是为了区分mysql保留字与普通字符而引入的字符串

单引号'：sql用单引号来环绕文本值

20240115，

1.

create database mybatis;

/* use 数据库名 */
use mybatis;

create table `user` (
    `id` int(20) not null primary key,
    `name` varchar(20) default null comment '姓名',
    `pwd` varchar(20) default null comment '密码'
) engine=InnoDB charset=utf8; 

20240205，

0.@Test是加在方法上

1.pom.xml

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.37</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <!-- 静态资源导出问题 -->
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

2.ini.sql

create database mybatis;

/* use 数据库名 */
use mybatis;

create table `user` (
    `id` int(20) not null primary key,
    `name` varchar(20) default null comment '姓名',
    `pwd` varchar(20) default null comment '密码'
) engine=InnoDB charset=utf8; 

select * from `user`;

select * from `mybatis`.`user`;

insert into `user` (`id`, `name`, `pwd`) values
(1, 'zhangsan', '123456'),
(2, 'lisi', '123456'),
(3, '狂神', '123321');

3.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="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

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

4.MyBatisUtils.java

public class MyBatisUtil {
    private static final SqlSessionFactory sqlSessionFactory;

    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

5.User.java

public class User {
    private int id;
    private String name;
    private String pwd;

    public User() {
    }

    public User(int id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }

    // get set 省略
}

6.UserMapper.java

public interface UserMapper {
    public List<User> getAllUser();
}

7.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.kuang.mapper.UserMapper">
    <select id="getAllUser" resultType="com.kuang.pojo.User">
        select * from mybatis.user;
    </select>
</mapper>

8.MyTest.java\

public class MyTest {
    @Test
    public void testGetAllUser() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.getAllUser();
        if (users != null && users.size() > 0) {
            for (User user : users) {
                System.out.println(user);
            }
        }
    }
}

20240309，

1.UserMapper.java

public interface UserMapper {
    public User getUserById(int id);

    public int insertUser(User user);

    public int updateUser(User user);

    public int deleteUser(int id);

    public int updateUserByMap(Map<String, Object> map);

    public User getUserByMap(Map<String, Object> map);

    public List<User> getUserLike(Map<String, Object> map);
}

2.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.kuang.mapper.UserMapper">
    <select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
        select * from mybatis.user where id=#{id}
    </select>

    <insert id="insertUser" parameterType="com.kuang.pojo.User">
        insert into mybatis.user (id, name, pwd) values (#{id}, #{name}, #{pwd})
    </insert>

    <update id="updateUser" parameterType="com.kuang.pojo.User">
        update mybatis.user set name=#{name}, pwd=#{pwd} where id=#{id}
    </update>

    <delete id="deleteUser" parameterType="int">
        delete from mybatis.user where id=#{id}
    </delete>

    <update id="updateUserByMap" parameterType="map">
        update mybatis.user set name=#{userName} where id=#{userId}
    </update>

    <select id="getUserByMap" parameterType="map" resultType="com.kuang.pojo.User">
        select * from mybatis.user where name=#{userName} and pwd=#{userPwd}
    </select>

    <!-- 练习点：like；typeAlias -->
    <select id="getUserLike" parameterType="map" resultType="User">
        <!--如下会报错：select * from mybatis.user where name like '#{userName}'-->
        select * from mybatis.user where name like #{userName}
    </select>
</mapper>

3.MyTest.java

public class MyTest {
    @Test
    public void testGetUserById() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User userById = userMapper.getUserById(1);
        System.out.println(userById);
        sqlSession.close();
    }

    @Test
    public void testInsertUser() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int res = userMapper.insertUser(new User(4, "哈哈", "123123"));
        sqlSession.commit();
        if (res > 0) {
            System.out.println("插入成功");
        }
        sqlSession.close();
    }

    @Test
    public void testUpdateUser() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int res = userMapper.updateUser(new User(4, "嘿嘿", "123223"));
        sqlSession.commit();
        if (res > 0) {
            System.out.println("更新成功");
        }
        sqlSession.close();
    }

    @Test
    public void testDeleteUser() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        int res = userMapper.deleteUser(4);
        sqlSession.commit();
        if (res > 0) {
            System.out.println("删除成功");
        }
        sqlSession.close();
    }

    @Test
    public void testUpdateUserByMap() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("userName", "测试Map");
        map.put("userId", "4");
        int res = userMapper.updateUserByMap(map);
        sqlSession.commit();
        if (res > 0) {
            System.out.println("测试通过Map更新成功");
        }
        sqlSession.close();
    }

    @Test
    public void testGetUserByMap() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("userName", "zhangsan");
        map.put("userPwd", "123456");
        User userByMap = userMapper.getUserByMap(map);
        System.out.println(userByMap.toString());
        sqlSession.close();
    }

    @Test
    public void testGetUserLike() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<>();
        map.put("userName", "李%");
        List<User> userLike = userMapper.getUserLike(map);
        System.out.println(userLike.toString());
        sqlSession.close();
    }
}

4.dbconfig.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myybatis?useUnicode=true&characterEncoding=utf8&failOverReadOnly=false
username=root
password=root

5.

优先级：优先使用properties文件，然后再使用property子元素，会根据优先级去覆盖原先配置的属性
举例：dbconfig.properties里配置的url（是个错误的url），会优先使用，然后会再使用config.xml里的url（是正确的url），后使用的会覆盖先使用的，可以理解为后来者居上。最终使用了config.xml里正确的url，可以正常启动

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>
    <properties resource="dbconfig.properties"/>
    <typeAliases>
        <typeAlias type="com.kuang.pojo.User" alias="User"/>
    </typeAliases>
    <!-- 优先级：优先使用properties文件，然后再使用property子元素，会根据优先级去覆盖原先配置的属性 -->
    <!-- 举例：dbconfig.properties里配置的url（是个错误的url），会优先使用，然后会再使用config.xml里的url
     （是正确的url），后使用的会覆盖先使用的，可以理解为后来者居上。最终使用了config.xml里正确的url，可以正常启动-->
    <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}"/>
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=utf8&amp;failOverReadOnly=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/kuang/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

20230310，

1.UserMapper.java

public interface UserMapper {
    public User getUserById(int id);

    public List<User> getUserLimit(Map<String, Integer> map);

    public List<User> getUserByRowBounds();
}

2.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.kuang.mapper.UserMapper">
    <!-- 结果集映射 -->
    <resultMap id="userResultMap" type="User">
<!--        <result property="id" column="id"/>-->
<!--        <result property="name" column="name"/>-->
        <result property="password" column="pwd"/>
    </resultMap>
    <select id="getUserById" parameterType="int" resultMap="userResultMap">
        select * from mybatis.user where id=#{id}
    </select>

    <select id="getUserLimit" parameterType="map" resultMap="userResultMap">
        select * from mybatis.user limit #{startIndex}, #{pageSize}
    </select>
    
    <select id="getUserByRowBounds" resultMap="userResultMap">
        select * from mybatis.user
    </select>

</mapper>

3.log4j.properties

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码
#log4j.rootLogger=DEBUG,console,file
log4j.rootLogger=INFO,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Encoding = UTF-8
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.Encoding=UTF-8
log4j.appender.file.File=./log/kuang.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

4.MyTest.java

public class MyTest {
    static final Logger logger = Logger.getLogger(MyTest.class);

    @Test
    public void testGetUserById() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User userById = userMapper.getUserById(1);
        logger.info("info:user:" + userById.toString());
        logger.debug("debug:user:" + userById.toString());
        logger.error("error:user:" + userById.toString());
        System.out.println(userById);
        sqlSession.close();
    }

    @Test
    public void testGetUserLimit() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String, Integer> map = new HashMap<>();
//        map.put("startIndex", 0);
        map.put("startIndex", 3);
        map.put("pageSize", 2);
        List<User> users = userMapper.getUserLimit(map);
        if (users.size() > 0) {
            for (User user : users) {
                logger.info(user);
            }
        }
        sqlSession.close();
    }

    @Test
    public void testGetUserByRowBounds() {
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        RowBounds rowBounds = new RowBounds(3, 2);
        List<User> users = sqlSession.selectList("com.kuang.mapper.UserMapper.getUserByRowBounds",
                null, rowBounds);
        if (users.size() > 0) {
            for (User user : users) {
                logger.info(user);
            }
        }
        sqlSession.close();
    }
}

5.pom.xml

<dependencies>
        <!-- https://mvnrepository.com/artifact/log4j/log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

20240311,