| 50 | | ^(Describe your design here.)^ |
| | 50 | == Database and JDBC template == |
| | 51 | === Database and schema === |
| | 52 | * The choosen database is H2 DB -> [http://www.h2database.com/html/main.html Homepage] |
| | 53 | * The other canditates were: |
| | 54 | * Derby or JavaDB, it is Sun's embedded database used mainly for Java Application, but in comparison to the H2 there are no inner optimizations with the transactions and there are some problems |
| | 55 | * Hypersonic -> The embedded DB of Hibernate, it is simmilar ot H2, but its main purpose is to be used with Hibernate. |
| | 56 | * The H2 database has a JDBC implementation and comes with one jar, added as dependency to the server.core module. |
| | 57 | * It can be embedded in the memory (The virtual machine), in file or to be used as server and client |
| | 58 | * In Sophie 2 it will be used as Embedded in the memory (JVM) and stored into a file. |
| | 59 | * JDBC can access the DB with similar URLS "jdbc:h2:path_on_the_machine;AUTO_SERVER=TRUE", the AUTO_SERVER_TRUE here means that if another client tries to connect to the DB our clent will continue to be connected, and not rejected, also means that if another client is connected already to the DB, our client will not be rejected... |
| | 60 | * Schema -> The schema will be constructed in sucha way that the queries to the DB could be optimal (It will be normalized and if you want for example to see the value of a given key of a given resource at a given revision, you will not need to select the change caused the revision, ie every logiical kind of data will be iiiin separate table) |
| | 61 | * Tables that have unique data will be named in the following convention : T_TABLENAME (T comes from table) |
| | 62 | * Tables that can be viewed with a series of joins on the other tables and are used mainly for ease when selecting will be names MV_TABLENAME (The V is from view, the M comes from multiple (tables)) |
| | 63 | * TODO SCHEMA_AND_DESCRIPTION_HERE |
| | 64 | === JDBC Template === |
| | 65 | * For our purposes using JDBC is enough there is no need of ORM like Hibernate and bean model for it, because we will have small database. |
| | 66 | * The queries for selecting/updating/inserting data can be categorized and there will be one class that gives methods for using them. This class will capsulate all the actions using JDBC and will take care with the JDBC API for the developers. |
| | 67 | * Class : JdbcTemplate -> Responsible for releasing all the JDBC resources that are used for query like ResultSets and Statements. It is not responsible for managing JDBC Connections. |
| | 68 | * The JdbcTemplate class can be constructed with a ConenctionManager (used for retrieving of threadsafe connections to the DB and commit and rollback of transactions) |
| | 69 | * Public methods: |
| | 70 | * {{{public <T> T execute(SQLCallback<T> callback)}}} -> Used for executing SQLCallbacks (Actions that executeeeeee sql queries) to the database. It returns the result of the callback. If there is any error, the current transaction to the DB will be rollbacked and the connection closed (This method must be use by all methods executing queries, it takes care of errors in the JDBC or in the DB) |
| | 71 | * {{{public <T> T execute(final ConnectionCallback<T> action) throws JdbcException}}} -> Used to execute a ConnectionCallback (Actions which use JDBC connections). This execute method uses the mentioned above one, creating a SQLCallback that executes the ConnectionCallback in a connection retrieved by the ConnectionManager. (This method takes care of retrieving the connections, it should release the connection if there is no user made transaction and there no errors) |
| | 72 | * {{{public <T> List<T> queryForList(String sql, RowMapper<T> rowMapper, Object... parameters)}}} -> Method for executing queries to the database which return list data, for example for selecting a list of resource names... |
| | 73 | * {{{public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... parameters)}}} -> Used for executing queries that hase a single result, for example the name of a resource with a given database ID. |
| | 74 | * {{{public <T> T query(final String sql, final ResultSetMapper<T> mapper, final Object... parameters)}}} -> Executes a query with a list of parameters, used by the two methods above. |
| | 75 | * {{{public <T, P> T query(final String sql, final ResultSetMapper<T> mapper, final ParametersSetter<P> parametersSetter)}}} -> The same as the above one, but its query parameters are managed by a given ParameterSetter. |
| | 76 | * {{{public int update(final String sql, final Object... parameters)}}} Used for a single update to the databse, it returns the update count of the query. |
| | 77 | * {{{public int[] updateBatch(final String sql, final Collection<Object[]> parameters)}}} Executes a number of updates to the database with one query string, but with different parameters, used by the above method for one update. |
| | 78 | * {{{public int[] updateBatch(final String sql, final ParametersSetter<Object[]> parametersSetter)}}} The same as the above, but using ParameterSetter to manage the parameters and used by the above. |
| | 79 | * {{{public int[] updateBatch(final String sql, final ParametersSetter<Object[]> parametersSetter)}}} Executes an insert to the database, there are analogical insertBatch method as the update ones. |
| | 80 | * {{{public ConnectionManager getConnectionManager()}}} -> Getter of the ConnectionManager of the template. |
| | 81 | * commit() and rollback() methods for user made transactions that can be commited or rollbacked by the user, the use the ConnectionManager. |
| | 82 | * As you can see there are plenty of public methods some of which should be protected or private (the batch methods, the query and execute methods), They are public because the user may not be able to change the template but wants to write qa query different from just insert/update one query or selllect one object or one list of objects, if you have oppinion on which of these methods should be private/protected, please write it in the review. |
