Java tutorial
/* * Copyright 2012 pcal.net * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package net.pcal.sqlsheet; import net.pcal.sqlsheet.parser.*; 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 java.sql.*; import java.util.HashMap; import java.util.Map; /** * SqlSheet implementation of java.sql.Statement. * * @author <a href='http://www.pcal.net'>pcal</a> * @author <a href='http://code.google.com/p/sqlsheet'>sqlsheet</a> */ public class XlsStatement implements Statement { private XlsConnection connection; private Map<String, XlsResultSet> sheet2rs = new HashMap<String, XlsResultSet>(); private SqlSheetParser parser; public XlsStatement(XlsConnection c) { if (c == null) throw new IllegalArgumentException(); this.connection = c; } public Connection getConnection() throws SQLException { return connection; } public void close() throws SQLException { } public boolean execute(String sql) throws SQLException { ParsedStatement parsed = parse(sql); if (parsed instanceof DropTableStatement) { doDropTable((DropTableStatement) parsed); } else { executeQuery(parsed); } return true; } private ResultSet executeQuery(ParsedStatement parsed) throws SQLException { if (parsed instanceof SelectStarStatement) { return doSelect((SelectStarStatement) parsed); } else if (parsed instanceof InsertIntoStatement) { return doInsert((InsertIntoStatement) parsed); } else if (parsed instanceof CreateTableStatement) { return doCreateTable((CreateTableStatement) parsed); } else { throw new IllegalStateException(parsed.getClass().getName()); } } public int executeUpdate(String sql) throws SQLException { executeQuery(sql); return 1; } public ResultSet executeQuery(String query) throws SQLException { ParsedStatement parsed = parse(query); return executeQuery(parsed); } private void doDropTable(DropTableStatement dropTableStatement) { connection.setWriteRequired(true); String tableName = dropTableStatement.getTable(); int sheetIndexToRemove = connection.getWorkBook().getSheetIndex(tableName); connection.getWorkBook().removeSheetAt(sheetIndexToRemove); } protected ParsedStatement parse(String sql) throws SQLException { if (sql == null) throw new IllegalArgumentException(); if (parser == null) parser = new SqlSheetParser(); return parser.parse(sql); } protected ResultSet doSelect(SelectStarStatement sss) throws SQLException { XlsResultSet out = findOrCreateResultSetFor(sss.getTable()); out.beforeFirst(); out.statement = this; return out; } protected ResultSet doCreateTable(CreateTableStatement cts) throws SQLException { connection.setWriteRequired(true); String tableName = cts.getTable().replace("\"", ""); Sheet newSheet = connection.getWorkBook().createSheet(tableName); Row row = newSheet.createRow(0); for (short i = 0; i < cts.getColumns().size(); i++) { Cell cell = row.createCell(i); cell.setCellValue(cts.getColumns().get(i)); } return findOrCreateResultSetFor(cts.getTable()); // REVIEW } protected ResultSet doInsert(InsertIntoStatement insert) throws SQLException { connection.setWriteRequired(true); XlsResultSet rs = findOrCreateResultSetFor(insert.getTable()); rs.moveToInsertRow(); for (int i = 0; i < insert.getColumns().size(); i++) { rs.updateObject(i + 1, insert.getValues().get(i)); } return rs; } private XlsResultSet findOrCreateResultSetFor(String tableName) throws SQLException { tableName = tableName.trim().toUpperCase(); XlsResultSet out = sheet2rs.get(tableName); if (out == null) { Sheet sheet = getSheetNamed(connection.getWorkBook(), tableName); out = new XlsResultSet(connection.getWorkBook(), sheet, connection.getInt(XlsDriver.HEADLINE, 1)); out.statement = this; sheet2rs.put(tableName, out); } return out; } private static Sheet getSheetNamed(Workbook wb, String name) throws SQLException { if (name == null) throw new IllegalArgumentException(); name = name.trim(); String allSheetNames = ""; int count = wb.getNumberOfSheets(); for (int i = 0; i < count; i++) { String sheetName = wb.getSheetName(i); allSheetNames += sheetName + ","; if (sheetName == null) continue; if (sheetName.equalsIgnoreCase(name) || ("\"" + sheetName + "\"").equalsIgnoreCase(name)) { return wb.getSheetAt(i); } } String message = "No sheet named '" + name; if (count == 0) { message += " can be found. Are you sure of the Excel file path ?"; } else { if (allSheetNames.length() > 2) { allSheetNames = allSheetNames.substring(0, allSheetNames.length() - 1); } message += ". Only the following " + count + " sheets can be found (" + allSheetNames + ")"; } throw new SQLException(message); } public void setMaxFieldSize(int p0) throws SQLException { nyi(); } public void setMaxRows(int p0) throws SQLException { nyi(); } public void setEscapeProcessing(boolean p0) throws SQLException { nyi(); } public void setQueryTimeout(int p0) throws SQLException { nyi(); } public void setCursorName(String p0) throws SQLException { nyi(); } public void setFetchDirection(int p0) throws SQLException { nyi(); } public void setFetchSize(int p0) throws SQLException { nyi(); } public int getMaxFieldSize() throws SQLException { nyi(); return -1; } public int getMaxRows() throws SQLException { nyi(); return -1; } public int getQueryTimeout() throws SQLException { nyi(); return -1; } public SQLWarning getWarnings() throws SQLException { nyi(); return null; } public ResultSet getResultSet() throws SQLException { nyi(); return null; } public int getUpdateCount() throws SQLException { nyi(); return -1; } public boolean getMoreResults() throws SQLException { nyi(); return false; } public boolean getMoreResults(int current) throws SQLException { nyi(); return false; } public int getFetchDirection() throws SQLException { nyi(); return -1; } public int getFetchSize() throws SQLException { nyi(); return -1; } public int getResultSetConcurrency() throws SQLException { nyi(); return -1; } public int getResultSetType() throws SQLException { nyi(); return -1; } public void cancel() throws SQLException { nyi(); } public void clearWarnings() throws SQLException { nyi(); } public void addBatch(String p0) throws SQLException { nyi(); } public void clearBatch() throws SQLException { nyi(); } public int[] executeBatch() throws SQLException { nyi(); return null; } public ResultSet getGeneratedKeys() throws SQLException { nyi(); return null; } public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException { nyi(); return -1; } public int executeUpdate(String sql, int[] columnIndexes) throws SQLException { nyi(); return -1; } public int executeUpdate(String sql, String[] columnNames) throws SQLException { nyi(); return -1; } public boolean execute(String sql, int autoGeneratedKeys) throws SQLException { nyi(); return false; } public boolean execute(String sql, int[] columnIndexes) throws SQLException { nyi(); return false; } public boolean execute(String sql, String[] columnNames) throws SQLException { nyi(); return false; } public int getResultSetHoldability() throws SQLException { nyi(); return -1; } protected void nyi() throws SQLException { throw new SQLException("NYI"); } public boolean isClosed() throws SQLException { return false; } public boolean isPoolable() throws SQLException { return false; } public void setPoolable(boolean poolable) throws SQLException { } public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } public <T> T unwrap(Class<T> iface) throws SQLException { return null; } public void closeOnCompletion() throws SQLException { throw new UnsupportedOperationException("Not supported yet."); } public boolean isCloseOnCompletion() throws SQLException { throw new UnsupportedOperationException("Not supported yet."); } }