2

I am making a Java gui and web application which will use the same mysql database.

It's a DTh management system where all the information will be stored and retrieved dynamically depending on input.

I believe that views are static by nature and thus would be useless as all my queries will have a different where condition (userid).

Do I need to use triggers? I mean I could code the java to execute multiple statements instead of using a inbuilt trigger (e.g. Insert in customers name and family members name both will have a duplicate copy for head of the family). Is there a performance hit? Am I wrong in some way?

And same thing what is the use of stored procedures? Can't I use methods in java to do everything?

So, I am asking is it possible to shift all the calculation intensive stuff to java and web script instead of the sql. If yes, does this mean I only have to create the backend structure of Database(i.e. all the different tables and FK,PK) and do rest without using any sql stuff on mysql workbench?

Thank you for helping.

chettyharish
  • 1,704
  • 7
  • 27
  • 41

2 Answers2

1

There is (as always) one correct answer: It depends.

If you only want to show and query some data, you probably won't need trigger or stored procedures.

Views are a different thing: They are pretty helpful if you want a static viesw to a join-table or something like that. If you don't need this, just don't use it.

Keys are really important. They make your data robust against wrong input.

What you shoud use is PrepearedStatement instead of Statement. If you only use PreparedStatements, you are (nearly ?) safe in the question of SQL-Injection.

Christian Kuetbach
  • 15,850
  • 5
  • 43
  • 79
  • All my queries will be run using Java GUI so, its like human will type information and then it will be stored while registering. While retrieval, the customer id will be required so views go out of range. So should I use a procedure during this retrieval, or stik to jave for everything? My current criteria is to get the project done and performance hit is Okay. – chettyharish Jan 31 '13 at 09:50
  • Do it in java. But use the PreparedStatement: http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html – Christian Kuetbach Jan 31 '13 at 10:24
  • Thank you for your help I will try to do everything in java – chettyharish Jan 31 '13 at 16:32
1

We use Views because it just faster than select query and for just showing data (not edit-update) it is faster and preferable.

Trigger are fired at database side so it is faster because it just execute 2 or more queries in single execution.

Same in Stored procedures, because we can execute more than one queries in single database connection. If we execute different queries than it take more time on every execution for database connection (find database server, authenticate, find database,... etc.).

Sachin
  • 2,152
  • 1
  • 21
  • 43
  • A view is nothing but a stored `SELECT` statement and can't be faster than the `SELECT` statement executed "stand alone" - unless you are talking about "*indexed views*" which is a SQL Server specific thing –  Jan 31 '13 at 10:27
  • So I believe views are practically the most useless thing ever. – chettyharish Jan 31 '13 at 16:34
  • I think for only showing data, views are very faster In SQL but in mysql it is not compulsory because MySQL does no caching of data in views.. – Sachin Feb 01 '13 at 08:09