GenesysLowlevelRepositoryCustomImpl.java

/**
 * Copyright 2014 Global Crop Diversity Trust
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 **/

package org.genesys.server.service.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.genesys.server.persistence.GenesysLowlevelRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class GenesysLowlevelRepositoryCustomImpl implements GenesysLowlevelRepository {
    public static final Logger LOG = LoggerFactory.getLogger(GenesysLowlevelRepositoryCustomImpl.class);

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	@Transactional(readOnly = false)
	public void updateFaoInstituteRefs() {
		LOG.info("Updating institute references");
		int count = this.jdbcTemplate.update("UPDATE `accession` A inner join `faoinstitute` FI on FI.code=A.instCode set A.instituteId=FI.id;");
		LOG.info("Institute references updated: {}", count);

		LOG.info("Updating institute accession count");
		count = this.jdbcTemplate
				.update("UPDATE faoinstitute FI inner join (select instituteId, count(*) X from accession group by instituteId) A on A.instituteId=FI.id set FI.accessionCount=A.X;");
		LOG.info("Institute accession counts updated: {}", count);
	}

	// @Override
	// @Transactional(readOnly = false, propagation = Propagation.SUPPORTS)
	// public void updateInstituteAccessionCount(long instituteId) {
	// LOG.debug("Updating institute accession count for instituteId=" +
	// instituteId);
	// // int count = jdbcTemplate
	// //
	// .update("UPDATE faoinstitute FI inner join (select instituteId, count(*) X from accession where instituteId= ? group by instituteId) A on A.instituteId=FI.id set FI.accessionCount=A.X where FI.id = ?;",
	// // instituteId, instituteId);
	// jdbcTemplate.update("UPDATE faoinstitute SET accessionCount = (select count(*) from accession where instituteId=?) where id = ?;",
	// instituteId, instituteId);
	// LOG.info("Institute accession count updated for " + instituteId);
	// }

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	@Transactional(readOnly = false)
	public void updateCountryRefs() {
		LOG.info("Updating country references");
		final int count = this.jdbcTemplate
				.update("UPDATE `accession` A inner join `country` C on C.code3=A.orgCty set A.orgCtyId=C.id WHERE A.orgCty IS NOT NULL;");
		LOG.info("Country references updated: {}", count);
	}

	@Override
	@PreAuthorize("hasRole('ADMINISTRATOR')")
	@Transactional(readOnly = false)
	public void updateFaoInstituteCountries() {
		LOG.info("Updating country references");
		final int count = this.jdbcTemplate.update("UPDATE `faoinstitute` I inner join `country` C on C.code3=left(I.code, 3) set I.countryId=C.id;");
		LOG.info("Country references updated: {}", count);
	}

	/**
	 * Data[] = accessionId, acceNumb and seqNo
	 */
	@Override
	@Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = false)
	public void updateAccessionSequentialNumber(final List<Object[]> data) {
		if (data == null || data.size() == 0)
			return;

		jdbcTemplate.batchUpdate("update accession set seqNo = ? where id = ? and acceNumb = ?", new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] o = data.get(i);
				ps.setFloat(1, (float) o[2]);
				ps.setLong(2, (long) o[0]);
				ps.setString(3, (String) o[1]);
			}

			public int getBatchSize() {
				return data.size();
			}
		});
	}
}