I'm trying to write an application to connect to a MS SQL Server database. The database already exists, the tables are already configured. All I want to do is to connect my Java Spring app to the already-existing server. However, when I try to do so, I get this error:
Caused by: java.sql.SQLException: There is already an object named 'Customer' in the database.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809) ~[jtds-1.3.1.jar:1.3.1]
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282) ~[jtds-1.3.1.jar:1.3.1]
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.1.10.Final.jar:5.1.10.Final]
... 87 common frames omitted
It seems like JTDS is trying to recreate my Customer table, and indeed if I delete the table and then run the application, it does indeed create a new table in my database called "Customer". However, I can't drop my table on every application start (for obvious reasons) so I need to configure my application to connect to a table which already exists. How do I do this?
Database appconfig:
@Configuration
@EnableJpaRepositories
@EntityScan("com.example.dbentity")
public class AppConfig {
@Value("${db.driver.class.name}")
private String dbDriverClassName;
@Value("${db.prefix}")
private String dbPrefix;
@Value("${db.url}")
private String dbUrl;
@Value("${db.name}")
private String dbName;
@Value("${db.username}")
private String dbUsername;
@Value("${db.password}")
private String dbPassword;
@Bean
public DataSource DataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(dbDriverClassName);
String connectionUrl = dbPrefix + dbUrl + "/" +dbName;
dataSource.setUrl(connectionUrl);
dataSource.setUsername(dbUsername);
dataSource.setPassword(dbPassword);
return dataSource;
}
@Bean
public JdbcTemplate JdbcTemplate() {
JdbcTemplate template = new JdbcTemplate();
template.setDataSource(DataSource());
return template;
}
@Bean
public EntityManagerFactory entityManagerFactory() {
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
vendorAdapter.setGenerateDdl(true);
LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
factory.setJpaVendorAdapter(vendorAdapter);
factory.setPackagesToScan("com.example.dbentity");
factory.setDataSource(DataSource());
factory.afterPropertiesSet();
return factory.getObject();
}
@Bean
public PlatformTransactionManager transactionManager() {
JpaTransactionManager txManager = new JpaTransactionManager();
txManager.setEntityManagerFactory(entityManagerFactory());
return txManager;
}
}
Configuration YAML:
spring:
application:
name: sample-application
jpa:
hibernate:
ddl-auto: validate
server:
port: 8080
max-http-header-size: 65536
db:
driver:
class:
name: net.sourceforge.jtds.jdbc.Driver
prefix: jdbc:jtds:sqlserver://
url: example-server.net:1433
name: example-db
username: user
password: password
Hibernate output:
2018-07-13 10:57:56.831 INFO 15336 --- [ main] o.s.j.d.DriverManagerDataSource : Loaded JDBC driver: net.sourceforge.jtds.jdbc.Driver
2018-07-13 10:57:56.882 INFO 15336 --- [ main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2018-07-13 10:57:56.901 INFO 15336 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [
name: default
...]
2018-07-13 10:57:57.002 INFO 15336 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {5.1.10.Final}
2018-07-13 10:57:57.002 INFO 15336 --- [ main] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found
2018-07-13 10:57:57.006 INFO 15336 --- [ main] org.hibernate.cfg.Environment : HHH000021: Bytecode provider name : javassist
2018-07-13 10:57:57.060 INFO 15336 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-07-13 10:57:57.531 INFO 15336 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.SQLServer2012Dialect
2018-07-13 10:57:57.672 INFO 15336 --- [ main] o.h.e.j.e.i.LobCreatorBuilderImpl : HHH000423: Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4
2018-07-13 10:57:58.754 WARN 15336 --- [ main] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement
Thanks.
There is a property for Hibernate called ddl-auto where you can set whether Hibernate will create the schema on start, validate the schema that's already there, or do no validation of the existing schema.
What you want to do is to just use validate (or none).
See here for how to configure this in Spring Boot and here (i.e. the hibernateProperties) for how to do this in Vanilla Spring.