Edward's Blog

利用JDBC对MySQL中数据进行操作

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.

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.