public class SqlQuery extends Object
Modifier and Type | Field and Description |
---|---|
protected static Calendar |
calendar_ |
protected static String |
columnDelimitor |
protected Connection |
con |
static short |
DB2_DB |
protected static String |
db2DbDriverName |
protected static String |
db2DbPort |
protected String |
dbConnectString |
protected String |
dbDriverName |
protected String |
dbIP |
protected String |
dbName |
protected String |
dbPassword |
protected String |
dbPort |
protected short |
dbType_
Field signifying what DB type this query will access.
|
protected String |
dbUser |
protected static boolean |
debugOut |
static String |
DEFAULT_COLUMN_DELIMITOR |
static short |
DEFAULT_DB |
static short |
DERBY_DB |
protected static String |
derbyDbDriverName |
protected static String |
derbyDbPort |
protected static int |
dy_ |
static short |
HSQL_DB |
protected static String |
hsqlDbDriverName |
protected static String |
hsqlDbPort |
protected static Log |
log_ |
static short |
MARIADB_DB |
protected static String |
mariadbDbDriverName |
protected static String |
mariadbDbPort |
protected static int |
mo_ |
static short |
MYSQL_DB |
protected static String |
mysqlDbDriverName |
protected static String |
mysqlDbPort |
static short |
ODBC_DB |
protected static String |
odbcDbDriverName |
static short |
ORACLE_DB |
static short |
ORACLEDEDICATED_DB |
protected static String |
oraDbDriverName |
protected static String |
oraDbPort |
protected boolean |
oraDedicated_ |
static short |
OTHER_DB |
protected static String |
pgDbDriverName |
protected static String |
pgDbPort |
static short |
POSTGRE_DB |
protected static String |
proxoolDriverName |
protected int |
proxoolMaximumConnectionCount_ |
protected long |
randLong |
protected String |
randStr |
protected String |
sqlFrom |
protected String |
sqlFrom2 |
protected String |
sqlFrom3 |
protected String |
sqlFrom4 |
protected String |
sqlFrom5 |
protected String |
sqlOrder |
protected String |
sqlSelect |
protected String |
sqlWhere |
static String |
SYSTEM_FILE_SEPERATOR
A holder for this clients System File Separator.
|
static String |
SYSTEM_LINE_SEPERATOR
A holder for this clients System line termination separator.
|
protected String |
testDbConnectString |
protected boolean |
useProxoolPool_ |
protected static int |
yr_ |
Constructor and Description |
---|
SqlQuery()
Constructor for the SqlQuery object
|
Modifier and Type | Method and Description |
---|---|
boolean |
canConnect()
Tests the Production DB connection.
|
boolean |
canConnect(boolean useProductionDb)
Tests the DB connection.
|
String |
convertSqlResultsToCSV(StringBuffer results)
Converts the results from the 'query' method into a Comma Seperated String.
|
boolean |
convertSqlResultsToXLS(StringBuffer results,
String xlsFilename)
Converts the results from the 'query' method into an xls file.
|
static WritableWorkbook |
copyIntoWorkbook(StringBuffer sb,
WritableWorkbook wbkIn,
int shtPosition,
String sheetName) |
static String |
createCurrentTimeStamp()
Creates a timestamp for the current time in the form of
'hour + "-" + min + "-" + sec + "-" + millis'.
|
private org.jfree.data.category.DefaultCategoryDataset |
createDatasetFromSqlResults(StringBuffer sqlResults)
A helper method to take the reaults from a DBquery and parse them into a
CategoryDataset for use in making a chart.
|
static String |
csvToJSON(String content,
String separator)
Converts a sql resultSet in the form of a deliminated string to a JSON format.
|
String |
generateCategoryBarChart(String sql,
HttpSession session,
PrintWriter pw,
String chartTitle,
String xAxisTitle,
String yAxisTitle,
boolean showLegend)
Generates a JFreeChart Categorized Bar Chart based on the SQL that is passed
in and executed.
|
String |
generatePieChart(String sql,
HttpSession session,
PrintWriter pw,
String chartTitle,
String xAxisTitle,
String yAxisTitle)
Generates a JFreeChart Pie Chart based on the SQL that is passed
in and executed.
|
static String |
getColumnDelimitor()
Get Method for class field 'columnDelimitor'.
|
String |
getDbConnectString()
Get Method for class field 'dbConnectString'.
|
String |
getDbDriverName()
Get Method for class field 'dbDriverName'.
|
String |
getDbIP()
Get Method for class field 'dbIP'.
|
String |
getDbName()
Get Method for class field 'dbName'.
|
String |
getDbPassword()
Get Method for class field 'dbPassword'.
|
String |
getDbUser()
Get Method for class field 'dbUser'.
|
String |
getSqlFrom()
Get Method for class field 'sqlFrom'.
|
String |
getSqlFrom2()
Get Method for class field 'sqlFrom2'.
|
String |
getSqlFrom3()
Get Method for class field 'sqlFrom3'.
|
String |
getSqlFrom4()
Get Method for class field 'sqlFrom4'.
|
String |
getSqlFrom5()
Get Method for class field 'sqlFrom3'.
|
String |
getSqlOrder()
Get Method for class field 'sqlOrder'.
|
String |
getSqlSelect()
Get Method for class field 'sqlSelect'.
|
String |
getSqlWhere()
Get Method for class field 'sqlWhere'.
|
static String |
getTimeDelta(String fromTimeStamp)
Creates a delta timestamp for the current time to the passed timestamp in the form of
'hour + "-" + min + "-" + sec + "-" + millis'.
|
static void |
main(String[] args) |
StringBuffer |
query(String sql)
Executes a SQL statement onto the JDBC accessed PRODUCTION database
defined by the class driver and connection string.
|
StringBuffer |
query(String sql,
boolean useProductionDb)
Executes a SQL statement onto the JDBC accessed database defined by the
class driver and connection string.
|
static String |
resultSetToCSVFile(ResultSet rs,
String fileName,
boolean includeColumnsHeaders)
Converts a ResultSet StringBuffer to a saved file that holds one result per line.
|
void |
saveChartToSVG(org.jfree.chart.JFreeChart chart,
String fileName,
int width,
int height)
Save chart as SVG file.
|
static WritableWorkbook |
sbIntoWorkbook(StringBuffer sb,
String wbkName,
String sheetName)
Adds a ResultSet StringBuffer into a NEW jxl.Workbook Excel workbook with the specified workbookName and sheetname.
|
static WritableWorkbook |
sbIntoWorkbook(StringBuffer sb,
WritableWorkbook wbkIn,
String sheetName)
Adds a ResultSet StringBuffer into a jxl.Workbook Excel workbook (new or existing) with the specified sheet name.
|
static String[] |
sbToArray(StringBuffer rs)
Converts a ResultSet StringBuffer to a String [] that holds one result per line.
|
static String[] |
sbToArray(StringBuilder rs)
Converts a ResultSet StringBuffer to a String [] that holds one result per line.
|
static String |
sbToCsv(StringBuffer rs,
String fileName)
Converts a ResultSet StringBuffer to a saved CSV file that holds one result per line.
|
static String[][] |
sbToDataArray(StringBuffer rs)
Converts a ResultSet StringBuffer to a String [][] that holds the results cell by cell ColumnxRow.
|
static String[][] |
sbToDataArray(StringBuilder rs)
Converts a ResultSet StringBuilder to a String [][] that holds the results cell by cell ColumnxRow.
|
static String[][] |
sbToDataRowArray(StringBuffer rs)
Converts a ResultSet StringBuffer to a String [][] that holds the results cell by cell RowxColumn.
|
static String[][] |
sbToDataRowArray(StringBuilder rs)
Converts a ResultSet StringBuilder to a String [][] that holds the results cell by cell RowxColumn.
|
static String |
sbToFile(StringBuffer rs,
String fileName)
Converts a ResultSet StringBuffer to a saved file that holds one result per line.
|
static String |
sbToJSON(StringBuffer contentSB)
Converts a sql resultSet StringBuffer in the form of a default delimited string to a JSON format.
|
static WritableSheet |
sbToSheet(StringBuffer sb,
String fileName)
Converts a ResultSet StringBuffer to a saved Excel file with one sheet named 'Results' that holds one result per line.
|
static WritableWorkbook |
sbToWbk(StringBuffer sb,
String fileName)
Converts a ResultSet StringBuffer to a saved Excel file with one sheet named 'Results' that holds one result per line.
|
static Workbook |
sbToWorkbook(StringBuffer sb)
Converts a ResultSet StringBuffer to a saved jxl.Workbook Excel workbook with one sheet named 'Results' that holds one result per line.
|
static Sheet |
sbToWorksheet(StringBuffer sb)
Converts a ResultSet StringBuffer to a saved jxl.Sheet Excel worksheet that holds one result per line.
|
static String |
sbToXLS(StringBuffer sb,
String fileName)
Converts a ResultSet StringBuffer to a saved Excel file with one sheet named 'Results' that holds one result per line.
|
void |
setCaseInsensitive()
Sets the queries to be case insensitive.this is an Oracle Var as of 10gR2
This is a very quick query.
|
void |
setCaseSensitive()
Sets the queries to be case sensitive.this is an Oracle Var as of 10gR2
This is a very quick query.
|
void |
setColumnDelimitor(String columnDelimitor)
Set Method for class field 'columnDelimitor'.
|
void |
setDbConnectString(String dbConnectString)
Set Method for class field 'dbConnectString'.
|
void |
setDbDriverName(String dbDriverName)
Set Method for class field 'dbDriverName'.
|
void |
setDbIP(String dbIP)
Set Method for class field 'dbIP'.
|
void |
setDbName(String dbName)
Set Method for class field 'dbName'.
|
void |
setDbPassword(String dbPassword)
Set Method for class field 'dbPassword'.
|
void |
setDbUser(String dbUser)
Set Method for class field 'dbUser'.
|
void |
setSqlFrom(String sqlFrom)
Set Method for class field 'sqlFrom'.
|
void |
setSqlFrom2(String sqlFrom2)
Set Method for class field 'sqlFrom2'.
|
void |
setSqlFrom3(String sqlFrom3)
Set Method for class field 'sqlFrom3'.
|
void |
setSqlFrom4(String sqlFrom4)
Set Method for class field 'sqlFrom4'.
|
void |
setSqlFrom5(String sqlFrom5)
Set Method for class field 'sqlFrom3'.
|
void |
setSqlOrder(String sqlOrder)
Set Method for class field 'sqlOrder'.
|
void |
setSqlSelect(String sqlSelect)
Set Method for class field 'sqlSelect'.
|
void |
setSqlWhere(String sqlWhere)
Set Method for class field 'sqlWhere'.
|
void |
setUseDb2DB()
Set the database to use Oracle DB.
|
void |
setUseDerbyDB()
Set the database to use Oracle DB.
|
void |
setUseHsqlDB()
Set the database to use PG.
|
void |
setUseMariadbDB()
Set the database to use Mysql.
|
void |
setUseMysqlDB()
Set the database to use Mysql.
|
void |
setUseOdbcDB()
Set the database to use the JDBC-ODBC bridge.
|
void |
setUseOracleDB()
Set the database to use Oracle DB.
|
void |
setUseOracleDedicatedDB()
Set the database to use Oracle DB with a dedicated connection to the server.
|
void |
setUsePostgreDB()
Set the database to use PG.
|
StringBuffer |
testDbQuery(String sql)
Executes a SQL statement onto the JDBC accessed TESTdatabase
defined by the class driver and connection string.
|
public static final String SYSTEM_FILE_SEPERATOR
public static final String SYSTEM_LINE_SEPERATOR
public static final String DEFAULT_COLUMN_DELIMITOR
protected static boolean debugOut
public static final short ORACLE_DB
public static final short POSTGRE_DB
public static final short MYSQL_DB
public static final short DB2_DB
public static final short DERBY_DB
public static final short HSQL_DB
public static final short ODBC_DB
public static final short ORACLEDEDICATED_DB
public static final short MARIADB_DB
public static final short OTHER_DB
public static final short DEFAULT_DB
protected static final String oraDbDriverName
protected static final String mysqlDbDriverName
protected static final String mariadbDbDriverName
protected static final String pgDbDriverName
protected static final String hsqlDbDriverName
protected static final String db2DbDriverName
protected static final String odbcDbDriverName
protected static final String proxoolDriverName
protected static final String derbyDbDriverName
protected static final String oraDbPort
protected static final String pgDbPort
protected static final String mysqlDbPort
protected static final String mariadbDbPort
protected static final String hsqlDbPort
protected static final String db2DbPort
protected static final String derbyDbPort
protected short dbType_
protected boolean useProxoolPool_
protected int proxoolMaximumConnectionCount_
protected boolean oraDedicated_
protected static String columnDelimitor
protected String dbPassword
protected String dbDriverName
protected String dbConnectString
protected String testDbConnectString
protected Connection con
protected long randLong
protected static int yr_
protected static int mo_
protected static int dy_
public SqlQuery()
public void setColumnDelimitor(String columnDelimitor)
columnDelimitor
- is the value to set this class field to.public static String getColumnDelimitor()
public void setSqlOrder(String sqlOrder)
sqlOrder
- is the value to set this class field to.public String getSqlOrder()
public void setSqlWhere(String sqlWhere)
sqlWhere
- is the value to set this class field to.public String getSqlWhere()
public void setSqlFrom(String sqlFrom)
sqlFrom
- is the value to set this class field to.public String getSqlFrom()
public void setSqlFrom2(String sqlFrom2)
sqlFrom2
- is the value to set this class field to.public String getSqlFrom2()
public void setSqlFrom3(String sqlFrom3)
sqlFrom3
- is the value to set this class field to.public String getSqlFrom3()
public void setSqlFrom4(String sqlFrom4)
sqlFrom4
- is the value to set this class field to.public String getSqlFrom4()
public void setSqlFrom5(String sqlFrom5)
sqlFrom5
- is the value to set this class field to.public String getSqlFrom5()
public void setSqlSelect(String sqlSelect)
sqlSelect
- is the value to set this class field to.public String getSqlSelect()
public void setDbConnectString(String dbConnectString)
dbConnectString
- is the value to set this class field to.public String getDbConnectString()
public void setDbDriverName(String dbDriverName)
dbDriverName
- is the value to set this class field to.public String getDbDriverName()
public void setDbPassword(String dbPassword)
dbPassword
- is the value to set this class field to.public String getDbPassword()
public void setDbUser(String dbUser)
dbUser
- is the value to set this class field to.public String getDbUser()
public String getDbIP()
public void setDbIP(String dbIP)
dbIP
- is the value to set this class field to.public void setDbName(String dbName)
dbName
- The value the class field 'dbName'.public String getDbName()
public StringBuffer testDbQuery(String sql)
sql
- is the SQL to executepublic static String[][] sbToDataArray(StringBuffer rs)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static String[][] sbToDataArray(StringBuilder rs)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static String[][] sbToDataRowArray(StringBuffer rs)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static String[][] sbToDataRowArray(StringBuilder rs)
rs
- is the SQL reusltSet (in StringBuilder Form as returned from any of the query methods) to convertpublic static String[] sbToArray(StringBuilder rs)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static String[] sbToArray(StringBuffer rs)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static String sbToFile(StringBuffer rs, String fileName)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertfileName
- is the name of the file to savepublic static String sbToCsv(StringBuffer rs, String fileName)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertfileName
- is the name of the file to savepublic static WritableWorkbook copyIntoWorkbook(StringBuffer sb, WritableWorkbook wbkIn, int shtPosition, String sheetName) throws IOException
IOException
public static WritableWorkbook sbIntoWorkbook(StringBuffer sb, WritableWorkbook wbkIn, String sheetName) throws IOException
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertwbkIn
- is an existing jxlWorkbook to save the StringBuffer resultSet into - If this is null then it creates and returns a new WorkbooksheetName
- name of the new sheet in the workbookIOException
public static WritableWorkbook sbIntoWorkbook(StringBuffer sb, String wbkName, String sheetName) throws IOException
sb
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertwbkName
- is the name of the new jxlWorkbook file to savesheetName
- name of the new sheet in the workbookIOException
public static Workbook sbToWorkbook(StringBuffer sb)
sb
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static Sheet sbToWorksheet(StringBuffer sb)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertpublic static String sbToXLS(StringBuffer sb, String fileName)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertfileName
- is the name of the file to savepublic static WritableWorkbook sbToWbk(StringBuffer sb, String fileName)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertfileName
- is the name of the file to savepublic static WritableSheet sbToSheet(StringBuffer sb, String fileName)
rs
- is the SQL reusltSet (in StringBuffer Form as returned from any of the query methods) to convertfileName
- is the name of the file to savepublic static String csvToJSON(String content, String separator)
content
- is the sql resultSet in the form of a deliminated stringseparator
- the split charpublic static String sbToJSON(StringBuffer contentSB)
contentSB
- is the sql resultSet in the form of a deliminated stringpublic static String resultSetToCSVFile(ResultSet rs, String fileName, boolean includeColumnsHeaders)
rs
- is the SQL resultSet to convertfileName
- is the name of the file to savepublic void setUseOracleDedicatedDB()
public void setUseOracleDB()
public void setUseDb2DB()
public void setUseDerbyDB()
public void setUsePostgreDB()
public void setUseMysqlDB()
public void setUseMariadbDB()
public void setUseHsqlDB()
public void setUseOdbcDB()
public void setCaseInsensitive()
public void setCaseSensitive()
public boolean canConnect()
public boolean canConnect(boolean useProductionDb)
useProductionDb
- flags whether the production or test/developmentDB get used.public static String getTimeDelta(String fromTimeStamp)
public static String createCurrentTimeStamp()
public StringBuffer query(String sql)
sql
- is the SQL to executepublic StringBuffer query(String sql, boolean useProductionDb)
FISCAL_YEAR|SUBSTATION_CD|FEEDER|FEEDER_CUSTOMERS|DTOR|CUSTS_OUT|CUST_OUTAGE_MIN|DEMAND_MW 2007|NVR|1260 NVR|218|P3835151.A|12|2172|null 2007|NVR|1260 NVR|218|P3868231.A|12|3864|null 2007|NVR|1261 NVR|2555|P3806671.B|152|21432|nullOther Helper Methods:
setColumnDelimitor
method.sbToArray
method.canConnect
method.sql
- is the SQL to executeuseProductionDb
- flags whether the production or test/developmentDB get used.public String convertSqlResultsToCSV(StringBuffer results)
results
- The sql results from the 'query' methodpublic boolean convertSqlResultsToXLS(StringBuffer results, String xlsFilename)
results
- The sql results from the 'query' methodxlsFilename
- The resulting xls filenamepublic void saveChartToSVG(org.jfree.chart.JFreeChart chart, String fileName, int width, int height)
chart
- JFreeChart to save.fileName
- Name of file to save chart in.width
- Width of chart graphic.height
- Height of chart graphic.Exception
- if failed.private org.jfree.data.category.DefaultCategoryDataset createDatasetFromSqlResults(StringBuffer sqlResults)
Coulmn1 Column2 Column3 Apr 10 task1 Apr 6 task2 Apr 8 task3 Apr 3 task4 Apr 25 task5 May 10 task1 May 6 task2 May 8 task3 May 3 task4 May 25 task5 Jun 10 task1 Jun 6 task2 Jun 8 task3 Jun 3 task4 Jun 25 task5
sqlResults
- is the sqlresults to use as the basis for the datasetpublic String generateCategoryBarChart(String sql, HttpSession session, PrintWriter pw, String chartTitle, String xAxisTitle, String yAxisTitle, boolean showLegend)
Coulmn1 Column2 Column3 Apr 10 task1 Apr 6 task2 Apr 8 task3 Apr 3 task4 Apr 25 task5 May 10 task1 May 6 task2 May 8 task3 May 3 task4 May 25 task5 Jun 10 task1 Jun 6 task2 Jun 8 task3 Jun 3 task4 Jun 25 task5
public String generatePieChart(String sql, HttpSession session, PrintWriter pw, String chartTitle, String xAxisTitle, String yAxisTitle)
Coulmn1 Column2 Apr 10 May 10 Jun 10
WebARTS Library Licensed Under the GNU - General Public License. Other Libraries licensed under their respective Open Source Licenses