JAVA实现对MySQL数据库的增删改查

JAVA实现对MySQL数据库的增删改查(学生信息管理系统案例)

Student类

public class Student {
	private String id;
	private String name;
	private String gender;
	private String birthday;
	public Student(){}
	public Student(String id,String name,String gender,String birthday){
		super();
		this.id=id;
		this.name=name;
		this.gender=gender;
		this.birthday=birthday;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getBirthday() {
		return birthday;
	}
	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}
	public String toString() {
		return id + "\t" + name + "\t" + gender + "\t" + birthday;
	}
}

JdbcUtils类

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtils {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	private static Properties p = null;
	static {
		try {
			// 加载配置文件内容到p对象中
			InputStream is = JdbcUtils.class.getResourceAsStream("/dbconfig.properties");
			p = new Properties();
			p.load(is);
			// 获取JDBC参数
			driver = p.getProperty("driver");
			url = p.getProperty("url");
			username = p.getProperty("username");
			password = p.getProperty("password");
			Class.forName(driver);
		} catch (IOException e) {

			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 封装获取Connection对象
	public static Connection getConnection() throws SQLException {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	// 封装获取PreparedStatement对象
	public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws SQLException {
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(sql);
		} catch (SQLException E) {
			E.printStackTrace();
		}
		return ps;
	}

	// 封装关闭资源方法
	public static void clossAll(ResultSet rs, PreparedStatement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}

Fuction类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class Function {
	private static Student student;
	final static Scanner input = new Scanner(System.in);

	// 查询所有记录
	public static List<Student> query(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
		String sql = "select * from student;";
		List<Student> list = new ArrayList<Student>();
		ps = JdbcUtils.getPreparedStatement(conn, sql);
		rs = ps.executeQuery();
		System.out.println("学号" + "\t" + "姓名" + "\t" + "性别" + "\t" + "出生年月");
		System.out.print("-------------------------------------\n");
		try {
			while (rs.next()) {
				Student student = new Student(rs.getString("id"), rs.getString("name"), rs.getString("gender"),
						rs.getString("birthday"));
				list.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			JdbcUtils.clossAll(rs, ps, conn);
		}
		return list;
	}

	// 查询学生信息
	public static Student select(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
		String sql = "select * from student where name=?;";
		System.out.println("请输入你要查询的学生姓名>>");
		String a = input.next();
		try {
			ps = JdbcUtils.getPreparedStatement(conn, sql);
			ps.setString(1, a);
			rs = ps.executeQuery();
			while (rs.next()) {
				student = new Student(rs.getString("id"), rs.getString("name"), rs.getString("gender"),
						rs.getString("birthday"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.clossAll(rs, ps, conn);
		}
		return student;
	}

	// 插入一条学生信息
	public static void insert(PreparedStatement ps, Connection conn) throws SQLException {
		System.out.println("请输入学号>>");
		student.setId(input.next());
		System.out.println("请输入姓名>>");
		student.setName(input.next());
		System.out.println("请输入性别>>");
		student.setGender(input.next());
		System.out.println("请输入出生年月>>");
		student.setBirthday(input.next());
		String sql = "insert into student values(?,?,?,?)";
		ps = JdbcUtils.getPreparedStatement(conn, sql);
		try {
			ps.setString(1, student.getId());
			ps.setString(2, student.getName());
			ps.setString(3, student.getGender());
			ps.setString(4, student.getBirthday());
			System.out.println("插入学生信息成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.clossAll(null, ps, conn);
		}
	}

	// 修改学生信息
	public static void update(PreparedStatement ps, Connection conn) throws SQLException {
		System.out.println("请输入需要被修改的学生姓名>>");
		String oldName = input.next();
		System.out.println("你要修改该学生的哪些信息>>\n" + "【a】 -> 姓名\n" + "【b】 -> 性别\n" + "【c】 -> 出生年月日");
		String x = input.next();
		try {
			switch (x) {
			case "a":
				System.out.println("请输入将被替换的姓名>>");
				String newName = input.next();
				String sql = "update student set name=? where name=?";
				ps = JdbcUtils.getPreparedStatement(conn, sql);
				ps.setString(1, newName);
				ps.setString(2, oldName);

				;
				break;
			case "b":
				System.out.println("请输入将被替换的性别>>");
				String newGender = input.next();
				String sql2 = "update student set gender=? where name=?";
				ps = JdbcUtils.getPreparedStatement(conn, sql2);
				ps.setString(1, newGender);
				ps.setString(2, oldName);
				;
				break;
			case "c":
				System.out.println("请输入将被替换的出生年月>>");
				String newBirthday = input.next();
				String sql3 = "update student set birthday=? where name=?";
				ps = JdbcUtils.getPreparedStatement(conn, sql3);
				ps.setString(1, newBirthday);
				ps.setString(2, oldName);

				;
				break;

			default:
				System.out.print("输入正确序号");
				;
				break;
			}
			System.out.println("修改学生信息成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.clossAll(null, ps, conn);
		}

	}

	// 删除数据,以姓名输入!
	public static void delete(PreparedStatement ps, Connection conn) throws SQLException {
		System.out.println("请输入需要被删除学生的姓名>>");
		student.setName(input.next());
		String sql = "delete from student where name=?";
		try {
			ps = JdbcUtils.getPreparedStatement(conn, sql);
			ps.setString(1, student.getName());
			System.out.println("删除学生信息成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.clossAll(null, ps, conn);
		}

	}

	// 分页显示学生信息
	public static List<Student> TestPage(ResultSet rs, PreparedStatement ps, Connection conn, int index, int size)
			throws SQLException {
		String sql = "select * from student limit ?,?";
		List<Student> list = new ArrayList<Student>();
		ps = JdbcUtils.getPreparedStatement(conn, sql);
		ps.setInt(1, (index - 1) * size);
		ps.setInt(2, size);
		rs = ps.executeQuery();
		System.out.println("学号" + "\t" + "姓名" + "\t" + "性别" + "\t" + "出生年月");
		System.out.print("-------------------------------------\n");
		try {
			while (rs.next()) {
				Student student = new Student(rs.getString("id"), rs.getString("name"), rs.getString("gender"),
						rs.getString("birthday"));
				list.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			JdbcUtils.clossAll(rs, ps, conn);
		}
		return list;

	}
}

Test类

import java.util.List;
import java.sql.*;
import java.util.Scanner;
public class main {
	final static Scanner input = new Scanner(System.in);
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;  
        boolean flag = false;
        while (true) { 
            try { 
                conn = JdbcUtils.getConnection();
                System.out.println("" +
                		"~~~~~~~~~~~~~~~~~学生  信息  管理   系统~~~~~~~~~~~~~~~\n" +
                        "~~~~~~~~~~~~~~~~~  请选择相关功能    ~~~~~~~~~~~~~~~\n" +
                        "【1】 -> 显示所有学生信息;\n" +
                        "【2】 -> 查询学生信息;\n" +
                        "【3】 -> 添加新的学生信息;\n" +
                        "【4】 -> 修改学生信息;\n" +
                        "【5】 -> 删除学生信息;\n" +
                        "【6】 -> 分页显示学生信息;\n" +
                        "【0】 -> 退出程序!\n>>");
                int x = input.nextInt();
                switch (x) {
                    case 1: 
                    	List<Student> list=Function.query(rs,ps,conn);
                    	for (int i = 0; i < list.size(); i++) {
							System.out.println(list.get(i).getId()+"\t"+list.get(i).getName()+"\t"+list.get(i).getGender()+"\t"+list.get(i).getBirthday());
						}
                    	break;
                    case 2:
                    	Student student=Function.select(rs,ps,conn);
                    	if (student!=null) {
                    		System.out.println("该学生的信息为:");
                    		System.out.println(student.getId()+"\t"+student.getName()+"\t"+student.getGender()+"\t"+student.getBirthday());
							
						}
                    	break;
                    case 3: Function.insert(ps,conn);break;
                    case 4: Function.update(ps,conn);break;
                    case 5: Function.delete(ps,conn);break;
                    case 6: 
                    	 int index=1;
                         int size=3;
                         System.out.println("输入‘n’显示第一页,再次输入显示下一页(一页3行)");
                         while(true){
                        	 String anyKey=input.next();
                             if (anyKey.equalsIgnoreCase("n")) {
                            	List<Student> list2= Function.TestPage(rs,ps,conn, index, size);
                            	for (int i = 0; i < list2.size(); i++) {
        							System.out.println(list2.get(i).getId()+"\t"+list2.get(i).getName()+"\t"+list2.get(i).getGender()+"\t"+list2.get(i).getBirthday());
        						}
                            	index++;
    						} 
                             else {
								break;
							}
                         }
                        
                        break;
                    case 0: flag = true;break;
                    default: 
                    	System.out.println("!!!!!请输入正确的功能序号!!!!!");
                    	break;
                }  
            } catch (SQLException e) { 
            	e.printStackTrace();
            } 
            if (flag==true) break;//退出无线循环
        }
        System.out.println("!!!!!谢谢使用!!!!!");
    }
     
}

dbconfig.properties 配置文件

driver=com.mysql.cj.jdbc.Driver
url =jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC
username=你的账户名
password=你的密码

创建数据库:

CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `gender` char(1) NOT NULL,
  `birthday` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO student (`id`, `name`, `gender`, `birthday`) VALUES ('1', '马盈辉', '男', '1999-10-03');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('2', '李龙飞', '男', '1992-11-21');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('3', '郑子科', '男', '1899-01-02');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('4', '何春阳', '女', '1993-10-22');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('5', '陈冠希', '男', '1999-10-03');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('6', '小刚', '男', '2001-05-10');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('7', '刘一手', '男', '2003-10-16');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('8', '孙悟空', '男', '1992-10-23');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('9', '猪八戒', '男', '1998-10-23');


版权声明:本文为qq_44694720原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。