validating if a table is present in database and has the required schema in java spring mvc

Hi,
I have the following in my application-context.xml :
 <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="robinsuri"/>
        <property name="password" value=""/>
    </bean>

Open in new window


Now i am making update and insert queries to a table called snippets in the test db.
What i want is to have a mechanism to make sure that the table snippets is present and has the same expected schema.
So if the database itself is not present then i create a database and then create the table with a particular schema.
Otherwise if the database is present and table is not there then i create a table with the schema.
And if the table is also present and doesnot match the schema i return some error .

The schema for which i have to implement this is :
 
id	      char(36)	        NO	PRI	NULL	
text	      text	                NO		NULL	
title	      varchar(255)	NO		NULL	
mode    varchar(255)	NO		NULL	

Open in new window


I am also attaching a csv file giving the schema.

Thanks
query_result.csv
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.

gurpsbassiCommented:
strange requirement.
depending on what database you are using there is usually a schema for metadata about tables. You could query that metadata to see if the table exists.

You're requirement seems similar to something I would do if I was using a noSQL database.
Rohit BajajAuthor Commented:
HI,
Whats the standard way then .. i am using mysql database. So if someone is using my application he needs to know that there should be a table in the database with a particular schema.
whats the standard practice ? Do i provide the table schema in a readme ?

please help me understand why the requirement would be fine if i was using noSQL database ?
Rohit BajajAuthor Commented:
What i am wondering is that the following lines in xml will only verify if the database is present when the server starts up :

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test"/>
        <property name="username" value="robinsuri"/>
        <property name="password" value=""/>
    </bean>

Open in new window


But when the user hits some url : localhost:9999/something
The code will be executed which will perform :
 public String insertData(Snippet snippet){
        UUID uuid = UUID.randomUUID();
        String randomUUIDString = uuid.toString();
        snippet.setId(randomUUIDString);
        jdbcTemplate.update("INSERT INTO snippets (id, title, mode, text) VALUES (?, ?, ?, ?)", snippet.getId(), snippet.getTitle(), snippet.getMode(), snippet.getText());
        return randomUUIDString;

    }

Open in new window


Now here  problems i notice is :
1) the table name is not configurable, code assumes there is a table with name snippets
2) Also if there is something wrong with the table schema then what my code is assuming, things will error out.
Ideally if there if something wrong it should error out before the server starts itself.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

gurpsbassiCommented:
I am still not clear why the user of your application needs to know about the database tables?

Define what you mean by "user" i.e. end user? another developer?

I am using experts exchange, I do not care about the underlying databases it uses to store the questions and solutions in.
Rohit BajajAuthor Commented:
HI,
No i didnt mean end user. But another developer. If say another developer wants to use or test and deploy it.
gurpsbassiCommented:
Normally if another developer checks out a project from a repository, I would tell them either verbally or by means of a README that they need to have a database in order to run it.

I would also have a build script to build the database and populate it with any starter data if required. I would never put code in my actual application to create the table if it doesn't exist.

These days you can do a lot with containers such as Docker.  You could create a container for a prebuilt database and ask the developer to simply download the container from a registry.

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
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.