1.create database sqooptest1
2.use sqooptest1
3.create table project(
id int not null auto_increment primary key,
name varchar(100) not null,
type tinyint(4) not nulldefault0,
description varchar(500)defaultnull,
create_at date defaultnull,
update_at timestamp not nulldefault current_timestamp on update current_timestamp,
status tinyint(4) not nulldefault0);4.insert into project( name,type,description,create_at,status)values( 'project1',1,'project1 zy','2019-07-27',0);
insert into project( name,type,description,create_at,status)values( 'project2',1,'project2 zy','2019-07-26',0);
insert into project( name,type,description,create_at,status)values( 'project2',2,'project2 zy','2019-07-25',0);
sqoop命令
sqoop import--connect jdbc:mysql://node3:3306/sqooptest1 --username root --password a --table project
结果
2.students组数据
数据库
1.create database sqooptest1
2.use sqooptest1
3.create table students(
id int not null primary key,
name varchar(100) not null,
age varchar(100) not null);
数据位置
E:\JAVA课程\...\11.Hadoop\12.Sqoop\a.txt
向数据库中插入数据
importjava.io.BufferedReader;importjava.io.File;importjava.io.FileInputStream;importjava.io.InputStreamReader;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Scanner;publicclassAddBatchMysql{publicstaticvoidmain(String[] args){//1.用户输入文件位置Scanner sc =newScanner(System.in);System.out.println("文件位置：");String path = sc.nextLine();//2.以流的形式读取文件中所有数据，按行读，按\t切，分出id,name,ageList<String> list =newArrayList<String>();try(BufferedReader br =newBufferedReader(newInputStreamReader(newFileInputStream(newFile(path))))){String str;while((str=br.readLine())!=null){
list.add(str);}}catch(Exception e){
e.printStackTrace();}//3.批量插入数据System.out.println("数据总条数："+list.size());String sql ="insert into students values(?,?,?)";Connection con=null;PreparedStatement pstmt=null;try{
con =DriverManager.getConnection("jdbc:mysql://node3:3306/sqooptest1?serverTimezone=UTC","root","a");
con.setAutoCommit(false);//设置成手动提交事务
pstmt = con.prepareStatement(sql);int total =0;String s;String[] ss;for(int i=0;i<list.size();i++){
s = list.get(i);
ss = s.split("\t");
pstmt.setString(1, ss[0]);
pstmt.setString(2, ss[1]);
pstmt.setString(3, ss[2]);//加入批处理操作//将当前要执行的操作添加到批缓存
pstmt.addBatch();if((i+1)%1000==0){//1000条数据处理一次int[] res = pstmt.executeBatch();
total+=sum(res);
con.commit();
pstmt.clearBatch();}}int[] res = pstmt.executeBatch();System.out.println(res);
total+=sum(res);
con.commit();
pstmt.clearBatch();System.out.println("实际插入数据条数："+total);}catch(Exception e){
e.printStackTrace();try{
con.rollback();}catch(SQLException e1){
e1.printStackTrace();}}finally{if(con!=null){try{
con.setAutoCommit(true);}catch(SQLException e){
e.printStackTrace();}try{
con.close();}catch(SQLException e){
e.printStackTrace();}}}}privatestaticintsum(int[] res){int total =0;if(res==null&&res.length<=0){return0;}for(int i=0;i<res.length;i++){
total+=res[i];}return total;}}
288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
