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>
|
nice one. A few points:
ReplyDelete1) In the first JDBC client, you don't need to do Class.forName() if you are using Java 6 onwards. JDBC 4.0 made this call obsolete and is happening behind the scenes
2) In the dataSource bean you could have the values of driverName/url/username/password read by a props file.
3) Your DAO (SpringDbTest) could extend Spring's convenience JdbcDaoSupport abstract class and in that case first you wouldn't need your jdbcTemplate bean and second by injecting dataSource to your SpringDbTest bean, you can directly call getJdbcTemplate() in your CRUD operations.