Connecting Spring Boot web app to a postgresql server

Question

I'm making a web application using Spring Boot and I have the functionality I want by using a in memory database(H2) but I can't connect it to the postgresql server I set up on my computer. I've been at this for some time and tried a bunch of stuff that didn't work so I set everything back to the way it was just to get it working again.

Here's my UploadController.java, it handles the upload from the server and puts it into my in memory database:

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import project.service.MediaFile;
import project.service.MediaFileRepository;

@Controller
public class UploadController {

    @Autowired
    private MediaFileRepository repository;

    @RequestMapping(value = "/uploadmedia", method = RequestMethod.GET)
    public String uploadForm() {
        return "upload";
    }

    @RequestMapping(value = "/uploadmedia", method = RequestMethod.POST)
    public String uploadSubmit(@RequestParam(value="files[]") MultipartFile[] files,
                               @RequestParam("tags") String tags, @RequestParam("type") String type)
    {
        String[] tagsArray = tags.split("\\s+");
        MultipartFile file;
        String name;
        String tag;
        String path;

        for (int i = 0; i < files.length; i++) {
            file = files[i];
            name = file.getOriginalFilename();
            path = "/Users/johannesthorkell/Developer/spring_prufa/images/" + name;
            System.out.println(name);

            if (!file.isEmpty()) {
                try {
                    byte[] bytes = file.getBytes();
                    BufferedOutputStream stream =
                            new BufferedOutputStream(new FileOutputStream(new File(path)));
                    stream.write(bytes);
                    stream.close();
                    for (int j = 0; j < tagsArray.length; j++) {
                        tag = tagsArray[j].toLowerCase();
                        repository.save(new MediaFile(name, tag, path, type));
                    }
                    System.out.println("Success!");
                } catch (Exception e) {
                    System.out.println("Failure... " + e.getMessage());
                }
            } else {
                System.out.println("No file");
            }
        }
        return "upload";
    }
}

...and here's my MediaFile class, the @Entity object:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class MediaFile {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private long id;
    private String name;
    private String tag;
    private String resource;
    private String type;

    protected MediaFile() {}

    public MediaFile(String name, String tag, String resource, String type) {
        this.name = name;
        this.tag = tag;
        this.resource = resource;
        this.type = type;
    }

    public String getTag() {
        return tag;
    }

    @Override
    public String toString() {
        return String.format(
                "MediaFile[id=%d, name='%s', tag='%s', resource='%s', type='%s']",
                id, name, tag, resource, type);
    }

}

...here's my pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>HBV501G</groupId>
    <artifactId>Spring_Web_MVC</artifactId>
    <version>0.1</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.5.RELEASE</version>
    </parent>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

...and finally, here's the application.properties:

spring.view.prefix=/WEB-INF/jsp/
spring.view.suffix=.jsp
multipart.maxFileSize=-1 

With this setup everything works. I tried putting the following in my application.properties:

spring.datasource.url=jdbc:postgresql://localhost/test
spring.datasource.username=myusername
spring.datasource.password=mypassword
spring.datasource.driver-class-name=org.postgresql.jdbc.Driver

...Along with adding the following dependency to the pom.xml:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>9.4-1200-jdbc41</version>
</dependency>

...And then I tried mixing and matching these things and reading tutorials for a few hours to no avail.

Edit:

I removed one line and added another (at the advice of Stéphane Nicoll) so now my application.properties look like this:

spring.view.prefix=/WEB-INF/jsp/
spring.view.suffix=.jsp
multipart.maxFileSize=-1
debug=true

spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=username
spring.datasource.password=password

...And my pom.xml looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>HBV501G</groupId>
    <artifactId>Spring_Web_MVC</artifactId>
    <version>0.1</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.2.5.RELEASE</version>
    </parent>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4-1200-jdbc41</version>
        </dependency>

        <!--<dependency>-->
            <!--<groupId>com.h2database</groupId>-->
            <!--<artifactId>h2</artifactId>-->
        <!--</dependency>-->

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

With the changes to my application.properties my app now runs! But I get the following error when I submit to my POST form to UploadController.java:

2015-10-06 11:32:14.878  INFO 22287 --- [           main] project.Application                      : Started Application in 11.897 seconds (JVM running for 12.971)
2015-10-06 11:32:40.263  INFO 22287 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet 'dispatcherServlet'
2015-10-06 11:32:40.264  INFO 22287 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization started
2015-10-06 11:32:40.295  INFO 22287 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet 'dispatcherServlet': initialization completed in 31 ms
hopaskipting.pdf
2015-10-06 11:32:49.752 DEBUG 22287 --- [nio-8080-exec-3] org.hibernate.SQL                        : select nextval ('hibernate_sequence')
2015-10-06 11:32:49.760  WARN 22287 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42P01
2015-10-06 11:32:49.760 ERROR 22287 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "hibernate_sequence" does not exist
  Position: 17
Failure... could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

I changed the GenerationType to IDENTITY and get the following error:

2015-10-06 12:56:32.496 DEBUG 22746 --- [nio-8080-exec-7] org.hibernate.SQL                        : insert into media_file (name, resource, tag, type) values (?, ?, ?, ?)
2015-10-06 12:56:32.505 DEBUG 22746 --- [nio-8080-exec-7] org.hibernate.SQL                        : insert into media_file (name, resource, tag, type) values (?, ?, ?, ?)
Failure... A different object with the same identifier value was already associated with the session : [project.service.MediaFile#0]; nested exception is javax.persistence.EntityExistsException: A different object with the same identifier value was already associated with the session : [project.service.MediaFile#0]

Answer 1

The key part of the error is:

2015-10-06 11:32:49.760 ERROR 22287 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "hibernate_sequence" does not exist

Hibernate's looking for a table named hibernate_sequence to support @GeneratedValue on MediaFile. You've configured it with AUTO at the moment. The actual behaviour then varies depending on the database you're using.

I think you have a couple of options:

1. Create a sequence in Postgres (CREATE SEQUENCE), named hibernate_sequence
2. Change to using a different generation type, e.g. GenerationType.IDENTITY

Answer 2

If I were you, I'd create my own dataSource bean, like:

@Configuration
public class MyConfig{

    @Autowired
    Environment env;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("myapp.dataSource.driver"));
        dataSource.setUrl(env.getProperty("myapp.dataSource.url"));
        dataSource.setUsername(env.getProperty("myapp.dataSource.username"));
        dataSource.setPassword(env.getProperty("myapp.dataSource.password"));
        return dataSource;
    }
}

application.properties:

logging.level. = INFO

myapp.dataSource.driver = 
myapp.dataSource.url = 
myapp.dataSource.username = 
myapp.dataSource.password = 

IF you don't want to try this, you could try changing your driver org.postgresql.jdbc.Driver to org.postgresql.Driver.

It's hard to help without any logs.