Accessing MS Access 2007 on NetBeans 6.5 using JDBC, Part 4: Perform SQL Operations

This entry is part 4 of 4 in the series Accessing MS Access 2007 on NetBeans 6.5 using JDBC

Perform SQL Operations

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

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

Query Code

Next, run the project. You see the result on the output window. It displays records from Customers table.
Query Result
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. 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.
Update Result
Back to top

Series Navigation<< Accessing MS Access 2007 on NetBeans 6.5 using JDBC, Part 3: Create Connection

33 Comments

  1. micman October 2, 2009
  2. amare December 7, 2009
  3. madhu April 25, 2010
  4. linglom April 25, 2010
  5. mansoor June 24, 2010
  6. robin August 1, 2010
  7. Karolina August 20, 2010
  8. mansoor August 27, 2010
  9. chetan January 9, 2011
  10. biigaa March 10, 2011
  11. yashpal March 26, 2011
  12. yashpal March 26, 2011
  13. Shahid April 9, 2011
  14. siddharth August 2, 2011
  15. siddharth August 2, 2011
  16. vgrocks August 21, 2011
  17. Sultan October 19, 2011
  18. rin November 19, 2011
  19. rin November 19, 2011
  20. Muhammad Bilal January 8, 2012
  21. bharath January 20, 2012
  22. saber February 27, 2012
  23. zeroFILL April 15, 2012
  24. Klug(JRDeveloper) April 27, 2012
  25. John Matovu April 29, 2012
  26. Niharika Gupta May 12, 2012
  27. venkat ravi September 14, 2012
  28. Mayur October 14, 2012
  29. Sasha October 23, 2012
  30. akor March 30, 2014
  31. sasa July 11, 2014
  32. anand January 8, 2016
  33. linglom January 10, 2016

Leave a Reply

%d bloggers like this: