1. 数据插入功能。能增加班级;给某班增加学生
2. 数据查询功能。列出所有班级;选择某个班级能查看班级下所有的学生
3. 数据删除功能。能删除某个学生;能删除班级,删除班级的同时需要删除班级下的所有学生。
4. 数据修改功能。能修改学生姓名;能修改班级名称
看到这个题目以后,不要认为有多难,根据题目所要求的一步一步的来,
第一步,先在数据库中创建两张表,并添加数据
SQL:
create table student(
sno number(10) primary key,
sname varchar(20) not null,
sex char(8),
brithday date,
phone number(20),
clazz_id varchar(20) references clazz(cno)
)
insert into student values(1001,'张三','男',to_date('1996-06-06','yyyy-mm-dd'),1234567989,'jsj01');
insert into student values(1002,'王二','女',to_date('1997-12-03','yyyy-mm-dd'),9874563211,'wl02');
insert into student values(1003,'刘六','男',to_date('1997-06-18','yyyy-mm-dd'),4563217899,'dx03');
select * from student
create table clazz(
cno varchar(20) primary key,
cname varchar(20) unique,
teacher varchar(20) not null
)
insert into clazz values('jsj01','计算机专业','欧老师');
insert into clazz values('wl02','网络工程专业','刘老师');
insert into clazz values('dx03','电信专业','罗老师');
select * from clazz
第二 步给两张表分别创建类:
Student:
package com.student.po;
import java.util.Date;
/**
* sno number(10) primary key,
sname varchar(20) not null,
sex char(8),
brithday date,
phone number(20),
majorno varchar(20) references major(mno)
* @author Administrator
*
*/
public class Student {
private int id;
private String name;
private String sex;
private Date brithday;
private long phone;
private String clzzId;
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", brithday=" + brithday + ", phone=" + phone
+ ", clzzId=" + clzzId + "]";
}
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 String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBrithday() {
return brithday;
}
public void setBrithday(Date brithday) {
this.brithday = brithday;
}
public long getPhone() {
return phone;
}
public void setPhone(long phone) {
this.phone = phone;
}
public String getClzzId() {
return clzzId;
}
public void setClzzId(String clzzId) {
this.clzzId = clzzId;
}
}
Clazz:
package com.student.po;
/**
* cno varchar(20) primary key,
cname varchar(20) unique,
teacher varchar(20) not null
* @author Administrator
*
*/
public class Clazz {
private String cno;
private String cname;
private String teacher;
@Override
public String toString() {
return "Clazz [cno=" + cno + ", cname=" + cname + ", teacher=" + teacher + "]";
}
public String getCno() {
return cno;
}
public void setCno(String cno) {
this.cno = cno;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getTeacher() {
return teacher;
}
public void setTeacher(String teacher) {
this.teacher = teacher;
}
}
第三步:写出数据库的增删改查
StudentDAO:
package com.student.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.book.util.DBUtil;
import com.student.po.Student;
public class StudentDAO {
Connection conn;
public StudentDAO() {
conn = DBUtil.getConnection();
}
public StudentDAO(Connection conn) {
this.conn = conn;
}
/**
* 通过班级ID查询学生信息
* @param bid
* @return
*/
public Student getStudent(String clazzId) {
String sql = "select * from student where clazz_id=?";
PreparedStatement ps;
Student student = null;
try {
ps = conn.prepareStatement(sql);
ps.setString(1, clazzId);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
student = new Student();
student.setId(rs.getInt("sno"));
student.setName(rs.getString("sname"));
student.setSex(rs.getString("sex"));
student.setBrithday(rs.getDate("brithday"));
student.setPhone(rs.getInt("phone"));
student.setClzzId(rs.getString("clazz_id"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return student;
}
/**
* 查询出所有的学生
* @return
*/
public List<Student> getAllStudent() {
String sql = "select * from student";
PreparedStatement ps;
List<Student> list = new ArrayList<Student>();
Student student = null;
try {
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
student = new Student();
student.setId(rs.getInt("sno"));
student.setName(rs.getString("sname"));
student.setSex(rs.getString("sex"));
student.setBrithday(rs.getDate("brithday"));
student.setPhone(rs.getLong("phone"));
student.setClzzId(rs.getString("clazz_id"));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//新增
public boolean createStudent(Student student) {
String sql = "insert into student values (?,?,?,?,?,?)";
PreparedStatement ps = null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setInt(1, student.getId());
ps.setString(2, student.getName());
ps.setString(3, student.getSex());
ps.setDate(4, (Date) student.getBrithday());
ps.setLong(5, student.getPhone());
ps.setString(6, student.getClzzId());
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
//更新
public boolean updateStudent(Student student) {
String sql = "update student set sname =? where sno=? ";
PreparedStatement ps=null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setString(1, student.getName());
ps.setInt(2, student.getId());
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
//删除
public boolean deleteStudent(int studentId) {
String sql = "delete student where sno=? ";
PreparedStatement ps = null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setInt(1, studentId);
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
//删除 由班级ID删掉
public boolean deleteStudent(String clazzId) {
String sql = "delete student where clazz_id=? ";
PreparedStatement ps = null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setString(1, clazzId);
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
}
ClazzDAO:
package com.student.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.book.util.DBUtil;
import com.student.po.Clazz;
public class ClazzDAO {
Connection conn;
public ClazzDAO() {
conn = DBUtil.getConnection();
}
public ClazzDAO(Connection conn) {
this.conn = conn;
}
/**
* 通过ID查询班级信息
* @param bid
* @return
*/
public Clazz getClazz(String clazzId) {
String sql = "select * from clazz where cno=?";
PreparedStatement ps;
Clazz clazz = null;
try {
ps = conn.prepareStatement(sql);
ps.setString(1, clazzId);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
clazz = new Clazz();
clazz.setCno(rs.getString("cno"));
clazz.setCname(rs.getString("cname"));
clazz.setTeacher(rs.getString("teacher"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return clazz;
}
/**
* 查询出所有的班级
* @return
*/
public List<Clazz> getAllClazz() {
String sql = "select * from clazz";
PreparedStatement ps;
List<Clazz> list = new ArrayList<Clazz>();
Clazz clazz = null;
try {
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
clazz = new Clazz();
clazz.setCno(rs.getString("cno"));
clazz.setCname(rs.getString("cname"));
clazz.setTeacher(rs.getString("teacher"));
list.add(clazz);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//新增
public boolean createClazz(Clazz clazz) {
String sql = "insert into clazz values (?,?,?)";
PreparedStatement ps = null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setString(1, clazz.getCno());
ps.setString(2, clazz.getCname());
ps.setString(3, clazz.getTeacher());
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
//更新
public boolean updateClazz(Clazz clazz) {
PreparedStatement ps = null;
String sql = "update clazz set cname =? where cno=? ";
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setString(1, clazz.getCname());
ps.setString(2, clazz.getCno());
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
//删除
public boolean deleteClazz(String clazzId) {
String sql = "delete clazz where cno=? ";
PreparedStatement ps = null;
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql);
ps.setString(1, clazzId);
ps.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println("出现错误,数据回滚");
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
DBUtil.close(ps);
}
return false;
}
}
最后,写出测试类!
1.数据插入功能。能增加班级;给某班增加学生
package com.student;
import com.student.dao.ClazzDAO;
import com.student.dao.StudentDAO;
import com.student.po.Clazz;
import com.student.po.Student;
public class Test01 {
public static void main(String[] args) {
// 1. 数据插入功能。能增加班级;给某班增加学生
System.out.println("********增加学生信息**********");
StudentDAO studentdao = new StudentDAO();
Student student = new Student();
student.setId(1004);
student.setName("大黄");
student.setSex("男");
java.util.Date utilDate = new java.util.Date(); //获取当前时间
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
student.setBrithday(sqlDate);
student.setPhone(123456789);
student.setClzzId("jsj01");
System.out.println(studentdao.createStudent(student));
System.out.println("********增加班级信息**********");
ClazzDAO clazzdao = new ClazzDAO();
Clazz clazz = new Clazz();
clazz.setCno("tx04");
clazz.setCname("通信专业");
clazz.setTeacher("陈老师");
System.out.println(clazzdao.createClazz(clazz));
}
}
2.数据查询功能。列出所有班级;选择某个班级能查看班级下所有的学生
package com.student;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.student.dao.ClazzDAO;
import com.student.dao.StudentDAO;
import com.student.po.Clazz;
import com.student.po.Student;
/**
* 2. 数据查询功能。列出所有班级;
* 选择某个班级能查看班级下所有的学生
* @author Administrator
*
*/
public class Test02 {
public static void main(String[] args) {
System.out.println("***********列出班级信息***********");
ClazzDAO clazzdao = new ClazzDAO();
List<Clazz> list = clazzdao.getAllClazz();
for (Clazz clazz : list) {
System.out.println(clazz);
}
System.out.println("********查看某个班级学生***********");
Clazz clazz = clazzdao.getClazz("jsj01");
StudentDAO studentdao = new StudentDAO();
List<Student> list2 = studentdao.getAllStudent();
for (Student stu : list2) {
if(stu.getClzzId().equals(clazz.getCno())) {
Student student = studentdao.getStudent(clazz.getCno());
System.out.println(student);
}
}
}
}
3.数据删除功能。能删除某个学生;能删除班级,删除班级的同时需要删除班级下的所有
学生。
package com.student;
import java.util.List;
import com.student.dao.ClazzDAO;
import com.student.dao.StudentDAO;
import com.student.po.Clazz;
import com.student.po.Student;
/**
* 数据删除功能。能删除某个学生;
* 能删除班级,删除班级的同时需要删除班级下的所有学生。
* @author Administrator
*
*/
public class Test03 {
public static void main(String[] args) {
System.out.println("**********删除学生信息**********");
StudentDAO studentdao = new StudentDAO();
System.out.println(studentdao.deleteStudent(1004));
System.out.println("**********删除班级信息***********");
ClazzDAO clazzdao = new ClazzDAO();
Clazz clazz = clazzdao.getClazz("jsj01");
List<Student> list2 = studentdao.getAllStudent();
for (Student stu : list2) {
if(stu.getClzzId().equals(clazz.getCno())) {
boolean student = studentdao.deleteStudent(clazz.getCno());
if(student==true) {
System.out.println(clazzdao.deleteClazz(clazz.getCno()));
}
}else{
System.out.println(clazzdao.deleteClazz(clazz.getCno()));
}
}}}4. 数据修改功能。能修改学生姓名;能修改班级名称package com.student;import com.student.dao.ClazzDAO;import com.student.dao.StudentDAO;import com.student.po.Clazz;import com.student.po.Student;/** * 4. 数据修改功能。能修改学生姓名;能修改班级名称 * @author Administrator * */public class Test04 {public static void main(String[] args) {System.out.println("*************修改学生姓名***************");StudentDAO studentdao = new StudentDAO();Student student = new Student();student.setId(1005);student.setName("周测JDBC");System.out.println(studentdao.updateStudent(student));System.out.println("*****************修改班级名称*************");ClazzDAO clazzdao = new ClazzDAO();Clazz clazz = new Clazz();clazz.setCno("wl02");clazz.setCname("网络专业");System.out.println(clazzdao.updateClazz(clazz));}}
为了省略步骤,我将连接数据库的步骤给封装成了一个DBUtil类
package com.entor.util;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class DBUtil {
public static Connection getConnection() {
Connection conn = null;
try {
Properties prop = new Properties();
prop.load(new FileInputStream("config/db.properties"));
Class.forName(prop.getProperty("driver"));
conn = DriverManager.getConnection(prop.getProperty("url"),
prop.getProperty("username"),
prop.getProperty("password"));
}catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* @param rs
*/
public static void close(ResultSet rs) {
try {
Statement stmt = rs.getStatement();
Connection conn = stmt.getConnection();
rs.close();
stmt.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* 通过Statement关闭数据库连�??
* @param stmt
*/
public static void close(Statement stmt) {
try {
Connection conn = stmt.getConnection();
stmt.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
以及连接数据库的地址,driver................
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
username=scott
password=tiger