JDBC对数据库进行增删改查【面向对象版】

写在前面的话：

1. 参考资料：尚硅谷视频
2. 本章内容：使用面向对象的思想，进行对数据库的增删改查
3. IDE：eclipse
4. JDK：Java8
5. MySQL：mysql  Ver 8.0.26 for Win64 on x86_64

---

目录

 1.首先，创建一张学生表

 2.代码部分

 3.截图

---

 

 1.首先，创建一张学生表

如何创建一张表https://blog.csdn.net/qq_56402474/article/details/124894951?spm=1001.2014.3001.5501

 2.代码部分

JdbcTest.java

package exer02;

import java.text.SimpleDateFormat;
import java.util.Date;


import org.junit.Test;

public class JdbcTest {

	@Test
	public void test1() throws Exception {

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String[] name = { "刘旎娜", "刘唯芳", "刘玥妘", "刘欣儿", "刘浚雯", "刘丁菡", "刘音颖", "刘静晓", "刘锦姣", "刘昭萱", "刘书云", "刘丹钰", "刘涵瑶",
				"刘晨薪", "刘羽童", "刘芮悠", "刘嫣晨", "刘槿萁", "刘朝英", "刘扬琴", "刘柠涛", "刘卉一", "刘菁妤", "刘昀蓉", "刘楚洪", "刘菡婌", "刘薇涵", "刘洪宸",
				"刘玉芹", "刘姝梓", "刘尚薇", "刘燚圭", "刘妘嫣", "刘施嘉", "刘慧赣", "刘琴心", "刘蕾雪", "刘绎涵", "刘音梦", "刘忆嘉", "刘誉函", "刘依月", "刘璐欣",
				"刘琳蕾", "刘轲文", "刘伊嵘", "刘冬珺", "刘煜凡", "刘丽枝", "刘荷冉", "刘梦茜", "刘荣娜", "刘俐昪", "刘瑜然", "刘若敏", "刘慕熙", "刘晴月", "刘晨微",
				"刘玉如", "刘韶婷" };

		// 添加学生
		Student student = null;
		for (int i = 1006; i < 1020; i++) {
			
			student = createStudent(i, name[i-1006], "女", sdf.parse("2000-12-23 12:00:42"), "19218229802", "重庆市沙坪坝区都市花园中路111号");
			addStudent(student);
		}

		// 查询学生
//		searchStudent(1001);
//		searchStudent(1002);
//		searchStudent(1003);
//		searchStudent(1004);
//		searchStudent(1005);
//		searchStudent(1006);
//		searchStudent(1007);

		// 删除学生
//		deleteStudent(1006);

	}

	/**
	 * 创建一个学生
	 * 
	 * @param id       学号
	 * @param stuname  姓名
	 * @param sex      性别
	 * @param birth    生日
	 * @param telphone 电话
	 * @param addr     地址
	 * @return 返回一个学生
	 */
	public Student createStudent(int id, String stuname, String sex, Date birth, String telphone, String addr) {

		// 创建学生
		Student student = new Student(id, stuname, sex, birth, telphone, addr);

		return student;
	}

	/**
	 * 添加一个新学生
	 * 
	 * @param student 学生
	 */
	public void addStudent(Student student) {

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		// 1.准备SQL语句
		String sql = "INSERT INTO student VALUES(" + student.getId() + ",'" + student.getStuname() + "','"
				+ student.getSex() + "','" + sdf.format(student.getBirth()) + "','" + student.getTelphone() + "','"
				+ student.getAddr() + "');";

		// 2.调用JdbcTools里面的执行工具
		JdbcTools.update(sql);
		System.out.println("添加学生成功！");
	}

	/**
	 * 删除一个学生
	 * 
	 * @param student 学生
	 */
	public void deleteStudent(int id) {

		// 1.准备SQL语句
		String sql = "DELETE FROM student " + "WHERE id = " + id + ";";

		// 2.调用JdbcTools里面的执行工具
		JdbcTools.update(sql);
		System.out.println("删除学生成功！");
	}

	/**
	 * 查找学生【通过学号】
	 * 
	 * @param id
	 */
	public void searchStudent(int id) {

		// 1.准备SQL语句
		String sql = "SELECT id,stuname,sex,birth,telphone,addr FROM student " + "WHERE id = " + id + ";";

		// 2.通过SQL语句获取学生
		Student student = JdbcTools.getStudent(sql);

		// 3.打印学生基本信息
		printStudent(student);

	}

	/**
	 * 打印学生信息
	 * 
	 * @param student
	 */
	private void printStudent(Student student) {

		if (student != null) {

			// 当student 不为空时，打印输出
			System.out.println("==学生基本信息==");
			System.out.println("学号：" + "\t" + student.getId());
			System.out.println("姓名：" + "\t" + student.getStuname());
			System.out.println("性别：" + "\t" + student.getSex());
			System.out.println("生日：" + "\t" + student.getBirth());
			System.out.println("电话：" + "\t" + student.getTelphone());
			System.out.println("地址：" + "\t" + student.getAddr());
		} else {
			System.out.println("未找到该学生,请换一个学号或者姓名试试");
		}

	}

	/**
	 * 查找学生【通过姓名】
	 * 
	 * @param name
	 */
	public void searchStudent(String name) {
		// 1.准备SQL语句
		String sql = "SELECT id,stuname,sex,birth,telphone,addr FROM student " + "WHERE name = '" + name + "';";

		// 2.通过SQL语句获取学生
		Student student = JdbcTools.getStudent(sql);

		// 3.打印学生基本信息
		printStudent(student);
	}

}

 JdbcTools.java

package exer02;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
 * JDBC工具类： 1.连接数据库 2.释放连接 3.对数据库进行增删改 4.对数据库进行查询
 * 
 * @author star-dream
 *
 */
public class JdbcTools {

	/**
	 * 获取数据库连接
	 * 
	 * @return 返回一个数据库连接 Connection
	 * @throws Exception
	 */
	private static Connection getConnection() throws Exception {

		// 准备连接数据库的4个字符串
		String driver = null;
		String jdbcUrl = null;
		String jdbcUser = null;
		String jdbcPassword = null;

		// 创建输入流的对象
		InputStream inputStream = JdbcTools.class.getClassLoader().getResourceAsStream("exer02//jdbc.properties");

		// 创建Properties的对象
		Properties properties = new Properties();

		// 加载properties配置文件到程序中
		properties.load(inputStream);

		// 获取文件中的数据
		driver = properties.getProperty("driver");
		jdbcUrl = properties.getProperty("jdbcURL");
		jdbcUser = properties.getProperty("user");
		jdbcPassword = properties.getProperty("password");

		// 获取数据库驱动程序
		Class.forName(driver);

		// 进行连接
		Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);

		// 返回连接
		return conn;
	}
	
	/**
	 * 释放连接
	 * 
	 * @param connection 获取的连接
	 * @param statement
	 * @param resultSet     结果集
	 */
	private static void release(Connection connection, Statement statement,ResultSet resultSet) {
		
		
		//判断结果集是否为null
		if(resultSet != null) {
			try {
				resultSet.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		// 判断连接是否为空
		if (statement != null) {

			// 为了保证连接必须关闭,使用异常处理，防止因为statement关闭出现异常,connection未能够关闭
			try {
				statement.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 修改数据库 INSERT UPDATE DELETE
	 * 
	 * @param sql 传入一个SQL语句
	 */
	public static void update(String sql) {

		// 获取数据库连接
		Connection conn = null;
		Statement statement = null;

		try {

			// 获取连接
			conn = getConnection();
			statement = conn.createStatement();

			// 执行SQL语句
			statement.executeUpdate(sql);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			// 释放连接
			release(conn, statement,null);
		}
	}
	
	/**
	 * 对数据库进行查询
	 * @param sql
	 * @return 获取查询到的学生
	 */
	public static Student getStudent(String sql) {
		
		Student student = null;
		
		//数据库连接
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try {
			
			connection = getConnection();
			
			statement = connection.createStatement();
			
			resultSet =  statement.executeQuery(sql);
			
			//判断是否存在
			if (resultSet.next()) {
				student = new Student(resultSet.getInt(1),resultSet.getString(2), resultSet.getString(3), 
						resultSet.getDate(4), resultSet.getString(5), resultSet.getString(6));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//释放连接
			release(connection, statement, resultSet);
		}		
		
		//将结果返回
		return student;
	}
}

student.java 

package exer02;

import java.util.Date;

public class Student {
	
	int id;//学号
	String stuname;//姓名
	String sex;//性别
	Date birth;//生日
	String telphone;//电话
	String addr;//地址
	
	public Student() {

	}

	public Student(int id, String stuname, String sex, Date birth, String telphone, String addr) {
		super();
		this.id = id;
		this.stuname = stuname;
		this.sex = sex;
		this.birth = birth;
		this.telphone = telphone;
		this.addr = addr;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getStuname() {
		return stuname;
	}

	public void setStuname(String stuname) {
		this.stuname = stuname;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Date getBirth() {
		return birth;
	}

	public void setBirth(Date birth) {
		this.birth = birth;
	}

	public String getTelphone() {
		return telphone;
	}

	public void setTelphone(String telphone) {
		this.telphone = telphone;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", stuname=" + stuname + ", sex=" + sex + ", birth=" + birth + ", telphone="
				+ telphone + ", addr=" + addr + "]";
	}

}

 jdbc.properties文件内容

driver=com.mysql.cj.jdbc.Driver
jdbcURL=jdbc:mysql://localhost:3306/_db3
user=root
password=root

 3.截图

 

 特别说明：本篇文章只是对另外的一篇文章作为一个补充，该文章更加完善。

JDBC对数据库进行增删改查https://blog.csdn.net/qq_56402474/article/details/124764858?spm=1001.2014.3001.5501