Solved

Basic Java Code to connect to a MS Access table

Posted on 2002-05-22
14
839 Views
Last Modified: 2012-06-27
I need a simple example of Java connecting to a MS Access database table on the same desktop PC.

I would like to be able to add, delete, change records in the Access table.  

Will add more points if your willing to help me learn more.

Thanks,
DanC3
0
Comment
Question by:DanC3
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 18

Expert Comment

by:bobbit31
Comment Utility
0
 

Expert Comment

by:lhshyong
Comment Utility
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

You can use this code to connect to MSAccess through ODBC.
You also need to setup in the ODBCDataSource,

then you can use package java.sql to update, select..with the database.
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
 First of all, the simplest example, using type 1 drivers (i.e. ODBC:JDBC bridge) you have to configure a MS-Access database to use. You can do it from Start.. Settings... Control Panel... ODBC Data sources.

  After you configure the database you need to load the drivers using:

// Load the JDBC-ODBC bridge driver
try {
      Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
    } catch( ClassNotFoundException ee) {  
        ee.printStackTrace();
    }

and connect to the database using:

 // Connect to the database
Conenction con = DriverManager.getConnection("jdbc:odbc:<the name of your dataabse here>", "<username>", "<password>");

  where username and password are the suername and password required to connect to the database.

  After this you have to create a statement object like:

Statement stmt = con.createStatement();

 and send simpel SQL commands like:

ResultSet rs = stmt.executeQuery("SELECT * FROM <table name>");

  where table name is the table you want to access the data from. Teh ResultSet contaisn all the results that you get back from the tabale.

  Hope it helps.
0
 

Author Comment

by:DanC3
Comment Utility
I'm new to Java and I need a working example if possible.

I can create the Access database/table easy enough.
If I had a complete a ?.java I can modify it down to meet my needs.

Anyone have something close?
0
 
LVL 1

Expert Comment

by:raid999
Comment Utility
I dont know what operating systems are you using but any way go to the control pannel and then try to find the ODBC then create a connection from odbc to the Access database and then you can use this code in java:

try{

                    Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

               }catch(Exception e){

               }

               try{
                    String url = "jdbc:odbc:UGC";
                    Connection con = DriverManager.getConnection(url);
                    Statement stmt = con.createStatement();
                    String dateid;
                    String date;
                         //list categories
                         ResultSet rs = stmt.executeQuery("SELECT * FROM dates");
                   

                         while(rs.next()){
                         
                              date=rs.getString("Date");
                              dateid=""+rs.getInt("Date_ID");
                              out.println("<p align=\"left\"><a href=\"http://80.195.36.75:8080/servlet/LookupServlet?Date="+date+"\">"+date+"</a></p>");
                         
                             
                         }
                    rs.close();
                         
                   
                    out.println("");
                    con.close();

               }catch(SQLException ex){
                    ex.printStackTrace(out);
                    out.println("<br/>");
               }
0
 
LVL 1

Expert Comment

by:raid999
Comment Utility
Sorry for got one thing The name that u create in the ODBC you use it insted of my UGC in the url STRING
____________
cheers
0
 
LVL 35

Accepted Solution

by:
girionis earned 100 total points
Comment Utility
 Ok here we are:

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;

public class DatabaseConnection
{
     public static void main(String [] arguments)
     {
          Connection connection = null;
          Statement statement = null;
          ResultSet rs = null;
         
          try
          {
               // Load the database drievrs. An ODBC: JDBC bridge
               Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
               System.out.println("drivers loaded");
               
               // Obtain connection to the database.
               connection = DriverManager.getConnection("jdbc:odbc:Reviews", "", "");
               System.out.println("Connection established");
               
               // Cerate statement to read and write to/from database.
               statement = connection.createStatement();
               System.out.println("Statement created");
               
               rs = statement.executeQuery("SELECT * FROM Emails");
               
               // Get next row.
               rs.next();
               System.out.println("data is: " + rs.getString("email"));    
               
               // Close statement and conenction.
               statement.close();
               connection.close();
          }
          catch (ClassNotFoundException cnf)
          {
               System.out.println("Could not find class: " + cnf);
          }
          catch (SQLException sqle)
          {
               System.out.println("SQL problem: " + sqle);
          }
     }
}

  Things to notice:

  The above code assumes that you have an Access database running on you systems named "Reviews" and you do not need a password and username to connect. If you have any other database change the:

connection = DriverManager.getConnection("jdbc:odbc:Reviews", "", "");

and instead of Reviews put the name of your database and your username and password connection = DriverManager.getConnection("jdbc:odbc:<your database here>", "<username>", "<password>");.

  If your database ir running on a remote host change the jdbc:odbc to: jdbc:odbc://www.somecompany.com:400/database

  This statemtn: rs = statement.executeQuery("SELECT * FROM Emails"); assumes that you have a table caleld "Emails". If your table is called something else then change the "mail" to the name of your table.

  This one:
    rs.next();
    System.out.println("data is: " + rs.getString("surname"));

  reads only one column that its name is "surname". You have to change ti to match the name of the column in your table.

   If you want to read all the data you have to use a while loop:

  while(rs.next())
  {
    System.out.println(rs.getString("<columnname>");
    System.out.println(rs.getString("<anothercolumnname>");
    ...
    ...
  }

  or if you know the number of columns use a number instead:

  while(rs.next())
  {
    System.out.println(rs.getString(1);
    System.out.println(rs.getString(2);
    ...
    ...
  }

  Hope it helps.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:DanC3
Comment Utility
girionis, yours was the first (easiest for me) solution I tried and yours is what I will accept.  Thank you very much! DanC3


raid999, your solution is similar but harder for me to understand (especally the URL part, I said I was new to Java).  Give me a chance to try your solution and I will setup a new question titled "Points for raid999".  I need to understand: 1)UGC - is it an MS Access DB named 'dates'?, 2) what is the href=\"http://... adding to the out.println method?.
Give me a chance and I will test your code.

Thanks all,
DanC3
0
 
LVL 1

Expert Comment

by:raid999
Comment Utility
DanC3,

   UGC is the ODBC Name so when you create an ODBC connection calle it UGC or just change the name and change it in the code.

   the second this was the out.println("http://........") this will retreve what ever data i needed from the database and then have that URL?date=+date that means when i click the next servlet will now the date that i have choosen.

  If you have any other question please ask?

________________
Cheers
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
 DanC3 I am glad I helped :-) If you have any more questions please post.
0
 

Author Comment

by:DanC3
Comment Utility
girionis,
Do you have code that does not use the JDBC Bridge method?  
For a desktop Java application what database(s) are available besides MS Access or Oracle that are license free.  The anticipated project DB needs are very limited, as in we are above using plain .txt files but should have some limited SQL capability, but definatly license free.
I know about MS Jett and MSDB but what do Java developers prefer?


0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
 My personall preference is MySQL. It is completely free to use and the drives come at no cost. Take a look here: http://www.mysql.com/

  Hope it helps.
0
 

Author Comment

by:DanC3
Comment Utility
What I asked for was perfect for my needs.

Thanks again Girionis!
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
 Thank you for the points. I am glad I helped.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
scoreUp challenge 14 48
Basics of Drool Rule Engine with few samples 2 39
count8 challlenge 13 84
backtracking recursion  code 19 39
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now