Java实现mybatis批量插入数据到Oracle
程序员文章站
2024-03-12 21:19:50
最近项目中遇到一个问题:导入数据到后台并将数据插入到数据库中,导入的数据量有上万条数据,考虑采用批量插入数据的方式;
结合网上资料,写了个小demo,文章末尾...
最近项目中遇到一个问题:导入数据到后台并将数据插入到数据库中,导入的数据量有上万条数据,考虑采用批量插入数据的方式;
结合网上资料,写了个小demo,文章末尾附上demo下载地址
1、新建项目:项目目录结构如下图所示,添加相应的jar包
2、新建数据库表:account_info
create table account_info ( "id" number(12) not null , "username" varchar2(64 byte) null , "password" varchar2(64 byte) null , "gender" char(1 byte) null , "email" varchar2(64 byte) null , "create_date" date null )
3、创建accountinfo实体类:
package com.oracle.entity; import java.sql.date; public class accountinfo { private long id; private string username; private string password; private string gender; private string email; private date createdate; public long getid() { return id; } public void setid(long 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 getemail() { return email; } public void setemail(string email) { this.email = email; } public date getcreatedate() { return createdate; } public void setcreatedate(date createdate) { this.createdate = createdate; } @override public string tostring() { return "accountinfo [id=" + id + ", username=" + username + ", password=" + password + ", gender=" + gender + ", email=" + email + ", createdate=" + createdate + "]"; } }
4、新建接口映射类:accountinfomapper.java
package com.oracle.mapper; import java.util.list; import com.oracle.entity.accountinfo; public interface accountinfomapper { /** * 查询所有的数据 * @return */ list<accountinfo> queryallaccountinfo(); /** * 批量插入数据 * * @param accountinfolist * @return */ int batchinsertaccountinfo(list<accountinfo> accountinfolist); }
5、创建mybatis配置文件:mybatis-configuration.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> <environments default="development"> <environment id="development"> <transactionmanager type="jdbc" /> <datasource type="pooled"> <property name="driver" value="oracle.jdbc.driver.oracledriver" /> <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl" /> <property name="username" value="xxx" /> <property name="password" value="xxx" /> </datasource> </environment> </environments> <mappers> <mapper resource="config/accountinfomapper.xml" /> </mappers> </configuration>
6、创建接口映射配置文件:accountinfomapper.xml
oracle的批量插入数据库跟mysql不一样,
mysql:
复制代码 代码如下:
insert into account_info(id, username,password,gender, email,create_date)values(,,,,,,)(,,,,,,,)
oracle:
复制代码 代码如下:
insert into account_info(id, username,password,gender, email,create_date) (select 1,,,,,, from dual union all select 1,,,,,, from dual)
<?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.oracle.mapper.accountinfomapper"><!-- 接口的全类名 --> <!-- type:实体类的全类名 --> <resultmap id="baseresultmap" type="com.oracle.entity.accountinfo"> <id column="id" property="id" jdbctype="decimal" /> <result column="username" property="username" jdbctype="varchar" /> <result column="password" property="password" jdbctype="varchar" /> <result column="gender" property="gender" jdbctype="char" /> <result column="email" property="email" jdbctype="varchar" /> <result column="create_date" property="createdate" jdbctype="date" /> </resultmap> <!-- id 跟接口中的方法名称保持一致 --> <select id="queryallaccountinfo" resultmap="baseresultmap"> select id, username,password, gender, email, create_date from account_info </select> <insert id="batchinsertaccountinfo" parametertype="java.util.list"> insert into account_info(id, username,password,gender, email,create_date) ( <foreach collection="list" index="" item="accountinfo" separator="union all"> select #{accountinfo.id}, #{accountinfo.username}, #{accountinfo.password}, #{accountinfo.gender}, #{accountinfo.email}, #{accountinfo.createdate} from dual </foreach> ) </insert> </mapper>
7、编写测试类:
package com.oracle.test; import java.io.inputstream; import java.sql.date; import java.util.arraylist; import java.util.list; 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 com.oracle.entity.accountinfo; import com.oracle.mapper.accountinfomapper; public class mybatistest { public static void main(string[] args) throws exception { string resource = "config/mybatis-configuration.xml"; inputstream inputstream = resources.getresourceasstream(resource); sqlsessionfactory sessionfactory = new sqlsessionfactorybuilder() .build(inputstream); sqlsession session = sessionfactory.opensession(); accountinfomapper mapper = session.getmapper(accountinfomapper.class); list<accountinfo> accountinfolist = mapper.queryallaccountinfo(); if (accountinfolist == null) { system.out.println("the result is null."); } else { for (accountinfo personinfo : accountinfolist) { system.out.println(personinfo); } } mapper.batchinsertaccountinfo(generatedata()); session.commit(); } static list<accountinfo> generatedata(){ list<accountinfo> result = new arraylist<accountinfo>(); accountinfo account = new accountinfo(); account.setid(3l); account.setusername("zhangsanfeng"); account.setpassword("123456"); account.setgender("1"); account.setemail("zhangsanfeng@wudang.com"); account.setcreatedate(new date(system.currenttimemillis())); result.add(account); account = new accountinfo(); account.setid(4l); account.setusername("zhouzhiruo"); account.setpassword("zhangwuji"); account.setgender("0"); account.setemail("zhouzhiruo@emei.com"); account.setcreatedate(new date(system.currenttimemillis())); result.add(account); account = new accountinfo(); account.setid(5l); account.setusername("zhaomin"); account.setpassword("zhangwuji"); account.setgender("0"); account.setemail("zhaomin@yuan.com"); account.setcreatedate(new date(system.currenttimemillis())); result.add(account); return result; } }
源码下载:
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。