databaseconnection.java

java 写的一个新闻发布系统

// $Id: DatabaseConnection.java,v 1.4 2002/04/11 17:16:43 knguyen Exp $
//
//                                   ____.
//                       __/\ ______|    |__/\.     _______
//            __   .____|    |       \   |    +----+       \
//    _______|  /--|    |    |    -   \  _    |    :    -   \_________
//   \\______: :---|    :    :           |    :    |         \________>
//           |__\---\_____________:______:    :____|____:_____\
//                                      /_____|
//
//              . . . i n   j a h i a   w e   t r u s t . . .
//
//
//  DatabaseConnection
//
//  30.03.2001  AK  added in jahia.
//  01.04.2001  AK  change the package.
//

package org.jahia.admin.database;

import java.sql.*;
import java.util.*;

import org.jahia.utils.*;



/**
 * desc:  It's a class used only by JahiaInstallation and
 * JahiaAdministration (they only have rights to establish direct connections
 * to a database and not via the connection pooling). This class mainly
 * provides you to open test a database connection and to execute sql queries.
 * All methods returns an <code>int</code>, and not ResultSet or anything
 * else. The principe is to check only status of connections, queries, etc.
 *
 * Copyright:    Copyright (c) 2002
 * Company:      Jahia Ltd
 *
 * @author Alexandre Kraft
 * @version 1.0
 */
public class DatabaseConnection
{
    private Statement   theStatement;
    private Connection  theConnection;



    /**
	 * Default constructor.
	 * @author  Alexandre Kraft
	 */
    public DatabaseConnection()
    {
        // do nothing :o)
    } // end constructor



    /**
     * Test a database connection, using settings defined by the user via
     * some inputs like db url, driver, script, etc. The method to test
     * used is to open the connection, try to create a table test in the
     * database and to drop this table. It also check if the user has
     * choosed the script for sqlserver and he have an access database, or,
     * on the opposite, if he have an access database and he choose the
     * sqlserver script.
     * An UTF-8 compliance test can be made by setting to true the
     * corresponding argument (see the configuration wizard).
     * The last test executed in this method is to check
     * if the database selected has already some jahia data inside.
     * @author  Alexandre Kraft
     *
     * @param   script    The database script selected filename.
     * @param   driver    The JDBC driver for the database.
     * @param   url       The url where JDBC can found the database.
     * @param   username  The username required to access the database.
     * @param   password  The password required to access the database.
     * @param   runtimeSQL       The first line of the database script.
     * @param   checkUTF8Compliance  Enabled the UTF-8 test database.
     * @return  An HashMap containing two Booleans (one for the connection
     *          test and one for the data inside check (<code>true</code> if
     *          a test generate error(s), otherwise the return value is
     *          <code>false</code>) and a String (it's the error message, if
     *          checks don't generate error(s), the String is simply empty.
     */
    public HashMap databaseTest( String script,
                                 String driver,
                                 String url,
                                 String username,
                                 String password,
                                 String runtimeSQL,
                                 boolean checkUTF8Compliance)
    {
        HashMap hashMap =  new HashMap();
        hashMap.put("testDatabaseTablesAlreadyExists",  Boolean.FALSE);
        hashMap.put("testDatabaseConnectionError",      Boolean.FALSE);
        hashMap.put("testDatabaseConnectionMessage",    "");

        // test to open a database connection...
        JahiaConsole.println("DatabaseConnection.databaseTest", "Try to open a database connection...");
        int testStatus =  databaseOpen( driver, url, username, password );

        // analyse the result...
        if(testStatus == 0) {       // okay, the connection is open.
            JahiaConsole.println("DatabaseConnection.databaseTest", "Connection opened successfully.");
        } else {
            hashMap.put("testDatabaseConnectionError",  Boolean.TRUE); // the connection generate error(s).
            if(testStatus == 1) {
                hashMap.put("testDatabaseConnectionMessage",  "Driver class not found: " + driver);
                JahiaConsole.println("DatabaseConnection.databaseTest",  "Driver class not found: " + driver);
            } else if(testStatus == 2) {
                hashMap.put("testDatabaseConnectionMessage",  "Can't connect to database. Check your settings.");
            }
        }

        // make other test only if the connection opened successfully...
        if(testStatus == 0)
        {
            // get the first table name in the script...
            String        runtimeTableName = runtimeSQL.substring(runtimeSQL.indexOf("jahia_"), runtimeSQL.indexOf("(")).trim();
            String        dbProductName           =  "";

            // in first, drop the table by security. if the table exists the results can be false...
            JahiaConsole.println("DatabaseConnection.databaseTest", "Try to talk with the database...");
            databaseQuery( "DROP TABLE " + runtimeTableName );

            // okay it's the time to test the *talk* by creating the table and dropping it...
            testStatus = databaseQuery( runtimeSQL.toString() );
            // check the UTF-8 compliance
            if (checkUTF8Compliance) {
                // Create 'random' string with Latin, Cyrillic and Chineeze characters
                // and insert them to the database.
                // My chineeze knowledges are limited but I think that '\u8bed\u8a00'
                // characters mean 'language'   :-)
                String testField = "Latin : 猷缒 / Cyrillic : \u0419\u0416 / Chineeze : \u8bed\u8a00";
                // FIXME : For coding simplicity 'testfield' is hardcoded here. :(
                testStatus += databaseQuery("INSERT INTO " + runtimeTableName +
                                            "(testfield) VALUES('" + testField + "')");
                try {
                    ResultSet rs = theStatement.executeQuery("SELECT testfield FROM jahia_db_test");
                    if (rs.next()) {
                        String testFieldResult = rs.getString("testfield");
                        if (!testFieldResult.equals(testField)) {
                            testStatus++;
                            hashMap.put("testDatabaseConnectionError", Boolean.TRUE);
                            hashMap.put("testDatabaseConnectionMessage", "This database seems to be not UTF-8 compliant");
                            return hashMap;
                        }
                    }
                } catch (SQLException sqle) {
                }
            }
            testStatus += databaseQuery( "DROP TABLE " + runtimeTableName );

            // last tests...
            if(testStatus == 0) {
                try {
                    DatabaseMetaData dbMetaData =  theConnection.getMetaData();
                    dbProductName =  dbMetaData.getDatabaseProductName().trim();
                } catch (SQLException sqle) {       // cannot get the metadata from this database (or the product name)...
                }

                // check if the user has selected sqlserver or access and if it's really this database...
                if(script.equals("sqlserver.script") && !dbProductName.equals("Microsoft SQL Server")) {
                    testStatus  = 1;
                } else if(script.equals("msaccess.script") && !dbProductName.equals("ACCESS")) {
                    testStatus  = 1;
                } else {
                    // FIXME : Is this test still necessary ?
                    // 'testDatabaseTablesAlreadyExists' entry is never used...
                    if(databaseQuery("SELECT * FROM " + runtimeTableName) == 0) {     // check if the database is already jahia-ified :o)
                        hashMap.put("testDatabaseTablesAlreadyExists",  Boolean.TRUE);
                    }
                }
            }
        }

        // okay all tests executed...
        if(testStatus == 0) {
            JahiaConsole.println("DatabaseConnection.databaseTest",  "Database respond successfully.");
        } else {
            hashMap.put("testDatabaseConnectionError",    Boolean.TRUE);
            hashMap.put("testDatabaseConnectionMessage",  "Can't talk with the database. Check your settings.");
        }

        return hashMap;
    } // end databaseTest



    /**
     * Open a database connection, using settings defined by the user like
     * the database driver, the url, username and password.
     * @author  Alexandre Kraft
     *
     * @param   driver    The JDBC driver for the database.
     * @param   url       The url where JDBC can found the database.
     * @param   username  The username required to access the database.
     * @param   password  The password required to access the database.
     * @return  <code>0</code> if the connection is open, <code>1</code> if the
     *          driver class is not found, or <code>2</code> if the connection
     *          cannot be opened.
     */
    public int databaseOpen( String driver,
                             String url,
                             String username,
                             String password )
    {
        // always close a possibly old connection before open a new...
        databaseClose();

        // try to open a database connection...
        try {
            Class.forName( driver );
            theConnection =  DriverManager.getConnection( url, username, password );
            theStatement  =  theConnection.createStatement();
            return 0;
        } catch (ClassNotFoundException cnfe) {
            return 1;
        } catch (SQLException sqle) {
            return 2;
        }
    } // end databaseOpen



    /**
     * Execute a SQL query. Be careful, this method don't return an ResultSet.
     * @author  Alexandre Kraft
     *
     * @param   sqlCode     The sql query you want to execute.
     * @return  <code>0</code> if the query generates no error(s) or <code>1</code>
     *          if there is an exception.
     */
    public int databaseQuery( String sqlCode )
    {
        try {
            theStatement.execute( sqlCode );
            return 0;
        } catch (Exception e) {
            return 1;
        }
    } // end databaseQuery



    /**
     * Execute a SQL query. Be careful, this method don't return an ResultSet.
     * @author  Alexandre Kraft
     *
     * @param   sqlCode     The sql query you want to execute.
     *
     * @throws  Exception   Propagate any exceptions
     */
    public void query( String sqlCode )
    throws Exception
    {
        theStatement.execute( sqlCode );
    } // end query



    /**
     * Close the current database connection. If the connection statement do
     * not exists, the exception is simply catched. There is no problem about
     * this and completely transparent for the user.
     * @author  Alexandre Kraft
     */
    public void databaseClose()
    {
        try {
            theStatement.close();
        } catch (SQLException sqle) {
        } catch (NullPointerException sqle) {
        }
    } // end databaseClose



    /**
     * Get the connection of this instance of the class.
     * @author  Alexandre Kraft
     *
     * @return  The connection of this instance of the class
     */
    public Connection getConnection()
    {
        return theConnection;
    } // end getConnection



    /**
     * Get the statement of this instance of the class.
     * @author  Alexandre Kraft
     *
     * @return  The statement of this instance of the class
     */
    public Statement getStatement()
    {
        return theStatement;
    } // end getStatement


} // end DatabaseConnection