Java tutorial
/* * Copyright (C) 2007 - 2011 ScalAgent Distributed Technologies * * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or any later version. * * This library 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 along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 * USA. * * Initial developer(s): * Contributor(s): ScalAgent Distributed Technologies */ package fr.dyade.aaa.util; import java.io.ByteArrayInputStream; import java.io.File; import java.io.FileNotFoundException; import java.io.IOException; import java.io.ObjectInputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import java.util.Vector; import org.apache.commons.dbcp.BasicDataSource; import org.objectweb.util.monolog.api.BasicLevel; import org.objectweb.util.monolog.api.Logger; import fr.dyade.aaa.common.Debug; /** * This class allows to use a MySQL database as repository with the * NTransaction module. * <p> * The basic setup at the code to get Database code to run is the same. * * The additional thing when using MySQL database is: * <ol> * <li>For the a3servers.xml that will be copy to run directory, we have * to add these: * <pre> * <property name="Transaction" value="fr.dyade.aaa.util.NTransaction"/> * <property name="NTRepositoryImpl" value="fr.dyade.aaa.util.MySqlDBRepository"/> * <property name="DBDriver" value="org.gjt.mm.mysql.Driver"/> * <property name="ConnURL" value="jdbc:mysql://hostname:3306/instance"/> * <property name="DBUser" value="dbUserName"/> * <property name="DBPass" value="dbPassword"/> * </pre> * <li>In the start script in the bin directory: * <ol> * <li>We have to add the following library * <pre> * CLASSPATH=$CLASSPATH:$VIATOR_LIB/mysql-connector-java-5.0.5-bin.jar * CLASSPATH=$CLASSPATH:$VIATOR_LIB/commons-dbcp-1.2.1.jar * CLASSPATH=$CLASSPATH:$VIATOR_LIB/commons-pool-1.3.jar * </pre> * <li>change the java command to add in a few -D parameter * <pre> * JAVA_ARGS=$JAVA_ARGS" * -DNTRepositoryImpl=fr.dyade.aaa.util.MySqlDBRepository * -DDBDriver=org.gjt.mm.mysql.Driver * -DConnURL=jdbc:mysql://hostname:3306/instance * -DDBUser=dbUserName * -DDBPass=dbPassword" * </pre> * <li>may need to up size the max memory for better performace * This is what we use: JAVA_ARGS=" -Xms100M -Xmx1024M " * </ol> * <li>At the MySQL database side: * <ol> * <li>we have to set the max-allowed-packet to a bigger size, I think the * default is 1M, if the number of messages become large, it will fail. * "max_allowed_packet=16M" * <li>create the JoramDB table first (we decided that we will create it * outside here instead of in the code. * <pre> * CREATE TABLE JoramDB (name VARCHAR(256), content longblob, primary key(name)); * </pre> * </ol> * * @see NTransaction * @see Repository */ public final class MySqlDBRepository implements Repository { /* String driver = "org.apache.derby.jdbc.EmbeddedDriver"; String connurl = "jdbc:derby:"; // String driver = "org.hsqldb.jdbcDriver"; // String connurl = "jdbc:hsqldb:file:"; */ String driver = System.getProperty("DBDriver", "org.gjt.mm.mysql.Driver"); String connurl = System.getProperty("ConnURL", "jdbc:mysql://localhost:3306/mysql"); String user = System.getProperty("DBUser", "root"); String pass = System.getProperty("DBPass", ""); BasicDataSource ds = null; boolean reconnectLoop = false; public static Logger logger = Debug.getLogger("fr.dyade.aaa.util.MySqlDBRepository"); File dir = null; private int nbsaved = 0; /** * Returns the number of save operation to repository. * * @return The number of save operation to repository. */ public int getNbSavedObjects() { return nbsaved; } private int nbdeleted = 0; /** * Returns the number of delete operation on repository. * * @return The number of delete operation on repository. */ public int getNbDeletedObjects() { return nbdeleted; } private int baddeleted = 0; /** * Returns the number of useless delete operation on repository. * * @return The number of useless delete operation on repository. */ public int getNbBadDeletedObjects() { return baddeleted; } private int nbloaded = 0; /** * Returns the number of load operation from repository. * * @return The number of load operation from repository. */ public int getNbLoadedObjects() { return nbloaded; } Connection conn = null; // Be careful the constructor must be public to allow newInstance from another package. public MySqlDBRepository() { } PreparedStatement insertStmt = null; PreparedStatement updateStmt = null; PreparedStatement deleteStmt = null; /** * Initializes the repository. * Opens the connection, evntually creates the database and tables. */ public void init(Transaction transaction, File dir) throws IOException { this.dir = dir; try { Class.forName(driver).newInstance(); // conn = DriverManager.getConnection(connurl + new File(dir, "JoramDB").getPath() + ";shutdown=true;server.no_system_exit=true", "sa", ""); Properties props = new Properties(); /* props.put("user", "user1"); props.put("password", "user1"); */ props.put("user", user); props.put("password", pass); /* conn = DriverManager.getConnection(connurl + new File(dir, "JoramDB").getPath() + ";create=true", props); */ // conn = DriverManager.getConnection(connurl, props); // MySQL (the database must exist and start seperately) conn = getConnection(); conn.setAutoCommit(false); } catch (IllegalAccessException exc) { throw new IOException(exc.getMessage()); } catch (ClassNotFoundException exc) { throw new IOException(exc.getMessage()); } catch (InstantiationException exc) { throw new IOException(exc.getMessage()); } catch (SQLException sqle) { throw new IOException(sqle.getMessage()); } try { // Creating a statement lets us issue commands against the connection. Statement s = conn.createStatement(); // We create the table. // s.execute("create cached table JoramDB(name VARCHAR PRIMARY KEY, content VARBINARY(256))"); /* s.execute("CREATE TABLE JoramDB (name VARCHAR(256), content LONG VARCHAR FOR BIT DATA, PRIMARY KEY(name))"); */ s.execute("CREATE TABLE JoramDB (name VARCHAR(256), content longblob, primary key(name))"); // MySQL s.close(); conn.commit(); } catch (SQLException sqle) { String exceptionString = sqle.toString(); if (exceptionString.indexOf("CREATE command denied") == -1) { sqle.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } try { insertStmt = conn.prepareStatement("INSERT INTO JoramDB VALUES (?, ?)"); updateStmt = conn.prepareStatement("UPDATE JoramDB SET content=? WHERE name=?"); deleteStmt = conn.prepareStatement("DELETE FROM JoramDB WHERE name=?"); } catch (SQLException sqle) { sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } catch (Exception e) { e.printStackTrace(); // throw e; } } /** * Gets a list of persistent objects that name corresponds to prefix. * * @return The list of corresponding names. */ public String[] list(String prefix) throws IOException { try { // Creating a statement lets us issue commands against the connection. Statement s = conn.createStatement(); ResultSet rs = s.executeQuery("SELECT name FROM JoramDB WHERE name LIKE '" + prefix + "%'"); Vector v = new Vector(); while (rs.next()) { v.add(rs.getString(1)); } rs.close(); s.close(); String[] result = new String[v.size()]; result = (String[]) v.toArray(result); return result; } catch (SQLException sqle) { if (sqle instanceof com.mysql.jdbc.CommunicationsException && !reconnectLoop) { logger.log(BasicLevel.WARN, "Database reconnection problem at list, Reconnecting"); reconnection(); reconnectLoop = true; String[] result = list(prefix); reconnectLoop = false; return result; } if (reconnectLoop) logger.log(BasicLevel.WARN, "Database reconnection problem at list"); logger.log(BasicLevel.WARN, "list, problem list " + prefix); sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } catch (Exception e) { logger.log(BasicLevel.WARN, "list, problem list " + prefix + " in e with " + e.getMessage()); e.printStackTrace(); throw new IOException(e.getMessage()); } } /** * Save the corresponding bytes array. */ public void save(String dirName, String name, byte[] content) throws IOException { String fname = null; if (dirName == null) { fname = name; } else { fname = new StringBuffer(dirName).append('/').append(name).toString(); } boolean requireReconnect = false; try { insertStmt.setString(1, fname); insertStmt.setBytes(2, content); insertStmt.executeUpdate(); } catch (SQLException e) { // e.printStackTrace(); try { updateStmt.setBytes(1, content); updateStmt.setString(2, fname); updateStmt.executeUpdate(); } catch (com.mysql.jdbc.PacketTooBigException ptbe) { System.err.println( "MySqlDBRepository.save - was trying to save\ndirName: " + dirName + "\nname: " + name); ptbe.printStackTrace(); } catch (SQLException sqle) { if (sqle instanceof com.mysql.jdbc.CommunicationsException && !reconnectLoop) { logger.log(BasicLevel.WARN, "Database CommunicationsException problem at save, requires Reconnection"); requireReconnect = true; } else { if (reconnectLoop) logger.log(BasicLevel.WARN, "Database reconnection problem at save. Still have CommunicationsException"); logger.log(BasicLevel.WARN, "save, CommunicationsException problem saving " + name); sqle.printStackTrace(); // throw new IOException(sqle.getMessage()); } } catch (NullPointerException ne) { if (!reconnectLoop) { logger.log(BasicLevel.WARN, "Database NullPointerException problem at save, requires Reconnection"); requireReconnect = true; } else { if (reconnectLoop) logger.log(BasicLevel.WARN, "Database reconnection problem at save. Still have NullPointerException"); logger.log(BasicLevel.WARN, "save, NullPointerException problem saving " + name); ne.printStackTrace(); // throw new IOException(sqle.getMessage()); } } catch (Exception e2) { logger.log(BasicLevel.WARN, "save, problem saving " + name + " in e2 with " + e2.getMessage()); e2.printStackTrace(); // throw e2; } } if (requireReconnect) { logger.log(BasicLevel.WARN, "Database problem at save, Reconnecting"); reconnection(); reconnectLoop = true; save(dirName, name, content); reconnectLoop = false; } nbsaved += 1; } /** * Loads the object. * * @return The loaded object or null if it does not exist. * @throws ClassNotFoundException */ public Object loadobj(String dirName, String name) throws IOException, ClassNotFoundException { if (logger.isLoggable(BasicLevel.DEBUG)) logger.log(BasicLevel.DEBUG, "loadobj, b4 load call"); byte[] content = load(dirName, name); if (logger.isLoggable(BasicLevel.DEBUG)) logger.log(BasicLevel.DEBUG, "loadobj, after load call"); ByteArrayInputStream bis = new ByteArrayInputStream(content); ObjectInputStream ois = new ObjectInputStream(bis); try { Object obj = ois.readObject(); return obj; } catch (Exception e) { String exceptionString = e.toString(); if (exceptionString.indexOf("KNOWN PROBLEM") == -1) { e.printStackTrace(); } throw new IOException(e.getMessage()); } finally { ois.close(); bis.close(); } } /** * Loads the byte array. * * @return The loaded bytes array. */ public byte[] load(String dirName, String name) throws IOException { if (logger.isLoggable(BasicLevel.DEBUG)) logger.log(BasicLevel.DEBUG, "load called"); String fname = null; if (dirName == null) { fname = name; } else { fname = new StringBuffer(dirName).append('/').append(name).toString(); } try { // Creating a statement lets us issue commands against the connection. Statement s = conn.createStatement(); // ResultSet rs = s.executeQuery("SELECT content FROM JoramDB WHERE name='" + fname + "'"); if (!rs.next()) { throw new FileNotFoundException("Cannot find object in JoramDB " + ("serverCounter".equals(fname) ? "[KNOWN PROBLEM] " : "") + fname); } byte[] content = rs.getBytes(1); rs.close(); s.close(); if (logger.isLoggable(BasicLevel.DEBUG)) logger.log(BasicLevel.DEBUG, "load, after database call"); nbloaded += 1; return content; } catch (SQLException sqle) { if (sqle instanceof com.mysql.jdbc.CommunicationsException && !reconnectLoop) { logger.log(BasicLevel.WARN, "Database reconnection problem at load, Reconnecting"); reconnection(); reconnectLoop = true; byte[] content = load(dirName, name); reconnectLoop = false; return content; } if (reconnectLoop) logger.log(BasicLevel.WARN, "Database reconnection problem at load"); logger.log(BasicLevel.WARN, "load, problem load " + name); sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } catch (Exception e) { String exceptionString = e.toString(); if (exceptionString.indexOf("KNOWN PROBLEM") == -1) { logger.log(BasicLevel.WARN, "load, problem load " + name + " in e with " + e.getMessage()); e.printStackTrace(); } throw new FileNotFoundException(e.getMessage()); } } /** * Deletes the corresponding objects in repository. */ public void delete(String dirName, String name) throws IOException { String fname = null; if (dirName == null) { fname = name; } else { fname = new StringBuffer(dirName).append('/').append(name).toString(); } int nb = 0; try { // Creating a statement lets us issue commands against the connection. Statement s = conn.createStatement(); // nb = s.executeUpdate("DELETE FROM JoramDB WHERE name='" + fname + "'"); } catch (SQLException sqle) { if (sqle instanceof com.mysql.jdbc.CommunicationsException && !reconnectLoop) { logger.log(BasicLevel.WARN, "Database reconnection problem at delete, Reconnecting"); reconnection(); reconnectLoop = true; delete(dirName, name); reconnectLoop = false; } else { if (reconnectLoop) logger.log(BasicLevel.WARN, "Database reconnection problem at delete"); logger.log(BasicLevel.WARN, "delete, problem delete " + name); sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } } catch (Exception e) { logger.log(BasicLevel.WARN, "delete, problem delete " + name + " in e with " + e.getMessage()); e.printStackTrace(); // throw e; } if (nb != 1) baddeleted += 1; nbdeleted += 1; } /** * Commits all changes to the repository. */ public void commit() throws IOException { try { conn.commit(); } catch (SQLException sqle) { sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } catch (Exception e) { e.printStackTrace(); // throw e; } } /** * Closes the repository. */ public void close() throws IOException { try { conn.close(); } catch (SQLException sqle) { sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } catch (Exception e) { e.printStackTrace(); // throw e; } if (ds != null) try { ds.close(); } catch (Exception e) { e.printStackTrace(); } } // public Connection getConnection() throws SQLException private Connection getConnection() throws SQLException { ds = new BasicDataSource(); ds.setDriverClassName(driver); ds.setUsername(user); ds.setPassword(pass); ds.setUrl(connurl); return ds.getConnection(); } private void closeConnection() { logger.log(BasicLevel.WARN, "closeConnection in progress"); try { if (ds != null) { ds.close(); } } catch (Exception e) { } logger.log(BasicLevel.WARN, "closeConnection success"); ds = null; } private void reconnection() throws IOException { logger.log(BasicLevel.WARN, "reconnection in progress - starting"); try { if (ds != null) { // conn = ds.getConnection(); closeConnection(); // attempt to close all connection and recreate DataSource. } logger.log(BasicLevel.WARN, "reconnection in progress - old connection closed"); conn = getConnection(); logger.log(BasicLevel.WARN, "reconnection in progress - getConnection success"); conn.setAutoCommit(false); insertStmt = conn.prepareStatement("INSERT INTO JoramDB VALUES (?, ?)"); updateStmt = conn.prepareStatement("UPDATE JoramDB SET content=? WHERE name=?"); deleteStmt = conn.prepareStatement("DELETE FROM JoramDB WHERE name=?"); } catch (Exception sqle) { sqle.printStackTrace(); throw new IOException(sqle.getMessage()); } logger.log(BasicLevel.WARN, "Database reconnection success"); } }