JDBC的CRUD（通过工具类和 PreparedStatement）

最新推荐文章于 2022-08-15 15:38:55 发布

原创最新推荐文章于 2022-08-15 15:38:55 发布 · 698 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#JDBC

工具类专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了如何通过配置db.properties文件，使用JDBCUtil工具类配合PreparedStatement，实现数据库的增删改查（CRUD）操作。包括创建数据、检索数据、更新数据和删除数据的步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

- 配置文件：db.properties

//驱动名
driverClass=com.mysql.jdbc.Driver
//homework为数据库名
url=jdbc:mysql://localhost:3306/homework
//mysql账号
username=root
//密码
password=1997

- 工具类：JDBCUtil

package jdbc;

import java.io.FileInputStream;
import java.io.IOException;
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.Properties;

public class JDBCUtil {
	//定义需要的常量
	private static final String driverClassName;
	private static final String url;
	private static final String username;
	private static final String password;
	
	//通过静态代码块加载配置文件中的数据
	static {
		Properties pp = new Properties();
		try {
			pp.load(new FileInputStream("src/db.properties"));
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		driverClassName=pp.getProperty("driverClass");
		url = pp.getProperty("url");
		username =pp.getProperty("username");
		password = pp.getProperty("password");
		
	}
	
	/*
	 * 加载驱动的方法
	 */
	public static void loadDriver() {
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	
	/*
	  *  获取连接的方法
	 */
	public static Connection getConnection(){
		//加载驱动
		loadDriver();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;	
	}
	
	/*
	 * 释放增删改资源的方法:
	 */
	public static void release(PreparedStatement ps,Connection conn) {
		if(ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//加速资源回收
			ps = null;
		}
		
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//加速资源回收
			conn = null;
		}
	}
	
	/*
	 * 释放查资源的方法:
	 */
	public static void release(ResultSet rs,Statement stmt,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//加速资源回收
			rs = null;
		}
		
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//加速资源回收
			stmt = null;
		}
		
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//加速资源回收
			conn = null;
		}
	}
}

- CRUD_Create 增数据

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.junit.Test;

public class CRUD_Create {
	@Test
	public void  create() {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//获得连接
			conn = JDBCUtil.getConnection();
			//编写SQL
			String sql = "insert into time values(?,?,?)";
			//预编译sql
			ps = conn.prepareStatement(sql);
			//设置参数
			ps.setInt(1, 4);
			ps.setDouble(2, 15.00);
			ps.setInt(3, 50);
			int num = ps.executeUpdate();
			if(num>0) {
				System.out.println("插入成功");
			}else {
				System.out.println("插入失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			//释放资源
			JDBCUtil.release(ps, conn);
		}
	}
}

- CRUD_Retrieve 查数据

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;

public class CRUD_Retrieve {
	@Test
	public void Retrieve() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
				conn = JDBCUtil.getConnection();
				String sql = "select * from time";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while(rs.next()) {
					System.out.println(rs.getInt("id")+" "+rs.getDouble("time")+" "+rs.getInt("values"));
				}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(rs, ps, conn);
		}
	}
}

- CRUD_Updata 改数据

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

public class CRUD_Updata {
	@Test
	public void updata() {
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = JDBCUtil.getConnection();
			String sql = "update time set time = ? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setDouble(1, 15.55);
			ps.setInt(2, 3);
			int num = ps.executeUpdate();
			if(num>0) {
				System.out.println("修改成功");
			}else {
				System.out.println("修改失败");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.release(ps, conn);
		}
	}
}

-CRUD_Delete 删数据

package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;

public class CRUD_Delete {
	@Test
	public void delete() {
		 Connection conn = null;
		 PreparedStatement ps = null;
		 try {
			 conn = JDBCUtil.getConnection();
			 String sql = "delete from time where id = ?";
			 ps = conn.prepareStatement(sql);
			 ps.setInt(1, 4);
			 int num = ps.executeUpdate();
			 if(num>0) {
				 System.out.println("删除成功");
			 }else {
				 System.out.println("删除失败");
			 }
		 }catch(Exception e) {
			 e.printStackTrace();
		 }finally {
			 JDBCUtil.release(ps, conn);
		 }
	}
}