JDBCUtil连接工具

package util;

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

/**
 * 操作数据库的工具类
 * @author 樊**
 *
 */
public class JDBCUtil {
	private static final String DB_USER_NAME = "root";//数据库的用户名
	private static final String DB_PASSWORD ="root";//数据库密码
	private static final String DB_NAME = "hello_world";//数据库名称
	private static final String IP = "127.0.0.1";//127.0.0.1安装mysql数据库的服务器的ip地址
	private static final String	URL="jdbc:mysql://"+IP+":3306/"+DB_NAME;//mysql连接地址
	//private static final String URL = "jdbc:mysql://"+IP+":3306/"+DB_NAME+"?serverTimezone=UTC";//mysql连接地址
	
	private static Connection conn;//创建连接对象
	private static Statement stmt;//执行sql语句的对象，执行命令的对象
	private static ResultSet rs;//保存select之后查询结果的数据的对象
	
	/**
	 * 获得连接对象
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	private static Connection getCon() throws ClassNotFoundException, SQLException{
		//加载mysql驱动
		//Class.forName("com.mysql.cj.jdbc.Driver");
		Class.forName("com.mysql.jdbc.Driver");
		if(conn == null || conn.isClosed()){//防止多次赋值
			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)的操作
	 * @param sql
	 * @return
	 */
	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）的操作
	 * @param sql
	 * @return
	 */
	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.ResultSet;
import java.sql.SQLException;
import java.util.Date;

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-01',100)";
		int i = JDBCUtil.executeSQL(sql);
		System.out.println(i);
	}
	
	/**
	 * 修改数据库中的内容
	 */
	@Test
	public void testUpdate(){
		String sql = "update goods set g_price=2.5 where g_name='可口可乐'";
		int i = JDBCUtil.executeSQL(sql);
		System.out.println(i);
	}
	
	/**
	 * 删除
	 */
	@Test
	public void testDelete(){
		String sql = "delete from goods where g_name = '可口可乐'";
		int i = JDBCUtil.executeSQL(sql);
		System.out.println(i);
	}
	
	/**
	 * 查询
	 * @throws SQLException 
	 */
	@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");//表中类型是varchar
			 double price = rs.getDouble("g_price");
			 Date date = rs.getDate("g_date");
			 int cnt = rs.getInt("cnt");
			 System.out.println("id:"+id+"商品名："+name+"价格："+price+"生产日期："+date+"库存："+cnt);
		 }
		 JDBCUtil.close();
	}
}