1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
55
56 public final class StructureTableDAO implements IStructureTableDAO
57 {
58
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
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 }