1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package com.philemonworks.selfdiagnose.check;
18
19 import java.sql.Connection;
20 import java.sql.PreparedStatement;
21 import java.sql.ResultSet;
22 import java.sql.SQLException;
23 import java.util.Arrays;
24
25 import org.apache.log4j.Logger;
26 import org.xml.sax.Attributes;
27
28 import com.philemonworks.selfdiagnose.DiagnoseException;
29 import com.philemonworks.selfdiagnose.DiagnoseUtil;
30 import com.philemonworks.selfdiagnose.DiagnosticTaskResult;
31 import com.philemonworks.selfdiagnose.ExecutionContext;
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 public class CheckDatabaseTableExists extends com.philemonworks.selfdiagnose.DiagnosticTask {
50 private static final long serialVersionUID = 3171806700398661642L;
51 private static final String PARAMETER_DATASOURCE = "datasource";
52 private static final String PARAMETER_NAME = "name";
53 private static final String PARAMETER_QUERY = "queryname";
54 private static final String[] PARAMETER_QUERY_SORTED_VALUES = new String[]{"IN_ALL_TABLES","COUNT","FETCH_FIRST"};
55
56 private static final String QUERY_FETCH_FIRST = "SELECT COUNT(*) FROM {0} FETCH FIRST 1 ROWS ONLY";
57 private static final String QUERY_COUNT = "SELECT COUNT(*) FROM {0}";
58 private static final String QUERY_ALL_TABLES = "SELECT COUNT(*) FROM all_tables WHERE table_name = ''{0}''";
59 private String tableName = null;
60 private String datasource;
61 private String queryTemplate = QUERY_COUNT;
62
63
64
65
66 public String getDescription() {
67 return "Check that a table is defined in a database.";
68 }
69
70 public void setUp(ExecutionContext ctx) throws DiagnoseException {
71 super.setUp(ctx);
72 DiagnoseUtil.verifyNonEmptyString(PARAMETER_NAME, tableName, CheckDatabaseTableExists.class);
73 DiagnoseUtil.verifyNonEmptyString(PARAMETER_DATASOURCE, datasource, CheckDatabaseTableExists.class);
74 DiagnoseUtil.verifyNotNull("queryTemplate", queryTemplate, CheckDatabaseTableExists.class);
75 int index = Arrays.binarySearch(PARAMETER_QUERY_SORTED_VALUES, queryTemplate);
76 if (index == 0) {
77 throw new DiagnoseException("CheckDatabaseTableExists parameter [" + PARAMETER_QUERY + "] has an invalid value. Must be one of "
78 + PARAMETER_QUERY_SORTED_VALUES);
79 }
80 }
81
82 public void run(ExecutionContext ctx, DiagnosticTaskResult result) throws DiagnoseException {
83 Connection con = null;
84 try {
85 con = DiagnoseUtil.getDataSourceConnection(datasource);
86 PreparedStatement stmt = con.prepareStatement(this.getQuery());
87 this.runStatement(result, stmt);
88 } catch (SQLException ex) {
89 result.setErrorMessage(DiagnoseUtil.format(
90 "Error in detecting table exists via datasource [{1}] because: {2}",
91 datasource, DiagnoseUtil.getErrorMessage(ex)));
92 } finally {
93 if (con != null) {
94 try {
95 con.close();
96 } catch (SQLException ex) {
97 Logger.getLogger(this.getClass()).warn("Exception during connection close:"+ex.getMessage());
98 }
99 }
100 }
101 }
102
103 private void runStatement(DiagnosticTaskResult result, PreparedStatement stmt){
104 boolean exists = false;
105 try {
106 ResultSet rst = stmt.executeQuery();
107 exists = rst.next();
108 if (exists) {
109 result.setPassedMessage(DiagnoseUtil.format(
110 "Table [{0}] exists via datasource [{1}]",tableName,datasource));
111 } else {
112 result.setPassedMessage(DiagnoseUtil.format(
113 "Table [{0}] exists via datasource [{1}] but is empty",tableName,datasource));
114 }
115 } catch (SQLException ex) {
116
117 exists = false;
118 }
119 if (!exists) {
120 result.setFailedMessage(DiagnoseUtil.format(
121 "No such table [{0}] exists via datasource [{1}]",
122 tableName,datasource));
123 }
124 }
125
126 public void initializeFromAttributes(Attributes attributes) {
127 this.setTableName(attributes.getValue(PARAMETER_NAME));
128 this.setDatasource(attributes.getValue(PARAMETER_DATASOURCE));
129 String queryNameOrNull = attributes.getValue(PARAMETER_QUERY);
130 if (queryNameOrNull != null)
131 if ("IN_ALL_TABLES".equals(queryNameOrNull))
132 this.setQueryTemplate(QUERY_ALL_TABLES);
133 else if ("COUNT".equals(queryNameOrNull))
134 this.setQueryTemplate(QUERY_COUNT);
135 else if ("FETCH_FIRST".equals(queryNameOrNull))
136 this.setQueryTemplate(QUERY_FETCH_FIRST);
137 super.initializeFromAttributes(attributes);
138 }
139
140 public String getQuery(){
141 return DiagnoseUtil.format(queryTemplate,tableName);
142 }
143
144 public String getDatasource() {
145 return datasource;
146 }
147
148 public void setDatasource(String datasource) {
149 this.datasource = datasource;
150 }
151
152 public String getTableName() {
153 return tableName;
154 }
155
156 public void setTableName(String tableName) {
157 this.tableName = tableName;
158 }
159
160 public String getQueryTemplate() {
161 return queryTemplate;
162 }
163
164 public void setQueryTemplate(String queryTemplate) {
165 this.queryTemplate = queryTemplate;
166 }
167 }