Created a Java Web app/MySql app

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 开发者_JS百科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?

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():

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.