[thelist] Need help building a simple JSP/MySQL webapp

[Rick den Haan]

Hey guys,

I've been googling around a bit, but easy-to-understand JSP explanations
aren't easy to find. Let me start by saying that I'm a PHP programmer, and
this is my first time ever working with JSP, but I want to broaden my
horizons a bit and thought I'd start with something simple. Here's what I
want:

My MySQL database is called "jsp_test", in which there's a table called
"customers" which has four columns for "id", "name", "address" and "phone".
I want a JSP page that ends up listing my customers, ordered by id. I.e.:

Customers:
1. John Doe - 13, Java Drive - 555-1320
2. Jane Doe - 11, JSP Avenue - 555-5428
etc...

Normally, in PHP, I'd use a customers.php file that looks like this and does
the job:
<?
  mysql_connect('localhost','jspuser','jsppass');
  mysql_select_db('jsp_test');

  echo '<ol>';

  $sql = mysql_query("SELECT * FROM customers ORDER BY id ASC");
  while ($customers = mysql_fetch_array($sql)) {
    echo '<li>' . $customers["name"] . ' - ' . $customers["address"] . ' - '
. $customers["phone"] . '</li>';
  }

  echo '</ol>';
?>

For JSP, I have absolutely no clue where to go from what I have now. I have
an excellent Java guidebook (Y. Daniel Liang's "Introduction to Java
Programming", 5th Edition), which told me to go and download a MySQL JDBC
connector, which I did, but I don't know where to place it so that Tomcat
will be able to connect to it, nor what JSP code I have to use to connect to
it once I get it there.

Here's what I have right now (customers.jsp, adapted from an example in the
book):
<%
  //somehow load "com.mysql.jdbc.Driver"

  Connection jspdb = DriverManager.getConnection
("jdbc:mysql://localhost/jsp_test");
  Statement sql = jspdb.createStatement();

  out.println("<ol>");

  ResultSet customers = sql.executeQuery("SELECT * FROM customers ORDER BY
id ASC");

  while (customers.next()) {
    out.println("<li>" + customers.getString(1) + ' - ' +
customers.getString(2) + ' - ' + customers.getString(3) + '</li>');
  }

  out.println('</ol>');

  jspdb.close();
%>

Is someone more used to handling JSP can help me out here, please do.... Be
gentle, as this is my first time working with this language. I might be
thinking too much along PHP's mindset here...

Thanks in advance,
Rick.