How to set driver for postgres?

Question

I have spring boot application, where i want to test batch insert to different data bases. I have pom file:

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.8</version>
    </dependency>

And code in controller:

@RequestMapping(value = "setdb/{url}/{schema}/{login}/{password}")
public String setDB(@PathVariable(name = "url") String url,
                    @PathVariable(name = "schema") String schema,
                    @PathVariable(name = "login") String login,
                    @PathVariable(name = "password") String password) throws SQLException, ClassNotFoundException {
    url = "jdbc:postgresql://"+url+"?currentSchema="+schema;
    connection = DriverManager.getConnection(url, login, password);
    return String.format("url: %s\nlogin: %s\npassword: %s", url, login, password);
}

When i tried to do this i have error

{
    "timestamp": "2020-02-03T09:06:46.800+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "No suitable driver found for jdbc:postgresql://127.0.0.1:5432?currentSchema=app",
    "path": "/setdb/127.0.0.1:5432/app/postgres/qwerty"
}

How can i fix it? Set the driver programmatically?

Try loading the postgress Driver first? Class.forName("org.postgresql.Driver"); — Worthless, Feb 03 '20 at 09:14
Most likely the driver jar isn't being included for some reason. Generate a war and check if the jar is included in the libraries. — Kayaman, Feb 03 '20 at 09:17
Does this answer your question? [The infamous java.sql.SQLException: No suitable driver found](https://stackoverflow.com/questions/1911253/the-infamous-java-sql-sqlexception-no-suitable-driver-found) — lugiorgi, Feb 03 '20 at 09:20
Is `app` a schema or a database? And if it's really a schema, then why aren't you providing a database name in the URL? — , Feb 03 '20 at 09:20
@a_horse_with_no_name it's so nice of MySQL to make it "easier" by treating databases and schemas as the same thing. — Kayaman, Feb 03 '20 at 09:23
@Kayaman Depending on the context, explicit driver loading may still be necessary (eg if the driver is in a WAR, and not on the initial classpath). — Mark Rotteveel, Feb 03 '20 at 09:41
You shouldn't use `DriverManager.getConnection` in Spring Boot, but instead use a datasource. See [How-to Guide: Data Access](https://docs.spring.io/spring-boot/docs/2.2.4.RELEASE/reference/html/howto.html#howto-data-access) — Mark Rotteveel, Feb 03 '20 at 09:44

score 1 · Accepted Answer · answered Feb 03 '20 at 11:40

1

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres

in your URL you need to add host and db name

url = "jdbc:postgresql://"+url+"?currentSchema="+schema;

url = "jdbc:postgresql://"+url+"/"+schema use this url

answered Feb 03 '20 at 11:40

Krupan Kasam

26
4

score 0 · Answer 2 · answered Feb 03 '20 at 09:19

If you are sure that the driver is in your classpath the error can also stem from a wrong connection URL. Check your URL Syntax, it should be either of

jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database

So if app is the db you should do:

jdbc:postgresql://127.0.0.1:5432/app

If app is a schema in a DB you still have to specify your DB in the URL

jdbc:postgresql://127.0.0.1:5432/DBNAME?currentSchema=app

Raj Verma · Answer 3 · 2020-02-03T10:04:39.037

-2

well what I did to test this is created an empty mvn project

spring init --name=postgres-demo --dependencies=web,jpa,postgresql 
postgres-test

and to the application properties under resources added this & it works for me:

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)

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

# dialect makes Hibernate generate better SQL for the database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update

edited Feb 03 '20 at 10:04

answered Feb 03 '20 at 09:36

Raj Verma

1,050
1
7
19

The OP already has the JDBC driver as a dependency, and given the code shown is not using a datasource. – Mark Rotteveel Feb 03 '20 at 09:45
so basically rather than doing it in the controller added it in the app properties & that does it. – Raj Verma Feb 03 '20 at 10:07
You are missing my point, the OP is currently not `DriverManager.getConnection`, so all this config you're showing will not solve his problem, unless you also explain what he needs to change in his code to make use of that config. – Mark Rotteveel Feb 03 '20 at 10:11
@MarkRotteveel got it – Raj Verma Feb 03 '20 at 10:27

How to set driver for postgres?

3 Answers3