Thursday, 3 April 2014

Spring Database connection with HSQLDB using datasource

Below is a simple example to try JDBC connection in Spring.  This example does not have the complete code


DAO

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.DataSource;

public class EmployeeDAO {

    private DataSource datasource;

    public EmployeeDAO() {
        
    }
    
    public EmployeeDAO(DataSource datasource) {
        super();
        this.datasource = datasource;
    }


    public DataSource getDatasource() {
        return datasource;
    }


    public void setDatasource(DataSource datasource) {
        this.datasource = datasource;
    }


    public void countEmployee() throws Exception{
        Connection con = null;
        try {
            con = datasource.getConnection();
            PreparedStatement ps = con.prepareStatement("select count(*) from employeebimap");
            ResultSet rs = ps.executeQuery();
            if(rs.next())
            {
                System.out.println("select count(*) from employeebimap ---> "+ rs.getString(1));
            }

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (con != null)
                con.close();
        }

    }
}




database-beans.xml in classpath

<beans br="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <bean br="" class="org.springframework.jdbc.datasource.DriverManagerDataSource" name="datasource">
        <property name="driverClassName" value="org.hsqldb.jdbc.JDBCDriver">
        <property name="url" value="jdbc:hsqldb:hsql://localhost/section1">
        <property name="username" value="sa">
        <property name="password" value="">
    </property></property></property></property></bean>

    <bean class="EmployeeDAO" name="EmployeeDAO">
        <property name="datasource" ref="datasource">
    </property></bean>

    <bean autowire="constructor" br="" class="EmployeeDAO" name="EmployeeDAOAutowired">
</bean>
</beans>



EmployeeDAOTest

import EmployeeDAO;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class EmployeeDAOTest {

    ApplicationContext ctx = new ClassPathXmlApplicationContext("database-beans.xml");
    
    @Test
    public void testEmployee() throws Exception{
    
        EmployeeDAO dao = (EmployeeDAO) ctx.getBean("EmployeeDAO");
        dao.countEmployee();
    }
    
    @Test
    public void testEmployeeAutowiredConstructor() throws Exception{
    
        EmployeeDAO dao = (EmployeeDAO) ctx.getBean("EmployeeDAOAutowired");
        dao.countEmployee();
    }

}


OUTPUT:

select count(*) from employeebimap ---> 2
select count(*) from employeebimap ---> 2


2 comments: