Accessing SQL Server on NetBeans using JDBC, Part 2: Perform SQL Operations

This entry is part 2 of 3 in the series Accessing SQL Server on NetBeans using JDBC

Perform SQL Operations

From Part 1, I have only established a connection with local SQL Server. Next I’ll show how to retrieve and modify data on remote SQL Server.

There are 3 parts:

  1. Part I : Create a connection
    This part which you’re reading shows about how to establish a connection between NetBeans and SQL Server. In this example, I use SQL Server 2000 SP4 and NetBeans IDE 5.5
  2. Part II : Perform SQL Operations
    This part show how to perform some basic operations from NetBeans with SQL Server. For instance, send querys as SELECT, INSERT, UPDATE to the database.
  3. Part III: Troubleshooting
    The last part is about problems and how to fix them.

SQL Server Connection using in this part

Assume that I have SQL Server running remotely on BKKPDC01 computer and I want to connect to Northwind database with username is ‘sa’ and password is ‘password’. The connection string will be

String connectionUrl = "jdbc:sqlserver://bkkpdc01:1433;databaseName=Northwind;user=sa;password=password";

Retrieve data from database

To get some data, I need to execute query on the SQL Server and get the result back to me. First, I create stmt (Statement object) and execute query in SQL language. Then I store the result on ResultSet object and iterative show the result on the output window.

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

Code Explanation:
– Statement object is used for sending SQL Statement to the database.
– ResultSet object is used to keep data from the executed query.
– In while-loop, iterative in the ResultSet object to show result (All ProductName and UnitPrice in Products table) on output window.

The example result will be similar to below.

Example query from SQL Server

Update data on database

To insert, update and delete records on SQL Server, you can use the code from retrieve data from database and simply change SQL command and also modify some code a little bit. On update, I must use executeUpdate(“SQL”) method on statement object instead executeQuery(“SQL”) and the return value will be rows affected instead of a record set.

Example
INSERT command

            // SQL query command
            String SQL = "INSERT INTO Products (ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReOrderLevel,Discontinued) VALUES ('MyProduct','10 Kg.',1234.0000,100,50,30,0)";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

UPDATE command

            // SQL query command
            String SQL = "UPDATE Products SET UnitPrice = 900, UnitsInStock = 55, UnitsOnOrder = 5 WHERE ProductName = 'MyProduct'";
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

DELETE command

            // SQL query command
            String SQL = "DELETE FROM Products WHERE ProductName = 'MyProduct'");
            int rowsEffected = stmt.executeUpdate(SQL);
            System.out.println(rowsEffected + " rows effected");

Summary

You can download the source code example here (Right-click on the link and select Save target As…).
But you have to change connection string to match your environment. The example code will connect to Northwind database and try to retrieve records, insert a new record, update the record and delete the record from Products table. The result is below.

Result from the example source code

Series Navigation<< Accessing SQL Server on NetBeans using JDBC, Part 1: Create a connectionAccessing SQL Server on NetBeans using JDBC, Part 3: Troubleshooting >>

51 Comments

  1. Cesar August 18, 2007
  2. Maricelys September 5, 2007
  3. Gunnar Þór Gunnarsson September 21, 2007
  4. linglom September 22, 2007
  5. José September 29, 2007
  6. José September 30, 2007
  7. Rana Guha October 27, 2007
  8. Jason November 15, 2007
  9. linglom November 16, 2007
  10. Yaseen Rao December 4, 2007
  11. kumar January 10, 2008
  12. Adam January 12, 2008
  13. Adam January 12, 2008
  14. linglom January 14, 2008
  15. nida April 10, 2008
  16. hazky June 19, 2008
  17. linglom June 20, 2008
  18. Arjun June 25, 2008
  19. Harmeet August 23, 2008
  20. reno812 September 20, 2008
  21. loga September 28, 2008
  22. peter jones February 12, 2009
  23. T February 12, 2009
  24. linglom February 14, 2009
  25. mehrvarz May 17, 2009
  26. ragnor July 15, 2009
  27. linglom July 16, 2009
  28. Mon August 17, 2009
  29. deathprog August 19, 2009
  30. linglom August 20, 2009
  31. deathprog August 21, 2009
  32. deathprog August 25, 2009
  33. linglom August 25, 2009
  34. deathprog August 25, 2009
  35. linglom August 25, 2009
  36. Praveen November 19, 2009
  37. linglom November 25, 2009
  38. Praveen November 25, 2009
  39. linglom November 25, 2009
  40. Tom March 5, 2010
  41. Carlos Fernandez March 8, 2010
  42. Pete March 11, 2010
  43. Norman May 13, 2010
  44. Norman May 13, 2010
  45. Francisco July 2, 2010
  46. Khalida-san December 1, 2011
  47. miky February 1, 2012
  48. Akshay March 13, 2016
  49. linglom March 14, 2016
  50. Akshay March 14, 2016
  51. linglom March 15, 2016

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.