0

HQL and loading .csv, possible?

asked 2010-02-16 07:34:54 +0800

JoH gravatar image JoH
105 2 3

Hello,
Is there any possibility to load .csv file using HQL?

delete flag offensive retag edit

2 Replies

Sort by ยป oldest newest

answered 2010-02-16 17:03:15 +0800

caclark gravatar image caclark
1753 2 5
http://clarktrips.intltwi...

No, I've used Hibernate for years and the only things you can do with it are relational database access and XML mapping. I've never done the latter.

link publish delete flag offensive edit

answered 2010-02-16 17:16:12 +0800

terrytornado gravatar image terrytornado flag of Germany
9393 3 7 16
http://www.oxitec.de/

updated 2010-02-16 17:24:59 +0800

In the hope it can helps you or get ideas.

I have a sample code that works in following way:

1. We get a .csv file from an Url as an ZipInputstream. ( We need this for periodically update a table from the web).
2. All csv fields for every line of this InputStream we mapped as a Bean (used the SuperCSV lib) and store it to a list<Bean>
3. We iterate through this list and make a hibernate saveOrUpdate(aBean) for all entries.

You can found the codes working in our Zksample2 app.

best
Stephan

/**
 * Copyright 2010 the original author or authors.
 * 
 * This file is part of Zksample2. http://zksample2.sourceforge.net/
 *
 * Zksample2 is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * Zksample2 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Zksample2.  If not, see <http://www.gnu.org/licenses/gpl.html>.
 */
package de.forsthaus.backend.service.impl;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Serializable;
import java.net.Inet4Address;
import java.net.Inet6Address;
import java.net.InetAddress;
import java.net.URL;
import java.net.URLConnection;
import java.util.zip.ZipInputStream;

import org.apache.commons.lang.ArrayUtils;
import org.apache.log4j.Logger;
import org.supercsv.io.CsvBeanReader;
import org.supercsv.prefs.CsvPreference;

import de.forsthaus.backend.dao.IpToCountryDAO;
import de.forsthaus.backend.model.IpToCountry;
import de.forsthaus.backend.service.IpToCountryService;

/**
 * Service implementation for methods that depends on <b>IpToCountry model</b>
 * class.<br>
 * 
 * @author bbruhns
 * @author sgerth
 */
public class IpToCountryServiceImpl implements IpToCountryService, Serializable {

	private static final long serialVersionUID = 893318843695896685L;
	private transient final static Logger logger = Logger.getLogger(IpToCountryServiceImpl.class);

	final private String updateUrl = "http://ip-to-country.webhosting.info/downloads/ip-to-country.csv.zip";

	final private String[] stringNameMapping = { "ipcIpFrom", "ipcIpTo", "ipcCountryCode2", "ipcCountryCode3", "ipcCountryName" };

	private IpToCountryDAO ipToCountryDAO;

	public void setIpToCountryDAO(IpToCountryDAO ipToCountryDAO) {
		this.ipToCountryDAO = ipToCountryDAO;
	}

	public IpToCountryDAO getIpToCountryDAO() {
		return ipToCountryDAO;
	}

	/**
	 * Converts an ip-address to a long value.<br>
	 * 
	 * @param address
	 * @return
	 */
	private static long inetAddressToLong(InetAddress address) {
		if (address.isAnyLocalAddress())
			return 0l;

		final byte[] bs;
		if (address instanceof Inet4Address) {
			bs = address.getAddress();
		} else if (address instanceof Inet6Address) {
			if (((Inet6Address) address).isIPv4CompatibleAddress()) {
				// take the last 4 digits
				bs = ArrayUtils.subarray(address.getAddress(), 12, 16);
			} else {
				throw new RuntimeException("IPv6 not supported!");
			}
		} else {
			throw new RuntimeException();
		}

		return bs[0] * 16777216l + bs[1] * 65536 + bs[2] * 256 + bs[3];
	}

	@Override
	public IpToCountry getIpToCountry(InetAddress address) {
		Long lg = Long.valueOf(inetAddressToLong(address));
		return ipToCountryDAO.getCountry(lg);
	}

	public void saveOrUpdate(IpToCountry ipToCountry) {
		getIpToCountryDAO().saveOrUpdate(ipToCountry);
	}

	/* *
	 * Import a csv file from the web .<br>
	 * 1. We get a .csv file from an Url as an ZipInputstream. ( We need this
	 * for periodically update a table from the web). <br>
	 * 2. All csv fields for every line of this InputStream we mapped as a Bean
	 * (used the SuperCSV lib) and store it to a list<Bean> <br>
	 * 3. We iterate through this list and make a hibernate saveOrUpdate(aBean)
	 * for all entries.
	 * 
	 * 
	 * @return count of imported records
	 */
	@Override
	public int importIP2CountryCSV() {
		try {

			// first, delete all records in the ip2Country table
			getIpToCountryDAO().deleteAll();
			System.out.println("Records after deleting : " + getIpToCountryDAO().getCountAllIpToCountry());

			final URL url = new URL(updateUrl);
			final URLConnection conn = url.openConnection();
			final InputStream istream = conn.getInputStream();

			final ZipInputStream zipInputStream = new ZipInputStream(new BufferedInputStream(istream));

			zipInputStream.getNextEntry();

			final BufferedReader in = new BufferedReader(new InputStreamReader(zipInputStream));

			final CsvBeanReader csvb = new CsvBeanReader(in, CsvPreference.STANDARD_PREFERENCE);

			// List<IpToCountry> list = new ArrayList<IpToCountry>();

			IpToCountry tmp = null;
			int id = 1;
			while (null != (tmp = csvb.read(IpToCountry.class, stringNameMapping))) {
				tmp.setId(id++);

				if (logger.isDebugEnabled()) {
					logger.debug(id + " --> " + tmp.getIpcCountryName());
				}
				// save in db
				getIpToCountryDAO().saveOrUpdate(tmp);

			}
			// close the stream !!!
			in.close();

			return getIpToCountryDAO().getCountAllIpToCountry();

		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public int getCountAllIpToCountry() {
		return getIpToCountryDAO().getCountAllIpToCountry();
	}

}

link publish delete flag offensive edit
Your reply
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!

[hide preview]

Question tools

Follow

RSS

Stats

Asked: 2010-02-16 07:34:54 +0800

Seen: 669 times

Last updated: Feb 16 '10

Support Options
  • Email Support
  • Training
  • Consulting
  • Outsourcing
Learn More