一、数据准备
- 进行准备工作,创建一个新数据库,建立一张student表,包含sid、name、age、birthaday四列,然后向表中插入几条数据。
-- 创建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');
此时表如下:
- 导入JDBC的jar包,另外,还使用到了jutil这个jar包,用于对工程中的某个方法执行进行测试。使用前需要先下载两个jar包,下载地址:官方网站 ,然后将jar包导入工程即可。使用时只需要在方法名上使用注解@Test即可。
二、创建项目基础架构
1.创建三层架构
目前使用较多的还是经典的三层架构模式,分别是控制层(controller)、业务层(service)、数据访问层(dao)。
- 项目中新建controller、service、dao三个目录,分别存放控制层、业务层、数据访问层的代码
- 新建domain目录存放实体类
1.创建Student类
- 首先在domain目录下创建Student类,其中定义了sid、name、age、birthday四个变量,并实现四个变量的set和get方法。
- 然后实现构造函数,四个变量作为形参。
- 最后重写toString()方法,方便打印数据查看。重写toString方法后,将Student实体传入System.out.println()方法即可自动调用此方法输出信息。
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 String getName() {
return name;
}
public Integer getAge() {
return age;
}
public Date getBirthday() {
return birthday;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public void setName(String name) {
this.name = name;
}
public void setAge(Integer age) {
this.age = age;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", name='" + name + '\'' +
", age=" + age +
", birthday=" + birthday +
'}';
}
}
2.创建dao层的接口和实现类
- 1.dao层接口
public interface StudentDao {
//查询所有的学生信息
public abstract ArrayList<Student> findAll();
//根据id获取学生信息
public abstract Student findById(Integer id);
//新增学生信息
public abstract int insert(Student stu);
//修改学生信息
public abstract int update(Student stu);
//删除学生信息
public abstract int delete(Integer id);
}
- dao实现类:
public class StudentDaoImpl implements StudentDao{
@Override
public ArrayList<Student> findAll() {
ArrayList<Student> list = new ArrayList<>();
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 1.注册驱动
//2.获取数据库连接
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/db14", "root", "12345678");
//3.获取执行者对象
statement = connection.createStatement();
//4.执行sql语句,并且接受返回的结果集
String querySql = "SELECT * FROM student";
resultSet = statement.executeQuery(querySql);
//5.处理结果集
while (resultSet.next()){
Integer sid = resultSet.getInt("sid");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
Date birthday = resultSet.getDate("birthday");
//封装Student对象
Student student = new Student(sid, name, age, birthday);
//将Student对象保存到list中
list.add(student);
}
}catch (Exception e){
e.printStackTrace();
}finally {
//6.释放资源
if(connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return list;
}
@Override
public Student findById(Integer id) {
Student student = new Student();
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 1.注册驱动
//2.获取数据库连接
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/db14", "root", "12345678");
//3.获取执行者对象
statement = connection.createStatement();
//4.执行sql语句,并且接受返回的结果集
String querySql = "SELECT * FROM student WHERE sid=" + id;
resultSet = statement.executeQuery(querySql);
//5.处理结果集
while (resultSet.next()){
Integer sid = resultSet.getInt("sid");
String name = resultSet.getString("name");
Integer age = resultSet.getInt("age");
Date birthday = resultSet.getDate("birthday");
//封装Student对象
student.setSid(sid);
student.setName(name);
student.setAge(age);
student.setBirthday(birthday);
}
}catch (Exception e){
e.printStackTrace();
}finally {
//6.释放资源
if(connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return student;
}
@Override
public int insert(Student stu) {
Connection connection = null;
Statement statement = null;
int res = 0; //影响行数
try {
// 1.注册驱动
//2.获取数据库连接
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/db14", "root", "12345678");
//3.获取执行者对象
statement = connection.createStatement();
//4.执行sql语句,并且接受返回的结果集
// "INSERT INTO student VALUES (" + NULL + ",'" + 小甲 +"'," + 23 + ",'" + 1999-09-23 + "')";
Date date = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(date);
String InsertSql =
"INSERT INTO student VALUES (" + stu.getSid() + ",'" + stu.getName() +
"'," + stu.getAge() + ",'" + birthday + "')";
res = statement.executeUpdate(InsertSql);
}catch (Exception e){
e.printStackTrace();
}finally {
//6.释放资源
if(connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return res;
}
@Override
public int update(Student stu) {
Connection connection = null;
Statement statement = null;
int res = 0; //影响行数
try {
// 1.注册驱动
//2.获取数据库连接
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/db14", "root", "12345678");
//3.获取执行者对象
statement = connection.createStatement();
//4.执行sql语句,并且接受返回的结果集
Date date = stu.getBirthday();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String birthday = sdf.format(date);
String updateSql =
"UPDATE student SET sid='"+ stu.getSid() +"',name='"+stu.getName()+"',age='"+stu.getAge()+"',birthday='"+ birthday + "' WHERE sid='"+ stu.getSid() +"'";
res = statement.executeUpdate(updateSql);
}catch (Exception e){
e.printStackTrace();
}finally {
//6.释放资源
if(connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return res;
}
@Override
public int delete(Integer id) {
Connection connection = null;
Statement statement = null;
int res = 0; //影响行数
try {
// 1.注册驱动
//2.获取数据库连接
connection = DriverManager.getConnection
("jdbc:mysql://localhost:3306/db14", "root", "12345678");
//3.获取执行者对象
statement = connection.createStatement();
//4.执行sql语句,并且接受返回的结果集
String updateSql ="DELETE FROM student WHERE sid='"+ id +"'";
res = statement.executeUpdate(updateSql);
}catch (Exception e){
e.printStackTrace();
}finally {
//6.释放资源
if(connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return res;
}
}
3.创建service层的接口和实现类
- 创建service层的接口
public interface StudentService {
//查询所有的学生信息
public abstract ArrayList<Student> findAll();
//根据id获取学生信息
public abstract Student findById(Integer id);
//新增学生信息
public abstract int insert(Student stu);
//修改学生信息
public abstract int update(Student stu);
//删除学生信息
public abstract int delete(Integer id);
}
- 创建service层的实现类
public class StudentServiceImpl implements StudentService{
private StudentDao dao = new StudentDaoImpl();
@Override
public ArrayList<Student> findAll() {
return dao.findAll();
}
@Override
public Student findById(Integer id) {
return dao.findById(id);
}
@Override
public int insert(Student stu) {
return dao.insert(stu);
}
@Override
public int update(Student stu) {
return dao.update(stu);
}
@Override
public int delete(Integer id) {
return dao.delete(id);
}
}
4.创建controller层
public class StudentController {
private StudentService service = new StudentServiceImpl();
@Test
public void findAll() {
ArrayList<Student> list = service.findAll();
for (Student stu : list){
System.out.println(stu);
}
}
@Test
public void findById() {
Student student = service.findById(3);
System.out.println(student);
}
@Test
public void insert() {
Student student = new Student(5, "大王", 33, new Date());
int res = service.insert(student);
if (res != 0){
System.out.println("插入成功!");
}else {
System.out.println("插入失败!");
}
}
@Test
public void update() {
Student student = service.findById(5);
student.setName("王王");
int res = service.update(student);
if (res != 0){
System.out.println("修改成功!");
}else {
System.out.println("修改失败!");
}
}
@Test
public void delete() {
int res = service.delete(5);
if (res != 0){
System.out.println("删除成功!");
}else {
System.out.println("删除失败!");
}
}
}
项目架构如下: