select,insert,update/edit,delete
1/ select query only use executeQuery
2/insert,update/edit,delete use execute
or
insert,update/edit executeUpdate
*/only select query return List or single row (with include model/info)
**//Search : Search means select Query
**//set means equals To
**//variable declare meaning :
private String sqlSeq=""; // means initialize
String sqlSeq=""; // means initialize
private CallableStatement cs = null;
N.B:
String[] offer2java; // declare way
String[] offer2java = new String[5]; //initialize way , here 5 is length of array
Null means :in java and oracle ??
RND : Research and development
GIS :Geographical
****************************
****************************
1/ insert query:
saveQuery = "insert into institutes_computers(REG_NO,FULL_NAME,institute_id,data_type,entered_by,entered_timestamp) values ('"+reg_noA+"','"+police_nameA+"','" + institute_id + "','N','"+user_no+"',sysdate)";
2/ update query:
saveQuery = "update institutes set INSTITUTE_NAME = '"+name+"',DIVISION_ID='"+instituteBoardDivision+"' where institute_id='"+institute_id+"'";
3/update/edit query :
queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"',DESIGNATION_NAME = '"+teacherDesig+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
4/ delete query:
String queryDelete = "delete from tech_general_info where TECH_GENERAL_INFO_ID='"+priId+"'";
NULL POINT EXCEPTION HANDLE :
suppose I think check box name is hscBm THAT database coloumn contain length is 2.
Now If i Sent null value from jsp page then Then coloumn will be 4 digit (null), But dont show exception If insert query is -
hscBm==null?"":hscBm
saveQuery = "insert into institutes_computers(REG_NO,FULL_NAME,institute_id,data_type,entered_by,entered_timestamp) values ('"+reg_noA+"','"+hscBm==null?"":hscBm +"','" + institute_id + "','N','"+user_no+"',sysdate)";
Not:
saveQuery = "insert into institutes_computers(REG_NO,FULL_NAME,institute_id,data_type,entered_by,entered_timestamp) values ('"+reg_noA+"','"+hscBm+"','" + institute_id + "','N','"+user_no+"',sysdate)";
database STATUS coloumn name is number or varchar then select query is -
//String query = "select * from kyamc_user_type where USER_ID='" + myName + "' and USER_PASS='" + myPassword + "' and STATUS=1"; // if database STATUS coloumn name is number then no cottation
String query = "select * from kyamc_user_type where USER_ID='" + myName + "' and USER_PASS='" + myPassword + "' and STATUS='1'"; // database STATUS coloumn name is varchar
** Not procedure call , sequence.nextvalue use in insert query
String fullName = firstName + lastName; // It is call concatenation
System.out.println(fullName);
// query = "insert into kyamc_user_type(ID,USER_FULL_NAME,USER_ID,USER_EMAIL,GROUP_ID,USER_PASS,USER_MOBILE,GENDER,ENTRY_TIMESTAMP,COLLEGE_NO ) values (SEQ_COLLEGEINFO_ID.nextval,'"+fullName+"','"+userName+"','"+userEmail+"','"+roleIdA+"','"+userPassword+"','"+mobNumber+"','"+gender +"',sysdate,'1')";
##############################################
##############################################
##############################################
What is Normalization ??
What is alias name ??
//String query = "SELECT ID,USER_ID,USER_PASS,USER_FULL_NAME,GROUP_ID FROM KYAMC_USER_TYPE where STATUS='0'";
String query = "select id,user_id,user_pass,user_full_name,group_id,(select urd_name from kyamc_roledata_details where urd_grouporrole_id =a.group_id ) urd_name from kyamc_user_type a where status='0'";
N.B: Here group_id = 5
urd_name = teacher =group_id = 5
pls observe image as coloumn name :
1/
2/
3/ See the below image :
Details image -
If I want to show zero value as inactive in the jsp page then query will be -
String query = "select id,user_id,user_pass,user_full_name,group_id,(select urd_name from kyamc_roledata_details where urd_grouporrole_id =a.group_id ) urd_name, decode(status,'1','Active','Inactive') status from kyamc_user_type a";
it is normalization. here two table is use .
urd_name and status is use for alias name
4// Query Run result :
5//IT (4// Query result show)is show in jsp page :
Now script call using on click :
<!-- **** Start status **** -->
<script type="text/javascript">
function userStatusCall(statusVal,unikId){
alert("statusVal :"+statusVal);
alert(unikId);
$.ajax({
url:"userStatusAdUser.do?statusVal="+statusVal+"&unikId="+unikId,
success:function(data){
$("#divs_id").html(data);
}
})
alert("statusVal_load");
}
</script>
or : alert confirmation call
<!-- **** Start status **** -->
<script type="text/javascript">
function userStatusCall(doctorNo,statusVal){
//alert("statusVal :"+statusVal);
//alert(doctorNo);
var x = window.confirm("Are you sure you are ok?")
if(x){
$.ajax({
url:"userStatusLoad.do?statusVal="+statusVal+"&doctorNo="+doctorNo,
success:function(data){
$("#doctorListShow_Div").html(data);
}
})
}else{
//alert("alert window close");
}
//alert("statusVal_load");
}
</script>
java action class call :
//Azax
private String statusVal;
private String unikId;
private String status = "";//Not set get just use for condition apply
public String userStatus(){
System.out.println("azax page: statusList call");
if(statusVal.equalsIgnoreCase("inactive")){
status ="1";
} else {
status ="0";
}
UserDataTableSQL userDataTableSQL = new UserDataTableSQL();
userDataTableSQL.getUpdateTable(status,unikId); // use for update query
userDataList(); // use for select query
return "userStatus";
}
sqlcall:
//***************************************************************** START
//***************************************************************** START
//========================= update query(executeUpdate) / insert query
public void getUpdateTable(String statusPosition,String booksSingleId){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
//update kyamc_user_type set Status = '0' where ID='1130000019'
String queryUpdate = "update kyamc_user_type set Status = '"+statusPosition+"' where ID='"+booksSingleId+"'";
System.out.println("QueryUpdate" + queryUpdate);
//st.execute(query);//It is use for insertQuery
st.executeUpdate(queryUpdate);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
//return "getBankInfoPageReturn"; //Remember UPDATE query not Return;.Only select query return List.
}
//***************************************************************** END
//***************************************************************** END
********************************************************
********************************************************
Global variable declare way :
String sqlSeq="",saveQuery="";
private CallableStatement cs = null;
private PreparedStatement pre = null;
Information:
and also variable declare:
//Update statement
public void getUpdateTable(String teachGeneralId,String teacherName){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
//query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
//queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"',DESIGNATION_NAME = '"+teacherDesig+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
System.out.println("QueryUpdate" + queryUpdate);
//st.execute(query);//It is use for insertQuery
st.executeUpdate(queryUpdate);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
//return "getBankInfoPageReturn"; //Remember UPDATE query not Return;.Only select query return List.
}
//Delete statement
public void getDeleteTable(String priId){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
//query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
//queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"',DESIGNATION_NAME = '"+teacherDesig+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
String queryDelete = "delete from tech_general_info where TECH_GENERAL_INFO_ID='"+priId+"'";
System.out.println("QueryDelete" + queryDelete);
//st.execute(query);//It is use for insert Query
st.execute(queryDelete);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
//return "getBankInfoPageReturn"; //Remember DELETE query not Return;.Only select query return List.
}
##############################
###############################
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
//Dear myself please RND this query . it is single row one coloumn, so I can use info or string .
//If it is single row multiple coloumn then I can use info . If it is Multiple row then I can use list
//RND 1
private String storePasswordDD;
//private String storeUserIdDD;
//******************************** It is select query for get password
public String getUserPassword(String userEmailAddress) {
/// List bookDataList = new ArrayList();
boolean fg = true;
this.con = this.dbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
String query = "select user_id,user_pass from KYAMC_USER_TYPE where USER_EMAIL='"+userEmailAddress+"'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
// BookTypeEntryInfo bookTypeEntryInfo = new BookTypeEntryInfo();
// bookTypeEntryInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
storePasswordDD=rs.getString("user_pass");
// storeUserIdDD=rs.getString("user_id");
// bookDataList.add(bookTypeEntryInfo);
}
} catch (SQLException sq) {
System.out.println("User password Info Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return storePasswordDD;
}
//RND 2
private String storeUserIdDD;
//******************************** It is select query for get password
public String getUserId(String userEmailAddress) {
/// List bookDataList = new ArrayList();
boolean fg = true;
this.con = this.dbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
String query = "select user_id,user_pass from KYAMC_USER_TYPE where USER_EMAIL='"+userEmailAddress+"'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
// BookTypeEntryInfo bookTypeEntryInfo = new BookTypeEntryInfo();
// bookTypeEntryInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
// storePasswordDD=rs.getString("user_pass");
storeUserIdDD=rs.getString("user_id");
//storeUserIdDD[1]=rs.getString("user_pass");
// bookDataList.add(bookTypeEntryInfo);
}
} catch (SQLException sq) {
System.out.println("UserId Info Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return storeUserIdDD;
}
Action File:
..................
public void getRecoverUserPassword(){
System.out.println("get user Password");
BookEntrySQL bookEntrySQL =new BookEntrySQL();
storePassword = bookEntrySQL.getUserPassword(userEmailAddress);
storeUserId = bookEntrySQL.getUserId(userEmailAddress);
System.out.println("storePassword"+storePassword);
System.out.println("storeUserId"+storeUserId);
// return "getPassword";
}
stringJava_doubleCotation_varcharDb_singleCotation :
public String saveCustomerDataInfo(){
System.out.println("select call data:");
UserBlockDao userBlockDao = new UserBlockDao();
String stopDate = "";
String fromDate = "";
if(status=="S"){
stopDate = statusWiseDate;
} else {
fromDate = statusWiseDate;
}
userBlockDao.insertCustomerInforma(areaExecId,regById,name,userId,userPass,areaId,stopDate,fromDate,status,previousId,previousName,instructionBy,instructionName,designation,dateWithTime,remarks);
return "saveCustomerDataInfo";
}
Again insert query from drug application :
public void insertCustomerInforma(String areaExecId,String regById,String name,String userId,String userPass,String areaId,String stopDate,String fromDate,String status,String previousId,String previousName,String instructionBy,String instructionName,String designation,String dateWithTime,String remarks){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
int processId = 0;
st = con.createStatement();
String unikProcessId = "select Nvl(max(process_id),0)+1 processId from ms_system_user_process";
rs = st.executeQuery(unikProcessId);
while(rs.next()) {
processId = rs.getInt("processId");
}
st = con.createStatement();
query = "Insert into MS_SYSTEM_USER_PROCESS(PROCESS_ID, AREA_EXEC_ID, REQ_BY_ID, EXECUTIVE_NAME, USER_ID,USER_PASS, AREA_ID, START_FROM_DT, STOP_FROM_DT,"
+ "USER_NEW_REPLACEMENT_STATUS,PRE_REQ_BY_ID, PRE_EXECUTIVE_NAME, INSTRUCTION_BY, "
+ "INSTRUCTION_BY_NAME, DESIGNATION,INSTRUCTION_DATE_TIME, "
+ "REMARKS)Values("+processId+", '"+areaExecId+"', '"+regById+"', '"+name+"', '"+userId+"', '"+userPass+"', '"+areaId+"', TO_DATE('"+fromDate+"', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('"+stopDate+"', 'MM/DD/YYYY HH24:MI:SS'), '"+status+"', '"+previousId+"', '"+previousName+"', '"+instructionBy+"', '"+instructionName+"', '"+designation+"', TO_DATE('"+dateWithTime+"', 'MM/DD/YYYY HH24:MI:SS'), '"+remarks+"')";
System.out.println("Query" + query);
st.executeUpdate(query);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// return "getBankInfoPageReturn"; // page return not query return.Remember insert query notreturn;.Only select query return List.
}
Garbase insert query :
//##############################################################################################
//##############################################################################################
//Garbase code
public void insertCustomerInformaEXXX(String areaExecId,String regById,String name,String userId,String userPass,String areaId,String stopDate,String fromDate,String status,String previousId,String previousName,String instructionBy,String instructionName,String designation,String dateWithTime,String remarks){
int processId = 0;
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
String unikProcessId = "select Nvl(max(process_id),0)+1 processId from ms_system_user_process";
rs = st.executeQuery(unikProcessId);
while(rs.next()) {
processId = rs.getInt("processId");
}
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
query = "Insert into MS_SYSTEM_USER_PROCESS(PROCESS_ID, AREA_EXEC_ID, REQ_BY_ID, EXECUTIVE_NAME, USER_ID,USER_PASS, AREA_ID, START_FROM_DT, STOP_FROM_DT,"
+ "USER_NEW_REPLACEMENT_STATUS,PRE_REQ_BY_ID, PRE_EXECUTIVE_NAME, INSTRUCTION_BY, "
+ "INSTRUCTION_BY_NAME, DESIGNATION,INSTRUCTION_DATE_TIME, "
+ "REMARKS)Values("+processId+", '"+areaExecId+"', '"+regById+"', '"+name+"', '"+userId+"', '"+userPass+"', '"+areaId+"', TO_DATE('"+fromDate+"', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('"+stopDate+"', 'MM/DD/YYYY HH24:MI:SS'), '"+status+"', '"+previousId+"', '"+previousName+"', '"+instructionBy+"', '"+instructionName+"', '"+designation+"', TO_DATE('"+dateWithTime+"', 'MM/DD/YYYY HH24:MI:SS'), '"+remarks+"')";
System.out.println("Query" + query);
st.executeUpdate(query);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// return "getBankInfoPageReturn"; // page return not query return.Remember insert query notreturn;.Only select query return List.
}
List Call (Oracle SQL) :
package com.allClass.Sql;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.allClass.Database.DatabaseConnection;
import com.allClass.Model.GenericDetailsInfo;
import com.allClass.Session.SessionVariable;
public class GenericSql {
Connection con=null;
ResultSet rs=null;
Statement st=null;
CallableStatement cs = null;
DatabaseConnection dbc=new DatabaseConnection();
SessionVariable sv=new SessionVariable();
public List<GenericDetailsInfo> getGenericListData() {
List<GenericDetailsInfo> genericDataList = new ArrayList<GenericDetailsInfo>();
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
String query="select thrapgrp_id,thrapgrp_name,description,thrapgrp_pid,rec_status,entered_by,entry_timestamp,update_by,updated_timestamp,company_no,who_selection from pm_theragrp";
rs = st.executeQuery(query);
while(rs.next()) {
GenericDetailsInfo genericDetailsInfo = new GenericDetailsInfo();
genericDetailsInfo.setThrapgrp_id(rs.getString("thrapgrp_id"));
genericDetailsInfo.setThrapgrp_name(rs.getString("thrapgrp_name"));
genericDetailsInfo.setDescription(rs.getString("description"));
genericDetailsInfo.setThrapgrp_pid(rs.getString("thrapgrp_pid"));
genericDetailsInfo.setRec_status(rs.getString("rec_status"));
genericDetailsInfo.setEntered_by(rs.getString("entered_by"));
genericDetailsInfo.setEntry_timestamp(rs.getString("entry_timestamp"));
genericDetailsInfo.setUpdate_by(rs.getString("update_by"));
genericDetailsInfo.setUpdated_timestamp(rs.getString("updated_timestamp"));
genericDetailsInfo.setCompany_no(rs.getString("company_no"));
genericDetailsInfo.setWho_selection(rs.getString("who_selection"));
genericDataList.add(genericDetailsInfo);
}
} catch (SQLException sq) {
System.out.println("Generic Name Query Exception " + sq.getMessage());
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return genericDataList;
}
}
When Jasper Report Call See the Following Script ::
<script>
function getReport() {
alert ("This is a Report message!");
/* var instituteType=$('#instituteType').val(); */
var e = document.getElementById("instituteType");
var instituteType = e.options[e.selectedIndex].value;
//alert(instituteType);
/* var instituteTypeTextValue = e.options[e.selectedIndex].innerHTML;
alert(instituteTypeTextValue); */
var o = document.getElementById("instituteBoardDivision");
var divisionVal = o.options[o.selectedIndex].value;
//alert(divisionVal);
var i = document.getElementById("gar_district");
var districtVal = i.options[i.selectedIndex].value;
//alert(districtVal);
var s = document.getElementById("gar_thana");
var thanaVal = s.options[s.selectedIndex].value;
//alert(thanaVal);
var ajaxURL = "getReportTchr.do?instituteType="+instituteType+"&divisionVal="+divisionVal+"&districtVal="+districtVal+"&thanaVal="+thanaVal;
window.open(ajaxURL, '_blank');
/* $.ajax({
beforeSend : function() {
},
url : ajaxURL,
success : function(result) {
//$("#garDisDiv").html(result);
$("#containerR").html(result);
}
});
*/
}
</script>
1/ select query only use executeQuery
2/insert,update/edit,delete use execute
or
insert,update/edit executeUpdate
*/only select query return List or single row (with include model/info)
**//Search : Search means select Query
**//set means equals To
**//variable declare meaning :
private String sqlSeq=""; // means initialize
String sqlSeq=""; // means initialize
private CallableStatement cs = null;
N.B:
String[] offer2java; // declare way
String[] offer2java = new String[5]; //initialize way , here 5 is length of array
Null means :in java and oracle ??
RND : Research and development
GIS :Geographical
****************************
****************************
1/ insert query:
saveQuery = "insert into institutes_computers(REG_NO,FULL_NAME,institute_id,data_type,entered_by,entered_timestamp) values ('"+reg_noA+"','"+police_nameA+"','" + institute_id + "','N','"+user_no+"',sysdate)";
2/ update query:
saveQuery = "update institutes set INSTITUTE_NAME = '"+name+"',DIVISION_ID='"+instituteBoardDivision+"' where institute_id='"+institute_id+"'";
3/update/edit query :
queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"',DESIGNATION_NAME = '"+teacherDesig+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
4/ delete query:
String queryDelete = "delete from tech_general_info where TECH_GENERAL_INFO_ID='"+priId+"'";
NULL POINT EXCEPTION HANDLE :
suppose I think check box name is hscBm THAT database coloumn contain length is 2.
Now If i Sent null value from jsp page then Then coloumn will be 4 digit (null), But dont show exception If insert query is -
hscBm==null?"":hscBm
saveQuery = "insert into institutes_computers(REG_NO,FULL_NAME,institute_id,data_type,entered_by,entered_timestamp) values ('"+reg_noA+"','"+hscBm==null?"":hscBm +"','" + institute_id + "','N','"+user_no+"',sysdate)";
Not:
saveQuery = "insert into institutes_computers(REG_NO,FULL_NAME,institute_id,data_type,entered_by,entered_timestamp) values ('"+reg_noA+"','"+hscBm+"','" + institute_id + "','N','"+user_no+"',sysdate)";
database STATUS coloumn name is number or varchar then select query is -
//String query = "select * from kyamc_user_type where USER_ID='" + myName + "' and USER_PASS='" + myPassword + "' and STATUS=1"; // if database STATUS coloumn name is number then no cottation
String query = "select * from kyamc_user_type where USER_ID='" + myName + "' and USER_PASS='" + myPassword + "' and STATUS='1'"; // database STATUS coloumn name is varchar
** Not procedure call , sequence.nextvalue use in insert query
String fullName = firstName + lastName; // It is call concatenation
System.out.println(fullName);
// query = "insert into kyamc_user_type(ID,USER_FULL_NAME,USER_ID,USER_EMAIL,GROUP_ID,USER_PASS,USER_MOBILE,GENDER,ENTRY_TIMESTAMP,COLLEGE_NO ) values (SEQ_COLLEGEINFO_ID.nextval,'"+fullName+"','"+userName+"','"+userEmail+"','"+roleIdA+"','"+userPassword+"','"+mobNumber+"','"+gender +"',sysdate,'1')";
##############################################
##############################################
##############################################
What is Normalization ??
What is alias name ??
//String query = "SELECT ID,USER_ID,USER_PASS,USER_FULL_NAME,GROUP_ID FROM KYAMC_USER_TYPE where STATUS='0'";
String query = "select id,user_id,user_pass,user_full_name,group_id,(select urd_name from kyamc_roledata_details where urd_grouporrole_id =a.group_id ) urd_name from kyamc_user_type a where status='0'";
N.B: Here group_id = 5
urd_name = teacher =group_id = 5
pls observe image as coloumn name :
1/
2/
3/ See the below image :
Details image -
If I want to show zero value as inactive in the jsp page then query will be -
String query = "select id,user_id,user_pass,user_full_name,group_id,(select urd_name from kyamc_roledata_details where urd_grouporrole_id =a.group_id ) urd_name, decode(status,'1','Active','Inactive') status from kyamc_user_type a";
it is normalization. here two table is use .
urd_name and status is use for alias name
4// Query Run result :
5//IT (4// Query result show)is show in jsp page :
Now script call using on click :
<!-- **** Start status **** -->
<script type="text/javascript">
function userStatusCall(statusVal,unikId){
alert("statusVal :"+statusVal);
alert(unikId);
$.ajax({
url:"userStatusAdUser.do?statusVal="+statusVal+"&unikId="+unikId,
success:function(data){
$("#divs_id").html(data);
}
})
alert("statusVal_load");
}
</script>
or : alert confirmation call
<!-- **** Start status **** -->
<script type="text/javascript">
function userStatusCall(doctorNo,statusVal){
//alert("statusVal :"+statusVal);
//alert(doctorNo);
var x = window.confirm("Are you sure you are ok?")
if(x){
$.ajax({
url:"userStatusLoad.do?statusVal="+statusVal+"&doctorNo="+doctorNo,
success:function(data){
$("#doctorListShow_Div").html(data);
}
})
}else{
//alert("alert window close");
}
//alert("statusVal_load");
}
</script>
java action class call :
//Azax
private String statusVal;
private String unikId;
private String status = "";//Not set get just use for condition apply
public String userStatus(){
System.out.println("azax page: statusList call");
if(statusVal.equalsIgnoreCase("inactive")){
status ="1";
} else {
status ="0";
}
UserDataTableSQL userDataTableSQL = new UserDataTableSQL();
userDataTableSQL.getUpdateTable(status,unikId); // use for update query
userDataList(); // use for select query
return "userStatus";
}
sqlcall:
//***************************************************************** START
//***************************************************************** START
//========================= update query(executeUpdate) / insert query
public void getUpdateTable(String statusPosition,String booksSingleId){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
//update kyamc_user_type set Status = '0' where ID='1130000019'
String queryUpdate = "update kyamc_user_type set Status = '"+statusPosition+"' where ID='"+booksSingleId+"'";
System.out.println("QueryUpdate" + queryUpdate);
//st.execute(query);//It is use for insertQuery
st.executeUpdate(queryUpdate);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
//return "getBankInfoPageReturn"; //Remember UPDATE query not Return;.Only select query return List.
}
//***************************************************************** END
//***************************************************************** END
********************************************************
********************************************************
Global variable declare way :
String sqlSeq="",saveQuery="";
private CallableStatement cs = null;
private PreparedStatement pre = null;
Information:
1/ Single
row return query তে info হয়
2/ Multiple
row return query তে List হয়
যে table থেকে value আনবো ঐ table যদি অন্য আর
কোন table এর সাথে যুক্ত থাকে তাহলে list এ multiple row সেট করবো
MySql and java code using status :
mysql active status show as Active and Inactive
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
inser sql file
public class BankInfoInsertSQL {
private Connection con = null;
private Statement st = null;
private CallableStatement cs = null;
private ResultSet rs = null;
private DatabaseConnection dbc = new DatabaseConnection();
String query = "";
public void addBankInformation(String bankName,String branchName,String branchAddress,String branchMobNo,String branchPhoneNo,String branchFax,String branchEmail,String branchWebAddress,String branchDescription){
String bank_id;
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
String sql="call PRC_BUILD_PK(?, ?,?,?,?,?)";
cs = con.prepareCall(sql);
cs.setString(1, "USER");
cs.setString(2, "SEQ_BANKINFO_ID");
cs.setString(3, "P");
cs.setString(4, "1");
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.setString(6, "Y");
cs.execute();
bank_id = cs.getString(5);
System.out.println(bank_id);
st = con.createStatement();
query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
System.out.println("Query" + query);
st.execute(query);
String query2 = "insert into fn_bank_branch(BANK_ID,BRANCH_NAME,BRANCH_ADDRESS,MOBILE_NO,PHONE_NO,FAX,E_MAIL,WEB_ADDRESS,BRANCH_DESCRIPTION,ENTRY_TIMESTAMP,COMPANY_NO) values('"+bank_id+"','"+branchName+"','"+branchAddress+"','"+branchMobNo+"','"+branchPhoneNo+"','"+branchFax+"','"+branchEmail+"','"+branchWebAddress+"','"+branchDescription+"',sysdate,'1')";
System.out.println("Query2" + query2);
st.execute(query2);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// return "subBankInformation";// As void not String
}
}
Now it is get in java action file:
MySql and java code using status :
mysql active status show as Active and Inactive
mysql active status show as Active and Inactive :
bataBase Table Show as screen Shot :
rbs_user this table active_status Coloumn Default=N
and active_status coloumn name is char.
SELECT user_id,user_name,e_mail,designation,CASE WHEN active_status ='Y' THEN 'Active' ELSE 'Inactive' END activestatus FROM rbs_user
activestatus is allias name
java code:
public String userStatus(){
System.out.println("kaniz");
if(statusVal.equalsIgnoreCase("inactive")){
status ="Y";
} else {
status ="N";
}
UsersSQL usersSQL = new UsersSQL();
usersSQL.getUpdateTable(status,unikId); // use for update query
//userDataList = usersSQL.selectUsersDate();// use for select query
return "userStatus";
}
bataBase Table Show as screen Shot :
rbs_user this table active_status Coloumn Default=N
and active_status coloumn name is char.
SELECT user_id,user_name,e_mail,designation,CASE WHEN active_status ='Y' THEN 'Active' ELSE 'Inactive' END activestatus FROM rbs_user
activestatus is allias name
java code:
public String userStatus(){
System.out.println("kaniz");
if(statusVal.equalsIgnoreCase("inactive")){
status ="Y";
} else {
status ="N";
}
UsersSQL usersSQL = new UsersSQL();
usersSQL.getUpdateTable(status,unikId); // use for update query
//userDataList = usersSQL.selectUsersDate();// use for select query
return "userStatus";
}
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
inser sql file
public class BankInfoInsertSQL {
private Connection con = null;
private Statement st = null;
private CallableStatement cs = null;
private ResultSet rs = null;
private DatabaseConnection dbc = new DatabaseConnection();
String query = "";
public void addBankInformation(String bankName,String branchName,String branchAddress,String branchMobNo,String branchPhoneNo,String branchFax,String branchEmail,String branchWebAddress,String branchDescription){
String bank_id;
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
String sql="call PRC_BUILD_PK(?, ?,?,?,?,?)";
cs = con.prepareCall(sql);
cs.setString(1, "USER");
cs.setString(2, "SEQ_BANKINFO_ID");
cs.setString(3, "P");
cs.setString(4, "1");
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.setString(6, "Y");
cs.execute();
bank_id = cs.getString(5);
System.out.println(bank_id);
st = con.createStatement();
query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
System.out.println("Query" + query);
st.execute(query);
String query2 = "insert into fn_bank_branch(BANK_ID,BRANCH_NAME,BRANCH_ADDRESS,MOBILE_NO,PHONE_NO,FAX,E_MAIL,WEB_ADDRESS,BRANCH_DESCRIPTION,ENTRY_TIMESTAMP,COMPANY_NO) values('"+bank_id+"','"+branchName+"','"+branchAddress+"','"+branchMobNo+"','"+branchPhoneNo+"','"+branchFax+"','"+branchEmail+"','"+branchWebAddress+"','"+branchDescription+"',sysdate,'1')";
System.out.println("Query2" + query2);
st.execute(query2);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// return "subBankInformation";// As void not String
}
}
Now it is get in java action file:
public String getBankInfoPageReturn() {
System.out.println("FFFF");
bankInfoInsertSQL.addBankInformation(bankName,branchName,branchAddress,branchMobNo,branchPhoneNo,branchFax,branchEmail,branchWebAddress,branchDescription);
return "getBankInfoPageReturn";
}
System.out.println("FFFF");
bankInfoInsertSQL.addBankInformation(bankName,branchName,branchAddress,branchMobNo,branchPhoneNo,branchFax,branchEmail,branchWebAddress,branchDescription);
return "getBankInfoPageReturn";
}
and also variable declare:
// Form variable
private String bankName;
private String branchName;
private String branchAddress;
private String branchMobNo;
private String branchPhoneNo;
private String branchFax;
private String branchEmail;
private String branchWebAddress;
private String branchDescription;
private String accountNo;
private String accountName;
private String bankName;
private String branchName;
private String branchAddress;
private String branchMobNo;
private String branchPhoneNo;
private String branchFax;
private String branchEmail;
private String branchWebAddress;
private String branchDescription;
private String accountNo;
private String accountName;
If this page insert query call then (java action class):
here not declare
public String getBankInfoPageReturnEXXX()
public String getBankInfoPageReturnEXXX()
Because variable declare as String in same class
private String bankName;
So take debug
//***************************Backup Start
public String getBankInfoPageReturnEXXX(){
String bank_id;
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
String sql="call PRC_BUILD_PK(?, ?,?,?,?,?)";
cs = con.prepareCall(sql);
cs.setString(1, "USER");
cs.setString(2, "SEQ_BANKINFO_ID");
cs.setString(3, "P");
cs.setString(4, "1");
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.setString(6, "Y");
cs.execute();
bank_id = cs.getString(5);
System.out.println(bank_id);
st = con.createStatement();
query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
System.out.println("Query" + query);
st.execute(query);
String query2 = "insert into fn_bank_branch(BANK_ID,BRANCH_NAME,BRANCH_ADDRESS,MOBILE_NO,PHONE_NO,FAX,E_MAIL,WEB_ADDRESS,BRANCH_DESCRIPTION,ENTRY_TIMESTAMP,COMPANY_NO) values('"+bank_id+"','"+branchName+"','"+branchAddress+"','"+branchMobNo+"','"+branchPhoneNo+"','"+branchFax+"','"+branchEmail+"','"+branchWebAddress+"','"+branchDescription+"',sysdate,'1')";
System.out.println("Query2" + query2);
st.execute(query2);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return "getBankInfoPageReturn"; // page return not query return.Remember insert query notreturn;.Only select query return List.
}
//***************************Backup End
public String getBankInfoPageReturnEXXX(){
String bank_id;
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
String sql="call PRC_BUILD_PK(?, ?,?,?,?,?)";
cs = con.prepareCall(sql);
cs.setString(1, "USER");
cs.setString(2, "SEQ_BANKINFO_ID");
cs.setString(3, "P");
cs.setString(4, "1");
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.setString(6, "Y");
cs.execute();
bank_id = cs.getString(5);
System.out.println(bank_id);
st = con.createStatement();
query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
System.out.println("Query" + query);
st.execute(query);
String query2 = "insert into fn_bank_branch(BANK_ID,BRANCH_NAME,BRANCH_ADDRESS,MOBILE_NO,PHONE_NO,FAX,E_MAIL,WEB_ADDRESS,BRANCH_DESCRIPTION,ENTRY_TIMESTAMP,COMPANY_NO) values('"+bank_id+"','"+branchName+"','"+branchAddress+"','"+branchMobNo+"','"+branchPhoneNo+"','"+branchFax+"','"+branchEmail+"','"+branchWebAddress+"','"+branchDescription+"',sysdate,'1')";
System.out.println("Query2" + query2);
st.execute(query2);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return "getBankInfoPageReturn"; // page return not query return.Remember insert query notreturn;.Only select query return List.
}
//***************************Backup End
This is use for procedure from above
String sql="call PRC_BUILD_PK(?, ?,?,?,?,?)";
cs = con.prepareCall(sql);
cs.setString(1, "USER");
cs.setString(2, "SEQ_BANKINFO_ID");
cs.setString(3, "P");
cs.setString(4, "1");
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.setString(6, "Y");
cs.execute();
bank_id = cs.getString(5);
System.out.println(bank_id);
cs = con.prepareCall(sql);
cs.setString(1, "USER");
cs.setString(2, "SEQ_BANKINFO_ID");
cs.setString(3, "P");
cs.setString(4, "1");
cs.registerOutParameter(5, java.sql.Types.VARCHAR);
cs.setString(6, "Y");
cs.execute();
bank_id = cs.getString(5);
System.out.println(bank_id);
Multiple Row and Single Row Return Query:
//3rd tree bank information
public List getAccountInfo6TreeViewData(String parentId) {
List bankNameParentTree3rdList = new ArrayList();
boolean fg = true;
this.con = this.mdbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
/*String query = "SELECT BANK_ID,BANK_NAME FROM FN_BANK";
String query2 = "SELECT BANK_SLNO,BANK_ID,BRANCH_NAME FROM FN_BANK_BRANCH";
String query3 = "SELECT BANK_SLNO,BANK_NAME FROM FN_BANK_BRANCH and FN_BANK";*/
/*String query = "SELECT fn_bank.BANK_NAME, fn_bank_branch.BRANCH_NAME, fn_bankacc.BANK_SLNO" +
" FROM (ati_phsystem.fn_bank_branch fn_bank_branch" +
" INNER JOIN ati_phsystem.fn_bankacc fn_bankacc" +
" ON (fn_bank_branch.BANK_SLNO = fn_bankacc.BANK_SLNO))" +
" INNER JOIN ati_phsystem.fn_bank fn_bank" +
" ON (fn_bank.BANK_ID = fn_bank_branch.BANK_ID)";*/
String query = "select m.BANK_ID bank_slno,m.BANK_NAME,c.BRANCH_NAME,d.AUTO_AC_NO from fn_bank m, fn_bank_branch c, fn_bankacc d where m.BANK_ID = c.BANK_ID and c.BANK_SLNO = d.BANK_SLNO and m.BANK_ID='1'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
AccountLevelInfo accountLevelInfo = new AccountLevelInfo();
accountLevelInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
accountLevelInfo.setBank_slno(rs.getString("bank_slno"));
bankNameParentTree3rdList.add(accountLevelInfo);
}
} catch (SQLException sq) {
System.out.println("Account Information6 Branch Name Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return bankNameParentTree3rdList;
}
//Account Information against Account Number Single Row Return
public AccountLevelInfo getAccountInfo7ViewData(String accountNumber) {
AccountLevelInfo accountLevelInfo = new AccountLevelInfo();
boolean fg = true;
this.con = this.mdbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
/*String query = "SELECT BANK_ID,BANK_NAME FROM FN_BANK";
String query2 = "SELECT BANK_SLNO,BANK_ID,BRANCH_NAME FROM FN_BANK_BRANCH";
String query3 = "SELECT BANK_SLNO,BANK_NAME FROM FN_BANK_BRANCH and FN_BANK";*/
/*String query = "SELECT fn_bank.BANK_NAME, fn_bank_branch.BRANCH_NAME, fn_bankacc.BANK_SLNO" +
" FROM (ati_phsystem.fn_bank_branch fn_bank_branch" +
" INNER JOIN ati_phsystem.fn_bankacc fn_bankacc" +
" ON (fn_bank_branch.BANK_SLNO = fn_bankacc.BANK_SLNO))" +
" INNER JOIN ati_phsystem.fn_bank fn_bank" +
" ON (fn_bank.BANK_ID = fn_bank_branch.BANK_ID)";*/
String query = "select m.BANK_NAME,n.BRANCH_NAME,n.BRANCH_ADDRESS,n.MOBILE_NO,n.PHONE_NO,n.FAX,n.E_MAIL,n.WEB_ADDRESS,n.DESCRIPTION,p.AUTO_AC_NO from fn_bank m, fn_bank_branch n,fn_bankacc p where AUTO_AC_NO='accountNumber'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
//AccountLevelInfo accountLevelInfo = new AccountLevelInfo();
accountLevelInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
accountLevelInfo.setBank_name(rs.getString("bank_name"));
accountLevelInfo.setBranch_name(rs.getString("branch_name"));
accountLevelInfo.setBranch_address(rs.getString("branch_address"));
accountLevelInfo.setMobile_no(rs.getString("mobile_no"));
accountLevelInfo.setPhone_no(rs.getString("phone_no"));
accountLevelInfo.setFax(rs.getString("fax"));
accountLevelInfo.setE_mail(rs.getString("e_mail"));
accountLevelInfo.setWeb_address(rs.getString("web_address"));
accountLevelInfo.setDescription(rs.getString("description"));
//bankInfoAgainstAccNumberList.add(accountLevelInfo);
}
} catch (SQLException sq) {
System.out.println("Account Information7 Branch Name Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return accountLevelInfo;
//
}
//3rd tree bank information
public List getAccountInfo6TreeViewData(String parentId) {
List bankNameParentTree3rdList = new ArrayList();
boolean fg = true;
this.con = this.mdbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
/*String query = "SELECT BANK_ID,BANK_NAME FROM FN_BANK";
String query2 = "SELECT BANK_SLNO,BANK_ID,BRANCH_NAME FROM FN_BANK_BRANCH";
String query3 = "SELECT BANK_SLNO,BANK_NAME FROM FN_BANK_BRANCH and FN_BANK";*/
/*String query = "SELECT fn_bank.BANK_NAME, fn_bank_branch.BRANCH_NAME, fn_bankacc.BANK_SLNO" +
" FROM (ati_phsystem.fn_bank_branch fn_bank_branch" +
" INNER JOIN ati_phsystem.fn_bankacc fn_bankacc" +
" ON (fn_bank_branch.BANK_SLNO = fn_bankacc.BANK_SLNO))" +
" INNER JOIN ati_phsystem.fn_bank fn_bank" +
" ON (fn_bank.BANK_ID = fn_bank_branch.BANK_ID)";*/
String query = "select m.BANK_ID bank_slno,m.BANK_NAME,c.BRANCH_NAME,d.AUTO_AC_NO from fn_bank m, fn_bank_branch c, fn_bankacc d where m.BANK_ID = c.BANK_ID and c.BANK_SLNO = d.BANK_SLNO and m.BANK_ID='1'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
AccountLevelInfo accountLevelInfo = new AccountLevelInfo();
accountLevelInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
accountLevelInfo.setBank_slno(rs.getString("bank_slno"));
bankNameParentTree3rdList.add(accountLevelInfo);
}
} catch (SQLException sq) {
System.out.println("Account Information6 Branch Name Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return bankNameParentTree3rdList;
}
//Account Information against Account Number Single Row Return
public AccountLevelInfo getAccountInfo7ViewData(String accountNumber) {
AccountLevelInfo accountLevelInfo = new AccountLevelInfo();
boolean fg = true;
this.con = this.mdbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
/*String query = "SELECT BANK_ID,BANK_NAME FROM FN_BANK";
String query2 = "SELECT BANK_SLNO,BANK_ID,BRANCH_NAME FROM FN_BANK_BRANCH";
String query3 = "SELECT BANK_SLNO,BANK_NAME FROM FN_BANK_BRANCH and FN_BANK";*/
/*String query = "SELECT fn_bank.BANK_NAME, fn_bank_branch.BRANCH_NAME, fn_bankacc.BANK_SLNO" +
" FROM (ati_phsystem.fn_bank_branch fn_bank_branch" +
" INNER JOIN ati_phsystem.fn_bankacc fn_bankacc" +
" ON (fn_bank_branch.BANK_SLNO = fn_bankacc.BANK_SLNO))" +
" INNER JOIN ati_phsystem.fn_bank fn_bank" +
" ON (fn_bank.BANK_ID = fn_bank_branch.BANK_ID)";*/
String query = "select m.BANK_NAME,n.BRANCH_NAME,n.BRANCH_ADDRESS,n.MOBILE_NO,n.PHONE_NO,n.FAX,n.E_MAIL,n.WEB_ADDRESS,n.DESCRIPTION,p.AUTO_AC_NO from fn_bank m, fn_bank_branch n,fn_bankacc p where AUTO_AC_NO='accountNumber'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
//AccountLevelInfo accountLevelInfo = new AccountLevelInfo();
accountLevelInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
accountLevelInfo.setBank_name(rs.getString("bank_name"));
accountLevelInfo.setBranch_name(rs.getString("branch_name"));
accountLevelInfo.setBranch_address(rs.getString("branch_address"));
accountLevelInfo.setMobile_no(rs.getString("mobile_no"));
accountLevelInfo.setPhone_no(rs.getString("phone_no"));
accountLevelInfo.setFax(rs.getString("fax"));
accountLevelInfo.setE_mail(rs.getString("e_mail"));
accountLevelInfo.setWeb_address(rs.getString("web_address"));
accountLevelInfo.setDescription(rs.getString("description"));
//bankInfoAgainstAccNumberList.add(accountLevelInfo);
}
} catch (SQLException sq) {
System.out.println("Account Information7 Branch Name Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return accountLevelInfo;
//
}
//Update statement
public void getUpdateTable(String teachGeneralId,String teacherName){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
//query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
//queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"',DESIGNATION_NAME = '"+teacherDesig+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
System.out.println("QueryUpdate" + queryUpdate);
//st.execute(query);//It is use for insertQuery
st.executeUpdate(queryUpdate);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
//return "getBankInfoPageReturn"; //Remember UPDATE query not Return;.Only select query return List.
}
//Delete statement
public void getDeleteTable(String priId){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
//query = "insert into fn_bank(BANK_ID,BANK_NAME,ENTRY_TIMESTAMP,COMPANY_NO) values ('"+bank_id+"','"+bankName+"',sysdate,'1')";
//queryUpdate = "update tech_general_info set TECH_NAME = '"+teacherName+"',DESIGNATION_NAME = '"+teacherDesig+"' where TECH_GENERAL_INFO_ID='"+teachGeneralId+"'";
String queryDelete = "delete from tech_general_info where TECH_GENERAL_INFO_ID='"+priId+"'";
System.out.println("QueryDelete" + queryDelete);
//st.execute(query);//It is use for insert Query
st.execute(queryDelete);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
//return "getBankInfoPageReturn"; //Remember DELETE query not Return;.Only select query return List.
}
###############################
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
//Dear myself please RND this query . it is single row one coloumn, so I can use info or string .
//If it is single row multiple coloumn then I can use info . If it is Multiple row then I can use list
//RND 1
private String storePasswordDD;
//private String storeUserIdDD;
//******************************** It is select query for get password
public String getUserPassword(String userEmailAddress) {
/// List bookDataList = new ArrayList();
boolean fg = true;
this.con = this.dbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
String query = "select user_id,user_pass from KYAMC_USER_TYPE where USER_EMAIL='"+userEmailAddress+"'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
// BookTypeEntryInfo bookTypeEntryInfo = new BookTypeEntryInfo();
// bookTypeEntryInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
storePasswordDD=rs.getString("user_pass");
// storeUserIdDD=rs.getString("user_id");
// bookDataList.add(bookTypeEntryInfo);
}
} catch (SQLException sq) {
System.out.println("User password Info Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return storePasswordDD;
}
//RND 2
private String storeUserIdDD;
//******************************** It is select query for get password
public String getUserId(String userEmailAddress) {
/// List bookDataList = new ArrayList();
boolean fg = true;
this.con = this.dbc.connectDB();
if (this.con == null) {
fg = false;
}
if (fg) {
try {
this.st = this.con.createStatement();
String query = "select user_id,user_pass from KYAMC_USER_TYPE where USER_EMAIL='"+userEmailAddress+"'";
this.rs = this.st.executeQuery(query);
while (this.rs.next()) {
// BookTypeEntryInfo bookTypeEntryInfo = new BookTypeEntryInfo();
// bookTypeEntryInfo.setAuto_ac_no(rs.getString("auto_ac_no").trim());
// storePasswordDD=rs.getString("user_pass");
storeUserIdDD=rs.getString("user_id");
//storeUserIdDD[1]=rs.getString("user_pass");
// bookDataList.add(bookTypeEntryInfo);
}
} catch (SQLException sq) {
System.out.println("UserId Info Query Exception "
+ sq.getMessage());
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
if (this.rs != null) {
this.rs.close();
}
this.con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return storeUserIdDD;
}
Action File:
..................
public void getRecoverUserPassword(){
System.out.println("get user Password");
BookEntrySQL bookEntrySQL =new BookEntrySQL();
storePassword = bookEntrySQL.getUserPassword(userEmailAddress);
storeUserId = bookEntrySQL.getUserId(userEmailAddress);
System.out.println("storePassword"+storePassword);
System.out.println("storeUserId"+storeUserId);
// return "getPassword";
}
stringJava_doubleCotation_varcharDb_singleCotation :
public String saveCustomerDataInfo(){
System.out.println("select call data:");
UserBlockDao userBlockDao = new UserBlockDao();
String stopDate = "";
String fromDate = "";
if(status=="S"){
stopDate = statusWiseDate;
} else {
fromDate = statusWiseDate;
}
userBlockDao.insertCustomerInforma(areaExecId,regById,name,userId,userPass,areaId,stopDate,fromDate,status,previousId,previousName,instructionBy,instructionName,designation,dateWithTime,remarks);
return "saveCustomerDataInfo";
}
Again insert query from drug application :
public void insertCustomerInforma(String areaExecId,String regById,String name,String userId,String userPass,String areaId,String stopDate,String fromDate,String status,String previousId,String previousName,String instructionBy,String instructionName,String designation,String dateWithTime,String remarks){
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
int processId = 0;
st = con.createStatement();
String unikProcessId = "select Nvl(max(process_id),0)+1 processId from ms_system_user_process";
rs = st.executeQuery(unikProcessId);
while(rs.next()) {
processId = rs.getInt("processId");
}
st = con.createStatement();
query = "Insert into MS_SYSTEM_USER_PROCESS(PROCESS_ID, AREA_EXEC_ID, REQ_BY_ID, EXECUTIVE_NAME, USER_ID,USER_PASS, AREA_ID, START_FROM_DT, STOP_FROM_DT,"
+ "USER_NEW_REPLACEMENT_STATUS,PRE_REQ_BY_ID, PRE_EXECUTIVE_NAME, INSTRUCTION_BY, "
+ "INSTRUCTION_BY_NAME, DESIGNATION,INSTRUCTION_DATE_TIME, "
+ "REMARKS)Values("+processId+", '"+areaExecId+"', '"+regById+"', '"+name+"', '"+userId+"', '"+userPass+"', '"+areaId+"', TO_DATE('"+fromDate+"', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('"+stopDate+"', 'MM/DD/YYYY HH24:MI:SS'), '"+status+"', '"+previousId+"', '"+previousName+"', '"+instructionBy+"', '"+instructionName+"', '"+designation+"', TO_DATE('"+dateWithTime+"', 'MM/DD/YYYY HH24:MI:SS'), '"+remarks+"')";
System.out.println("Query" + query);
st.executeUpdate(query);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// return "getBankInfoPageReturn"; // page return not query return.Remember insert query notreturn;.Only select query return List.
}
Garbase insert query :
//##############################################################################################
//##############################################################################################
//Garbase code
public void insertCustomerInformaEXXX(String areaExecId,String regById,String name,String userId,String userPass,String areaId,String stopDate,String fromDate,String status,String previousId,String previousName,String instructionBy,String instructionName,String designation,String dateWithTime,String remarks){
int processId = 0;
System.out.println("kaniz");
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
String unikProcessId = "select Nvl(max(process_id),0)+1 processId from ms_system_user_process";
rs = st.executeQuery(unikProcessId);
while(rs.next()) {
processId = rs.getInt("processId");
}
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
query = "Insert into MS_SYSTEM_USER_PROCESS(PROCESS_ID, AREA_EXEC_ID, REQ_BY_ID, EXECUTIVE_NAME, USER_ID,USER_PASS, AREA_ID, START_FROM_DT, STOP_FROM_DT,"
+ "USER_NEW_REPLACEMENT_STATUS,PRE_REQ_BY_ID, PRE_EXECUTIVE_NAME, INSTRUCTION_BY, "
+ "INSTRUCTION_BY_NAME, DESIGNATION,INSTRUCTION_DATE_TIME, "
+ "REMARKS)Values("+processId+", '"+areaExecId+"', '"+regById+"', '"+name+"', '"+userId+"', '"+userPass+"', '"+areaId+"', TO_DATE('"+fromDate+"', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('"+stopDate+"', 'MM/DD/YYYY HH24:MI:SS'), '"+status+"', '"+previousId+"', '"+previousName+"', '"+instructionBy+"', '"+instructionName+"', '"+designation+"', TO_DATE('"+dateWithTime+"', 'MM/DD/YYYY HH24:MI:SS'), '"+remarks+"')";
System.out.println("Query" + query);
st.executeUpdate(query);
} catch (SQLException sq) {
sq.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
// return "getBankInfoPageReturn"; // page return not query return.Remember insert query notreturn;.Only select query return List.
}
List Call (Oracle SQL) :
package com.allClass.Sql;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.allClass.Database.DatabaseConnection;
import com.allClass.Model.GenericDetailsInfo;
import com.allClass.Session.SessionVariable;
public class GenericSql {
Connection con=null;
ResultSet rs=null;
Statement st=null;
CallableStatement cs = null;
DatabaseConnection dbc=new DatabaseConnection();
SessionVariable sv=new SessionVariable();
public List<GenericDetailsInfo> getGenericListData() {
List<GenericDetailsInfo> genericDataList = new ArrayList<GenericDetailsInfo>();
boolean fg = true;
con = dbc.connectDB();
if (con == null) {
fg = false;
}
if (fg) {
try {
st = con.createStatement();
String query="select thrapgrp_id,thrapgrp_name,description,thrapgrp_pid,rec_status,entered_by,entry_timestamp,update_by,updated_timestamp,company_no,who_selection from pm_theragrp";
rs = st.executeQuery(query);
while(rs.next()) {
GenericDetailsInfo genericDetailsInfo = new GenericDetailsInfo();
genericDetailsInfo.setThrapgrp_id(rs.getString("thrapgrp_id"));
genericDetailsInfo.setThrapgrp_name(rs.getString("thrapgrp_name"));
genericDetailsInfo.setDescription(rs.getString("description"));
genericDetailsInfo.setThrapgrp_pid(rs.getString("thrapgrp_pid"));
genericDetailsInfo.setRec_status(rs.getString("rec_status"));
genericDetailsInfo.setEntered_by(rs.getString("entered_by"));
genericDetailsInfo.setEntry_timestamp(rs.getString("entry_timestamp"));
genericDetailsInfo.setUpdate_by(rs.getString("update_by"));
genericDetailsInfo.setUpdated_timestamp(rs.getString("updated_timestamp"));
genericDetailsInfo.setCompany_no(rs.getString("company_no"));
genericDetailsInfo.setWho_selection(rs.getString("who_selection"));
genericDataList.add(genericDetailsInfo);
}
} catch (SQLException sq) {
System.out.println("Generic Name Query Exception " + sq.getMessage());
} finally {
try {
if (rs != null) {
rs.close();
}
con.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
return genericDataList;
}
}
When Jasper Report Call See the Following Script ::
<script>
function getReport() {
alert ("This is a Report message!");
/* var instituteType=$('#instituteType').val(); */
var e = document.getElementById("instituteType");
var instituteType = e.options[e.selectedIndex].value;
//alert(instituteType);
/* var instituteTypeTextValue = e.options[e.selectedIndex].innerHTML;
alert(instituteTypeTextValue); */
var o = document.getElementById("instituteBoardDivision");
var divisionVal = o.options[o.selectedIndex].value;
//alert(divisionVal);
var i = document.getElementById("gar_district");
var districtVal = i.options[i.selectedIndex].value;
//alert(districtVal);
var s = document.getElementById("gar_thana");
var thanaVal = s.options[s.selectedIndex].value;
//alert(thanaVal);
var ajaxURL = "getReportTchr.do?instituteType="+instituteType+"&divisionVal="+divisionVal+"&districtVal="+districtVal+"&thanaVal="+thanaVal;
window.open(ajaxURL, '_blank');
/* $.ajax({
beforeSend : function() {
},
url : ajaxURL,
success : function(result) {
//$("#garDisDiv").html(result);
$("#containerR").html(result);
}
});
*/
}
</script>
No comments:
Post a Comment