New
Database Connection
Peter Komisar
Conestoga
College
What's new with
MySQL
Sun recently bought MySQL. This almost certainly
guarantees a high integration plan for MySQL in use
with both Sun platforms and Java.
As an example, already claims such as the following
are being made with respect to the 8086 platform;
" Improve performance by more than 50% and reduce
database size up to 40%, regardless of operating system".
MySQL is originally a project created in C. Sun has
armies of C programmers so it is easy for them to
optimize the database for their products.
Download
MySQL
Go to mysql.com --> downloads
Download the MySQL Community Server for Windows.
I did the one with the installer. Pick a mirror. You don't
have to log in or register if you would rather not.
Install MySQL with defaults. Pick an easy password for
this academic exercise. 'root', 'admin' etc. In an actual
deployment you should use a strong password.
To Test
Start the command line client from Program Files.
Enter your password. Type in ' show databases; ',
(without the quotes) and press enter. If you see three
databases you are in business.
Download the JDBC
Driver
Go to: http://dev.mysql.com/downloads/
Find Connectors in the left hand menu. Download
a current version of Connector/J. Unzip it. Find the
jar file and put it in the /jre/lib/ext directory under your
home JDK directory.
Load
the Database
From the command line, create a database, a table
and insert an entry into the table via the following or
a similar script.
SQL Script
mysql>
create database autos;
use autos;
CREATE TABLE Cars
(
InventoryNo INTEGER NOT NULL,
Make
CHAR(40) NOT NULL,
Model
CHAR(40) NOT NULL,
Year
CHAR(10) NOT NULL
);
INSERT INTO Cars
VALUES ( 82123,
'Ford',
'Focus',
2000
);
select * from cars;
// press enter
if needed
Open the Database Perspective
Eclipse supplies a Database Development Perspective.
Go to Window --> Open Perspective --> Other
Database Development.
In the left hand window, above where the Databases
hierarchy is shown, are a row of icons. The fourth one
over, that looks kind of like a pair of chain links is the
New Connection Profile button. Click it and many
potential databases are shown.
You can also arrive here by right-clicking the Databases
folder and clicking 'New'.
Select and doublle click MySQL.
Enter your password and perhaps click save.
Find the triangle icon click. This allows you to edit
the driver information. Go to properties.
Change the word database to whatever you named
your database. If it was 'autos' as in the above script,
then change it to autos.
Connect
Under Databases You should see a MySQL listing.
Right Click and press connect if you are not already
connected. 'Drill down' until you find databases and
the table that was connected. Right click on it and you
will find Edit. Edit the table by adding three more entries.
Press Save. You should see the SQL statements that
are executed recorded in the lower left hand panel.
//
Screen shot!
Run
a Java Test
The following Java class confirms that the driver is
available inside Eclipse.
Java Sample to Test For Driver
Access
import java.sql.*;
public class S {
/**
* @param args
*/
public static void main(String[] args) {
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("We have the
Driver, Houston!");
}
catch(Exception e){
System.out.println("We have a problem Houston!");
}
}
}
Run it as a Java application from inside Eclipse.
Capture the output in a screenshot.
Run
a Java Application Against the Database
If the above code runs, then try the following code
against your code. This is an adaption of a sample
that was created in 'Just Java' by Peter Van der
Linden a while back.
Notice, that we can use a complete URL or one
that relies on data supplied internally by Eclipse
in it's properties. (Once the protocol of url is stated
it becomes a sort of relative path.)
Example
import java.sql.*;
public class S {
public static void main(String[] args) {
String url =
"jdbc:mysql://localhost:3306/X";
// a sort
of relative version of the url also works
// String url ="jdbc:mysql:///X";
String query =
"SELECT * FROM Cars";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection( url,"root","root" );
Statement stmt = con.createStatement( );
ResultSet rs = stmt.executeQuery(query);
printResultSet(rs);
rs.close( );
stmt.close( );
con.close( );
}
catch(SQLException ex){
while (ex != null){
System.out.println("SQL Exception: " + ex.getMessage( ));
ex = ex.getNextException();
}
}
catch(java.lang.Exception ex){
ex.printStackTrace( );
}
}
private static void
printResultSet(ResultSet rs) throws SQLException{
int numCols =
rs.getMetaData( ).getColumnCount( );
while( rs.next( ) ){
for ( int i = 0; i < numCols; i ++ ){
System.out.print(rs.getString(i+1) + " | " );
}
System.out.println( );
}
}
}
Database Connection From Web
Applications
Now that we are connected, tried and tested in Eclipse to
MySQL it is a short step to accessing the database data
from Servlets and JSPs. Now that Eclipse has the handle
to the database, we can run the Servlet from Eclipse and
access the database.
Servlet Accessing a Database
Example
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class WebDB
*/
public class WebDB extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public WebDB() {
super();
// TODO Auto-generated
constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest
request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter
out=response.getWriter();
ResultSet rs=getDBResults();
try{
outputResults(rs,out);
}
catch(SQLException sqle){
out.println("Exception: " + sqle.toString());
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest
request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
super.doPost(request, response);
}
///////////////////////
ResultSet getDBResults(){
ResultSet rs=null;
String url ="jdbc:mysql:///X";
String query = "SELECT * FROM
Cars";
try{
Class.forName("com.mysql.jdbc.Driver");
//
Attempt to connect to a driver
Connection con =
DriverManager.getConnection( url,"root","root" );
//
Create a Statement object so we can submit SQL statements to the driver
Statement stmt =
con.createStatement( );
//
Submit a query, creating a ResultSet object
rs =
stmt.executeQuery(query);
//
Display all columns and rows from the result set
}
catch(SQLException ex){
while (ex != null){
System.out.println("SQL Exception: " + ex.getMessage( ));
ex = ex.getNextException();
}
}
catch(java.lang.Exception ex){
ex.printStackTrace( );
}
return rs;
} // end of
method
////////////////////////
void outputResults(ResultSet rs, PrintWriter out)
throws SQLException{
out.println("<html>");
out.println("<head>");
out.println("<title>Maps
Page </title>");
out.println("</head>");
out.println("<body>");
out.println("<h1>Database
Data!</h1>");
out.println("<HR/>");
int numCols = rs.getMetaData(
).getColumnCount( );
while( rs.next( ) )
{
for (
int i = 0; i < numCols; i ++ )
{
out.print(rs.getString(i+1) + " | " );
}
out.println( );
}
out.println("</body>");
out.println("</html>");
}
void doClose(Connection con,Statement stmt,ResultSet
rs){
try{
rs.close();
stmt.close();
con.close();
}
catch(SQLException
sqle2){sqle2.toString();}
}
}
Assignment
Supply the Screenshots for the edited database in the
Eclipse Database perspective marked above. Open the
command line client in MySQL and confirm the inserts
were executed and that the new data is in the database
by executing the following SELECT statement.
Check Database For Changes From
MySQL Command Line Client
Use database autos;
Select * from Cars;
Screen shot the output.
Screenshot the Java Output from the Java class you
created above.