07-jdbc案例-需求介绍和数据准备
08-jdbc案例-需求一查询所有学生信息
09-jdbc案例-需求二根据id查询学生信息
10-jdbc案例-需求三添加学生信息
11-jdbc案例-需求四修改学生信息
12-jdbc案例-需求五删除学生信息
1.数据准备
-- 创建db14数据库
CREATE DATABASE db14;
-- 使用db14数据库
USE db14;
-- 创建student表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT, -- 学生id
NAME VARCHAR(20), -- 学生姓名
age INT, -- 学生年龄
birthday DATE -- 学生生日
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'1999-09-23'),(NULL,'李四',24,'1998-08-10'),(NULL,'王五',25,'1996-06-06'),(NULL,'赵六',26,'1994-10-20');
- 实体类
- Student类,成员变量对应表中的列
- 注意:所有的基本数据类型需要使用包装类,以防null值无法赋值
package com.itheima02.domain;
import java.util.Date;
public class Student {
private Integer sid;
private String name;
private Integer age;
private Date birthday;
public Student() {
}
public Student(Integer sid, String name, Integer age, Date birthday) {
this.sid = sid;
this.name = name;
this.age = age;
this.birthday = birthday;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
}
2.需求一:查询全部
@Override
public ArrayList<Student> findAll() {
ArrayList<Student> list = new ArrayList<>();
Connection con = null;
Statement stat = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14", "root", "itheima");
stat = con.createStatement();
String sql = "SELECT * FROM student";
rs = stat.executeQuery(sql);
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
Student stu = new Student(sid,name,age,birthday);
list.add(stu);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
@Override
public ArrayList<Student> findAll() {
return dao.findAll();
}
@Test
public void findAll() {
ArrayList<Student> list = service.findAll();
for(Student stu : list) {
System.out.println(stu);
}
}
3.需求二:条件查询
@Override
public Student findById(Integer id) {
Student stu = new Student();
Connection con = null;
Statement stat = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14", "root", "itheima");
stat = con.createStatement();
String sql = "SELECT * FROM student WHERE sid='"+id+"'";
rs = stat.executeQuery(sql);
while(rs.next()) {
Integer sid = rs.getInt("sid");
String name = rs.getString("name");
Integer age = rs.getInt("age");
Date birthday = rs.getDate("birthday");
stu.setSid(sid);
stu.setName(name);
stu.setAge(age);
stu.setBirthday(birthday);
}
} catch(Exception e) {
e.printStackTrace();
} finally {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return stu;
}
@Override
public Student findById(Integer id) {
return dao.findById(id);
}
@Test
public void findById() {
Student stu = service.findById(3);
System.out.println(stu);
}
4.需求三:新增数据
@Override
public int insert(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14", "root", "itheima");
stat = con.createStatement();
Date d = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(d);
String sql = "INSERT INTO student VALUES ('"+stu.getSid()+"','"+stu.getName()+"','"+stu.getAge()+"','"+birthday+"')";
result = stat.executeUpdate(sql);
} catch(Exception e) {
e.printStackTrace();
} finally {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
public int insert(Student stu) {
return dao.insert(stu);
}
@Test
public void insert() {
Student stu = new Student(5,"周七",27,new Date());
int result = service.insert(stu);
if(result != 0) {
System.out.println("新增成功");
}else {
System.out.println("新增失败");
}
}
5.需求四:修改数据
@Override
public int update(Student stu) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14", "root", "itheima");
stat = con.createStatement();
Date d = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(d);
String sql = "UPDATE student SET sid='"+stu.getSid()+"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+birthday+"' WHERE sid='"+stu.getSid()+"'";
result = stat.executeUpdate(sql);
} catch(Exception e) {
e.printStackTrace();
} finally {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
public int update(Student stu) {
return dao.update(stu);
}
@Test
public void update() {
Student stu = service.findById(5);
stu.setName("周七七");
int result = service.update(stu);
if(result != 0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
}
6.需求五:删除数据
@Override
public int delete(Integer id) {
Connection con = null;
Statement stat = null;
int result = 0;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://192.168.59.129:3306/db14", "root", "itheima");
stat = con.createStatement();
String sql = "DELETE FROM student WHERE sid='"+id+"'";
result = stat.executeUpdate(sql);
} catch(Exception e) {
e.printStackTrace();
} finally {
if(con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stat != null) {
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
public int delete(Integer id) {
return dao.delete(id);
}
@Test
public void delete() {
int result = service.delete(5);
if(result != 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}