本文主要是介绍2022.1.17-18 Javaweb Mybatis快速入门完成增删改查,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
Mybatis
Mybatis是持久层框架,简化JDBC开发。
create database mybatis;
use mybatis;drop table if exists tb_user;create table tb_user(id int primary key auto_increment,username varchar(20),password varchar(20),gender char(1),addr varchar(30)
);INSERT INTO tb_user VALUES (1, 'zhangsan', '123', '男', '北京');
INSERT INTO tb_user VALUES (2, '李四', '234', '女', '天津');
INSERT INTO tb_user VALUES (3, '王五', '11', '男', '西安');
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>org.example</groupId><artifactId>mybatis-demo</artifactId><version>1.0-SNAPSHOT</version><dependencies><dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.5</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.46</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13</version><scope>test</scope></dependency><!-- 添加slf4j日志api --><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>1.7.20</version></dependency><!-- 添加logback-classic依赖 --><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-classic</artifactId><version>1.2.3</version></dependency><!-- 添加logback-core依赖 --><dependency><groupId>ch.qos.logback</groupId><artifactId>logback-core</artifactId><version>1.2.3</version></dependency></dependencies>
</project>
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//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"/><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/mybatis?allowPublicKeyRetrieval=true&useSSL=false"/><property name="username" value="root"/><property name="password" value="root"/></dataSource></environment></environments><mappers><!--加载sql映射文件--><mapper resource="UserMapper.xml"/></mappers>
</configuration>
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="test"><select id="selectAll" resultType="com.itest.pojo.User">select * from tb_user;</select>
</mapper>
User.java
package com.itest.pojo;/*** @Description: TODO* @author: scott* @date: 2022年01月17日 9:17*/
public class User {private Integer id;private String username;private String password;private String gender;private String addr;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getAddr() {return addr;}public void setAddr(String addr) {this.addr = addr;}@Overridepublic String toString() {return "User{" +"id=" + id +", username='" + username + '\'' +", password='" + password + '\'' +", gender='" + gender + '\'' +", addr='" + addr + '\'' +'}';}
}
MyBatisDemo.java
package com.itest.pojo;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.IOException;
import java.io.InputStream;
import java.util.List;/*** @Description: TODO* @author: scott* @date: 2022年01月17日 9:26*/
public class MyBatisDemo {public static void main(String[] args) throws IOException {//加载核心配置文件,获取sqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取sqlsession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//执行sqlList<User> users = sqlSession.selectList("test.selectAll");System.out.println(users);//释放资源sqlSession.close();}
}
Mapper代理开发
即可用 <package name="com.itest.mapper"/>
简化。
Mybatis核心文件配置
enviroment :配置数据库连接环境信息,可以配置多个environment,通过default属性切换不同的environment。
配置各个标签时,需要遵守前后顺序。
配置文件完成增删改查
sql语句
-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(-- id 主键id int primary key auto_increment,-- 品牌名称brand_name varchar(20),-- 企业名称company_name varchar(20),-- 排序字段ordered int,-- 描述信息description varchar(100),-- 状态:0:禁用 1:启用status int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织,构建万物互联的智能世界', 1),('小米', '小米科技有限公司', 50, 'are you ok', 1);SELECT * FROM tb_brand;
Brand类
package com.itest.pojo;/*** 品牌** alt + 鼠标左键:整列编辑** 在实体类中,基本数据类型建议使用其对应的包装类型*/public class Brand {// id 主键private Integer id;// 品牌名称private String brandName;// 企业名称private String companyName;// 排序字段private Integer ordered;// 描述信息private String description;// 状态:0:禁用 1:启用private Integer status;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBrandName() {return brandName;}public void setBrandName(String brandName) {this.brandName = brandName;}public String getCompanyName() {return companyName;}public void setCompanyName(String companyName) {this.companyName = companyName;}public Integer getOrdered() {return ordered;}public void setOrdered(Integer ordered) {this.ordered = ordered;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public Integer getStatus() {return status;}public void setStatus(Integer status) {this.status = status;}@Overridepublic String toString() {return "Brand{" +"id=" + id +", brandName='" + brandName + '\'' +", companyName='" + companyName + '\'' +", ordered=" + ordered +", description='" + description + '\'' +", status=" + status +'}';}
}
查询所有数据
定义实体类Brand.java
package com.itest.pojo;/*** 品牌** alt + 鼠标左键:整列编辑** 在实体类中,基本数据类型建议使用其对应的包装类型*/public class Brand {// id 主键private Integer id;// 品牌名称private String brandName;// 企业名称private String companyName;// 排序字段private Integer ordered;// 描述信息private String description;// 状态:0:禁用 1:启用private Integer status;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getBrandName() {return brandName;}public void setBrandName(String brandName) {this.brandName = brandName;}public String getCompanyName() {return companyName;}public void setCompanyName(String companyName) {this.companyName = companyName;}public Integer getOrdered() {return ordered;}public void setOrdered(Integer ordered) {this.ordered = ordered;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public Integer getStatus() {return status;}public void setStatus(Integer status) {this.status = status;}@Overridepublic String toString() {return "Brand{" +"id=" + id +", brandName='" + brandName + '\'' +", companyName='" + companyName + '\'' +", ordered=" + ordered +", description='" + description + '\'' +", status=" + status +'}';}
}
在Mapper中定义BrandMapper.java
package com.itest.mapper;import com.itest.pojo.Brand;
import java.util.List;public interface BrandMapper {public List<Brand> selectAll();}
在resources里创建BrandMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.itest.mapper.BrandMapper"><select id="selectAll" resultType="com.itest.pojo.Brand">select * from tb_brand;</select>
</mapper>
最后在test中新建MyBatisTest.java
package com.itest.pojo;import com.itest.mapper.BrandMapper;
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.IOException;
import java.io.InputStream;
import java.util.List;/*** @Description: TODO* @author: scott* @date: 2022年01月17日 9:26*/
public class MyBatisDemo {public static void main(String[] args) throws IOException {//加载核心配置文件,获取sqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取sqlsession对象,用它来执行sqlSqlSession sqlSession = sqlSessionFactory.openSession();//获取Mapper接口的代理对象BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//执行方法List<Brand> brands = brandMapper.selectAll();System.out.println(brands);//释放资源sqlSession.close();}
}
结果显示如下:
有字段显示为null。因为Java中驼峰命名与数据库名称不一致。
解决方案有以下两种:
1.对不一样的列名起别名,让别名和实体类的属性一样。
<select id="selectAll" resultType="com.itest.pojo.Brand">select id,brand_name as brandName,company_name as companyName,ordered,description,statusfrom tb_brand;</select>
结果:
如果觉得每次写很多列名很麻烦 ,可以用sql片段方式来引用。代码如下:
<sql id="brand_column">id,brand_name as brandName,company_name as companyName,ordered,description,status</sql><select id="selectAll" resultType="com.itest.pojo.Brand">select<include refid="brand_column"> </include>from tb_brand;</select>
2.用resultmap来解决
<resultMap id="resultBrand" type="com.itest.pojo.Brand"><result column="brand_name" property="brandName"/><result column="company_name" property="companyName"/></resultMap><select id="selectAll" resultMap="resultBrand">select *from tb_brand;</select>
主键用<id>,其他用<result>
查询-查看详情
BrandMapper.java中定义方法
package com.itest.mapper;
import com.itest.pojo.Brand;
import java.util.List;
public interface BrandMapper {public Brand selectById(int id);
}
BrandMapper.xml中写SQL语句
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.itest.mapper.BrandMapper"><resultMap id="resultBrand" type="com.itest.pojo.Brand"><result column="brand_name" property="brandName"/><result column="company_name" property="brandName"/></resultMap><select id="selectAll" resultMap="resultBrand">select *from tb_brand;</select><select id="selectById" resultMap="resultBrand">select * from tb_brand where id = #{id};</select>
</mapper>
在MyBatisTest.java中测试
package com.itest.test;import com.itest.mapper.BrandMapper;
import com.itest.mapper.UserMapper;
import com.itest.pojo.Brand;
import com.itest.pojo.User;
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.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;/*** @Description: TODO* @author: scott* @date: 2022年01月17日 15:08*/
public class MyBatisTest {@Testpublic void selectById() throws IOException {//模拟接收参数int id =1;//获取SqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取Sqlsession对象SqlSession sqlSession = sqlSessionFactory.openSession();BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);Brand brand = brandMapper.selectById(id);System.out.println(brand);//释放资源sqlSession.close();}
}
得到结果:
·注意:参数占位符:
1.#{ } :会将其替换为?,为了防止SQL注入
2.${ } :会存在SQL注入问题
使用参数传递的时候统一使用#{ }
参数类型可以省略,不省略如下:
<select id="selectById" parameterType="int" resultMap="resultBrand">select * from tb_brand where id = #{id};
</select>
XML中<会报错,解决方法有两种
1.转义字符 如<的转义字符为<
2.CDATA区 如小于号如下:<![CDATA[<]]>
条件查询-多条件查询
1.散装参数格式:注意@Param(" xxx")中xxx需要对应占位符名称 status = #{ status }
2.如果都来自同一对象,则 List<Brand>selectByCondition(Brand brand);
这样 status = #{ status } 就会到对象brand中去匹配属性。
3.封装成map,map键的名称与占位符保持一致,List<Brand>selectByCondition(Map map);
代码实现:
1.
BrandMapper.xml代码
<select id = "selectByCondition" resultMap="resultBrand">select * from tb_brandwhere status = #{status}and brand_name like #{brandName}and company_name like #{companyName}</select>
BrandMapper.java代码
public interface BrandMapper {List <Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName,@Param("brandName") String brandName);
}
MyBatisTest.java测试核心代码
//模拟接收参数int status =1;String brandName = "华为";String companyName = "华为";//处理参数brandName ="%"+brandName+"%";companyName = "%"+companyName+"%";···List<Brand> brands = brandMapper.selectByCondition(status,companyName,brandName);System.out.println(brands);
运行结果
2.
BrandMapper.xml代码不需要修改
<select id = "selectByCondition" resultMap="resultBrand">select * from tb_brandwhere status = #{status}and brand_name like #{brandName}and company_name like #{companyName}</select>
BrandMapper.java
public interface BrandMapper {List<Brand> selectByCondition(Brand brand);}
MyBatisTest.java
//模拟接收参数int status =1;String brandName = "华为";String companyName = "华为";//处理参数brandName ="%"+brandName+"%";companyName = "%"+companyName+"%";//封装对象Brand brand = new Brand();brand.setStatus(status);brand.setBrandName(companyName);brand.setCompanyName(brandName);···List<Brand> brands = brandMapper.selectByCondition(brand);System.out.println(brands);
结果
3.
BrandMapper.xml中不需要修改
<select id = "selectByCondition" resultMap="resultBrand">select * from tb_brandwhere status = #{status}and brand_name like #{brandName}and company_name like #{companyName}</select>
BrandMapper.java
public interface BrandMapper {List<Brand> selectByCondition(Map map);}
MyBatisTest.java核心代码
//模拟接收参数int status =1;String brandName = "华为";String companyName = "华为";//处理参数brandName ="%"+brandName+"%";companyName = "%"+companyName+"%";//map 封装对象Map map = new HashMap();map.put("status",status);map.put("brandName",brandName);map.put("companyName",companyName);···List<Brand> brands = brandMapper.selectByCondition(map);System.out.println(brands);
运行结果
总结多条件查询
动态SQL
针对上面多条件查询,无法满足仅填一个条件时完成正确查询结果。对此,mybatis提供强大的动态sql支撑。
·if
·choose(when,otherwise)
·trim(where,set)
·foreach
通过<if>标签来改造sql,代码如下
<select id = "selectByCondition" resultMap="resultBrand">select * from tb_brandwhere<if test="status != null">status = #{status}</if><if test="brandName !=null and brandName!='' ">and brand_name like #{brandName}</if><if test="companyName !=null and companyName!='' ">and company_name like #{companyName}</if></select>
其中Sting类型 判断条件是:不等于空,也不等于空字符串
新问题:如果status为空 则 后面语句格式不对 报错
org.apache.ibatis.exceptions.PersistenceException: ### Error
querying database. Cause:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an
error in your SQL syntax
改进方法1:恒等式 统一加and
<select id = "selectByCondition" resultMap="resultBrand">select * from tb_brandwhere 1=1<if test="status != null">and status = #{status}</if><if test="brandName !=null and brandName!='' ">and brand_name like #{brandName}</if><if test="companyName !=null and companyName!='' ">and company_name like #{companyName}</if></select>
改进方法二:mybatis<where>标签替换where关键字
<select id = "selectByCondition" resultMap="resultBrand">select * from tb_brand<where><if test="status != null">and status = #{status}</if><if test="brandName !=null and brandName!='' ">and brand_name like #{brandName}</if><if test="companyName !=null and companyName!='' ">and company_name like #{companyName}</if></where></select>
单条件-动态条件查询
BrandMapper.java
public interface BrandMapper {List<Brand> selectSingleCondition(Brand brand);}
BrandMapper.xml
<select id = "selectSingleCondition" resultMap="resultBrand">select * from tb_brand<where><choose><when test="status!=null">status=#{status}</when><when test="brandName!=null and brandName!='' ">brand_name like #{brandName}</when><when test="companyName!=null and companyName!='' ">company_name like #{companyName}</when></choose></where></select>
MyBatisTest.java
//模拟接收参数int status =1;String brandName = "华为";String companyName = "华为";//处理参数brandName ="%"+brandName+"%";companyName = "%"+companyName+"%";//封装对象Brand brand = new Brand();// brand.setStatus(status);brand.setBrandName(companyName);//brand.setCompanyName(brandName);···List<Brand> brands1 = brandMapper.selectSingleCondition(brand);System.out.println(brands1);
添加(增、删、改都需要提交事务 ,不然无法修改数据库信息)
BrandMapper.java
public interface BrandMapper {void add(Brand brand);}
BrandMapper.xml
<insert id="add" >insert into tb_brand (brand_name,company_name,ordered,description,status)values (#{brandName},#{companyName},#{ordered},#{description},#{status});</insert>
MyBatisTest.java
@Testpublic void add() throws IOException {//模拟接收参数String brandName = "小米手机";String companyName = "小米";int ordered =100;String description ="为发烧而生";int status =1;//封装参数Brand brand = new Brand();brand.setBrandName(brandName);brand.setCompanyName(companyName);brand.setOrdered(ordered);brand.setDescription(description);brand.setStatus(status);//获取SqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取Sqlsession对象SqlSession sqlSession = sqlSessionFactory.openSession();BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//执行方法brandMapper.add(brand);//提交事务sqlSession.commit();//释放资源sqlSession.close();}
添加-主键返回
若要在新增完成后获取主键id ,在insert标签中定义两个量:
修改全部字段
BrandMapper.java
public interface BrandMapper {int update(Brand brand);
}
BrandMapper.xml
<update id="update">update tb_brandsetbrand_name = #{brandName},company_name = #{companyName},ordered = #{ordered},description = #{description},status = #{status}where id = #{id};</update>
MyBatisTest.java
@Testpublic void update() throws IOException {//模拟接收参数String brandName = "ooo";String companyName = "oo";int ordered =100;String description ="1111111";int status =1;int id =5;//封装参数Brand brand = new Brand();brand.setBrandName(brandName);brand.setCompanyName(companyName);brand.setOrdered(ordered);brand.setDescription(description);brand.setStatus(status);brand.setId(id);//获取SqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取Sqlsession对象SqlSession sqlSession = sqlSessionFactory.openSession();BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//执行方法int count = brandMapper.update(brand);System.out.println(count);//提交事务sqlSession.commit();//释放资源sqlSession.close();}
修改动态字段
利用<set>标签可以避免出错;
即在BrandMapper.xml中修改为:
<update id="update">update tb_brand<set><if test="brandName!=null and brandName!=''">brand_name = #{brandName},</if><if test="companyName!=null and companyName!=''">company_name = #{companyName},</if><if test="ordered!=null ">ordered = #{ordered},</if><if test="description!=null and description!=''">description = #{description},</if><if test="status!=null">status = #{status}</if></set>where id = #{id};</update>
删除
BrandMapper.java
public interface BrandMapper {void deleteById(int id);
}
BrandMapper.xml
<delete id="deleteById">delete from tb_brand where id =#{id};</delete>
MyBatisTest.java
@Testpublic void deleteById() throws IOException {//模拟接收参数int id =5;//获取SqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取Sqlsession对象SqlSession sqlSession = sqlSessionFactory.openSession();BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//执行方法brandMapper.deleteById(id);//提交事务sqlSession.commit();//释放资源sqlSession.close();}
}
批量删除
会把选中的ID封装成一个数组,在根据数组的值把对应id全部删除。
<foreach collection=“ids” item =“id” >
利用foreach来遍历集合,其中collection表明指定便利的数组,item指定便利的元素
mybatis中会将数组参数,封装为一个Map集合。
*默认:array = 数组 *在.java中使用@Param注解来改变map集合的默认key的名称
如void deleteByIds(@Param("ids")"ids")int [] ids);
则.xml中可以使用:<foreach collection = "ids"></foreach>
代码如下:
BrandMapper.java
public interface BrandMapper {void deleteByIds(@Param("ids") int[] ids);
}
BrandMapper.xml
<delete id="deleteByIds">delete from tb_brandwhere id in<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach></delete>
MyBatisTest.java
@Testpublic void deleteByIds() throws IOException {//模拟接收参数int[] ids = {6,7};//获取SqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取Sqlsession对象SqlSession sqlSession = sqlSessionFactory.openSession();BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//执行方法brandMapper.deleteByIds(ids);//提交事务sqlSession.commit();//释放资源sqlSession.close();}
MyBatis参数传递
注解开发
代码如下:
BrandMapper.java
@Select("select * from tb_brand where id = #{id}")
Brand selectById(int id);
MyBatisTest.java
@Testpublic void selecttest() throws IOException {//模拟接收参数int id=1;//获取SqlsessionFactoryString resource = "mybatis-config.xml";InputStream inputStream = Resources.getResourceAsStream(resource);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//获取Sqlsession对象SqlSession sqlSession = sqlSessionFactory.openSession();BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);//执行方法Brand brand = new Brand();brand= brandMapper.selectById(id);System.out.println(brand);//释放资源sqlSession.close();}
总结:简单的用注解开发可以不用xml配置,但是复杂的业务还是需要用xml配置
这篇关于2022.1.17-18 Javaweb Mybatis快速入门完成增删改查的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!