Java用类实现对Student数据表(Mysql)的操作（增删改查）

叁歲伎倆 2022-05-26 02:42 248阅读 0赞

Java连接mysql数据库利用了mysql-JDBC的jar包，查询操作用的是executeQuery（）方法，除了查询操作外的增删改都是用executeUpdate（）方法实现。

代码：

Student类

package test;
    
    public class Student {
    	private int id;
    	private String name;
    	private int age;
    
    	public Student(int id, String name, int age) {
    		super();
    		this.id = id;
    		this.name = name;
    		this.age = age;
    	}
    
    	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;
    	}
    
    	@Override
    	public String toString() {
    		return "Student [id=" + id + ", name=" + name + ", age=" + age + "]";
    	}
    
    	
    }

Sql类：

package test;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Scanner;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class Sql {
    	Connection con;
    
    	public Connection getConn() throws SQLException, ClassNotFoundException {
    		Class.forName("com.mysql.jdbc.Driver");
    		Connection con = DriverManager.getConnection(
    				"jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8", "root", "helloworld");
    		System.out.println(con);
    		return con;
    	}
    
    	public int addStu(Student st) throws SQLException, ClassNotFoundException {
    		con=getConn();
    		try {
    			con = getConn();
    		} catch (ClassNotFoundException | SQLException e) {
    			e.printStackTrace();
    		}
    		String sql_insert = "insert into student(id, name, age) values(?, ?, ?)";
    		PreparedStatement pst = con.prepareStatement(sql_insert);
    		pst.setInt(1, st.getId());
    		pst.setString(2, st.getName());
    		pst.setInt(3, st.getAge());
    		int rest = pst.executeUpdate();
    		pst.close();
    		con.close();
    		return rest;
    	}
    	public int deleteStu(int id) throws ClassNotFoundException, SQLException {
    	  con=getConn();
    	  String sql="delete from student where id=?";
    	  PreparedStatement pst = con.prepareStatement(sql);
    	  pst = con.prepareStatement(sql);
    	  pst.setInt(1,id);
    	  
    	  int result=pst.executeUpdate();
    	  pst.close();
    	  con.close();
    	  return result;  
    	}
    	public int updateStu(int id, int flag) throws SQLException, ClassNotFoundException {
    		con=getConn();
    		Scanner cin = new Scanner(System.in);
    		String name;
    		int age;
    		String sql;
    		int rst = 0;
    		if(flag == 1) {
    			name = cin.next();
    			sql = "update student set name = ? where id = ?";
    			PreparedStatement pst = con.prepareStatement(sql);
    			pst.setString(1, name);
    			pst.setInt(2, id);
    			rst = pst.executeUpdate();
    			pst.close();
    		}
    		else if(flag == 2) {
    			age = cin.nextInt();
    			sql = "update student set age = ? where id = ?";
    			PreparedStatement pst = con.prepareStatement(sql);
    			pst.setInt(1, age);
    			pst.setInt(2, id);
    			rst = pst.executeUpdate();
    			pst.close();
    		}
    		else if(flag == 3) {
    			age = cin.nextInt();
    			name = cin.next();
    			sql = "update student set name = ?, age = ? where id = ?";
    			PreparedStatement pst = con.prepareStatement(sql);
    			pst.setString(1, name);
    			pst.setInt(2, age);
    			pst.setInt(3, id);
    			rst = pst.executeUpdate();
    			pst.close();
    		}
    		
    		con.close();
    		return rst;
    	}
    
    	public List<Student> queryAll() throws SQLException, ClassNotFoundException {
    		con=getConn();
    		List<Student> stu = new ArrayList<Student>();
    		String sql = "select * from student";
    		PreparedStatement pst = con.prepareStatement(sql);
    		ResultSet rst = pst.executeQuery();
    		while (rst.next()) {
    			int id = rst.getInt(1);
    			String name = rst.getString(2);
    			int age = rst.getInt(3);
    			stu.add(new Student(id, name, age));
    			//System.out.println(id + "---" + name + "---" + age);
    		}
    		pst.close();
    		con.close();
    		return stu;
    	}
    
    	public void queryById(int id) throws SQLException, ClassNotFoundException {
    		con=getConn();
    		String sql = "select name, age from student where id=?";
    		PreparedStatement pst = con.prepareStatement(sql);
    		pst.setInt(1, id);
    		ResultSet rst = pst.executeQuery();
    		if(rst.next()!= false) {
    			String name = rst.getString("name");
    			int age = rst.getInt("age");
    			System.out.println("学号为"+id+"的学生："+"\n"+"姓名： "+name+"\n"+"年龄: "+age+"\n");
    		}
    		pst.close();
    		con.close();
    	}
    }

Main类;

package test;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    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 Main {
    	public static void main(String[] args) throws ClassNotFoundException, SQLException {
    		Scanner cin = new Scanner(System.in);
    		Sql mysql_obj = new Sql();
    		int order = 0;
    		System.out.println("请输入指令来进行操作：");
    		System.out.println("输入1添加学生信息");
    		System.out.println("输入2删除学生信息");
    		System.out.println("输入3修改学生信息");
    		System.out.println("输入4根据学号查询");
    		System.out.println("输入5查询全部学生信息");
    		while(cin.hasNext()) {
    			
    			order = cin.nextInt();
    			if(order == 1) {
    				System.out.println("输入学生信息id name age");
    				Student stu = new Student(cin.nextInt(), cin.next(), cin.nextInt());
    				int rst = mysql_obj.addStu(stu);
    				if(rst == 1) {
    					System.out.println("success!");
    				}
    				else {
    					System.out.println("failed!");
    				}
    			}
    			else if(order == 2) {
    				System.out.println("输入学生id");
    				int id = cin.nextInt();
    				int rst = mysql_obj.deleteStu(id);
    				if(rst == 1) {
    					System.out.println("success!");
    				}
    				else {
    					System.out.println("failed!");
    				}
    			}
    			else if(order == 3) {
    				System.out.println("输入学生id");
    				int id = cin.nextInt();
    				System.out.println("更改姓名输入1  更改年龄输入2  两者都更改输入3");
    				int flag = cin.nextInt();
    				int rst = mysql_obj.updateStu(id, flag);
    				if(rst == 1) {
    					System.out.println("success!");
    				}
    				else {
    					System.out.println("failed!");
    				}
    			}
    			else if(order == 4) {
    				System.out.println("输入学生id");
    				int id = cin.nextInt();
    				mysql_obj.queryById(id);
    			}
    			else if(order == 5) {
    				List<Student> stu = new ArrayList<Student>();
    				stu = mysql_obj.queryAll();
    				for(Student st: stu) {
    					System.out.println(st.toString());
    				}
    			}
    		}
    		
    	}
    }