· 7 years ago · Dec 23, 2018, 12:10 AM
1I'm new to developing web applications in general, and this is my first time trying to create one using Spring and Hibernate. I have gone through couple of tutorials and now I am starting to work on my own web application.
2I'm trying to setup Spring and Hibernate web application using pure java (no XML). I have set up Azure MySQL database and I want to connect hibernate to it.
3I have created AppConfig file in which I configure Hibernate and Spring, also I have db.properties file with information for connecting. This is a Maven project and I have added dependency for mysql-connector-java. I have also created a file Main.java in which I try to insert data into table to test if a connection is working. When I try to start the app I get following errors
4
5
6
7<b>Error stack trace</b>
8<pre><code>
9Exception in thread "main" org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'adminDAOImpl': Unsatisfied dependency expressed through field 'sessionFactory'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'getSessionFactory' defined in hr.fer.opp.infiniteloop.config.AppConfig: Invocation of init method failed; nested exception is org.hibernate.exception.GenericJDBCException: Unable to open JDBC Connection for DDL execution
10 at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:587)
11 at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:91)
12 at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessPropertyValues(AutowiredAnnotationBeanPostProcessor.java:373)
13 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1344)
14 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:582)
15 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502)
16 at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312)
17 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
18 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)
19 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
20 at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:760)
21 at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868)
22 at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
23 at org.springframework.context.annotation.AnnotationConfigApplicationContext.<init>(AnnotationConfigApplicationContext.java:88)
24 at hr.fer.opp.infiniteloop.testing.Main.main(Main.java:14)
25Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'getSessionFactory' defined in hr.fer.opp.infiniteloop.config.AppConfig: Invocation of init method failed; nested exception is org.hibernate.exception.GenericJDBCException: Unable to open JDBC Connection for DDL execution
26 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1710)
27 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583)
28 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502)
29 at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312)
30 at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
31 at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)
32 at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
33 at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251)
34 at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138)
35 at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1065)
36 at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:584)
37 ... 14 more
38Caused by: org.hibernate.exception.GenericJDBCException: Unable to open JDBC Connection for DDL execution
39 at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
40 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
41 at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
42 at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:69)
43 at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60)
44 at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcDatabaseMetaData(ImprovedExtractionContextImpl.java:67)
45 at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTables(InformationExtractorJdbcDatabaseMetaDataImpl.java:329)
46 at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.getTablesInformation(DatabaseInformationImpl.java:120)
47 at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:65)
48 at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
49 at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
50 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
51 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
52 at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:309)
53 at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462)
54 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
55 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
56 at org.springframework.orm.hibernate5.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:535)
57 at org.springframework.orm.hibernate5.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:519)
58 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769)
59 at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706)
60 ... 24 more
61Caused by: java.sql.SQLException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL '"jdbc:mysql://infiniteloop.mysql.database.azure.com:3306/zalagaonica?useSSL=true&requireSSL=false";'
62 at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:2167)
63 at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2037)
64 at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1543)
65 at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
66 at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180)
67 at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43)
68 ... 41 more
69Caused by: java.sql.SQLException: No suitable driver
70 at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:2158)
71 ... 46 more
72</code></pre>
73
74
75
76<b>AppConfig</b>
77
78<pre><code>
79@Configuration
80@EnableWebMvc
81@EnableTransactionManagement
82@ComponentScan(basePackages="hr.fer.opp.infiniteloop")
83@PropertySource("classpath:db.properties")
84public class AppConfig {
85
86 // set up variable to hold the properties
87 @Autowired
88 private Environment env;
89
90 // set up a logger for diagnostics
91 private Logger logger = Logger.getLogger(getClass().getName());
92
93
94 // define a bean for ViewResolver
95 @Bean
96 public ViewResolver viewResolver() {
97
98 InternalResourceViewResolver viewResolver = new
99 InternalResourceViewResolver();
100
101 viewResolver.setPrefix("/WEB-INF/view/");
102 viewResolver.setSuffix(".jsp");
103
104 return viewResolver;
105 }
106
107 // beans for hibernate
108 @Bean
109 public DataSource getDataSource() {
110 BasicDataSource dataSource = new BasicDataSource();
111 dataSource.setDriverClassName(env.getProperty("db.driver"));
112 dataSource.setUrl(env.getProperty("db.url"));
113 dataSource.setUsername(env.getProperty("db.user"));
114 dataSource.setPassword(env.getProperty("db.password"));
115 return dataSource;
116 }
117
118 @Bean
119 public LocalSessionFactoryBean getSessionFactory() {
120 LocalSessionFactoryBean factoryBean = new LocalSessionFactoryBean();
121
122 factoryBean.setDataSource(getDataSource());
123 factoryBean.setPackagesToScan("hr.fer.opp.infiniteloop.entity");
124
125 Properties props = new Properties();
126 props.setProperty("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
127 props.setProperty("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
128 props.setProperty("hibernate.dialect", env.getProperty("hibernate.dialect"));
129
130 factoryBean.setHibernateProperties(props);
131 return factoryBean;
132 }
133
134 @Bean
135 public HibernateTransactionManager getTransactionManager() {
136 HibernateTransactionManager transactionManager = new HibernateTransactionManager();
137 transactionManager.setSessionFactory(getSessionFactory().getObject());
138 return transactionManager;
139 }
140</code></pre>
141
142
143<b>db.properties</b>
144<pre><code>
145 #
146 # JDBC connection properties
147 #
148 db.driver=com.mysql.jdbc.Driver
149 db.url="jdbc:mysql://infiniteloop.mysql.database.azure.com:3306
150 /zalagaonica?useSSL=true&requireSSL=false";
151 db.user=username
152 db.password=password
153
154 #
155 # Hibernate properties
156 #
157 hibernate.show_sql=true
158 hibernate.hbm2ddl.auto=update
159 hibernate.dialect=org.hibernate.dialect.MySQLDialect
160</code></pre>
161
162<b>Main.java</b> main method
163<pre><code>
164 public static void main(String[] args) {
165 AnnotationConfigApplicationContext context = new
166 AnnotationConfigApplicationContext(AppConfig.class);
167
168 AdminService adminService =
169 context.getBean(AdminService.class);
170
171 // Add Admins
172 adminService.add(new Admin("Admin1"));
173 adminService.add(new Admin("Admin2"));
174
175 // Get Admins
176 List<Admin> admins = adminService.getAdmins();
177
178 for(Admin admin : admins)
179 System.out.println(admin.getUsername());
180
181 context.close();
182
183 }
184</code></pre>
185
186Also I used this code in the same project to verify that I can connect to the database using this JDBC driver and code worked.
187<pre><code>
188 public static void main (String[] args) throws Exception
189 {
190 // Initialize connection variables.
191 String host = "infiniteloop.mysql.database.azure.com";
192 String database = "zalagaonica";
193 String user = "username";
194 String password = "password";
195
196 // check that the driver is installed
197 try
198 {
199 Class.forName("com.mysql.jdbc.Driver");
200 }
201 catch (ClassNotFoundException e)
202 {
203 throw new ClassNotFoundException("MySQL JDBC driver NOT detected in library path.", e);
204 }
205
206 System.out.println("MySQL JDBC driver detected in library path.");
207
208 Connection connection = null;
209
210 // Initialize connection object
211 try
212 {
213 String url = String.format("jdbc:mysql://%s/%s", host, database);
214
215 // Set connection properties.
216 Properties properties = new Properties();
217 properties.setProperty("user", user);
218 properties.setProperty("password", password);
219 properties.setProperty("useSSL", "true");
220 properties.setProperty("verifyServerCertificate", "true");
221 properties.setProperty("requireSSL", "false");
222
223 // get connection
224 connection = DriverManager.getConnection(url, properties);
225 }
226 catch (SQLException e)
227 {
228 throw new SQLException("Failed to create connection to database.", e);
229 }
230 if (connection != null)
231 {
232 System.out.println("Successfully created connection to database.");
233
234 // Perform some SQL queries over the connection.
235 try
236 {
237 // Drop previous table of same name if one exists.
238 Statement statement = connection.createStatement();
239 statement.execute("DROP TABLE IF EXISTS inventory;");
240 System.out.println("Finished dropping table (if existed).");
241
242 // Create table.
243 statement.execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);");
244 System.out.println("Created table.");
245
246 // Insert some data into table.
247 int nRowsInserted = 0;
248 PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO inventory (name, quantity) VALUES (?, ?);");
249 preparedStatement.setString(1, "banana");
250 preparedStatement.setInt(2, 150);
251 nRowsInserted += preparedStatement.executeUpdate();
252
253 preparedStatement.setString(1, "orange");
254 preparedStatement.setInt(2, 154);
255 nRowsInserted += preparedStatement.executeUpdate();
256
257 preparedStatement.setString(1, "apple");
258 preparedStatement.setInt(2, 100);
259 nRowsInserted += preparedStatement.executeUpdate();
260 System.out.println(String.format("Inserted %d row(s) of data.", nRowsInserted));
261
262 // NOTE No need to commit all changes to database, as auto-commit is enabled by default.
263
264 }
265 catch (SQLException e)
266 {
267 throw new SQLException("Encountered an error when executing given sql statement.", e);
268 }
269 }
270 else {
271 System.out.println("Failed to create connection to database.");
272 }
273 System.out.println("Execution finished.");
274</code></pre>
275
276I have looked every stack overflow post about this topic and non did provide me with the answer.