Java tutorial
/* * Copyright 2009 Martin Palma * * 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 it.unibz.inf.xmlssd.metadator.sqlite; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.DecimalFormat; import org.dom4j.Document; import org.dom4j.DocumentHelper; import org.dom4j.Element; /** * Private class which represents the photos table. * @author Martin Palma <martin@palma.bz> * */ class Photo { public static final String CREATE = "CREATE TABLE IF NOT EXISTS photos ( " + "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," + "camera_id INTEGER NOT NULL," + "title VARCHAR," + "author VARCHAR," + "copyright VARCHAR," + "width INTEGER," + "height INTEGER," + "filename VARCHAR," + "exposure VARCHAR," + "aperture FLOAT," + "focalLength FLOAT," + "iso INTEGER," + "flash BOOL, " + "FOREIGN KEY (camera_id) REFERENCES cameras(id));"; private static final String INSERT = "INSERT INTO photos (camera_id, title, author, copyright, width, height, filename, exposure, aperture, focalLength, iso, flash ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; private static final String SELECT = "SELECT photos.*, cameras.manufactor AS manufactor, cameras.model AS model FROM photos LEFT JOIN cameras ON photos.camera_id = cameras.id;"; private static final String DELETE = "DELETE FROM photos WHERE id = ?;"; private Connection aConnection; private DecimalFormat dec = new DecimalFormat("###.##");; /** * Constructs the camera object. * @param connection */ public Photo(Connection connection) { aConnection = connection; } /** * Method which inserts the given XML element into the database by * extracting the values form the XML. * @param element * @throws SQLException */ public void put(Element element) throws SQLException { PreparedStatement insert = aConnection.prepareStatement(Photo.INSERT); insert.setInt(1, Integer.parseInt(element.attributeValue("camera_id"))); insert.setString(2, element.valueOf("title")); insert.setString(3, element.valueOf("author")); insert.setString(4, element.valueOf("copyright")); insert.setInt(5, Integer.parseInt(element.attributeValue("width"))); insert.setInt(6, Integer.parseInt(element.attributeValue("height"))); insert.setString(7, element.attributeValue("filename")); insert.setString(8, element.valueOf("exposure")); insert.setFloat(9, Float.parseFloat(element.valueOf("aperture"))); insert.setFloat(10, Float.parseFloat(element.valueOf("focalLength"))); insert.setInt(11, Integer.parseInt(element.valueOf("iso"))); insert.setBoolean(12, stringToBool(element.valueOf("flash"))); insert.executeUpdate(); insert.close(); } /** * Method which delete the item with the given id from the database. * @param id * @return * @throws SQLException */ public int delete(int id) throws SQLException { PreparedStatement delete = aConnection.prepareStatement(Photo.DELETE); delete.setInt(1, id); int ret = delete.executeUpdate(); delete.close(); return ret; } /** * Method which returns the whole stored data in the database. * @return XML Documents of the data. * @throws SQLException */ public Document get() throws SQLException { Document document = DocumentHelper.createDocument(); Element root = document.addElement("metadator"); Statement stat = aConnection.createStatement(); ResultSet rs = stat.executeQuery(Photo.SELECT); while (rs.next()) { Element photo = root.addElement("photo"); // attributes photo.addAttribute("id", rs.getString("id")); photo.addAttribute("filename", rs.getString("filename")); photo.addAttribute("width", rs.getString("width")); photo.addAttribute("height", rs.getString("height")); // nodes photo.addElement("camera").addAttribute("manufactor", rs.getString("manufactor")).addAttribute("model", rs.getString("model")); photo.addElement("title").addText(rs.getString("title")); photo.addElement("author").addText(rs.getString("author")); photo.addElement("copyright").addText(rs.getString("copyright")); photo.addElement("exposure").addText(rs.getString("exposure")); photo.addElement("aperture").addText(dec.format(Float.parseFloat(rs.getString("aperture")))); photo.addElement("focalLength").addText(dec.format(Float.parseFloat(rs.getString("focalLength")))); photo.addElement("iso").addText(rs.getString("iso")); photo.addElement("flash").addText(boolToString(Boolean.parseBoolean(rs.getString("flash")))); } rs.close(); stat.close(); return document; } /** * Helper method which converts a string like on/off to a the corresponding * boolean value. * @param str * @return */ private Boolean stringToBool(String str) { return (str.toLowerCase().equals("on")) ? true : false; } /** * Helper method which converts a boolean object to a string like on/off * @param bool * @return */ private String boolToString(Boolean bool) { return (bool) ? "on" : "off"; } }