This guide walks you through the process of creating a Spring application connected with a MySQL Database, as opposed to an in-memory, embedded database, which all of the other guides and many sample apps use. It uses Spring Data JPA to access the database, but this is only one of many possible choices (e.g. you could use plain Spring JDBC).
What you’ll build
You’ll create a MySQL database, build a Spring application and connect it with the newly created database.
|MySQL is licensed with the GPL, so any program binary that you distribute using it must use the GPL too. Refer to the GNU General Public Licence.|
What you’ll need
Create the database
Go to the terminal (command Prompt
cmd in Microsoft Windows). Open MySQL client with a user that can create new users.
For example: On a Linux, use the command
$ sudo mysql --password
|This connects to MySQL as a root, this is not the recommended way for a production server.|
Create a new database
mysql> create database db_example; -- Create the new database mysql> create user 'springuser'@'localhost' identified by 'ThePassword'; -- Creates the user mysql> grant all on db_example.* to 'springuser'@'localhost'; -- Gives all the privileges to the new user on the newly created database
Spring Boot gives you defaults on all things, the default in database is
H2, so when you want to change this and use any other database you must define the connection attributes in the
In the sources folder, you create a resource file
spring.jpa.hibernate.ddl-auto can be
create-drop, refer to the Hibernate documentation for details.
noneThis is the default for
MySQL, no change to the database structure.
updateHibernate changes the database according to the given Entity structures.
createCreates the database every time, but don’t drop it when close.
create-dropCreates the database then drops it when the
We here begin with
create because we don’t have the database structure yet. After the first run, we could switch it to
none according to program requirements. Use
update when you want to make some change to the database structure.
The default for
H2 and other embedded databases is
create-drop, but for others like
It is good security practice that after your database is in production state, you make this
none and revoke all privileges from the MySQL user connected to the Spring application, then give him only SELECT, UPDATE, INSERT, DELETE.
This is coming in details in the end of this guide.
This is the entity class which Hibernate will automatically translate into a table.
Create the repository
This is the repository interface, this will be automatically implemented by Spring in a bean with the same name with changing case
The bean name will be
Create a new controller for your Spring application
The above example does not explicitly specify
Make the application executable
Although it is possible to package this service as a traditional WAR file for deployment to an external application server, the simpler approach demonstrated below creates a standalone application. You package everything in a single, executable JAR file, driven by a good old Java
main() method. Along the way, you use Spring’s support for embedding the Tomcat servlet container as the HTTP runtime, instead of deploying to an external instance.
Logging output is displayed. The service should be up and running within a few seconds.
Test the application
Now that the application is running, you can test it.
curl for example.
Now you have 2 REST Web Services you can test
localhost:8080/demo/all This gets all data
localhost:8080/demo/add This adds one user to the data
$ curl 'localhost:8080/demo/add?name=Firstfirstname.lastname@example.org'
The reply should be
$ curl 'localhost:8080/demo/all'
The reply should be
Make some security changes
Now when you are on production environment, you may be exposed to SQL injection attacks. A hacker may inject
DROP TABLE or any other destructive SQL commands. So as a security practice, make those changes to your database before you expose the application to users.
mysql> revoke all on db_example.* from 'springuser'@'localhost';
This revokes ALL the priviliges from the user associated with the Spring application. Now the Spring application cannot do anything in the database. We don’t want that, so
mysql> grant select, insert, delete, update on db_example.* to 'springuser'@'localhost';
This gives your Spring application only the privileges necessary to make changes to only the data of the database and not the structure (schema).
Now make this change to your
This is instead of
create which was on the first run for Hibernate to create the tables from your entities.
When you want to make changes on the database, regrant the permissions, change the
update, then re-run your applications, then repeat. Or, better, use a dedicated migration tool such as Flyway or Liquibase.
Congratulations! You’ve just developed a Spring application which is bound to a MySQL database, Ready for production!
The following guides may also be helpful: