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

Related post