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.importexport.business;
35
36 import fr.paris.lutece.portal.service.i18n.I18nService;
37 import fr.paris.lutece.portal.service.plugin.Plugin;
38 import fr.paris.lutece.portal.service.util.AppException;
39 import fr.paris.lutece.util.sql.DAOUtil;
40
41 import java.util.ArrayList;
42 import java.util.Iterator;
43 import java.util.List;
44 import java.util.Locale;
45 import java.util.NoSuchElementException;
46
47 import org.apache.commons.lang3.StringUtils;
48
49
50
51
52 public abstract class AbstractImportExportDAO
53 {
54 protected static final String SQL_QUERY_GET_COLUMNS = " SELECT DISTINCT column_name, data_type FROM information_schema.columns WHERE table_name = ? ";
55 protected static final String SQL_QUERY_GET_COLUMNS_NAME = " SELECT DISTINCT column_name FROM information_schema.columns WHERE table_name = ? ";
56
57 private static final String CONSTANT_SQL_INT = "int";
58 private static final String CONSTANT_SQL_LONG = "bigint";
59 private static final String CONSTANT_SQL_DOUBLE = "double";
60 private static final String CONSTANT_SQL_FLOAT = "float";
61 private static final String CONSTANT_SQL_REAL = "real";
62 private static final String CONSTANT_SQL_VARCHAR = "varchar";
63 private static final String CONSTANT_SQL_CHARACTER = "character";
64 private static final String CONSTANT_SQL_TEXT = "text";
65 private static final String CONSTANT_SQL_TIMESTAMP = "timestamp";
66 private static final String CONSTANT_SQL_DATE = "date";
67 private static final String CONSTANT_SQL_BYTE = "byte";
68 private static final String CONSTANT_SQL_BLOB = "blob";
69
70 private static final String ERROR_MESSAGE_COLUMN_NOT_FOUND = "importexport.import_data.errors.columnNotFound";
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87 protected static List<TableColumn> getTableColumns( List<String> listColumnNames, String strTableName, Plugin plugin, Locale locale ) throws AppException
88 {
89 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_GET_COLUMNS, plugin );
90 daoUtil.setString( 1, strTableName );
91 daoUtil.executeQuery( );
92 List<TableColumn> listColumns = new ArrayList<TableColumn>( listColumnNames.size( ) );
93 String strPrimaryKeyName = listColumnNames.get( 0 );
94 while ( daoUtil.next( ) )
95 {
96 String strColumnName = daoUtil.getString( 1 );
97 if ( strColumnName != null )
98 {
99 strColumnName = strColumnName.toLowerCase( );
100 }
101 if ( listColumnNames.contains( strColumnName ) )
102 {
103 String strColumnType = daoUtil.getString( 2 );
104 TableColumnport/business/TableColumn.html#TableColumn">TableColumn tableColumn = new TableColumn( strColumnName, StringUtils.equals( strPrimaryKeyName, strColumnName ),
105 getJavaTypeFromSqlString( strColumnType ) );
106 listColumns.add( tableColumn );
107 }
108 }
109 daoUtil.free( );
110
111
112 List<TableColumn> listColumnsSorted = new ArrayList<TableColumn>( listColumns.size( ) );
113 for ( String strColumnName : listColumnNames )
114 {
115 Iterator<TableColumn> iterator = listColumns.iterator( );
116 TableColumn tableColumn;
117 try
118 {
119 while ( ( tableColumn = iterator.next( ) ) != null )
120 {
121 if ( StringUtils.equals( strColumnName, tableColumn.getColumnName( ) ) )
122 {
123 listColumnsSorted.add( tableColumn );
124 iterator.remove( );
125 break;
126 }
127 }
128 }
129 catch( NoSuchElementException e )
130 {
131 throw new AppException( I18nService.getLocalizedString( ERROR_MESSAGE_COLUMN_NOT_FOUND, locale ) );
132 }
133 }
134
135 return listColumnsSorted;
136 }
137
138
139
140
141
142
143
144
145
146
147 public static List<String> getTableColumnsNames( String strTableName, Plugin plugin )
148 {
149 DAOUtil daoUtil = new DAOUtil( SQL_QUERY_GET_COLUMNS_NAME, plugin );
150 daoUtil.setString( 1, strTableName );
151 daoUtil.executeQuery( );
152 List<String> listColumnsName = new ArrayList<String>( );
153 while ( daoUtil.next( ) )
154 {
155 String strColumnName = daoUtil.getString( 1 );
156 listColumnsName.add( strColumnName );
157 }
158 daoUtil.free( );
159 return listColumnsName;
160 }
161
162
163
164
165
166
167
168
169 protected static ColumnType getJavaTypeFromSqlString( String strColumnType )
170 {
171 ColumnType columnType = null;
172 if ( strColumnType == null || StringUtils.isBlank( strColumnType ) )
173 {
174 return null;
175 }
176 String strColumnTypeSearch = strColumnType.toLowerCase( );
177 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_LONG ) )
178 {
179 columnType = ColumnType.TYPE_LONG;
180 }
181 else
182 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_INT ) )
183 {
184 columnType = ColumnType.TYPE_INT;
185 }
186 else
187 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_VARCHAR ) || StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_TEXT )
188 || StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_CHARACTER ) )
189 {
190 columnType = ColumnType.TYPE_STRING;
191 }
192 else
193 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_TIMESTAMP ) )
194 {
195 columnType = ColumnType.TYPE_TIMESTAMP;
196 }
197 else
198 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_DATE ) )
199 {
200 columnType = ColumnType.TYPE_DATE;
201 }
202 else
203 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_DOUBLE ) || StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_FLOAT )
204 || StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_REAL ) )
205 {
206 columnType = ColumnType.TYPE_DOUBLE;
207 }
208 else
209 if ( StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_BYTE )
210 || StringUtils.contains( strColumnTypeSearch, CONSTANT_SQL_BLOB ) )
211 {
212 columnType = ColumnType.TYPE_BYTE;
213 }
214
215 if ( columnType != null )
216 {
217 return columnType;
218 }
219
220 return null;
221 }
222 }