How to connect with MYSQL or how to connect with MYSQL database to create dynamic software or web applications in Java. In this JDBC tutorial, We will see all this in detail with multiple MySQL versions but before starting do you know what a Database is?
What is a Database?
A database is a collection of information used to store data and get it when required. In real-time life let’s take an example of an organization or company.
JDBC driver is used to create a connection with the MYSQL database in java. JDBC drive is another java program(Jar) that helps to create connections and perform other operations like Add, Delete and update the data. there are many operations that we can perform by the JDBC driver. To get more detail on it check out the MYSQL tutorial.
Java database connectivity with MYSQL v8.0
MYSQL community continues updating MYSQL applications to make them more reliable and scalable. but the most used versions are MYSQL 5 and MYSQL 8.
In this example, we are going to discuss the following points step by step to perform Java database connectivity with MYSQL 8.0
- Create a Database in MYSQL.
- Design a registration form using JSP or HTML.
- Database connectivity to insert data in the database.
Note: There are only two changes for MYSQL 8.0 the first one is maven dependencies for MySQL 8 and the driver name “com.mysql.cj.jdbc.Driver”. Driver name is changed from MYSQL v8.0.
Step to create database connectivity using MySQL in java
-
Load MYSQL driver
Class.forName("com.mysql.cj.jdbc.Driver"); Class.forName() will get the MySQL driver "com.mysql.cj.jdbc.Driver” as an input parameter.
- Create a connection with URL, Username, and password
DriverManager.getConnection("jdbc:mysql://localhost:3306/javawebtraining","root","root"); getConnection() method is used to create a connection with MYSQL. that required 3 Important things Connection URL, UserName, and Password.
-
Write a MySQL Query.
String query = "insert into register (username, email, password) values (?, ?, ?)"; Write a SQL query to insert data into the “register” table.
-
Prepare a statement
PreparedStatement stmt = con.prepareStatement(query); Create a statement or Prepared statement and pass the query into a statement.
-
Pass the data according to the column
stmt.setString(1, username); Put the information on statements.
-
Execute and close the connection.
stmt.execute(); to send the statement and con.close();or close the connection.
Java Code to connect with MYSQL Database
// Get the data request
String username = request.getParameter("uname");
String email = request.getParameter("email");
String Password = request.getParameter("Password");
//Database connectivity
try {
//Load MySQL Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//URL, Username and password to create connection
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/javawebtraining","root","root");
//Write a sql query to insert data in register table
String query = "insert into register (username, email, password) values (?, ?, ?)";
//Create a statement on the connection
PreparedStatement stmt = con.prepareStatement(query);
//Pass data to statement.......... accordingly in column 1,2 and 3
stmt.setString(1, username);
stmt.setString(2, email);
stmt.setString(3, Password);
//Execute prepare statement
stmt.execute();
//Close the connection
con.close();
out.print("Data is instered successfully");
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Example of User registration in Java and MYSQL
Let’s create a simple user registration form in java and create a connection with MySQL and insert the user information into the user table in MYSQL.
Create a database name as “javawebtraining” in MySQL
CREATE SCHEMA `javawebtraining` ;
Create a Table name as “register”.
CREATE TABLE `javawebtraining`.`register` ( `uid` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR(45) NULL, `email` VARCHAR(45) NULL, `password` VARCHAR(45) NULL, PRIMARY KEY (`uid`));
Create a maven project and add the dependency for Mysql 8
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency>
Design a simple registration form using HTML and CSS.
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {
font-family: Arial, Helvetica, sans-serif;
background-color: black;
}
* {
box-sizing: border-box;
}
.container {
padding: 16px;
background-color: white;
}
input[type=text], input[type=password] {
width: 100%;
padding: 15px;
margin: 5px 0 22px 0;
display: inline-block;
border: none;
background: #f1f1f1;
}
input[type=text]:focus, input[type=password]:focus {
background-color: #ddd;
outline: none;
}
.registerbtn {
background-color: #4CAF50;
color: white;
padding: 16px 20px;
margin: 8px 0;
border: none;
cursor: pointer;
width: 100%;
opacity: 0.9;
}
</style>
</head>
<body>
<form action="Register">
<div class="container">
<h1>Registeration form</h1>
<h3>Example to insert data in mysql 8.0</h3>
<label for="uname"><b>User Name :</b></label>
<input type="text" placeholder="Enter User Name" name="uname" required>
<label for="psw"><b>Email</b></label>
<input type="password" placeholder="Enter Password" name="email" required>
<label for="psw-repeat"><b>Password</b></label>
<input type="password" placeholder="Repeat Password" name="password" required>
<button type="submit" class="registerbtn">Register</button>
</div>
</form>
</body>
</html>
Create a servlet name as Register.java
Servlet will get the request from the client and pass it to the database
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class Register
*/
public class Register extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public Register() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
PrintWriter out = response.getWriter();
// Get the data request
String username = request.getParameter("uname");
String email = request.getParameter("email");
String Password = request.getParameter("Password");
//Database connectivity
try {
//Load MySQL Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//URL, Username and password to create connection
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/javawebtraining","root","root");
//Write a sql query to insert data in register table
String query = "insert into register (username, email, password) values (?, ?, ?)";
//Create a statement on the connection
PreparedStatement stmt = con.prepareStatement(query);
//Pass data to statement.......... accordingly in column 1,2 and 3
stmt.setString(1, username);
stmt.setString(2, email);
stmt.setString(3, Password);
//Execute prepare statement
stmt.execute();
//Close the connection
con.close();
out.print("Data is instered successfully");
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
What is JDBC Connection URL for MySQL?
jdbc:mysql://localhost:3306/javawebtraining is a JDBC connection URL where “jdbc:mysql://localhost:” is a domain string to connect a Java application with MYSQL application “3306” is a port number on which MYSQL server is running and “javawebtraining” is the name of the database. So the complete JDBC connection will be created by Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/javawebtraining","root","root");
Get connection method will require 3 parameters Connection URL, Database Username, and Database password.
MYSQL version error
You might get some MYSQL versions error to solve them make sure following points.
- MYSQL server version and the version of java MySQL connection (Jar) should be the same.
- If you are using MYSQL version below the 8 then the Java MYSQL driver will be “com.mysql.cj.jdbc.Driver”.
- If you are using the MYSQL 8 version or above to 8 then the Java MYSQL driver will be “com.mysql.jdbc.Driver”.
Practise Task in JSP
- Inserts a date in the database using date picker, JSP
- Insert Data of multiple pages in a single table.
- Insert data from the single page into multiple tables.
- login and logout with JSP and servlet with Mysql
- JSP page directives Jsp, Include Directive, JSP Taglib directive
- Include Directive in JSP
- Implicit Objects in JSP with example
- How to count the number of visitors for the website in JSP
- Java database connectivity with MYSQL 8.0
