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

This entry is part 2 of 2 in the series Accessing MySQL on NetBeans using JDBC

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

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

MySQL Connection using in this part

Suppose that I have MySQL running remotely on IP:192.168.1.101 with default port (3306) and I want to connect to Northwind database with username is ‘root’ and password is ‘123456’. The connection string will be

String connectionUrl = "jdbc:mysql://192.168.1.101:3306/Northwind?" + 
            "user=root&password=123456";

Retrieve data from a database

To get some data, I need to execute query on the MySQL 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 objects allow you to execute basic SQL queries and retrieve the results through the ResultSet class.
– In while-loop, iterative in the ResultSet object to show result in console (ProductName and UnitPrice columns in Products table) on output window.

The example result will be similar to below.
Note: I have imported only 4 records from Products table in Northwind database.

Select query result

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 insert command
            String strSQL = "INSERT INTO Products (ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder," +
                            "ReOrderLevel,Discontinued) VALUES ('MyProduct','10 Kg.',1234.0000,100,50,30,0)";
            int rowsEffected = stmt.executeUpdate(strSQL);
            System.out.println(rowsEffected + " rows effected");

UPDATE command

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

DELETE command

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

Summary

You can download source code example testMySQL.java (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.

Example query result from MySQL

Series Navigation<< Accessing MySQL on NetBeans using JDBC, Part 1: Create a connection

77 Comments

  1. Chad Hutchins April 23, 2008
  2. Ian Semmel June 3, 2008
  3. ccc June 11, 2008
  4. linglom June 12, 2008
  5. ccc June 15, 2008
  6. Arnaud June 19, 2008
  7. linglom June 20, 2008
  8. dory June 23, 2008
  9. Arnaud June 24, 2008
  10. whatever July 18, 2008
  11. Xavier Domingo October 9, 2008
  12. Luis Eduardo Oliveira Lizardo October 25, 2008
  13. inD_05 October 28, 2008
  14. linglom October 28, 2008
  15. juanmf November 20, 2008
  16. juanmf November 20, 2008
  17. Lunga November 22, 2008
  18. Cotoblanc May 3, 2009
  19. to June 1, 2009
  20. Luis November 24, 2009
  21. Ciberboot December 1, 2009
  22. Kichoos December 10, 2009
  23. Veronica December 17, 2009
  24. linglom January 6, 2010
  25. Sitti February 25, 2010
  26. linglom February 26, 2010
  27. Sitti February 26, 2010
  28. linglom March 2, 2010
  29. halima March 3, 2010
  30. linglom March 4, 2010
  31. Sitti March 5, 2010
  32. halima March 6, 2010
  33. halima March 8, 2010
  34. Ankit Verma March 23, 2010
  35. Jules March 31, 2010
  36. Jules March 31, 2010
  37. Warlockk April 15, 2010
  38. Warlockk April 15, 2010
  39. manoj May 27, 2010
  40. Manish K. Aasawat June 17, 2010
  41. vishakha July 16, 2010
  42. Joseph September 10, 2010
  43. Saemmanuel November 23, 2010
  44. chithra December 13, 2010
  45. john January 7, 2011
  46. teguh teja January 7, 2011
  47. PrathapSingh February 4, 2011
  48. Irshad Ali March 21, 2011
  49. Anildas August 14, 2011
  50. Anildas August 14, 2011
  51. shafali September 2, 2011
  52. Anandhan October 17, 2011
  53. cashwise November 18, 2011
  54. Verline Lafleur November 25, 2011
  55. Tonmoy December 25, 2011
  56. ap January 4, 2012
  57. Sahil January 9, 2012
  58. ap January 9, 2012
  59. Sahil January 10, 2012
  60. ap January 11, 2012
  61. ap January 11, 2012
  62. Sahil January 12, 2012
  63. ap January 12, 2012
  64. Sahil January 13, 2012
  65. ap January 13, 2012
  66. Sahil January 14, 2012
  67. Sahil January 14, 2012
  68. ap January 16, 2012
  69. ap January 16, 2012
  70. ap January 16, 2012
  71. Sahil January 19, 2012
  72. ap January 19, 2012
  73. Cristina March 25, 2012
  74. sp May 3, 2012
  75. venkat ravi September 14, 2012
  76. Sanjaya January 13, 2013
  77. Mohamed Augustin Sesay November 5, 2015

Leave a Reply

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