已知
:建立表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);
基本要求:
将表操作封装成类,将功能封装成类的方法。
功能要求:
(1)向表中增加记录并显示所有记录(数据自己指定);
(2)从表中删除id=1的记录并显示所有记录;
(3)修改表中记录:查询条件id=2,将name修改为:山东理工,修改完毕显示所有记录;
(4)查询表中id=3的记录并显示。
提交说明:粘贴JAVA程序代码。
难度:30
评价标准:
(1)类的层次设计合理,5分;
(2)增加功能实现,5分;
(3)删除功能实现,5分;
(4)修改功能实现,5分;
(5)查询单条记录实现,5分。
(6)查询所有记录实现,5分。
共计30分。
StudentDao:
import java.sql.*;
import java.text.SimpleDateFormat;
public class StudentDao {
Connection con;
PreparedStatement pst;
ResultSet rs;
//连接数据库
public Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3307/school?useUnicode=true&characterEncoding=utf-8","root","usbw");
return con;
}
//关闭数据库
public void closeAll() {
try {
if(rs != null)
rs.close();
if(pst != null)
pst.close();
if(con != null)
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//1、向表中增加记录并显示所有记录(数据自己指定)
public void addStudent(String name, java.sql.Date date, float score) throws ClassNotFoundException, SQLException {
con = getConnection();
String sqladd = "INSERT INTO student VALUES(NULL,?,?,?)";
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 ? "数据插入成功":"数据插入失败");
closeAll();
}
//2、从表中删除id=1的记录并显示所有记录
public void del_By_id(int id) throws ClassNotFoundException, SQLException {
con = getConnection();
String sql = "DELETE FROM student WHERE id = ?";
pst = con.prepareStatement(sql);
pst.setInt(1, id);
int result = pst.executeUpdate();
System.out.println(result > 0 ? "数据删除成功":"数据不存在");
closeAll();
}
//3、修改表中记录:查询条件id=2,将name修改为:山东理工,修改完毕显示所有记录;
public void updatebyid(int id, String name) throws ClassNotFoundException, SQLException {
con = getConnection();
String update = "update student set name=? WHERE id=?";
pst = con.prepareStatement(update);
pst.setString(1, name);
pst.setInt(2, id);
int result = pst.executeUpdate();
System.out.println(result > 0 ? "信息修改成功" : "信息修改失败");
closeAll();
}
//显示全部信息
public void selectAll() throws SQLException, ClassNotFoundException {
con = getConnection();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String sql = "select * from student";
pst = con.prepareStatement(sql);
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));
}
closeAll();
}
//4、查询表中id=3的记录并显示
public void selectByid(int id) throws ClassNotFoundException, SQLException {
con = getConnection();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String sqlselect = "SELECT * FROM student WHERE id = ?";
pst = con.prepareStatement(sqlselect);
pst.setInt(1, id);
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("该学生不存在");
closeAll();
}
}
Test:
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class Test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner input = new Scanner(System.in);
StudentDao sd = new StudentDao();
int id;
float score;
String name;
Date schoolin;
java.sql.Date date;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
//1、向表中增加记录并显示所有记录(数据自己指定)
System.out.println("请输入学生姓名、入学时间、成绩");
name = input.next();
schoolin = sdf.parse(input.next());
date = new java.sql.Date(schoolin.getTime());
score = input.nextFloat();
sd.addStudent(name,date,score);
System.out.println();
//2、从表中删除id=1的记录并显示所有记录
System.out.println("请输入需要删掉的 id ");
id = input.nextInt();
sd.del_By_id(id);
System.out.println();
//3、修改表中记录:查询条件id=2,将name修改为:山东理工,修改完毕显示所有记录;
System.out.println("请输入需要修改学生的id");
id = input.nextInt();
System.out.println("请输入修改后的姓名");
name = input.next();
sd.updatebyid(id, name);
System.out.println();
System.out.println("显示全部信息");
sd.selectAll();
System.out.println();
//4、查询表中id=3的记录并显示
System.out.println("请输入需要查询的id");
id = input.nextInt();
sd.selectByid(id);
System.out.println();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
input.close();
}
}