mybatis curd
https://blog.csdn.net/weixin_44364444/article/details/111354615?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166432470116782427478965%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=166432470116782427478965&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~top_positive~default-2-111354615-null-null.142^v50^control,201^v3^control_1&utm_term=mybatis&spm=1018.2226.3001.4187
https://caochenlei.blog.csdn.net/article/details/119992847?spm=1001.2014.3001.5502
3、CRUD
3.1、namespace namespace中的包名要和Dao/mapper接口的包名保持一致
3.2、select
id:就是对应的namespace中的方法名;
resultType:Sql语句执行的返回值!
parameterType:参数类型!
12345678910111213package
com.rui.dao;
import
com.rui.pojo.User;
import
java.util.List;
public
interface
UserMapper {
//根据id查询用户
User getUserById(
int
id);
}
编写对应的mapper中的sql语句
12345<select id=
"getUserById"
resultType=
"com.rui.pojo.User"
parameterType=
"int"
>
/*定义sql*/
select * from mybatis.user where id = #{id};
</select>
测试
12345678@Test
public
void
getUserById(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.
class
);
User user = mapper.getUserById(
1
);
System.out.println(user);
sqlSession.close();
}
3.3、Insert
3.4、Update
3.5、Delete
12345678910111213141516171819202122232425262728<?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核心配置文件-->
<configuration>
<!--environments配置环境组-->
<!--
default
默认环境-->
<environments
default
=
"development"
>
<!--environment单个环境-->
<environment id=
"development"
>
<!--transactionManager配置事务管理器-->
<transactionManager type=
"JDBC"
/>
<!--配置连接池-->
<dataSource type=
"POOLED"
>
<property name=
"driver"
value=
"com.mysql.cj.jdbc.Driver"
/>
<property name=
"url"
value=
"jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useSSL=true&useUnicode=true"
/>
<property name=
"username"
value=
"root"
/>
<property name=
"password"
value=
"root"
/>
</dataSource>
</environment>
</environments>
<!--每一个Mapper.xml需要在Mybatis核心配置文件中注册-->
<mappers>
<mapper resource=
"com/newer/dao/UserMapper.xml"
/>
</mappers>
</configuration>
定义mapper层接口
1234567891011121314151617181920212223package
com.newer.dao;
import
com.newer.pojo.User;
import
java.util.List;
public
interface
UserMapper {
//查询全部用户
List<User> getUserList();
//根据ID查询用户
User getUserById(
int
id);
//insert一个用户
int
addUser(User user);
//修改用户
int
updateUser(User user);
//删除用户
int
deleteUser(
int
id);
}
sql语句实现mapper接口的方法
123456789101112131415161718192021222324252627<?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"
>
<!--namespace绑定一个对应的Mapper接口-->
<mapper namespace=
"com.newer.dao.UserMapper"
>
<select id=
"getUserList"
resultType=
"com.newer.pojo.User"
>
select * from mybatis.user
</select>
<select id=
"getUserById"
parameterType=
"int"
resultType=
"com.newer.pojo.User"
>
select * from mybatis.user where id=#{id}
</select>
<insert id=
"addUser"
parameterType=
"com.newer.pojo.User"
>
insert into mybatis.user (id,name,pwd) values (#{id},#{name },#{pwd})
</insert>
<update id=
"updateUser"
parameterType=
"com.newer.pojo.User"
>
update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id}
</update>
<delete id=
"deleteUser"
parameterType=
"int"
>
delete from mybatis.user where id=#{id}
</delete>
</mapper>
测试
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586package
com.newer.dao;
import
com.newer.pojo.User;
import
com.newer.utils.MyBatisUtils;
import
org.apache.ibatis.session.SqlSession;
import
org.junit.Test;
import
java.util.List;
public
class
UserDaoTest {
@Test
public
void
test(){
//第一步:获得SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//方式一:getMapper
UserMapper userDao = sqlSession.getMapper(UserMapper.
class
);
List<User> userList = userDao.getUserList();
//方式二
//List<User> userList = sqlSession.selectList("com.newer.dao.UserMapper.getUserList");
for
(User user : userList) {
System.out.println(user);
}
//关闭sqlSession
sqlSession.close();
}
@Test
public
void
getUserById(){
//第一步:获得SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.
class
);
User user = mapper.getUserById(
1
);
System.out.println(user);
//关闭sqlSession
sqlSession.close();
}
//增删改需要提交事务
@Test
public
void
addUser(){
//第一步:获得SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.
class
);
int
res = mapper.addUser(
new
User(
4
,
"哈哈"
,
"12364"
));
if
(res>
0
){
System.out.println(
"插入成功!"
);
}
//提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
@Test
public
void
updateUser(){
//第一步:获得SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.
class
);
int
res = mapper.updateUser(
new
User(
4
,
"呵呵"
,
"88888"
));
if
(res>
0
){
System.out.println(
"修改成功!"
);
}
//提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
@Test
public
void
deleteUser(){
//第一步:获得SqlSession对象
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.
class
);
int
res = mapper.deleteUser(
4
);
if
(res>
0
){
System.out.println(
"删除成功!"
);
}
//提交事务
sqlSession.commit();
//关闭sqlSession
sqlSession.close();
}
}
3.6、分析错误
- 标签不要匹配错误
- resource绑定mapper,需要使用路径!
- 程序配置文件必须符合规范
- NullPointerException,没有注册到资源
- 输出的xml文件中存在中文乱码问题
- maven资源没有导出问题
3.7、万能Map
我们的实体类,或者数据库中的表,字段或者参数过多,我们应当考虑mapper层里使用Map!
int addUser2(Map<String,Object> map);
<!--对象中的属性,可以直接取出来 parameterType=传递map中的key-->
<insert id="addUser2" parameterType="map">
insert into mybatis.user (id, name, pwd) values (#{userId},#{userName},#{password});
</insert>@Test
public void addUser2(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put(“userId”,4);
map.put(“userName”,“王五”);
map.put(“password”,“23333”);
mapper.addUser2(map);
//提交事务
sqlSession.commit();
sqlSession.close();
}
- Map传递参数,直接在sql中取出key即可!【parameterType=“map”】
- 对象传递参数,直接在sql中取对象的属性即可!【parameterType=“Object”】
- 只有一个基本类型参数的情况下,可以直接在sql中取到!
- 多个参数用Map,或者注解!
5.2、resultMap
结果集映射
- id name pwd
- id name password
123456789<resultMap id=
"UserMap"
type=
"User"
>
<!--column数据库中的字段,property实体类中的属性-->
<result column=
" id"
property=
"id"
/>
<result column=
"name"
property=
"name"
/>
<result column=
"pwd"
property=
"password"
/>
</resultMap>
<select id=
"getUserById"
resultMap=
"UserMap"
parameterType=
"int"
>
/*定义sql*/
select * from mybatis.user where id = #{id};
resultMap 元素是 MyBatis 中最重要最强大的元素
ResultMap 的设计思想是,对于简单的语句根本不需要配置显式的结果映射,而对于复杂一点的语句只需要描述它们的关系就行了。
ResultMap 最优秀的地方在于,虽然你已经对它相当了解了,但是根本就不需要显式地用到他们。
————————————————7、分页
为什么要分页?------------减少数据的处理量7.1、使用Limit分页
select * from user limit startIndex,pageSize
使用Mybatis实现分页,核心SQL//分页
List<User> getUserByLimit(Map<String,Integer> map);
Mapper.xml<!--分页-->
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
select * from mybatis.user limit #{startIndex},#{pageSize}
</select>
测试@Test
public void getUserByLimit(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Integer> map = new HashMap<>();
map.put("startIndex",0);
map.put("pageSize",2);
List<User> userList = mapper.getUserByLimit(map);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
7.2、RowBounds分页
不再使用SQL实现分页List<User> getUserByRowBounds();
mapper.xml<!--分页2-->
<select id="getUserByRowBounds" resultMap="UserMap">
select * from mybatis.user
</select>
测试@Test
public void getUserByRowBounds(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//RowBounds实现
RowBounds rowBounds = new RowBounds(1, 2);
//通过java代码层面实现分页
List<User> userList = sqlSession.selectList("com.rui.dao.UserMapper.getUserByRowBounds",null,rowBounds);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
7.3、分页插件
8、使用注解开发
————————————————8.2、使用注解开发
注解在接口上实现
12@Select
(value =
"select * from user"
)
List<User> getUsers();
需要在核心配置文件中绑定接口!
1234<!--绑定接口-->
<mappers>
<mapper
class
=
"rui.dao.UserMapper"
/>
</mappers>
测试
12345678910111213public
class
UserMapperTest {
@Test
public
void
test(){
SqlSession sqlSession = MyBatisUtils.getSqlSession();
//底层主要应用反射
UserMapper mapper = sqlSession.getMapper(UserMapper.
class
);
List users = mapper.getUsers();
for
(User user : users) {
System.out.println(user);
}
sqlSession.close();
}
}
编写接口,增加注解
1234567891011121314151617public
interface
UserMapper {
@Select
(value = “select * from user”)
List getUsers();
//方法存在多个参数,所有的参数前面必须加上@Param注解
@Select
(
"select * from user where id = #{id} or name = #{name}"
)
User getUserByID(
@Param
(
"id"
)
int
id,
@Param
(
"name"
)String name);
@Insert
(
"insert into user(id,name,pwd) values (#{id},#{name},#{password})"
)
int
addUser(User user);
@Update
(
"update user set name = #{name},pwd = #{password} where id = #{id}"
)
int
updateUser(User user);
@Delete
(
"delete from user where id = #{uid}"
)
int
deleteUser(
@Param
(
"uid"
)
int
id);
}
测试类
【注意:我们必须要将接口注册绑定到我们的核心配置文件中!】
关于@Param()注解
- 基本类型的参数或者String类型,需要加上
- 引用类型不需要加
- 如果只有一个基本类型的话,可以忽略,但是建议大家都加上
- 我们在SQL中引用的就是我们这里的@Param()中设定的属性名
#{} ${}区别
10、多对一处理
多对一:
- 多个学生,对应一个老师
- 对于学生这边而言,关联...多个学生,关联一个老师【多对一】
- 对于老师而言,集合,一个老师又很多学生【一对多】
测试环境:
- 导入lombok 新建实体类Teacher,Student
- 新建Mapper接口
- 建立Mapper.XML文件
- 在核心配置文件中绑定注册我们的MApper接口或者文件!【方式很多,随意选】
- 测试查询是否成功!
1234567891011121314151617181920212223242526按照查询嵌套处理<br><?xml version=
"1.0"
encoding=
"UTF-8"
?>
<!DOCTYPE mapper
PUBLIC
"-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"
>
<mapper namespace=
"com.newer.dao.StudentMapper"
>
<!--
1
.查询所有学生信息
2
.根据查询出来的学生的tid,寻找对应的老师
-->
<select id=
"getStudent"
resultMap=
"StudentTeacher"
>
select * from student
</select>
<resultMap id=
"StudentTeacher"
type=
"Student"
>
<result property=
"id"
column=
"id"
></result>
<result property=
"name"
column=
"name"
></result>
<!--复杂的属性,需要单独处理
对象:association
集合:collection
-->
<association property=
"teacher"
column=
"tid"
javaType=
"Teacher"
select=
"getTeacher"
></association>
</resultMap>
<select id=
"getTeacher"
resultType=
"Teacher"
>
select * from teacher where id=#{id}
</select>
</mapper><br><br>按照结果嵌套处理<br>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.newer.dao.StudentMapper">
<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid=t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"></result>
<result property="name" column="tname"></result>
</association>
</resultMap>
</mapper>
回顾Mysql多对一查询方式:
- 子查询
- 联表查询
11、一对多处理
比如:一个老师拥有多个学生!
对于老师而言,就是一对多的关系!
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
1234567@Data
public
class
Student {
private
int
id;
private
String name;
private
int
tid;
}
1234567//按照结果嵌套处理
select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t where s.tid=t.id and t.id = #{tid}
//按照查询嵌套处理
select * from mybatis.teacher where id = #{tid} select * from mybatis.student where tid = #{tid}
1234567891011121314151617181920212223242526272829303132333435<?xml version=
"1.0"
encoding=
"UTF-8"
?>
<!DOCTYPE mapper
PUBLIC
"-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"
>
<mapper namespace=
"com.newer.dao.TeacherMapper"
>
<select id=
"getTeacher"
resultMap=
"TeacherStudent"
>
select s.id sid ,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid=t.id and t.id=#{tid}
</select>
<resultMap id=
"TeacherStudent"
type=
"Teacher"
>
<result property=
"id"
column=
"tid"
></result>
<result property=
"name"
column=
"tname"
></result>
<collection property=
"students"
ofType=
"Student"
>
<result property=
"id"
column=
"sid"
></result>
<result property=
"name"
column=
"sname"
></result>
<result property=
"tid"
column=
"tid"
></result>
</collection>
</resultMap>
<!--=====================================================-->
<select id=
"getTeacher2"
resultMap=
"TeacherStudent2"
>
select * from teacher where id=#{tid}
</select>
<resultMap id=
"TeacherStudent2"
type=
"Teacher"
>
<collection property=
"students"
column=
"id"
javaType=
"ArrayList"
ofType=
"Student"
select=
"getStudentByTeacherId"
></collection>
</resultMap>
<select id=
"getStudentByTeacherId"
resultType=
"Student"
>
select * from student where tid=#{tid}
</select>
</mapper>
关联-association【多对一】
集合-collection 【一对多】
javaType & ofType
JavaType用来指定实体类中属性的类型
ofType用来指定映射到List或者集合中的pojo类型,泛型中的约束类型!
注意点:保证SQL的可读性,尽量保证通俗易懂
注意一对多和多对一中,属性名和字段的问题!
如果问题不好排查错误,可以使用日志,建议使用Log4j
慢SQL 1S 1000S面试高频:
Mysql引擎
InnoDB底层原理
索引
索引优化!
12、动态SQL
动态SQL:动态SQL就是指根据不同的条件生成不同的SQL语句动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类,现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
if
choose (when, otherwise)
trim (where, set)
foreach
————————————————
版权声明:本文为CSDN博主「最小的帆也能远航」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_44364444/article/details/111354615
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· DeepSeek本地性能调优
· 一文掌握DeepSeek本地部署+Page Assist浏览器插件+C#接口调用+局域网访问!全攻略