java-7: 使用JDBC驱动连接mysql数据库遇到的若干问题

最新推荐文章于 2023-04-15 09:29:44 发布

warrior_musician

最新推荐文章于 2023-04-15 09:29:44 发布

阅读量796

点赞数 1

分类专栏： Java 文章标签： java jdbc 驱动数据库连接

本文链接：https://blog.csdn.net/xushaozhang/article/details/101977618

版权

Java 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

如下在eclipse（Java EE IDE）使用JDBC连接MySql数据库，进行一个简单的select程序，只要能查询数据库，其他的都好办。

本文涉及两类问题：第一类是驱动报错，第二类是数据库连接报错。

以下代码是完整的驱动和数据库连接配置。

package org.szxu.volatileTest;

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

public class DBTest {
	public static void JDBCRead(){		
		//装载驱动
		try {
			//String driverName = "com.mysql.jdbc.Driver";  //旧版本，这里用的是：mysql-connector-java-5.1.46-bin.jar
			String driverName = "com.mysql.cj.jdbc.Driver";  //新版本，这里用的是：mysql-connector-java-8.0.11.jar
			Class.forName(driverName);		
			System.out.println("MYSQL JDBC Driver is loaded successfully!");
		}catch(ClassNotFoundException e){
			System.out.println("Where is your MYSQL JDBC Driver?");
			e.printStackTrace();
			return;
		}
		
		//连接数据库
		//String url = "jdbc:mysql://127.0.0.1:3306/crm";  //报错：
		String url = "jdbc:mysql://127.0.0.1:3306/crm?useSSL=false&serverTimezone=GMT";  //数据库连接串，正确
		String user = "root";  //用户名
		String passWord = "admin";  //密码
		Connection connection = null;
		Statement stmt = null;
		try {
			connection = DriverManager.getConnection(url, user, passWord);
			if(connection != null) {
				stmt = connection.createStatement();
				String sql = "select id, name, score from student";
				ResultSet rs = stmt.executeQuery(sql);
				while(rs.next()) {
					System.out.print("id:" + rs.getString("id") + " ");
					System.out.print("name:" + rs.getString("name") + " ");
					System.out.print("score:" + rs.getInt("score") + "\r\n");
				}
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				connection.close();
				stmt.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
}

第一类问题：驱动名称报错，驱动更换报错：

（1）驱动名称报错：【Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.】

驱动名称报错分析：在使用旧版本mysql-connector-java-5.1.46-bin.jar时，使用的driverName = "com.mysql.jdbc.Driver"，但是如果切换到mysql-connector-java-8.0.11.jar，就要使用driverName = "com.mysql.cj.jdbc.Driver"。否则报如上错误。

（2）驱动更换报错：【错误: 找不到或无法加载主类 org.szxu.volatileTest.Program】

这个错误非常隐蔽，必须查看“problems”的Tab页，原因是在更换驱动时，是直接到lib文件夹中，右击文件删除的，此时编译目录中还有残留路径，因此会报如上图无法编译（必须重新clean一次再run，否则还是上一次的编译结果）。如下图只需要在项目上右击选中build path -- configure buid path -- libraries，删除无用的驱动文件即可。

第二类问题：数据库连接报错

（1）传输加密未设定方式报错：【Thu Oct 03 10:30:13 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.】

只需要在URL中添加“useSSL=false”即可；

（2）数据库返回时间问题报错：【The server time zone value '???ú±ê×??±??' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.】

只需要在URL中添加“serverTimezone=GMT”即可。每项配置之间用“&”连接，第一项配置之前要放“?”

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

附录：驱动的下载和配置请参考下一篇博文

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

参考文章：

1.https://blog.csdn.net/jabony/article/details/90242860，关于“传输加密未设定方式报错"。

2.https://blog.csdn.net/weixin_37577564/article/details/80329775，关于“数据库返回时间问题报错”。

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

--封装后的源码：

package org.szxu.volatileTest;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

public class MySqlDBTest {
	private MySqlDBTest() { 
		//String driverName = "com.mysql.jdbc.Driver";  //旧版本，这里用的是：mysql-connector-java-5.1.46-bin.jar
		String driverName = "com.mysql.cj.jdbc.Driver";  //新版本，这里用的是：mysql-connector-java-8.0.11.jar
		mySqlDBInite(driverName);
	}
	
	public static MySqlDBTest dbTest = null;
	public static Connection connection = null;
	//public static String url = "jdbc:mysql://127.0.0.1:3306/crm";  //报错：
	public static String url = "jdbc:mysql://127.0.0.1:3306/crm?useSSL=false&serverTimezone=GMT&allowPublicKeyRetrieval=true";  //数据库连接串，正确
	public static String user = "root";  //用户名
	public static String passWord = "admin";  //密码
	private static String obj = "lock";
	
	public static MySqlDBTest instance() {
		if(dbTest == null){
			synchronized(obj) {
				if(dbTest == null) {
					dbTest = new MySqlDBTest();
				}
			}
		}
		return dbTest;
	}
	
	/**
	 * JDBC初始化驱动装载
	 * @param driverName
	 */
	public static boolean mySqlDBInite(String driverName){
		//装载驱动
		try {			
			Class.forName(driverName);		
			System.out.println("MYSQL JDBC Driver is loaded successfully!");
			return true;
		}catch(ClassNotFoundException e){
			System.out.println("Where is your MYSQL JDBC Driver?");
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static synchronized Connection getConnection(){
		if(connection == null) {
			try {
				connection = DriverManager.getConnection(url, user, passWord);
			}catch(SQLException e) {
				e.printStackTrace();
			}
			
			if(connection == null) {
				System.out.println("Wrong, MYSQL database connects failed!");
			}
			else {
				System.out.println("MYSQL database connects successfully!");
			}
		}
		
		return connection;
	}
	
	/**
	 * 操作数据库:statement方式
	 */
	public void StatementOperate(){
		Connection connection = getConnection();
		if(connection == null){
			return;
		}
		
		Statement stmt = null;    // 仅需要创建一次即可
		try {
			//statement对象
			stmt = connection.createStatement();
			
			//查
			printStudentData(connection);
			
			//改
			String sql = "update student set score = 85 where id = 3";
			int affectedRows = stmt.executeUpdate(sql);
			System.out.printf(String.format("student表，更新数量=%d\r\n", affectedRows));
			
			//增
			sql = "insert into student(name, score) select 'jlxu', '65'";
			affectedRows = stmt.executeUpdate(sql);
			System.out.printf("student表，插入数量=%d\r\n", affectedRows);
			printStudentData(connection);
			
			//删
			sql = "delete from student where name = 'jlxu'";
			affectedRows = stmt.executeUpdate(sql);
			System.out.printf("student表，删除数量=%d\r\n", affectedRows);
			printStudentData(connection);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			try {
				connection.close();
				stmt.close();
			}catch(SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	/**
	 * 操作数据库：prepared方式
	 */
	public void preparedOperate() {
		Connection connection = getConnection();
		if(connection == null){
			return;
		}
		
		PreparedStatement ps = null;    // 每次操作均需重新创建
		try {
			//查
			printStudentData(connection);
			
			//改
			String sql = "update student set score = 85 where id = 3";
			ps = connection.prepareStatement(sql);
			int affectedRows = ps.executeUpdate();
			
			//增
			sql = "insert into student(name, score) select 'jlxu', '65'";
			 
			ps = connection.prepareStatement(sql);
			affectedRows = ps.executeUpdate();
			System.out.printf("student表，插入数量=%d\r\n", affectedRows);
			printStudentData(connection);
			
			//删
			sql = "delete from student where name = 'jlxu'";
			ps = connection.prepareStatement(sql);
			affectedRows = ps.executeUpdate();
			System.out.printf("student表，删除数量=%d\r\n", affectedRows);
			printStudentData(connection);
			
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				connection.close();
				ps.close();
			}catch(SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
	
	/**
	 * 查询学生表数据
	 * @param connection
	 */
	public void printStudentData(Connection connection) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			//statement对象
			stmt = connection.createStatement();
			
			//查(使用ResultSet)
			String sql = "select id, name, score from student";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				System.out.print("id:" + rs.getString("id") + " ");
				System.out.print("name:" + rs.getString("name") + " ");
				System.out.print("score:" + rs.getInt("score") + "\r\n");
			}
		}catch(SQLException e) {
			e.printStackTrace();
			
		}finally {
			try {
				stmt.close();
				rs.close();
			}catch(SQLException ex) {
				ex.printStackTrace();
			}
		}
	}
	
}