Hey coders,
This tutorial will teach us how to connect the MySQL database more specifically your SQL server with Java. Here, we will connect the SQL server to Java using JDBC i.e., Java Database Connectivity, and access our tables within the database. Then, use them in our Java code to perform simple SQL functions, such as creating tables, inserting into tables, deleting from tables and finally dropping tables.
So let’s start with installing Java and Mysql into our systems:
Installing Java And MySQL
- Firstly we have to download JDK (Java Development Kit) along with an IDE to program in Java. For this tutorial, I will use IntelliJ as my IDE for running Java on my system.
- JDK can be downloaded from the official website of ORACLE India, and IntelliJ can be downloaded from the official website of JetBrains.
- Once downloaded install both of them using the instructions given at each step of the installation, add the path, set the environment variables and you are all set to run your code in Java using IntelliJ, but there is one more thing we have to do.
- Next up we have to install MySQL in our system, for that, we will be reaching out to MySQL’s official site, navigating to the downloads section, clicking on MySQL community downloads and then clicking on MySQL installer for Windows and get the latest version at the time you are reading this.
- Follow the installation instructions, set the server name and password, and after installing you are all set to move on to the next section of learning how to connect to Database tables in Java and code.
Starting the Code
- We start with importing all the required packages, creating classes and creating a main function, after that, we add a Scanner object to take inputs and perform actions with the inputted variables.
import java.util.*; import java.lang.*; import java.sql.*;
- Now to start with the connection we have to create an object using the inbuilt Connector interface, driver manager class and the getConnection() method these tools help us to establish a connection between the database stored within MySQL of the computer and the java class we are working in. This can be done in the following manner:
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "123456789");
-
-
- The format to be followed within getConnection() is:
-
- We have to enter the database URL which is of the form- “jdbc:subprotocol:subname”- the subprotocol is fixed as mysql and the subname can be retained from SQL as it is the address where your databases are located.
- Next, we enter the username which was set during the time of installation of MySQL into the system. (eg: “root”)
- Finally, we enter the password to your SQL databases which was set during the time of installation. (“123456789” in this case )
- All of these are to be inserted in String format i.e., enclosed within inverted commas.
-
- The format to be followed within getConnection() is:
- Now we can use queries used in MySQL by inserting them as strings in variables of String data types and then creating and using Statement objects and some functions to execute these queries as shown in the following examples, I have taken the database name as “data” and table name as “salary_info” for these examples:
-
-
Creating Database:
-
System.out.println("enter the name of database to be created");
String name=sc.next();
String t="Create Database "+name;
Statement ST=con.createStatement();
ST.executeUpdate(t);
-
Using Database:
System.out.println("enter the database u wish to use");
String name=sc.next();
String t2="Use "+ name;
Statement STt=con.createStatement();
STt.executeUpdate(t2);
-
Creating a Table:
System.out.println("enter the name of table to be created");
String table=sc.next();
String t="Create table "+table
+ " (id INTEGER not NULL, "
+ " name VARCHAR(20), "
+ " age INTEGER, "
+ " address VARCHAR(20), "
+ " salary DOUBLE, "
+ " PRIMARY KEY( id ));";
Statement ST=con.createStatement();
ST.executeUpdate(t);
-
Inserting into Table:
System.out.println("Enter the details to be inputted");
String str2 = "insert into data.salary_info values(?,?,?,?,?)";
PreparedStatement p1 = con.prepareStatement(str2);
p1.setInt(1, sc.nextInt());
p1.setString(2, sc.next());
p1.setInt(3, sc.nextInt());
p1.setString(4, sc.next());
p1.setDouble(5, sc.nextDouble());
p1.executeUpdate();
-
Deleting from Table:
System.out.println("enter the ID to be deleted");
int std=sc.nextInt();
String str_d="DELETE FROM data.salary_info WHERE id = ?";
PreparedStatement p2= con.prepareStatement(str_d);
p2.setInt(1,std);
p2.executeUpdate();
-
Printing Table:
System.out.println("___________________________________________________________________________");
System.out.printf("%-20s%-20s%-20s%-20s%-20s%n", "ID", "name", "Age", "Address","Salary");
System.out.println("___________________________________________________________________________");
Statement st = con.createStatement();
String q = "Select * from data.salary_info;";
ResultSet set = st.executeQuery(q);
while (set.next()) {
int id = set.getInt(1);
String name = set.getString(2);
int age = set.getInt(3);
String address = set.getString(4);
double Salary = set.getDouble(5);
System.out.printf("%-20s%-20s%-20s%-20s%-20s%n", id, name, age, address,Salary);
-
Dropping Table:
Statement stt= con.createStatement(); String ste="Drop table data.salary_info;"; stt.executeUpdate(ste);
-
Dropping Database:
Statement strr=con.createStatement(); String ss="Drop database data;"; strr.executeUpdate(ss);
All of these queries can be used in a single file and can be understood easily in the following manner:
Main Code Example
import java.util.*;
import java.lang.*;
import java.sql.*;
class SQLfun{
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "123456789");
Scanner sc=new Scanner(System.in);
public SQLfun() throws SQLException {
}
public void create_table(String table) throws SQLException{
String name= "data";
String t2="Use "+name;
Statement STt=con.createStatement();
STt.executeUpdate(t2);
String t="Create table "+table
+ " (id INTEGER not NULL, "
+ " name VARCHAR(20), "
+ " age INTEGER, "
+ " address VARCHAR(20), "
+ " salary DOUBLE, "
+ " PRIMARY KEY( id ));";
Statement ST=con.createStatement();
ST.executeUpdate(t);
}
public void print_table() throws SQLException {
System.out.println("___________________________________________________________________________");
System.out.printf("%-20s%-20s%-20s%-20s%-20s%n", "ID", "name", "Age", "Address","Salary");
System.out.println("___________________________________________________________________________");
Statement st = con.createStatement();
String q = "Select * from data.salary_info;";
ResultSet set = st.executeQuery(q);
while (set.next()) {
int id = set.getInt(1);
String name = set.getString(2);
int age = set.getInt(3);
String address = set.getString(4);
double Salary = set.getDouble(5);
System.out.printf("%-20s%-20s%-20s%-20s%-20s%n", id, name, age, address,Salary);
}
}
public void inputs() throws SQLException{
System.out.println("Enter the details to be inputted");
String str2 = "insert into data.salary_info values(?,?,?,?,?)";
PreparedStatement p1 = con.prepareStatement(str2);
p1.setInt(1, sc.nextInt());
p1.setString(2, sc.next());
p1.setInt(3, sc.nextInt());
p1.setString(4, sc.next());
p1.setDouble(5, sc.nextDouble());
p1.executeUpdate();
}
public void deletes() throws SQLException{
System.out.println("enter the ID to be deleted");
int std=sc.nextInt();
String str_d="DELETE FROM data.salary_info WHERE id = ?";
PreparedStatement p2= con.prepareStatement(str_d);
p2.setInt(1,std);
p2.executeUpdate();
}
public void drop() throws SQLException{
Statement stt= con.createStatement();
String ste="Drop table data.salary_info;";
stt.executeUpdate(ste);
}
public void drop_database() throws SQLException{
Statement strr=con.createStatement();
String ss="Drop database data;";
strr.executeUpdate(ss);
}
}
public class Main {
public static void main(String[] args) throws SQLException {
Scanner sc = new Scanner(System.in);
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "123456789");
SQLfun obj=new SQLfun();
System.out.println("do u wanna create a database");
char inpp = sc.next().charAt(0);
if(inpp=='Y'||inpp=='y'){
System.out.println("enter the name of database to be created");
String name=sc.next();
String t="Create Database "+name;
Statement ST=con.createStatement();
ST.executeUpdate(t);
String t2="Use "+name;
Statement STt=con.createStatement();
STt.executeUpdate(t2);
System.out.println("do u wanna create a table");
char inpt = sc.next().charAt(0);
if(inpt=='Y'||inpt=='y'){
System.out.println("enter the name of table to be created");
String table=sc.next();
obj.create_table(table);
}
}else{
System.out.println("enter the database u wish to use");
String name=sc.next();
String t2="Use "+ name;
Statement STt=con.createStatement();
STt.executeUpdate(t2);
System.out.println("do u wanna create a table");
char inpt = sc.next().charAt(0);
if(inpt=='Y'||inpt=='y'){
System.out.println("enter the name of table to be created");
String table=sc.next();
obj.create_table(table);
}
}
System.out.println("do u wanna input");
char inp = sc.next().charAt(0);
while(inp == 'Y'||inp=='y') {
obj.inputs();
System.out.println("do u wanna continue inputting");
inp=sc.next().charAt(0);
}
obj.print_table();
System.out.println("do u wanna Delete");
char inp2 = sc.next().charAt(0);
while(inp2=='Y'||inp2=='y') {
obj.deletes();
System.out.println("do u wanna continue deleting");
inp2=sc.next().charAt(0);
}
obj.print_table();
System.out.println("do u wanna Drop table in data");
char inp3 = sc.next().charAt(0);
if(inp3=='Y'||inp3=='y'){
obj.drop();
}
System.out.println("do u wanna Drop database data");
char inp4 = sc.next().charAt(0);
if(inp4=='Y'||inp4=='y'){
obj.drop_database();
}
}
}
Output:
do u wanna create a database y enter the name of database to be created data do u wanna create a table y enter the name of table to be created salary_info do u wanna input y Enter the details to be inputted 101 Sujal 20 Raipur 10000.0 do u wanna continue inputting y Enter the details to be inputted 102 Keshav 40 Nagpur 50000.0 do u wanna continue inputting m ___________________________________________________________________________ ID name Age Address Salary ___________________________________________________________________________ 101 Sujal 20 Raipur 10000.0 102 Keshav 40 Nagpur 50000.0 do u wanna Delete y enter the ID to be deleted 102 do u wanna continue deleting n ___________________________________________________________________________ ID name Age Address Salary ___________________________________________________________________________ 101 Sujal 20 Raipur 10000.0 do u wanna Drop table in data y do u wanna Drop database data y Process finished with exit code 0
External Libraries:
- There can be a case when you do all of this but still the code gives an error that Java is not connected to the database in this we have to download a mysql jdbc connector driver and add it into the libraries of your current project under the external libraries folder. This connector driver can be found easily on the internet and a zip file can be downloaded easily which after extracting is to be added to the external libraries section of your current project.
- Alternatively, you can download the driver from the following link: https://dev.mysql.com/downloads/connector/j/
I hope this tutorial helped you to learn how to connect the MySql server to a Java project.
Thank you