CODEWITHSUNDEEP
springjerseyspring-jdbc
tonga
tonga

Reputation: 11961

How to execute SQL script only once at startup in Spring?

I have a web application based on Spring JDBC and Jersey RESTful web service. I'm using the following Spring JDBC template class to initiate the dataSource and execute an SQL script (update_condition_table.sql):

public class CustomerJDBCTemplate implements CustomerDAO {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplateObject;

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplateObject = new JdbcTemplate(dataSource);
        Resource rc = new ClassPathResource("update_condition_table.sql");
        JdbcTestUtils.executeSqlScript(jdbcTemplateObject, rc, false);
    }

    // ......other methods
}

The bean configuration file is beans.xml:

<!-- Initialization for data source -->
<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/customer" />
    <property name="username" value="root" />
    <property name="password" value="mypassword" />
</bean>

<!-- Definition for customerJDBCTemplate bean -->
<bean id="customerJDBCTemplate" class="com.example.db.CustomerJDBCTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

The Jersey controller class contains the instantiation of class CustomerJDBCTemplate and serves as the REST web service:

@Path("/customer")
public class CustomerService {

    ApplicationContext context = new ClassPathXmlApplicationContext("beans.xml");
    CustomerJDBCTemplate dbController = (CustomerJDBCTemplate) context.getBean("customerJDBCTemplate");

    // ... some GET/POST methods
}

When I launched my web app by entering the index URL in the browser, the SQL script gets executed by the customerJDBCTemplate bean. However, when I clicked to navigate to other pages, it crashed and reported that the SQL script cannot be executed again. So obviously the SQL script was executed again after initialization of dataSource and initial launch of the index web page. How to avoid this by just running the SQL script only once upon initial startup of the web app?

Looks like I need to move the bean instantiate code out of CustomerService class, but where should I put that code?

Upvotes: 1

Views: 3265

Answers (2)

tonga
tonga

Reputation: 11961

I figured it out that I should set the bean application context to be static within CustomerService class and do it in the static initialization block as follows:

@Path("/customer")
public class CustomerService {

    private static ApplicationContext context;
    private static CustomerJDBCTemplate dbController;

    static {
        context = new ClassPathXmlApplicationContext("beans.xml");
        dbController = (CustomerJDBCTemplate) context.getBean("customerJDBCTemplate");
    }

    //... other methods
}

I guess the reason is Jersey creates a different instance of CustomerService for each HTTP session (correct me if I'm wrong). So if I set the bean context as instance variable, it will do the initialization for every HTTP request.

Upvotes: 1

ikumen
ikumen

Reputation: 11643

Have your CustomerJDBCTemplate implement InitializingBean. afterPropertiesSet will get called once, right after all properties have been set by Spring's BeanFactory.

For example:

public class CustomerJDBCTemplate implements CustomerDAO, InitializingBean {
  ... 
  // ......other methods

  public void afterPropertiesSet() throws Exception {
    //do your initializing, or call your initializing methods
  }
}

Upvotes: 0

Related Questions