文章目录
user.sql
create table user
(
user_id int auto_increment
primary key,
user_name varchar(50) not null,
password_hash varchar(255) not null,
email varchar(100) null,
phone_number varchar(20) null,
first_name varchar(50) null,
last_name varchar(50) null,
birth_date date null,
gender char null,
address varchar(255) null,
city varchar(50) null,
state varchar(50) null,
country varchar(50) null,
postal_code varchar(20) null,
is_active tinyint(1) default 1 null,
created_at timestamp default CURRENT_TIMESTAMP null,
updated_at timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
constraint email
unique (email),
constraint user_name
unique (user_name)
);
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.aistat</groupId>
<artifactId>mybatis_tech</artifactId>
<version>1.0-SNAPSHOT</version>
<!--打包类型-->
<packaging>jar</packaging>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<!--所有依赖-->
<dependencies>
<!-- MySQL驱动-->
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.31</version>
</dependency>
<!-- mybatis依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.15</version>
</dependency>
<!--测试环境-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!--Lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.14.8</version>
<scope>provided</scope>
</dependency>
<!-- logback日志 slf规范-->
<!-- 配置文件也是在resouce目录下,且只能以logback.xml/logback-test.xml命名-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.11</version>
</dependency>
</dependencies>
</project>
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>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.aistart.tech.pojo"/>
</typeAliases>
<!-- <typeAliases>-->
<!-- <typeAlias alias="Products" type="com.aistart.tech.pojo.Products"/>-->
<!-- </typeAliases>-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatisdb"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="poolMaximumActiveConnections" value="1"/>
</dataSource>
</environment>
<environment id="testdevelopment">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.aistart.tech.mapper.ProductsMapper"/>
<mapper resource="com/aistart/tech/mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration >
<!--0. 日志格式和颜色渲染 -->
<!-- 彩色日志依赖的渲染类 -->
<conversionRule conversionWord="clr" converterClass="org.springframework.boot.logging.logback.ColorConverter" />
<conversionRule conversionWord="wex" converterClass="org.springframework.boot.logging.logback.WhitespaceThrowableProxyConverter" />
<conversionRule conversionWord="wEx" converterClass="org.springframework.boot.logging.logback.ExtendedWhitespaceThrowableProxyConverter" />
<!-- 彩色日志格式 -->
<property name="CONSOLE_LOG_PATTERN" value="${CONSOLE_LOG_PATTERN:-%clr(%d{yyyy-MM-dd HH:mm:ss.SSS}){faint} %clr(${LOG_LEVEL_PATTERN:-%5p}) %clr(${PID:- }){magenta} %clr(---){faint} %clr([%15.15t]){faint} %clr(%-40.40logger{39}){cyan} %clr(:){faint} %m%n${LOG_EXCEPTION_CONVERSION_WORD:-%wEx}}"/>
<!--
CONSOLE ：表示当前的日志信息是可以输出到控制台的。
-->
<appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
</encoder>
</appender>
<!--触动到定义部分,就输出对应级别日志-->
<logger name="org.apache.ibatis" level="TRACE"/>
<logger name ="java.sql.Connection" level="DEBUG"/>
<logger name="java.sql.PreparedStatement" level="DEBUG"/>
<logger name="java.sql.Statement" level="DEBUG"/>
<logger name="com.aistart.tech.mapper" level="DEBUG"/>
<!--
level:用来设置打印级别，大小写无关：TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
， 默认debug
INFO类似于sout
<root>可以包含零个或多个<appender-ref>元素，标识这个输出位置将会被本日志级别控制。
-->
<root level="DEBUG">
<appender-ref ref="Console"/>
</root>
</configuration>
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.aistart.tech.mapper.UserMapper">
<select id="findOne" resultType="User" parameterType="User">
select * from user
<trim prefix="where" prefixOverrides="and | or">
<if test="userName != null">
user_name = #{userName}
</if>
<!-- if标签之间是相互独立的 -->
<if test="email != null">
AND email = #{email}
</if>
</trim>
<!-- 只会执行一个-->
<!-- <choose>
<when test="userName != null">
AND user.user_name like #{userName}
</when>
<when test="email != null ">
AND email like #{email}
</when>
<otherwise>
AND 1 = 1
</otherwise>
</choose>-->
<!-- 默认帮我们拼接了where,并智能识别where or and是否应该写出来
<where>
<!– test就是条件表达if的() –>
<if test="userName != null">
user_name = #{userName}
</if>
<!– if标签之间是相互独立的 –>
<if test="email != null">
AND email = #{email}
</if>
</where>-->
limit 1;
</select>
<select id="findOneLikeName" resultType="User">
<bind name="pattern" value="'%' + name + '%'" />
SELECT * FROM user
WHERE user.user_name LIKE #{pattern}
</select>
<insert id="insertList" >
insert into user (user_name,password_hash,email)
value
<foreach collection="userList" item="user" separator=",">
(#{user.userName},#{user.passwordHash},#{user.email})
</foreach>
</insert>
</mapper>
UserMapper.java
package com.aistart.tech.mapper;
import com.aistart.tech.pojo.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface UserMapper {
/**
*
* 通过条件集合返回特定查询的结果user
*
* @Param user就是所有条件的集合(id name .....)
* @return 根据动态的组合条件产生的结果
*/
//也就说${}是非预编译的statement(字符串拼接,更直接但不安全),而#{}是praperStatemen预编译语句(留占位符?)
public User findOne(User user);
// @Select("select * from user where user_name like concat('%',#{name},'%') limit 1")
public User findOneLikeName(String name);
public int insertList(@Param("userList") List<User> userList);
}
UserMapperTest.java
package com.aistart.tech.mapper;
import com.aistart.tech.pojo.User;
import com.aistart.tech.utils.DButil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;
import static org.junit.Assert.*;
public class UserMapperTest {
SqlSession sqlSession = DButil.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
@Test
public void findOneTest() {
User user = new User();
//里面有什么值就是传递什么参数(条件)
// user.setUserName("小明");
user.setEmail("qq.com");
User one = mapper.findOne(user);
System.out.println(one);
sqlSession.close();;
}
@Test
public void findOneLikeName() {
User one = mapper.findOneLikeName("王");
System.out.println(one);
}
@Test
public void insertList() {
ArrayList<User> users = new ArrayList<>();
users.add(new User("lxy","123456","123456@qq.com"));
users.add(new User("yc","hhxx,ttxs","hhxx@qq.com"));
int rows = mapper.insertList(users);
System.out.println(rows);
sqlSession.commit();
sqlSession.close();
}
}
本文含有隐藏内容，请 开通VIP 后查看