导读
1.简介
2.MyBatis增删改查（包括模糊查询）
3.动态标签
简介
MyBatis是一个轻量级持久层ORM框架，可以完成表字段与实体类中属性之间的映射
MyBatis增删改查（包括模糊查询）
配置文件Configuration.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!--
Copyright 2009-2012 the original author or authors.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<!-- useGeneratedKeys是否使用数据库自带主键 -->
<setting name="useGeneratedKeys" value="true"/>
<!-- useColumnLabel是否允许属于别名 -->
<setting name="useColumnLabel" value="true"/>
</settings>
<!-- 给类起一个别名 -->
<typeAliases>
<typeAlias alias="Book" type="com.hala.entity.Book"/>
</typeAliases>
<environments default="development">
<environment id="development"><!-- 开发环境 -->
<transactionManager type="JDBC"><!-- 使用JDBC事务 -->
<property name="" value=""/>
</transactionManager>
<dataSource type="UNPOOLED"><!-- 关于数据库链接的配置 -->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/message"/>
<property name="username" value="hala"/>
<property name="password" value="hala"/>
</dataSource>
</environment>
</environments>
<!-- 添加映射文件的路径 -->
<mappers>
<mapper resource="com/hala/mapper/Book.xml"/>
</mappers>
</configuration>
Book.java
package com.hala.entity;
public class Book {
private int bookId;
private String bookName;
private Double price;
private String author;
public Book() {
super();
}
public Book(String bookName, Double price, String author) {
super();
this.bookName = bookName;
this.price = price;
this.author = author;
}
public Book(int bookId, String bookName, Double price, String author) {
super();
this.bookId = bookId;
this.bookName = bookName;
this.price = price;
this.author = author;
}
public int getBookId() {
return bookId;
}
public void setBookId(int bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
@Override
public String toString() {
return "Book [bookId=" + bookId + ", bookName=" + bookName + ", price=" + price + ", author=" + author + "]";
}
}
Book.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--
Copyright 2009-2012 the original author or authors.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Book">
<!-- type指定类一般是要带有包和类名，但如果在配置文件中添加了别名，使用别名就好 -->
<resultMap type="Book" id="BookResult">
<!-- column对应数据库中表的字段 property对应实体类的属性-->
<id column="book_id" jdbcType="INTEGER" property="bookId"/>
<result column="book_name" jdbcType="VARCHAR" property="bookName"/>
<result column="price" jdbcType="DOUBLE" property="price"/>
<result column="author" jdbcType="VARCHAR" property="author"/>
</resultMap>
<!-- +++++++++++++++++++++++++++++++查询+++++++++++++++++++++++++++++++++++++++++ -->
<!-- parameterType是指查询条件的类型，这里id是int类型 -->
<!-- resultMap要与上边<resultMap>中id保持一致 -->
<select id="queryOne" parameterType="int" resultMap="BookResult">
SELECT * FROM my_book WHERE book_id = #{bookId}
</select>
<select id="queryMany" resultMap="BookResult">
SELECT * FROM my_book
</select>
<select id="queryByPrice" parameterType="double" resultMap="BookResult">
SELECT * FROM my_book WHERE price BETWEEN #{price1} AND #{price2}
</select>
<select id="queryNameByAuthor" parameterType="String" resultType="String">
SELECT book_name FROM my_book WHERE author LIKE concat('%',#{author},'%')
</select>
<!-- +++++++++++++++++++++++++++++++插入+++++++++++++++++++++++++++++++++++++++++ -->
<!-- useGeneratedKeys是否使用数据库自带主键 -->
<insert id="insert" parameterType="Book" useGeneratedKeys="true">
INSERT INTO my_book
(
book_name,
price,
author
)
VALUES
(
<!-- 这里要和属性保持一致，原理是调用了get方法 -->
#{bookName},
#{price},
#{author}
)
</insert>
<!-- +++++++++++++++++++++++++++++++修改+++++++++++++++++++++++++++++++++++++++++ -->
<update id="update" parameterType="Book">
UPDATE my_book SET
price=#{price}
WHERE
book_name = #{bookName}
</update>
<!-- +++++++++++++++++++++++++++++++删除+++++++++++++++++++++++++++++++++++++++++ -->
<delete id="delete" parameterType="int">
DELETE FROM my_book WHERE book_id = #{bookId}
</delete>
</mapper>
BookTest.java
package com.hala.test;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import com.hala.entity.Book;
public class BookTest {
private SqlSession ss=null;
@BeforeEach
public void before() throws IOException {
//Mybatis中操作数据使用sqlSession对象
//由sqlSessionFactory生成
//1.解析配置文件,并生成字符流
Reader reader=Resources.getResourceAsReader("Configuration.xml");
//2.根据文件配置信息生成SqlSessionFactory
SqlSessionFactory ssf=
new SqlSessionFactoryBuilder().build(reader);
//3.根据SqlSessionFactory获取SqlSession
ss=ssf.openSession();
//设置自动提交事务
//ss=ssf.openSession(true);
}
@AfterEach
public void after() {
ss.close();
}
//按照id查询单个（这里根据什么查询是在Book.xml<select>中规定的）
// @Test
public void query() {
//Book->Book.xml中namespace query-><select id>
Book book=ss.selectOne("Book.queryOne", 2);
System.out.println(book);
}
//查询所有
// @Test
public void queryMany() {
List<Book> books=ss.selectList("Book.queryMany");
for (Book book : books) {
System.out.println(book);
}
}
//根据价格条件查询
// @Test
public void queryByPrice() {
//MyBatis中不能同时传多个参数，要进行集合封装
Map<String,Double> map=new HashMap<>();
map.put("price1", 20.0);
map.put("price2",100.0);
List<Book> books=ss.selectList("Book.queryByPrice",map);
for (Book book : books) {
System.out.println(book);
}
}
//模糊查询(查询作者名字包含J的书名)
@Test
public void queryNameByAuthor() {
List<String> books=ss.selectList("Book.queryNameByAuthor","J");
for (String book : books) {
System.out.println(book);
}
}
//插入数据
// @Test
public void insert() {
Book book=new Book("Holmes",56.6,"Conan");
int count=ss.insert("Book.insert",book);
//对数据库改动要提交事务
ss.commit();
System.out.println(count);
}
//修改数据
// @Test
public void update() {
Book book=new Book();
book.setPrice(78.0);
book.setBookName("Holmes");
int count=ss.update("Book.update",book);
ss.commit();
System.out.println(count);
}
//删除
// @Test
public void delete() {
int count=ss.delete("Book.delete",2);
ss.commit();
System.out.println(count);
}
}
处理模糊查询的方法：
1.传入参数时，自己处理
2.如上用concat函数
3.用下边bind标签处理
提高代码复用率的方法
动态标签
1.<if>
2.<where>替代WHERE关键字，去除多余的and
3.<set>替代set关键字，并去除最后一个多余的逗号
4.<bind name="n" value="'%'+_parameter.trim()+'%'"/>
5.<trim prefixOverrides="and" suffixOverrides=",">
and name = #{name},
</trim>
6.<foreach>
7.<choose>
其他文件一致
Book.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--
Copyright 2009-2012 the original author or authors.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Book">
<!-- type指定类一般是要带有包和类名，但如果在配置文件中添加了别名，使用别名就好 -->
<resultMap type="Book" id="BookResult">
<!-- column对应数据库中表的字段 property对应实体类的属性-->
<id column="book_id" jdbcType="INTEGER" property="bookId"/>
<result column="book_name" jdbcType="VARCHAR" property="bookName"/>
<result column="price" jdbcType="DOUBLE" property="price"/>
<result column="author" jdbcType="VARCHAR" property="author"/>
</resultMap>
<select id="query" parameterType="map" resultType="Book">
SELECT * FROM my_book
<!-- where标签可以去掉第一个多余的and -->
<where>
<!-- 符合if标签内容就加入标签内语句，否则不加 -->
<if test="bookName!=null and !bookName.equals("")">
and book_name LIKE #{bookName}
</if><if test="price!=null">
and price= #{price}
</if><if test="author!=null">
and author LIKE #{author}
</if>
</where>
</select>
<!-- 下边的处理与where标签的处理一样 -->
<!--
<select id="query" parameterType="map" resultType="Book">
SELECT * FROM my_book WHERE 1=1
<if test="bookName!=null">
and book_name LIKE #{bookName}
</if><if test="price!=null">
and price= #{price}
</if><if test="author!=null">
and author=#{author}
</if>
</select>
-->
<update id="update" parameterType="map">
<!--set标签可以去掉最后多余的,-->
UPDATE my_book
<set>
<if test="bookName!=null and !bookName.equals("")">
book_name LIKE #{bookName},
</if><if test="price!=null">
price= #{price},
</if><if test="author!=null">
author=#{author},
</if>
</set>
</update>
</mapper>
BookTest.java
package com.hala.test;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import com.hala.entity.Book;
public class BookTest {
private SqlSession ss=null;
@BeforeEach
public void before() throws IOException {
//Mybatis中操作数据使用sqlSession对象
//由sqlSessionFactory生成
//1.解析配置文件,并生成字符流
Reader reader=Resources.getResourceAsReader("Configuration.xml");
//2.根据文件配置信息生成SqlSessionFactory
SqlSessionFactory ssf=
new SqlSessionFactoryBuilder().build(reader);
//3.根据SqlSessionFactory获取SqlSession
ss=ssf.openSession();
//设置自动提交事务
//ss=ssf.openSession(true);
}
@AfterEach
public void after() {
ss.close();
}
@Test
public void query() {
Map<String,Object> map=new HashMap<>();
map.put("author", "J%");
List<Book> books=ss.selectList("Book.query",map);
for (Book book : books) {
System.out.println(book);
}
}
}
bind标签
trim标签
foreach标签
choose标签
138
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
