MySQL: JDBC-based Remote Operation

/*************************************** *********************

Statement: If You Need To reprint it, please indicate the source!

**************************************** ********************/

In my MySQL blog series, I shared how to connect to the MySQL database using JDBC.

Click here to view details.

If you patiently put the example of that blog into practice, this blog will soon make you like it.

To remotely connect to MySQL, remember:MySQL allows remote access.

1. Start MySQL

Sudo/etc/init. d/MySQL. Server start

Mysql-u root-P

2. Add a user

Use MySQL (MySQL is a built-in database file with a table user)

Run the following two commands:

Explanation:

Grant all privileges on *. * To 'mark' @ '% 'identified by '123 ';

Authorize the user mark with the password 123456 and use any IP address (%) to access any database (*.*).

View table data:

We can see that Mark is created by this user.

The password is encrypted. If the host is %, any IP address (IPv4) is configured ).

3. Command Line Verification

CTRL + C stop MySQL Interaction Mode

Ifconfig: Check the local IP Address: 192.168.1.102

Mysql-H 192.168.1.102-umark-p123456

If you enter the interaction mode, OK.

Create a new database file mydb and table mytable:

4. JDBC

Eclipse --- New Java Project

Note:

The jar file build path of JDBC (connector-java-5.1.6-bin.jar version) to this project.

Mysqlutil. Java

package net.mark.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class MySqlUtil {public static Connection openConnection(String url, String user,String password) {Connection conn = null;try {final String DRIVER_NAME = "com.mysql.jdbc.Driver";Class.forName(DRIVER_NAME);conn = DriverManager.getConnection(url, user, password);} catch (ClassNotFoundException e) {conn = null;} catch (SQLException e) {conn = null;}return conn;}public static void query(Connection conn, String sql) {if (conn == null) {return;}Statement statement = null;ResultSet result = null;try {statement = conn.createStatement();result = statement.executeQuery(sql);if (result != null && result.first()) {int idColumnIndex = result.findColumn("id");int nameColumnIndex = result.findColumn("name");while (!result.isAfterLast()) {System.out.println("------------------");System.out.print("id " + result.getString(idColumnIndex) + "\t");System.out.println("name " + result.getString(nameColumnIndex));result.next();}}} catch (SQLException e) {e.printStackTrace();} finally {try {if (result != null) {result.close();result = null;}if (statement != null) {statement.close();statement = null;}} catch (SQLException sqle) {}}}public static boolean execSQL(Connection conn, String sql) {boolean execResult = false;if (conn == null) {return execResult;}Statement statement = null;try {statement = conn.createStatement();if (statement != null) {execResult = statement.execute(sql);}} catch (SQLException e) {execResult = false;}return execResult;}}

Main. Java

package net.mark;import java.sql.Connection;import net.mark.util.MySqlUtil;public class Main {private static final String URL = "jdbc:mysql://192.168.1.102/mydb";private static final String USER = "mark";private static final String PASSWORD = "123456";public static void main(String[] args) throws Exception {Connection conn = MySqlUtil.openConnection(URL, USER, PASSWORD);System.out.println("All users info:");MySqlUtil.query(conn, "select * from mytable");}}

Run as Java application:

You can also insert, delete, or update data:

Package net. mark; import Java. SQL. connection; import net. mark. util. mysqlutil; public class main {Private Static final string url = "JDBC: mysql: // 192.168.1.102/mydb"; Private Static final string user = "mark "; private Static final string Password = "123456"; public static void main (string [] ARGs) throws exception {connection conn = mysqlutil. openconnection (URL, user, password); system. out. println ("All Users info:" inserting into mysqlutil.exe csql (Conn, "insert into mytable values (56, 'lily')" inserting into mysqlutil.exe csql (Conn, "Update mytable set name = 'mark' where id = 1" cannot parse mysqlutil.exe csql (Conn, "delete from mytable where id = 6"); mysqlutil. query (Conn, "select * From mytable ");}}

The above access is to operate MySQL through JDBC Based on the LAN.

Remote Access to MySQL is also possible using the above method, but the speed is not very fast.