Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Basic Java Code to connect to a MS Access table

Posted on 2002-05-22
14
Medium Priority
?
877 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +2
14 Comments
 

Expert Comment

by:lhshyong
ID: 7027148
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
ID: 7027152
 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DanC3
ID: 7027719
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
ID: 7028496
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
ID: 7028497
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 400 total points
ID: 7029184
 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
 

Author Comment

by:DanC3
ID: 7030718
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
ID: 7031367
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
ID: 7031450
 DanC3 I am glad I helped :-) If you have any more questions please post.
0
 

Author Comment

by:DanC3
ID: 7032507
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
ID: 7032559
 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
ID: 7032733
What I asked for was perfect for my needs.

Thanks again Girionis!
0
 
LVL 35

Expert Comment

by:girionis
ID: 7032969
 Thank you for the points. I am glad I helped.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

610 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