Insert,fetch,update&delete operation through JDBC & struts2.0 framework

Core java OOPs Tutorial

Core Java Interview Materials

This application provide example to perform insert,fetch,update and delete operation in JDBC and struts 2.0 connectivity .

Require tools to run this application1. Struts2.0 jar file
2. Tomcat server
3. Database Oracle10g

Directory Structure of JDBC All Query Example in Struts 2.0 Using MyEclipse IDE



index.jsp

<html>
<head></head>
<body><center>
<table cellpadding="0" cellspacing="10" bordercolor="red" border="5">
<tr><td align="center" height="30" width="250">
<a href="insert.jsp" style="color: gray;text-decoration: none">Data Insert</a>
</td></tr>
<tr><td align="center" height="30" width="250">
<a href="fetch.jsp" style="color: gray;text-decoration: none">Data Fetch</a>
</td></tr>
<tr><td align="center" height="30" width="250">
<a href="nameUpdate.jsp" style="color: gray;text-decoration: none">Update Data</a>
</td></tr>
<tr><td align="center" height="30" width="250">
<a href="deletedata.jsp" style="color: gray;text-decoration: none">Delete Row Wise Data</a>
</td></tr>
</table></center>
</body>
</html>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" 
	xmlns="http://java.sun.com/xml/ns/javaee" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
	http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
  <welcome-file-list>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  <filter>
  <filter-name>struts2</filter-name>
  <filter-class>
  org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter
  </filter-class>
  </filter>
  <filter-mapping>
  <filter-name>struts2</filter-name>
  <url-pattern>/*</url-pattern>
  </filter-mapping></web-app>

struts.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 
2.1//EN" "http://struts.apache.org/dtds/struts-2.1.dtd">
<struts>
<!--Insert Data in database-->
<package name="demo" extends="struts-default">
<action name="insert" class="org.r4r.InsertAction">
<result name="success">/successInsert.jsp</result>
<result name="error">/insert.jsp</result>
</action>
<!--All data fetch on the browser in tabular form-->
<action name="alldatafetch" class="org.r4r.FetchAction">
<result name="success">/alldatafetch.jsp</result>
</action>
<!--Name wise data fetch on the browser in tabular form-->
<action name="nameFetch" class="org.r4r.FetchAction" method="nameFetch">
<result name="success">/alldatafetch.jsp</result>
</action>
<!--Name wise update data in the database table-->
<action name="fetch" class="org.r4r.FetchAction" method="nameFetch">
<result name="success">/updateFetch.jsp</result>
</action>
<action name="update" class="org.r4r.FetchAction" method="update">
<result name="success">/index.jsp</result>
<result name="input">/fetch.jsp</result>
<result name="error">/fetch.jsp</result>
</action>
<!--Row wise delete data in the database table-->
<action name="rowdatadelete" class="org.r4r.FetchAction">
<result name="success">/deletedatafetch.jsp</result>
</action>
<action name="delete" class="org.r4r.FetchAction" method="delete">
<result name="success" type="chain">rowdatadelete</result>
</action>
<!--Name wise delete data in the database table-->
<action name="nameDelete" class="org.r4r.FetchAction" method="nameFetch">
<result name="success">/nameDeleteFetch.jsp</result>
</action>
<action name="delete1" class="org.r4r.FetchAction" method="delete">
<result name="success">/nameDelete.jsp</result>
</action>
</package>
</struts>

InsertAction.java

package org.r4r;
public class InsertAction {
	String name;
	String address;
	String city;
	String state;
	DAO dao=new DAO();
	public String execute(){
		if(dao.insert(getName(), getAddress(), getCity(), getState()))
		return "success";
		else
		return "error";
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}
	
}

FetchAction.java

package org.r4r;

import java.util.List;

public class FetchAction {
	private String name;
	private String address;
	private String city;
	private String state;
	private UserList userlist;
	private List<UserList> userlistlist;
	DAO dao=new DAO();
	
	public String execute(){
		userlistlist=dao.fetch();
		return "success";
		
	}
	public String nameFetch(){
		userlistlist=dao.nfetch(getName());
		return "success";
	}
	public String update(){
		dao.update(getName(),getAddress(), getCity(), getState());
		return "success";		
	}
	public String delete(){
		dao.delete(getName());
		return "success";
	}
	public UserList getUserlist() {
		return userlist;
	}
	public void setUserlist(UserList userlist) {
		this.userlist = userlist;
	}
	public List<UserList> getUserlistlist() {
		return userlistlist;
	}
	public void setUserlistlist(List<UserList> userlistlist) {
		this.userlistlist = userlistlist;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}
}

 UserList.java

package org.r4r;

public class UserList {
	String name;
	String address;
	String city;
	String state;	
	public UserList() {
	super();
	// TODO Auto-generated constructor stub
	}
	public UserList(String name, String address, String city, String state) {
		super();
		this.name = name;
		this.address = address;
		this.city = city;
		this.state = state;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}

}

DAO.java

package org.r4r;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DAO {
	public boolean insert(String name,String address,String city,String state){
	try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
	PreparedStatement stmt=con.prepareStatement("insert into userlist values(?,?,?,?)");
	stmt.setString(1,name);
	stmt.setString(2,address);
	stmt.setString(3,city);
	stmt.setString(4,state);
	stmt.executeQuery();
	return true;
	}catch(Exception e){
	System.out.println(e);
	}
	return false;
	}
	public List<UserList> fetch(){
	try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
	PreparedStatement stmt=con.prepareStatement("select * from userlist");
	ResultSet rset=stmt.executeQuery();
	UserList userlist;
	List<UserList> list=new ArrayList<UserList>();
	while(rset.next()){
	userlist=new UserList();
	userlist.setName(rset.getString(1));
	userlist.setAddress(rset.getString(2));
	userlist.setCity(rset.getString(3));
	userlist.setState(rset.getString(4));
	list.add(userlist);
	}
	return list;
	}catch(Exception e){
	System.out.println(e);
	}
	return null;
	}
	public List<UserList> nfetch(String name){
	try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
	PreparedStatement stmt=con.prepareStatement("select * from userlist where name=?");
	stmt.setString(1,name);
	ResultSet rset=stmt.executeQuery();
	UserList userlist;
	List<UserList> list=new ArrayList<UserList>();
	while(rset.next()){
	userlist=new UserList();
	userlist.setName(rset.getString(1));
	userlist.setAddress(rset.getString(2));
	userlist.setCity(rset.getString(3));
	userlist.setState(rset.getString(4));
	list.add(userlist);
	}
	return list;
	}catch(Exception e){
	System.out.println(e);
	}
	return null;
	}
	public boolean update(String name,String address,String city,String state){
	try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
	PreparedStatement stmt=con.prepareStatement("update userlist set name=?,address=?,city=?,state=? where name=?");
	stmt.setString(1,name);
	stmt.setString(2,address);
	stmt.setString(3,city);
	stmt.setString(4,state);
	stmt.setString(5,name);
	stmt.executeQuery();
	return true;
	}catch(Exception e){
	System.out.println(e);
	}
	return false;
	}
	public boolean delete(String name){
	try{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
	PreparedStatement stmt=con.prepareStatement("delete from userlist where name=?");
	stmt.setString(1,name);
	ResultSet rset=stmt.executeQuery();
	if(rset.next())
	return true;
	else
	return false;
	}catch(Exception e){
	System.out.println(e);
	}
	return false;
	}

}

insert.jsp

<%@taglib uri="/struts-tags" prefix="s"%>

<s:form action="insert">
<s:textfield name="name" label="name"/>
<s:textfield name="address" label="Address"/>
<s:textfield name="city" label="City"/>
<s:textfield name="state" label="State"/>
<s:submit value="Insert Data"/>
</s:form>
<br/><br/><br/>
<a href="index.jsp">Back</a>

successInsert.jsp

<b>You are successfully insert data</b><br/>
<b>Again insert,<a href="insert.jsp">Click Me</a></b>

fetch.jsp

<html>
<head></head>
<body><center>
<table cellpadding="0" cellspacing="10" bordercolor="red" border="5">
<tr><td align="center" height="30" width="250">
<a href="alldatafetch" style="color: gray;text-decoration: none">All Data Fetch</a>
</td></tr>
<tr><td align="center" height="30" width="250">
<a href="nameFetch.jsp" style="color: gray;text-decoration: none">Name Wise Fetch</a>
</td></tr>
</table>
<br/><br/>
<a href="index.jsp" style="font-size: 18px;">Back</a>
</center>
</body>
</html>

alldatafetch.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<html>
<head></head>
<body><center>
<table cellpadding="0" cellspacing="2" bordercolor="red" border="5">
<tr>
<th align="center" height="30" width="250">Name</th>
<th align="center" height="30" width="250">Address</th>
<th align="center" height="30" width="250">City</th>
<th align="center" height="30" width="250">State</th>
</tr>
<s:iterator value="userlistlist" var="userlistS">
<tr>
<td align="center" height="30" width="250"><s:property value="name"/></td>
<td align="center" height="30" width="250"><s:property value="address"/></td>
<td align="center" height="30" width="250"><s:property value="city"/></td>
<td align="center" height="30" width="250"><s:property value="state"/></td>
</tr>
</s:iterator>
</table>
<br/><br/>
<a href="fetch.jsp" style="font-size: 18px;">Back</a>
</center>
</body>
</html>

nameFetch.jsp

<%@taglib uri="/struts-tags" prefix="s"%>

<s:form action="nameFetch">
<s:textfield name="name" label="Name"/>
<s:submit value="Submit"/>
</s:form>

updateFetch.jsp

<%@taglib uri="/struts-tags" prefix="s"%>

<s:form action="update">
<s:iterator value="userlistlist" var="userlist">
<s:textfield name="name" label="name"/>
<s:textfield name="address" label="Address"/>
<s:textfield name="city" label="City"/>
<s:textfield name="state" label="State"/>
</s:iterator>
<s:submit value="Insert Data"/>
</s:form>

<br/><br/><br/>
<a href="nameUpdate.jsp">Back</a>

nameUpdate.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<s:actionerror/>
<s:form action="fetch">
<s:textfield name="name" label="Name"/>
<s:submit value="Submit"/>
</s:form>
<br/><br/><br/>
<a href="index.jsp">Back</a>

deleteData.jsp

<html>
<head></head>
<body><center>
<table cellpadding="0" cellspacing="10" bordercolor="red" border="5">
<tr><td align="center" height="30" width="250">
<a href="rowdatadelete" style="color: gray;text-decoration: none">All data Fetch to delete</a>
</td></tr>
<tr><td align="center" height="30" width="250">
<a href="nameDelete.jsp" style="color: gray;text-decoration: none">Name wise data fetch to delete</a>
</td></tr>
</table>
<br/><br/>
<a href="index.jsp" style="font-size: 18px;">Back</a>
</center>
</body>
</html>

deletedatafetch.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<html>
<head></head>
<body><center>
<table cellpadding="0" cellspacing="2" bordercolor="red" border="5">
<tr>
<th align="center" height="30" width="250">Name</th>
<th align="center" height="30" width="250">Address</th>
<th align="center" height="30" width="250">City</th>
<th align="center" height="30" width="250">State</th>
<th align="center" height="30" width="250">Delete</th>
</tr>
<s:iterator value="userlistlist" var="userlistS">
<tr>
<td align="center" height="30" width="250"><s:property value="name"/></td>
<td align="center" height="30" width="250"><s:property value="address"/></td>
<td align="center" height="30" width="250"><s:property value="city"/></td>
<td align="center" height="30" width="250"><s:property value="state"/></td>
<td align="center" height="30" width="250"><a href="delete?name=<s:property value='name'/>">Delete</a></td>
</tr>
</s:iterator>
</table>
<br/><br/>
<a href="deletedata.jsp" style="font-size: 18px;">Back</a>
</center>
</body>
</html>

nameDelete.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<s:actionerror/>
<s:form action="nameDelete">
<s:textfield name="name" label="Name"/>
<s:submit value="Submit"/>
</s:form>
<br/><br/><br/>
<a href="index.jsp">Back</a>

nameDeleteFetch.jsp

<%@taglib uri="/struts-tags" prefix="s"%>
<html>
<head></head>
<body><center>
<table cellpadding="0" cellspacing="2" bordercolor="red" border="5">
<tr>
<th align="center" height="30" width="250">Name</th>
<th align="center" height="30" width="250">Address</th>
<th align="center" height="30" width="250">City</th>
<th align="center" height="30" width="250">State</th>
<th align="center" height="30" width="250">Delete</th>
</tr>
<s:iterator value="userlistlist" var="userlistS">
<tr>
<td align="center" height="30" width="250"><s:property value="name"/></td>
<td align="center" height="30" width="250"><s:property value="address"/></td>
<td align="center" height="30" width="250"><s:property value="city"/></td>
<td align="center" height="30" width="250"><s:property value="state"/></td>
<td align="center" height="30" width="250">
<a href="delete1?name=<s:property value='name'/>">Delete</a></td>
</tr>
</s:iterator>
</table>
<br/><br/>
<a href="deletedata.jsp" style="font-size: 18px;">Back</a>
</center>
</body>
</html>

Output




Struts 1.3 Tutorial and Example
Spring 2.5 Tutorial and Example
Spring 2.5 MVC Tutorial
Spring 2.5 MVC Example