We can add new record into database table through insert command of SQL statement.
First get user data from JSP form and then save it into table
Example of add Record in database through JSP
CREATE TABLE staff_register (
`iEmpID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sStaffName` varchar(60) DEFAULT NULL,
`sStaffDept` varchar(45) DEFAULT NULL,
`iStaffPhone` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`iEmpID`),
UNIQUE KEY `sStaffName` (`sStaffName`)
)
First we will make a form to collect information from user to store record into table.
addRecord.jsp
HTML Code Example
<%@ page language="java" import="java.sql.*" errorPage="" %>
<html>
<head>
<title>Add new record into database in JSP</title>
</head>
<body>
<form name="frm" action="saveRecord.jsp">
<h1>Add New Record into Database</h1> <br>
Staff Name <input type="text" name="sStaffName"><br>
Staff Department <input type="text" name="sDept"><br>
Staff Phone <input type="text" name="sPhone"><br>
<input type="submit" name="submit" value=" Submit ">
</form>
</body>
</html>
When submit addRecords.jsp form, it go to saveRecord.jsp file which contains JSP code to save data record into table.
saveRecord.jsp
<%@ page language="java" import="java.sql.*" errorPage="" %>
<%
Connection conn = null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp","root", "");
PreparedStatement psInsertRecord=null;
String sqlInsertRecord=null;
String staffName=request.getParameter("sStaffName");
String staffDept=request.getParameter("sDept");
int staffPhone=Integer.parseInt(request.getParameter("sPhone"));
//// if this come blank and alpha number it will throw parse int
//NumberFormatException exception, only number
try
{
sqlInsertRecord="insert into staff_register (sStaffName, sStaffDept, iStaffPhone) values(?,?,?)";
psInsertRecord=conn.prepareStatement(sqlInsertRecord);
psInsertRecord.setString(1,staffName);
psInsertRecord.setString(2,staffDept);
psInsertRecord.setInt(3,staffPhone);
psInsertRecord.executeUpdate();
}
catch(Exception e)
{
response.sendRedirect("addRecord.jsp");//// On error it will send back to addRecord.jsp page
}
try{
if(psInsertRecord!=null)
{
psInsertRecord.close();
}
if(conn!=null)
{
conn.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
<html>
<body>
Data is saved successfully.
</body>
</html>
Remember:
iEmpID field in table is auto increment key and database automatically generates value for it. So don’t include auto incremented field in SQL insert query.
`sStaffDept` varchar(45) NOT NULL, if field set as Not null we can not save null value for this field. We have to save some value in this field, or error will occur.
|
