Monday, January 3, 2011

Writing JDBC Client and Spring Database Client

Below are a simple JDBC client and a Spring Database client.

Let's start with a JDBC client.


package com.amq.test;

import java.sql.*;

public class SimpleJDBCTest {

 private Connection conn;

 public SimpleJDBCTest(){
 }
 
 public void connect(){
  
    try{
        Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
        String url = "jdbc:sybase:Tds:MYLOCALPC:5000/mydatabase";
        conn = DriverManager.getConnection(url, "user","password");
    }
    catch (Exception ex){
        System.out.println("Connect Exception: "+ex.toString());
    }
 }
 
 public void doTests(){
    try{
        System.out.println("......Select test.......");
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery("select name from sysobjects");

        while (rs.next()) {
          String s = rs.getString("name");
          System.out.println(s);
        }

        System.out.println("..........SP with parameters test......");
        PreparedStatement pstmt = conn.prepareStatement("exec sp_spaceused ?");
        pstmt.setString(1, "sysobjects");
        ResultSet rs1 = pstmt.executeQuery();

        while (rs1.next()) {
          String s = rs1.getString(1);
          s = s + " " + rs1.getString(2);
          System.out.println(s);
        }

    }
    catch(Exception ex){
        System.out.println("doTest Exception: "+ex.toString());
    }
  
 }
 
 public static void main(String[] args){
  
    System.out.println("Starting SimpleJDBCTest");
    SimpleJDBCTest simpleJDBCTest = new SimpleJDBCTest();
    simpleJDBCTest.connect();
    simpleJDBCTest.doTests();
 }

}

Now the same client, but using Spring


package com.amq.test;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class SpringDbTest {

 private JdbcTemplate jdbcTemplateDBConnector;
 
 public JdbcTemplate getJdbcTemplateDBConnector() {
    return jdbcTemplateDBConnector;
 }

 public void setJdbcTemplateDBConnector(JdbcTemplate JdbcTemplateDBConnector) {
    this.jdbcTemplateDBConnector = JdbcTemplateDBConnector;
 }

 public void runQuery(){
    if(jdbcTemplateDBConnector == null){
       System.out.println("jdbcTemplateDBConnector not initialized");
       return;
    }
 
    /* Execute select */
    List resultList = 
       jdbcTemplateDBConnector.queryForList("select name from sysobjects");

    System.out.println("Result from Select: "+resultList);
     
    /* Execute SP with parameters */
    Object[] parameters = new Object[] {new String("sysobjects")};
    List resultListSPParam = 
       jdbcTemplateDBConnector.queryForList("exec sp_spaceused ?", parameters);

    System.out.println("Result from SP with PARAMs: "+resultListSPParam);
     
 }

 public static void main(String[] args) {
    try {
      System.out.println("trying to get spring context");
      ApplicationContext ctx =  
            new FileSystemXmlApplicationContext("config/db_config.xml");  
      System.out.println("got spring context");

      SpringDbTest springDBTest = (SpringDbTest)ctx.getBean("SpringDbTest");
      springDBTest.runQuery();
            
    } 
    catch (Exception ex) {
      ex.printStackTrace();
    }
 }
 
}

XML that configures Spring Database access client


<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:util="http://www.springframework.org/schema/util"
  xmlns:oxm="http://www.springframework.org/schema/oxm"
  xsi:schemaLocation="http://www.springframework.org/schema/beans
  http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
  http://www.springframework.org/schema/util 
  http://www.springframework.org/schema/util/spring-util-2.0.xsd
  http://www.springframework.org/schema/oxm 
  http://www.springframework.org/schema/oxm/spring-oxm-1.5.xsd">

  <!-- Primary datasource for reading configurations from the DB -->
   <bean id="dataSource" destroy-method="close"
     class="org.apache.commons.dbcp.BasicDataSource">
      <property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDriver" />
      <property name="url" value="jdbc:sybase:Tds:MYLOCALPC:5000/mydatabase" />
      <property name="username" value="user" />
      <property name="password" value="password" />
   </bean>
 
  <!-- JDBCTemplate to load the config from DB on startup -->
   <bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate">
     <property name="dataSource" ref="dataSource"/>
   </bean>
  
   <bean id="SpringDbTest" class="com.amq.test.SpringDbTest">
     <property name="jdbcTemplateDBConnector" ref="jdbcTemplate">
     </property>
   </bean>

</beans>