Spring 3 Part 7: Spring with Databases

This is the 7 th part of the Spring 3 Series.

Create MySQL 5.6 database ‘payroll’,

CREATE DATABASE payroll;

Next create a user for example, user ‘ojitha’

 CREATE USER 'ojitha'@'localhost' IDENTIFIED BY 'ojitha';

Need to grant the ‘payroll’ access to user ‘ojitha’ as shown in the following.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON payroll.* TO 'ojitha'@'localhost';

You can download freeware of TOAD for MySQL from here.

image

Let create a first table, that contains all the states in Australia. You can create a table using visually using the icon given directly below the Tables tab in the Object Explorer as shown in the above diagram.

CREATE TABLE payroll.STATE (
   CODE CHAR(3) ASCII COMMENT 'State code',
   STATE VARCHAR(30),
  PRIMARY KEY (CODE)
) ENGINE = InnoDB COMMENT = 'Australian states' ROW_FORMAT = DEFAULT;

In the my sql console you can the newly added table using the following command

SHOW TABLES;

However, I would recommend TOAD because it is nice tool to do all the kind of database stuff without pain.

CREATE TABLE payroll.ADDRESS (
   ID INT,
   STREET_NUMBER VARCHAR(20),
   SUBURB VARCHAR(15) NOT NULL,
   STATE CHAR(3) ASCII NOT NULL,
  FOREIGN KEY (STATE) REFERENCES payroll.state (CODE) ON UPDATE RESTRICT ON DELETE RESTRICT,
  PRIMARY KEY (ID)
) ENGINE = InnoDB COMMENT = 'Addresses' ROW_FORMAT = DEFAULT;

Relationships are shown in the following diagram

image

Let’s start with the easy example ‘state’.

Create DAO interface which holds CURD operations general for all the xxxDAOs

package au.com.ojitha.blogspot.spring3.part7;

import java.io.Serializable;
import java.util.List;

public interface DAO<T, K extends Serializable> {

    //CURD operations
    K create (T instance);
    void update(T t);
    T findByPrimaryKey(K id);
    List<T> findAll();
    void delete(T t);

}

In this example, we are going to implement DAO for the state: StateDAO which holds ‘state’ specific methods only. The ‘generic type declaration’ defines two type parameters, respectively type variable T for the return type that is State bean and the type variable K for the primary key that is String type. 

package au.com.ojitha.blogspot.spring3.part7;

public interface StateDAO<State> extends DAO<State, String> {
    public State getStateCode(String state);
}

As shown in the above code, ‘getStateCode’ specific to ‘state’. The StateDAO extends the DAO and generic type invocations are State and String as explained above.

Here, the Java Bean standard, State bean which will be used as type argument in the StateDAOImpl.

package au.com.ojitha.blogspot.spring3.part7;

public class State{
    
    private String code;
    private String state;
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getState() {
        return state;
    }
    public void setState(String state) {
        this.state = state;
    }
    

}

In the following code, you will find the StateDAOImpl which the implementation of the StateDAO. There are two advantages of using spring DAOs

  1. Datasource is injected and it is reusable across all the custom DAOs
  2. Proprietary RDBMS or JDBC specific SQLException translation to generic spring based exceptions.

The second advantage is very important because it allows to change the database any time.

package au.com.ojitha.blogspot.spring3.part7;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.object.MappingSqlQuery;
import org.springframework.stereotype.Repository;

@Repository("stateDAO")
public class StateDAOImpl implements StateDAO<State> {

    private DataSource datasource;
    
    
    public DataSource getDatasource() {
        return datasource;
    }

    @Resource(name="dataSource")
    public void setDatasource(DataSource datasource) {
        this.datasource = datasource;
    }

    public String create(State instance) {
        // TODO Auto-generated method stub
        return null;
    }

    public void update(State t) {
        // TODO Auto-generated method stub
        
    }

    public State findByPrimaryKey(String id) {
        // TODO Auto-generated method stub
        return null;
    }

    public List<State> findAll() {
        
        return (new SelectAll()).execute();
    }
    
    public void delete(State t) {
        // TODO Auto-generated method stub
        
    }

    public State getStateCode(String state) {
        // TODO Auto-generated method stub
        return null;
    }

    private class SelectAll extends MappingSqlQuery<State>{

        SelectAll(){
            super(datasource, "SELECT CODE, STATE FROM STATE");
        }
        
        @Override
        protected State mapRow(ResultSet rs, int rowNum) throws SQLException {
            State state = new State();
            state.setCode(rs.getString("code"));
            state.setState(rs.getString("state"));
            return state;
        }
        
    }
}

In the above code, only the findAll() method has been implemented (for simplicity). Instead of using JDBCTemplate, I’ve used MappingSqlQuery.

Here the spring configuration file, app-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <description>Example configuration to get you started.</description>

    <context:component-scan base-package="au.com.ojitha.blogspot.spring3.part7" />
    <context:annotation-config></context:annotation-config>
    
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName">
                <value>${jdbc.driverClassName}</value>
            </property>
            <property name="url">
                <value>${jdbc.url}</value>
            </property>
            <property name="username">
                <value>${jdbc.username}</value>
            </property>
            <property name="password">
                <value>${jdbc.password}</value>
            </property>
        </bean>
        <context:property-placeholder location="classpath:META-INF/spring/jdbc.properties"/>
</beans>

The jdbc.properties contains the values for the above place holders such as jdbc.driverClassName

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/payroll
jdbc.username=ojitha
jdbc.password=ojitha

We are ready to run the example with Client application as shown in the following code

package au.com.ojitha.blogspot.spring3.part7;

import java.util.List;

import org.springframework.context.support.GenericXmlApplicationContext;

public class Client {

    /**
     * @param args
     */
    public static void main(String[] args) {
        GenericXmlApplicationContext ctx = new GenericXmlApplicationContext();
        ctx.load("classpath:META-INF/spring/app-context.xml");
        ctx.refresh();
        StateDAO<State> stateDAO = ctx.getBean("stateDAO", StateDAO.class);
        List<State> states =stateDAO.findAll();
        for (State state : states) {
            System.out.println("code: "+state.getCode()+ ", State: "+state.getState());
        }

    }

}

Here the pom.xml which configure the MySQL connector and Spring-jdbc

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.springframework.samples.spring</groupId>
    <artifactId>spring-utility</artifactId>
    <version>1.0.0.CI-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>Spring Utility</name>
    <url>http://www.springframework.org</url>
    <description>
        <![CDATA[
      This project is a minimal jar utility with Spring configuration.
    ]]>
    </description>
    <properties>
        <maven.test.failure.ignore>true</maven.test.failure.ignore>
        <spring.framework.version>3.2.2.RELEASE</spring.framework.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.7</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.framework.version}</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.framework.version}</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.14</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.24</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.2.2.RELEASE</version>
        </dependency>
<dependency>
    <groupId>hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>1.8.0.10</version>
</dependency>
            

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                </configuration>
            </plugin>

        </plugins>
    </build>
</project>

The next step is how to test this code. If you see the above pom.xml, I’ve already add the HSQL plugin for embedded databases.

package au.com.ojitha.blogspot.spring3.part7;

import static org.junit.Assert.*;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@ContextConfiguration
@RunWith(SpringJUnit4ClassRunner.class)
public class StateDAOImplTest {

    @Autowired
    StateDAO stateDao;
    
    @Test
    public void testFindAll() {
        assertEquals(2, this.stateDao.findAll().size());

    }

}

This is just a simple test, as sated in the StateDAOImplTest-context.xml file, HSQL embedded database used with DDL(schema.sql) and DML (data.sql)

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <description>Example configuration to get you started.</description>

    <context:component-scan base-package="au.com.ojitha.blogspot.spring3.part7" />
    <context:annotation-config></context:annotation-config>

    <jdbc:embedded-database id="dataSource" type="HSQL">
        <jdbc:script location="/au/com/ojitha/blogspot/spring3/part7/schema.sql" />
        <jdbc:script location="/au/com/ojitha/blogspot/spring3/part7/data.sql" />
    </jdbc:embedded-database>

</beans>

As shown in the following schema.sql DDL script, state table is created, because at this time we are only testing StateDAOImpl.java,

CREATE TABLE state (
    CODE CHAR(3) NOT NULL,
    STATE VARCHAR(30),
    PRIMARY KEY (CODE)
);

As well, DML script that is data.sql insert two records to the sate database

insert into state values ('ACT', 'Capital');
insert into state values ('NSW', 'New South Wales');

 

You can download the source code for this blog from the GitHub.

Comments

Post a Comment

commented your blog

Popular posts from this blog

How To: GitHub projects in Spring Tool Suite

Image
The first step is sign up in the GitHub and create a  new project. For example I’ve created a new project Spring3part7 in the GitHub. Before add the projects you need to configure STS for GitHub access. For example, you need to add configuration as shown in the following picture Git local repository also important Now you are ready to pull the project from the GitHub, in the STS import menu select the following Now type the project name and click the ‘Search’ as shown in the following. However, when you select the found project click ‘Finish’ nothing will change in the STS interface. Now you are going to create real project. Here I am going to create simple utility project. This template project need to be created inside the imported GitHub project You have to give the same project name of the GitHub project as shown in the following project Now your project is ready to push. Before that you need to add ignore flag to all the folders and files except pom...
Continue »

Parse the namespace based XML using Python

In this blog, I am considering how to parser and modify the xml file using python. For example, I need to parser the following xml 1 (slightly modified for this blog) and need to write the modified xml to out.xml file. Here the country.xml <?xml version="1.0"?> <actors xmlns:fictional="http://characters.example.com" xmlns="http://people.example.com"> <actor type='T1'> <name>John Cleese</name> <fictional:character>Lancelot</fictional:character> <fictional:character>Archie Leach</fictional:character> </actor> <actor type='T2'> <name>Eric Idle</name> <fictional:character>Sir Robin</fictional:character> <fictional:character>Gunther</fictional:character> <fictional:character>Commander Clement</fictional:character> </actor> </actors> In ...
Continue »