csv和数据表的转换(一)

csv是逗号分隔符的缩写,csv文件通常是纯文本文件,建议在编辑csv文件时使用记事本.
注意:如果你的本地安装了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();
	}
}