csv是逗号分隔符的缩写,csv文件通常是纯文本文件,建议在编辑csv文件时使用记事本.
注意:如果你的本地安装了Mircrosoft Excel,csv文件会默认以Excel方式打开,如果你使用默认Excel方式编辑后保存会有问题.
本实例使用第三方框架supercsv.
注意:如果你的本地安装了Mircrosoft Excel,csv文件会默认以Excel方式打开,如果你使用默认Excel方式编辑后保存会有问题.
本实例使用第三方框架supercsv.
1.创建数据库表,直接看代码csv.sql.
drop database if exists csv;
create database csv character set utf8;
use csv;
create table user (
id varchar(255) unique,
username varchar(255) not null,
password varchar(255) not null
) default charset=utf8;
2.读取csv文件数据到数据表,直接看代码Csv2Table.
package com.ilucky.csv;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.supercsv.cellprocessor.constraint.NotNull;
import org.supercsv.cellprocessor.constraint.UniqueHashCode;
import org.supercsv.cellprocessor.ift.CellProcessor;
import org.supercsv.io.CsvMapReader;
import org.supercsv.io.ICsvMapReader;
import org.supercsv.prefs.CsvPreference;
/**
* @author IluckySi
* @date 20140603
*/
public class Csv2Table {
public void csv2Table() {
//从csv文件中读取User数据放到集合中.
List<User> users = new ArrayList<User>();
InputStream is = null;
InputStreamReader isr = null;
BufferedReader br = null;
ICsvMapReader mapReader = null;
Map<String, Object> userMap = null;
try {
is = Csv2Table.class.getClassLoader().getResourceAsStream("user.csv");
isr = new InputStreamReader(is);
br = new BufferedReader(isr);
mapReader = new CsvMapReader(br, CsvPreference.STANDARD_PREFERENCE);
final String[] header = mapReader.getHeader(true);
final CellProcessor[] processors = new CellProcessor[] {new UniqueHashCode(), new NotNull(), new NotNull()};
while ((userMap = mapReader.read(header, processors)) != null) {
User user = new User();
user.setId((String) userMap.get("id"));
user.setUsername((String) userMap.get("username"));
user.setPassword((String) userMap.get("password"));
users.add(user);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if(mapReader != null) {
mapReader.close();
mapReader = null;
}
if(br != null) {
br.close();
br = null;
}
if(isr != null) {
isr.close();
isr = null;
}
if(is != null) {
is.close();
is = null;
}
} catch (Exception e) {
System.out.println(e.toString());
}
}
//将User数据写到mysql数据库.
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1/csv?useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "mysql";
connection = DriverManager.getConnection(url, username, password);
preparedStatement = connection.prepareStatement("insert into user values(?, ?, ?)");
for(int i = 0; users != null && i < users.size(); i++) {
preparedStatement.setString(1, users.get(i).getId());
preparedStatement.setString(2, users.get(i).getUsername());
preparedStatement.setString(3, users.get(i).getPassword());
preparedStatement.executeUpdate();
System.out.println("成功保存一条数据到user表(" + users.get(i) + ")");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.读取数据表数据到csv文件,直接看代码Table2Csv.
package com.ilucky.csv;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.supercsv.io.CsvMapWriter;
import org.supercsv.io.ICsvMapWriter;
import org.supercsv.prefs.CsvPreference;
/**
* @author IluckySi
* @date 20140603
*/
public class Table2Csv {
public void table2Csv() {
//从mysql数据库中读取User数据放到集合中.
List<User> users = new ArrayList<User>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1/csv?useUnicode=true&characterEncoding=utf-8";
String username = "root";
String password = "mysql";
connection = DriverManager.getConnection(url, username, password);
preparedStatement = connection.prepareStatement("select * from user");
resultSet = preparedStatement.executeQuery();
while(resultSet.next()) {
User user = new User();
user.setId(resultSet.getString("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
users.add(user);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
//将User数据写到csv文件.
String path = "D:/user33333.csv";
File file = null;
OutputStream os = null;
OutputStreamWriter osr = null;
BufferedWriter br = null;
ICsvMapWriter mapWriter = null;
try {
file = new File(path);
os = new FileOutputStream(file);
osr = new OutputStreamWriter(os);
br = new BufferedWriter(osr);
mapWriter = new CsvMapWriter(br, CsvPreference.EXCEL_PREFERENCE);
final String[] header = new String[] {"id", "username", "password"};
mapWriter.writeHeader(header);
for(int i = 0; users != null && i < users.size(); i++) {
final HashMap<String, ? super Object> map = new HashMap<String, Object>();
map.put(header[0], users.get(i).getId());
map.put(header[1], users.get(i).getUsername());
map.put(header[2], users.get(i).getPassword());
mapWriter.write(map, header);
System.out.println("成功导出一条数据到csv文件(" + users.get(i) + ")");
}
} catch (Exception e){
System.out.println(e.toString());
} finally {
try {
if(mapWriter != null) {
mapWriter.close();
mapWriter = null;
}
if(br != null) {
br.close();
br = null;
}
if(osr != null) {
osr.close();
osr = null;
}
if(os != null) {
os.close();
os = null;
}
} catch (Exception e) {
System.out.println(e.toString());
}
}
}
}
4.还有一个model类,直接看代码User.
package com.ilucky.csv;
/**
* @author IluckySi
* @date 20140603
*/
public class User {
private String id;
private String username;
private String password;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String toString() {
return "id=" + id +", username=" + username + ", password=" + password;
}
}
5.最后通过测试程序对上面两个过程进行测试,直接看测试代码MainTest.
package com.ilucky.csv;
/**
* @author IluckySi
* @date 20140603
*/
public class MainTest {
public static void main(String[] args) {
//将csv文件中的数据写入到数据表.
Csv2Table c2T = new Csv2Table();
//c2T.csv2Table();
//将数据表中的数据写入到csv文件.
Table2Csv t2C = new Table2Csv();
//t2C.table2Csv();
}
}