MySql表远程映射及本地向服务器表写数据

一、MySql数据库表远程映射

本部分参考来源：MySql学习笔记–MySql数据库表远程映射

（1）、Windows环境

1.查看当前federated引擎是否开启

2.如果未开启federated引擎

① 查看mysql的安装目录和数据存放路径

② 找到mysql的数据存放路径,会找到相应的my.ini文件,打开my.ini文件在其中加上federated

3.重启mysql服务，再次使用show engines;命令查看federated引擎是否开启

（2）Linux环境

1.连接mysql,使用命令show engines;查看引擎是否开启

2.使用命令whereis my.cnf查看配置文件的位置(我的在/etc/my.cnf)

3.使用命令vim /etc/my.cnf编辑配置文件

4.配置文件中加上federated

5.使用命令service mysqld restart重启mysql服务

6.再次使用命令show engines;查看是否开启成功

（3）映射远程表：服务端一定要存在与本地相同的数据表，比mine_quack_stationcondition，并且表结构要相同，而且本地用户要有权限访问服务端，如果没有权限可以在服务器的数据库用户上添加新用户：

-- ----------------------------
-- Table structure for mine_quack_stationcondition
-- ----------------------------
DROP TABLE IF EXISTS `mine_quack_stationcondition`;
CREATE TABLE `mine_quack_stationcondition`  (
  `day` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `panfu` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `location` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `xData` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `yData` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `zData` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `status` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `unused` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `used` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `total` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `netspeed` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`day`, `panfu`) USING BTREE
) ENGINE = FEDERATED 
CONNECTION='mysql://yhy:071***@192.***.**.100:3306/ks/mine_quack_stationcondition';

说明 ：yhy为服务器上添加的用户名，071***为添加的用户名密码，而且还要指定用户的ip。192.*..100是服务器的ip,3306是服务器的端口，ks是服务器的数据库，mine_quack_stationcondition是他的表名。

----------------------------------------------------------------

二、本地向服务器数据表写数据

只需要在本地上连接服务器的ip，给定用户名和密码即可

private static String url = null;
	private static String username = null;
	private static String password = null;
	private static String driver = null;
	private static Connection connection = null;
	private static Statement statement;
	private static ResultSet resultSet;
	private static final String TABLENAME = "test";

	public static Connection getConnection() {
		try {
			Properties properties = new Properties();
			InputStream in = DatabaseUtil.class.getClassLoader().getResourceAsStream("./jdbc.properties");

			properties.load(in);
			properties.setProperty("driver", "com.mysql.cj.jdbc.Driver");
			driver = properties.getProperty("driver");
			properties.setProperty("url", "jdbc:mysql://***.**.***.30:3306/ks?serverTimezone=UTC&useSSL=false");
			url = properties.getProperty("url");
//			username = properties.getProperty("username");
//			password = properties.getProperty("password");

			username = "root";
			password = "root";

			Class.forName(driver);
			if(connection == null)
				connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return connection;
	}

数据库操作代码

package com.yhy.getinformation;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

/**
 * This class is used for adding,querying,updating for the database table of  mine_quack_stationcondition
 * @author Coloring
 * @version 1.0 2020-11-14
 */

public class DatabaseUtil {
	private static String url = null;
	private static String username = null;
	private static String password = null;
	private static String driver = null;
	private static Connection connection = null;
	private static Statement statement;
	private static ResultSet resultSet;
	private static final String TABLENAME = "mine_quack_stationcondition";

	public static Connection getConnection() {
		try {
			Properties properties = new Properties();
			InputStream in = DatabaseUtil.class.getClassLoader().getResourceAsStream("./jdbc.properties");

			properties.load(in);
			properties.setProperty("driver", "com.mysql.cj.jdbc.Driver");
			driver = properties.getProperty("driver");
			properties.setProperty("url", "jdbc:mysql://***.**.***.30:3306/ks?serverTimezone=UTC&useSSL=false");
			url = properties.getProperty("url");
//			username = properties.getProperty("username");
//			password = properties.getProperty("password");

			username = "root";
			password = "root";

			Class.forName(driver);
			if(connection == null)
				connection = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return connection;
	}

	
	public static void close() {
		try {
			if (resultSet != null)
				resultSet.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			resultSet = null;
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				statement = null;
				try {
					if (connection != null)
						connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					connection = null;
				}
			}
		}
	}

	
	public static boolean update(String sql) {
		boolean successful = false;
		try {
			statement = DatabaseUtil.getConnection().createStatement();
			successful = statement.execute(sql);
			//System.out.println(successful);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		//true if the first result is a ResultSetobject; 
		//false if it is an update count or there areno results
		return successful;
	}
	
	public static ArrayList<TableProperty> query(String sql) {
		ArrayList<TableProperty> resultSet =new  ArrayList<>();
		ResultSet rs = null;
		TableProperty tp = null;
		try {
			rs = DatabaseUtil.getConnection().
					createStatement().executeQuery(sql);
			
			while (rs.next()) {	
				tp = new TableProperty();
				tp.setDay(rs.getString("day"));
				tp.setPanfu(rs.getString("panfu"));
				tp.setxData(rs.getString("xData"));
				tp.setyData(rs.getString("yData"));
				tp.setzData(rs.getString("zData"));
				tp.setLocation(rs.getString("location"));
				tp.setStatus(rs.getString("status"));
				tp.setUnused(rs.getString("unused"));
				tp.setUsed(rs.getString("used"));
				tp.setTotal(rs.getString("total"));
				tp.setNetspeed(rs.getString("netspeed"));
				resultSet.add(tp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return resultSet;
	}
	
	
	public static boolean insert(String sql) {
		boolean successful = false;
		try {
			successful = DatabaseUtil.getConnection().
			createStatement().execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return successful;
	}
	
	/**
	 * 
	 * @param values is the whole TableProperty fields
	 * @return true indicates succeed
	 */
	public static boolean insert(String[] values) {
		boolean flag = false;
		String sql = "insert into " + TABLENAME + " values(?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement preStatement = null;
		try {
			preStatement = DatabaseUtil.getConnection().prepareStatement(sql);
			preStatement.setString(1,values[0]);
			preStatement.setString(2,values[1]);
			preStatement.setString(3,values[2]);
			preStatement.setString(4,values[3]);
			preStatement.setString(5,values[4]);
			preStatement.setString(6,values[5]);
			preStatement.setString(7,values[6]);
			preStatement.setString(8,values[7]);
			preStatement.setString(9,values[8]);
			preStatement.setString(10,values[9]);
			preStatement.setString(11,values[10]);
			flag = preStatement.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	
	/**
	 * 
	 * @param values , is the whole TableProperty fields
	 * @param needUpdate  ,if true indicates that we need update the database,
	 * because there may be exist the values that we want to insert in the database.
	 * @return true indicates success when it is insert operation, false indicate 
	 * success when it is update operation.
	 */
	public static boolean insert(String[] values,boolean needUpdate) {
		boolean flag = false;
		
		if(needUpdate) {
			ResultSet rs = null;
			String sql = "select * from "+TABLENAME+" where day='"
						+values[0]+"' and panfu='"+values[1]+"' ";
			//System.out.println(sql);
			try {
				rs = DatabaseUtil.getConnection().
						createStatement().executeQuery(sql);
				if(rs.next()) {
					String updateSql =  "update "+TABLENAME+" set status='"+values[6]
							+"',unused='"+values[7]+"',used='"+values[8]+"',total='"+values[9]
							+"',netspeed='"+values[10]+"' where day='"+values[0]
							+"' and panfu='"+values[1]+"' ";
					flag = DatabaseUtil.getConnection().
							createStatement().execute(updateSql);
				}else {
					flag = insert(values);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}else {
			flag = insert(values);
		}
		return flag;
	}
	
	//----------------------------------------------------------------------
	// This is the test code.
	public static void main(String[] args) {
		
//		String []values= {"2020-11-14","K","ddd","140.0","102.5","159.6"
//				,"xiugaile","325G","65G","390G","2.66Mbps"};
//		insert(values,true);
//		
//		ArrayList<TableProperty> al;
//		String sql = "select * from "+TABLENAME;
//		al = query(sql);
//		for(TableProperty tp:al) {
//			System.out.println(tp.toString());
//		}
//		
//		
//		String sql3 = "update "+TABLENAME+" set location='xiugaile' where day='2020-11-14' and panfu='K'";
//		boolean flag = update(sql3);
//		System.out.println(flag);
//		al = query(sql);
//		for(TableProperty tp:al) {
//			System.out.println(tp.toString());
//		}
		
		String sql = "insert into test values('1','123')";
		update(sql);
		close();
	}
	//end test code.
	//-----------------------------------------------------------------------
}