From: SVN by r. <sv...@ca...> - 2008-01-27 12:57:39
|
Author: roy Date: 2008-01-27 13:39:29 +0100 (Sun, 27 Jan 2008) New Revision: 230 Modified: src/main/java/nl/improved/sqlclient/ src/main/java/nl/improved/sqlclient/DBConnector.java src/main/java/nl/improved/sqlclient/QueryExecutor.java src/main/java/nl/improved/sqlclient/SQLShell.java Log: fixes for displaying timestamp/date format on oracle and mysql Property changes on: src/main/java/nl/improved/sqlclient ___________________________________________________________________ Name: svn:ignore - .SQLPlusPlus.java.swp .SQLPlus.java.swp .DBConnector.java.swp .SQLUtil.java.swp .Point.java.swp .SQLLineWrapper.java.swp .SQLOutput.java.swp + .SQLPlusPlus.java.swp .SQLPlus.java.swp .DBConnector.java.swp .SQLUtil.java.swp .Point.java.swp .SQLLineWrapper.java.swp .SQLOutput.java.swp .QueryExecutor.java.swp Modified: src/main/java/nl/improved/sqlclient/DBConnector.java =================================================================== --- src/main/java/nl/improved/sqlclient/DBConnector.java 2008-01-27 10:41:23 UTC (rev 229) +++ src/main/java/nl/improved/sqlclient/DBConnector.java 2008-01-27 12:39:29 UTC (rev 230) @@ -16,10 +16,14 @@ package nl.improved.sqlclient; import java.sql.Connection; +import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.sql.Types; +import java.text.DateFormat; +import java.text.SimpleDateFormat; import java.util.Hashtable; import java.util.Set; import java.util.Map; @@ -36,7 +40,15 @@ import jcurses.widgets.WidgetsConstants; public final class DBConnector { + + /** + * The default formatting pattern for Date or Date-like columns. + */ + private static final String DEFAULT_TIMESTAMP_PATTERN = "yyyy-MM-dd HH:mm:ss"; + private static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd"; + private static final String DEFAULT_TIME_PATTERN = "HH:mm:ss"; + private static DBConnector instance = null; private boolean tableNamesUppercase = false; @@ -45,7 +57,10 @@ private Connection activeConnection; private Statement statement; + private boolean dateIsTimeStamp; + private QueryExecutor queryExecutor; + private DBConnector() { predefinedConnections = new Hashtable<String, ConnectionSettings>(); Properties dbProperties = new Properties(); @@ -91,6 +106,10 @@ return name.toLowerCase(); } + public boolean treatDateAsTimestamp() { + return dateIsTimeStamp; + } + /** * Return the used schema. * NOTE: hack because Oracle uses as a schema the current username @@ -176,6 +195,17 @@ return connect(getPredefinedConnectionSettings(ident), username, password); } + public QueryExecutor getQueryExecutor() { + if (queryExecutor == null) { + if (dateIsTimeStamp) { + queryExecutor = new QueryExecutor(DEFAULT_TIMESTAMP_PATTERN, DEFAULT_TIME_PATTERN, DEFAULT_TIMESTAMP_PATTERN); + } else { + queryExecutor = new QueryExecutor(DEFAULT_DATE_PATTERN, DEFAULT_TIME_PATTERN, DEFAULT_TIMESTAMP_PATTERN); + } + } + return queryExecutor; + } + private ConnectionSettings getPredefinedConnectionSettings(String identifier) { if (predefinedConnections.containsKey(identifier)) { return predefinedConnections.get(identifier); @@ -256,10 +286,10 @@ activeConnection = DriverManager.getConnection(settings.getConnectionURL(), username, password); activeConnection.setAutoCommit(autoCommit); - // INITIALIZE to uppercase type + // INITIALIZE database settings try { - ResultSet rs = activeConnection.getMetaData() - .getTables(activeConnection.getCatalog(), getSchema() + DatabaseMetaData metaData = activeConnection.getMetaData(); + ResultSet rs = metaData.getTables(activeConnection.getCatalog(), getSchema() , null, new String[]{"TABLE"}); while (rs.next()) { String tableName = rs.getString("TABLE_NAME"); @@ -270,6 +300,20 @@ } } } + rs = metaData.getTypeInfo(); + dateIsTimeStamp = true; + String timestampType = null, dateType =null; + while (rs.next() && (timestampType == null && dateType == null)) { + if (Types.TIMESTAMP == rs.getInt("DATA_TYPE")) { + timestampType = rs.getString("LOCAL_TYPE_NAME"); + } if (Types.DATE == rs.getInt("DATA_TYPE")) { + dateType = rs.getString("LOCAL_TYPE_NAME"); + } + } + if (timestampType != null && dateType != null) { + dateIsTimeStamp = dateType.equals(timestampType); + } + } catch(Exception e) { /* ignore */ } @@ -286,6 +330,7 @@ activeConnection.close(); activeConnection = null; } + queryExecutor = null; } public Connection getConnection() { Modified: src/main/java/nl/improved/sqlclient/QueryExecutor.java =================================================================== --- src/main/java/nl/improved/sqlclient/QueryExecutor.java 2008-01-27 10:41:23 UTC (rev 229) +++ src/main/java/nl/improved/sqlclient/QueryExecutor.java 2008-01-27 12:39:29 UTC (rev 230) @@ -15,9 +15,12 @@ */ package nl.improved.sqlclient; +import java.sql.Date; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; +import java.sql.Time; +import java.sql.Timestamp; import java.sql.Types; import java.util.List; import java.util.ArrayList; @@ -41,19 +44,24 @@ public class QueryExecutor { /** - * The default formatting pattern for Date or Date-like columns. - */ - private static final String DEFAULTDATEPATTERN = "yyyy-MM-dd HH:mm:ss"; - /** * Used to format dates with unspecified patterns. */ - private DateFormat defaultDateFormat; + private DateFormat defaultDateFormat, defaultTimeFormat, defaultTimestampFormat; + private String timeFormat; + private String timestampFormat; + private String dateFormat; + /** * Constructor. */ - public QueryExecutor() { - defaultDateFormat = new SimpleDateFormat(DEFAULTDATEPATTERN); + QueryExecutor(String dateFormat, String timeFormat, String timestampFormat) { + this.dateFormat = dateFormat; + this.timeFormat = timeFormat; + this.timestampFormat = timestampFormat; + defaultDateFormat = new SimpleDateFormat(dateFormat); + defaultTimeFormat = new SimpleDateFormat(timeFormat); + defaultTimestampFormat = new SimpleDateFormat(timestampFormat); } /** @@ -79,22 +87,6 @@ } /** - * Check if a column is a Date or Date-like. - * @param metadata the metadata describing the resultset - * @param column the column to check - * @return true if the column is date-like, false otherwise. - */ - private boolean isDate(ResultSetMetaData metadata, int column) throws SQLException { - switch (metadata.getColumnType(column)) { - case Types.DATE: - case Types.TIMESTAMP: - case Types.TIME: - return true; - } - return false; - } - - /** * Returns the width at wich a column should be displayed. * Usually the ResultSetMetaData will be responsible for this width, but a few exceptions * are made (this would typicall be the case for dates). @@ -104,8 +96,13 @@ * @return the width in characters that should be used to display the column. */ private int getColumnWidth(ResultSetMetaData metadata, int column) throws SQLException { - if (isDate(metadata, column)) { - return DEFAULTDATEPATTERN.length(); + switch (metadata.getColumnType(column)) { + case Types.DATE: + return dateFormat.length(); + case Types.TIMESTAMP: + return timestampFormat.length(); + case Types.TIME: + return timeFormat.length(); } // Let's assume for now that most columns CAN actually contain NULL values, and therefore we want every column to have a minimum width of 4 return Math.max(4, metadata.getColumnDisplaySize(column)); @@ -122,11 +119,43 @@ private CharSequence getDisplayValue(ResultSet rset, int column) throws SQLException { ResultSetMetaData metadata = rset.getMetaData(); + switch (metadata.getColumnType(column)) { + case Types.DATE: { + if (dateFormat.equals(timestampFormat)) {// for databases that see date as a timestamp + Timestamp date = rset.getTimestamp(column); + if (date == null) { + return "NULL"; + } + return defaultTimestampFormat.format(date); + } + Date date = rset.getDate(column); + if (date == null) { + return "NULL"; + } + return defaultDateFormat.format(date); + } + case Types.TIMESTAMP: { + try { + Timestamp date = rset.getTimestamp(column); + if (date == null) { + return "NULL"; + } + return defaultTimestampFormat.format(rset.getTimestamp(column)); + } catch(SQLException e) { + return "NULL"; + } + } + case Types.TIME: { + Time date = rset.getTime(column); + if (date == null) { + return "NULL"; + } + return defaultTimeFormat.format(date); + } + } Object colValue = rset.getObject(column); if (colValue == null) { return "NULL"; - } else if (isDate(metadata, column)) { - return defaultDateFormat.format(rset.getTimestamp(column)); } else { return colValue.toString(); } Modified: src/main/java/nl/improved/sqlclient/SQLShell.java =================================================================== --- src/main/java/nl/improved/sqlclient/SQLShell.java 2008-01-27 10:41:23 UTC (rev 229) +++ src/main/java/nl/improved/sqlclient/SQLShell.java 2008-01-27 12:39:29 UTC (rev 230) @@ -94,10 +94,6 @@ private CommandManager commands = new CommandManager(); /** - * Executor for SQL Queries - */ - private QueryExecutor querySelector; - /** * Executor for SQL Statements */ private StatementExecutor queryExecutor; @@ -648,10 +644,7 @@ */ protected CharSequence getResult(CharSequence command) throws SQLException { if (command.length() > "select".length() && "select".equalsIgnoreCase(command.subSequence(0, "create".length()).toString())) { - if (querySelector == null) { - querySelector = new QueryExecutor(); - } - return querySelector.executeQuery(command); + return DBConnector.getInstance().getQueryExecutor().executeQuery(command); } if (queryExecutor == null) { queryExecutor = new StatementExecutor(); |