Groovy 读取 csv 写入 H2

IT►开发
阅读量： ...
文章目录
  1. 引入依赖
  2. 执行读取 csv 并导入 h2

参考文档：

引入依赖

1
2
3
4
5
6
@Grapes([
	@GrabConfig(systemClassLoader=true),
    @Grab(group='com.h2database', module='h2', version='1.4.193', scope='test'),
	@Grab(group='ch.qos.logback', module='logback-classic', version='1.2.3'),
    @Grab('com.xlson.groovycsv:groovycsv:1.2')
])

注意：

  1. h2版本不能用最新的200，会出现打开数据库报错的bug，193没问题

  2. groovycsv 最新的1.3版本会出现 grape 无法下载依赖包 ，调整到1.2就ok了

    1
    java.lang.RuntimeException: Error grabbing Grapes -- [download failed: commons-logging#commons-logging;1.2!commons-logging.jar]

执行读取 csv 并导入 h2

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
import groovy.sql.Sql
import java.text.SimpleDateFormat
import groovy.util.logging.Slf4j
import java.nio.charset.StandardCharsets;
import static com.xlson.groovycsv.CsvParser.parseCsv

def partSample="D:/sample.csv"
def partOne="D:/emp-sample1.csv"
def partTwo="D:/emp-sample2.csv"
def dbUrl='jdbc:h2:E:/database/test'

def sdf=new SimpleDateFormat("yyyy-MM-dd")
//def dbFile=new File("E:/database/sd-emp.mv.db")
//if (dbFile.exists()) dbFile.delete()

def readCsv=new ReadCSV()
readCsv.execute(partOne,dbUrl)
readCsv.execute(partTwo,dbUrl)

@Slf4j
class ReadCSV{
	def execute(csvFile,dbUrl){
		//数据库连接配置
		def db = [
		    //url:'jdbc:h2:mem:test',
		    url:dbUrl,
		    user:'sa',
		    password:'',
		    driver:'org.h2.Driver'
		];

		def sql = Sql.newInstance(db.url, db.user, db.password, db.driver);
		//for(line in parseCsv(new FileReader(partSample,StandardCharsets.UTF_8), separator: ',')) {
		def data=parseCsv(new InputStreamReader(new FileInputStream(csvFile),StandardCharsets.UTF_8), separator: ',')

		def count=0
		def batchSize=5000
		for(line in data){
			count++
			//create table
			if (count==1){
				sql.execute(generateDDL(line))
			}
			//sql.execute(generateInsertSql(line,"test"))
			sql.withBatch(batchSize){stmt->
				stmt.addBatch(generateInsertSql(line,"test"))
			}
			if (count.mod(5000)==0) log.info("current insert line:{}",count)
		}
		log.info("complete. total records:{}",count)
	}

	def generateInsertSql(line,table){
		def insertSql=new StringBuffer()
		insertSql.append("INSERT INTO ${table}(")
		def values=""
		line.columns.keySet().each{
			insertSql.append("${it},")
			def value=line."${it}"
			if (value){
				if (it=="start_date") values=values+"parsedatetime('${value}','yyyy/M/dd'),"
				else values=values+"'${value}',"
			}else
				values=values+"null,"
		}
		insertSql.append("description) values (${values}")
		insertSql.append("'')")
		insertSql=insertSql.toString()
		log.trace("insertSql: {}",insertSql)
		return insertSql
	}
	def generateDDL(line){
		log.trace("line type:{}",line.getClass().getName())
		def ddl=new StringBuffer()
		ddl.append("    CREATE TABLE IF NOT EXISTS test(\r\n")
		ddl.append("        id bigint auto_increment,\r\n")
		line.columns.keySet().each{
			log.trace it
			ddl.append("        ${it} varchar(256),\r\n")
		}
		ddl.append("        description varchar(256)\r\n")
		ddl.append("    )\r\n")
		ddl=ddl.toString()
		ddl=ddl.replace("start_date varchar(256)","start_date DATE")
		log.trace("ddl:{}",ddl)
		return ddl
	}
}