Java tutorial
/* * Copyright 2007 Zhang, Zheng <oldbig@gmail.com> Xu, Chuan <xuchuan@gmail.com> * * This file is part of ZOJ. * * ZOJ 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 revision 3 of the License, or (at your option) any later revision. * * ZOJ 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 ZOJ. if not, see * <http://www.gnu.org/licenses/>. */ package cn.edu.zju.acm.onlinejudge.persistence.sql; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.Collection; import java.util.Date; import java.util.Properties; import org.apache.commons.dbcp.BasicDataSource; import cn.edu.zju.acm.onlinejudge.persistence.PersistenceException; /** * <p> * Dateabase. * </p> * * @version 2.0 * @author Zhang, Zheng * @author Xu, Chuan */ public class Database { /** * The query to get last id. */ private static final String GET_LAST_ID = "SELECT LAST_INSERT_ID()"; /** * The data source config file. */ public static String CONFIG_FILE = "data_source.properties"; /** * A BasicDataSource instance used to create connection. */ private static BasicDataSource ds = null; /** * Private constructor. */ private Database() { // empty } static { try { Properties properties = new Properties(); try { properties.load(Database.class.getClassLoader().getResourceAsStream(Database.CONFIG_FILE)); } catch (IOException ioe) { throw new PersistenceException("IO error occurs when load " + Database.CONFIG_FILE + ".", ioe); } Database.ds = new BasicDataSource(); Database.ds.setDefaultAutoCommit( "true".equalsIgnoreCase(Database.getStringProperty(properties, "DefaultAutoCommit"))); Database.ds.setDefaultCatalog(Database.getStringProperty(properties, "DefaultCatalog")); Database.ds.setDriverClassName(Database.getStringProperty(properties, "DriverClassName")); Database.ds.setMaxActive(Database.getIntegerProperty(properties, "MaxActive")); Database.ds.setMaxIdle(Database.getIntegerProperty(properties, "MaxIdle")); Database.ds.setMaxWait(Database.getLongProperty(properties, "MaxWait")); Database.ds.setUrl(Database.getStringProperty(properties, "Url")); Database.ds.setPassword(Database.getStringProperty(properties, "Password")); Database.ds.setUsername(Database.getStringProperty(properties, "Username")); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } /** * Gets a connection. This method is synchronized. * * @return a connection * @throws PersistenceException * if any error occurs. */ public static Connection createConnection() throws PersistenceException { try { return Database.ds.getConnection(); } catch (SQLException e) { throw new PersistenceException("Failed to create connection", e); } } public static void dispose(PreparedStatement ps) { if (ps != null) { try { ps.close(); } catch (Exception e) { } } } public static void dispose(Connection conn) { if (conn != null) { try { conn.close(); } catch (Exception e) { } } } /** * Create a string containing given values. The string is like '(value1, value2, value3)' * * @param values * the values * @return a string like '(value1, value2, value3)' */ public static String createValues(Collection<String> values) { StringBuilder ret = new StringBuilder(); ret.append('('); for (String value : values) { if (ret.length() > 1) { ret.append(','); } ret.append('\'').append(value.replaceAll("'", "''")).append('\''); } ret.append(')'); return ret.toString(); } /** * Create a string containing given number values. The string is like '(value1, value2, value3)' * * @param values * the values * @return a string like '(value1, value2, value3)' */ public static String createNumberValues(Collection<Long> values) { StringBuilder ret = new StringBuilder(); ret.append('('); for (Long value : values) { if (ret.length() > 1) { ret.append(','); } ret.append(value); } ret.append(')'); return ret.toString(); } /** * Rollback given connection. * * @param conn * the connection */ public static void rollback(Connection conn) { try { if (conn != null) { conn.rollback(); } } catch (SQLException e) { // ignore } } /** * Gets the last id. * * @param conn * @param ps * @param rs * @return the last id * @throws SQLException */ public static long getLastId(Connection conn) throws SQLException { PreparedStatement ps = null; try { ps = conn.prepareStatement(Database.GET_LAST_ID); ResultSet rs = ps.executeQuery(); rs.next(); return rs.getLong(1); } finally { Database.dispose(ps); } } /** * Gets a date value from given ResultSet. * * @param rs * the ResultSet * @param column * the column name * @return a date value from given ResultSet. * @throws SQLException */ public static Date getDate(ResultSet rs, String column) throws SQLException { Timestamp date = rs.getTimestamp(column); if (date == null) { return null; } return new Date(date.getTime()); } /** * Convert given Date to Timestamp. * * @param date * the date * @return a Timestamp instance. */ public static Timestamp toTimestamp(Date date) { if (date == null) { return null; } return new Timestamp(date.getTime()); } /** * Gets the given string property * * @param properties * properties * @param key * property key * @return the property value in string */ private static String getStringProperty(Properties properties, String key) throws PersistenceException { String value = properties.getProperty(key); if (value == null) { throw new PersistenceException(key + " property is missing in " + Database.CONFIG_FILE); } return value; } /** * Gets the given int property * * @param properties * properties * @param key * property key * @return the property value in int */ private static int getIntegerProperty(Properties properties, String key) throws PersistenceException { String value = Database.getStringProperty(properties, key); try { return Integer.parseInt(value); } catch (NumberFormatException nfe) { throw new PersistenceException(key + " property is an invalid integer"); } } /** * Gets the given long property * * @param properties * properties * @param key * property key * @return the property value in long */ private static long getLongProperty(Properties properties, String key) throws PersistenceException { String value = Database.getStringProperty(properties, key); try { return Long.parseLong(value); } catch (NumberFormatException nfe) { throw new PersistenceException(key + " property is an invalid long"); } } }