操作MySQL数据库的工具类

package util;

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

/**
 * 操作数据库的工具类
 * @author Tan
 *
 */
public class JDBCUtil {
	private static final String DB_USER_NAME = "root"; //数据库用户名
	private static final String DB_PASSWORD = "tanhuiwen849"; //数据库密码
	private static final String DB_NAME = "hello_world"; //数据库名称
	private static final String IP = "localhost"; //安装mysql数据库的服务器的IP地址
	private static final String URL = "jdbc:mysql://"+IP+":3306/"+DB_NAME+"?useSSL=false"; //mysql连接地址
	
	//引用
	private static Connection conn; //创建连接对象(连接Java和MySQL的路)
	private static Statement stmt; //执行sql语句的对象(执行命令的对象)(人)
	private static ResultSet rs; //保存查询结果的数据的对象(车)
	
	/**
	 * 获得数据库连接对象Connection(实例化Connection对象conn)
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private static Connection getCon() throws ClassNotFoundException, SQLException{
		//加载MySQL驱动
		Class.forName("com.mysql.jdbc.Driver");
		//若为空才赋值 因为static 静态占用内存
		if (conn == null) {
			conn = DriverManager.getConnection(URL, DB_USER_NAME, DB_PASSWORD);
		}
		return conn;
	}
	
	/**
	 * 获得Statement对象，用于执行SQL语句
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private static Statement openStmt() throws ClassNotFoundException, SQLException {
		stmt = getCon().createStatement();
		return stmt;
	}

	/**
	 * 用于外界调用，执行数据库增(insert),删(delete),改(update)的操作
	 */
	public static int executeSQL(String sql) {
		int i = 0;
		try {
			i = openStmt().executeUpdate(sql);//执行SQL语句方法
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//关闭数据库相关对象
			close();
		}
		return i; //返回为改变了几条数据
		
	}
	
	/**
	 * 执行数据库查询(select)的操作
	 */
	public static ResultSet search(String sql) {
		try {
			rs = openStmt().executeQuery(sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 关闭数据库的方法
	 */
	public static void close() {
		try {
			if(rs != null) {
				rs.close(); //关闭ResultSet对象(车)
			}
			if(stmt != null) {
				stmt.close(); //关闭Statement对象(人)
			}
			if(conn != null) {
				conn.close(); //关闭Connection对象(路)
			}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

}

• 测试类

package test;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import util.JDBCUtil;

public class SQLDemo {
	
	//添加
	@Test
	public void testInsert() { //测试向数据库中添加数据
		String sql = "insert into goods values(default,'可口可乐',3.5,'2021-09-08',100)";
		int i = JDBCUtil.executeSQL(sql);
		System.out.println(i);
		
	}
	
	//修改
	@Test
	public void testUpdate() { //测试向数据库中修改数据
		String sql = "update goods set g_price=2.5 where id = 1";
		int i = JDBCUtil.executeSQL(sql);
		System.out.println(i);
	}
	
	//删除
	@Test
	public void testDelete() {//测试向数据库中删除数据
		String sql = "delete from goods where id = 1";
		int i = JDBCUtil.executeSQL(sql);
		System.out.println(i);
	}

	//查询
	@Test
	public void testSelect() throws SQLException {
		String sql = "select * from goods";
		ResultSet rs = JDBCUtil.search(sql);
		while(rs.next()) {
			int id = rs.getInt("id"); //表中字段为id的值，类型是int型
			String name = rs.getString("g_name"); //表中字段为g_name的值，类型是varchar
			double price = rs.getDouble("g_price"); // 表中字段为g_priced值，类型为double
			Date date = rs.getDate("g_date");//表中字段为g_dated值，类型为date
			int cnt = rs.getInt("cnt");//表中字段为cnt的值，类型为int
			
			System.out.println("id："+id+"，名字："+name+"，价格："+price+"，生产日期："+date+"，库存："+cnt);
		}
		//关闭数据库
		JDBCUtil.close();
		
	}
}