I'm very new to Spring JDBC. I'm able to connect to my local MS SQL Server instance using manual code, but got the following exception using Spring JDBC:
Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host localhost, named instance \sqlexpress failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:390)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511)
at org.springdemo.demo1.CustomJDBCTemplate.getDBs(CustomJDBCTemplate.java:25)
at org.springdemo.demo1.MainApp.main(MainApp.java:15)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host localhost, named instance \sqlexpress failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
SQL Browser Service is already running.
Following is the beans.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
<bean id="dataSource1"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="url"
value="jdbc:sqlserver://localhost\\SQLExpress;integratedSecurity=true;" />
</bean>
<bean id="dataSource2"
class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver" />
<property name="url"
value="jdbc:sqlserver://localhost\\SQLExpress;integratedSecurity=true;" />
</bean>
<bean id="customJDBCTemplate" class="org.springdemo.demo1.CustomJDBCTemplate">
<property name="dataSources">
<list>
<ref bean="dataSource1" />
</list>
</property>
</bean>
As you can see, I've tried both spring JDBC and commons dbcp, but both failed. Here's the main code:
package org.springdemo.demo1;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.support.rowset.SqlRowSet;
public class MainApp {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(
"beans.xml");
CustomJDBCTemplate template = (CustomJDBCTemplate) context
.getBean("customJDBCTemplate");
SqlRowSet srs = template.getDBs();
while (srs.next()) {
System.out.println(srs.getString("name"));
}
}
}
Here's the template class :
package org.springdemo.demo1;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
public class CustomJDBCTemplate {
private List<DataSource> dataSources;
private List<JdbcTemplate> jdbcTemplateObjects = new ArrayList<JdbcTemplate>();
public void setDataSources(List<DataSource> dataSources) {
this.dataSources = dataSources;
for(DataSource s : dataSources)
this.jdbcTemplateObjects.add(new JdbcTemplate(s));
}
public SqlRowSet getDBs()
{
String query = "SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');";
return jdbcTemplateObjects.get(0).queryForRowSet(query);
}
}
Edit 1: @Shailendra - Here's the manual working code:
package jdbcexample;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Class2 {
final static String DB_URL = "jdbc:sqlserver://localhost\\SQLExpress;integratedSecurity=true;";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// STEP 2: Register JDBC driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL);
// STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql_getDBList = "SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');";
ResultSet rs = stmt.executeQuery(sql_getDBList);
// STEP 5: Extract data from result set
while (rs.next()) {
// Retrieve by column name
String dis = rs.getString("name");
System.out.println(dis);
}
stmt.close();
conn.close();
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}// nothing we can do
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}// end finally try
}// end try
System.out.println("Goodbye!");
}
}
