Dear all, I am a fresh JSP leaner, i would like to know how to construct the SQL statement in JSP. I have no problem with the database connection, but a statement problem. Let's say, i have a table (created in MS-Access) ABC, with 2 fields, ID (Number data type), Name (Text data type). How can i construct a statment to find a Name based on Id, which is captured from a HTML form ? Thank you for your help I am looking for your help ! thank you in advance !
If you're just starting out, I highly recommend finding a good book or tutorial on JSP/Servlets (and if you haven't already, Java). Use the search tool on this and/or the servlets forum with the keyword "book" and you'll find plendy of recommendations.
Thank you very much for your all recommendation ! However, i encouter a problem "Data type mismatch in criteria expression." when i follow your instruction ! Please help me with this problem ! Thank you very much !
(note, I have URL-encoded the special value for id).
What is your SQL statement going to look like? Like this:
SELECT * FROM t_person WHERE id = '0' or '1'='1'
Suddenly it returns all the rows in the table t_person.
Maybe that isn't a big problem with this SQL statement, but suppose you have another JSP page that's meant to delete a row from the database. If you would build the DELETE statement the same way as you did above, a hacker could let your JSP page delete the contents of a whole table by carefully choosing a value for the parameter.
This topic of sql injection comes up a bit. Can't sql injection also be avoided by doubling up on single quotes that are part of data
Maybe, but then you're escaping the SQL by hand. Why wouldn't you want to let PreparedStatement take care of it automatically? Also, using PreparedStatement is potentially more efficient, since the driver and/or database can cache the SQL statement - if you're sending it a new statement everytime, it has to interpret the whole statement every time.
There is one compelling reason to use Statements over PreparedStatements. If you use Statement you can abstract your jdbc calls (eliminate explicit use of Connections/PreparedStatements/ResultSets), and also abstract your query passed type.
For example the posted code below that uses PrepareStatement requires you to deal with Connections/PreparedStatements/ResultSets for EVERY unique query in your application. I have seem many developers not close connections or introduce other bugs due to writing such error prone code.
If you use Statments you can write code that abstracts the JDBC calls out. For example in my openSource project (fdsapi.com) I have a DataAcccess class. This class has a method that takes any SQL Query and converts it to an Object (an ArrayList too) without the developer having to open Connections/Statments/ResultSets. The class below called the ResultSetConverter contains the resulting array.
Also, using PreparedStatement is potentially more efficient...
Whether you get performance gains from PreparedStatements/Statements is product/version dependent. Also in the web world most PreparedStatements are used by one request and then returned to the garbage collector so they may actually perform worse than Statements. Also, for Sybase I even compared Statement/PreparedStatement performance and saw NO performance difference even when the PreparedStatement was reused!
If you look at my live demo available at http://www.fdsapi.com you can run live queries and go to the JAMon stats page and look at performance. You can see with HSQLDB (my demo database) Statements are very fast also.
So I think those are some compelling reasons to consider Statements.
Originally posted by Tien Thinh Nguyen: Thank you very much for your all recommendation ! However, i encouter a problem "Data type mismatch in criteria expression." when i follow your instruction ! Please help me with this problem ! Thank you very much !
Im guessing the 'data type mismatch' error is because your database id is in number format and the sample code stores its id as a String ie text.
Why is the word "abbreviation" so long? And this ad is so short?
Devious Experiments for a Truly Passive Greenhouse!