Persistence Tutorial: MySQL Datasource

The MySQL Datasource tutorial will illustrate the following concepts:

  • Adding a custom MySQL datasource to Union.
  • Saving accounts and account attributes to a custom datasource.

Objective

For our application we want to create a custom MySQL datasource that will be responsible for storing two global account attributes: score and title. All other attribtues will continue to be handled by Derby, the embedded built-in database. Although we will be creating the database from scratch you will generally want to create a custom database for scenarios such as:

  • You already have an existing database managing accounts that you want to integrate with Union.
  • You want to store attributes as datatypes other Strings for the purpose of search and aggregation.

Datasource

A datasource class must implement the interface net.user1.union.api.Datasource. Here is the code for the MySqlAccountDatasource class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
package net.user1.union.example.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.apache.log4j.Logger;
import net.user1.union.api.Account;
import net.user1.union.api.Datasource;
import net.user1.union.api.Room;
import net.user1.union.api.Server;
import net.user1.union.core.attribute.Attribute;
import net.user1.union.core.context.DatasourceContext;
import net.user1.union.core.exception.DatasourceException;

/**
 * Example of a Datasource that stores "score" and "title" attribute for
 * Accounts backed by a MySQL database. It particularly allows for non-String data to be
 * more easily stored and queried.
 *
 * This example is meant to illustrate the Datasource methods that should be implemented and
 * does not focus on the database connectivity and should not be considered a reference
 * implementation of MySql database connectivity.
 *
 * The database tables are assumed to exist and are not created in the example.
 *
 */

public class MySqlAccountDatasource implements Datasource {
    private static Logger log = Logger.getLogger(MySqlAccountDatasource.class);
    private String dbURL;
    private String dbUsername;
    private String dbPassword;
   
    public boolean init(DatasourceContext ctx) {
        // load the database details which need to be provided in the union.xml datasource
        // declaration
        // we are just checking for the presence of an attribute but a more detailed
        // implementation could do additional checking such as ensuring the attribute isn't
        // just empty spaces or meets a particular format
        if ((dbURL = getAttribute(ctx, "dbURL")) == null) {
            log.fatal("Datasource MySqlAccountDatasource requires attribute [dbURL].");
            return false;
        }
        if ((dbUsername = getAttribute(ctx, "dbUsername")) == null) {
            log.fatal("Datasource MySqlAccountDatasource requires attribute [dbUsername].");
            return false;
        }
        if ((dbPassword = getAttribute(ctx, "dbPassword")) == null) {
            log.fatal("Datasource MySqlAccountDatasource requires attribute [dbPassword].");
            return false;
        }

        // load the driver class
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch(ClassNotFoundException e) {
            // could not load the driver class
            log.fatal("Could not load mysql driver.", e);
            return false;          
        } catch (InstantiationException e) {
            // could not load the driver class
            log.fatal("Could not load mysql driver.", e);
            return false;
        } catch (IllegalAccessException e) {
            // could not load the driver class
            log.fatal("Could not load mysql driver.", e);
            return false;
        }
       
        // everything OK
        return true;
    }
   
    /**
     * Return a datasource attribute defined in union.xml.
     */

    private String getAttribute(DatasourceContext ctx, String name) {
        Object attr = ctx.getAttributes().get(name);
        if (attr != null) {
            return attr.toString();
        } else {
            return null;
        }
    }
   
    /**
     * Return a connection to the MySql database. A more efficient implementation would use
     * a connection pool.
     */

    private Connection getConnection()
    throws SQLException {
        return DriverManager.getConnection(dbURL, dbUsername, dbPassword);
    }
   
    /**
     * Close the given resources.
     */

    private void close(Connection con, PreparedStatement ps, ResultSet rs) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                log.error("Could not close connection.", e);
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                log.error("Could not close prepared statement.", e);
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("Could not close result set.", e);
            }
        }
    }

    public void loadAccountGlobalAttributes(Account account)
    throws DatasourceException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
       
        try {
            con = getConnection();
           
            // scores
            ps = con.prepareStatement("SELECT score FROM scores " +
                    "WHERE user = ?");
            ps.setString(1, account.getUserID());
            rs = ps.executeQuery();
            if (rs.next()) {
                try {
                    account.setAttribute("score", new Integer(rs.getInt("score")),
                            Attribute.SCOPE_GLOBAL, Attribute.FLAG_SERVER_ONLY);
                } catch (Exception e) {
                    throw new DatasourceException(e);
                }
            }
            close(null, ps, rs);
           
            // title
            ps = con.prepareStatement("SELECT title FROM titles " +
            "WHERE user = ?");
            ps.setString(1, account.getUserID());
            rs = ps.executeQuery();
            if (rs.next()) {
                try {
                    account.setAttribute("title", rs.getString("title"), Attribute.SCOPE_GLOBAL,
                            Attribute.FLAG_SERVER_ONLY);
                } catch (Exception e) {
                    throw new DatasourceException(e);
                }
            }
        } catch (SQLException e) {
            throw new DatasourceException(e);
        } finally {
            close(con, ps, rs);
        }  
    }
   
    public void saveAccountAttribute(Account account, Attribute attr)
    throws DatasourceException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
       
        try {                      
            // set up prepared statement according to the attribute
            if ("score".equals(attr.getName())) {
                con = getConnection();
                ps = con.prepareStatement("INSERT INTO scores (user, score) VALUES " +
                        "(?, ?) ON DUPLICATE KEY UPDATE score = ?");
                ps.setString(1, account.getUserID());
                ps.setInt(2, (Integer)attr.getValue());
                ps.setInt(3, (Integer)attr.getValue());
                ps.executeUpdate();
            } else if ("title".equals(attr.getName())) {
                con = getConnection();
                ps = con.prepareStatement("INSERT INTO titles (user, title) VALUES " +
                "(?, ?) ON DUPLICATE KEY UPDATE title = ?");
                ps.setString(1, account.getUserID());
                ps.setString(2, attr.getValue().toString());
                ps.setString(3, attr.getValue().toString());
                ps.executeUpdate();
            } else {
                // datasource entry in union.xml not configured properly
                throw new DatasourceException("Datasource method called for an attribute [" +
                        attr.getName() + "] it is not able to handle. " +
                        "Check union.xml configuration.");
            }
        } catch (SQLException e) {
            throw new DatasourceException(e);
        } finally {
            close(con, ps, rs);
        }
    }
   
    public void removeAccountAttribute(Account account, Attribute attr)
    throws DatasourceException {
        Connection con = null;
        PreparedStatement ps = null;
       
        try {
            con = getConnection();
           
            // set up prepared statement according to the attribute
            if ("score".equals(attr.getName())) {
                ps = con.prepareStatement("REMOVE FROM scores " +
                        "WHERE user = ?");
            } else if ("title".equals(attr.getName())) {
                ps = con.prepareStatement("REMOVE FROM titles " +
                "WHERE user = ?");
            } else {
                // datasource entry in union.xml not configured properly
                throw new DatasourceException("Datasource method called for an attribute it " +
                        "is not able to handle. Check union.xml configuration.");
            }
            ps.setString(1, account.getUserID());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DatasourceException(e);
        } finally {
            close(con, ps, null);
        }
    }
   
    public boolean containsAccount(String userID)
    throws DatasourceException {  
        // this datasource does not handle account
        return false;
    }

    public String createAccount(String userID, String password)
    throws DatasourceException {  
        // this datasource does not handle account
        return null;
    }
   
    public String saveAccount(Account account)
    throws DatasourceException {
        // this datasource does not handle account
        return null;
    }

    public List<String> getAccounts()
    throws DatasourceException {
        // this datasource does not handle account
        return null;
    }

    public String getPassword(String userID)
    throws DatasourceException {      
        // this datasource does not handle account
        return null;
    }

    public void loadAccount(Account account)
    throws DatasourceException {
        // this datasource does not handle account
    }
   
    public String removeAccount(String userID)
    throws DatasourceException {
        // this datasource does not handle account        
        return null;
    }
   
    public void loadAccountRoomAttributes(Account account, String roomID)
    throws DatasourceException {
        // this datasource does not handle room scoped attributes
    }

    public void loadAllAccountAttributes(Account account)
    throws DatasourceException {
        // this datasource only stores two global attributes (score and title)
        loadAccountGlobalAttributes(account);
    }

    public void loadRoomAttributes(Room room)
    throws DatasourceException {
        // this datasource does not handle room attributes
    }

    public void loadServerAttributes(Server server)
    throws DatasourceException {
        // this datasource does not handle server attributes
    }

    public void removeRoomAttribute(Room room, Attribute attr)
    throws DatasourceException {
        // this datasource does not handle room attributes
    }

    public void removeServerAttribute(Attribute attr)
    throws DatasourceException {
        // this datasource does not handle server attributes
    }
   
    public void saveRoomAttribute(Room room, Attribute attr)
    throws DatasourceException {
        // this datasource does not handle room attributes
    }

    public void saveServerAttribute(Attribute attr)
    throws DatasourceException {
        // this datasource does not handle server attributes
    }

 
    public void shutdown() {
        // since we create a new connection for each call rather than using resource such as
        // a pool there is nothing to clean up
    }
}

Configuration

Edit union.xml to add the datasource to the chain of datasources used by Union. We'll add an entry for MySqlAccountDatasource while keeping the default Union Derby datasource to handle accounts and all other attributes.

Here is the code for the persistence section of union.xml.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<persistence>
    <datasource id="sqlDS">            
        <class>net.user1.union.example.datasource.MySqlAccountDatasource</class>
            <attributes>
                <attribute name="dbURL">jdbc:mysql://example.com:3306/myDB</attribute>
                <attribute name="dbUsername">dbUsername</attribute>
                <attribute name="dbPassword">dbPassword</attribute>
            </attributes>
            <account-attributes>
            <scope id=""> <!-- global scope -->
                    <name>score</name>
                    <name>title</name>
            </scope >
            </account-attributes>
        </datasource>
    <datasource id="union"/> <!-- handle everything else -->
</persistence>

MySQL Database

Go to www.mysql.com for information on installing MySQL and creating a database. You will need to download a MySQL JDBC driver here, place it in the lib directory, and then add it to the CLASSPATH. The easiest way to do this is edit the startserver script to include the MySQL JDBC driver jar.

In startserver.sh:

1
2
#!/bin/sh
java -Dfile.encoding=UTF-8 -cp lib/mysql.jar:lib/union.jar:lib/stax-api-1.0.1.jar:lib/wstx-asl-3.2.6.jar net.user1.union.core.UnionMain start &

In startserver.bat:

1
java  -Dfile.encoding=UTF-8 -cp lib\mysql.jar;lib\union.jar;lib\stax-api-1.0.1.jar;lib\wstx-asl-3.2.6.jar net.user1.union.core.UnionMain start

Finally, here is the SQL to create the two tables used in this example:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `scores` (
  `user` varchar(256) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY  (`user`)
);

CREATE TABLE `titles` (
  `user` varchar(256) NOT NULL,
  `title` varchar(64) NOT NULL,
  PRIMARY KEY  (`user`)
);