使用存储过程在mysql中批量插入数据
2024-08-26 07:49:15
一、在mysql数据库中创建一张表test
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` INT (11),
`name` VARCHAR (225),
`age` INT (11),
`create_time` DATE
);
二、使用存储过程插入数据
DROP PROCEDURE IF EXISTS BatchInsert;
DELIMITER $$
CREATE PROCEDURE BatchInsert(IN i INT, IN len INT)
BEGIN
WHILE i <= len DO
INSERT INTO `test`(`id`,`name`,`age`,`create_time`) VALUES (i, CONCAT('tom-', i),FLOOR(RAND()*100),NOW());
SET i = i + 1;
END WHILE;
END;
$$
DELIMITER ;
CALL BatchInsert(1, 100);
三、在MyBatis中使用存储过程
1.新建一个maven工程，引入相应的依赖
<?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.yqd</groupId>
<artifactId>hello-mybatis-store procedure</artifactId>
<version>1.0.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
</project>
2.创建mybatis-config.xml
<?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>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<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/db20200930_springboot_demo?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/yqd/dao/TestMapper.xml"/>
</mappers>
</configuration>
3.新建一个mybatis工具类
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSession(boolean flag){
return sqlSessionFactory.openSession(flag);
}
//获取SqlSession连接
public static SqlSession getSession(){
return getSession(true); //设置事务自动提交
}
}
4.创建实体类Test
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Test {
private int id;
private String name;
private int age;
private Date createTime;
}
5.创建mapper和对应的xml
public interface TestMapper {
int batchInsert(@Param("i") int i, @Param("len") int len);
}
<?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">
<mapper namespace="com.yqd.dao.TestMapper">
<insert id="batchInsert" statementType="CALLABLE">
{call batchInsert(#{i,mode=IN,jdbcType=INTEGER}, #{len,mode=IN})}
</insert>
</mapper>
6.在mysql数据库中新建存储过程,执行一遍下面的代码
DROP PROCEDURE IF EXISTS batchInsert;
DELIMITER $$
CREATE PROCEDURE batchInsert(IN i INT, IN len INT)
BEGIN
WHILE i <= len DO
INSERT INTO `test`(`id`,`name`,`age`,`create_time`) VALUES (i, CONCAT('tom-', i),FLOOR(RAND()*100),NOW());
SET i = i + 1;
END WHILE;
END;
$$
DELIMITER ;
7.测试
public class MyTest {
@Test
public void testBatchInsert() {
SqlSession session = MybatisUtils.getSession();
TestMapper mapper = session.getMapper(TestMapper.class);
mapper.batchInsert(1, 10);//调用mapper中的方法，执行存储过程
session.close();
}
}
最新文章
- 在APACHE服务器上的访问方式上去除index.php
- spring amqp rabbitmq fanout配置
- ord函数-php
- Android布局— — —帧布局
- C++之构造函数重载
- 【译】使用微软企业库5.0进行WCF服务边界上的异常保护
- 【架构研习】欲善其事先利其器-Robot Framework实战演练之框架的选择
- python 列表去重(数组)的几种方法(转)
- Python 的 setitem、getitem、delitem 特殊方法使用
- Scala 按名称参数调用函数 与 =>的用法
- 洛谷.2590.[ZJOI2008]树的统计(树分块)
- CNN卷积层：ReLU函数
- 利用monkeyrunner、python脚本来做多设备多apk适配ui界面截屏的自动化测试
- Delphi：MSBuild编译dproj工程
- yum 安装mysql数据库
- Actor模式初步入门
- 如何快速找到某个研究领域的所有SCI期刊
- RabbitMQ.Client API (.NET)中文文档
- MyBatis 查询缓存
- Flume（二）Flume的Source类型