We will first be creating a table in mysql for our example:
CREATE TABLE `humans` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `LastName` varchar(90) NOT NULL, `FirstName` varchar(90) NOT NULL, `Gender` varchar(90) NOT NULL, `Address` varchar(255) NOT NULL, `Phone` varchar(45) NOT NULL, PRIMARY KEY (`ID`) )
You can also use this backup file[attachment=21419:test_backup 20070101 0837.zip]
Installing the Driver
1. Download MySQL ODBC Connector. I have a copy for windows users at www.mysqlodbccon.4shared.com . Please refer to the links below for more info.(Don't bother the filename if it says noinstall, you're still going to be installing it anyway)
2. Extract the Zip File and Run Install.bat (see, like I told you... although the batch (.bat) file copies the library(.dll) files into your operating system unlike real installers where they involve file extraction)
Preview
3. A message will prompt that the instalation (copying of libraries) was successful.
Setting up the Data Source
1. We will now create a Data Source Name(DNS) which will be used as a reference by the program. First go to your control panel then Administrative Tools > Data Sources(ODBC)
Preview
2. The ODBC Data Source Dialog will then appear, select the System DSN tab.
Preview
3. Click on the Add button then another Dialog Box containing the Drivers will appear. Select MySQL ODBC Driver then click Finish.
Preview
4. Another Dialog Box will appear for the final configuration. Just fill up the neccessary inputs, the Data Source Name that you will be using on the text box. The Server should contain the local server name(you can use localhost or your computer name). Enter the user and password fields, and the database that you will be using as source.
Preview
Coding in Java
We will now explain the coding for connecting to the database
1. First import the neccessary libraries for Database connection, java.sql:
import java.sql.*;
These are the necessary tools we will be needing:
Connection - This represents the connection to a specific database. This is used to establish the connection with the database drive.
Statement - This is used to execute the SQL statement.
ResultSet - This holds the table of the data that is generated from an sql statement. For statements other than select, this is not neccessary used since the statement would not return a table.
2. Then instantiate your connection variables
Connection con; Statement stmt; ResultSet rs;
3. On your procedural method, create an exception handler
try{
//Your Code Goes Here
}catch(Exception e){
System.err.println(e);
}
To make your exception handling more specific, you can add 2 more catch before Exception containing SQLException and ClassNotFoundException
4.In the try block, insert your connection code, First is the forced loading of the Database Driver in Class.forName() followed by the declaration of objects we instantiated earlier.
//Load the JdbcOdbc Driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Specify the Database URL where the DNS will be and the user and password
con = DriverManager.getConnection("jdbc:odbc:TOY2","root","root");
//Initialize the statement to be used, specify if rows are scrollable
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
//ResultSet will hold the data retrieved
rs = stmt.executeQuery("SELECT * FROM Humans");
on our con.createStatement() method, we had 2 parameters, (these are optional but it would be recommended for you to add this for some convenience): ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY
According to javadoc, TYPE_SCROLL_SENSITIVE is a constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes made by others. I add this to make the buffer of the ResultSet move back to the beginning whenever needed. While CONCUR_READ_ONLY is The constant indicating the concurrency mode for a ResultSet object that may NOT be updated. I only use this for retrieving data,, this also ensures that no changes will be made on the database.
Then we now get the data from the ResultSet object
//Display the results
while(rs.next()){
System.out.println(rs.getInt("ID") + " " + rs.getString("LastName") + " " + rs.getString("FirstName"));
}
For a list of methods for ResultSet, please refer to the Java Documentation
For a full source code listing:
import java.sql.*;
public class ViewingMySQL {
public static void main(String[] args) {
Connection con;
Statement stmt;
ResultSet rs;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:TOY2","root","root");
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery("SELECT * FROM Humans");
while(rs.next()){
System.out.println(rs.getInt("ID") + " " + rs.getString("LastName") + " " + rs.getString("FirstName"));
}
}catch(Exception e){
System.err.println(e);
}
}
}
For more information about MySQL ODBC 5.1 Driver, including
installation instructions, please visit;
Mysql.com(odbc)
I also have a tutorial in squidoo.com
MultiQuote
| 
