Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

JSP with MySQL

In this article we are working with MySQL database management as MySQL very reliable and we can easily use it with the server side or client/server architecture model.

JSP is the Server side programming language and it is very much useful and standardized than other languages. So this language is used for database programming system logic. Generally we use this JSP program through ODBC connection procedure. When we are working with it, we must use any database system like MS-Access, Oracle, SQL Server, and MySQL.

The MySQL is a leading open source RDBMS. MySQL is also a multi-user, multithreaded database management system. MySQL is especially popular on the web based server programming model. As it is free database server no license key is required for the server based programming model. So we can easily accept it in our project model diagram. This server is now used more over the software based programming model.

Why we choose MySQL for the Java Server Page programming:

There is lot more than database server present in the JSP System but we are using MYSQL for more purpose. Some major purpose for choosing MySQL is:

  • MS Access does not support Client Server Architecture.
  • Oracle is very expensive than other software.
  • SQL Server is Microsoft platform.
  • But MySQL is free software and it also supports Client Server Architecture model.

Procedure to Start, stop and Connecting MySQL with JSP:

  • Open MySQL Prompt
  • Start the MySQL database server: $sudo/etc/init.d/mysql stop

  • Stopping the MySQL database server:
  • $sudo myslqadmin -p ping
    Password: _________
    Enter password: ____________
    MySQL is alive

  • Checking, the status of the server. Type two passwords.
  • One for the sudo command and one for the mysqladmin command.

Processing to Connect MYSQL with JSP:

  • Download mysql-connector-java-5.1.13-bin.jar
  • Store it in the hard drive.
  • Right click on the project libraries.
  • Select mysql-connector-java-5.1.13-bin.jar file
  • This file automatically stored in our libraries folder.
  • This mysql-connector-java-5.1.13-bin.jar is responsible for accessing the connection procedure MYSQL with Java Server Pages
  • The Following figure shows that process to connect with JSP Project in Net beans.
Showing connection

Figure 1: Showing connection

Creating a database:

In this article we will develop with creating an empty database. This Program model consists of two jsp files. One is called index.jsp and validation.jsp. The validation.jsp creates a new database or reports an error in validation.jsp file.

Listing 1: Showing index.jsp code

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page errorPage="validation.jsp" %>
<%@ page import="java.sql.*" %>
<html>
<head>
<title>MySQL Database creation Process….</title>
</head>
<body>
<h2>Creation of a B.C.E.I Course database</h2>
<jsp:declaration>
Statement stmt;
Connection con;
String url = "jdbc:mysql://localhost:3306/";
</jsp:declaration>
<jsp:scriptlet><![CDATA[
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, "root", "mrbool"); 
stmt = con.createStatement();
stmt.executeUpdate("Create Database Course");
con.close();
]]></jsp:scriptlet>
</body>
</html>

Through this program model index.jsp page connects to a server and creates a course database. If we are refreshing the page again then we get an error message and we see that validation.jsp page is encumbered. MYSQL cannot support creating two databases with a same name.

Listing 2: Showing validation code sample

 
<%@ page errorPage=" validation.jsp" %>
If an Exception occurs in page, the server loads a validation.jsp page.
<jsp:declaration>
 Statement stmt;
 Connection con;
 String url = "jdbc:mysql://localhost:3306/";
 </jsp:declaration>

Here we declare a Statement, Connection and a URL to connect. The URL (connection)is having a protocol, host and a port number. The default MySQL port number is 3306.

Class.forName("com.mysql.jdbc.Driver");

Now we have to register a MySQL JDBC driver in our database project.

con = DriverManager.getConnection(url,"root","mrbool"); 

We get a connection to the database for user root with “mrbool” password. Through this process we can access the account on MySQL.

stmt = con.createStatement();
stmt.executeUpdate("Create Database Course..");

After getting the statement object, we will execute the statement.

con.close();

At the end of the program we close the connection.

Listing 3: Sample showing error page

<%@ page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page isErrorPage="true" %>
<html>
<head>
<title>Error page</title>
<style>
     { font-size: 12px; font-family: Mistral} //This is the Cascading Style Sheet
</style>
</head>
<body>
<h2>An Error will be occurred!</h2>
<p>Message <jsp:expression> exception.getMessage() </jsp:expression>
</body>
</html>

This is our validation page.

 <%@ page isErrorPage="true" %> 

This is for enabling an exception object for a page.

 <p>Message <jsp:expression> exception.getMessage() </jsp:expression>

Now we show the validation message. This is the procedure to check it from the mysql command line tool when the database was created in the server.

In the following example we will be adding and deleting the items.

mysql> create table course(id int not null primary key auto_increment, 
              cname varchar(30), duration varchar(40), amt int);

Query OK, 0 rows affected (0.13 sec)

We create a course table first. The table will have five columns. The fields are id, cname, duration of the course, amount of the course.

Now we see that the table which looks likes as using the MySQL command line tools. The style.css file defines the style of our application and it also defines the look and feel of input boxes, buttons, tables and anchors. It also generates a navigation toolbar for our project.

The index.jsp file is used to add new course into the database. In the form we input data and submit it.

Listing 4: Showing for submit

<%
  String author = request.getParameter("id");
  String title = request.getParameter("cname");
  String year = request.getParameter("duration");
  String remark = request.getParameter("amt");
%>

In that we are describing the methods for inserting data to the database, deleting data and getting all data from the table.

String insert = "INSERT INTO course(cname, duration, amt)" + "VALUES (?, ?, ?, )";
PreparedStatement ps = con.prepareStatement(insert);
 ps.setString(1, cname);
 ps.setString(2, duration);
 ps.setString(3, amt);
ps.executeUpdate();

Now we use prepared statements. These are parameterized SQL(Structure Query Language) statements. There are two most important explanations to use parameterized statements. The statement helps us securing the code against the SQL(Structure Query Language) injections and easier coding due to difficulties with double and single quotes.

It is the example of edit, delete and searching record procedure through JSP and MySQL Program.

Listing 5: Sample showing complete example

<%@ page import="java.sql.*" %>
<html>
<head>
<script language="javascript">
function editRec (id){
    var f=document.form;
    f.method="post";
    f.action='edit.jsp?id='+id;
    f.submit();
}
function deleteRec (id){
    var f=document.form;
    f.method="post";
    f.action='delete.jsp?id='+id;
    f.submit();
}
</script>
</head>
<body>

<br><br>
<form method="post" name="form">
<table border="1">
<tr><th>Course Id</th><th>Course Name</th><th>Duration</th><th>Amount</th></tr>
<%
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String db = "cei";
String driver = "com.mysql.jdbc.Driver";
String userName ="root";
String password="srimani";
int sumcount=0;
Statement st;
try{
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+db,userName,password);
out.println(con);
String q = "select * from course";
st = con.createStatement();
ResultSet rs = st.executeQuery(q);
%>

<%
while(rs.next())
{
%>
<tr><td><%=rs.getString(2)%></td>
<td><%=rs.getString(3)%></td>
<td><%=rs.getString(4)%></td>
<td><%=rs.getString(5)%></td>
<td><input type="button" name="edit" value="Edit" style="background-color:#49743D;font-weight:bold;color:#ffffff;" onclick="editRec (<%=rs.getString(1)%>);" ></td>
<td><input type="Button" name="delete" value="Delete" style="background-color:#ff0000;font-weight:bold;color:#ffffff;" onclick="deleteRec(<%=rs.getString(1)%>);" ></td>
</tr>
<%
}
%>
<%
}
catch(Exception e){
e.printStackTrace();
}
%>
</table>
</form>
</body>
</html>

Conclusion

MySQL is very dedicated database management system that supports to develop a good server side system and this server side system gives a good backup of data. Java Server page is also a standard and high security provided application software. Through this software we generate different types of project and that project is built-up with very reliable procedure. MySQL is no doubt a reliable system for Java Server Page.



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login