jpa mysql脚本迁移_JPA通过LOAD DATA LOCAL INFILE大批量导入数据到MySQL

连接层代码：

import org.hibernate.Session;

import org.hibernate.internal.SessionFactoryImpl;

import org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

public int loadFromInputStream(String loadDataSql, InputStream dataStream) throws JPAException {

Session session = (Session) entityManagerFactory.getDelegate();

SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();

int result = 0;

try {

//获取C3P0连接

Connection conn = sessionFactory.getConnectionProvider().getConnection();

PreparedStatement ps = conn.prepareStatement(loadDataSql);

//将C3P0的连接转换成mysql的

C3P0NativeJdbcExtractor cp30NativeJdbcExtractor = new C3P0NativeJdbcExtractor();

com.mysql.jdbc.Connection mysql_conn = (com.mysql.jdbc.Connection) cp30NativeJdbcExtractor.getNativeConnection(ps.getConnection());

//将C3P0的PreparedStatement转换成mysql的

com.mysql.jdbc.PreparedStatement mysql_ps = mysql_conn.prepareStatement(loadDataSql).unwrap(com.mysql.jdbc.PreparedStatement.class);

//设置文件流

mysql_ps.setLocalInfileInputStream(dataStream);

result = ps.executeUpdate();

} catch (SQLException e) {

e.printStackTrace();

}

return result;

}

使用连接层代码：

public void testLoadFile() throws ServiceException {

String loadDataSql = "load data LOCAL infile 'C:/Users/Administrator/Desktop/userinfo.txt' into table userinfo character set utf8 fields terminated by ',' enclosed by '\"' lines terminated by '\r\n' (`userId`,`userName`,`userNickName`,`createTime`,`userEmail`,`userPassword`,`userSex`,`userMobile`,`lastLoginTime`);";

byte[] bytes = loadDataSql.getBytes();

InputStream is = new ByteArrayInputStream(bytes);

try {

userDAO.testLoadFile(loadDataSql, is);

} catch (JPAException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

userinfo.txt内容：

"2","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"3","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"4","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"5","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"6","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"7","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"8","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"9","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"10","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"11","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"12","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"13","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"14","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"15","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"16","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

"17","2","2","2017-01-13 14:03:53","2","2","2","2","2017-01-13 14:03:53"

userinfo表结构：

CREATE TABLE `userinfo` (

`userId` varchar(36) NOT NULL,

`userName` varchar(45) DEFAULT NULL,

`userNickName` varchar(45) DEFAULT NULL,

`createTime` datetime DEFAULT NULL,

`userEmail` varchar(45) DEFAULT NULL,

`userPassword` varchar(45) DEFAULT NULL,

`userSex` int(11) DEFAULT NULL,

`userMobile` varchar(45) DEFAULT NULL,

`lastLoginTime` datetime DEFAULT NULL,

PRIMARY KEY (`userId`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;