Created a Java Web app/MySql app

Question

Started coming up with a java web app for online user interaction. Decided to use a MySql DB for data storage. I have already created the tables with the proper/expected data types. My question is I always thought the next step would be to creat stored procedures like Search/Add/Delete/etc.. that the user could envoke from the page. So in my java code I could just call the procedure ex:

CallableStatement cs;
Try 
{
  String outParam = cs.getString(1);     // OUT parameter

  // Call a procedure with one in and out parameter
  cs = connection.prepareCall("{call SearchIt(?)}");
  cs.registerOutParameter(1, Types.VARCHAR);
  cs.setString(1, "a string");
  cs.execute();
  outParam = cs.getString(1);    
}
catch (SQLException e) {
}

but if my application was not in the need for stored procedures because the user actions would be simple enough to execute simple tedious queries. How could I set up my Java and Sql code to handle that. Could I just have the "Select" or "Update" statements in my code to manipulate the data in my MySQL DB. If so how would that syntax look like?

Answer 1

Just use Statement, or PreparedStatement.

http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Statement.html

In a similar way to what you did, just call :

Statement stm = Connection.createStatement();

then execute your SQL :

stm.execute("SELECT * FROM MYTABLE");

grab the resultset and check out the results.

Beware though - this is bad bad as far as security goes - as others have mentioned, PreparedStatements are a bit more secure, but still not 100%.

To be honest, although basic JDBC is pretty simple, I really hate all the SQL strings littered around your code. If you want something a bit more elegant have a quick look at hibernate - it hides all the hackiness from you, and is also pretty easy to setup.

Answer 2

This URL has documentation on using prepared statements which is what you want to use to avoid security flaws (SQL Injection and such).

http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

here's an example from that page

PreparedStatement updateSales = connection.prepareStatement(
        "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate():