Java tutorial
/* * Copyright (C) 2014 SETRONICA - setronica.com * This source code is available under the terms of the GNU Lesser General Public License * as published by The Open Source Initiative (OSI), either version 3 of the License, * or (at your option) any later version. * * UCS is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. * * See the GNU Lesser General Public License for more details. * You should have received a copy of the GNU Lesser General Public License. */ package com.setronica.ucs.storage; import com.fasterxml.jackson.core.JsonGenerator; import com.fasterxml.jackson.core.JsonParser; import com.fasterxml.jackson.databind.*; import com.fasterxml.jackson.databind.deser.std.PrimitiveArrayDeserializers; import com.fasterxml.jackson.databind.module.SimpleModule; import com.setronica.ucs.category.impl.MemCategoryDAO; import com.setronica.ucs.service.DataIntegrityException; import gnu.trove.set.TLongSet; import gnu.trove.set.hash.TLongHashSet; import org.apache.log4j.Logger; import javax.sql.DataSource; import java.io.IOException; import java.sql.*; import java.util.ArrayList; import java.util.List; public class PgCategoryStorage extends MemCategoryStorage { private static final Logger logger = Logger.getLogger(PgCategoryStorage.class); private DataSource ds; private Connection c; private String tableName; private PreparedStatement delTreePs; private PreparedStatement delCatPs; private PreparedStatement insCatPs; private PreparedStatement updateCatPs; private ObjectMapper mapper = new ObjectMapper(); public PgCategoryStorage(DataSource ds, String tableName) { this.ds = ds; this.tableName = "ucs." + tableName; SimpleModule enumModule = new SimpleModule("MyModule") .addDeserializer(TLongSet.class, new TLongSetDeserializer()) .addSerializer(TLongSet.class, new TLongSetSerializer()); mapper.registerModule(enumModule); mapper.disable(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES); logger.info("Create PgCategoryStorage for table: " + tableName); } public void open() throws Exception { c = ds.getConnection(); if (c.getAutoCommit()) { c.setAutoCommit(false); } delTreePs = c.prepareStatement( "SET LOCAL synchronous_commit TO OFF; delete from " + tableName + " where tree_id=?; commit"); delCatPs = c.prepareStatement("SET LOCAL synchronous_commit TO OFF; delete from " + tableName + " where tree_id=? and category_id=?; commit"); insCatPs = c.prepareStatement("SET LOCAL synchronous_commit TO OFF; insert into " + tableName + " (owner_id, tree_id, tree_name, category_id, val) values(?, ?, ?, ?, cast(? as json)); commit"); updateCatPs = c.prepareStatement("SET LOCAL synchronous_commit TO OFF; update " + tableName + " set val=cast(? as json) where tree_id=? and category_id=?; commit"); String sql = "select owner_id, tree_id, tree_name, category_id, val from " + tableName + " order by owner_id, tree_id, category_id"; logger.info("Starting populate PgCategoryStorage for table: " + tableName); try (Statement s = c.createStatement(); ResultSet rs = s.executeQuery(sql)) { while (rs.next()) { int treeId = rs.getInt(2); int index = rs.getInt(4); MemCategoryDAO.MemPackedCategory category = mapper.readValue(rs.getString(5), MemCategoryDAO.MemPackedCategory.class); MemPackedTree tree = treeStorage.get(treeId); if (tree == null) { tree = new MemPackedTree(); tree.treeId = treeId; tree.ownerId = rs.getInt(1); tree.treeName = rs.getString(3); tree.categories = new ArrayList<MemCategoryDAO.MemPackedCategory>(); treeStorage.put(treeId, tree); } List<MemCategoryDAO.MemPackedCategory> categories = tree.categories; while (index >= categories.size()) { categories.add(null); } categories.set(index, category); } populateOwnerMap(); } } public void close() throws SQLException { logger.info("Close PgCategoryStorage for table: " + tableName); delTreePs.close(); delCatPs.close(); insCatPs.close(); updateCatPs.close(); c.close(); } @Override public int createCategory(int treeId, MemCategoryDAO.MemPackedCategory category) { int index = super.createCategory(treeId, category); logger.debug("createCategory for treeId=" + treeId + ", index=" + index); try { MemPackedTree tree = super.getTree(treeId); insCatPs.setInt(1, tree.ownerId); insCatPs.setInt(2, treeId); insCatPs.setString(3, tree.treeName); insCatPs.setInt(4, index); if (category != null) { insCatPs.setString(5, mapper.writeValueAsString(category)); } else { insCatPs.setNull(5, Types.VARCHAR); } insCatPs.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } return index; } @Override public void updateCategory(int treeId, int categoryId, MemCategoryDAO.MemPackedCategory category) { logger.debug("updateCategory for treeId=" + treeId + ", index=" + categoryId); super.updateCategory(treeId, categoryId, category); try { if (category != null) { updateCatPs.setString(1, mapper.writeValueAsString(category)); } else { updateCatPs.setNull(1, Types.VARCHAR); } updateCatPs.setInt(2, treeId); updateCatPs.setInt(3, categoryId); updateCatPs.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } } @Override public void removeCategory(int treeId, int categoryId) throws DataIntegrityException { logger.debug("removeCategory for treeId=" + treeId + ", categoryId=" + categoryId); super.removeCategory(treeId, categoryId); try { delCatPs.setInt(1, treeId); delCatPs.setInt(2, categoryId); delCatPs.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } } @Override public void removeTree(int treeId) { logger.debug("removeTree for treeId=" + treeId); super.removeTree(treeId); try { delTreePs.setInt(1, treeId); delTreePs.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } } public static class TLongSetDeserializer extends JsonDeserializer<TLongSet> { private JsonDeserializer<long[]> bob = (JsonDeserializer<long[]>) PrimitiveArrayDeserializers .forType(Long.TYPE); @Override public TLongSet deserialize(JsonParser jsonParser, DeserializationContext deserializationContext) throws IOException { long[] array = bob.deserialize(jsonParser, deserializationContext); return array == null ? null : new TLongHashSet(array); } } public static class TLongSetSerializer extends JsonSerializer<TLongSet> { @Override public void serialize(TLongSet value, JsonGenerator jgen, SerializerProvider provider) throws IOException { provider.defaultSerializeValue(value.toArray(), jgen); } } }