About MySQL

MySQL is an open source database maintained by Oracle : link.

How to use MySQL Module

Import MySQL module into your project

Add the needed dependencies into your pom.xml

						
							org.ddbstoolkit.toolkit.modules.datastore.mysql
							ddbstoolkit-mysql
							1.0.0-beta2
							compile
						

Create your data model

You need to create your data classes with the following requirements :

• For non distributed data: your classes need to implement IEntity
• For distributed data: your classes need to extend DistributedEntity

Primary keys are indicated with the @Id annotation. @EntityName define the column name in the database.

Example for distributed Actor and Film data

				public class Actor extends DistributedEntity {

				    @Id
				    @EntityName(name="actor_id")
				    private Integer actorId;
				
				    @EntityName(name="actor_name")
				    private String actorName;
				
				    @EntityName(name="film_id")
				    private Integer filmId;
				
					public Integer getActorId() {
						return actorId;
					}
				
					public void setActorId(Integer actorId) {
						this.actorId = actorId;
					}
				
					public String getActorName() {
						return actorName;
					}
				
					public void setActorName(String actorName) {
						this.actorName = actorName;
					}
				
					public Integer getFilmId() {
						return filmId;
					}
				
					public void setFilmId(Integer filmId) {
						this.filmId = filmId;
					}

				public class Film extends DistributedEntity {

				    @Id
					@EntityName(name="film_id")
				    private Integer filmID;
				
					@EntityName(name="film_name")
				    private String filmName;
				
				    private Actor[] actors;
				    
				    public Integer getFilmID() {
						return filmID;
					}
				
					public void setFilmID(Integer filmID) {
						this.filmID = filmID;
					}
				
					public String getFilmName() {
						return filmName;
					}
				
					public void setFilmName(String filmName) {
						this.filmName = filmName;
					}
					
					public Actor[] getActors() {
						return actors;
					}
				
					public void setActors(Actor[] actors) {
						this.actors = actors;
					}
				}

Instantiate your MySQL module

				MySQLConnector mysqlConnector = new MySQLConnector("jdbc:mysql://localhost:3306/myDB", "user", "password");
				DistributableEntityManager manager = new DistributedMySQLTableManager(mysqlConnector);

MySQL Module actions

Add an element

				manager.open();
		
				Actor anActor = new Actor();
				anActor.setActorName("Steven");
					
				manager.add(anActor);
				
				manager.close();

Update an element

				manager.open();
		
				Actor anActor = new Actor();
				anActor.setActorId(3);
				anActor.setActorName("Steven");
					
				manager.update(anActor);
				
				manager.close();

Delete an element

				manager.open();
		
				Actor anActor = new Actor();
				anActor.setActorId(3);
					
				manager.delete(anActor);
				
				manager.close();

Read an element

				manager.open();
		
				Actor anActor = new Actor();
				anActor.setActorId(3);
					
				anActor = manager.read(anActor);
				
				manager.close();

Read last added element

				manager.open();
		
				Actor anActor = manager.readLastElement(new Actor());
				
				manager.close();

List elements

				manager.open();
		
				//List all films
				List films = manager.listAll(new Film(), null, null);
		
				//List all films ordered by film_ID
				films = manager.listAll(new Film(), null, OrderBy.get("filmId", OrderByType.ASC));
				
				List conditions = new ArrayList<>();
        		conditions.add("film_name LIKE 'S%'");
				
				//List all films who starts with 'S'
				films = manager.listAll(new Film(), conditions, null);
				
				//Same thing with Conditions
				Conditions conditionLike = Conditions.createConditions().add(
				Conditions.like("filmName", "S%"));
				List results = manager.listAll(new Film(),conditionLike, null);
				
				manager.close();

Load elements linked to an entity

				manager.open();
     
				Film lastFilmAdded = manager.readLastElement(new Actor());
				
				//List actors of a movie ordered by actor_id field
				lastFilmAdded = manager.loadArray(lastFilmAdded, "actors", OrderBy.get("actorId", OrderByType.ASC));				
 
				manager.close();

Execute transactions

				manager.open();
				
				//Start a new transaction
				DDBSTransaction transactionAddCommit = new DDBSTransaction("myTransaction");
     
				Actor anActor = new Actor();
				anActor.setActorName("Steven");
				
				transactionAddCommit.add(filmToAdd);
		
				//Execute the transaction
				manager.executeTransaction(transactionAddCommit);
				
				//Commit the transaction
				manager.commit(transactionAddCommit);
				
				//Rollback the transaction
				manager.rollback(transactionAddCommit);				
 
				manager.close();