PageRenderTime 255ms CodeModel.GetById 111ms app.highlight 51ms RepoModel.GetById 90ms app.codeStats 0ms

/src/mpv5/db/common/DatabaseConnection.java

http://mp-rechnungs-und-kundenverwaltung.googlecode.com/
Java | 324 lines | 225 code | 32 blank | 67 comment | 27 complexity | bf2cced830aee14a82aff648b02f19b2 MD5 | raw file
  1/*
  2 * To change this template, choose Tools | Templates
  3 * and open the template in the editor.
  4 */
  5package mpv5.db.common;
  6
  7import java.sql.Connection;
  8import java.sql.Driver;
  9import java.sql.DriverManager;
 10import java.sql.SQLException;
 11import java.sql.Statement;
 12import java.util.logging.Level;
 13import java.util.logging.Logger;
 14import javax.swing.JProgressBar;
 15import mpv5.Main;
 16import mpv5.db.objects.User;
 17import mpv5.globals.LocalSettings;
 18import mpv5.globals.Messages;
 19import mpv5.logging.Log;
 20import mpv5.ui.dialogs.Popup;
 21
 22/**
 23 *
 24 *
 25 */
 26public class DatabaseConnection {
 27
 28    private static java.sql.Connection conn;
 29    private static DatabaseConnection connector;
 30
 31    /**
 32     * @return the connector
 33     */
 34    public static DatabaseConnection getConnector() {
 35        return connector;
 36    }
 37
 38    /**
 39     * @return the user
 40     */
 41    public static String getUser() {
 42        return user;
 43    }
 44
 45    /**
 46     * @return the password
 47     */
 48    public static String getPassword() {
 49        return password;
 50    }
 51
 52    /**
 53     * @return the prefix
 54     */
 55    public static String getPrefix() {
 56        return prefix;
 57    }
 58    private ConnectionTypeHandler ctype;
 59    private static String user;
 60    private static String password;
 61    private static String prefix = "";
 62
 63    /**
 64     *
 65     * @return Database connector
 66     * @throws Exception
 67     */
 68    public static synchronized DatabaseConnection instanceOf() throws Exception {
 69        if (getConnector() == null) {
 70            connector = new DatabaseConnection();
 71            getConnector().connect(false);
 72            new connectionPing(connector).start();
 73        }
 74        return getConnector();
 75    }
 76    private Statement statement;
 77    private JProgressBar prog;
 78
 79    /**
 80     *
 81     * @return
 82     */
 83    public java.sql.Connection getConnection() {
 84        return conn;
 85    }
 86
 87    /**
 88     * Test-Verbindung zur Datenbank herstellen.
 89     *
 90     * @param predefinedDriver
 91     * @param user
 92     * @param password
 93     * @param location
 94     * @param dbname
 95     * @param create
 96     * @return Connection
 97     * @throws Exception
 98     */
 99    public boolean connect(String predefinedDriver, String user, String password, String location, String dbname, String prefix, boolean create) throws Exception {
100
101        try {
102            ctype = new ConnectionTypeHandler();
103            getCtype().setDRIVER(predefinedDriver);
104            getCtype().setURL(location);
105            getCtype().setDBName(dbname);
106            getCtype().setPrefix(prefix);
107            DatabaseConnection.user = user;
108            DatabaseConnection.password = password;
109            DatabaseConnection.prefix = prefix;
110
111            DriverManager.registerDriver((Driver) Class.forName(getCtype().getDriver()).newInstance());
112            Log.Debug(this, "Driver: " + getCtype().getDriver());
113        } catch (Exception ex) {
114            Log.Debug(this, ex.getMessage());
115        }
116
117        return reconnect(create);
118    }
119
120    public boolean reconnect(boolean create) throws SQLException {
121        Statement stmt;
122        String sql;
123        try {
124            Log.Debug(this, "RECONNECT::Datenbankverbindung: " + getCtype().getConnectionString(create));
125            conn = DriverManager.getConnection(getCtype().getConnectionString(create), user, password);
126            conn.setAutoCommit(true);
127            if (conn != null //&& conn.isValid(10)//does not work with MySQL Connector/J 5.0
128                    ) {//mysql (and others) need explicit create database, derby does it by itself
129                if (create && ConnectionTypeHandler.getDriverType() != ConnectionTypeHandler.DERBY) {
130                    stmt = conn.createStatement();
131                    conn.setCatalog(ConnectionTypeHandler.getDBNAME());
132                    if (User.PROPERTIES_OVERRIDE.hasProperty("drop_database_on_create")) {
133                        try {
134                            sql = "DROP DATABASE "
135                                    + ConnectionTypeHandler.getDBNAME()
136                                    + ";";
137                            stmt.execute(sql);
138                        } catch (SQLException ex) {
139                            Log.Debug(this, "Database Error Cleaing of old DB failed!");
140                        }
141                    }
142                    try {
143                        sql = "CREATE DATABASE "
144                                + ConnectionTypeHandler.getDBNAME()
145                                + " ;";
146                        stmt.execute(sql);
147                    } catch (SQLException ex) {
148                        Popup.OK_dialog(Messages.CREATE_DATABASE_OWN.toString(), "Database Creation");
149                        return false;
150                    }
151
152                }
153                connector = this;
154                return true;
155            } else {
156                throw new RuntimeException("Could not create connection: " + getCtype().getConnectionString(create));
157            }
158        } catch (SQLException ex) {
159            System.out.println("Database Error: " + ex.getMessage());
160            Popup.notice(ex.getLocalizedMessage());
161            Log.Debug(this, ex);
162            Log.Debug(this, ex.getNextException());
163
164            DatabaseConnection.shutdown();
165            throw ex;
166        }
167    }
168
169    /**
170     * Verbindung zur Datenbank herstellen.
171     *
172     * @return Connection
173     */
174    private Connection connect(boolean create) throws Exception {
175
176        ctype = new ConnectionTypeHandler();
177
178        if (getCtype().getDriver() != null) {
179            Log.Debug(this, "Datenbanktreiber: " + getCtype().getDriver());
180
181            try {
182                Class.forName(getCtype().getDriver()).newInstance();
183            } catch (ClassNotFoundException ex) {
184//                Popup.error(ex);
185                //possibly not fatal, driver can have a different class [org.apache.derby.jdbc.ClientDriver,org.apache.derby.jdbc.EmbeddedDriver]
186                Log.Debug(this, ex.getMessage());
187            }
188            user = LocalSettings.getProperty("dbuser");
189            password = LocalSettings.getProperty("dbpassword");
190            prefix = LocalSettings.getProperty("dbprefix");
191
192            reconnect(create);
193        } else {
194            throw new UnsupportedOperationException("Datenbanktreiber: undefined");
195        }
196        return conn;
197    }
198
199    /**
200     * Verbindung trennen
201     */
202    @SuppressWarnings("CallToThreadDumpStack")
203    public static void shutdown() {
204        try {
205            if (conn != null && !conn.isClosed()) {
206                if (ConnectionTypeHandler.getDriverType() == ConnectionTypeHandler.DERBY) {
207                    try {
208                        DriverManager.getConnection(
209                                DatabaseConnection.instanceOf().getCtype().getConnectionString(false) + "shutdown=true;", user, password);
210                    } catch (Exception ex) {
211                        Log.Debug(DatabaseConnection.class, ex.getLocalizedMessage());
212                    }
213                }
214                conn.close();
215                conn = null;
216            }
217
218        } catch (SQLException ex) {
219
220            ex.printStackTrace();
221            System.exit(1);
222        }
223    }
224
225    public boolean runQueries(String[] queries) throws SQLException {
226        if (prog != null) {
227            prog.setStringPainted(true);
228            prog.setMaximum(queries.length);
229            prog.setMinimum(0);
230            prog.setValue(0);
231        }
232        Statement stm = this.getConnection().createStatement();
233//        for (int i = 0; i < queries.length; i++) {
234//            stm.addBatch(queries[i]);
235//        }
236//        Log.PrintArray(queries);
237        try {
238//            stm.executeBatch();
239            for (int i = 0; i < queries.length; i++) {
240                try {
241                    String string = queries[i];
242                    if (prefix != null) {
243                        string = string.replace(" table ", " table " + prefix);
244                        string = string.replace(" TABLE ", " TABLE " + prefix);
245                        string = string.replace(" on ", " on " + prefix);
246                        string = string.replace(" ON ", " ON " + prefix);
247                        string = string.replace(" into ", " into " + prefix);
248                        string = string.replace(" INTO ", " INTO " + prefix);
249                    }
250                    Log.Print(string);
251                    stm.execute(string);
252                    if (prog != null) {
253                        prog.setValue(i);
254                    }
255                    Thread.sleep(100);
256                } catch (Exception ex) {
257                    throw ex;
258                }
259            }
260            return true;
261        } catch (Exception sQLException) {
262            Log.Debug(this, sQLException.getMessage());
263            return false;
264        } finally {
265            if (prog != null) {
266                prog.setValue(0);
267            }
268        }
269    }
270
271    /**
272     * Set a progressbar
273     *
274     * @param progressbar
275     */
276    public void setProgressbar(JProgressBar progressbar) {
277        prog = progressbar;
278    }
279
280    /**
281     * @return the ctype
282     */
283    public ConnectionTypeHandler getCtype() {
284        return ctype;
285    }
286
287    /**
288     * @return the statement
289     */
290    public Statement getStatement() {
291        return statement;
292    }
293
294    static class connectionPing extends Thread {
295
296        private DatabaseConnection c;
297
298        public connectionPing(DatabaseConnection c) {
299            this.c = c;
300            setPriority(MIN_PRIORITY);
301        }
302
303        @Override
304        public void run() {
305            try {
306                while (!c.getConnection().isClosed()) {                
307                    try {
308                        sleep(180000);
309                    } catch (InterruptedException ex) {
310                        Log.Debug(ex);
311                    }
312                    try {
313                        Log.Debug(this, "ping to " + c.getCtype().getURL());
314                        c.runQueries(new String[]{"select count(ids) from groups"});
315                    } catch (SQLException ex) {
316                        Log.Debug(ex);
317                    }
318                }
319            } catch (SQLException ex) {
320                Log.Debug(ex);
321            }
322        }
323    }
324}