数据库:注意数据库支持utf8mb4
我的客户端版本
/*
Navicat Premium Data Transfer
Source Server : mysql
Source Server Type : MySQL
Source Server Version : 50717
Source Host : localhost:3306
Source Schema : java93_s3
Target Server Type : MySQL
Target Server Version : 50717
File Encoding : 65001
Date: 26/05/2020 17:15:48
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_book
-- ----------------------------
DROP TABLE IF EXISTS `t_book`;
CREATE TABLE `t_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`author` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`book_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`price` float NULL DEFAULT NULL,
`kind` int(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `UK_occ40ug47u3fyga6l0xpadbmp`(`name`) USING BTREE,
INDEX `FK_kind`(`kind`) USING BTREE,
CONSTRAINT `FK_kind` FOREIGN KEY (`kind`) REFERENCES `kind` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 23 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_book
-- ----------------------------
INSERT INTO `t_book` VALUES (1, 'author0', 'desc0', 'name0', 0, 1);
INSERT INTO `t_book` VALUES (2, 'author1', 'desc1', 'name1', 10, 2);
INSERT INTO `t_book` VALUES (3, 'author2', 'aaa', 'name2', 20, 3);
INSERT INTO `t_book` VALUES (4, 'author3', 'apple', 'name3', 30, 3);
INSERT INTO `t_book` VALUES (5, 'author4', 'acer', '唐僧', 40, 2);
INSERT INTO `t_book` VALUES (6, 'author5', 'desc5', '黄蓉', 50, 1);
INSERT INTO `t_book` VALUES (7, 'author6', 'desc6', '黄飞鸿', 60, 2);
INSERT INTO `t_book` VALUES (13, '田少军100', 'SQl', 'SQL', 100, 2);
INSERT INTO `t_book` VALUES (16, '程序员2', '程序员的小说2', '人月神话2', 100, 3);
INSERT INTO `t_book` VALUES (20, 'test10----', '程序员的小说3', 'test110---', 100, 3);
SET FOREIGN_KEY_CHECKS = 1;
依赖
<?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.hr</groupId>
<artifactId>mybatis-4</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>mybatis-4 Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<slf4j.version>1.7.25</slf4j.version>
<log4j.version>1.2.17</log4j.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!--mybatis核心依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.4</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<!--日志,MyBatis通过日志输入sql相关信息-->
<!--log4j日志包 -->
<!--日志管理核心包-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
<!--<scope>test</scope>-->
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<!--dbcp2-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.5.0</version>
</dependency>
</dependencies>
<build>
<finalName>mybatis-4</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>
实体
package com.hr.entity;
public class Book {
private Integer id;
private String author;
//book_desc
private String bookDesc;
private String name;
private double price;
private Integer kind;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getBookDesc() {
return bookDesc;
}
public void setBookDesc(String bookDesc) {
this.bookDesc = bookDesc;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Integer getKind() {
return kind;
}
public void setKind(Integer kind) {
this.kind = kind;
}
}
配置文件
<?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"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/java93_s3?useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="52Java"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--注解:注册-->
<mapper class="com.hr.mapper.BookMapper"/>
</mappers>
</configuration>
log4j.properties
#Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
#log4j.logger.org.mybatis.example.BlogMapper=TRACE
log4j.logger.com.hr.mapper.BookMapper=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
Mapper接口
package com.hr.mapper;
import com.hr.entity.Book;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import java.util.List;
public interface BookMapper {
//添加
@Insert("INSERT INTO `t_book`(`author`, `book_desc`, `name`, `price`, `kind`) VALUES ( #{author}, #{bookDesc}, #{name}, #{price}, #{kind})")
int add(Book book);
//修改
@Update("UPDATE `t_book` SET `author` = #{author}, `book_desc` = #{bookDesc}, `name` = #{name}, `price` = #{price}, `kind` = #{kind} WHERE `id` = #{id}")
int update(Book book);
//删除
@Delete("delete from t_book where id = #{id}")
int del(Integer id);
//查单个
@Results(id = "findOne",value = {
@Result(property = "id",column = "id",id = true),
@Result(property = "author",column = "author"),
@Result(property = "name",column = "name"),
@Result(property = "bookDesc",column = "book_desc"),
@Result(property = "price",column = "price"),
@Result(property = "kind",column = "kind")
})
@Select("select * from t_book where id = #{id}")
Book findOne(Integer id);
//查所有
@Results(id = "findAll",value = {
@Result(property = "id",column = "id",id = true),
@Result(property = "author",column = "author"),
@Result(property = "name",column = "name"),
@Result(property = "bookDesc",column = "book_desc"),
@Result(property = "price",column = "price"),
@Result(property = "kind",column = "kind")
})
@Select("select * from t_book")
List<Book> findAll();
//修改(带判断)
@UpdateProvider(type = BookProvider.class,method = "update")
int update2(Book book);
class BookProvider{
public static String update(Book book){
return new SQL(){{
UPDATE("t_book");
if(book.getName()!=null){
SET("name=#{name}");
}
if(book.getAuthor()!=null){
SET("author=#{author}");
}
WHERE("id = #{id}");
}
}.toString();
}
// 查询
public static String findByLike(String name){
StringBuffer sb = new StringBuffer("select * from t_book where 1=1");
// if(name!=null && !"".equals(name)){
// sb.append(" and name like '%${name}%'");
// }
if(name!=null && !"".equals(name)){
sb.append(" and name like \"%\"#{name}\"%\"");
}
return sb.toString();
}
// 查询
public static String findByLike2( String name, String orderByColumn){
return new SQL(){{
SELECT("*");
FROM("t_book");
WHERE("name like \"%\"#{name}\"%\"");
ORDER_BY(orderByColumn);
}}.toString();
}
// 查询3
public static String findByLike3(@Param("orderByColumn") String orderByColumn){
return new SQL(){{
SELECT("*");
FROM("t_book");
WHERE("name like \"%\"#{name}\"%\"");
ORDER_BY(orderByColumn);
}}.toString();
}
// 查询4
public static String findByLike4(String name,String orderByColumn){
return new SQL(){{
SELECT("*");
FROM("t_book");
WHERE("name like \"%\"#{arg0}\"%\"");
ORDER_BY("#{param2}");
}}.toString();
}
}
//模糊查
@SelectProvider(type = BookProvider.class,method = "findByLike")
List<Book> findByName(String name);
//模糊查
@SelectProvider(type = BookProvider.class,method = "findByLike2")
List<Book> findByName2( @Param("name") String name, @Param("orderByColumn") String orderByColumn);
//模糊查
@SelectProvider(type = BookProvider.class,method = "findByLike3")
List<Book> findByName3( @Param("name") String name, @Param("orderByColumn") String orderByColumn);
//模糊查
@SelectProvider(type = BookProvider.class,method = "findByLike4")
List<Book> findByName4(String name,String orderByColumn);
}
测试类
package com.hr.test;
import com.hr.entity.Book;
import com.hr.mapper.BookMapper;
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.Reader;
import java.util.List;
/**
* @ClassName TestBook
* @Description: TODO
* @Author 汤永红
* @Date 2020/5/26 0026
* @Version V1.0
**/
public class TestBook {
SqlSessionFactory factory;//工厂 和数据对话
SqlSession sqlSession;//会话
BookMapper mapper;
@Before
public void init() throws Exception{
//1.加载配置文件
String xml = "mybatis.xml";
Reader reader = Resources.getResourceAsReader(xml);
factory = new SqlSessionFactoryBuilder().build(reader);
//2.创建会话
sqlSession=factory.openSession();
mapper = sqlSession.getMapper(BookMapper.class);
}
//
@Test
public void findAll(){
List<Book> all = mapper.findAll();
}
@Test
public void findOne(){
Book book= mapper.findOne(1);
System.out.println(book.getName());
}
@Test
public void add(){
Book book = new Book();
book.setName("test110");
book.setAuthor("test110");
book.setBookDesc("test110");
book.setPrice(10);
book.setKind(1);
mapper.add(book);
System.out.println(book.getName());
}
@Test
public void update(){
Book book = mapper.findOne(22);
//问:反正是全部set了,没有判断
book.setName("test110---");
book.setAuthor("test110---");
book.setBookDesc("test110---");
mapper.update(book);
System.out.println(book.getName());
}
@Test
public void update2(){
Book book = new Book();
book.setId(20);
//问:反正是全部set了,没有判断
book.setName("test110---");
//book.setAuthor("test10----");
// book.setAuthor("test110---");
// book.setBookDesc("test110---");
mapper.update2(book);
System.out.println(book.getName());
}
@Test
public void del(){
mapper.del(22);
}
@Test
public void findByLike(){
List<Book> books = mapper.findByName("黄");
}
@Test
public void findByLike2(){
List<Book> books = mapper.findByName2("黄","price");
}
@Test
public void findByLike3(){
List<Book> books = mapper.findByName3("黄","price");
}
@Test
public void findByLike4(){
List<Book> books = mapper.findByName4("黄","price");
}
@After
public void destroy(){
sqlSession.commit();
sqlSession.close();
}
}