Checking database before the server starts up

Hi,
In my spring MVC application i have configured database like :
 <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="${snippet.jdbc.url}"/>
        <property name="username" value="${snippet.jdbc.username}"/>
        <property name="password" value="${snippet.jdbc.password}"/>
    </bean>

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

Open in new window



Following is the way i am accessing my database and quering it.
package org.directi.code.dao;

import org.directi.code.model.Snippet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

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

@Repository
public class SnippetDaoImpl implements SnippetDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final Logger logger = LoggerFactory.getLogger(SnippetDaoImpl.class);

    public void setJdbcTemplate(JdbcTemplate template) {
        this.jdbcTemplate = template;
    }

    @Override
    public String insertData(Snippet snippet) {
        UUID uuid = UUID.randomUUID();
        String randomUUIDString = uuid.toString();
        snippet.setId(randomUUIDString);
        jdbcTemplate.update("INSERT INTO snippet (id, title, mode, text) VALUES (?, ?, ?, ?)", snippet.getId(), snippet.getTitle(), snippet.getMode(), snippet.getText());
        return randomUUIDString;
    }

    @Override
    public void updateData(Snippet snippet) {
        jdbcTemplate.update("UPDATE snippet set text = ? where id = ?", snippet.getText(), snippet.getId());
    }

    @Override
    public Snippet getSnippetById(String id) {
        String query = "select id,title,mode,text from snippet where id=?";
        Snippet snippet;
        snippet = jdbcTemplate.queryForObject(query, new Object[]{id}, new SnippetMaper());
        return snippet;
    }

    private static final class SnippetMaper implements RowMapper<Snippet> {
        public Snippet mapRow(ResultSet rs, int rowNum) throws SQLException {
            Snippet snippet = new Snippet();
            snippet.setText(rs.getString("text"));
            snippet.setId(rs.getString("id"));
            snippet.setMode(rs.getString("mode"));
            snippet.setTitle(rs.getString("title"));
            return snippet;
        }
    }
}

Open in new window


I want to check if the database exists  before the application starts and if it doesnot exists stop the application from running. Also if i can check for the existence of table it will be better.

Thanks
Rohit BajajAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zzynxSr. Software engineerCommented:
Also if i can check for the existence of table it will be better.
Depends on what database you are working with.
But in most cases there exists a query to check for the existance of a table.

Eg. in Oracle:
select count(*) from all_objects where object_type in ('TABLE','VIEW') and object_name = 'YOUR_TABLE';

Open in new window

gurpsbassiCommented:
I've not tested it but doesn't the spring context fail to start if the database is not available?
i.e. DriverManagerDataSource bean should fail to initialise.

What is the motivation behind such a requirement?

What behaviour do you expect if the database goes down whilst the application is running happily?
mccarlIT Business Systems Analyst / Software DeveloperCommented:
I've not tested it but doesn't the spring context fail to start if the database is not available?
i.e. DriverManagerDataSource bean should fail to initialise.

No, there is no database validation on bean initialisation, so it will start up fine. Only the JDBC driver (ie. driverClassName) is validated at that time.



Otherwise, I agree with gurpsbassi, why not have the application still start up. Then if the database is not there (or disappears at some later time) you can return a friendly, helpful message to the user about what is happening, rather than the service just not being available at all. Also, then if the database does come up at a later point in time, the app should just start working.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Rohit BajajAuthor Commented:
yes i agree but probably it will be helpful to know on server startup only if there is some issue in database so that we can know beforehand. although i have taken care of scenario if database is not running then display a message to the user
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Well then, there would be a number of ways, but you could put some DB validation in the initialisation of your DAO object. To do this, implement Spring's InitializingBean interface on your DAO class, and then in the "afterPropertiesSet()" method that you now need to implement, execute a query that will fail if the database/table is not present, such as...

public void afterPropertiesSet() throws Exception {
    jdbcTemplate.queryForObject("select count(*) from MY_REQUIRED_TABLE", Integer.class);
}

Open in new window


The code doesn't do anything with the result, but it will still throw exceptions if it can't connection or if the SQL fails because that table is not present.

I can't be sure about your case if the application will actually stop when an exception is thrown from inside that method, but if not then you could change the above to "catch" the exception and then print a helpful message to inform that there are DB issues.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rohit BajajAuthor Commented:
Hi,
That does not kill the server. However what i did is like :
@Override
    public void afterPropertiesSet() throws Exception {
        try {
            jdbcTemplate.queryForObject("select count(*) from snippet", Integer.class);
        }
        catch (Exception e){
            logger.error("Error querying Database : ",e.getMessage());
            AppStart.server.stop();
        }

Open in new window


And in my Main AppStart class i have made the following :
    public static Server server;
So that i can access it inside snippetDAoImpl and stop the server if Database is not present.

Please provide you comments on this approach.. Or suggest if there is better approach.

I think making server object static and public should not be a problem.. But ideally it should be private so that other code should not be able to mess around with it .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.