JDBC增删改查详解——java

需要：
jdk1.8，
mysql8.0.13，
mysql-connector-java-8.0.16.jar

看代码：

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

public class DBUtil {
	// 对于属性的封装
	private static String driver = "com.mysql.cj.jdbc.Driver";
	private static String url = "jdbc:mysql://localhost:3306/test?userSSl=false&allowPublicKeyRetreval=True&serverTimezone=UTC";
	private static String user = "root";
	private static String password = "root";

	// 1.获取驱动
	static {
		try {
			Class.forName(driver);
		} catch (Exception e) {
			System.out.println("找不到驱动程序类 ，加载驱动失败！");
			e.printStackTrace();
		}
	}

	// 2.创建连接
	public static Connection get_Conn() throws SQLException {
		Connection conn = DriverManager.getConnection(url, user, password);
		// System.out.println("数据库连接成功"+conn);
		return conn;
	}

	// 3.关闭连接
	public static void get_CloseConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws SQLException {
		if (rs != null) {
			rs.close();
		}
		if (pstm != null) {
			pstm.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	public static void main(String[] args) {
		try {
			get_Conn();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

//实体类
public class Student {
	private int id;
	private String name;
	private int age;

	// get方法和set方法
	public int getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public Student(int id, String name, int age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}

	public Student(String name, int age) {
		super();
		this.name = name;
		this.age = age;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
	}

}

public class UpdateStudent {
	int choose_id;
	String update_name;
	int new_age;

	public UpdateStudent(int choose_id, String update_name, int new_age) {
		super();
		this.choose_id = choose_id;
		this.update_name = update_name;
		this.new_age = new_age;
	}

}

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

public class Service {

	Connection conn = null;
	PreparedStatement pstm = null;
	ResultSet rs = null;//结果集

	// 1.增加
	public void addstudent(Student student) {
		String sql = "insert into student(name,age) values(?,?)";
		try {
			// 1.获取连接
			conn = DBUtil.get_Conn();
			// 2.获取存放sql语句的对象
			pstm = conn.prepareStatement(sql);
			// 3.填坑
			pstm.setString(1, student.getName());
			pstm.setInt(2, student.getAge());
			// 4.执行sql并得到结果
			int i = pstm.executeUpdate();
			// 5.处理结果
			if (i > 0) {
				System.out.println("增加成功");
			} else {
				System.out.println("增加失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 6.关闭连接
			try {
				DBUtil.get_CloseConn(null, pstm, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 2.删除
	public void deletestudent(Student student) {
		// String sql = "delete from student where name='%"+name+"%'";
		String sql = "delete from student where name=?";
		try {
			// 1.获取连接
			conn = DBUtil.get_Conn();
			// 2.获取存放sql语句的对象
			pstm = conn.prepareStatement(sql);
			// 填坑
			pstm.setString(1, student.getName());
			// 3.执行sql并得到结果
			int i = pstm.executeUpdate();
			// 4.处理结果
			if (i > 0) {
				System.out.println("删除成功");
			} else {
				System.out.println("删除失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 5.关闭连接
			try {
				DBUtil.get_CloseConn(null, pstm, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 3.修改
	public void updatestudent(UpdateStudent updateStudent) {
		String sql = "update student set name=? , age=? where id=?";
		try {
			conn = DBUtil.get_Conn();
			pstm = conn.prepareStatement(sql);
			// 填坑
			pstm.setString(1, updateStudent.update_name);
			pstm.setInt(2, updateStudent.new_age);
			pstm.setInt(3, updateStudent.choose_id);
			int i = pstm.executeUpdate();
			if (i > 0) {
				System.out.println("修改成功");
			} else {
				System.out.println("修改失败");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.get_CloseConn(null, pstm, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 4.查询用户列表
	public void selectAllstudent() {
		String sql = "select * from student";
		try {
			conn = DBUtil.get_Conn();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				System.out.print("学生ID\t学生姓名\t学生年龄\n");
				System.out.print(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getInt(3) + "\n");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.get_CloseConn(rs, pstm, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 5.依据关键字查询用户
	public void selectstudent(String name) {
		String sql = "select * from student where name like '%" + name + "%'";
		try {
			conn = DBUtil.get_Conn();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				System.out.print("学生ID\t学生姓名\t学生年龄\n");
				System.out.print(rs.getInt(1) + "\t" + rs.getString(2) + "\t" + rs.getInt(3));
				System.out.println();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.get_CloseConn(rs, pstm, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 查找指定用户
	public int selectOnestudent(String name) {
		String sql = "select * from student where name like '%" + name + "%'";
		try {
			conn = DBUtil.get_Conn();
			pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				return 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBUtil.get_CloseConn(rs, pstm, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	// 打印菜单
	public static void printMenu() {
		System.out.println("=====菜单=====");
		System.out.println("1.增加学生");
		System.out.println("2.删除学生");
		System.out.println("3.修改学生信息");
		System.out.println("4.查找指定学生");
		System.out.println("5.输出学生信息列表");
		System.out.println("0.退出");
		System.out.println("请输入菜单选项：");
	}
}

import java.util.Scanner;

public class Test {
	public static void main(String[] args) {
		Service service = new Service();
		Scanner sc = new Scanner(System.in);
		int choose;
		Service.printMenu();
		choose = sc.nextInt();
		while (choose != 0) {
			switch (choose) {
			case 1:
				System.out.print("请输入要添加的学生名：");
				String addName = sc.next();
				while (service.selectOnestudent(addName) != 0) {
					System.out.print("该学生名已存在，请重新输入：");
					addName = sc.next();
				}
				System.out.print("请输入学生名的年龄：");
				int addAge = sc.nextInt();
				Student student = new Student(addName, addAge);
				service.addstudent(student);
				break;
			case 2:
				System.out.print("请输入要删除的学生的学生名和他的年龄：");
				String dltName = sc.next();
				int dltAge = sc.nextInt();
				Student dltstudent = new Student(dltName, dltAge);
				service.deletestudent(dltstudent);
				break;
			case 3:
				System.out.print("请输入要修改的学生信息的学生ID：");
				int choose_id = sc.nextInt();
				System.out.print("请输入新的学生名字：");
				String newName = sc.next();
				System.out.print("请输入" + newName + "的年龄：");
				int newAge = sc.nextInt();
				UpdateStudent updateStudent = new UpdateStudent(choose_id, newName, newAge);
				service.updatestudent(updateStudent);
				break;
			case 4:
				System.out.print("请输入要查询的学生名关键字：");
				String sltName = sc.next();
				service.selectstudent(sltName);
				break;
			case 5:
				service.selectAllstudent();
				break;
			}
			Service.printMenu();
			choose = sc.nextInt();
		}
		System.out.println("欢迎下次使用！");
	}
}