Mysql文件配置
/*
Navicat Premium Data Transfer
Source Server : Mybatis
Source Server Type : MySQL
Source Server Version : 50528
Source Host : localhost:3306
Source Schema : mybatis
Target Server Type : MySQL
Target Server Version : 50528
File Encoding : 65001
Date: 14/03/2022 18:09:01
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`pwd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', '123');
INSERT INTO `user` VALUES (2, '李四', '236');
INSERT INTO `user` VALUES (3, '王五', '456');
INSERT INTO `user` VALUES (5, '法外狂徒', '478');
SET FOREIGN_KEY_CHECKS = 1;
配置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.wfy</groupId>
<artifactId>MybatisDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</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>true</filtering>
</resource>
</resources>
</build>
</project>
创建数据库连接信息配置文件 db.properties
mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&\
characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=root
创建MyBatis核心配置文件 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="db.properties"/>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/wfy/mapper/CustomerMapper.xml"/>
</mappers>
</configuration>
创建pojo实体
package com.wfy.pojo;
public class Customer {
private int id ;
private String username;
private String jobs ;
private String phone;
public Customer(){
}
public Customer(int id, String username, String jobs, String phone) {
this.id = id;
this.username = username;
this.jobs = jobs;
this.phone = phone;
}
public int getId() {
return id;
}
public void setId(int 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 "Customer{" +
"id=" + id +
", username='" + username + '\'' +
", jobs='" + jobs + '\'' +
", phone='" + phone + '\'' +
'}';
}
}
创建工具类
package com.wfy.utils;
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 java.io.Console;
import java.io.InputStream;
public class MyBatisUtils {
static SqlSessionFactory factory=null;
static {
String configFile= "mybatis-config.xml";
try {
InputStream stream= Resources.getResourceAsStream(configFile);
factory = new SqlSessionFactoryBuilder().build(stream);
}
catch (Exception e)
{
System.out.println(e);
}
}
public static SqlSession getSession()
{
return factory.openSession();
}
}
创建映射文件CustomerMapper.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.wfy.mapper.CustomerMapper">
<select id="findByNameAndJob" resultType="com.wfy.pojo.Customer" >
select * from customer
<where>
<if test=" username !=null and username !=''">
and username like concat ('%',#{username},'%')
</if>
<if test=" jobs !=null and jobs !=''">
and jobs =#{jobs}
</if>
</where>
</select>
<select id="findByNameOrJob" resultType="com.wfy.pojo.Customer">
select * from customer
<where>
<choose>
<when test="username != null and username !=''">
and username like concat ('%',#{username},'%')
</when>
<when test="jobs !=null and jobs !=''">
and jobs =#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</where>
</select>
<select id="findCustomerByNameAndJob" resultType="com.wfy.pojo.Customer">
select * from customer
<trim prefix="where" prefixOverrides="and">
<if test="username !=null and username != ''">
and username like concat('%',#{username},'%')
</if>
<if test=" jobs !=null and jobs !=''">
and jobs =#{jobs}
</if>
</trim>
</select>
<update id="updateCustomerBySet" parameterType="com.wfy.pojo.Customer" >
update customer
<set>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="jobs!=null and jobs!=''">
jobs=#{jobs},
</if>
<if test="phone!=null and phone!=''">
phone=#{phone},
</if>
</set>
where id=#{id}
</update>
</mapper>
创建接口CustomerMapper
package com.wfy.mapper;
import com.wfy.pojo.Customer;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CustomerMapper {
List<Customer> findByNameAndJob(@Param("username") String username , @Param("jobs") String jobs) ;
List<Customer> findByNameOrJob( @Param("username") String username , @Param("jobs") String jobs);
List<Customer> findCustomerByNameAndJob(@Param("username") String username,@Param("jobs") String jobs);
int updateCustomerBySet(Customer customer);
}
创建测试类
package Test;
import com.wfy.mapper.CustomerMapper;
import com.wfy.pojo.Customer;
import com.wfy.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CustomerTest {
@Test
public void TestfindByNameAndJob( ){
//获取连接
SqlSession sqlSession = MyBatisUtils.getSession();
CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
List<Customer> customers= (List<Customer>) mapper.findByNameAndJob("张三","教师");
for(Customer customer:customers)
System.out.println(customer);
sqlSession.close();
}
@Test
public void findByNameOrJobTest(){
//获取连接
SqlSession sqlSession = MyBatisUtils.getSession();
CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
List<Customer> customers =mapper.findByNameOrJob("WFY","CEO");
for (Customer customer : customers) {
System.out.println(customer);
}
sqlSession.close();
}
@Test
public void findCustomerByNameAndJob(){
SqlSession sqlSession = MyBatisUtils.getSession();
CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
List<Customer> customerList = mapper.findCustomerByNameAndJob("绫小路清隆", "学生");
for (Customer customer : customerList) {
System.out.println(customer);
}
sqlSession.close();
}
@Test
public void updateCustomerByTest(){
SqlSession sqlSession = MyBatisUtils.getSession();
CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
int rows = mapper.updateCustomerBySet(new Customer(1,"法外狂徒","律师","12345678"));
if(rows>0){
System.out.println("成功修改"+rows+"条数据");
}else {
System.out.println("执行修改操作失败");
}
sqlSession.commit();
sqlSession.close();
}
}
MyBatis创建第二个项目的相关教程结束。
