StructureTableDAO.java

/*
 * Copyright (c) 2002-2017, Mairie de Paris
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 *  1. Redistributions of source code must retain the above copyright notice
 *     and the following disclaimer.
 *
 *  2. Redistributions in binary form must reproduce the above copyright notice
 *     and the following disclaimer in the documentation and/or other materials
 *     provided with the distribution.
 *
 *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
 *     contributors may be used to endorse or promote products derived from
 *     this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 *
 * License 1.0
 */
package fr.paris.lutece.plugins.adminsql.business;

import fr.paris.lutece.portal.service.database.PluginConnectionService;
import fr.paris.lutece.portal.service.plugin.Plugin;
import fr.paris.lutece.portal.service.util.AppException;
import fr.paris.lutece.portal.service.util.AppLogService;
import fr.paris.lutece.util.sql.DAOUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;


/**
 * This class provides Data Access methods for StructureTable objects
 */
public final class StructureTableDAO implements IStructureTableDAO
{
    // Constants
    private static final String SQL_QUERY_NEW_PK = "SELECT max( fieldValue ) FROM table_name";
    private static final String SQL_QUERY_SELECT = "SELECT fieldValue, typeValue, nullValue, keyValue, defaultValue, extraValue FROM table_name WHERE fieldValue = ?";
    private static final String SQL_QUERY_INSERT = "INSERT INTO table_name ( fieldValue, typeValue, nullValue, keyValue, defaultValue, extraValue ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
    private static final String SQL_QUERY_DELETE = "DELETE FROM table_name WHERE fieldValue = ? ";
    private static final String SQL_QUERY_UPDATE = "UPDATE table_name SET fieldValue = ?, typeValue = ?, nullValue = ?, keyValue = ?, defaultValue = ?, extraValue = ? WHERE fieldValue = ?";

    //Requests
    private static final String SQL_QUERY_SHOW_TABLES = "SHOW TABLES ";
    private static final String SQL_QUERY_SHOW_COLUMNS = "SHOW COLUMNS FROM ";
    private static final String SQL_QUERY_SELECT_DATA = "SELECT * FROM ";
    private static final String SQL_QUERY_SELECT_FIELDS = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.`COLUMNS` C where c.table_name='";
    private static final String SQL_QUERY_SELECT_FIELD = "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '";

    public List<List<String>> selectAllTables( PluginConnectionService connectionService )
    {
        List<List<String>> list = new ArrayList<List<String>>(  );

        try
        {
            list = selectSqlQuery( SQL_QUERY_SHOW_TABLES, connectionService );
        }
        catch ( SQLException sql )
        {
            AppLogService.error( sql.getMessage(  ), sql );
        }

        return list;
    }

    public List<List<String>> selectAllColumns( PluginConnectionService connectionService, String strTableName )
    {
        List<List<String>> list = new ArrayList<List<String>>(  );

        try
        {
            list = selectSqlQuery( SQL_QUERY_SHOW_COLUMNS + strTableName, connectionService );
        }
        catch ( SQLException sql )
        {
            AppLogService.error( sql.getMessage(  ), sql );
        }

        return list;
    }

    public List<List<String>> selectSqlQuery( String strRequest, PluginConnectionService connectionService )
        throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        String strSQL = strRequest;

        try
        {
            connection = connectionService.getConnection(  );
            statement = connection.prepareStatement( strSQL );

            ResultSet resultSet = statement.executeQuery(  );
            ResultSetMetaData rsmd = resultSet.getMetaData(  );

            ArrayList listRow = new ArrayList(  );

            while ( resultSet.next(  ) )
            {
                String strValue = null;
                ArrayList listLine = new ArrayList(  );

                for ( int i = 1; i <= rsmd.getColumnCount(  ); i++ )
                {
                    if ( resultSet.getObject( rsmd.getColumnName( i ) ) != null )
                    {
                        strValue = resultSet.getObject( rsmd.getColumnName( i ) ).toString(  );
                    }
                    else
                    {
                        strValue = " ";
                    }

                    listLine.add( strValue );
                }

                listRow.add( listLine );
            }

            statement.close(  );
            statement = null;

            return listRow;
        }
        finally
        {
            try
            {
                if ( statement != null )
                {
                    statement.close(  );
                }
            }
            catch ( SQLException e )
            {
                throw new AppException( "SQL Error executing command : " + e.toString(  ) );
            }

            connectionService.freeConnection( connection );
        }
    }

    public List<List<String>> selectAllData( PluginConnectionService connectionService, String strTableName )
    {
        List<List<String>> listData = new ArrayList<List<String>>(  );

        try
        {
            listData = selectSqlQuery( SQL_QUERY_SELECT_DATA + strTableName, connectionService );
        }
        catch ( SQLException sql )
        {
            AppLogService.error( sql.getMessage(  ), sql );
        }

        return listData;
    }

    public List<List<String>> selectAllFields( PluginConnectionService connectionService, String strTableName )
    {
        List<List<String>> listFields = new ArrayList<List<String>>(  );

        try
        {
            listFields = selectSqlQuery( SQL_QUERY_SELECT_FIELDS + strTableName + "'", connectionService );
        }
        catch ( SQLException sql )
        {
            AppLogService.error( sql.getMessage(  ), sql );
        }

        return listFields;
    }

    public List<List<String>> selectAField( PluginConnectionService connectionService, String strTableName,
        String strFieldName )
    {
        List<List<String>> listField = new ArrayList<List<String>>(  );

        try
        {
            listField = selectSqlQuery( SQL_QUERY_SELECT_FIELD + "'" + strTableName + "AND COLUMN_NAME = " +
                    strFieldName + "'", connectionService );
        }
        catch ( SQLException sql )
        {
            AppLogService.error( sql.getMessage(  ), sql );
        }

        return listField;
    }
}