Page 1 of 1

Java to MySQL Connection ( Using the JDBC-ODBC bridge) Rate Topic: ***** 1 Votes

#1 jeremejazz  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 22
  • View blog
  • Posts: 48
  • Joined: 23-April 10

Posted 28 December 2010 - 07:09 PM

*
POPULAR

For portability and general purposes, we will be using the Open Database Connectivity(ODBC). Connecting to MySQL with the use of Open Database is similar to connecting to MS Access only with some additional steps.
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

Is This A Good Question/Topic? 10
  • +

Replies To: Java to MySQL Connection ( Using the JDBC-ODBC bridge)

#2 Guest_fly_guy*


Reputation:

Posted 30 December 2010 - 12:30 AM

very good tutorial.. I will bookmark this one
Was This Post Helpful? 0

#3 Ryu8146  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 22-September 10

Posted 10 January 2011 - 03:37 AM

Thanks....!! love it so much... will bookmark this too... :)
Was This Post Helpful? 0
  • +
  • -

#4 Pustik  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 12-August 10

Posted 06 February 2011 - 06:24 AM

Thank you a lot, you just saved my leaving exam work:)
Was This Post Helpful? 0
  • +
  • -

#5 lexxe  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 44
  • Joined: 05-December 10

Posted 09 February 2011 - 09:02 AM

This is brilliant. My final project for my database class is to set up a database for a fake college, and we're supposed to have a Java program to go through and view, edit, and insert data, and its not a huge leap to take this tutorial and make it an insert statement. Definitely a must for anybody who wants to set up any sort of MySQL database and have a front-end software to manage it specifically.

Thanks a ton for posting this.
Was This Post Helpful? 0
  • +
  • -

#6 jeremejazz  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 22
  • View blog
  • Posts: 48
  • Joined: 23-April 10

Posted 24 February 2011 - 06:37 PM

Thanks. I hope I could post more of these
Was This Post Helpful? 0
  • +
  • -

#7 v0rtex  Icon User is offline

  • Caffeine: db "Never Enough!"
  • member icon

Reputation: 223
  • View blog
  • Posts: 773
  • Joined: 02-June 10

Posted 01 May 2011 - 11:36 AM

Thanks! Nice Tutorial
Was This Post Helpful? 0
  • +
  • -

#8 IamTw_  Icon User is offline

  • D.I.C Head

Reputation: 32
  • View blog
  • Posts: 238
  • Joined: 25-February 11

Posted 25 June 2011 - 09:32 AM

View Postjeremejazz, on 28 December 2010 - 07:09 PM, said:

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`)
) 


Is it me, or did you forget to explain where you create this table :P
I don't know where to create it. I downloaded myodbc, but all I get when opening it is a promt. Is there no GUI for it?
Was This Post Helpful? 0
  • +
  • -

#9 mak9292  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 08-May 12

Posted 08 May 2012 - 07:22 AM

this post is really very helpful..this site is rocking.here i found best code with detailed explanation.you save my day DIC.thanksssssss
:bananaman:
Was This Post Helpful? 0
  • +
  • -

#10 vbabey  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 73
  • Joined: 30-July 12

Posted 28 August 2012 - 03:52 AM

This is really a good tutorial...Easy to understand...
Keep it up...
Was This Post Helpful? 0
  • +
  • -

#11 Garg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 25-August 12

Posted 28 August 2012 - 08:46 AM

sir ,,, one problm ... i have not understand ForName() method.... please explain how write a driver name within ForName()..
please sir...........
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1