12

I have following Spring Boot controller code that works. (Some sensitive text was replaced)

package com.sample.server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.bind.annotation.RequestMapping;

@RestController
public class DetailReportController
{
    @RequestMapping(value="/report/detail", method=RequestMethod.GET)
    public List<UFGroup> detailReport()
    {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("net.sourceforge.jtds.jdbc.Driver");
        dataSource.setUrl("jdbc:jtds:sqlserver://111.11.11.11/DataBaseName;user=sa;password=password");

        JdbcTemplate jt = new JdbcTemplate(dataSource);

        List<UFGroup> results = jt.query(
            "select NID, SCode, SName from UFGroup",
            new RowMapper<UFGroup>()
            {
                @Override
                public UFGroup mapRow(ResultSet rs, int rowNum) throws SQLException
                {
                    return new UFGroup(rs.getInt("NID"), rs.getString("SCode"),
                            rs.getString("SName"));
                }
            });

        return results;
    }

    private static class UFGroup
    {
        public int nid;
        public String scode;
        public String sname;

        public UFGroup(int nid, String scode, String sname)
        {
            this.nid = nid;
            this.scode = scode;
            this.sname = sname;
        }
    }
}

Now I want to externalize the configuration of the datasource. That is, BasicDataSource class, driver class name, datasource URL should be placed into application.properties. How can I do that?

BTW, I am a newbie of Spring, Spring Boot and even Java Beans. All I have is some Java programming experience, mainly for mobile devices. I've spent a few days to study Spring Boot environment, but I was literally overwhelmed. So please, give me the exact instruction with concrete example.

UPDATE: when I applied the answer of M. Deinum, following error occured when I ran the application:

2013-11-18 19:37:54.789  INFO 6868 --- [           main] com.logicplant.uflow.server.Application  : Starting Application on zeo-PC with PID 6868 (C:\Projects\uFlow\Dev\Server\Spring\uFlowServer\build\libs\uFlowServer-1.0.0.jar started by zeo)
2013-11-18 19:37:54.830  INFO 6868 --- [           main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@7f83698f: startup date [Mon Nov 18 19:37:54 KST 2013]; root of context hierarchy
2013-11-18 19:37:55.931  INFO 6868 --- [           main] o.apache.catalina.core.StandardService   : Starting service Tomcat
2013-11-18 19:37:55.932  INFO 6868 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/7.0.42
2013-11-18 19:37:56.009  INFO 6868 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2013-11-18 19:37:56.010  INFO 6868 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1183 ms
2013-11-18 19:37:56.165  INFO 6868 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet 'dispatcherServlet'
2013-11-18 19:37:56.165  INFO 6868 --- [ost-startStop-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
2013-11-18 19:37:56.242  INFO 6868 --- [ost-startStop-1] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2013-11-18 19:37:56.388  INFO 6868 --- [ost-startStop-1] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/report/detail],methods=[GET],params=[],headers=[],consumes=[],produces=[],custom=[]}" onto public java.util.List<com.logicplant.uflow.server.DetailReportController$UFGroup> com.logicplant.uflow.server.DetailReportController.detailReport()
2013-11-18 19:37:56.438  INFO 6868 --- [ost-startStop-1] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2013-11-18 19:37:56.439  INFO 6868 --- [ost-startStop-1] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2013-11-18 19:37:56.788  INFO 6868 --- [ost-startStop-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 622 ms
2013-11-18 19:37:56.881  INFO 6868 --- [           main] o.apache.catalina.core.StandardService   : Stopping service Tomcat
java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:53)
    at java.lang.Thread.run(Unknown Source)
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'detailReportController': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private org.springframework.jdbc.core.JdbcTemplate com.logicplant.uflow.server.DetailReportController.jt; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.springframework.jdbc.core.JdbcTemplate] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:292)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1139)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:537)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:475)
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:299)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:295)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:195)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:665)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:760)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:482)
    at org.springframework.boot.context.embedded.EmbeddedWebApplicationContext.refresh(EmbeddedWebApplicationContext.java:122)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:509)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:278)
    at com.logicplant.uflow.server.Application.main(Application.java:17)
    ... 6 more
Caused by: org.springframework.beans.factory.BeanCreationException: Could not autowire field: private org.springframework.jdbc.core.JdbcTemplate com.logicplant.uflow.server.DetailReportController.jt; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.springframework.jdbc.core.JdbcTemplate] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:505)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:87)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:289)
    ... 20 more
Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.springframework.jdbc.core.JdbcTemplate] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.raiseNoSuchBeanDefinitionException(DefaultListableBeanFactory.java:1051)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:919)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:820)
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:477)
    ... 22 more

For reference, the content of build.grade file (I use Gradle) is as follows: (following M. Deinum's suggestion, I removed the dependency for org.apache.commons.dbcp.)

buildscript {
    repositories {
        maven { url "http://repo.spring.io/libs-snapshot" }
        mavenLocal()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:0.5.0.M5")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'spring-boot'

jar {
    baseName = 'uFlowServer'
    version = '1.0.0'
}

repositories {
    mavenCentral()
    maven { url "http://repo.spring.io/libs-snapshot" }
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web:0.5.0.M5")
    compile("com.fasterxml.jackson.core:jackson-databind")
    compile("org.springframework:spring-jdbc:4.0.0.M3")
    runtime("net.sourceforge.jtds:jtds:1.3.1")
    testCompile("junit:junit:4.11")
}

task wrapper(type: Wrapper) {
    gradleVersion = '1.8'
}

And this is Application.java file, which is the main source file.

package com.sample.server;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.SpringApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;

@Configuration
@EnableAutoConfiguration
@ComponentScan
public class Application
{
    public static void main(String args[])
    {
        SpringApplication app = new SpringApplication(Application.class);
        app.setShowBanner(false);
        app.run(args);
    }
}

What can be done for the error?

UPDATE: As M. Deinum suggested, when I changed build.gradle file as follows, the application worked!

buildscript {
    repositories {
        maven { url "http://repo.spring.io/libs-snapshot" }
        mavenLocal()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:0.5.0.M6")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'idea'
apply plugin: 'spring-boot'

jar {
    baseName = 'uFlowServer'
    version = '1.0.0'
}

repositories {
    mavenCentral()
    maven { url "http://repo.spring.io/libs-snapshot" }
}

dependencies {
    compile("org.springframework.boot:spring-boot-starter-web:0.5.0.M6")
    compile("org.springframework.boot:spring-boot-starter-jdbc:0.5.0.M6")
    compile("com.fasterxml.jackson.core:jackson-databind")
    runtime("net.sourceforge.jtds:jtds:1.3.1")
    testCompile("junit:junit:4.11")
}

task wrapper(type: Wrapper) {
    gradleVersion = '1.8'
}
zeodtr
  • 10,645
  • 14
  • 43
  • 60

2 Answers2

19

Change your controller to the following

@RestController
public class DetailReportController {

    @Autowired
    private JdbcTemplate jt;

    @RequestMapping(value="/report/detail", method=RequestMethod.GET)
    public List<UFGroup> detailReport() {
        List<UFGroup> results = jt.query(
            "select NID, SCode, SName from UFGroup",
            new RowMapper<UFGroup>(){
                @Override
                public UFGroup mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return new UFGroup(rs.getInt("NID"), rs.getString("SCode"), rs.getString("SName"));
                }
            });
        return results;
    }

    private static class UFGroup
    {
        public int nid;
        public String scode;
        public String sname;

        public UFGroup(int nid, String scode, String sname)
        {
            this.nid = nid;
            this.scode = scode;
            this.sname = sname;
        }
    }
}

In src/main/resources add an application.properties with the following

spring.datasource.driverClassName=net.sourceforge.jtds.jdbc.Driver
spring.datasource.url=jdbc:jtds:sqlserver://111.11.11.11/DataBaseName
spring.datasource.username=sa
spring.datasource.password=password

And simply start your application. No xml needed. Spring boot will create the DataSource and will add a default JdbcTemplate instance.

Tip: Remove the dependency for org.apache.commons.dbcp spring-boot will give you the newer (and IMHO better) tomcat connection pool (which despite the name can be used entirely on its own).

Rich
  • 15,602
  • 15
  • 79
  • 126
M. Deinum
  • 115,695
  • 22
  • 220
  • 224
  • Thank you for your answer. But when I applied it, an error occured. I've edited the question to include the error output. – zeodtr Nov 18 '13 at 10:47
  • I assumed you already had all dependencies add `spring-boot-starter-jdbc` as a dependency. Also remove the `spring-jdbc` dependency as that will be pulled in by `spring-boot-starter-jdbc`. Another suggestion update to **M6** instead of **M5** for Spring Boot. – M. Deinum Nov 18 '13 at 10:51
  • 1
    Thank you very much. You saved my days! When I applied both of your suggestion (include spring-boot-starter-jdbc dependency and update to M6), application worked. Thank you again. For reference, I updated my question to include final (corrected) content of build.gradle file. – zeodtr Nov 18 '13 at 11:26
0

I will remodify your code in better approach.

Firstly no need of using new operator in your code as you are using Spring so you can use the powerful feature of Spring i.e Dependency Injection.

@RestController
public class DetailReportController
{       @Required
            private JdbcTemplate jt;

            //setter for the same

            @RequestMapping(value="/report/detail", method=RequestMethod.GET)
    public List<UFGroup> detailReport()
    {

        List<UFGroup> results = jt.query(
            "select NID, SCode, SName from UFGroup",
            new RowMapper<UFGroup>()
            {
                @Override
                public UFGroup mapRow(ResultSet rs, int rowNum) throws SQLException
                {
                    return new UFGroup(rs.getInt("NID"), rs.getString("SCode"),
                            rs.getString("SName"));
                }
            });

        return results;
    }

    private static class UFGroup
    {
        public int nid;
        public String scode;
        public String sname;

        public UFGroup(int nid, String scode, String sname)
        {
            this.nid = nid;
            this.scode = scode;
            this.sname = sname;
        }
    }
}

application-context.xml

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="maxActive" value="100"/>
        <property name="maxIdle" value="30"/>
        <property name="maxWait" value="16000"/>
        <property name="minIdle" value="0"/>
    </bean>

   <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

    <property name="location">
        <value>database.properties</value>
    </property>
</bean>


    <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate;">
         <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="detailReportController" class="your class">
         <property name="jt" ref="jt"/>
    </bean>

Another suggestion that you can move your code related to database in DAO classes,its very bad practice to have the same in Controller.Else if you want to stick to your approach use Properties class of java.util package

See here

Community
  • 1
  • 1
Shoaib Chikate
  • 8,665
  • 12
  • 47
  • 70
  • Thank you for quick response. But I've read that if Spring Boot is used, xml configuration file is no longer needed, and I can use application.properties file to override some default configuration value. Do I still need a xml file like you suggested? – zeodtr Nov 18 '13 at 10:03
  • And you cannot completely eliminate xml files if you are using Spring and check the updated answer and accept my answer if you feel its appropriate. – Shoaib Chikate Nov 18 '13 at 10:09
  • Thank you again, I'll study your answer. Since I'm a newbie, it may take some time... – zeodtr Nov 18 '13 at 10:18
  • 5
    @ShoaibChikate That’s not true (not even half year ago), you can _completely_ eliminate XML files when using Spring. Even web.xml isn’t necessary when running in Servlet 3.0+ environment. ;) – Jakub Jirutka May 15 '14 at 18:28