JAVA链接JDBC,编写简单的学生管理系统1
0.创建springboot项目
https://editor.csdn.net/md/?articleId=129443511
1.插入数据库
CREATE TABLE student (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`age` tinyint DEFAULT '0',
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('1', 'may', '18', '武汉市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('2', 'me', '23', '上海市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('3', '李思', '12', '重庆市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('4', '刘流', '27', '武汉市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('5', '王麻子', '12', '成都市');
INSERT INTO student(`id`, `name`, `age`, `address`) VALUES ('6', 'KKK', '11', '成都');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('7', 'key', '22', '成都市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('8', '999', '33', '上海市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('9', 'wang', '55', '成都市');
INSERT INTO student (`id`, `name`, `age`, `address`) VALUES ('10', 'lisi', '38', '成都市');
分层架构
entity—实体类—-创建实体类与数据库表结构字段一一对应的
dao—-数据库访问层—-和数据库打交道
serivce—业务逻辑层
controller—控制层
2. 创建实体类entity
package JDBC.entity;
public class StudentEntity {
private Long id;
private String name;
private Integer age;
private String address;
public StudentEntity(Long id, String name, Integer age) {
this.id = id;
this.name = name;
this.age = age;
}
public StudentEntity(Long id, String name, Integer age, String address) {
this.id = id;
this.name = name;
this.age = age;
this.address = address;
}
public void setId(Long id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setAge(Integer age) {
this.age = age;
}
public void setAddress(String address) {
this.address = address;
}
public Long getId() {
return id;
}
public String getName() {
return name;
}
public Integer getAge() {
return age;
}
public String getAddress() {
return address;
}
@Override
public String toString() {
return "StudentEntity{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", address='" + address + '\'' +
'}';
}
}
3. 创建dao层
java通过jdbc数据库的具体操作链接
package JDBC.dao;
import JDBC.entity.StudentEntity;
import com.mysql.cj.jdbc.Driver; //1.导入mysql驱动包
import java.sql.*;
import java.util.ArrayList;
public class StudentDao {
/**
* 学生对象数据库访问层
*/
/**
* 查询所有的学生信息
*
* @return
*/
public ArrayList<StudentEntity> allStudent() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//A.java连接mysql数据库查询所有数据
//1.导入mysql驱动jar包;
//2. 注册驱动 javase 反射机制Class.forName()
Class.forName("com.mysql.cj.jdbc.Driver");
//3. 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
//4. 获取执行者对象
statement = connection.createStatement();
//5. 执行sql语句并获取返回结果
resultSet = statement.executeQuery("select * from student");
ArrayList<StudentEntity> studentEntities = new ArrayList<>();
//6. 对结果进行处理
while (resultSet.next()) { // 如果false结束该循环
// 获取该行数据的第一列 id
Long id = resultSet.getLong("id");
// 获取该行数据的第二列 name
String name = resultSet.getString("name");
// 获取该行数据的第三列 age
Integer age = resultSet.getInt("age");
// 获取该行数据的第四列 address
String address = resultSet.getString("address");
// 将db中查询到数据封装成java学生对象
StudentEntity studentEntity = new StudentEntity(id, name, age, address);
// 将该对象存入到集合中
studentEntities.add(studentEntity);
}
return studentEntities;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
// 7. 释放jdbc资源
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 根据学生id 查询学生信息 学生的id
*
* @return
*/
public StudentEntity getByIdStudent(Long stuId) {
/**
* 判断用户是否传递学生id的值
*/
if (stuId == null) {
return null;
}
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//A.java连接mysql数据库查询所有数据
//1.导入mysql驱动jar包;
//2. 注册驱动 javase 反射机制Class.forName()
Class.forName("com.mysql.cj.jdbc.Driver");
//3. 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
//4. 获取执行者对象
statement = connection.createStatement();
//5. 执行sql语句并获取返回结果 自己拼接 查询sql语句
resultSet = statement.executeQuery("select * from student where id=" + stuId);
boolean result = resultSet.next(); // 查询不到数据 false
// 判断如果查询不到数据 则不会取值
if (!result) {
return null;
}
//6. 对结果进行处理
// 获取该行数据的第一列 id
Long id = resultSet.getLong("id");
// 获取该行数据的第二列 name
String name = resultSet.getString("name");
// 获取该行数据的第三列 age
Integer age = resultSet.getInt("age");
// 获取该行数据的第四列 address
String address = resultSet.getString("address");
// 将db中查询到数据封装成java学生对象
StudentEntity studentEntity = new StudentEntity(id, name, age, address);
return studentEntity;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
// 7. 释放jdbc资源
try {
if (resultSet != null)
resultSet.close();
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 插入我们的学生
*
* @param stu
* @return
*/
public int insertStudent(StudentEntity stu) {
Connection connection = null;
Statement statement = null;
try {
//A.java连接mysql数据库查询所有数据
//1.导入mysql驱动jar包;
//2. 注册驱动 javase 反射机制Class.forName()
Class.forName("com.mysql.cj.jdbc.Driver");
//3. 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
//4. 获取执行者对象
statement = connection.createStatement();
//5. 执行sql语句并获取返回结果 executeUpdate执行 insert sql语句
String insertStudentSql = "INSERT INTO student values(null,'" + stu.getName() + "'," + stu.getAge() + ",'" + stu.getAddress() + "')";
System.out.println("insertStudentSql:" + insertStudentSql);
// log输出
int result = statement.executeUpdate(insertStudentSql);
// 执行该sql语句 影响行数
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 7. 释放jdbc资源
try {
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 修改学生的信息
*
* @param stu
* @return
*/
public int updateStudent(StudentEntity stu) {
Connection connection = null;
Statement statement = null;
try {
//A.java连接mysql数据库查询所有数据
//1.导入mysql驱动jar包;
//2. 注册驱动 javase 反射机制Class.forName()
Class.forName("com.mysql.cj.jdbc.Driver");
//3. 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
//4. 获取执行者对象
statement = connection.createStatement();
//5. 执行sql语句并获取返回结果 executeUpdate执行 update sql语句
String updateStudentSql = "update student set name='" + stu.getName() + "' ,age=" + stu.getAge() + "," +
"address='" + stu.getAddress() + "' where id=" + stu.getId() + "";
// log输出
System.out.println("updateStudentSql:" + updateStudentSql);
int result = statement.executeUpdate(updateStudentSql);
// 执行该sql语句 影响行数
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 7. 释放jdbc资源
try {
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 根据主键id删除学生信息
*
* @param id
* @return
*/
public int delStudent(Long id) {
// 判断id是否为null
if (id == null) {
return 0;
}
Connection connection = null;
Statement statement = null;
try {
//A.java连接mysql数据库查询所有数据
//1.导入mysql驱动jar包;
//2. 注册驱动 javase 反射机制Class.forName()
Class.forName("com.mysql.cj.jdbc.Driver");
//3. 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/shs_demo?serverTimezone=UTC", "root", "root");
//4. 获取执行者对象
statement = connection.createStatement();
//5. 执行sql语句并获取返回结果 executeUpdate执行 delete sql语句
String delSQL = "delete from student where id=" + id;
System.out.println("delSql:" + delSQL);
// log输出
int result = statement.executeUpdate(delSQL);
// 执行该sql语句 影响行数
return result;
} catch (Exception e) {
e.printStackTrace();
return 0;
} finally {
// 7. 释放jdbc资源
try {
if (statement != null)
statement.close();
if (connection != null)
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
4. 创建service层
package JDBC.service;
import JDBC.dao.StudentDao;
import JDBC.entity.StudentEntity;
import java.util.ArrayList;
public class StudentService {
private StudentDao studentDao = new StudentDao();
public ArrayList<StudentEntity> allStudent(){
//通过业务逻辑层调用dao层代码
ArrayList<StudentEntity> studentEntities=studentDao.allStudent();
return studentEntities;
}
public StudentEntity getByIdStudent(Long stuId) {
return studentDao.getByIdStudent(stuId);
}
public int insertStudent(StudentEntity stu) {
return studentDao.insertStudent(stu);
}
public int updateStudent(StudentEntity stu) {
return studentDao.updateStudent(stu);
}
public int delStudent(Long id) {
return studentDao.delStudent(id);
}
}