Java Connection to MariaDB / MySQL
MariaDB and MySQL are highly popular open-source databases, used by developers all over the world. In this instruction, we’ll show you how to connect your Java application to these databases, standalone server,s and clustered solutions.
1. Log into BitssCloud and create an environment with the MariaDB (or MySQL) database server (available within the SQL wizard section):
- for the standalone database server
- for Auto-Clustering solution.
We’ve also added a Tomcat node to provide an example of a database connection from application server.
2. Check your email inbox – it should contain a message with administration details for the created MariaDB (or MySQL) server.
In case of a database cluster, the Entry Point for Connecting is referred to as a ProxySQL load balancer.
3. Switch back to the dashboard and click the Open in Browser button for your MariaDB/MySQL node.
If you have clustered solution, press on Open in Browser next to the master database node (marked as M).
Log into the opened admin panel using credentials from the above-mentioned emails.
4. Use an existing database (e.g. test) or Create a new one.
5. Return to dashboard and click the Config button next to the application server (Tomcat, in our case) to access configuration file manager.
6. Navigate to the /opt/tomcat/temp folder, create a new mydb.cfg file.
For standalone database connection, add the following data in the mydb.cfg file:
1 2 3 4 | host=jdbc:mysql://{host}/{db_name} username={user} password={password} driver=com.mysql.jdbc.Driver |
All the required info can be found within the MariaDB/MySQL node email:
- {host} – link to your DB node without protocol part
- {db_name} – name of the database (test in our case)
- {user} and {password} – database admin credentials (for production usage, it’s recommended to create a dedicated account with the appropriate permissions)
For connection to the cluster, ProxySQL load balancer is used as the entry point, and each type of databases has its own connector. So add the following data to the mydb.cfg file:
For MariaDB:
1 2 3 4 | host=jdbc:mariadb://{hostname}/{db_name}?usePipelineAuth=false username={user} password={password} driver=org.mariadb.jdbc.Driver |
- {hostname} – link to your DB cluster load balancer (i.e. ProxySQL layer)
- {db_name} – name of the database. We chose test in the first step
- usePipelineAuth – if activated different queries are executed using pipeline (all queries are sent, only then all results are read), permitting faster connection creation. This value should be set to false, as such implementation doesn’t work with the ProxySQL in front of the cluster
- {user} and {password} – database credentials received in the email
For MySQL:
1 2 3 4 | host=jdbc:mysql://{host}/{db_name} username={user} password={password} driver=com.mysql.jdbc.Driver |
- {hostname} – link to your DB cluster load balancer (i.e. ProxySQL layer)
- {db_name} – name of the database (test in our case)
- {user} and {password} – database admin credentials (for production usage, it’s recommended to create a dedicated account with the appropriate permissions)
In such a way, all connection settings are saved in a single file, which, subsequently, will be read by the application.
7. For deployment and further connection, we are going to use the following sample application:
package connection; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Properties; import java.util.logging.Level; import java.util.logging.Logger; public class DbManager { public String date = new SimpleDateFormat("dd-MM-yyyy-HH-mm").format(new Date()); private final String createTable = "CREATE TABLE `" + date + "` (id INT, data VARCHAR(100));"; private static final int LoginTimeout = 10; public DbManager() { } public Connection createConnection() throws IOException, ClassNotFoundException, SQLException { Properties prop = new Properties(); System.out.println("\n\n=======================\nJDBC Connector Test " + date); System.out.println("User home directory: " + System.getProperty("user.home")); String host; String username; String password; String driver; try { prop.load(new java.io.FileInputStream(System.getProperty("user.home") + "/mydb.cfg")); host = prop.getProperty("host").toString(); username = prop.getProperty("username").toString(); password = prop.getProperty("password").toString(); driver = prop.getProperty("driver").toString(); } catch (IOException e) { System.out.println("Unable to find mydb.cfg in " + System.getProperty("user.home") + "\n Please make sure that configuration file created in this folder."); host = "Unknown HOST"; username = "Unknown USER"; password = "Unknown PASSWORD"; driver = "Unknown DRIVER"; } System.out.println("host: " + host + "\nusername: " + username + "\npassword: " + password + "\ndriver: " + driver); Class.forName(driver); System.out.println("--------------------------"); System.out.println("DRIVER: " + driver); System.out.println("Set Login Timeout: " + LoginTimeout); DriverManager.setLoginTimeout(LoginTimeout); Connection connection = DriverManager.getConnection(host, username, password); System.out.println("CONNECTION: " + connection); return connection; } public String runSqlStatement() { String result = ""; try { Statement statement = createConnection().createStatement(); System.out.println("SQL query: " + createTable); statement.execute(createTable); } catch (IOException | ClassNotFoundException ex) { Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex); System.out.println("Exception occurred: " + ex); result = ex.getMessage(); } catch (SQLException ex) { ex.printStackTrace(); result = ex.getMessage(); } return result; } } |
8. Deploy our example application to your Tomcat server using the following link:
Note:
- Our example application already contains the jdbc-connectors for MariaDB/MySQL database access. However, to connect your own project, you need to manually upload them to the webapps/{app_context}/WEB-INF/lib folder on your application server.
- Don’t forget to restart your application server to apply mydb.cfg changes, by pressing Restart Node button.
9. Once deployment is finished, click Open in Browser in popup window or next to your application server.
10. In the opened browser tab, click on the Create test table in your database button.
11. Now, in order to ensure everything works fine, return to the phpMyAdmin panel and navigate to the test database.
You’ll see that the newly created table appeared with the name {date-time of creation}, that means the DB has been successfully accessed and modified from your Java application. It’s that easy