當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

11.2 注解的使用示例1 select insert update和delete操作

發(fā)布時間：2023/12/20 编程问答 21 豆豆

生活随笔收集整理的這篇文章主要介紹了 11.2 注解的使用示例1 select insert update和delete操作小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

11.2 注解的使用
示例:測試 select,insert,update和delete操作
- 項目搭建
  - 數(shù)據(jù)庫表
  - 持久化對象
  - mapper接口
  - mybatis-cofig.xml
  - 數(shù)據(jù)庫信息配置文件db.properties
  - log4j.xml
  - 工具類
- 測試select功能
  - 數(shù)據(jù)表列名和持久化對象屬性名相同的情況
- 測試insert
  - mapper接口方法
  - 測試類
- 測試update
  - 測試類
- 測試delete
  - mapper接口方法
  - 測試類
- 全部查詢
  - 接口方法
  - 測試類

11.2 注解的使用

@select、@insert、@update和@delete可以完成常見的CRUD(增刪改査)SQL語句映射。

示例:測試 select,insert,update和delete操作

項目搭建

數(shù)據(jù)庫表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 創(chuàng)建數(shù)據(jù)庫表
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(18) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入數(shù)據(jù)
INSERT INTO `tb_user` VALUES ('1', '小明', '男', '21');
INSERT INTO `tb_user` VALUES ('2', '小王', '男', '22');
INSERT INTO `tb_user` VALUES ('3', '小麗', '女', '18');
INSERT INTO `tb_user` VALUES ('4', '小芳', '女', '18');
INSERT INTO `tb_user` VALUES ('5', '小王', '男', '22');

持久化對象

1
2
3
4
5
6
7
8
9
10

package domain;
public class User {
private Integer id;
private String name;
private String sex;
private Integer age;
// 此處省略無參構(gòu)造器User()
// 此處省略getter和setter方法,請自己補上
// 此處省略toString()方法.
}

mapper接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

package mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import domain.User;
public interface UserMapper {
@Select("select * from tb_user where id=#{id}")
@Results({
@Result(property="id",column="id",id=true),
@Result(property="name",column="name"),
@Result(property="sex",column="sex"),
@Result(property="age",column="age")
})
User selectUserById(Integer id);
//如果數(shù)據(jù)表的列名和持久化對象的屬性名完全一致,則可以省略@Results注解,Mybatis可以自動映射.
@Select("select * from tb_user where id=#{id}")
User selectUserById2(Integer id);
}

mybatis-cofig.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

<?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"/>

<settings>
<setting
name="logImpl"
value="log4j"/>
</settings>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"/>
<dataSource type="pooled">
<property
name="driver"
value="${driver}"/>
<property
name="url"
value="${url}"/>
<property
name="username"
value="${username}"/>
<property
name="password"
value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>


<mapper class="mapper.UserMapper"/>
</mappers>
</configuration>

數(shù)據(jù)庫信息配置文件db.properties

1
2
3
4
5
6

# 保存為db.properties文件,然后在mybatis-config.xml中通過下面標簽引入:
# <properties resource="db.properties"/>
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=root

log4j.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration
PUBLIC "-//LOG4J//DTD LOG4J//EN"
"https://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd" >






<log4j:configuration>
<appender
name="STDOUT"
class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param
name="ConversionPattern"
value="%5p [%t] %m%n"/>
</layout>
</appender>
<logger name="domain.User">
<level value="DEBUG"/>
</logger>
<root>
<level value="ERROR"/>
<appender-ref ref="STDOUT"/>
</root>
</log4j:configuration>

工具類

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

package fractory;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SqlSessionFratoryTools {
private static SqlSessionFactory sqlSessionFactory = null;
static
{
try
{
InputStream mybatisConfigXML = Resources.getResourceAsStream("mybatis-config.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(mybatisConfigXML);
} catch (IOException e)
{
e.printStackTrace();
}
}
public static SqlSession getSqlSession()
{
return sqlSessionFactory.openSession();
}
}

測試select功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectUserByIdTest {
public static void main(String[] args)
{
SqlSession sqlSession = null;
sqlSession = SqlSessionFratoryTools.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserById(1);
System.out.println("-----------------------------------------");
System.out.println(user);
sqlSession.close();
}
}

運行結(jié)果:

1
2
3
4
5

DEBUG [main] ==> Preparing: select * from tb_user where id=?
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <== Total: 1
-----------------------------------------
User [id=1, name=小明, sex=男, age=21]

調(diào)用selectUserById方法,會執(zhí)行@Select注解中的SQL語句。

數(shù)據(jù)表列名和持久化對象屬性名相同的情況

@Result注解用于列和屬性之間的結(jié)果映射,如果列和屬性名稱相同,則可以省略@Result注解,MyBatis會自動進行映射。
我的持久化類的屬性名和數(shù)據(jù)表的列名完全一致,則上面的查詢方法可以寫成如下形式:

1 2	@Select("select * from tb_user where id=#{id}") User selectUserById2(Integer id);

測試:

1
2
3
4
5
6
7
8
9
10
11

public static void main(String[] args)
{
SqlSession sqlSession = null;
sqlSession = SqlSessionFratoryTools.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// User user = userMapper.selectUserById(1);
User user = userMapper.selectUserById2(1);
System.out.println("-----------------------------------------");
System.out.println(user);
sqlSession.close();
}

運行結(jié)果:

1
2
3
4
5

可以看到運行結(jié)果與上面的完全一致.

測試insert

mapper接口方法

在mapper接口中添加如下方法:

1
2
3
4
5
6
7
8

@Insert("insert into tb_user(name,sex,age) values(#{name},#{sex},#{age})")
// useGeneratedKeys = true,表示使用自動增長的主鍵
// keyProperty = "id",表示插入數(shù)據(jù)庫表時生成的主鍵設置到User對象的id屬性.
@Options(
useGeneratedKeys = true,
keyProperty = "id"
)
void insertUser(User user);

測試類

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class InsertUserTest {
public static void main(String[] args)
{
SqlSession sqlSession = null;
// 1.加載mybatis-cofig.xml獲取SqlSession實例
sqlSession = SqlSessionFratoryTools.getSqlSession();
// 2.獲取mapper接口的代理對象.
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User("小張", "男", 22);
System.out.println("插入數(shù)據(jù)庫之前:" + user);
System.out.println("--------------------------------------------------");
userMapper.insertUser(user);
System.out.println("--------------------------------------------------");
System.out.println("插入數(shù)據(jù)庫之后:" + user);
// 提交事務
sqlSession.commit();
sqlSession.close();
}
}

運行結(jié)果:

1
2
3
4
5
6
7

插入數(shù)據(jù)庫之前:User [id=null, name=小張, sex=男, age=22]
--------------------------------------------------
DEBUG [main] ==> Preparing: insert into tb_user(name,sex,age) values(?,?,?)
DEBUG [main] ==> Parameters: 小張(String), 男(String), 22(Integer)
DEBUG [main] <== Updates: 1
--------------------------------------------------
插入數(shù)據(jù)庫之后:User [id=6, name=小張, sex=男, age=22]

調(diào)用insertUser方法,會執(zhí)行@insert注解中的SQL語句。需要注意的是,insertUser方法還使用了@Options注解,@Options注解的屬性

useGeneratedKeys=true表示使用數(shù)據(jù)庫自動增長的主鍵,該操作需要底層數(shù)據(jù)庫的支持。
keyProperty="id"表示將插入數(shù)據(jù)生成的主鍵設置到user對象的id當中。

測試update

在UserMapper接口中添加如下方法:

1 2	@Update("update tb_user set name=#{name},sex=#{sex},age=#{age} where id=#{id}") void updateUser(User user);

測試類

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class UpdateUserTest {
public static void main(String[] args)
{
SqlSession sqlSession = null;
try
{
// 1.加載mybatis-cofig.xml配置文件,并獲取會話
sqlSession = SqlSessionFratoryTools.getSqlSession();
// 2.創(chuàng)建mapper接口的代理對象.
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserById(6);
if (user != null)
{
System.out.println("更新之前的用戶信息:" + user);
User user1 = new User(user.getId(), "老張頭", "男", 88);
userMapper.updateUser(user1);
user = userMapper.selectUserById(6);
System.out.println("更新之后的用戶信息:" + user);
sqlSession.commit();
}
} catch (Exception e)
{
e.printStackTrace();
sqlSession.rollback();
} finally
{
if (sqlSession != null)
{
sqlSession.close();
}
}
}
}

運行結(jié)果:

1
2
3
4
5
6
7
8
9
10
11

DEBUG [main] ==> Preparing: select * from tb_user where id=?
DEBUG [main] ==> Parameters: 6(Integer)
DEBUG [main] <== Total: 1
更新之前的用戶信息:User [id=6, name=小張, sex=男, age=22]
DEBUG [main] ==> Preparing: update tb_user set name=?,sex=?,age=? where id=?
DEBUG [main] ==> Parameters: 老張頭(String), 男(String), 88(Integer), 6(Integer)
DEBUG [main] <== Updates: 1
DEBUG [main] ==> Preparing: select * from tb_user where id=?
DEBUG [main] ==> Parameters: 6(Integer)
DEBUG [main] <== Total: 1
更新之前的用戶信息:User [id=6, name=老張頭, sex=男, age=88]

測試delete

mapper接口方法

1 2	@Delete("delete from tb_user where id=#{id}") void deleteUserById(@Param("id") Integer id);

調(diào)用deleteUser方法,會執(zhí)行@Delete注解中的SQL語句。deleteUser方法參數(shù)前面的@Param("id")注解表示給該注解后面的變量取一個參數(shù)名稱,對應@Delete注解中的#{id}。如果沒有使用Param注解,則參數(shù)將會以它們的順序位置來和SQL語句中的表達式進行映射.

測試類

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

package test;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class DeleteTest {
public static void main(String[] args)
{
SqlSession sqlSession = null;
try
{
sqlSession = SqlSessionFratoryTools.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserById(5);
if (user != null)
{
System.out.println("即將刪除記錄:" + user);
userMapper.deleteUserById(user.getId());
}
sqlSession.commit();
} catch (Exception e)
{
sqlSession.rollback();
e.printStackTrace();
} finally
{
sqlSession.close();
}
}
}

運行效果:

1
2
3
4
5
6
7

DEBUG [main] ==> Preparing: select * from tb_user where id=?
DEBUG [main] ==> Parameters: 5(Integer)
DEBUG [main] <== Total: 1
即將刪除記錄:User [id=5, name=小王, sex=男, age=22]
DEBUG [main] ==> Preparing: delete from tb_user where id=?
DEBUG [main] ==> Parameters: 5(Integer)
DEBUG [main] <== Updates: 1

全部查詢

接口方法

1 2	@Select("select * from tb_user") List<User> seleteAllUser();

測試類

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

package test;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import domain.User;
import fractory.SqlSessionFratoryTools;
import mapper.UserMapper;
public class SelectAllUser {
public static void main(String[] args)
{
SqlSession sqlSession = null;
sqlSession = SqlSessionFratoryTools.getSqlSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> users = userMapper.seleteAllUser();
users.forEach(user -> System.out.println(" " + user));
sqlSession.close();
}
}

運行效果:

1
2
3
4
5
6
7
8

DEBUG [main] ==> Preparing: select * from tb_user
DEBUG [main] ==> Parameters:
DEBUG [main] <== Total: 5
User [id=1, name=小明, sex=男, age=21]
User [id=2, name=小王, sex=男, age=22]
User [id=3, name=小麗, sex=女, age=18]
User [id=4, name=小芳, sex=女, age=18]
User [id=6, name=老張頭, sex=男, age=88]

原文鏈接: 11.2 注解的使用示例1 select insert update和delete操作

總結(jié)

以上是生活随笔為你收集整理的11.2 注解的使用示例1 select insert update和delete操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： EPG开发《异常排查以及解决方案》
下一篇：浅谈数学、数学建模与人工智能（机器学习，