VALUE MANIPULATION IN MySQL with JAVA question..

[justineee]

Hello, I am creating a mini accounting software that adds account
names and their respective values..
I use mySQL as my database for this project and I am successful in
making tables for each account name upon input of the user. The table
for each account name has four columns (date, debit, credit and
balance). For each row the balance column should be equal to debit-
credit. However, I'm having problems in doing this. This is my code in
getting the balance using java with SQL

public int balance(String tablename)
{
try
{
String get = "SELECT * FROM "+tablename+"";
db.connect();
stmt = db.conn.createStatement();
rset = stmt.executeQuery(get);
int totaldeb = 0;
int totalcre = 0;
int total = 0;
int balance = 0;
int temp = 0;
int ctr = 0;


while(rset.next())
{

totaldeb += rset.getInt("debit");
totalcre += rset.getInt("credit");
total = totaldeb - totalcre;
balance = total;

}

stmt.close();
db.disconnect();
return balance;

}
catch (Exception e)
{
System.out.println(e.getMessage());
}
return -1;
}

and here is how I insert the values in the table in SQL

//for debit

public void insertValuesDeb(String tablename)
{
insertToDb = "INSERT into "+tablename+" VALUES('"+getDate+"',
"+getDebitAmount+",'0',"+balance(tablename)+")";
insert(insertToDb);
}

//for credit

public void insertValuesCred(String tablename)
{
insertToDb = "INSERT into "+tablename+" VALUES('"+getDate+"',
'0', '"+getCreditAmount+"', "+balance(tablename)+")";
insert(insertToDb);
}

When I insert values.. Here is an example result in mySQL

date debit credit balance

jan 1 23 0 0
23 0 23
0 0 46
0 1 46
0 0 45

In this example, the problem here is that the result of the balance is
not in the right row.. In the first row, I put 23 on debit and 0 on
credit but the balance is 0. On the second row, I put 23 again on
debit and 0 on credit and the balance is 23 (the result of balance
here is 23, it came from the result of the first row). And on the
third row, the result of balance is 46 which should be on the second
row..

I want to know what I'm doing wrong with my balance method typewritten
above. I have tried this without the sql with the same concept.

This is my code for my test without sql.

import java.util.*;

public class Extra
{
public static void main(String[] args)
{
Scanner s = new Scanner(System.in);
int totaldeb = 0;
int totalcre = 0;
int total = 0;
int balance = 0;
int ctr = 0;

while (ctr < 6)
{
totaldeb+=s.nextInt();
totalcre+=s.nextInt();
total = totaldeb-totalcre;
balance=total;
System.out.println(balance);
ctr++;
}

}
}

This worked for me.

But I really can't do it right with sql. Any help please?

 

[Lew]

public void insertValuesCred(String tablename)
{
insertToDb = "INSERT into "+tablename+" VALUES('"+getDate+"',
'0', '"+getCreditAmount+"', "+balance(tablename)+")";
insert(insertToDb);
}

http://xkcd.com/327/

Never use SQL this way.

Two other comments: SSCCE, and third-normal form.

 

[justineee]

To Mr. Matt,

you need an additional timestamp or
transaction id for that.

What do you mean by this? How am I going to do this?

Is there a reason you recompute the entire balance on each transaction
(full
debits and credits) rather than simply get the balance of the previous
transaction?

I think I need to recompute the balance on each transaction because I
am doing this for the three column running balance for the ledger.





To Mr. Lew,

I'm so sorry. I'm just new with sql and for the SSCCE, this is the
best SSCCE I can do. I don't really know how to make my example like
in the SSCCE..

 

[Owen Jacobson]

http://xkcd.com/327/

Never use SQL this way.

Two other comments: SSCCE, and third-normal form.

Lew's right. _Never_ build SQL using string concatenation or variable
substitution. Consider:

PreparedStatement insertCredit =
conn.prepareStatement
("INSERT INTO credits VALUES (?, '0', ?, ?)");
try {
insertCredit.setDate (1, getDate);
insertCredit.setBigDecimal (2, getCreditAmount);
insertCredit.setBigDecimal (3, balance);

insertCredit.executeUpdate ();
} finally {
insertCredit.close ();
}

There is absolutely no chance for badly-formatted data to turn into an
SQL injection problem, because the database driver (as specified by the
JDBC spec) handles the parameters separately from the query itself,
including any quoting or escaping necessary.

It's worth noting that you are not permitted to use a ? placeholder for
table or column names: you can't portably prepareStatement ("SELECT *
FROM ?"), for example. You're expected to know in advance what your
queries are accessing. ORMs and other tools that generate SQL on the
fly go to great lengths to ensure the result is safely executable even
in the face of weird data. If for some reason you absolutely cannot
avoid dynamically-generated SQL, use iBatis, rather than writing it
yourself.

I also strongly object to the implied data model: one-table-per-ledger
will break down very badly as your number of ledgers grows, or if you
need to relate anything to ledger items. Use a table structure that
includes the ledger key in the rows, rather than in the table name, and
make it part of the table's primary key.

There are some performance advantages in certain situations to
physically sharding data across multiple tables - but the application
should never be aware of it. Use views and query rewriting, or stored
procedures, to map sharded physical data models to a uniform logical
data model that apps actually use.

-o

 

[alexandre_paterson]

http://xkcd.com/327/

Never use SQL this way.

Two other comments: SSCCE, and third-normal form.

This...

And in addition to that:

http://en.wikipedia.org/wiki/Object-relational_mapping

and:

http://en.wikipedia.org/wiki/Hibernate_(Java)


Even for a "small project", you're losing a lot
and re-inventing a (broken) wheel by rewriting
an ORM.

 

[Lew]

justineee wrote
Do you really need the historic balance as of each transaction, or
only the current balance?

Consider that every time you insert you have to read the entire contents of
the account.  If the user has 2 transactions per day for 5 years that's 3000
terms you read and add up every time you insert a new record.  What makes
this particularly wasteful is that the number you are computing is already
present as the balance of the previous transaction.  You can write a
getBalance () query that simply finds the transaction with the largest id
and returns the balance of that row.

To normalize further and more correctly, keep the current balance in a
separate table, IDed by the account. Correlate the individual
transactions to the account table via a foreign key.

Every row will have a unique number here. Most databases have a way to
automatically generate this key--just make sure it is sequential and
increasing. Look for a column attribute called "auto increment" and set the
first row to 0. After that each insert adds one automatically.

Each insert adds at least one; it's not guaranteed to add only one.

This may be more than you need to know yet, justineee, but that auto-
incremented key should not be visible to the view layer. It is
strictly a data-layer internal housekeeping construct, an artificial
key that stands in as a surrogate (a "surrogate key") for the key
expressed in business-domain terms. It is the business-domain key, or
"natural key", that should be visible to users. The natural key can
and often does involve more than one column of the table. The
business logic can keep track of the correlation between the natural
key and the internal surrogate key.

Surrogate, auto-incremented (sequenced) keys exist to be smaller and
faster than natural keys, and to simplify handling of certain rare
data anomalies. Not everyone approves of them. In your ledger
application, you might need to invent a user-visible "transaction
ID". Such a key might be a numeric string (not a number, because you
never do calculations with it), or some formatted combination of date,
time and sequence, or something arbitrary but sensible to users.

For your needs, follow Matt's advice, including careful study of third
normal form. Do avoid revealing internal keys to users.

 

[Lew]

This...

And in addition to that:

http://en.wikipedia.org/wiki/Object-relational_mapping

and:

http://en.wikipedia.org/wiki/Hibernate_(Java)


Even for a "small project", you're losing a lot
and re-inventing a (broken) wheel by rewriting
an ORM.

As long as you're discussing Hibernate, there're at least two other decent JPA
products, OpenJPA and TopLink.

 

[Arne Vajhøj]

This...

And in addition to that:

http://en.wikipedia.org/wiki/Object-relational_mapping

and:

http://en.wikipedia.org/wiki/Hibernate_(Java)


Even for a "small project", you're losing a lot
and re-inventing a (broken) wheel by rewriting
an ORM.

Creates ones own ORM is bad.

Using direct JDBC instead of ORM can be justified in
some cases.

Arne