mysql操作类

创作不易，请勿抄袭，转载请注明出处。如有疑问，请加微信 wx15151889890，谢谢。
[本文链接:]https://blog.csdn.net/wx740851326/article/details/https://blog.csdn.net/wx740851326/article/details/83744662

本文主要写了如何测试连接mysql数据库，sql操作数据库以及读出来的数据逐行处理，如有错误，请不吝赐教。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import redis.clients.jedis.JedisCluster;
/**
 * define mysql handlers
 * 
 * @author dmvoishi
 * @since  20181024
 */
public class MysqlUtil {
	static JedisCluster jedisCluster = RedisUtil.getJedisCluster();

	public static void main(String[] args) {
		String mysqlConfigPath = "hdfs:///user/hdfs/mysql.cfg";
		// 声明Connection对象
		Connection con;
		Properties prop = CommonUtils.getProperty(mysqlConfigPath, "hdfs");

		// 驱动程序名
		// String driver = "com.mysql.jdbc.Driver";
		String driver = prop.getProperty("driver");
		// URL指向要访问的数据库名mydata
		// String url = "jdbc:mysql://10.0.0.1:3306/";
		String url = prop.getProperty("url");

		// MySQL配置时的用户名
		// String user = "root";
		String user = prop.getProperty("user");

		// MySQL配置时的密码
		// String password = "root";
		String password = prop.getProperty("password");

		// 遍历查询结果集
		try {
			// 加载驱动程序
			Class.forName(driver);
			// 1.getConnection()方法，连接MySQL数据库！！
			con = DriverManager.getConnection(url, user, password);
			if (!con.isClosed())
				System.out.println("Succeeded connecting to the Database!");
			// 2.创建statement类对象，用来执行SQL语句！！
			Statement statement = con.createStatement();
			// 要执行的SQL语句
			// String sql = "select plc_name,plc_data_name,id from alarm";
			String sql = "select CONCAT(plc_name,\"_alarm\") plc_name,CONCAT(\"{\",GROUP_CONCAT(CONCAT(\"\"\"\",plc_data_name,\"\"\"\",\":\",\"\"\"\",id,\"\"\"\")),\"}\") alarm_content from alarm GROUP BY plc_name";
			// 3.ResultSet类，用来存放获取的结果集！！
			// statement.executeQuery(sql);
			ResultSet rs = statement.executeQuery(sql);

			String plc_name = "plc_name";
			String alarm_content = "alarm_content";
			while (rs.next()) {
				plc_name = rs.getString("plc_name");
				alarm_content = rs.getString("alarm_content");
				jedisCluster.set(plc_name, alarm_content);
				// System.out.println(plc_name);
				// System.out.println(alarm_content);
			}
			rs.close();
			con.close();
		} catch (ClassNotFoundException e) {
			// 数据库驱动类异常处理
			System.out.println("Sorry,can`t find the Driver!");
			e.printStackTrace();
		} catch (SQLException e) {
			// 数据库连接失败异常处理
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			System.out.println("数据库数据成功获取！！");
		}
	}
}