How to add Image to MySql database using Servlet and JDBC
Structured Query Language or SQL is a standard Database language which is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc. In this article, we will understand how to add an image to the MYSQL database using servlet.
MYSQL is a relational database. Relational database means the data is stored as well as retrieved in the form of relations (tables). Servlets are the Java programs that run on the Java-enabled web server or application server. They are used to handle the request obtained from the webserver, process the request, produce the response, then send the response back to the webserver. The properties of the servlets are that they work on the server-side. And they are capable of handling complex requests obtained from the webserver. In this article, the Eclipse IDE is used to execute the servlet and MYSQL workbench is used to run the MYSQL database. The following steps can be followed to add an image into the MYSQL database using servlet and JDBC:
- Step 1: Open Eclipse IDE and on the top right corner You’ll find 2 icons. Upon hovering, it will show JAVA EE and JAVA. As we will be working with a web project, so we will choose JAVA EE. After selecting it, go to File -> New -> Dynamic Web Project.
- Step 2: Now We need to copy and paste the JDBC Driver for MySQL (Connector – JAR_FILE) in lib folder of WEB-INF folder which is present in WebContent folder. (All the external libraries for the project needs to be here).
- Step 3: Now, we need to create a home page which will present a form wherein a user can upload an image file. We can create a JSP as well as HTML file in the WebContent folder. For Instance, we will create AddFile.jsp. We need to update the web.xml file as well, to update its Welcome File List. We have to add AddFile.jsp in the Welcome File List.
<
welcome-file-list
>
<
welcome-file
>AddFile.jsp</
welcome-file
>
</
welcome-file-list
>
- Step 4: Now, we need to add the code for the AddFile.jsp which has been created above. The code for this file is as follows:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<
html
>
<
head
>
<
meta
charset
=
"ISO-8859-1"
>
<
title
>Add File Here</
title
>
</
head
>
<
body
>
<
form
method
=
"post"
action
=
"serv"
enctype
=
"multipart/form-data"
>
<
div
>
<
label
>First Name:</
label
>
<
input
type
=
"text"
name
=
"firstName"
size
=
"50"
/>
</
div
>
<
div
>
<
label
>Last Name:</
label
>
<
input
type
=
"text"
name
=
"lastName"
size
=
"50"
/>
</
div
>
<
div
>
<
label
>Profile Photo: </
label
>
<
input
type
=
"file"
name
=
"photo"
size
=
"50"
/>
</
div
>
<
input
type
=
"submit"
value
=
"Save"
>
</
form
>
</
body
>
</
html
>
- Step 5: Now, we will make a class which will be used to establish the connection with MySql database using JDBC and can use that connection anytime in future to reflect anything upon Database. For instance, we will create “MyConnection.java” class and “getConnection()” static method to get a connection as follows:
import
java.sql.*;
public
class
MyConnection {
public
static
Connection getConnection()
{
// As java.sql package classes
// contains Checked Exceptions,
// we have to surround it with
// Try/Catch Block.
try
{
// We have to register the
// Driver class present in
// com.mysql.jdbc package.
DriverManager.registerDriver(
new
com.mysql.jdbc.Driver());
// To get the connection,
// 3 params need to be passed in
// DriverManager.getConnection() method.
Connection con
= DriverManager.getConnection(
"jdbc:mysql:// localhost:3306/"
+
"FileUploadDatabase"
,
"root"
,
"root"
);
/* 1. URL: "jdbc:mysql://", is the address
"localhost: " is the local machine
and "3306" is the port number
and "FileUploadDatabase" is the name
of the database.
2. UserName: "root", which is set
while creating a database server in
MySql Workbench.
3. Password: "root"
*/
return
con;
}
catch
(SQLException e) {
System.out.println(e.getMessage());
}
return
null
;
}
}
- Step 6: Now, we will create a database and table “users” which will store the contents passed from the HTML Form. The query for the same is as follows:
CREATE DATABASE FileUploadDatabase
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
first_name varchar(45) DEFAULT NULL,
last_name varchar(45) DEFAULT NULL,
photo mediumblob,
PRIMARY KEY (`id`)
) - Step 7: Now, we will create a Servlet which will take the request from the webpage and perform all the business logic and store the contents in MySql Database. For Instance, we will name it as “serv.java”. The following is the code in this servlet:
// Java program to implement
// the servlet
package
controllers;
import
java.io.IOException;
import
java.io.InputStream;
import
java.sql.Connection;
import
java.sql.PreparedStatement;
import
java.sql.SQLException;
import
javax.servlet
.ServletConfig;
import
javax.servlet
.ServletException;
import
javax.servlet
.annotation.MultipartConfig;
import
javax.servlet
.annotation.WebServlet;
import
javax.servlet
.http.HttpServlet;
import
javax.servlet
.http.HttpServletRequest;
import
javax.servlet
.http.HttpServletResponse;
import
javax.servlet.http.Part;
import
Dao.UploadFileDao;
import
connection.copy.MyConnection;
// This is the annotation-based
// mapping URL to Servlet.
@WebServlet
(
"/serv"
)
// This annotation defines the maximum
// file size which can be taken.
@MultipartConfig
(maxFileSize =
16177215
)
public
class
serv
extends
HttpServlet {
// auto generated
private
static
final
long
serialVersionUID = 1L;
public
serv()
{
super
();
}
// This Method takes in All the information
// required and is used to store in the
// MySql Database.
public
int
uploadFile(String firstName,
String lastName,
InputStream file)
{
String SQL
=
"INSERT INTO users "
+
"(first_name, last_name, "
+
"photo) values (?, ?, ?)"
;
int
row =
0
;
Connection connection
= MyConnection.getConnection();
PreparedStatement preparedStatement;
try
{
preparedStatement
= connection.prepareStatement(sql);
preparedStatement
.setString(
1
, firstName);
preparedStatement
.setString(
2
, lastName);
if
(file !=
null
) {
// Fetches the input stream
// of the upload file for
// the blob column
preparedStatement.setBlob(
3
, file);
}
// Sends the statement to
// the database server
row = preparedStatement
.executeUpdate();
}
catch
(SQLException e) {
System.out.println(e.getMessage());
}
return
row;
}
// As Submit button is hit from
// the Web Page, request is made
// to this Servlet and
// doPost method is invoked.
protected
void
doPost(
HttpServletRequest request,
HttpServletResponse response)
throws
ServletException, IOException
{
// Getting the parametes from web page
String firstName
= request.getParameter(
"firstName"
);
String lastName
= request.getParameter(
"lastName"
);
// Input stream of the upload file
InputStream inputStream =
null
;
String message =
null
;
// Obtains the upload file
// part in this multipart request
Part filePart
= request.getPart(
"photo"
);
if
(filePart !=
null
) {
// Prints out some information
// for debugging
System.out.println(
filePart.getName());
System.out.println(
filePart.getSize());
System.out.println(
filePart.getContentType());
// Obtains input stream of the upload file
inputStream
= filePart.getInputStream();
}
// Sends the statement to the
// database server
int
row
= UploadFileDao
.uploadFile(firstName,
lastName,
inputStream);
if
(row >
0
) {
message
=
"File uploaded and "
+
"saved into database"
;
}
System.out.println(message);
}
}
Note: The above code cannot be run on the online IDE.
Output:
- We can view the database after running the code to check the images uploaded in the database by clicking on “Open Value In Editor” as follows:
- After we click on “Open Value in Editor”, a window will pop up showing the image which is stored in the database in three formats: Binary Format, Text Format and Image format.
- Now, if we click on the image option, we will be able to see the image which is uploaded.