Spring MVC, How to read .sql file from src/main/resources directory when running?

Question

Help me please in this problem.I have a spring MVC project with Hibernate, and a db.sql file in "src/main/resources/" directory. How can I execute the script in db.sql file when running the application? This is my db.sql file script.

    CREATE DATABASE IF NOT EXISTS `accounts`;
USE `accounts`;
--
-- Table structure for table `role`
--

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id`   INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45)      DEFAULT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 2
  DEFAULT CHARSET = utf8;

--
-- Dumping data for table `role`
--

LOCK TABLES `role` WRITE;
INSERT INTO `role` VALUES (1, 'ROLE_USER');
UNLOCK TABLES;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id`             INT(11) NOT NULL AUTO_INCREMENT,
  `username`       VARCHAR(255)     DEFAULT NULL,
  `password`       VARCHAR(255)     DEFAULT NULL,
  `firstName`      VARCHAR(255)     DEFAULT NULL,
  `lastName`       VARCHAR(255)     DEFAULT NULL,
  `email`          VARCHAR(255)     DEFAULT NULL,
  `dateRegistered` DATE             DEFAULT NULL,
  `skypeID`        VARCHAR(255)     DEFAULT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 4
  DEFAULT CHARSET = utf8;

--
-- Table structure for table `user_role`
--

DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
  `user_id` INT(11) NOT NULL,
  `role_id` INT(11) NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  KEY `fk_user_role_roleid_idx` (`role_id`),
  CONSTRAINT `fk_user_role_roleid` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_role_userid` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `catalog`;
CREATE TABLE catalog (
  `id`         INT(11)      NOT NULL AUTO_INCREMENT,
  `user_id`    INT(11)      NOT NULL,
  `link`       VARCHAR(100) NOT NULL,
  `comment`    VARCHAR(100) NOT NULL,
  `type`       VARCHAR(100) NOT NULL,
  `insertDate` DATE                  DEFAULT NULL,
  `content`    LONGBLOB     NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_catalog` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

I read some posts about this and I found useful results, for example this one

Resource resource = applicationContext.getResource("classpath:db.sql");
InputStream is = resource.getInputStream();

But I don't know how to execute this two lines of code at startup of my program. where to put it.

Check this question, it is related to your needs: https://stackoverflow.com/questions/32838100/spring-hibernate-mysql-initialization — Dez, Oct 01 '16 at 19:59

score 1 · Accepted Answer · answered Oct 03 '16 at 18:16

The problem is solved by adding this function in "HibernateConfiguration.java".

 @Bean
public DataSourceInitializer dataSourceInitializer() {
    ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
    resourceDatabasePopulator.addScript(new ClassPathResource("/data.sql"));

    DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
    dataSourceInitializer.setDataSource(dataSource());
    dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
    return dataSourceInitializer;
}

Spring MVC, How to read .sql file from src/main/resources directory when running?

1 Answers1