Thursday 13 February 2014

Configuring OpenXava with MySQL Database

Configuring OpenXava with MySQL Database:
------------------------------------------------
Install
1) MySQL database (ex. MySQL 5.5)
2) MySQL connector J (eg. mysql-connector-java-5.1.22-bin.jar)
3) OpenXava 4.9.1

Steps to follow :

1) Create a new project (ex. Academy) according to the steps mentioned in the OpenXava reference guide. (http://openxava.wikispaces.com/reference_en)

2) Go to MySQL DB Console and create a new database schema such as eg. academydb
also create a table using the following scrript
CREATE  TABLE IF NOT EXISTS `ACADEMYDB`.`ACADEMY` (
  `academyId` INT(3) NOT NULL ,
  `academyName` VARCHAR(25) NOT NULL,
  `city` VARCHAR(25),
  `state` VARCHAR(25),
  PRIMARY KEY (`academyId`) ,
  UNIQUE INDEX `ACADEMY_NAME_UNIQUE` (`academyName` ASC) );

3. Place MySQL connector jar into
{OpenXavaBase}/tomcat/lib
{OpenXavaBase}/workspace/OpenXavaTest/lib

4. Open the context.xml file in
{OpenXavaBase}/tomcat/conf folder.
Insert the following lines into the file.
<Resource name="jdbc/AcademyDS" auth="Container" type="javax.sql.DataSource"
                                maxActive="20" maxIdle="5" maxWait="10000"
                                username="root" password="root@123" driverClassName="com.mysql.jdbc.Driver"
                                url="jdbc:mysql://localhost:3306/academydb"/>

5. Go to the project folder’s build.xml (Academy/build.xml) and change updateSchema target as follows. You have to change the value of the schema.path to point to the MySQL connector jar which is available in OpenXavaTest/lib folder.
<target name="updateSchema">
                                <ant antfile="../OpenXava/build.xml" target="updateSchemaJPA">                                    
                                                <property name="persistence.unit" value="junit"/>
                                                <property name="schema.path" value="../OpenXavaTest/lib/mysql-connector-java-5.1.22-bin.jar"/>
                                </ant>
                </target>          

6.Go to persistence.xml (Academy/persistence/META-INF/persistence.xml)
Change the default persistence unit as follows
 <!-- Tomcat + MySQL-->
    <persistence-unit name="default">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
                <non-jta-data-source>java:comp/env/jdbc/AcademyDS</non-jta-data-source>
                <class>org.openxava.session.GalleryImage</class>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
        </properties>
    </persistence-unit>

<!-- JUnit MySQL-->
    <persistence-unit name="junit">
                <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
                                <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
                                <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
                                <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/academydb"/>
        </properties>
    </persistence-unit>  

7.Go to hibernate.cfg.xml (Academy/persistence/hibernate.cfg.xml) and change the hibernate.dialect to org.hibernate.dialect.MySQLDialect as follows
<!-- Tomcat + MySQL-->
                                <property name="hibernate.connection.datasource">java:comp/env/jdbc/AcademyDS</property>
                                <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
                                <property name="hibernate.jdbc.use_get_generated_keys">false</property>                            
                                <property name="hibernate.show_sql">false</property>

& now you run updateSchema target, it will create war & unzip it under webapps folder in
{OpenXavaBase}\tomcat\webapps\Academy, that will access the MySQL to retrieve and save data.

OpenXava RETAILDB DataModel MySQL DB Script

-- -----------------------------------------------------
-- Table RETAILDB.CUSTOMER
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CUSTOMER (
  customerId INTEGER NOT NULL ,
  customerName VARCHAR(50) NOT NULL ,
  PRIMARY KEY (customerId) ,
  UNIQUE INDEX customerName_UNIQUE (customerName ASC) );

-- -----------------------------------------------------
-- Table RETAILDB.CATEGORY
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.CATEGORY (
  categoryCode VARCHAR(5) NOT NULL ,
  categoryName VARCHAR(50) NOT NULL ,
  PRIMARY KEY (categoryCode) ,
  UNIQUE INDEX categoryName_UNIQUE (categoryName ASC) );

-- -----------------------------------------------------
-- Table RETAILDB.PRODUCT
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.PRODUCT (
  productCode VARCHAR(10) NOT NULL ,
  productName VARCHAR(50) NOT NULL,
  description VARCHAR(100) ,  
  category_categoryCode VARCHAR(5) NOT NULL ,
  PRIMARY KEY (productCode) ,
  UNIQUE INDEX productName_UNIQUE (productName ASC),
 INDEX category_categoryCode_idx (category_categoryCode ASC) ,
CONSTRAINT category_categoryCode0
    FOREIGN KEY (category_categoryCode)
    REFERENCES RETAILDB.CATEGORY (categoryCode));
 

-- -----------------------------------------------------
-- Table RETAILDB.ITEM
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.ITEM (
  itemCode VARCHAR(10) NOT NULL ,
  itemName VARCHAR(50) NOT NULL,  
  unitPrice NUMERIC NOT NULL,
  product_productCode VARCHAR(10) NOT NULL ,
  PRIMARY KEY (itemCode) ,
  UNIQUE INDEX itemName_UNIQUE (itemName ASC),
 INDEX product_productCode_idx (product_productCode ASC) ,
CONSTRAINT product_productCode0
    FOREIGN KEY (product_productCode)
    REFERENCES RETAILDB.PRODUCT (productCode));

 
-- -----------------------------------------------------
-- Table RETAILDB.IMAGES
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.IMAGES (
  ID VARCHAR(255) NOT NULL ,
  GALLERY VARCHAR(255) ,
  IMAGE VARBINARY(255) ,
  PRIMARY KEY (ID));

-- -----------------------------------------------------
-- Table RETAILDB.INVOICE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS RETAILDB.INVOICE (
  id VARCHAR(32) NOT NULL ,
  customer_customerId INTEGER NOT NULL ,
  invoiceNo INTEGER NOT NULL,
  year INTEGER,
  date TIMESTAMP,
  vatPercentage INTEGER,  
  remarks VARCHAR(255),
PRIMARY KEY (id) ,
UNIQUE INDEX invoiceNo_UNIQUE (invoiceNo ASC),
INDEX customer_customerId_idx (customer_customerId ASC) ,
CONSTRAINT customer_customerId0
    FOREIGN KEY (customer_customerId)
    REFERENCES RETAILDB.CUSTOMER (customerId ));

-- -----------------------------------------------------
-- Table RETAILDB.INVOICEDETAIL
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS RETAILDB.INVOICEDETAIL (
  id VARCHAR(32) NOT NULL ,
  invoice_id VARCHAR(32) NOT NULL,
  item_itemCode VARCHAR(10) NOT NULL ,
  quantity INTEGER NOT NULL,
PRIMARY KEY (id) ,
INDEX invoice_id_idx (invoice_id ASC) ,
CONSTRAINT invoice_id0
    FOREIGN KEY (invoice_id)
    REFERENCES RETAILDB.INVOICE (id ),
INDEX item_itemCode_idx (item_itemCode ASC) ,
CONSTRAINT item_itemCode0
    FOREIGN KEY (item_itemCode)
    REFERENCES RETAILDB.ITEM (itemCode));

-- -----------------------------------------------------
-- Table RETAILDB.XYZ
-- -----------------------------------------------------

Groovy/Grails Sample Questions and Answers Set II

Groovy/Grails Questions & Answers - 3.2 SET II:
-----------------------------------------------
1. What is the in-built database server which is used by grails apps?
A. H2 DB Server (ANSWER)
B. MySQL DB Server
C. PostgreSQL DB Server
D. Mongo DB Server (No SQL Database)
---------------------------------------------------------------------------------------------
2. In the given Open Source Framework, Which one is Apache's Web Framework?
A. Hibernate
B. Spring MVC
C. Struts (ANSWER)
D. Grails
---------------------------------------------------------------------------------------------
3. In the given Open Source Framework, Which one is not belongs to Web Framework?
A. Grails
B. Hibernate (ANSWER)
C. Struts
D. Rails
---------------------------------------------------------------------------------------------
4. In the given Web Framework, Which option is not the correct compination?
A. JRuby/Rails
B. Groovy/Grails
C. Scala/Play
D. JRuby/Grails (ANSWER)
---------------------------------------------------------------------------------------------
5. Convention over configuration (also known as coding by convention) is a software design paradigm which seeks to decrease the number of decisions that developers need to make, gaining simplicity, but not necessarily losing flexibility. Which option is followed this concept?
A. Ruby on Rails
B. Groovy on Grails
C. All of the above (ANSWER)
D. None of the above
---------------------------------------------------------------------------------------------
6. In the given option, Which option is not belongs to MVC Framework?
A. Struts -2.3.12
B. JavaServer Faces - 2.1
C. Play -2.1.1
D. Google Web Toolkit(GWT) -2.5.1 (ANSWER)
---------------------------------------------------------------------------------------------
7. In the given option, Which option is not belongs to JavaScript Framework?
A. Ext JS-4.2
B. Google Web Toolkit-2.4 (ANSWER)
C. jQuery UI-1.9
D. DOJO-1.8
---------------------------------------------------------------------------------------------
8. Which file contains configuration properties and information needed when running your application?.
It contains properties used by your services, controllers, etc.
A. Config.groovy (ANSWER)
B. BootStrap.groovy
C. BuildConfig.groovy
D. DataSource.groovy
---------------------------------------------------------------------------------------------
9. Which file contains information relevant to building and deploying your application, such as repositories, jar file dependencies and war file name.?
A. Config.groovy
B. BuildConfig.groovy (ANSWER)
C. UrlMappings.groovy
D. DataSource.groovy
---------------------------------------------------------------------------------------------
10. Which file that allows you to define code that is ran every time your grails application starts up.?
A. BuildConfig.groovy
B. Config.groovy
C. UrlMappings.groovy
D. BootStrap.groovy (ANSWER)
---------------------------------------------------------------------------------------------
11. Explain about Grails shell:
A. Swing-based command console similar to the Groovy console. It will execute code against a full environment that has access to all your domain classes and quickly test out code that would go into controllers and services.

B. Interactive mode of the grails command line interface. It lets you run Gant scripts in the script runner one after another.

C. Headless version of Grails console.This is useful when you want to access and test out code in a remote SSH-based server.  It does not reload when domain classes change like the console does, so it is useful also for long-running scripts, although the new run-script command in Grails  (ANSWER)

D.  None of the above
---------------------------------------------------------------------------------------------
12. Which environments are available by default in Grails?
A. Development(dev), Test(test), Staging(stage) & Production(prod)
B. Production (prod), Staging (stage) & Development(dev)
C. Staging(prod), Test(test) & Development (dev)
D. Production(prod), Test(test) & Development(dev) (ANSWER)
---------------------------------------------------------------------------------------------
13. What is the difference between run-app and run-war?
A. run-war will compile and builds a war file that it runs from the embedded container ( Tomcat unless you’ve changed it ).
   run-app runs the application from compiled sources in the file system.
   This means that any change to controllers, views, etc will be reflected immediately by run-app, but not by run-war (ANSWER)
B. There are no difference between run-app and run-war.
C. run-app will compile and builds a war file that it runs from the embedded container ( Tomcat unless you’ve changed it ).
   run-war runs the application from compiled sources in the file system.
   This means that any change to controllers, views, etc will be reflected immediately by run-war, but not by run-app (ANSWER)
D. None of the above.
---------------------------------------------------------------------------------------------
14. "select distinct s.name from Student s" - This query of the domain objects belongs to
A. Dynamic finder
B. HSQL is a query language that looks like SQL but is fully object-oriented. (ANSWER)
C. Criterias are a DSL based on Hibernate Criterias and Groovy builders
D. None of the above.
---------------------------------------------------------------------------------------------
15. Student.findByName('Siddharth') added to domain classes - This query of the domain objects belongs to
A. Criterias are a DSL based on Hibernate Criterias and Groovy builders
B. Dynamic finder. (ANSWER)
C. HSQL is a query language that looks like SQL but is fully object-oriented.
D. All of the above.
---------------------------------------------------------------------------------------------
16. Gorm provides two auto-timestamping variables, what are they?
A. createdDateTime and updatedDateTime
B. createdDate & updatedDate
C. createdDateTS and updatedDateTS
D. dateCreated & lastUpdated (ANSWER)
---------------------------------------------------------------------------------------------
17. I have a property such as 'password' in my domain class that I don’t want to be persisted, how do I make sure it doesn’t generate a field in my database?
A. static transient = ['password'] (ANSWER)
B. static encrypt = ['password']
C. static String = ['password']
D. static volatile = ['password']
---------------------------------------------------------------------------------------------
18. In the given option, Which one is not belongs to programming language?
A. Java
B. Groovy
C. Scala
D. Javascript
---------------------------------------------------------------------------------------------