Hell...guys today I want to show you how to give database connection to the HTML pages.
In a previous article, I showed you how to make a Paytm gift card page. in that article, I do not explained give a database connection to the page and how the input data given by the user is stored in the database.
So in this article, I will give you the full information about database connection and how to store user data in the database.
What is the database:
the database is used to store information/data. and these information/data are arranged in the form of tables having rows and columns. so we cannot access the Database directly. but we can access it through the DBMS concept. so we will be using the "MySQL" Database App. As a DBMS, We will Use the "SQLYog" application.
NOTE:
what is logical DB: Small Portion of Database dedicated for a particular purpose. The creation of a logical database is optional.
NOTE:
Creation of a table: to create a table, we have two options.
i. using SQL Queries(DDL-->create query).
ii. using shortcuts of the SQLYog app.
Syntax:
Create table tablename (colname_1 datatype(size)
constraint,
colname_2 datatype(size)
constraint,
colname_n datatype(size)
constraint);
Example-1: Query for creating student table with 4-columns.
create table student (regNum int(10),
stuName varchar(15),
phoneNumber varchar(12),
mailID varchar(30));
Example-2: Query for creating dept table.
create table dept (deptNum int(5), deptName varchar(10), loc varchar(10));
What is JDBC architecture:
Here, we have 4-important components:
i. Java Application/project[Java code+SQL Queries].
ii. JDBC-API
iii. JDBC-Drivers
iv. MySQL Database Application
Java application: Java Application consists of Source code, which is a combination of Java and SQL.
JDBC-API: it is used to share information from Java App to Database App.
JDBC-Drivers: it is used to translate Java Calls to SQL Calls.
MySQL Database Application: it is used to store information/data.
NOTE: JDBC-API corresponds to java.sql package.
Some of the interfaces present in java.sql package(JDBC-API) are;
a. Connection interface
b. Statement interface
c. PreparedStatement interface
d. CallableStatement interface
e. ResultSet interface etc.
One of the important Helper classes in java.sql package(JDBC-API) is DriverManager.
What is a URL:
URL means(uniform resource locator):
URL is used to access some information.
URL consists of four important components:
i. Protocol
ii. HostInfo
iii. PortNumber
iv. Userinfo
i. Protocol: it is a set of rules which must be followed to start accessing an application.
ii. HostInfo: is of two types
a. localhost
b. remote host
iii. PortNumber: is a small gateway to the server/database.
iv. UserInfo: is used to provide security for the server/database.
a. username
b. password
Syntax:
protocol://hostinfo: port number? user info
where the protocol is "http" or "https", in the case of Server. and protocol is "jdbc: subprotocol", in the case of Database.
Example-1: URL for MySQL Database
jdbc:mysql://localhost:3306?user=root&password=12345
Example-2: URL for Oracle Database
jdbc:oracle://localhost:1521?user=scott&password=tiger
Inbuilt Library: java.sql package(JDBC-API)
NOTE: Access to the database using URL
Step-1: Establish a connection between the java application and database connection.
Step-2: create a platform
Step-3: execute SQL queries
Step-4: process resultant data(optional)
Step-5: close the connection
Step-1: Establish a connection between the java application and database connection.
1.getConnection() is static method, which is present in java.sql.DriverManager class.
2.The Method signature for getConnection() is public static Connection getConnection(String arg1)
3.getConnection() throws SQLException(checked exception), which must be handled by try-catch block.
The syntax used for establishing the connection is:
Connection connection = DriverManager.getConnection(String url);
NOTE: "No Suitable Driver found" error is because of two reasons:
Reason-1: Driver jar file is not present in a project.
Reason-2: The URL in the program would be incorrect.
- To avoid the above error, we have to copy and paste the MySQL-connector jar file(Drivers).
Step-2: Create the platform.
1.JDBC Program = Java Code + SQL Queries.
2.Java Compiler - is a Specific Complier.
3.Generic Compiler - (Doesn't Exist in Industry)
- The Purpose of Platform creation is "To carry SQL Queries to the database"
- Three Types of Platforms.
i. Statement Type: it is used for HardCoded values
ii. PreparedStatement Type: it is used for Runtime values
iii. CallableStatement Type: it is used for Stored Procedures
- createStatement(): a method is a non-static method used for creating Statement tye platform
Syntax: used for creating Statement Type platform is:
Statement statement = connection.createStatement();
- The Method Signature for createStatement() method is:
public Statement createStatement()
Step-3: Execution of SQL Queries.
- Write Operation:- can alter/modify/change contents in file.
- Read Opeartion:- cannot alter/modify/change contents in file.
- Insert, Update and Delete queries is underwrite the operation.
- Select query is under reading operation.
- Since the behavior of (insert, update, delete) and a select query is different, we have two inbuilt methods.
i. executeUpdate():- write operation
ii. executeQuery():- read operation
so, it's a brief explanation about the database and how it works. now I'm showing you a JSP(java server page) it is a file. the extension of JSP file is ".jsp". this file is used to provide a connection between the java application and database.
what is JSP and how it works? this concept I will explain in my next article.
Check the below code:
Note: use this file to connect the previously used Paytm gift card page.
this is the file to give the database connection for HTML front-end pages.
File: Connect.jsp
<%@ page import="java.sql.*"%>
<%
String number = request.getParameter("number");
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306?user=root&password=12345");
PreparedStatement ps = con.prepareStatement("insert into test.addingnumber values(?)");
ps.setString(1, number);
int x = ps.executeUpdate();
if (x > 0) {
out.println("Number added successfully...");
} else {
out.println("Adding number faild...");
}
} catch (SQLException e) {
e.printStackTrace();
}
%>
Result:
If you have any doubts please let me know in the comment section below. and feel free to use this code without hesitation.
0 Comments