0

So I have a Dynamic Web Project that queries a MySQL database and returns employee information based on user demand.

Ex: htp://localhost:8080/Employees/123 //would return info on employee 123 htp://localhost:8080/Employees //would return info on all employees

Thing is, if the database gets updated and someone asks for a newly inserted employee the program is going to throw a null pointer. Is there anyway to notify or check for updates only after there is a change in the database?

This is the only relevant code concerning mySQL, the rest does it from the the two hashmaps I'm using.

package resources;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.HashMap;

import pojo.Party;

public class DBConnection {

    private Connection con;
    private static Statement statement;
    private static ResultSet resultSet;
    public  static DBConnection connection;
    private static ResultSetMetaData meta;
    private static HashMap<String,Party> map;

    public Party party;

    private DBConnection()
    {
        try 
        {
            map = new HashMap<String,Party>();
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(//not relevant, assume this works);
            statement = con.createStatement();
            readData();
        }
        catch (Exception e)
        {
            System.out.print("Error: "+e);
        }
    }
    public void readData()
    {
        try
        {
            String query = "(SELECT * FROM PureServlet)";
            resultSet = statement.executeQuery(query);
            meta = resultSet.getMetaData();
            String columnName, value, partyName;
            while(resultSet.next())
            {
                partyName = resultSet.getString("PARTY_NAME");
                map.put(partyName, new Party()); //this is the map that keeps track of all parties
                party = map.get(partyName);
              //getColumn...() irritatingly starts at 1 and not 0 thus j=1
                for(int j=1;j<=meta.getColumnCount();j++)
                {
                    columnName = meta.getColumnLabel(j);
                    value = resultSet.getString(columnName);
                    party.getPartyInfo().put(columnName, value); //this is the hashmap within the party that keeps 
                    //track of the individual values. The column Name = label, value is the value
                }
            }
        }
        catch (Exception e)
        {
            System.out.println(e);
        }
    }
    public static HashMap<String,Party> getPartyCollection()
    {
        if(connection == null)
        {
            connection = new DBConnection();
        }
        return map;
    }
}
  • It sounds like the goal is to *cache* results of the database. A simple read cache works as a read-through: if not in the cache, it will talk to the underlying data and see if it exists there, updating the cache as required. Since it's a read-through only, the cache must be periodically updated. (Such caching is automatically handled in many DAL/ORM frameworks!) Primitive pull synchronization is easy using a get-updates-since on some monotonically increasing column such as DATETIME "lastUpdatedAt" (set on update) or ROWVERSION (SQL Server, dunno is MySQL has an equivalent). – user2246674 Jul 16 '13 at 00:23
  • I eventually do want to cache, yes, but that's not the focus of this question. ReadData() only happens upon initialization, it is not repeated, thus if someone attempts to query an employee inserted after the initialization it won't be present in the data structures. I am asking for a way to check (preferably only after a change has occurred, not on a timed interval), so that I avoid that case –  Jul 16 '13 at 00:26
  • So, it *is* a cache. Either read-through (fetch single item as needed) and/or periodically update everything (perhaps as the result of a failing read, using the ideas at the end). – user2246674 Jul 16 '13 at 00:27
  • Correct me if I'm wrong as I'm relatively inexperienced in Web applications, but isn't the purpose of a cache to store recent queries so as to access them more efficiently for a certain period of time? That's not what I'm looking for. I guess I could just catch the null result, readData, and then run it again, but I was hoping to do it a different way. If you can do it with a cache by all means please elaborate, ah I see the updated posts now –  Jul 16 '13 at 00:30
  • Well, that is one use of a cache, yes. However any storage (in this case, said hash) works as a cache when it is "offline" wrt. the master source. I would either eliminate the hash or provide a "get" method that knows how to attempt an update (single item, or refresh the entire hash) from the DB before returning - the trigger could be expiry, missing (for sure here), or a combination of the two. – user2246674 Jul 16 '13 at 00:32
  • Let me ask another question then, is there a more efficient way to return the data than to "cache" all the data as it seems I'm doing. I need to be able to return the information in XML/JSON but I'm not sure how to do that directly from a query, without storing it in some local data structures. And if someone requests all the employees, it can get pretty big. –  Jul 16 '13 at 00:39
  • I don't cache the facing data in any of my ("non-internet"!) business applications. Databases are generally *fast* and serialization is *fast* too: don't cache unless there is a proven benefit or need! So; on each request, I'll grab the data and serialize it appropriately. I cheat and use C#/LINQ2SQL/JSON.NET+POCO, but using adequate tooling in Java should also help reduce the pain. Building a good WS API also helps - don't allow an "insanely huge collection" to be returned without appropriate paging or applicable filtering; and write the WS to support specific use-cases. – user2246674 Jul 16 '13 at 00:47
  • That being said, on a current application I'm working on, I use the database as a cache for BC (Business Catalyst - run if you have to work with it). It uses the approach I outlined above to obtain get-updates-since when applicable (for the CRM API) and a read-through when not applicable (for the e-commerce API). – user2246674 Jul 16 '13 at 00:52
  • As far as the "how to detect changes" bit, MySQL doesn't seem to have a ROWVERSION equivalent (but see http://www.designedtoscale.com/mysql-2/row-versioning-in-mysql-like-ms-sql-server/) so one approach is to use a trigger (http://stackoverflow.com/questions/6290695/row-versioning-in-mysql) it may be also be possible to use MySQL replication (http://dev.mysql.com/doc/refman/5.7/en/replication.html). Basically, the client keeps a sync token, performs a `SELECT .. WHERE Sync > {SyncToken}` and updates the SyncToken it will use the next time to skip the current results. – user2246674 Jul 16 '13 at 01:19
  • 1
    And, if you want to get really advanced (i.e. Push vs. Pull), something like a [MySQL Replication Listener](http://intuitive-search.blogspot.com/2011/07/binary-log-api-and-replication-listener.html) could be used. YMMV. I'd go with the simplest solution and keep a clean separation of concerns and codebase. – user2246674 Jul 16 '13 at 01:25
  • Not going to lie, a lot of that went over my head but I'll take tomorrow to go through the links you posted. Thanks for the help nonetheless, I'd give you the check mark but I don't think that can be done on comments –  Jul 16 '13 at 04:15

0 Answers0