1、JDBC单表记录的增删改查

已知：

建立表student，并向表里插入几条记录，
create table student(
id int PRIMARY KEY auto_increment,
name varchar(20) not null,
schoolin date not null,
score float not null);

insert into student values(null,’李丽’,’2015-09-01’,86);
insert into student values(null,’王五’,’2016-09-01’,99);
insert into student values(null,’张三’,’2014-09-01’,88);

要求：

用JAVA程序实现如下功能：
1、向表中增加记录并显示所有记录（数据自己指定）；
2、从表中删除id=1的记录并显示所有记录；
3、修改表中记录：查询条件id=2，将name修改为：山东理工，修改完毕显示所有记录；
4、查询表中id=3的记录并显示；
提交说明：粘贴JAVA程序代码；

难度：20

评价标准：
（1）程序的基本结构明确，4分；
（2）增加功能实现，4分；
（3）删除功能实现，4分；
（4）修改功能实现，4分；
（5）查询单条记录实现，4分。
共计20分。

import java.sql.*;
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class Main {
	public static void main(String[] args) throws ClassNotFoundException, SQLException, ParseException {
		Scanner input = new Scanner(System.in);

		int id;
		float score;
		String name;
		Date schoolin;
		java.sql.Date date;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		
		//1、向表中增加记录并显示所有记录（数据自己指定）
		System.out.println("请输入学生姓名、入学时间、成绩");
		name = input.next();
		schoolin = sdf.parse(input.next());
		date = new java.sql.Date(schoolin.getTime());
		score = input.nextFloat();
		addStudent(name,date,score);
		System.out.println();
		
		//2、从表中删除id=1的记录并显示所有记录
		System.out.println("请输入需要删掉的 id ");
		id = input.nextInt();
		del_By_id(id);
		System.out.println();
		
		
		//3、修改表中记录：查询条件id=2，将name修改为：山东理工,修改完毕显示所有记录；
		System.out.println("请输入需要修改学生的id");
		id = input.nextInt();
		System.out.println("请输入修改后的姓名");
		name = input.next();
		updatebyid(id, name);
		System.out.println();
		
		System.out.println("显示全部信息");
		selectAll();
		
		//4、查询表中id=3的记录并显示
		System.out.println("请输入需要查询的id");
		id = input.nextInt();
		selectByid(id);
		System.out.println();
		
		input.close();	
	}
	
	//1、向表中增加记录并显示所有记录（数据自己指定）
	private static void addStudent(String name, java.sql.Date date, float score) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8","root","usbw");
		// ?useUnicode=true&characterEncoding=utf-8  输入会乱码
		
		String sqladd = "INSERT INTO student VALUES(NULL,?,?,?)";
		PreparedStatement pst = con.prepareStatement(sqladd);
		pst.setString(1, name);
		pst.setDate(2, date);
		pst.setFloat(3, score);
		//数字代表第几个'?'
		
		int result = pst.executeUpdate();
		System.out.println(result > 0 ? "数据插入成功":"数据插入失败");
		
		pst.close();
		con.close();
	}
	
	//2、从表中删除id=1的记录并显示所有记录
	private static void del_By_id(int id) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8","root","usbw");
	
		String sql = "DELETE FROM student WHERE id = ?";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setInt(1, id);
		int result = pst.executeUpdate();
		System.out.println(result > 0 ? "数据删除成功":"数据不存在");
		
		pst.close();
		con.close();
	}
	
	//3、修改表中记录：查询条件id=2，将name修改为：山东理工,修改完毕显示所有记录；
	private static void updatebyid(int id, String name) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8","root","usbw");
		String update = "update student set name=? WHERE id=?";
		PreparedStatement pst = con.prepareStatement(update);
		pst.setString(1, name);
		pst.setInt(2, id);
		
		int result = pst.executeUpdate();
		
		System.out.println(result > 0 ? "信息修改成功" : "信息修改失败");
		
		pst.close();
		con.close();
	}
	
	private static void selectAll() throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8","root","usbw");				
		String sql = "select * from student";
		PreparedStatement pst = con.prepareStatement(sql);
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//记录指针,初始化在第一条数据之前
		ResultSet rs = pst.executeQuery();
		while(rs.next()) {
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String schoolin = sdf.format(rs.getDate("schoolin"));
			float score = rs.getFloat("score");
			System.out.println(id+" "+name+" "+schoolin+" "+String.format("%.2f", score));
		}
		System.out.println();
		
		pst.close();
		con.close();
	}
	
	//4、查询表中id=3的记录并显示
	private static void selectByid(int id) throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8","root","usbw");
		String sqlselect = "SELECT * FROM student WHERE id = ?";
		PreparedStatement pst = con.prepareStatement(sqlselect);
		pst.setInt(1, id);
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		ResultSet rs = pst.executeQuery();
		if(rs.next()) {
			String name = rs.getString("name");
			String schoolin = sdf.format(rs.getDate("schoolin"));
			float score = rs.getFloat("score");
			System.out.println(id+" "+name+" "+schoolin+" "+String.format("%.2f", score));
		}
		else
			System.out.println("该学生不存在");
		pst.close();
		con.close();
	}
		
}