svn-gvsig-desktop / tags / v1_0_RELEASE / libraries / libIverUtiles / src / com / iver / utiles / connections / ConnectionDB.java @ 9167
History | View | Annotate | Download (13.9 KB)
1 | 2874 | caballero | /* gvSIG. Sistema de Informaci?n Geogr?fica de la Generalitat Valenciana
|
---|---|---|---|
2 | *
|
||
3 | * Copyright (C) 2004 IVER T.I. and Generalitat Valenciana.
|
||
4 | *
|
||
5 | * This program is free software; you can redistribute it and/or
|
||
6 | * modify it under the terms of the GNU General Public License
|
||
7 | * as published by the Free Software Foundation; either version 2
|
||
8 | * of the License, or (at your option) any later version.
|
||
9 | *
|
||
10 | * This program is distributed in the hope that it will be useful,
|
||
11 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
12 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
||
13 | * GNU General Public License for more details.
|
||
14 | *
|
||
15 | * You should have received a copy of the GNU General Public License
|
||
16 | * along with this program; if not, write to the Free Software
|
||
17 | * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,USA.
|
||
18 | *
|
||
19 | * For more information, contact:
|
||
20 | *
|
||
21 | * Generalitat Valenciana
|
||
22 | * Conselleria d'Infraestructures i Transport
|
||
23 | * Av. Blasco Ib??ez, 50
|
||
24 | * 46010 VALENCIA
|
||
25 | * SPAIN
|
||
26 | *
|
||
27 | * +34 963862235
|
||
28 | * gvsig@gva.es
|
||
29 | * www.gvsig.gva.es
|
||
30 | *
|
||
31 | * or
|
||
32 | *
|
||
33 | * IVER T.I. S.A
|
||
34 | * Salamanca 50
|
||
35 | * 46005 Valencia
|
||
36 | * Spain
|
||
37 | *
|
||
38 | * +34 963163400
|
||
39 | * dac@iver.es
|
||
40 | */
|
||
41 | package com.iver.utiles.connections; |
||
42 | |||
43 | import java.io.File; |
||
44 | import java.io.FileInputStream; |
||
45 | import java.io.FileOutputStream; |
||
46 | import java.io.IOException; |
||
47 | import java.sql.Connection; |
||
48 | import java.sql.DatabaseMetaData; |
||
49 | import java.sql.DriverManager; |
||
50 | import java.sql.ResultSet; |
||
51 | import java.sql.SQLException; |
||
52 | import java.sql.Statement; |
||
53 | import java.util.ArrayList; |
||
54 | import java.util.Properties; |
||
55 | |||
56 | 8765 | jjdelcerro | import org.apache.commons.dbcp.ConnectionFactory; |
57 | import org.apache.commons.dbcp.DriverManagerConnectionFactory; |
||
58 | import org.apache.commons.dbcp.PoolableConnectionFactory; |
||
59 | import org.apache.commons.dbcp.PoolingDriver; |
||
60 | import org.apache.commons.pool.ObjectPool; |
||
61 | import org.apache.commons.pool.impl.GenericObjectPool; |
||
62 | 2874 | caballero | |
63 | 8765 | jjdelcerro | |
64 | 2874 | caballero | /**
|
65 | * Class responsible of the operations on the database.
|
||
66 | *
|
||
67 | * @author Vicente Caballero Navarro
|
||
68 | */
|
||
69 | public class ConnectionDB { |
||
70 | public static ConnectionDB instance = new ConnectionDB(); |
||
71 | private ConnectionTrans[] connTrans; |
||
72 | /**
|
||
73 | * Creates a new one ConnectionDB.
|
||
74 | */
|
||
75 | private ConnectionDB() {
|
||
76 | } |
||
77 | public void setConnTrans(ConnectionTrans[] ct){ |
||
78 | connTrans=ct; |
||
79 | } |
||
80 | /**
|
||
81 | * It returns a static instance of the class.
|
||
82 | *
|
||
83 | * @return instance
|
||
84 | */
|
||
85 | public static ConnectionDB getInstance() { |
||
86 | return instance;
|
||
87 | } |
||
88 | |||
89 | /**
|
||
90 | * It verifies that the connection is correct
|
||
91 | *
|
||
92 | * @param ct Data of the connection
|
||
93 | * @param driver Driver
|
||
94 | *
|
||
95 | * @throws ConnectionException
|
||
96 | */
|
||
97 | public boolean testDB(ConnectionTrans ct) |
||
98 | throws ConnectionException {
|
||
99 | /*try {
|
||
100 | Class.forName(driver.getDriverString());
|
||
101 | } catch (java.lang.ClassNotFoundException e) {
|
||
102 | throw new ConnectionException("Driver no encontrado", e);
|
||
103 | }
|
||
104 | */
|
||
105 | String url = ct.getConnBeginning() + "//" + |
||
106 | ct.getHost() + ":" + ct.getPort() + "/" + ct.getDb(); |
||
107 | String user = ct.getUser();
|
||
108 | String password = ct.getPassword();
|
||
109 | ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url,
|
||
110 | user, password); |
||
111 | Connection connection = null; |
||
112 | |||
113 | try {
|
||
114 | connection = connectionFactory.createConnection(); |
||
115 | } catch (SQLException e) { |
||
116 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_crear_conexion"), e); |
117 | 2874 | caballero | } catch (Exception e) { |
118 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_crear_conexion"), e); |
119 | 2874 | caballero | } |
120 | |||
121 | try {
|
||
122 | connection.close(); |
||
123 | } catch (SQLException e) { |
||
124 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_crear_conexion"), e); |
125 | 2874 | caballero | } |
126 | return true; |
||
127 | } |
||
128 | |||
129 | /**
|
||
130 | * Returns a connection by Name.
|
||
131 | *
|
||
132 | * @param url_name Name of connection
|
||
133 | *
|
||
134 | * @return Connection
|
||
135 | *
|
||
136 | * @throws ConnectionException
|
||
137 | */
|
||
138 | public Connection getConnectionByName(String url_name) |
||
139 | throws ConnectionException {
|
||
140 | Connection conn = null; |
||
141 | |||
142 | try {
|
||
143 | conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + |
||
144 | url_name); |
||
145 | } catch (SQLException e) { |
||
146 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_obtener_conexion_existente"), |
147 | 2874 | caballero | e); |
148 | } |
||
149 | |||
150 | return conn;
|
||
151 | } |
||
152 | |||
153 | /**
|
||
154 | * Returns a connection from the data
|
||
155 | *
|
||
156 | * @param ct Data connection
|
||
157 | *
|
||
158 | * @return Connection
|
||
159 | *
|
||
160 | * @throws ConnectionException
|
||
161 | */
|
||
162 | public Connection getConnection(ConnectionTrans ct) |
||
163 | throws ConnectionException {
|
||
164 | //VectorialJDBCDriver driver = getDriver(ct.getDriver());
|
||
165 | |||
166 | String name = ct.getHost() + "_" + ct.getName(); |
||
167 | Connection conn = null; |
||
168 | |||
169 | /* try {
|
||
170 | Class.forName(driver.getDriverString());
|
||
171 | } catch (ClassNotFoundException e) {
|
||
172 | throw new ConnectionException("Driver no encontrado", e);
|
||
173 | }
|
||
174 | */
|
||
175 | setupDriver(ct); |
||
176 | conn = getConnectionByName(name); |
||
177 | |||
178 | return conn;
|
||
179 | } |
||
180 | |||
181 | /**
|
||
182 | * Returns a Driver by name.
|
||
183 | *
|
||
184 | * @param name Driver?s name
|
||
185 | *
|
||
186 | * @return Driver
|
||
187 | *
|
||
188 | * @throws ConnectionException
|
||
189 | */
|
||
190 | /* public VectorialJDBCDriver getDriver(String name)
|
||
191 | throws ConnectionException {
|
||
192 | VectorialJDBCDriver driver = null;
|
||
193 | |||
194 | String[] drivers = LayerFactory.getDM().getDriverNames();
|
||
195 | |||
196 | for (int i = 0; i < drivers.length; i++) {
|
||
197 | try {
|
||
198 | if (LayerFactory.getDM().getDriver(drivers[i]) instanceof VectorialJDBCDriver) {
|
||
199 | VectorialJDBCDriver d = (VectorialJDBCDriver) LayerFactory.getDM()
|
||
200 | .getDriver(drivers[i]);
|
||
201 | |||
202 | if (d.getName().equals(name)) {
|
||
203 | driver = d;
|
||
204 | }
|
||
205 | }
|
||
206 | } catch (DriverLoadException e) {
|
||
207 | throw new ConnectionException("Driver no encontrado", e);
|
||
208 | }
|
||
209 | }
|
||
210 | |||
211 | return driver;
|
||
212 | }
|
||
213 | */
|
||
214 | /**
|
||
215 | * Registers in the driverpool a new connection
|
||
216 | *
|
||
217 | * @param ct Data connection
|
||
218 | * @param driver Driver
|
||
219 | *
|
||
220 | * @throws ConnectionException
|
||
221 | */
|
||
222 | public void setupDriver(ConnectionTrans ct) |
||
223 | throws ConnectionException {
|
||
224 | String url = ct.getConnBeginning() + "//" + |
||
225 | ct.getHost() + ":" + ct.getPort() + "/" + ct.getDb(); |
||
226 | String user = ct.getUser();
|
||
227 | String password = ct.getPassword();
|
||
228 | String name = ct.getHost() + "_" + ct.getName(); |
||
229 | ObjectPool connectionPool = new GenericObjectPool();
|
||
230 | ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url,
|
||
231 | user, password); |
||
232 | |||
233 | try {
|
||
234 | PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,
|
||
235 | connectionPool, null, null, false, true); |
||
236 | } catch (Exception e) { |
||
237 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_crear_pool"), |
238 | 2874 | caballero | e); |
239 | } |
||
240 | |||
241 | try {
|
||
242 | Class.forName("org.apache.commons.dbcp.PoolingDriver"); |
||
243 | } catch (ClassNotFoundException e) { |
||
244 | throw new ConnectionException("Clase : " + |
||
245 | "org.apache.commons.dbcp.PoolingDriver", e);
|
||
246 | } |
||
247 | |||
248 | PoolingDriver driverPool; |
||
249 | |||
250 | try {
|
||
251 | driverPool = (PoolingDriver) DriverManager.getDriver(
|
||
252 | "jdbc:apache:commons:dbcp:");
|
||
253 | } catch (SQLException e) { |
||
254 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_registrar_conexion"), e); |
255 | 2874 | caballero | } |
256 | |||
257 | driverPool.registerPool(name, connectionPool); |
||
258 | ct.setConnected(true);
|
||
259 | } |
||
260 | |||
261 | /**
|
||
262 | * Executes a query
|
||
263 | *
|
||
264 | * @param SQL query
|
||
265 | * @param name Driver?s name
|
||
266 | *
|
||
267 | * @throws ConnectionException
|
||
268 | */
|
||
269 | public void ejecutaSQLnors(String SQL, String name) |
||
270 | throws ConnectionException {
|
||
271 | Connection conn = null; |
||
272 | Statement s = null; |
||
273 | |||
274 | try {
|
||
275 | try {
|
||
276 | conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + |
||
277 | name); |
||
278 | |||
279 | s = conn.createStatement(); |
||
280 | s.execute(SQL); |
||
281 | } catch (SQLException e) { |
||
282 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_realizar_consulta"), |
283 | 2874 | caballero | e); |
284 | } |
||
285 | } finally {
|
||
286 | try {
|
||
287 | s.close(); |
||
288 | } catch (Exception e) { |
||
289 | } |
||
290 | |||
291 | try {
|
||
292 | conn.close(); |
||
293 | } catch (Exception e) { |
||
294 | } |
||
295 | } |
||
296 | } |
||
297 | |||
298 | /**
|
||
299 | * Returns the names of the tables
|
||
300 | *
|
||
301 | * @param conn Connection
|
||
302 | *
|
||
303 | * @return Array of string with the names of the tables.
|
||
304 | *
|
||
305 | * @throws ConnectionException
|
||
306 | */
|
||
307 | public String[] getTableNames(Connection conn) throws ConnectionException { |
||
308 | //Connection conn=getConnectionByName(name);
|
||
309 | ArrayList tableNames = new ArrayList(); |
||
310 | String nombreTablas = "%"; // Listamos todas las tablas |
||
311 | String[] tipos = new String[1]; // Listamos s?lo tablas |
||
312 | tipos[0] = "TABLE"; |
||
313 | |||
314 | try {
|
||
315 | DatabaseMetaData dbmd = conn.getMetaData();
|
||
316 | ResultSet tablas = dbmd.getTables(null, null, nombreTablas, tipos); |
||
317 | |||
318 | boolean seguir = tablas.next();
|
||
319 | |||
320 | while (seguir) {
|
||
321 | // Mostramos s?lo el nombre de las tablas, guardado
|
||
322 | // en la columna "TABLE_NAME"
|
||
323 | System.out.println(tablas.getString(tablas.findColumn(
|
||
324 | "TABLE_NAME")));
|
||
325 | tableNames.add(tablas.getString(tablas.findColumn("TABLE_NAME")));
|
||
326 | seguir = tablas.next(); |
||
327 | } |
||
328 | } catch (SQLException e) { |
||
329 | 2908 | caballero | throw new ConnectionException(JDBCManager.getTranslation("fallo_obtener_tablas"), |
330 | 2874 | caballero | e); |
331 | } |
||
332 | |||
333 | return (String[]) tableNames.toArray(new String[0]); |
||
334 | } |
||
335 | |||
336 | /**
|
||
337 | * Keeps in disk the data of the connection
|
||
338 | *
|
||
339 | * @param ct Data connection
|
||
340 | *
|
||
341 | * @throws IOException
|
||
342 | */
|
||
343 | public void setPersistence(ConnectionTrans ct) throws IOException { |
||
344 | String name = ct.getHost() + "_" + ct.getName(); |
||
345 | Properties properties = new Properties(); |
||
346 | properties.put("jdbc.drivers", ct.getDriver());
|
||
347 | properties.put("jdbc.name", ct.getName());
|
||
348 | properties.put("jdbc.host", ct.getHost());
|
||
349 | properties.put("jdbc.port", ct.getPort());
|
||
350 | properties.put("jdbc.username", ct.getUser());
|
||
351 | properties.put("jdbc.savepassword", String.valueOf(ct.isSavePassword())); |
||
352 | |||
353 | if (ct.isSavePassword()) {
|
||
354 | properties.put("jdbc.password", ct.getPassword());
|
||
355 | } |
||
356 | |||
357 | properties.put("jdbc.database", ct.getDb());
|
||
358 | properties.put("jdbc.connBeginning",ct.getConnBeginning());
|
||
359 | boolean success = true; |
||
360 | File file = null; |
||
361 | 7693 | luisw2 | /*
|
362 | * TODO LWS: esto tiene pinta de no ser portable (case, en linux). Ahora
|
||
363 | * en el Launcher de Andami hay un appHome, pero si lo pongo aqui ya estamos metiendo
|
||
364 | * dependencias circulares ...
|
||
365 | */
|
||
366 | 2874 | caballero | String directory = System.getProperty("user.home") + "\\" + "gvsig" + |
367 | "\\" + "connections"; |
||
368 | |||
369 | if (!new File(directory).exists()) { |
||
370 | file = new File(directory); |
||
371 | success = file.mkdirs(); |
||
372 | } |
||
373 | |||
374 | if (success) {
|
||
375 | File f = new File(directory + "/" + name + ".properties"); |
||
376 | f.createNewFile(); |
||
377 | |||
378 | FileOutputStream out = new FileOutputStream(f); |
||
379 | properties.store(out, name); |
||
380 | out.close(); |
||
381 | } |
||
382 | } |
||
383 | public void delPersistence(String name){ |
||
384 | String directory = System.getProperty("user.home") + "\\" + "gvsig" + |
||
385 | "\\" + "connections"; |
||
386 | File dir = new File(directory); |
||
387 | File[] files = dir.listFiles(); |
||
388 | for (int i = 0; i < files.length; i++) { |
||
389 | if (files[i].getName().substring(0,files[i].getName().length()-11).equals(name)){ |
||
390 | files[i].delete(); |
||
391 | } |
||
392 | } |
||
393 | } |
||
394 | /**
|
||
395 | * Returns the data of the connection
|
||
396 | *
|
||
397 | * @return Array of data connections
|
||
398 | *
|
||
399 | * @throws IOException
|
||
400 | */
|
||
401 | public ConnectionTrans[] getPersistence() throws IOException { |
||
402 | ArrayList conns = new ArrayList(); |
||
403 | String directory = System.getProperty("user.home") + "\\" + "gvsig" + |
||
404 | "\\" + "connections"; |
||
405 | File dir = new File(directory); |
||
406 | File[] files = dir.listFiles(); |
||
407 | 3080 | fjp | if (files == null) |
408 | return null; |
||
409 | 2874 | caballero | for (int i = 0; i < files.length; i++) { |
410 | Properties properties = new Properties(); |
||
411 | FileInputStream in = new FileInputStream(files[i]); |
||
412 | properties.load(in); |
||
413 | in.close(); |
||
414 | |||
415 | ConnectionTrans ct = new ConnectionTrans();
|
||
416 | ct.setDriver(properties.get("jdbc.drivers").toString());
|
||
417 | ct.setName(properties.get("jdbc.name").toString());
|
||
418 | ct.setHost(properties.get("jdbc.host").toString());
|
||
419 | ct.setPort(properties.get("jdbc.port").toString());
|
||
420 | ct.setUser(properties.get("jdbc.username").toString());
|
||
421 | |||
422 | boolean isSave = Boolean.valueOf(properties.get("jdbc.savepassword") |
||
423 | .toString()) |
||
424 | .booleanValue(); |
||
425 | ct.setSavePassword(isSave); |
||
426 | |||
427 | if (isSave) {
|
||
428 | ct.setPassword(properties.get("jdbc.password").toString());
|
||
429 | } |
||
430 | |||
431 | ct.setDb(properties.get("jdbc.database").toString());
|
||
432 | ct.setConnBegining(properties.get("jdbc.connBeginning").toString());
|
||
433 | conns.add(ct); |
||
434 | } |
||
435 | |||
436 | return (ConnectionTrans[]) conns.toArray(new ConnectionTrans[0]); |
||
437 | } |
||
438 | public ConnectionTrans[] getDefaultTrans() { |
||
439 | return connTrans;
|
||
440 | } |
||
441 | |||
442 | } |