aspose file tools*
The moose likes JDBC and the fly likes JSP Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JSP Query" Watch "JSP Query" New topic
Author

JSP Query

Joseph Maina
Greenhorn

Joined: Dec 02, 2013
Posts: 8
Am having trouble posting string data to mysql and displaying it correctly in a browser. I have a JSP, which takes input from a user. The data is then posted to Mysql database. If the string has an apostrophe or double quotes, the Mysql throws an error. How do I solve this issue since the user might put those characters that might generate an error? Also how do I display such data which has such characters correctly if I manage to post it with the mysql escape characters?
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 892
    
    9
How are you putting the values into the database? If you are using JDBC then you should use a PreparedStatement because
1.) It will escape the special characters for you
2.) It protects you from SQL injection where a user can provide input like "input;delete from transactions;drop table users;" e.t.c
3.) Some caching is done which makes future executions faster.

You also are hopefully not doing the database code in a JSP. It should be a normal Java class called from a servlet.
Joseph Maina
Greenhorn

Joined: Dec 02, 2013
Posts: 8
Thanks. Am using <private Statement statement;>. Should I change this to < private PreparedStatement statement;> and the issue will be resolved? If there is some more coding needed in order to use the PreparedStatement, would you kindly assist?

Am not doing the database code in a JSP but in a Java class called as a bean through JSP useBean functionality.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42282
    
  64
Should I change this to < private PreparedStatement statement;> and the issue will be resolved?

No, that's not sufficient. Read http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html to learn all about it.


Ping & DNS - my free Android networking tools app
Joseph Maina
Greenhorn

Joined: Dec 02, 2013
Posts: 8
I appreciate.
Best regards.
Joseph Maina
Greenhorn

Joined: Dec 02, 2013
Posts: 8
Here is my code:



Is there something wrong with the logic of the code?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42282
    
  64
What leads you to believe that anything is wrong with the code? It certainly is rather verbose for something as basic as executing a single SQL statement, but that shouldn't impact its functionality. But it does make it harder to read and spot problems.

I notice that you're not using the PreparedStatement correctly - you're not setting any parameters.
Joseph Maina
Greenhorn

Joined: Dec 02, 2013
Posts: 8
What parameters would that be and how do I set them?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42282
    
  64
I don't know what parameters your query has, but you mentioned that it includes input from users, so it must have at least one. The article I linked to describes how to set parameters in PreparedStatements.
J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 1001
    
  13

Joseph Maina wrote:What parameters would that be and how do I set them?

The values that you want to insert. Don't pass the entire SQL string.

Let me take a shot at this. First, what you have here is not a bean, it's a DAO, Data Access Object. Beans are used to pass data around. They just have fields with getters and setters.

Here's how I typically setup a DAO. Define the SQL for your insert statement.


The values are going to be passed to your DAO. They could be passed individually or in a collection. Whatever. For this example let's say you passed in a bean called empBean. Setup your connection whatever way works for your. As Ulf pointed out yours is rather verbose, but if that works for you that's fine. Now for the Statement. We'll assume you already have a connection called "connection".


Each of those "setString" calls corresponds to one of the question marks in the PreparedStatement. They have to match in number and data type. If your SQL string has 12 questions marks you have to do 12 "set" methods. If the database data type for that field is an int, then you do a setInt.

Returning the result code is optional, but it provides a way for the servlet to make sure everything went okay and provide some feedback to the user. I often evaluate it to display a simple message on the page such as "Record inserted successfully" or something like that.

I hope this helps.


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." -- Ted Nelson
Joseph Maina
Greenhorn

Joined: Dec 02, 2013
Posts: 8
@ J. Kevin Robbins;

Thanks Robbins. That sets things into perspective, especially for starters like me.

The challenge I have is that I have various JSPs posting data to different tables in a database each with its unique schema. I needed a single DAO that can handle all the posting without having to define each string variable for each database table, like in the example given; private final static String INSERT_EMPLOYEE_RECORD . Is there a way to do this or I have to define the SQL insert statement for each table?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42282
    
  64
I advise to keep DB statements separate for each table. They can still be part of the same DAO.
J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 1001
    
  13

You can define as many SQL statements as you want. My DAO's typically will have a statement for inserts, another for deletes, another for updates, and then perhaps several selects such as one to select all records and one to select a single record and one to select records within a given date range. Usually each of these will be called by a separate method because each will have different parameters.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JSP Query