package com.legend;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
public class ZipFileDemo {
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
//用户名
private static String user = "scott";
//密码
private static String password = "tiger";
public static Connection conn;
public static PreparedStatement ps;
public static ResultSet rs;
public static Statement st;
public static void main(String[] args) throws Exception {
int i = 0; // 计数器,初始值为0,因为csv的表头是不需要插入到DB里的,所以当为0时,不做任何
JDBC_Test JdbcDao = new JDBC_Test();
StudentBean stu1 = new StudentBean();
List<String> dataList=ZipFileDemo.importCsv(new File("D:/CSVFile.csv"));
if(dataList!=null && !dataList.isEmpty()){
for(String data : dataList){
System.out.println(data);
String dataBean[] = data.split(",");
if (i > 0) {
stu1.setId(Integer.parseInt(dataBean[0].trim())); // id为int, 所以要讲数组为1的转成int
stu1.setName(dataBean[1].trim());
stu1.setAge(Integer.parseInt(dataBean[2].trim()));
stu1.setSex(dataBean[3].trim());
try {
getConnection();
String sql = "INSERT INTO student(id,name,age,sex)"
+ "VALUES(" + stu1.getId() + "," + "'" +stu1.getName() +"'" + "," + stu1.getAge() + "," + "'" +stu1.getSex()+"'" + ")";
// String sql = "INSERT INTO student(id,name,age,sex)" + "VALUES(" + 510 + "," + "'Test'" + "," + 99 + "," + "'Demo'" + ")";
st = (Statement) conn.createStatement(); // 创建用于执行静态sql语句的Statement对象
int count = st.executeUpdate(sql); // 执行插入操作的sql语句,并返回插入数据的个数
System.out.println("向student表中插入 " + count + " 条数据"); //输出插入操作的处理结果
} catch (Exception e) {
e.printStackTrace();
}finally {
conn.close();
}
}
i++;
}
}
}
public static List<String> importCsv(File file){
List<String> dataList=new ArrayList<String>();
BufferedReader br=null;
try {
br = new BufferedReader(new FileReader(file));
String line = "";
while ((line = br.readLine()) != null) {
dataList.add(line);
}
}catch (Exception e) {
e.printStackTrace();
}finally{
if(br!=null){
try {
br.close();
br=null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
return dataList;
}
// 连接数据库的方法
public static void getConnection() {
try {
//初始化驱动包
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
}
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
public class ZipFileDemo {
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
//用户名
private static String user = "scott";
//密码
private static String password = "tiger";
public static Connection conn;
public static PreparedStatement ps;
public static ResultSet rs;
public static Statement st;
public static void main(String[] args) throws Exception {
int i = 0; // 计数器,初始值为0,因为csv的表头是不需要插入到DB里的,所以当为0时,不做任何
JDBC_Test JdbcDao = new JDBC_Test();
StudentBean stu1 = new StudentBean();
List<String> dataList=ZipFileDemo.importCsv(new File("D:/CSVFile.csv"));
if(dataList!=null && !dataList.isEmpty()){
for(String data : dataList){
System.out.println(data);
String dataBean[] = data.split(",");
if (i > 0) {
stu1.setId(Integer.parseInt(dataBean[0].trim())); // id为int, 所以要讲数组为1的转成int
stu1.setName(dataBean[1].trim());
stu1.setAge(Integer.parseInt(dataBean[2].trim()));
stu1.setSex(dataBean[3].trim());
try {
getConnection();
String sql = "INSERT INTO student(id,name,age,sex)"
+ "VALUES(" + stu1.getId() + "," + "'" +stu1.getName() +"'" + "," + stu1.getAge() + "," + "'" +stu1.getSex()+"'" + ")";
// String sql = "INSERT INTO student(id,name,age,sex)" + "VALUES(" + 510 + "," + "'Test'" + "," + 99 + "," + "'Demo'" + ")";
st = (Statement) conn.createStatement(); // 创建用于执行静态sql语句的Statement对象
int count = st.executeUpdate(sql); // 执行插入操作的sql语句,并返回插入数据的个数
System.out.println("向student表中插入 " + count + " 条数据"); //输出插入操作的处理结果
} catch (Exception e) {
e.printStackTrace();
}finally {
conn.close();
}
}
i++;
}
}
}
public static List<String> importCsv(File file){
List<String> dataList=new ArrayList<String>();
BufferedReader br=null;
try {
br = new BufferedReader(new FileReader(file));
String line = "";
while ((line = br.readLine()) != null) {
dataList.add(line);
}
}catch (Exception e) {
e.printStackTrace();
}finally{
if(br!=null){
try {
br.close();
br=null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
return dataList;
}
// 连接数据库的方法
public static void getConnection() {
try {
//初始化驱动包
Class.forName("oracle.jdbc.driver.OracleDriver");
conn=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
}
}