已知:
建立表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();
}
}