Linglom's blog

This article is one of the series of Accessing Access 2007 on NetBeans 6.5 using JDBC. You can see the index of this series at Accessing Access 2007 on NetBeans 6.5 using JDBC, Part 1: Introduction

Perform SQL Operations

From Part 3: Create a Connection, I show how to create a connection from NetBeans 6.5 to the Northwind database of Microsoft Access 2007. On this post, you see how to perform basic SQL operations such as SELECT, INSERT, DELETE and UPDATE to the Customers table on Northwind database.

• Retrieve data from database
• Update data on database

Retrieve data from database

Copy and paste the code below on main method between these lines:

System.out.println("Connected!");

and

con.close();

The Code

1
2
3
4
5
6
7
8
9
10
11
12

            Statement stmt = null;
            ResultSet rs = null;
 
            // SQL query command
            String SQL = "SELECT * FROM Customers";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            while (rs.next()) {
                System.out.println(rs.getString("Company") + " : "
                        + rs.getString("First Name")+ " : "
                        + rs.getString("Last Name"));
            }

Code Explanation:

• Line 1-2: Declare some variables.
• Line 5: Define SQL query string.
• Line 6: Create a statement object for sending SQL statements to the database.
• Line 7: Executes the SQL statement and returns a single ResultSet object.
• Line 8-12: Iterative in the ResultSet object to show results on output window

Next, run the project. You see the result on the output window. It displays records from Customers table.

Back to top

Update data on database

To insert, update and delete records on MS Access 2007 databases, you can re-use the code from the previous section. But you need to replace the SQL statement and code on this line:

rs = stmt.executeQuery(SQL);
while (rs.next()) {
                System.out.println(rs.getString("Company") + " : "
                        + rs.getString("First Name")+ " : "
                        + rs.getString("Last Name"));
            }

to

int rowsEffected = stmt.executeUpdate(SQL);
System.out.println(rowsEffected + " rows effected");

Code Explanation:
Executes the given SQL statement which may be INSERT, UPDATE or DELETE statement and returns the row count for the update.

INSERT command

            // SQL INSERT command
            String SQL = "INSERT INTO Customers (Company,[First Name]," +
                    "[Last Name]) VALUES ('MyCompany','Linglom','My Last Name')";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

UPDATE command

            // SQL UPDATE command
            String SQL = "UPDATE Customers SET [Last Name] = 'New Last Name'" +
                            "WHERE [First Name] = 'Linglom'";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

DELETE command

            // SQL DELETE command
            String SQL = "DELETE FROM Customers WHERE [First Name] = 'Linglom'";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

Summary

You can download the source code of this series at SampleAccess2007.java. The example code will create a connection to Northwind database on Microsoft Access 2007. Then, retrieve records, insert a new record, update and delete the record from Customer table. Here is output of the example code.

Back to top