6. Java code example to insert data from CSV to database
// copyright by https://www.codejava.net
//https://www.codejava.net/coding/java-code-example-to-insert-data-from-csv-to-database
-------------------------------------------------------------------------------------------------------------------
mysqldump --no-data --skip-comments --user=root --password=root --host=localhost test review> D:\vm\review.sql
-------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS `review`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `review` (
`id` int DEFAULT NULL,
`course_name` varchar(128) DEFAULT NULL,
`student_name` varchar(45) DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT NULL,
`rating` float DEFAULT NULL,
`comment` varchar(1024) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-------------------------------------------------------------------------------------------------------------------
Input File Format:
-------------------------------------------------------------------------------------------------------------------
Course Name,Student Name,Timestamp,Rating,Comment
Java Servlet JSP and Hibernate,Praveen Gurram,2019-07-31 19:10:13,5.0,excellent teaching
Java Microservices Masterclass,Van Hoang Tran,2019-04-23 09:48:58,4.5,
Python for Data Science,Dhara Patel,2019-06-18 02:50:17,5.0,Amazing experience after this course.
Design Patterns in Java,Darshan Patel,2019-06-28 21:46:56,5.0,Great Experience.
-------------------------------------------------------------------------------------------------------------------
package net.codejava;
import java.io.*;
import java.sql.*;
public class SimpleCsv2DbInserter {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/test?characterEncoding=latin1";
String username = "root";
String password = "root";
String csvFilePath = "D:\\vm\\eclipse\\eclipse-workspace\\Test\\csv\\Reviews-simple.csv";
int batchSize = 20;
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(jdbcURL, username, password);
connection.setAutoCommit(false);
String sql = "INSERT INTO review (course_name, student_name, timestamp, rating, comment) VALUES (?, ?, ?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
BufferedReader lineReader = new BufferedReader(new FileReader(csvFilePath));
String lineText = null;
int count = 0;
lineReader.readLine(); // skip header line
while ((lineText = lineReader.readLine()) != null) {
String[] data = lineText.split(",");
String courseName = data[0];
String studentName = data[1];
String timestamp = data[2];
String rating = data[3];
String comment = data.length == 5 ? data[4] : "";
statement.setString(1, courseName);
statement.setString(2, studentName);
Timestamp sqlTimestamp = Timestamp.valueOf(timestamp);
statement.setTimestamp(3, sqlTimestamp);
Float fRating = Float.parseFloat(rating);
statement.setFloat(4, fRating);
statement.setString(5, comment);
statement.addBatch();
if (count % batchSize == 0) {
statement.executeBatch();
}
}
lineReader.close();
// execute the remaining queries
statement.executeBatch();
connection.commit();
connection.close();
} catch (IOException ex) {
System.err.println(ex);
} catch (SQLException ex) {
ex.printStackTrace();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
Course Name,Student Name,Timestamp,Rating,Comment
"Java Servlet JSP and Hibernate",Praveen Gurram,2019-07-31 19:10:13,5.0,"excellent teaching"
"Java Microservices Masterclass",Van Hoang Tran,2019-04-23 09:48:58,4.5,"The course is great"
"Python for Data Science",Dhara Patel,2019-06-18 02:50:17,5.0,"Amazing experience after this course.
Thank you, instructor"
"Design Patterns in Java",Darshan Patel,2019-06-28 21:46:56,5.0,"Great Experience, I love this course"
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
// copyright by https://www.codejava.net
package net.codejava;
import java.io.*;
import java.sql.*;
import org.supercsv.cellprocessor.Optional;
import org.supercsv.cellprocessor.ParseDouble;
import org.supercsv.cellprocessor.constraint.NotNull;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.io.CsvBeanReader;
import org.supercsv.io.ICsvBeanReader;
import org.supercsv.prefs.CsvPreference;
public class ComplexCsv2DbInserter {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/sales";
String username = "user";
String password = "password";
String csvFilePath = "Reviews-complex.csv";
int batchSize = 20;
Connection connection = null;
ICsvBeanReader beanReader = null;
CellProcessor[] processors = new CellProcessor[] {
new NotNull(), // course name
new NotNull(), // student name
new ParseTimestamp(), // timestamp
new ParseDouble(), // rating
new Optional()// comment
};
try {
long start = System.currentTimeMillis();
connection = DriverManager.getConnection(jdbcURL, username, password);
connection.setAutoCommit(false);
String sql = "INSERT INTO review (course_name, student_name, timestamp, rating, comment) VALUES (?, ?, ?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
beanReader = new CsvBeanReader(new FileReader(csvFilePath),
CsvPreference.STANDARD_PREFERENCE);
beanReader.getHeader(true); // skip header line
String[] header = {"courseName", "studentName", "timestamp", "rating", "comment"};
Review bean = null;
int count = 0;
while ((bean = beanReader.read(Review.class, header, processors)) != null) {
String courseName = bean.getCourseName();
String studentName = bean.getStudentName();
Timestamp timestamp = bean.getTimestamp();
double rating = bean.getRating();
String comment = bean.getComment();
statement.setString(1, courseName);
statement.setString(2, studentName);
statement.setTimestamp(3, timestamp);
statement.setDouble(4, rating);
statement.setString(5, comment);
statement.addBatch();
if (count % batchSize == 0) {
statement.executeBatch();
}
}
beanReader.close();
// execute the remaining queries
statement.executeBatch();
connection.commit();
connection.close();
long end = System.currentTimeMillis();
System.out.println("Execution Time: " + (end - start));
} catch (IOException ex) {
System.err.println(ex);
} catch (SQLException ex) {
ex.printStackTrace();
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
package net.codejava;
import java.sql.Timestamp;
public class Review {
private String courseName;
private String studentName;
private Timestamp timestamp;
private double rating;
private String comment;
// constructors...
// getters and setters...
}
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
package net.codejava;
import java.sql.Timestamp;
import org.supercsv.cellprocessor.CellProcessorAdaptor;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.util.CsvContext;
public class ParseTimestamp extends CellProcessorAdaptor {
public ParseTimestamp() {
super();
}
public ParseTimestamp(CellProcessor next) {
super(next);
}
@Override
public Object execute(Object value, CsvContext context) {
return Timestamp.valueOf((String) value);
}
}
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment