In this article, you will explore the process of creating a MySQL or MariaDB database using Java. A step-by-step guide walks you through setting up a database connection, creating a database, and executing basic SQL commands to manipulate data in the database. Essential concepts such as connecting to the database server, creating a new database, creating tables, inserting data, and querying data are covered.
To get started, you should have a basic understanding of Java programming and SQL. The article assumes the use of the JDBC (Java Database Connectivity) API, the standard Java API for interacting with databases. The guide also covers the use of the MySQL Connector/J, a JDBC driver that enables Java applications to connect to MySQL and MariaDB databases.
Code snippets and examples help you understand the different stages of database creation. Topics such as connecting to the database server, creating a database, creating tables, inserting data, and retrieving data are covered in the examples.
By completing the guide, you will have a solid understanding of how to create and manipulate databases using Java. This knowledge can be applied to a variety of applications, from simple scripts to complex enterprise applications. With the growing popularity of databases in modern applications, this article is a valuable resource for Java developers seeking to master database creation with MySQL or MariaDB.
Creating a Database in MySQL or MariaDB using Java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class CreateDatabase { public static void main(String[] args) { try { // Step 1: Load the MySQL driver Class.forName("com.mysql.cj.jdbc.Driver"); // Step 2: Establish a connection to the database Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306", "root", "password"); // Step 3: Create a statement Statement statement = connection.createStatement(); // Step 4: Execute the CREATE DATABASE statement statement.executeUpdate("CREATE DATABASE database_name"); System.out.println("Database created successfully!"); } catch (Exception e) { e.printStackTrace(); } } } |
Note: In this example, replace database_name with the name of the database you want to create, and replace "root" and "password" with the username and password for your MySQL or MariaDB database.
Creating a Table in MySQL or MariaDB using Java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class CreateTable { public static void main(String[] args) { try { // Step 1: Load the MySQL driver Class.forName("com.mysql.cj.jdbc.Driver"); // Step 2: Establish a connection to the database Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "root", "password"); // Step 3: Create a statement Statement statement = connection.createStatement(); // Step 4: Execute the CREATE TABLE statement String sql = "CREATE TABLE table_name " + "(column1_name data_type1, " + "column2_name data_type2, " + "column3_name data_type3, " + "PRIMARY KEY (column1_name))"; statement.executeUpdate(sql); System.out.println("Table created successfully!"); } catch (Exception e) { e.printStackTrace(); } } } |
Note: In this example, replace database_name with the name of the database you want to create a table in, replace table_name with the name of the table you want to create, replace column1_name, column2_name, and column3_name with the names of the columns you want to create, replace data_type1, data_type2, and data_type3 with the data types for each column, and replace “root” and “password” with the username and password for your MySQL database.
Also Read:
Inserting Values into a Table in MySQL or MariaDB using Java
Here’s a basic program in Java that inserts values into a table in a database in MySQL. If the table does not exist, it creates the table first:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class InsertValues { public static void main(String[] args) { try { // Step 1: Load the MySQL driver Class.forName("com.mysql.cj.jdbc.Driver"); // Step 2: Establish a connection to the database Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "root", "password"); // Step 3: Create a statement Statement statement = connection.createStatement(); // Step 4: Check if the table exists ResultSet resultSet = connection.getMetaData().getTables(null, null, "table_name", null); if (!resultSet.next()) { // Step 5: Create the table if it does not exist String sql = "CREATE TABLE table_name " + "(column1_name data_type1, " + "column2_name data_type2, " + "column3_name data_type3, " + "PRIMARY KEY (column1_name))"; statement.executeUpdate(sql); } // Step 6: Insert values into the table String sql = "INSERT INTO table_name " + "VALUES (value1, value2, value3)"; statement.executeUpdate(sql); System.out.println("Values inserted successfully!"); } catch (Exception e) { e.printStackTrace(); } } } |
Note: In this example, replace database_name with the name of the database you want to insert values into, replace table_name with the name of the table you want to insert values into, replace column1_name, column2_name, and column3_name with the names of the columns you want to insert values into, replace data_type1, data_type2, and data_type3 with the data types for each column, replace value1, value2, and value3 with the values you want to insert, and replace “root” and “password” with the username and password for your MySQL database.
Retrieving and Displaying Table Data from MySQL or MariaDB using Java
Here’s a basic program in Java that retrieves data from a table in a database in MySQL and displays the data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DisplayData { public static void main(String[] args) { try { // Step 1: Load the MySQL driver Class.forName("com.mysql.cj.jdbc.Driver"); // Step 2: Establish a connection to the database Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "root", "password"); // Step 3: Create a statement Statement statement = connection.createStatement(); // Step 4: Execute a SELECT statement String sql = "SELECT * FROM table_name"; ResultSet resultSet = statement.executeQuery(sql); // Step 5: Loop through the result set and display the data while (resultSet.next()) { System.out.println("column1_name: " + resultSet.getString("column1_name") + ", column2_name: " + resultSet.getString("column2_name") + ", column3_name: " + resultSet.getString("column3_name")); } System.out.println("Data displayed successfully!"); } catch (Exception e) { e.printStackTrace(); } } } |
Note: In this example, replace database_name with the name of the database you want to retrieve data from, replace table_name with the name of the table you want to retrieve data from, replace column1_name, column2_name, and column3_name with the names of the columns you want to retrieve data from, and replace “root” and “password” with the username and password for your MySQL database.
Exporting MySQL or MariaDB Table Data to Excel file using Java
Here’s a basic program in Java that exports a table in a MySQL database to an Excel file:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; public class ExportData { public static void main(String[] args) { try { // Step 1: Load the MySQL driver Class.forName("com.mysql.cj.jdbc.Driver"); // Step 2: Establish a connection to the database Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "root", "password"); // Step 3: Create a statement Statement statement = connection.createStatement(); // Step 4: Execute a SELECT statement String sql = "SELECT * FROM table_name"; ResultSet resultSet = statement.executeQuery(sql); // Step 5: Create a new Excel workbook and sheet XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Table_Name"); // Step 6: Create a row for the header and populate it with column names Row headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("column1_name"); headerRow.createCell(1).setCellValue("column2_name"); headerRow.createCell(2).setCellValue("column3_name"); // Step 7: Loop through the result set and add the data to the sheet int rowNum = 1; while (resultSet.next()) { Row dataRow = sheet.createRow(rowNum++); dataRow.createCell(0).setCellValue(resultSet.getString("column1_name")); dataRow.createCell(1).setCellValue(resultSet.getString("column2_name")); dataRow.createCell(2).setCellValue(resultSet.getString("column3_name")); } // Step 8: Write the workbook to a file FileOutputStream fileOut = new FileOutputStream("Table_Name.xlsx"); workbook.write(fileOut); fileOut.close(); System.out.println("Data exported successfully to Table_Name.xlsx"); } catch (Exception e) { e.printStackTrace(); } } } |
Note: In this example, replace database_name with the name of the database you want to export data from, replace table_name with the name of the table you want to export data from, replace column1_name, column2_name, and column3_name with the names of the columns you want to export data from.
Converting Excel (.xlsx) file to MySQL or MariaDB Table using Java
Here’s a basic program in Java that converts an Excel file to a table in a MySQL database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ImportData { public static void main(String[] args) { try { // Step 1: Load the MySQL driver Class.forName("com.mysql.cj.jdbc.Driver"); // Step 2: Establish a connection to the database Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name", "root", "password"); // Step 3: Create a table in the database String createTableSQL = "CREATE TABLE IF NOT EXISTS table_name (column1_name VARCHAR(255), column2_name VARCHAR(255), column3_name VARCHAR(255))"; connection.createStatement().execute(createTableSQL); // Step 4: Load the Excel file FileInputStream file = new FileInputStream(new File("Excel_File.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); // Step 5: Prepare a statement for inserting data into the table String insertDataSQL = "INSERT INTO table_name (column1_name, column2_name, column3_name) VALUES (?, ?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(insertDataSQL); // Step 6: Loop through the rows and insert data into the table for (Row row : sheet) { if (row.getRowNum() == 0) { continue; } preparedStatement.setString(1, row.getCell(0).getStringCellValue()); preparedStatement.setString(2, row.getCell(1).getStringCellValue()); preparedStatement.setString(3, row.getCell(2).getStringCellValue()); preparedStatement.execute(); } System.out.println("Data imported successfully into table_name"); } catch (Exception e) { e.printStackTrace(); } } } |
Note: In this example, replace database_name with the name of the database you want to import data into, replace table_name with the name of the table you want to import data into, replace column1_name, column2_name, and column3_name with the names of the columns you want to import data into, and replace Excel_File.xlsx with the name of the Excel file you want to import data from.
