package com.shorindo.sqliteloader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class SQLiteLoader {
private Connection conn = null;
private Workbook book = null;
private Map<String,String> tableMap = new HashMap<String,String>();
public static void main(String args[]) {
SQLiteLoader loader = new SQLiteLoader();
if (args.length < 3) {
usage();
} else if ("import".equals(args[0])) {
try {
loader.imp(args[1], args[2]);
} catch(SQLiteLoaderException ex) {
ex.printStackTrace();
}
} else if ("export".equals(args[0])) {
try {
loader.exp(args[1], args[2]);
} catch (SQLiteLoaderException ex) {
ex.printStackTrace();
}
} else {
usage();
}
}
private static void usage() {
Class<SQLiteLoader> clazz = SQLiteLoader.class;
System.out.println("usage: java " + clazz.getName() + " [import|export] dbfile xlsfile");
}
private Connection getConnection(String dbFile) throws SQLiteLoaderException {
Connection conn = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
} catch (ClassNotFoundException e) {
throw new SQLiteLoaderException(e);
} catch (SQLException e) {
throw new SQLiteLoaderException(e);
}
return conn;
}
private void imp(String dbFile, String xlsFile) throws SQLiteLoaderException {
conn = getConnection(dbFile);
book = xlsFile.endsWith(".xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
try {
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM sqlite_master WHERE type='table'");
ResultSet rset = stmt.executeQuery();
while (rset.next()) {
tableMap.put(rset.getString("tbl_name"), rset.getString("sql"));
}
rset.close();
this.impTable("sqlite_master");
for (Iterator<String> iter = tableMap.keySet().iterator(); iter.hasNext();) {
this.impTable(iter.next());
}
book.write(new FileOutputStream(xlsFile));
} catch (SQLException ex) {
throw new SQLiteLoaderException(ex);
} catch (FileNotFoundException ex) {
throw new SQLiteLoaderException(ex);
} catch (IOException ex) {
throw new SQLiteLoaderException(ex);
} finally {
try { conn.close(); } catch (SQLException ex) { ex.printStackTrace(); }
}
}
private void impTable(String tableName) throws SQLException {
Sheet sheet = book.createSheet(tableName);
int rowNum = 0;
Row row;
Cell cell;
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + tableName);
ResultSet rset = stmt.executeQuery();
boolean noHead = true;
int count = 0;
while (rset.next()) {
if (noHead) {
row = sheet.createRow(rowNum++);
ResultSetMetaData meta = rset.getMetaData();
count = meta.getColumnCount();
for (int i = 1; i <= count; i++) {
cell = row.createCell(i - 1);
cell.setCellValue(meta.getColumnName(i));
}
noHead = false;
}
row = sheet.createRow(rowNum++);
for (int i = 1; i <= count; i++) {
cell = row.createCell(i - 1);
cell.setCellValue(rset.getString(i));
}
}
rset.close();
stmt.close();
}
private void exp(String dbFile, String xlsFile) throws SQLiteLoaderException {
conn = getConnection(dbFile);
try {
InputStream is = new FileInputStream(xlsFile);
book = xlsFile.endsWith(".xlsx") ? new XSSFWorkbook(is) : new HSSFWorkbook(is);
Sheet sheet = book.getSheet("sqlite_master");
Map<String,String> tableMap = new HashMap<String,String>();
if (sheet == null) {
throw new SQLiteLoaderException("sqlite_master not found");
}
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
if ("table".equals(row.getCell(0).getStringCellValue())) {
tableMap.put(row.getCell(2).getStringCellValue(), row.getCell(4).getStringCellValue());
}
}
for (Iterator<String> iter = tableMap.keySet().iterator(); iter.hasNext();) {
String tableName = iter.next();
//create table
System.out.println(tableName);
String sql = tableMap.get(tableName).replaceAll("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.executeUpdate();
stmt.close();
//delete table
stmt = conn.prepareStatement("DELETE FROM " + tableName);
stmt.executeUpdate();
stmt.close();
//insert rows
sheet = book.getSheet(tableName);
List<String> cellNames = new ArrayList<String>();
List<String> holders = new ArrayList<String>();
Row row = sheet.getRow(0);
for (Iterator<Cell> iter2 = row.iterator(); iter2.hasNext();) {
Cell cell = iter2.next();
cellNames.add(cell.getStringCellValue());
holders.add("?");
}
sql = "INSERT INTO " + tableName + " (";
String sep = "";
for (String name : cellNames) {
sql += sep + name;
sep = ",";
}
sql += ") VALUES (";
sep = "";
for (String holder : holders) {
sql += sep + holder;
sep = ",";
}
sql += ")";
stmt = conn.prepareStatement(sql);
for (int j = 1; j < sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if (row != null) {
for (int k = 0; k < holders.size(); k++) {
Cell cell = row.getCell(k);
stmt.setString(k + 1, cell.getStringCellValue());
}
}
stmt.executeUpdate();
}
}
} catch (IOException ex) {
throw new SQLiteLoaderException(ex);
} catch (SQLException ex) {
throw new SQLiteLoaderException(ex);
} finally {
try { conn.close(); } catch (SQLException ex) { ex.printStackTrace(); }
}
}
public class SQLiteLoaderException extends Exception {
private static final long serialVersionUID = 1L;
public SQLiteLoaderException(Exception ex) {
super(ex);
}
public SQLiteLoaderException(String message) {
super(message);
}
}
}
