View Javadoc
1   /*
2    * Copyright (c) 2002-2017, Mairie de Paris
3    * All rights reserved.
4    *
5    * Redistribution and use in source and binary forms, with or without
6    * modification, are permitted provided that the following conditions
7    * are met:
8    *
9    *  1. Redistributions of source code must retain the above copyright notice
10   *     and the following disclaimer.
11   *
12   *  2. Redistributions in binary form must reproduce the above copyright notice
13   *     and the following disclaimer in the documentation and/or other materials
14   *     provided with the distribution.
15   *
16   *  3. Neither the name of 'Mairie de Paris' nor 'Lutece' nor the names of its
17   *     contributors may be used to endorse or promote products derived from
18   *     this software without specific prior written permission.
19   *
20   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
21   * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
22   * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
23   * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDERS OR CONTRIBUTORS BE
24   * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
25   * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
26   * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
27   * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
28   * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
29   * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
30   * POSSIBILITY OF SUCH DAMAGE.
31   *
32   * License 1.0
33   */
34  package fr.paris.lutece.plugins.adminsql.business;
35  
36  import fr.paris.lutece.portal.service.database.PluginConnectionService;
37  import fr.paris.lutece.portal.service.plugin.Plugin;
38  import fr.paris.lutece.portal.service.util.AppException;
39  import fr.paris.lutece.portal.service.util.AppLogService;
40  import fr.paris.lutece.util.sql.DAOUtil;
41  
42  import java.sql.Connection;
43  import java.sql.PreparedStatement;
44  import java.sql.ResultSet;
45  import java.sql.ResultSetMetaData;
46  import java.sql.SQLException;
47  
48  import java.util.ArrayList;
49  import java.util.Collection;
50  import java.util.List;
51  
52  
53  /**
54   * This class provides Data Access methods for StructureTable objects
55   */
56  public final class StructureTableDAO implements IStructureTableDAO
57  {
58      // Constants
59      private static final String SQL_QUERY_NEW_PK = "SELECT max( fieldValue ) FROM table_name";
60      private static final String SQL_QUERY_SELECT = "SELECT fieldValue, typeValue, nullValue, keyValue, defaultValue, extraValue FROM table_name WHERE fieldValue = ?";
61      private static final String SQL_QUERY_INSERT = "INSERT INTO table_name ( fieldValue, typeValue, nullValue, keyValue, defaultValue, extraValue ) VALUES ( ?, ?, ?, ?, ?, ? ) ";
62      private static final String SQL_QUERY_DELETE = "DELETE FROM table_name WHERE fieldValue = ? ";
63      private static final String SQL_QUERY_UPDATE = "UPDATE table_name SET fieldValue = ?, typeValue = ?, nullValue = ?, keyValue = ?, defaultValue = ?, extraValue = ? WHERE fieldValue = ?";
64  
65      //Requests
66      private static final String SQL_QUERY_SHOW_TABLES = "SHOW TABLES ";
67      private static final String SQL_QUERY_SHOW_COLUMNS = "SHOW COLUMNS FROM ";
68      private static final String SQL_QUERY_SELECT_DATA = "SELECT * FROM ";
69      private static final String SQL_QUERY_SELECT_FIELDS = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.`COLUMNS` C where c.table_name='";
70      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 = '";
71  
72      public List<List<String>> selectAllTables( PluginConnectionService connectionService )
73      {
74          List<List<String>> list = new ArrayList<List<String>>(  );
75  
76          try
77          {
78              list = selectSqlQuery( SQL_QUERY_SHOW_TABLES, connectionService );
79          }
80          catch ( SQLException sql )
81          {
82              AppLogService.error( sql.getMessage(  ), sql );
83          }
84  
85          return list;
86      }
87  
88      public List<List<String>> selectAllColumns( PluginConnectionService connectionService, String strTableName )
89      {
90          List<List<String>> list = new ArrayList<List<String>>(  );
91  
92          try
93          {
94              list = selectSqlQuery( SQL_QUERY_SHOW_COLUMNS + strTableName, connectionService );
95          }
96          catch ( SQLException sql )
97          {
98              AppLogService.error( sql.getMessage(  ), sql );
99          }
100 
101         return list;
102     }
103 
104     public List<List<String>> selectSqlQuery( String strRequest, PluginConnectionService connectionService )
105         throws SQLException
106     {
107         Connection connection = null;
108         PreparedStatement statement = null;
109         String strSQL = strRequest;
110 
111         try
112         {
113             connection = connectionService.getConnection(  );
114             statement = connection.prepareStatement( strSQL );
115 
116             ResultSet resultSet = statement.executeQuery(  );
117             ResultSetMetaData rsmd = resultSet.getMetaData(  );
118 
119             ArrayList listRow = new ArrayList(  );
120 
121             while ( resultSet.next(  ) )
122             {
123                 String strValue = null;
124                 ArrayList listLine = new ArrayList(  );
125 
126                 for ( int i = 1; i <= rsmd.getColumnCount(  ); i++ )
127                 {
128                     if ( resultSet.getObject( rsmd.getColumnName( i ) ) != null )
129                     {
130                         strValue = resultSet.getObject( rsmd.getColumnName( i ) ).toString(  );
131                     }
132                     else
133                     {
134                         strValue = " ";
135                     }
136 
137                     listLine.add( strValue );
138                 }
139 
140                 listRow.add( listLine );
141             }
142 
143             statement.close(  );
144             statement = null;
145 
146             return listRow;
147         }
148         finally
149         {
150             try
151             {
152                 if ( statement != null )
153                 {
154                     statement.close(  );
155                 }
156             }
157             catch ( SQLException e )
158             {
159                 throw new AppException( "SQL Error executing command : " + e.toString(  ) );
160             }
161 
162             connectionService.freeConnection( connection );
163         }
164     }
165 
166     public List<List<String>> selectAllData( PluginConnectionService connectionService, String strTableName )
167     {
168         List<List<String>> listData = new ArrayList<List<String>>(  );
169 
170         try
171         {
172             listData = selectSqlQuery( SQL_QUERY_SELECT_DATA + strTableName, connectionService );
173         }
174         catch ( SQLException sql )
175         {
176             AppLogService.error( sql.getMessage(  ), sql );
177         }
178 
179         return listData;
180     }
181 
182     public List<List<String>> selectAllFields( PluginConnectionService connectionService, String strTableName )
183     {
184         List<List<String>> listFields = new ArrayList<List<String>>(  );
185 
186         try
187         {
188             listFields = selectSqlQuery( SQL_QUERY_SELECT_FIELDS + strTableName + "'", connectionService );
189         }
190         catch ( SQLException sql )
191         {
192             AppLogService.error( sql.getMessage(  ), sql );
193         }
194 
195         return listFields;
196     }
197 
198     public List<List<String>> selectAField( PluginConnectionService connectionService, String strTableName,
199         String strFieldName )
200     {
201         List<List<String>> listField = new ArrayList<List<String>>(  );
202 
203         try
204         {
205             listField = selectSqlQuery( SQL_QUERY_SELECT_FIELD + "'" + strTableName + "AND COLUMN_NAME = " +
206                     strFieldName + "'", connectionService );
207         }
208         catch ( SQLException sql )
209         {
210             AppLogService.error( sql.getMessage(  ), sql );
211         }
212 
213         return listField;
214     }
215 }