Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm happily coding a client-server application in Java using H2 as database. The server runs the database as an embedded local database while clients connect to it via TCP.

Now more and more I see myself in need of syncing between clients and server which requires complex messaging and threads keeping track of the last change to the database. Caching is also a good thing I might need because right now each client requests the same table every time an update has been made.

Now my question is: isn't there something for syncing and caching with Java and H2 which is quick and easy library not an entire stack of database layer like hibernate or Mongrel?

The closest question I found to my case is http://stackoverflow.com/questions/271610/strategy-for-offline-online-data-synchronization however there they suggest database replication solutions like daffodil or SymmetricDS, I have a single database so the problem doesn't lie there..

I feel like reinventing the wheel for something not extremely complex to do.

EDIT:

Ok here is a sample code for a JList with an underlying database (so we can better understand the case scenario), if multiple instances are launched from different machines connected to the LAN one has to be set as server and the others as clients..

here is MyPanel.java, the main panel

package remotepeers;

import java.util.Iterator;
import javax.swing.DefaultListModel;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
public class MyPanel extends javax.swing.JPanel
{

    DefaultListModel defaultListModel;

    public static void main(String args[])
    {
        MyPanel myPanel = new MyPanel();
        JFrame frame = new JFrame();
        frame.setContentPane(myPanel);
        frame.setSize(400, 500);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setVisible(true);
    }

    public MyPanel()
    {
        defaultListModel = new DefaultListModel();
        initComponents();
        defaultListModel = new DefaultListModel();
        Iterator<Person> iterator = personList1.getIterator();
        Person tmpPerson;
        while (iterator.hasNext())
        {
            tmpPerson = iterator.next();
            defaultListModel.addElement(tmpPerson);
        }
        personList1.setModel(defaultListModel);
    }

    public void refreshPersonList()
    {
        personList1.addRandomPerson();
        defaultListModel = new DefaultListModel();
        Iterator<Person> iter = personList1.getIterator();
        Person currentPerson;
        while (iter.hasNext())
        {
            currentPerson = iter.next();
            defaultListModel.addElement(currentPerson);
        }
        personList1.setModel(defaultListModel);
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    private void initComponents()
    {

        addJButton = new javax.swing.JButton();
        removeJButton = new javax.swing.JButton();
        jScrollPane1 = new javax.swing.JScrollPane();
        personList1 = new remotepeers.PersonList(this);

        addJButton.setText("Add");
        addJButton.addActionListener(new java.awt.event.ActionListener()
        {
            public void actionPerformed(java.awt.event.ActionEvent evt)
            {
                addJButtonActionPerformed(evt);
            }
        });

        removeJButton.setText("Remove");

        personList1.setModel(defaultListModel);
        jScrollPane1.setViewportView(personList1);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 298, Short.MAX_VALUE)
                .addGap(18, 18, 18)
                .addComponent(addJButton, javax.swing.GroupLayout.PREFERRED_SIZE, 110, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(removeJButton, javax.swing.GroupLayout.PREFERRED_SIZE, 102, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(addJButton)
                            .addComponent(removeJButton))
                        .addGap(0, 0, Short.MAX_VALUE))
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 324, Short.MAX_VALUE))
                .addContainerGap())
        );
    }// </editor-fold>                        

    private void addJButtonActionPerformed(java.awt.event.ActionEvent evt)                                           
    {                                               
        PersonDialogPanel personDialogPanel = new PersonDialogPanel();
        int result = JOptionPane.showOptionDialog(null, personDialogPanel,
                "",
                JOptionPane.OK_CANCEL_OPTION,
                JOptionPane.PLAIN_MESSAGE,
                null,
                new String[]
        {
            "Save", "cancel"
        }, // this is the array
                "default");

        if (result == JOptionPane.OK_OPTION)
        {
            Person person = new Person();
            person.setName(personDialogPanel.getNameJTextField().getText());
            person.setSurname(personDialogPanel.getSurnameJTextField().getText());
            personList1.addPerson(person);
            refreshPersonList();
        }
    }                                          
    // Variables declaration - do not modify                     
    private javax.swing.JButton addJButton;
    private javax.swing.JScrollPane jScrollPane1;
    private remotepeers.PersonList personList1;
    private javax.swing.JButton removeJButton;
    // End of variables declaration                   
}

Here id DBCOnnection.java

package remotepeers;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.h2.tools.Server;

public class DBConnection
{

    private static String Protocol = "jdbc:h2:";
    private static String DataBase;
    private static boolean createTables = false;
    private static org.h2.tools.Server server;
    private static Connection connection = null;
    //If this instance acts as a server this boolean is set to true
    private boolean selfServer;
    //If at least one server replies with its ip serverAvailable is set to true
    private static String serverAddress;

    public DBConnection()
    {
    }

    public Connection getConnection() throws InstantiationException,
            IllegalAccessException
    {
        if (connection == null)
        {
            serverAddress = ipAutoDiscovery();
            if (selfServer)
            {
                DataBase = "db" + File.separator + "persondb;create=true";
            }
            else
            {
                DataBase = "tcp://" + serverAddress + ":8093/";
                DataBase += "db" + File.separator + "persondb;create=true";
            }
            final String[] h2args = new String[]
            {
                "-tcpPort", "8093", "-tcpAllowOthers", "true"
            };
            try
            {
                Class.forName("org.h2.Driver");
            }
            catch (java.lang.ClassNotFoundException e)
            {
            }

            try
            {
                if (selfServer)
                {
                    server = Server.createTcpServer(h2args).start();
                }
                connection = DriverManager.getConnection(Protocol + DataBase);
            }
            catch (SQLException e)
            {
            }

            /*Here we check that the tables required exist, otherwise we create them*/
            try
            {
                String sql = "SELECT * FROM PERSON;";
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.execute();
            }
            catch (SQLException e)
            {
                createTables = true;
            }
            if (createTables)
            {
                try
                {
                    loadTables(connection, null);
                }
                catch (IOException | SQLException | InstantiationException | IllegalAccessException e)
                {
                }
            }
        }
        return connection;
    }

    public static void loadTables(Connection con, String database) throws IOException,
            SQLException, InstantiationException, IllegalAccessException
    {
        if (database != null)
        {
            DataBase = database;
        }

        String recordsTable = "DROP TABLE IF EXISTS PERSON;"
                + "CREATE TABLE PERSON (ID INTEGER NOT NULL AUTO_INCREMENT,"
                + "NAME VARCHAR (1024) NOT NULL,"
                + "SURNAME VARCHAR(1024),"
                + "PRIMARY KEY (ID));";

        for (String t : recordsTable.split(";"))
        {
            con.createStatement().executeUpdate(t);
        }
    }

    public void closeConnection() throws SQLException
    {
        if (connection != null)
        {
            connection.close();
            connection = null;
        }
        if (selfServer)
        {
            server.stop();
        }
    }

    public String ipAutoDiscovery()
    {
        selfServer = true; //set this to true if you want to be server
        String serverAddress = "192.168.1.x"; // set here the ip of server
        return serverAddress;
    }
}

The bean class Person.java

package remotepeers;

public class Person implements Comparable
{
    private int id;
    private String name;
    private String surname;

    /**
     * @return the name
     */
    public String getName()
    {
        return name;
    }

    /**
     * @param name the name to set
     */
    public void setName(String name)
    {
        this.name = name;
    }

    /**
     * @return the surname
     */
    public String getSurname()
    {
        return surname;
    }

    /**
     * @param surname the surname to set
     */
    public void setSurname(String surname)
    {
        this.surname = surname;
    }

    /**
     * @return the id
     */
    public int getId()
    {
        return id;
    }

    /**
     * @param id the id to set
     */
    public void setId(int id)
    {
        this.id = id;
    }

    @Override
    public int compareTo(Object o)
    {
        if (o == null)
        {
            throw new NullPointerException();
        }
        if (!(o instanceof Person))
        {
            throw new ClassCastException();
        }
        Person person = (Person) o;
        if (person.name.equals(this.name) && person.surname.equals(this.surname))
        {
            return this.id < person.id ? -1 : (this.id > person.id ? 1 : 0);
        }
        return this.name.compareTo(person.name);
    }

    @Override
    public String toString()
    {
        return this.name + " " + this.surname; //To change body of generated methods, choose Tools | Templates.
    }

}

PersonDAO.java

package remotepeers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

public class PersonDao
{
     public int add(Person person)
    {
        try
        {
            String sql = "INSERT INTO PERSON(NAME, "
                    + "SURNAME) "
                    + "VALUES (?,?)";
            // Create a Preparedstatement
            Connection connection = new DBConnection().getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, person.getName());
            ps.setString(2, person.getSurname());
            ps.executeUpdate();

            /* We just added a Person however we need to know the id the database
             * assigned to the Person
             */
            String sqlPersonId = "SELECT * FROM PERSON WHERE NAME=? AND "
                    + "SURNAME=?";

            // Create a Preparedstatement
            PreparedStatement psPersonId = connection.prepareStatement(sqlPersonId);
            psPersonId.setString(1, person.getName());
            psPersonId.setString(2, person.getSurname());

            ResultSet personIdResultSet = psPersonId.executeQuery();

            while (personIdResultSet.next())
            {
                person.setId(personIdResultSet.getInt("ID"));
            }
            return person.getId();
        }
        catch (InstantiationException | IllegalAccessException | SQLException e)
        {
            return -1;
        }
    }

    public boolean remove(Person person)
    {
        try
        {
            String sql = "DELETE FROM `PERSON` WHERE NAME=? AND "
                    + "SURNAME=? AND ID=?";
            // Create a Preparedstatement
            Connection connection = new DBConnection().getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, person.getName());
            ps.setString(2, person.getSurname());
            ps.setInt(3, person.getId());
            ps.executeUpdate();

            return true;
        }
        catch (InstantiationException | IllegalAccessException e)
        {
            return false;
        }
        catch (SQLException e)
        {
            return false;
        }
    }

    public ArrayList<Person> getAllPerson()
    {
        ArrayList<Person> personList = new ArrayList<>();
        try
        {
            String sql = "SELECT * FROM `PERSON` ORDER BY (ID)";
            Connection connection = new DBConnection().getConnection();
            ResultSet resultSet = connection.createStatement().executeQuery(sql);

            while (resultSet.next())
            {
                Person person = new Person();
                person.setId(resultSet.getInt("ID"));
                person.setName(resultSet.getString("NAME"));
                person.setSurname(resultSet.getString("SURNAME"));
                personList.add(person);
            }
        }
        catch (InstantiationException | IllegalAccessException | SQLException e)
        {
        }
        return personList;
    }

    public Iterator getIterator()
    {
        if (getAllPerson() != null)
        {
            return getAllPerson().iterator();
        }
        else
        {
            return null;
        }
    }  
}

PersonList.java

package remotepeers;

import java.util.Iterator;
import java.util.TreeSet;
import javax.swing.JList;

public class PersonList extends JList
{

    private TreeSet personTreeSet;
    private PersonDao personDao;

    public PersonList()
    {
    }

    public PersonList(MyPanel parent)
    {
        personTreeSet = new TreeSet();
        personDao = new PersonDao();
    }

    public void addPerson(Person person)
    {
        int id = personDao.add(person);
        if (id > 0)
        {
            person.setId(id);
        }
        else
        {
        }
        addPersonList(person);
    }

    public void addRandomPerson()
    {
        for (int i = 0; i < 5000; i++)
        {
            Person randPerson = new Person();
            randPerson.setName("person" + new Integer(i).toString());
            randPerson.setSurname(" ajsjsj");

            int id = personDao.add(randPerson);
            if (id > 0)
            {
                randPerson.setId(id);
            }
            else
            {
            }
            addPersonList(randPerson);
        }
    }

    public boolean removePerson(Person person)
    {
        removePersonList(person.getId());
        personDao.remove(person);
        return true;
    }

    public void removePersonList(int id)
    {
        Iterator<Person> iter = personTreeSet.iterator();
        Person currentPerson;
        while (iter.hasNext())
        {
            currentPerson = iter.next();
            if (currentPerson.getId() == id)
            {
                iter.remove();
            }
        }
    }

    public void addPersonList(Person person)
    {
        personTreeSet.add(person);
    }

    public Iterator getIterator()
    {
        return personDao.getIterator();
    }
}

and PersonDialogPanel.java to ask person name and surname

package remotepeers;
public class PersonDialogPanel extends javax.swing.JPanel
{

    /**
     * Creates new form PersonDialogPanel
     */
    public PersonDialogPanel()
    {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">                          
    private void initComponents()
    {

        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        nameJTextField = new javax.swing.JTextField();
        surnameJTextField = new javax.swing.JTextField();

        jLabel1.setText("Name");

        jLabel2.setText("Surname");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jLabel1)
                        .addGap(18, 18, 18)
                        .addComponent(nameJTextField, javax.swing.GroupLayout.PREFERRED_SIZE, 222, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jLabel2)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(surnameJTextField)))
                .addContainerGap(23, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(23, 23, 23)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(nameJTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel2)
                    .addComponent(surnameJTextField, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(38, Short.MAX_VALUE))
        );
    }// </editor-fold>                        
    // Variables declaration - do not modify                     
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JTextField nameJTextField;
    private javax.swing.JTextField surnameJTextField;
    // End of variables declaration                   

    /**
     * @return the nameJTextField
     */
    public javax.swing.JTextField getNameJTextField()
    {
        return nameJTextField;
    }

    /**
     * @return the surnameJTextField
     */
    public javax.swing.JTextField getSurnameJTextField()
    {
        return surnameJTextField;
    }
}
share|improve this question
    
It sounds like you need to use just a normal relational database for this. If there was a central database it would be trivial to have all clients connect to it. –  JNK Mar 28 '13 at 20:43
    
Indeed I want to continue to use my relational h2 database, what I'm surprised is that there's no library to simply add a caching and synchronization layer between the application and the database. By the way in some sense I have a central database, however each client has to keep its JLists and Tables updated with the database without continuously querying the central database. –  dendini Mar 29 '13 at 9:36
    
I can't find a library that answers your question, but h2 seems to be capable of caching based on the documentation h2database.com/html/features.html#cache_settings .Maybe you can do some simple polling on a dummy table that gets the last time your database was updated then only query the database when a change has been detected. Since caching is enabled, subsequent queries on the database shouldn't be as taxing. And since deployment is through the LAN, this shouldn't cause that much network traffic unless you poll too frequently. Just my 5 cents in case you reinvent the wheel. –  maru Oct 1 '13 at 7:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.