October 2014 ~ Mantled

=======MYSQL=======
--Login---
C:\Program Files (x86)\MySQL\MySQL Server 5.6>mysql -u root -p
Enter password: **** //root

mysql> CREATE DATABASE db;
Query OK, 1 row affected (0.00 sec)

mysql> USE db;
Database changed
mysql> CREATE TABLE user_table(username varchar(25),password varchar(25),secureno int);

//check table ----------
--Login---
C:\Program Files (x86)\MySQL\MySQL Server 5.6>mysql -u root -p
Enter password: **** //root

//pointing db
mysql> USE db;

desc user_table;
///---Result-------
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(25) | YES | | NULL | |
| password | varchar(25) | YES | | NULL | |
| secureno | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

//to know prot no

mysql> show variables; //3306

//---------Imp--------- not used this resource to new forum site ,collected from

http://bigknol.com/open-blog/2013/07/eclipse-jee-development-tutorial/

,add mysql env variable screen shot also--------------------imp-------//

Prerequisite for JSP/MySQL Application with Eclipse JEE Kepler.

Download and Install MySQL Server 5.5 on your Windows Machine
Install Tomcat Server6.0( 32-bit windows zip)
Download MySQL Connector/j for connecting JSP to MySQL Server.(mysql-connector-java-5.1.25.zip, Platform Independent (Architecture Independent))
Download Eclipse IDE for Java EE Developers 32 bit windows And unzip it

JSP MySQL Eclipse JEE Development Kepler Tutorial

Creating a database and Table using MySQL : Open cmd prompt (Windows+R and Type ‘cmd’) or use MySQL 5.5 Command line client. Change directory to MySQL Server 5.5 installed Path. (In this example path looks like : C:\mysql\MySQL Server 5.5\bin>)

Create a new user in MYSQL Sever

1

2

mysql –u <user-name> -p

enter password: *****

You can use root if you have super user privilege, or create a new user in MySQL, to create a new user:

1

2

3

CREATE USER ‘nikisurf’@’localhost’ IDENTIFIED BY ‘password’;

GRANT ALL PRIVILEGES ON *.* TO ‘nikisurf’@’localhost’;

Login as a new user

mysql –u nikisurf -p

Create a new Database db and Selecting Database

1

2

CREATE DATABASE db;

USE db;

Create a new Table in MySQL called ‘info’

1

2

3

4

CREATE TABLE info(username varchar(25),password varchar(25),

firstname varchar(25),lastname varchar(25),

address varchar(25),pincode int,

phonenumber int);

Open Eclipse

Click eclipse.exe file from unzipped folder and set a workspace(location of projects)

Create a new Dynamic Web Project

File => New => Dynamic Web Project, and give Project name as ‘Info’ click Next

again Next Finish.

Connect MySQL to JSP with Eclipse Kepler

Add Library : MySQL Connector/j

Unzip mysql-connector-java-5.1 and select mysql-connector-java-5.1.23-bin.jar file and drag and drop into the Project explorer of the Eclipse(info => WebContent => WEB-INF => lib).

Start Coding

Right Click on Info from Project Explorer => New => HTML File (Give Name as index.html)

index.html

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

<!DOCTYPE html>

<html>

<head>

<meta charset="ISO-8859-1">

<title>Insert title here</title>

</head>

<body>

<h1>Add Info</h1>

<form action="add.jsp" method="post">

User name : <input type="text" name="username" /><br />

password : <input type="password" name="password" /><br />

First name :<input type="text" name="firstname" /><br />

Last name :<input type="text" name="lastname" /><br />

address :<textarea cols="10" rows="15" name="address">

</textarea><br />

Pin code <input type="text" name="pincode" /><br /> <br />

phone number <input type="text" name="phonenumber" /><br />

<input type="submit" value="Add Details" /><br />

</form>

</body>

</html>

Create a new JSP file : Right Click on Info from Project Explorer => New => JSP File (Give Name as add.jsp) Insert the codes between body tag of the document

add.jsp

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

<%@page import="java.sql.*" %>

<%@page import="java.io.*" %>

<%@page import="javax.sql.*" %>

<%@page import="java.sql.Connection" %>

<%

String username=request.getParameter("username");

String password=request.getParameter("password");

String firstname=request.getParameter("firstname");

String lastname=request.getParameter("lastname");

String address=request.getParameter("address");

String pincode=request.getParameter("pincode");

String phonenumber=request.getParameter("phonenumber");

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

Connection con=DriverManager.getConnection

("jdbc:mysql://localhost:3306/db","nikisurf","nikisurf");

Statement st=con.createStatement();

String sql="insert into info

(username,password,firstname,lastname,address,pincode,phonenumber)

values('"+username+"','"+password+"','"+firstname+"','"+lastname+"',

'"+address+"','"+pincode+"','"+phonenumber+"')";

int flag=st.executeUpdate(sql);

if(flag==1)

{

out.println("Added!");

}

else

{

out.println("Failed");

}

response.sendRedirect("show.html");

%>

Create delete.html

1

2

3

4

5

6

7

8

9

10

11

12

13

<!DOCTYPE html>

<html>

<head>

<meta charset="ISO-8859-1">

<title>Insert title here</title>

</head>

<body>

<form action="delete.jsp" method="post" >

Enter First name to delete:<input type="text" name="username" />

<input type="submit" value="delete" />

</form>

</body>

</html>

delete.jsp

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

<%@page import="java.sql.*" %>

<%@page import="java.io.*" %>

<%@page import="javax.sql.*" %>

<%@page import="java.sql.Connection" %>

<%

String username=request.getParameter("username");

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

Connection con=DriverManager.getConnection

("jdbc:mysql://localhost:3306/db",

"nikisurf","nikisurf");

Statement st=con.createStatement();

try

{

st.executeUpdate("DELETE FROM info WHERE username = '"+username+"'");

boolean flag=true;

if(flag==true)

{

out.println("Deleted");

}

else

{

out.println("Not possible : error!");

}

}

catch(Exception e)

{

out.println("Exception");

}

%>

show.html

1

2

3

4

<form action="show.jsp" method="post">

Enter User name

<input type="text" name="username" />

<input type="submit" value="show details" />

show.jsp

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

<%@page import="java.sql.*" %>

<%@page import="javax.sql.*" %>

<%@page import="java.sql.Connection" %>

<%

String username=request.getParameter("username");

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

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db","nikisurf","nikisurf");

Statement st=con.createStatement();

String query="select * from info where username='"+username+"'";

ResultSet rs=st.executeQuery(query);

%>

<%

while(rs.next())

{

%>

<table border="2" bordercolor="#2494b7">

<tr>

<th>UserName</th>

<th>Password</th>

<th>First Name</th>

<th>Last Name</th>

<th>Address</th>

<th>Pin code</th>

<th>Mobile Phone</th>

</tr>

<tr>

<td><%=rs.getString(1)%></td>

<td><%=rs.getString(2)%></td>

<td><%=rs.getString(3)%></td>

<td><%=rs.getString(4)%></td>

<td><%=rs.getString(5)%></td>

<td><%=rs.getString(6)%></td>

<td><%=rs.getString(7)%></td>

</tr>

</table>

<%

}

%>

Run JEE Eclipse Info Project

To run tomcat server with Eclipse : Right Click on Info from Project Explorer =>Run as => Run on Server