Connecting Spring Boot JDBCTemplate to SQL Server (MSSQL)

Question

I'm very new to Spring Boot and I'm having trouble trying to set my project up so that it can communicate to SQL Server - more specifically, my JDBCTemplate instance variable is null and for some reason isn't being 'autowired' with the datasource I've specified in my application.properties file. These are the steps I've taken so far:

1. Using STS I created a new Spring Boot project using the Spring Start Project template.
2. I selected Gradle to by the 'Type' and ticked JDBC.
3. I then followed the following tutorial to create an abstract interface (DAO) to SQL Server (http://www.tutorialspoint.com/spring/spring_jdbc_example.htm).
4. If you scroll down the tutorial page to the MainApp.java bit, the first 4 lines of the main method I did not use - because I don't have a beans.xml file. This is where I presume Spring Boot's @Autowired annotation comes in and creates my beans for me?
5. I downloaded the SQL Server jar file from Microsoft and added it to my project as an external JAR by right clicking on my project -> Build Path -> Configure Build Path -> Add External JARs..'. Doing this removed an error I had which indicated that the driverClassName I specified in my application.properties file couldn't be found.

I'll start by displaying the contents of my 'application.properties' file:

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=testdb
spring.datasource.username=sa
spring.datasource.password=myPassword
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerConnection
spring.datasource.initialize=true

Below is my 'JDBCTemplate.java' class which contains my CRUD methods:

package demo;

import java.util.List;

import org.apache.tomcat.jdbc.pool.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

public class BranchJDBCTemplate implements BranchDAO {

    private DataSource dataSource;

    @Autowired
    protected JdbcTemplate jdbcTemplateObject;

    @Autowired
    @Override
    public void setDataSource(DataSource ds) {
        this.dataSource = ds;
        this.jdbcTemplateObject = new JdbcTemplate(dataSource);
    }

    @Override
    public void create(String name) {
        String SQL = "insert into branches (name) values (?)";
        jdbcTemplateObject.update(SQL, name);
        System.out.println("Created Record Name = " + name);
        return;
    }

    @Override
    public Branch getBranch(Integer id) {
        String SQL = "select * from branches where id = ?";
        Branch student = jdbcTemplateObject.queryForObject(SQL, 
                    new Object[]{id}, new BranchMapper());
        return student;
    }

    @Override
    public List<Branch> listBranches() {
        String SQL = "select * from branches";
        List <Branch> branches = jdbcTemplateObject.query(SQL, new BranchMapper());
        return branches;
    }

    @Override
    public void delete(Integer id) {
        String SQL = "delete from branches where id = ?";
        jdbcTemplateObject.update(SQL, id);
        System.out.println("Deleted Record with ID = " + id );
        return;
    }

    @Override
    public void update(Integer id, String name) {
        String SQL = "update Student set name = ? where id = ?";
        jdbcTemplateObject.update(SQL, id);
        System.out.println("Updated Record with ID = " + id );
        return;
    }

}

And finally, here is my 'CustController.java' class which contains the request mapping where I use the JDBCTemplate class to perform a database operation:

package demo;

import java.util.List;

import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class CustController {

    @RequestMapping("/customer")
    public Cust customer(@RequestParam(value="name", required=false, defaultValue="World") String name) {

        BranchJDBCTemplate branchTemplate = new BranchJDBCTemplate();

        List<Branch> branchesList = branchTemplate.listBranches();

        for (Branch branch : branchesList) {
            System.out.print("ID : " + branch.getId());
        }

        return new Cust(12, "Test", "Test");

    }

}

The issue I'm encountering as mentioned previously is that my jdbcTemplateObject instance ...

protected JdbcTemplate jdbcTemplateObject;

is null and therefore throwing an exception on the following line:

List <Branch> branches = jdbcTemplateObject.query(SQL, new BranchMapper());

It isn't being initialised automatically, can anyone point out what I'm doing wrong?

Many thanks!

Tony

Answer 1

You are right, you need to have beans.xml with datasource configured in it.

In CustController class customer() method, you are using new operator as:

    BranchJDBCTemplate branchTemplate = new BranchJDBCTemplate();

and so this branchTemplate instance is not spring manged and so datasource is not autowired resulting in null value of jdbctemplate.

Instead use the annotatioan as:

    @Repository("branchDao")
    public class BranchJDBCTemplate implements BranchDAO {
    ...
    }

and access branchTemplate in CustController as:

    @RestController
    public class CustController {
        @Autowired
        @Qualifier("branchDao")
        BranchJDBCTemplate branchTemplate;  
        ...
    }   

Answer 2

try using the following in your application.properties file

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=testdb;integratedSecurity=false;