AF
HomeTagSubmit NotesAsk AnythingLoginSubscribe Us
AF
1. Feel Free to ask and submit anything on Anyforum.in and get satisfactory answer
2. Registration is not compulsory, you can directly login via google or facebook
3. Our Experts are looking for yours ?.



corejava-jdbc: How to get Balance of Account

eg:

Sr.....Accountno.......Amount...Atype Balance.
1 44444 50 Deposit 50
2 44444 100 Deposit 150
3 44444 20 Withdraw 130
4 44444 40 withdraw 90
like this

<%@ page contentType="text/html; charset=iso-8859-1" language="java"
import="java.sql.*,java.util.*" errorPage="" %>
<%@ page autoFlush="true" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.util.Calendar" %>
<%
//java Code
try
{
String date = (new java.util.Date()).toString();
String Username = request.getParameter("Username");
int Account = Integer.parseInt(request.getParameter("Account"));
int Amount = Integer.parseInt(request.getParameter("Amount"));
int Balance = Integer.parseInt(request.getParameter("Balance"));
String AType = request.getParameter("AType");
String sDate = request.getParameter("sDate");

//int update = 0;
String s="jdbc:odbc:Database1";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection(s);
Statement st = con.createStatement();
Calendar calendar = Calendar.getInstance();
java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

String bal_sql = "SELECT Balance FROM Tata WHERE
Username=´"+Username+" ´ And Account =´"+Account+" ´ ";
ResultSet result = st.executeQuery(bal_sql);

while(result.next())
{
Balance = result.getInt(1);
if(Balance == 0)
{
System.out.println("Current Balance : "+Balance);
Balance = Balance + Amount;
}
else
{
Balance = Balance + Amount;

String sql_up = "UPDATE Tata SET WHERE Username="+Username+" And
Account ="+Account+" ";
update = st.executeUpdate(sql_up);
con.close();
}
}
String sql = "INSERT INTO Tata (Username, Account, Amount, AType,
Balance,sDate ) VALUES (´"+Username+"´,"+ Account+","+
Amount+",´DEPOSITE´,"+Balance+", ´"+date+"´ )";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,Username);
ps.setString(2,Account);
ps.setString(3,Amount);
ps.setString(4,"DEPOSITE");
ps.setString(5,Balance);
ps.setDate(6,startDate);
update = ps.executeUpdate(sql);

}
catch(Exception ex)
{
System.err.println(ex.getMessage());
}
%>

Answer the above query as early as possible.

corejava x 336
jdbc x 32
Posted On : 2016-11-24 07:14:55.0
profile MOHAMMAD SALEEM BASHA - anyforum.in MOHAMMAD SALEEM BASHA
266150
up-rate
4
down-rate

Answers


As per my understanding you should write logic like this:

Balance = result.getInt(1);
if(Atype.equalsIgmoreCase("Deposit") )
{
System.out.println("Current Balance : "+Balance);
Balance = Balance + Amount;
}
else if(Atype.equalsIgmoreCase("Withdraw") )
{
if(Balance>=Amount){
System.out.println("Current Balance : "+Balance);
Balance = Balance - Amount;
}else{
System.out.println("Unable to dispense.");
}
}

After this change the update query as below:
UPDATE Tata SET Balance="+Balance+" WHERE Username="+Username+" And
Account ="+Account+" ";

and Insert Query must be like this:
"INSERT INTO Tata (Username, Account, Amount, AType,
Balance,sDate ) VALUES (?"+Username+"?,"+ Account+","+
Amount+",?"+Atype+"?,"+Balance+", ?"+date+"? )";

and there is no need to set any parameter as you are directly putting value in sql statement so you can comment on below lines:
ps.setString(1,Username);
ps.setString(2,Account);
ps.setString(3,Amount);
ps.setString(4,"DEPOSITE"); //It must be ps.setString(4,Atype);
ps.setString(5,Balance);
ps.setDate(6,startDate);


If you want to execute query with parameter then change the Insert query like this:
"INSERT INTO Tata (Username, Account, Amount, AType,
Balance,sDate ) VALUES (?,?,?,?,?,?)";

Posted On : 2016-11-24 23:46:34
Satisfied : 1 Yes  0 No
profile Rishi Kumar - anyforum.in Rishi Kumar
523185319357
Reply This Thread
up-rate
0
down-rate
Comments
Use select query to get last closing balance like this:
"SELECT Balance FROM Tata WHERE
Username=?"+Username+" ? And Account =?"+Account+" ? order by sDate desc";

and instead of while(rs.next()) use if(rs.next())
profile Rishi Kumar - anyforum.in Rishi Kumar
523  1853  19357
Posted On :2016-11-24 23:51:41.0
Leave a Comment



Post Answer
Please Login First to Post Answer: Login login with facebook - anyforum.in