<%@ page session = "true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<%
String schema = "";
if(request.getParameter("country")!=null)
{
schema = request.getParameter("country").toString().trim();
}
else
{
schema = "india";
}
session.setAttribute("schema",schema);
%>
<head>
<script type="text/javascript">
function error()
{
if(document.form1.user_name.value == "")
{
alert("Username cannot be left blank");
document.form1.user_name.focus();
return false;
}
if(document.form1.password.value == "")
{
alert("Password cannot be left blank");
document.form1.password.focus();
return false;
}
}
function getFocus()
{
document.getElementById('user_name').focus();
}
</script>
<style type="text/css">
<!--
.style2 {
font-family: Georgia, "Times New Roman", Times, serif;
color: #000066;
}
-->
</style>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>DialuZ-Registration Login Page</title>
</head>
<body onload="getFocus()">
<form id="form1" name="form1" method="post" action="validate_tl.jsp" onsubmit = "return error()">
<br><br><br><br>
<table width="426" height="292" border="1" align="center" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#CCCCCC"><table width="396" height="270" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="200" height="139"><table width="170" height="30" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="166"><span class="style2">Please Login!</span></td>
</tr>
</table></td>
<td width="196" rowspan="2"><table width="200" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="39"><span class="style2">Username:</span></td>
<td><input type="text" name="user_name" id="user_name" /></td>
</tr>
<tr>
<td height="38"><span class="style2">Password:</span></td>
<td><input type="password" name="password" id="password" /></td>
</tr>
</table>
<table width="200" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="95"><div align="center">
<input type="submit" name="submit" id="submit" value="Submit" />
</div></td>
<td width="105"><div align="center">
<input type="reset" name="Reset" id="Reset" value="Reset" />
</div></td>
</tr>
</table></td>
</tr>
<tr>
<td> <img src="images/lock.jpg" width = "150" height = "150"/></td>
</tr>
</table></td>
</tr>
</table>
</form>
</html>
<%@ page import = "java.util.*" %>
<%@ page import = "tl_task.*" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<%
QueryProcessor qp = new QueryProcessor();
String user_tl ="";
String pass_tl="";
String schema1 = "";
if(session.getAttribute("schema")!=null)
{
schema1 = session.getAttribute("schema").toString();
System.out.println(schema1);
}
if(request.getParameter("user_name")!=null)
{
user_tl= request.getParameter("user_name").toString().trim();
}
if(request.getParameter("password")!=null)
{
pass_tl = request.getParameter("password").toString().trim();
}
session.setAttribute("schema",schema1);
qp.setSchema(schema1);
String type="";
String query = "Select SQL_CALC_FOUND_ROWS * from login_tl where tl_username ='"+user_tl+"' and tl_pass = '"+pass_tl+"';";
int rec=0;
int count = 0;
int status_login=0;
count=qp.verify(query);
query = "Select user_type,reallocate_dp_status from login_tl where tl_username ='"+user_tl+"' and tl_pass = '"+pass_tl+"';";
type = qp.getType(query);
session.setAttribute("type",type);
if(count == 1 && type.equals("20"))
{
status_login=qp.setStatus(user_tl,"login");
session.setAttribute("user_tl",user_tl);
//System.out.println("user_tl in validate_tl : "+(String)session.getAttribute("user_tl"));
%>
<
jsp:forward page="tl_select_task.jsp"/>
<%}
else if(count == 1 && type.equals("10")){
status_login=qp.setStatus(user_tl,"login");
session.setAttribute("user_tl",user_tl);
%><jsp:forward page="gateway.jsp"/><%}else{%>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title></title>
</head>
<body >
<h3>Invalid UserName or Password</h3>
<%@include file = "tl_login.jsp" %>
</body>
<% }
%>
</html>
package tl_task;
import java.sql.*;
import java.sql.Date;
import java.util.*;
public class QueryProcessor {
Connection con;
Statement stmt;
ResultSet res ,res1;
PreparedStatement pstmt;
//String url = "jdbc:mysql://192.168.1.56:3306/";
String url = "jdbc:mysql://localhost:3306/";
//String url = "jdbc:mysql://localhost:3307/";
public void setSchema(String schema)
{
url = url+schema;
}
public void connect()
{
//String user_name = "mrinal.s";
String pass = "dialuz";
String user_name = "root";
//String pass = "Smr1n@lr0";
//String url = "jdbc:mysql://192.168.1.2:3307/praveen";
//String url = "jdbc:mysql://192.168.1.55:3306/praveen";
System.out.println("url for database is"+url);
String dbclass = "com.mysql.jdbc.Driver";
try
{
Class.forName(dbclass);
}
catch(ClassNotFoundException cnfe)
{
System.out.println("Class Not Found");
cnfe.printStackTrace();
}
catch(Exception e)
{
System.out.println("Other Exception Occured");
}
try
{
con = DriverManager.getConnection(url,user_name,pass);
}
catch(SQLException e)
{
System.out.println("Not Able To Connect to Database Queryprocessor()");
}
stmt=null;
}
public int verify(String verify_query)
{
int verify=0;
connect();
try{
stmt=con.createStatement();
stmt.executeQuery(verify_query);
String count="SELECT FOUND_ROWS() as cnt";
pstmt=con.prepareStatement(count);
res=pstmt.executeQuery();
if(res.next())
{
verify=res.getInt("cnt");
}
}
catch(Exception e){}
finally{
try{
stmt.close();
res.close();
pstmt.close();
con.close();
}
catch(Exception e){}
}
return verify;
}
public synchronized String getType(String query)
{
System.out.println("rajendar"+query);
String type="";
connect();
try
{
stmt = con.createStatement();
res = stmt.executeQuery(query);
while(res.next())
{
type = res.getString(1);
}
}
catch(SQLException se)
{
System.out.println("Caught in getType");
}
disconnect();
return type;
}
public synchronized ArrayList<String> Get_user(String user_get_query)throws SQLException
{
ArrayList<String> user_get_al = new ArrayList<String>();
//Iterator it = user_get_al.iterator();
//int i=0;
connect();
try{
stmt=con.createStatement(ResultSet.CLOSE_CURSORS_AT_COMMIT,ResultSet.TYPE_SCROLL_INSENSITIVE);
res1=stmt.executeQuery(user_get_query);
while(res1.next()){
user_get_al.add(""+res1.getString("username").toString());
}
}
catch(SQLException se){
}
res1.close();
stmt.close();
con.close();
return user_get_al;
}
public synchronized ArrayList<String> get_Category(String cat_query,int i)throws SQLException
{
ArrayList<String> cat_list = new ArrayList<String>();
ArrayList<String> subcat_list = new ArrayList<String>();
ArrayList<String> subcat1_list = new ArrayList<String>();
connect();
try{
stmt=con.createStatement();
res1=stmt.executeQuery(cat_query);
while(res1.next()){
cat_list.add(""+res1.getString(2).toString());
subcat_list.add(""+res1.getString(3).toString());
subcat1_list.add(""+res1.getString(4).toString());
}
res1.close();
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Exception caught in getcategory");
res1.close();
stmt.close();
con.close();
}
if(i==1)
{
return cat_list;
}
else if(i==2)
{
return subcat_list;
}
else if(i==3)
{
return subcat1_list;
}
return null;
}
public synchronized int modifyCategory(String cat_name,String query,String rec_cat,String rec_subcat,String rec_subcat1,String action,String user_name,String date)
{
connect();
int row=0;
int cat_id=0;
int flag =0;
try
{
stmt = con.createStatement();
String query1 = "select * from local_catlist where rec_cat='"+cat_name+"'";
String cat_name1 = "";
String subcat_name1 = "";
String subcat1_name1 = "";
//String status =0;
stmt = con.createStatement();
res = stmt.executeQuery(query1);
if(res.next())
{
cat_name1 = res.getString("rec_cat");
subcat_name1 = res.getString("rec_subcat");
subcat1_name1 = res.getString("rec_subcat1");
row++;
}
if(row!=0)
{
pstmt = con.prepareStatement("select * from dump_cat_area_list_tl where rec_cat = '"+rec_cat+"' and status = 1 and action = 'Modify'");
res=pstmt.executeQuery();
if(res.next())
{
cat_id = res.getInt(1);
flag = 1;
}
query1 = "insert into dump_cat_area_list_tl(rec_cat,rec_subcat,rec_subcat1,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?,?)";
pstmt = con.prepareStatement(query1);
pstmt.setString(1, cat_name1);
pstmt.setString(2, subcat_name1);
pstmt.setString(3, subcat1_name1);
pstmt.setString(4, user_name);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.setString(6, action);
pstmt.setString(7,"0");
pstmt.executeUpdate();
stmt.executeUpdate(query);
row=0;
if(flag==1)
{
query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+cat_id+"'";
stmt.executeUpdate(query1);
//stmt.executeUpdate(delete_query);
}
}
res.close();
if(pstmt!=null)
pstmt.close();
if(stmt!=null)
stmt.close();
if(con!=null)
con.close();
}
catch(SQLException se)
{
System.out.println("Error in modify Category");
se.printStackTrace();
}
return row;
}
public synchronized int insertCategory(String cat_name,String query,String rec_cat,String rec_subcat,String rec_subcat1,String action,String user_name,String date)
{
connect();
int row=0;
int cat_id=0;
int flag =0;
try
{
stmt = con.createStatement();
int rec_row=0;
res = stmt.executeQuery("select rec_cat from local_catlist where rec_cat='"+rec_cat+"'");
while(res.next())
{
row++;
}
if(row==0)
{
pstmt = con.prepareStatement(query);
pstmt.setString(1,rec_cat);
pstmt.setString(2,rec_subcat);
pstmt.setString(3,rec_subcat1);
pstmt.setString(4,user_name);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.executeUpdate();
pstmt = con.prepareStatement("select * from dump_cat_area_list_tl where rec_cat = '"+rec_cat+"' and status = 1 and action = 'Insert'");
res=pstmt.executeQuery();
if(res.next())
{
cat_id = res.getInt(1);
flag = 1;
}
String query1 = "insert into dump_cat_area_list_tl(rec_cat,rec_subcat,rec_subcat1,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?,?)";
pstmt = con.prepareStatement(query1);
pstmt.setString(1, rec_cat);
pstmt.setString(2, rec_cat);
pstmt.setString(3, rec_subcat1);
pstmt.setString(4, user_name);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.setString(6, action);
pstmt.setString(7,"0");
pstmt.executeUpdate();
if(flag==1)
{
query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+cat_id+"'";
stmt.executeUpdate(query1);
//stmt.executeUpdate(delete_query);
}
}
res.close();
if(pstmt!=null)
pstmt.close();
if(stmt!=null)
stmt.close();
if(con!=null)
con.close();
}
catch(SQLException se)
{
System.out.println("Error in insert Category");
se.printStackTrace();
}
return row;
}
public synchronized int setCategory(String cat_name,String query,String rec_cat,String rec_subcat,String rec_subcat1,String action,String user_name,String date,String rec_id)
{
connect();
int row=0;
int cat_id=0;
try
{
stmt = con.createStatement();
//System.out.println(row);
if(action.equalsIgnoreCase("insert"))
{
int rec_row=0;
res = stmt.executeQuery("select rec_cat from local_catlist where rec_cat='"+rec_cat+"'");
while(res.next())
{
row++;
}
if(row==0)
{
res = stmt.executeQuery("select cat_id from local_catlist");
while(res.next())
{
cat_id = (Integer)res.getInt(1);
}
cat_id = cat_id+1;
pstmt = con.prepareStatement(query);
pstmt.setString(1,rec_cat);
pstmt.setString(2,rec_subcat);
pstmt.setString(3,rec_subcat1);
pstmt.setString(4,user_name);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.executeUpdate();
String query1 = "select * from dump_cat_area_list_tl where cat_id='"+rec_id+"'";
res = stmt.executeQuery(query1);
while(res.next())
{
rec_row++;
}
if(rec_row>=1){
query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+rec_id+"'";
stmt.executeUpdate(query1);
}
}
else
{
return row;
}
}
if(action.equalsIgnoreCase("modify"))
{
String query1 = "select * from local_catlist where rec_cat='"+cat_name+"'";
String cat_name1 = "";
String subcat_name1 = "";
String subcat1_name1 = "";
int rec_row=0;
//String status =0;
stmt = con.createStatement();
res = stmt.executeQuery(query1);
while(res.next())
{
cat_name1 = res.getString("rec_cat");
subcat_name1 = res.getString("rec_subcat");
subcat1_name1 = res.getString("rec_subcat1");
}
query1 = "insert into dump_cat_area_list_tl(rec_cat,rec_subcat,rec_subcat1,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?,?)";
pstmt = con.prepareStatement(query1);
pstmt.setString(1, cat_name1);
pstmt.setString(2, subcat_name1);
pstmt.setString(3, subcat1_name1);
pstmt.setString(4, user_name);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.setString(6, action);
pstmt.setString(7,"0");
pstmt.executeUpdate();
query1 = "select * from dump_cat_area_list_tl where cat_id='"+rec_id+"'";
res = stmt.executeQuery(query1);
while(res.next())
{
rec_row++;
}
if(rec_row>=1){
query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+rec_id+"'";
stmt.executeUpdate(query1);
}
row=0;
//System.out.println("in modify"+cat_name);
//System.out.println("in modify"+rec_cat);
if(cat_name.equals(rec_cat))
{
//System.out.println("in if");
stmt.executeUpdate(query);
}
else {
res = stmt.executeQuery("select rec_cat from local_catlist where rec_cat='"+rec_cat+"'");
//System.out.println(cat_name);
while(res.next())
{
row++;
//System.out.println("in while"+row);
}
//System.out.println("after while"+row);
if(row==0){
stmt.executeUpdate(query);
}
else
{
//System.out.println("in else"+row);
//disconnect();
return row;
}
}
}
if(res!=null)
{
res.close();
}
if(stmt!=null)
{
stmt.close();
}
if(pstmt!=null)
{
pstmt.close();
}
if(con!=null)
{
con.close();
}
}
catch(SQLException se)
{
System.out.println("Caught in setCategory");
se.printStackTrace();
}
//disconnect();
return row;
}
public synchronized int removeCategory(String delete_query,String rec_cat,String user_name_tl,String date,String rec_id){
connect();
int rows=0;
int cat_id=0;
String rec_subcat="";
String rec_subcat1="";
int rec_row=0;
stmt=null;
res=null;
try
{
stmt = con.createStatement();
res = stmt.executeQuery("select * from local_catlist where rec_cat='"+rec_cat+"'");
while(res.next())
{
cat_id = res.getInt("cat_id");
rec_subcat = res.getString("rec_subcat");
rec_subcat1=res.getString("rec_subcat1");
rows++;
}
if(rows!=0){
pstmt = con.prepareStatement("select cat_id from dump_cat_area_list_tl where cat_id='"+cat_id+"'");
res=pstmt.executeQuery();
if(!(res.next())){
pstmt= con.prepareStatement("insert into dump_cat_area_list_tl(rec_cat,rec_subcat,rec_subcat1,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?,?)");
//pstmt.setInt(1,cat_id);
pstmt.setString(1,rec_cat);
pstmt.setString(2,rec_subcat);
pstmt.setString(3,rec_subcat1);
pstmt.setString(4,user_name_tl);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.setString(6,"Delete");
pstmt.setString(7, "0");
pstmt.executeUpdate();
stmt.executeUpdate(delete_query);
rows=0;
}
String query1 = "select * from dump_cat_area_list_tl where cat_id='"+rec_id+"'";
res = stmt.executeQuery(query1);
while(res.next())
{
rec_row++;
}
if(rec_row>=1){
query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+rec_id+"'";
stmt.executeUpdate(query1);
}
}
res.close();
if(pstmt!=null)
{
pstmt.close();
}
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught In Remove Category");
se.printStackTrace();
}
//disconnect();
return rows;
}
public synchronized int deleteCategory(String delete_query,String rec_cat,String user_name_tl,String date){
connect();
int rows=0;
int cat_id=0;
int flag = 0;
String rec_subcat="";
String rec_subcat1="";
int rec_row=0;
stmt=null;
res=null;
pstmt = null;
try
{
stmt = con.createStatement();
res = stmt.executeQuery("select * from local_catlist where rec_cat='"+rec_cat+"'");
if(res.next())
{
rec_subcat = res.getString("rec_subcat");
rec_subcat1=res.getString("rec_subcat1");
rows++;
}
if(rows!=0){
pstmt = con.prepareStatement("select * from dump_cat_area_list_tl where rec_cat = '"+rec_cat+"' and status = 1 and action = 'Delete'");
res=pstmt.executeQuery();
if(res.next())
{
cat_id = res.getInt(1);
flag = 1;
}
pstmt= con.prepareStatement("insert into dump_cat_area_list_tl(rec_cat,rec_subcat,rec_subcat1,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?,?)");
//pstmt.setInt(1,cat_id);
pstmt.setString(1,rec_cat);
pstmt.setString(2,rec_subcat);
pstmt.setString(3,rec_subcat1);
pstmt.setString(4,user_name_tl);
pstmt.setDate(5,Date.valueOf(date.toString()));
pstmt.setString(6,"Delete");
pstmt.setString(7, "0");
pstmt.executeUpdate();
stmt.executeUpdate(delete_query);
rows=0;
}
if(flag==1)
{
String query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+cat_id+"'";
stmt.executeUpdate(query1);
//stmt.executeUpdate(delete_query);
rows=0;
}
res.close();
if(pstmt!=null){
pstmt.close();
}
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught In Remove Category");
se.printStackTrace();
}
//disconnect();
return rows;
}
public synchronized int getRecords(String query_user)
{
int row=0;
connect();
stmt = null;
res = null;
try
{
stmt = con.createStatement();
res = stmt.executeQuery(query_user);
/*String count="SELECT FOUND_ROWS() as cnt";
pstmt=con.prepareStatement(count);
res=pstmt.executeQuery();*/
while(res.next())
{
row++;
}
if(res!=null)
{
res.close();
}
if(stmt!=null)
{
stmt.close();
}
if(con!=null)
{
con.close();
}
}
catch(SQLException se)
{
System.out.println("Caught in GetRecords");
se.printStackTrace();
}
//disconnect();
return row;
}
public synchronized int setStatus(String user_tl,String status)
{ connect();
int row=0;
String query="";
int counter=0;
int type=0;
res=null;
try
{
stmt = con.createStatement();
query = "select reallocate_dp_status,user_type from login_tl where tl_username='"+user_tl+"'";
res = stmt.executeQuery(query);
if(res.next())
{
counter=Integer.parseInt(res.getString(1).toString());
type = Integer.parseInt(res.getString(2).toString());
}
if(status.equals("login")){
query="select * from login_tl where tl_login_status='1' and tl_username='"+user_tl+"'";
//System.out.println(query);
res=stmt.executeQuery(query);
if(res.next())
{
row++;
return row;
}
else
{
if(type==10)
{
query="update login_tl set tl_login_status='1' where tl_username='"+user_tl+"'";
}
else
{
query="update login_tl set tl_login_status='1',allocate_dp_status='"+counter+"',reallocate_dp_status='0' where tl_username='"+user_tl+"'";
}
}
}
else
{
query="update login_tl set tl_login_status='0' where tl_username='"+user_tl+"'";
}
row=stmt.executeUpdate(query);
if(res!=null)
{
res.close();
}
if(stmt!=null)
{
stmt.close();
}
if(con!=null)
{
con.close();
}
}
catch(SQLException se)
{
System.out.println("Caught In Set Status");
se.printStackTrace();
}
System.out.println("status"+row);
//disconnect();
return row;
}
public void disconnect()
{
try
{
res.close();
pstmt.close();
stmt.close();
con.close();
}
catch(Exception e)
{
System.out.println("Caught in Closing Connection");
}
}
public synchronized ArrayList getTlData(String query)
{
ArrayList get_data = new ArrayList();
connect();
//get_data=null;
try
{
stmt = con.createStatement();
res = stmt.executeQuery(query);
if(res.next())
{
get_data.add(res.getString(1));
get_data.add(res.getInt(2));
}
}
catch(SQLException se)
{
System.out.println("Caught in getTlData");
se.printStackTrace();
}
try
{
res.close();
stmt.close();
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
return get_data;
}
public synchronized void setTlStatus(String query)
{
connect();
try
{
stmt = con.createStatement();
stmt.executeUpdate(query);
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in setTlStatus");
}
//disconnect();
}
public synchronized void setDpData(ArrayList dp_data,String query)
{
connect();
try
{
pstmt = con.prepareStatement(query);
pstmt.setString(1,dp_data.get(0).toString());
pstmt.setString(2,dp_data.get(1).toString());
pstmt.setString(4,dp_data.get(3).toString());
pstmt.setString(5,dp_data.get(4).toString());
pstmt.setDate(3,Date.valueOf(dp_data.get(2).toString()));
pstmt.setString(6, dp_data.get(5).toString());
pstmt.setString(7, "1");
pstmt.executeUpdate();
pstmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in setTlStatus");
se.printStackTrace();
}
//disconnect();
}
public synchronized void setDpDataCategory(ArrayList dp_data,String query)
{
connect();
try
{
pstmt = con.prepareStatement(query);
pstmt.setString(1,dp_data.get(0).toString());
pstmt.setString(2,dp_data.get(1).toString());
pstmt.setString(4,dp_data.get(3).toString());
pstmt.setString(5,dp_data.get(4).toString());
pstmt.setDate(3,Date.valueOf(dp_data.get(2).toString()));
pstmt.setString(6, dp_data.get(5).toString());
pstmt.setString(7, dp_data.get(6).toString());
pstmt.setString(8, "1");
pstmt.executeUpdate();
pstmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("setDpDataCategory");
se.printStackTrace();
}
//disconnect();
}
public int getRequest(String query_area_request)
{
int row = 0;
connect();
try
{
stmt=con.createStatement();
res=stmt.executeQuery(query_area_request);
while(res.next())
{
row++;
}
res.close();
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in getRequest");
}
//disconnect();
return row;
}
public synchronized ArrayList getData(String query)
{
ArrayList get_data = new ArrayList();
connect();
try
{
stmt = con.createStatement();
res = stmt.executeQuery(query);
if(res.next())
{
get_data.add(res.getString(1));
}
res.close();
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in getTlData");
se.printStackTrace();
}
//disconnect();
return get_data;
}
public synchronized void setDeleteCategory(ArrayList dp_data, String query)
{
connect();
try
{
pstmt = con.prepareStatement(query);
pstmt.setString(1,dp_data.get(0).toString());
pstmt.setString(2,dp_data.get(1).toString());
pstmt.setString(4,dp_data.get(3).toString());
pstmt.setString(5,dp_data.get(4).toString());
pstmt.setDate(3,Date.valueOf(dp_data.get(2).toString()));
pstmt.setString(6,"1");
pstmt.executeUpdate();
pstmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("setDpDataCategory");
se.printStackTrace();
}
}
public synchronized ResultSet getRequestData(String query)
{
ArrayList request_data = new ArrayList();
connect();
try
{
stmt = con.createStatement();
res=stmt.executeQuery(query);
// while(res.next())
// {
//
// for(int i=1;i<9;i++)
// {
//
//
// request_data.add(res.getString(i));
//
//
// }
// }
}
catch(SQLException se)
{
System.out.println("Caught in get Request Data");
se.printStackTrace();
}
//disconnect();
return res;
}
public synchronized void transferCounter(String query,String name)
{
connect();
int counter=0;
try
{
stmt = con.createStatement();
res = stmt.executeQuery(query);
if(res.next())
{
counter = Integer.parseInt(res.getString(1).toString());
}
stmt.executeUpdate("update login_tl set reallocate_dp_status='"+counter+"',allocate_dp_status='0' where tl_username='"+name+"'");
//disconnect();
res.close();
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in transferCounter");
se.printStackTrace();
}
}
public synchronized void deleteRecord(String query)
{
connect();
try
{
stmt = con.createStatement();
stmt.executeUpdate(query);
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in deleteRecord");
se.printStackTrace();
}
}
public synchronized int setArea(String city_name,String query,String rec_city,String final_area,String action,String user_name,String date,String area)
{
connect();
int row=0;
int cat_id=0;
int flag =0;
res = null;
pstmt = null;
try{
if(action.equalsIgnoreCase("modify") || action.equalsIgnoreCase("insert") || action.equalsIgnoreCase("delete"))
{
String query1 = "select * from local_city_list where city_name='"+city_name+"'";
String city_name1 = "";
String area_name1 = "";
String[] area_name = {};
int rec_row=0;
//String status =0;
stmt = con.createStatement();
res = stmt.executeQuery(query1);
if(res.next())
{
city_name1 = res.getString("city_name");
area_name1 = res.getString("city_areas");
}
area_name1 = area_name1.replaceAll("'","`");
stmt.executeUpdate(query);
query1 = "insert into dump_cat_area_list_tl(rec_tl_city,rec_tl_area,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?)";
pstmt = con.prepareStatement(query1);
pstmt.setString(1, city_name1);
pstmt.setString(2, area_name1);
pstmt.setString(3, user_name);
pstmt.setDate(4,Date.valueOf(date.toString()));
pstmt.setString(5, action);
pstmt.setString(6,"0");
pstmt.executeUpdate();
query1 = "select * from dump_cat_area_list_tl where rec_tl_city = '"+city_name+"' and action = '"+action+"' and status = '1' ";
res = stmt.executeQuery(query1);
if(res.next())
{
cat_id = res.getInt(1);
flag = 1;
rec_row++;
}
if(rec_row>=1){
query1 = "update dump_cat_area_list_tl set status = '0' where cat_id='"+cat_id+"'";
stmt.executeUpdate(query1);
}
}
if(res!=null)
{
res.close();
}
if(stmt!=null)
{
stmt.close();
}
if(pstmt!=null)
{
pstmt.close();
}
if(con!=null)
{
con.close();
}
}
catch(SQLException se)
{
System.out.println("Caught in setArea");
se.printStackTrace();
}
//disconnect();
return row;
}
public void onlyQuery(String query)
{
connect();
try
{
stmt=con.createStatement();
stmt.executeUpdate(query);
stmt.close();
con.close();
//disconnect();
}
catch(SQLException se)
{
System.out.println("onlyQuery");
}
}
public synchronized int setTeleCode(String city_name,String query,String rec_city,String final_area,String action,String user_name,String date)
{
connect();
int row=0;
try{
if(action.equalsIgnoreCase("modify") || action.equalsIgnoreCase("insert") || action.equalsIgnoreCase("delete"))
{
String query1 = "select * from local_city_list where city_name='"+city_name+"'";
String city_name1 = "";
String tele_code1 = "";
//String status =0;
stmt = con.createStatement();
res = stmt.executeQuery(query1);
while(res.next())
{
city_name1 = res.getString("city_name");
tele_code1 = res.getString("tele_code");
}
stmt.executeUpdate(query);
query1 = "insert into dump_cat_area_list_tl(rec_tl_city,rec_tl_tele_code,rec_tl_username,rec_tl_date,action,status) values(?,?,?,?,?,?)";
pstmt = con.prepareStatement(query1);
pstmt.setString(1, city_name1);
pstmt.setString(2, tele_code1);
pstmt.setString(3, user_name);
pstmt.setDate(4,Date.valueOf(date.toString()));
pstmt.setString(5, action);
pstmt.setString(6,"0");
pstmt.executeUpdate();
}
pstmt.close();
stmt.close();
con.close();
}
catch(SQLException se)
{
System.out.println("Caught in setArea");
se.printStackTrace();
}
//disconnect();
return row;
}
}
org.apache.jasper.JasperException: An exception occurred processing JSP page /validate_tl.jsp at line 39
36: count=qp.verify(query);
37: query = "Select user_type,reallocate_dp_status from login_tl where tl_username ='"+user_tl+"' and tl_pass = '"+pass_tl+"';";
38:
39: type = qp.getType(query);
40: session.setAttribute("type",type);
41:
42: if(count == 1 && type.equals("20"))
Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:521)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:430)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
root cause
java.lang.NullPointerException
tl_task.QueryProcessor.getType(QueryProcessor.java:87)
org.apache.jsp.validate_005ftl_jsp._jspService(validate_005ftl_jsp.java:97)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:388)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
india
jdbc:mysql://localhost:3306/india
Not Able To Connect to Database
jdbc:mysql://localhost:3306/india
Not Able To Connect to Database
May 29, 2012 6:35:23 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for
servlet jsp threw exception
java.lang.NullPointerException
at tl_task.QueryProcessor.getType(QueryProcessor.java:87)
at org.apache.jsp.validate_005ftl_jsp._jspService(validate_005ftl_jsp.java:97)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:388)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:602)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Unknown Source)