2 * ============LICENSE_START=======================================================
3 * ONAP : ccsdk features
4 * ================================================================================
5 * Copyright (C) 2021 highstreet technologies GmbH Intellectual Property.
7 * ================================================================================
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
19 * ============LICENSE_END=========================================================
22 package org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.database;
24 import com.fasterxml.jackson.core.JsonProcessingException;
25 import java.lang.reflect.InvocationTargetException;
26 import java.sql.Connection;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.util.ArrayList;
31 import java.util.Arrays;
32 import java.util.List;
34 import java.util.Optional;
35 import org.eclipse.jdt.annotation.Nullable;
36 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.SqlDBClient;
37 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.data.rpctypehelper.QueryResult;
38 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.DeleteQuery;
39 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.InsertQuery;
40 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.SelectQuery;
41 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.SqlQuery;
42 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.UpdateQuery;
43 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.UpsertQuery;
44 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.Entity;
45 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.EntityInput;
46 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.entity.input.Filter;
47 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.entity.input.FilterBuilder;
48 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.entity.input.FilterKey;
49 import org.opendaylight.yangtools.yang.binding.DataObject;
50 import org.slf4j.Logger;
51 import org.slf4j.LoggerFactory;
53 public class SqlDBReaderWriter<T extends DataObject> {
55 private static final Logger LOG = LoggerFactory.getLogger(SqlDBReaderWriter.class);
57 protected final Entity entity;
58 private final Class<T> clazz;
59 protected final SqlDBClient dbService;
60 protected final String controllerId;
61 protected final String tableName;
62 private final boolean ignoreControllerId;
64 public SqlDBReaderWriter(SqlDBClient dbService, Entity e, String dbSuffix, Class<T> clazz,
65 String controllerId) {
66 this(dbService, e, dbSuffix, clazz, controllerId, false);
69 public SqlDBReaderWriter(SqlDBClient dbService, Entity e, String dbSuffix, Class<T> clazz,
70 String controllerId, boolean ignoreControllerId) {
71 this.dbService = dbService;
74 this.tableName = this.entity.getName() + dbSuffix;
75 this.controllerId = controllerId;
76 this.ignoreControllerId = ignoreControllerId;
79 public long count(List<Filter> filter) throws SQLException {
81 if (filter == null || filter.isEmpty()) {
82 // query = String.format("SELECT table_rows FROM `information_schema`.`tables` "
83 // + "WHERE `table_schema` = '%s' AND `table_name` = '%s'", this.dbName, this.tableName);
84 query = String.format("SELECT COUNT(`id`) FROM `%s`", this.tableName);
86 query = String.format("SELECT COUNT(`id`) FROM `%s` %s", this.tableName,
87 SqlQuery.getWhereExpression(filter));
89 ResultSet data = this.dbService.read(query);
95 cnt = data.getLong(1);
99 } catch (SQLException ignore) {
104 public long count(List<Filter> list, String controllerId) throws SQLException {
106 list = new ArrayList<>();
108 Optional<Filter> cFilter =
109 list.stream().filter(e -> SqlDBMapper.ODLID_DBCOL.equals(e.getProperty())).findFirst();
110 if (!cFilter.isEmpty()) {
111 list.remove(cFilter.get());
113 if (controllerId != null) {
115 new FilterBuilder().setProperty(SqlDBMapper.ODLID_DBCOL).setFiltervalue(this.controllerId).build());
117 return this.count(list);
120 public QueryResult<T> getData(EntityInput input) {
121 SelectQuery query = new SelectQuery(this.tableName, input, this.controllerId);
122 if (LOG.isTraceEnabled()) {
123 LOG.trace("query={}", query.toSql());
126 ResultSet data = this.dbService.read(query.toSql());
127 List<T> mappedData = SqlDBMapper.read(data, clazz);
128 final Map<FilterKey, Filter> filter = input.getFilter();
133 } catch (SQLException ignore) {
135 long total = this.count(filter != null ? new ArrayList<>(filter.values()) : null, this.controllerId);
136 return new QueryResult<T>(mappedData, query.getPage(), query.getPageSize(), total);
137 } catch (SQLException | IllegalAccessException | IllegalArgumentException | InvocationTargetException
138 | InstantiationException | SecurityException | NoSuchMethodException | JsonProcessingException e) {
139 LOG.warn("problem reading data {}: ", this.entity, e);
141 return QueryResult.createEmpty();
146 public <S extends DataObject> String write(S object, String id) {
148 return this.writeWithoutId(object);
150 InsertQuery<S> query = new InsertQuery<S>(this.entity, object, this.controllerId, this.ignoreControllerId);
152 if (LOG.isTraceEnabled()) {
153 LOG.trace("query={}", query.toSql());
155 boolean success = false;
157 success = this.dbService.write(query.toSql());
158 } catch (SQLException e) {
159 LOG.warn("problem writing data into db: ", e);
162 return success ? id : null;
165 private <S extends DataObject> String writeWithoutId(S object) {
167 InsertQuery<S> query =
168 new InsertQuery<S>(this.entity, object, this.controllerId, this.ignoreControllerId, true);
169 if (LOG.isTraceEnabled()) {
170 LOG.trace("query={}", query.toSql());
173 return this.dbService.writeAndReturnId(query.toSql());
174 } catch (SQLException e) {
175 LOG.warn("problem writing data into db: ", e);
180 public <S extends DataObject> String update(S object, String id) {
181 UpdateQuery<S> query = new UpdateQuery<S>(this.entity, object, this.controllerId, this.ignoreControllerId, true);
183 if (LOG.isTraceEnabled()) {
184 LOG.trace("query={}", query.toSql());
186 String insertedId = null;
187 PreparedStatement stmt = null;
188 Connection connection = null;
190 connection = this.dbService.getConnection();
191 stmt = connection.prepareStatement(query.toSql());
194 int affectedRows = stmt.getUpdateCount();
196 if (affectedRows > 0) {
199 if (LOG.isTraceEnabled()) {
200 LOG.trace("insertedid={}", insertedId);
202 } catch (SQLException e) {
203 LOG.warn("problem writing data into db: ", e);
208 } catch (SQLException e) {
209 LOG.warn("problem closing sql statement: ", e);
212 if (connection != null) {
215 } catch (SQLException e) {
216 LOG.warn("problem closing sql connection: ", e);
224 public <S extends DataObject> String updateOrInsert(S object, String id) {
225 UpsertQuery<S> query = new UpsertQuery<S>(this.entity, object, this.controllerId, this.ignoreControllerId, true);
227 String insertedId = null;
228 if (LOG.isTraceEnabled()) {
229 LOG.trace("query={}", query.toSql());
231 PreparedStatement stmt = null;
232 Connection connection = null;
234 connection = this.dbService.getConnection();
235 stmt = connection.prepareStatement(query.toSql());
238 int affectedRows = stmt.getUpdateCount();
240 if (affectedRows > 0) {
243 } catch (SQLException e) {
244 LOG.warn("problem writing data into db: ", e);
249 } catch (SQLException e) {
250 LOG.warn("problem closing sql statement: ", e);
253 if (connection != null) {
256 } catch (SQLException e) {
257 LOG.warn("problem closing sql connection: ", e);
264 public SqlDBReaderWriter<T> setWriteInterface(Class<? extends DataObject> writeInterfaceClazz) {
265 LOG.debug("Set write interface to {}", writeInterfaceClazz);
266 if (writeInterfaceClazz == null) {
267 throw new IllegalArgumentException("Null not allowed here.");
270 // this.writeInterfaceClazz = writeInterfaceClazz;
274 public int remove(List<Filter> filters) {
275 DeleteQuery query = new DeleteQuery(this.entity, filters);
276 if (LOG.isTraceEnabled()) {
277 LOG.trace("query={}", query.toSql());
279 int affectedRows = 0;
280 PreparedStatement stmt = null;
281 Connection connection = null;
283 connection = this.dbService.getConnection();
284 stmt = connection.prepareStatement(query.toSql());
286 affectedRows = stmt.getUpdateCount();
288 } catch (SQLException e) {
289 LOG.warn("problem execute delete query: ", e);
294 } catch (SQLException e) {
295 LOG.warn("problem closing sql statement: ", e);
298 if (connection != null) {
301 } catch (SQLException e) {
302 LOG.warn("problem closing sql connection: ", e);
309 public int remove(@Nullable String id) {
310 return this.remove(Arrays.asList(new FilterBuilder().setProperty("id").setFiltervalue(id).build()));
313 public <S extends DataObject> List<S> readAll(Class<S> clazz) {
314 SelectQuery query = new SelectQuery(this.tableName);
315 if (LOG.isTraceEnabled()) {
316 LOG.trace("query={}", query.toSql());
319 ResultSet data = this.dbService.read(query.toSql());
320 List<S> mappedData = SqlDBMapper.read(data, clazz);
323 } catch (SQLException ignore) {
326 } catch (SQLException | IllegalAccessException | IllegalArgumentException | InvocationTargetException
327 | InstantiationException | SecurityException | NoSuchMethodException | JsonProcessingException e) {
328 LOG.warn("problem reading all data{}: ", this.entity, e);
333 public List<String> readAll(String key) {
334 SelectQuery query = new SelectQuery(this.tableName, key, this.controllerId).groupBy(key);
335 if (LOG.isTraceEnabled()) {
336 LOG.trace("query={}", query.toSql());
339 ResultSet data = this.dbService.read(query.toSql());
340 List<String> mappedData = SqlDBMapper.read(data, String.class, key);
343 } catch (SQLException ignore) {
346 } catch (SQLException | IllegalAccessException | IllegalArgumentException | InvocationTargetException
347 | InstantiationException | SecurityException | NoSuchMethodException | JsonProcessingException e) {
348 LOG.warn("problem reading all data {} for key: ", this.entity, key, e);
353 public T read(String id) {
355 new SelectQuery(this.tableName, this.controllerId).addFilter(SqlDBMapper.ID_DBCOL, id);
356 if (LOG.isTraceEnabled()) {
357 LOG.trace("query={}", query.toSql());
361 ResultSet data = this.dbService.read(query.toSql());
362 List<T> mappedData = SqlDBMapper.read(data, clazz);
363 item = mappedData.size()>0? mappedData.get(0): null;
364 } catch (SQLException | IllegalAccessException | IllegalArgumentException | InvocationTargetException
365 | InstantiationException | SecurityException | NoSuchMethodException | JsonProcessingException e) {
366 LOG.warn("problem reading data {}: ", this.entity, e);