MyBatis创建第二个项目 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();
}
}
