|
/Users/lyon/j4p/src/net/server/servlets/SQLBeanie.java
|
1 package net.server.servlets;
2
3 // package pFormC;
4
5 import java.sql.*;
6 import java.util.Vector;
7
8 public class SQLBeanie {
9
10 private String driver = "org.gjt.mm.mysql.Driver";
11 private String url = "jdbc:mysql://localhost:3306/test";
12 // private String userId = "DeltaHall";
13 private String userId = null;
14 private String password = null;
15 // private String password = "Pongo1";
16
17 private Connection cn;
18 private Statement stmt;
19 // private ResultSet rs;
20
21
22 // private String url = "jdbc:mysql://JHVY501/test";
23 // private String url = "jdbc:odbc:db1";
24 // private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
25 // private String userId = "Admin";
26 // private String password = "";
27
28 private Connection c;
29 private DatabaseMetaData dbmd;
30 private boolean isReadOnly = false;
31 private boolean usesLocalFiles = false;
32 private String driverName = null;
33 private String catalogName = null;
34 private String productName = null;
35
36 public boolean getUsesLocalFiles() {
37 return usesLocalFiles;
38 }
39
40 public boolean getReadOnly() {
41 return isReadOnly;
42 }
43
44 public String getCatalogName() {
45 return catalogName;
46 }
47
48 public String getDriverName() {
49 return driverName;
50 }
51
52 public String getProductName() {
53 return productName;
54 }
55
56 public void setUserId(String _userId) {
57 userId = _userId;
58 }
59
60 public void setPassword(String _password) {
61 password = _password;
62 }
63
64 public void setUrl(String _url) {
65 url = _url;
66 }
67
68 public void setDriver(String _driver) {
69 driver = _driver;
70 }
71
72 public SQLBeanie() {
73 }
74
75 public static void main(String args[]) {
76 SQLBeanie s = new SQLBeanie();
77 System.out.println("will try to open");
78 s.open();
79 s.printInfo();
80 System.out.println("I opened!");
81 s.close();
82 System.out.println("I closed");
83 }
84
85 public void open() {
86 try {
87 Class.forName(driver);
88 c = DriverManager.getConnection(
89 url, userId, password);
90 dbmd = c.getMetaData();
91 catalogName =
92 c.getCatalog();
93 isReadOnly = c.isReadOnly();
94 usesLocalFiles =
95 dbmd.usesLocalFiles();
96 driverName =
97 dbmd.getDriverName();
98 productName =
99 dbmd.getDatabaseProductName();
100 stmt = c.createStatement();
101 } catch (ClassNotFoundException e) {
102 e.printStackTrace();
103 } catch (SQLException e) {
104 e.printStackTrace();
105 }
106 System.out.println("Opened Connection:" + url);
107 }
108
109 public void printInfo() {
110 println("productName=" + productName);
111 println("catalogName=" + catalogName);
112 println("is ReadOnly=" + getReadOnly());
113 println("usesLocalFiles=" + getUsesLocalFiles());
114 println("driverName=" + driverName);
115 }
116
117 public void close() {
118 try {
119 c.close();
120 System.out.println("closed connection");
121 } catch (SQLException e) {
122 e.printStackTrace();
123 }
124 }
125
126 private ResultSet rs;
127 private ResultSetMetaData rsmd;
128
129 public void execute(String sql) {
130 rs = query(sql);
131 rsmd = getResultSetMetaData(rs);
132 }
133
134 public String[][] getResults() {
135 if (rs == null) return null;
136 if (rsmd == null) return null;
137 String names[] = getColumnNames(rsmd);
138 int c = names.length;
139 Vector v = new Vector();
140 v.add(names);
141 while (nextRow(rs)) {
142 v.add(getRowAsString(rs));
143 }
144 int r = v.size();
145 String sa[][] = new String[r][c];
146 for (int x = 0; x < r; x++) {
147 String s[] = (String[]) v.elementAt(x);
148 for (int y = 0; y < c; y++) {
149 sa[x][y] = s[y];
150 }
151 }
152 return sa;
153
154 }
155
156
157 public ResultSet query(String sql) {
158 try {
159 Statement s = c.createStatement();
160 return s.executeQuery(sql);
161 } catch (SQLException e) {
162 e.printStackTrace();
163 }
164 return null;
165 }
166
167 public ResultSetMetaData
168 getResultSetMetaData(ResultSet rs) {
169 try {
170 return rs.getMetaData();
171 } catch (SQLException e) {
172 e.printStackTrace();
173 }
174 return null;
175 }
176
177 public int getColumnCount(
178 ResultSetMetaData rsmd) {
179 try {
180 return rsmd.getColumnCount();
181 } catch (SQLException e) {
182 e.printStackTrace();
183 }
184 return 0;
185 }
186
187 public String[] getcolumnTypeNames(
188 ResultSetMetaData rsmd) {
189 int count = getColumnCount(rsmd);
190 String sa [] = new String[count];
191 try {
192 for (int i = 0; i < sa.length; i++) {
193 sa[i] = rsmd.getColumnTypeName(i + 1);
194 }
195 } catch (SQLException e) {
196 e.printStackTrace();
197 }
198 return sa;
199 }
200
201 public String[] getRowAsString(ResultSet rs) {
202
203 int N = getColumnCount(
204 getResultSetMetaData(rs));
205 String s[] = new String[N];
206 try {
207 for (int i = 0; i < N; i++) {
208 s[i] = rs.getString(i + 1);
209 //deh
210 System.out.println(s[i]);
211 }
212 //deh
213 } catch (SQLException e) {
214 e.printStackTrace();
215 }
216 return s;
217 }
218
219 public void print(ResultSet rs) {
220 int i;
221 String cn[] = getColumnNames(
222 getResultSetMetaData(rs));
223 println(cn);
224 boolean more = false;
225 while (more = nextRow(rs))
226 println(getRowAsString(rs));
227 }
228
229 public void println(Object o) {
230 System.out.println(o);
231 }
232
233 public void println(String s[]) {
234 for (int i = 0; i < s.length; i++)
235 System.out.print(s[i] + '\t');
236 System.out.println();
237 }
238
239 public void println(String s[][]) {
240 for (int r = 0; r < s.length; r++) {
241 for (int c = 0; c < s[r].length; c++)
242 System.out.print(s[r][c] + '\t');
243 System.out.println();
244 }
245 }
246
247 public boolean nextRow(ResultSet rs) {
248 try {
249 return rs.next();
250 } catch (SQLException e) {
251 return false;
252 }
253 }
254
255 public String[] getColumnNames(ResultSetMetaData rsmd) {
256 String s []
257 = new String[getColumnCount(rsmd)];
258 try {
259 for (int i = 1; i <= s.length; i++)
260 s[i - 1] = rsmd.getColumnLabel(i);
261 } catch (SQLException e) {
262 e.printStackTrace();
263 }
264
265 return s;
266 }
267
268 public DatabaseMetaData getDatabaseMetaData() {
269 return dbmd;
270 }
271
272 public void insert(String sql) {
273 try {
274 Statement s = c.createStatement();
275 int insertResult = s.executeUpdate(sql);
276 } catch (SQLException e) {
277 e.printStackTrace();
278 }
279 }
280 /* public static void main(String args[]) {
281 SqlBeanie sb = new SqlBeanie();
282 sb.open();
283
284
285 sb.execute(
286 "SELECT FirstName, LastName FROM mailingList"
287 );
288 sb.println(sb.getResults());
289 sb.close();
290 }
291 */
292 /**
293 * Return true/false
294 *
295 * @parm String sql
296 * @return boolean
297 */
298
299 public boolean checkRecordExists(String sql) {
300
301 try {
302 open();
303 System.out.println("The SQL is " + sql);
304 rs = stmt.executeQuery(sql);
305 close();
306 return rs.next();
307 }
308 // catch (ClassNotFoundException cnf) {
309 // System.out.println("\n ClassNotFound");
310 // cnf.printStackTrace();
311 // return false;
312 // }
313 catch (SQLException sqle) {
314 System.out.println("\n checkRecordExists Failure");
315 // sqle.printStackTrace();
316 return false;
317 } catch (Exception e) {
318 System.out.println("\n Exception");
319 e.printStackTrace();
320 return false;
321 }
322 }
323
324 }
325