SQL Useful Links, CISC474, 06S

See also: Reading Notes for Sebesta, Chapter 14

The reading notes for Sebesta Chapter 14 contain information on how to access the MySQL database on jaguar.cis.udel.edu, where to obtain your password (it is different from your strauss and porsche passwords!), and other helpful information.

Example using JDBC with MySQL in a Servlet

Under the Safari terms of service, instructors are permitted to make up to two sections of Safari content available online, provided access is restricted to their students:

The following excerpt from an O'Reilly book is provided under those terms

• Example of a Servlet using JDBC (only available from UD IP addresses)
• To access this from off-campus, go through the UD Proxy Service (proxy.nss.udel.edu) to authenticate yourself first, and use this URL:
  • http://www.udel.edu/CIS/restricted/474/pconrad/06S/topics/sql/JDBC.Servlet.Example.html
• My version of that same servlet
  • as a tar file
  • as browsable code
  • running on strauss and also running on porsche
• My version is modified from the original as follows
  • it includes a build.xml file suitable for use on strauss or porsche
    (you need to edit the global.properties to use it on porsche)
  • it includes a companion servlet to initialize the database
  • it includes code and modifications to make the result validate as XHTML 1.1
  • it fixes a few minor bugs in the code to display random comments

Using MySQL with JDBC

Connector/J is what makes JDBC work with MySQL

To access a MySQL database from Java, the most basic method is to use JDBC. To do this, you need a software component called Connector/J. As of Spring 2006, the most recent generally available version (according to the MySQL download page) is Connector/J 3.1

Documentation about using JDBC with
Tomcat 5.0, MySQL 5.0 and Java 1.5

• Tomcat 5.0 documentation on working with JDBC
• MySQL 5.0 documentation on working with JDBC and Connector/J
• An older tutorial from Sun on JDBC

See also, topics/tomcat/usefulLinks.htm#MySQL

SQL Tools

• SQLyog is a GUI front end to SQL databases recommended by CISC474 alum Matt Cole. A free version is downloadable from www.webyog.com.

Configuring JDBC

• The role of JNDI in J2EE: Decoupling yourself from trouble.
  
  By Kirk Pepperdine (kirk@javaperformancetuning.com), Chief Technical Officer, JavaPerformanceTuning.com, and Joseph Ottinger (joeo@enigmastation.com), Consultant From IBM Developer Works.
  
  • A tutorial on using JNDI so that you don't have to hard code your username, password, port number, etc. etc. into your source code.
    
• Using JNDI Outside J2EE. (Sample chapter from from the book Sams Teach Yourself J2EE in 21 Days, 2nd Edition))
  • Might be particularly helpful for figuring out how to access JNDI resources from a model class that should not depend on being part of a webapp, and therefore needs some way other than the deployment descriptor to define JNDI resources.
  • Describes how to define a file jndi.properties in your classpath containing definitions of properties.

SQL Security

SQL Injection Attacks

• An article by Chris Shiflett about SQL Injection in PHP applications (but broadly applicable to any webapp with an SQL backend, whether written in PHP, or some other language such as Java).
• An article on SQL Injection from Robert Hurlbut's SQL Server Blog. Contains several links to further information.

JDBC "Best Practices" Articles

• Scalability and Performance: JDBC Best Practices and Pitfalls, by Julia Gutjahr and Andreas Loew of Sun Microsystems. Recommended by Pete Steijn, a former CISC474 student now doing undergraduate research on connection pooling.
• Another JDBC best practices tutorial from a site called PreciseJava.com