实验三 JDBC数据库操作编程（设计性）
实验三 JDBC数据库操作编程（设计性）
- 实验目的
- 掌握JDBC的数据库编程方法。
- 掌握采用JDBC完成数据库链接、增删改查，以及操作封装的综合应用。
- 实验要求
- 本实验要求每个同学单独完成；
- 调试程序要记录调试过程中出现的问题及解决办法，并写入到实验总结和心得；
- 完成实验后直接在题目后面按要求给出本实验的实验报告。
- 实验设备、环境
奔腾以上计算机，装有Myeclipse 6.0以上。
- 实验内容
- 用JDBC完成数据操作层的封装，具体要求如下：
-
- 封装数据库连接
- 把数据库的增删改查封装成方法
- 完成一个基于自己实现方法的数据库带参数查询实例，要求查询参数不得少于3个，同时返回类型必须为一个具体的对象类型
- 查询所有信息，封装返回具体列表对象
- 自己建立数据库
- 图片信息的直接保存和读取。
根据以上要求，设计一个学生管理系统的数据库操作层StudentDao，系统可以有学生表student。
把程序运行界面截图贴在这里，源码贴到附录部分：
-
- 使用4.1完成的StudentDao，完成学生管理系统的增、删、改、查的场景。（可以用System.out.print在MyEclipse的 Console完成）
把程序运行界面截图贴在这里，源码贴到附录部分：
- 设计一个学生选课系统的数据库操作层CourseSelectionDao，系统可以有学生表student、课程表course和成绩表score，可以实现:
- save(Student s, Course c)； //一个学生选了一门课程
- save(Student s, List<Course> courses)； //一个学生选了多门课程
- save(List<Student> students, Course c)；//多个学生都选了同一门课程
把程序运行界面截图贴在这里，源码贴到附录部分：
- 实验总结与心得（请详细叙述你通过该实验掌握的知识点和心得，比如对错误的解决办法等）
在实验过程中遇到了连接不到sqlserve的问题：com.microsoft.sqlserver.jdbc.SQLServerException: 通过端口 1433 连接到主机 localhost 的 TCP/IP 连接失败。错误:“Connection refused: no further information。请验证连接属性。确保 SQL Server 的实例正在主机上运行，且在此端口接受 TCP/IP 连接，还要确保防火墙没有阻止到此端口的 TCP 连接。”。
at mssql.jdbc@12.8.1.jre8/com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:242)
at
打开这个配置管理器
把这个TCP/IP启动就好了
接着又遇到这个问题：com.microsoft.sqlserver.jdbc.SQLServerException: “Encrypt”属性设置为“true”且 “trustServerCertificate”属性设置为“false”，但驱动程序无法使用安全套接字层 (SSL) 加密与 SQL Server 建立安全连接:错误:PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target。 ClientConnectionId:235f4c8c-84dd-41c5-9e44-f39b34dca841
at mssql.jdbc@12.8.1.jre8/com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4271)
at mssql.jdbc@12.8.1.jre8/com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1965)
at
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=StudentDB_New;encrypt=false";
在引入URL时加上encrypt=false就解决问题了
INSERT 语句与 FOREIGN KEY 约束"FK__score__course_id__70DDC3D8"冲突。该冲突发生于数据库"StudentDB"，表"dbo.course", column 'id'
新建了一个数据库与之前的StudentDB数据库分开，重新创建多张表并插入数据，最终解决问题
这个实验做完，通过实际操作，我对 SQL 语言有了更深入的理解，特别是在数据插入和查询方面。学习如何使用 INSERT、SELECT、DELETE 等基本 SQL 语句。在实验中，我意识到数据的完整性和一致性是非常重要的。在插入数据之前，我们需要确保没有重复的主键，这样才能避免违反约束条件。在实验过程中，我遇到了一些错误，比如主键冲突和 SQL 语法错误。通过仔细阅读错误信息和调试代码，翻阅网上资料，我学会了如何快速定位问题并进行修复。
- 源码附录
4.1
封装数据库连接代码：
package com.studentmanagement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=StudentDB;encrypt=false";
private static final String USER = "sa"; // 替换为你的数据库用户名
private static final String PASSWORD = "123456"; // 替换为你的数据库密码
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据库的增删改查封装成StudentDao方法
package com.studentmanagement;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentDao {
public int addStudent(Student student) {
String sql = "INSERT INTO student (id, name, age, gender, photo) VALUES (?, ?, ?, ?, ?)";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, student.getId());
ps.setString(2, student.getName());
ps.setInt(3, student.getAge());
ps.setString(4, student.getGender());
ps.setBytes(5, student.getPhoto());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int deleteStudent(int id) {
String sql = "DELETE FROM student WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int updateStudent(Student student) {
String sql = "UPDATE student SET name = ?, age = ?, gender = ?, photo = ? WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, student.getName());
ps.setInt(2, student.getAge());
ps.setString(3, student.getGender());
ps.setBytes(4, student.getPhoto());
ps.setInt(5, student.getId());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public Student findStudentByIdAndNameAndAge(int id, String name, int age) {
String sql = "SELECT * FROM student WHERE id = ? AND name = ? AND age = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.setString(2, name);
ps.setInt(3, age);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return new Student(rs.getInt("id"), rs.getString("name"),
rs.getInt("age"), rs.getString("gender"), rs.getBytes("photo"));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public List<Student> findAllStudents() {
List<Student> students = new ArrayList<>();
String sql = "SELECT * FROM student";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
students.add(new Student(rs.getInt("id"), rs.getString("name"),
rs.getInt("age"), rs.getString("gender"), rs.getBytes("photo")));
}
} catch (SQLException e) {
e.printStackTrace();
}
return students;
}
public int saveStudentPhoto(int id, byte[] photo) {
String sql = "UPDATE student SET photo = ? WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setBytes(1, photo);
ps.setInt(2, id);
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public byte[] getStudentPhoto(int id) {
String sql = "SELECT photo FROM student WHERE id = ?";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getBytes("photo");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
一个具体的student学生类，可以创建具体对象，并且有对应的图片信息
package com.studentmanagement;
public class Student {
private int id;
private String name;
private int age;
private String gender;
private byte[] photo;
public Student(int id, String name, int age, String gender, byte[] photo) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.photo = photo;
}
// Getter 和 Setter 方法
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public byte[] getPhoto() {
return photo;
}
public void setPhoto(byte[] photo) {
this.photo = photo;
}
}
图片信息的保存和读取：
package com.studentmanagement;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;
import java.io.IOException;
import java.nio.file.Path;
public class Main {
public static void main(String[] args) {
StudentDao studentDao = new StudentDao();
// 1. 添加学生
byte[] photo1 = null; // 假设这是学生1的图片
try {
photo1 = Files.readAllBytes(Paths.get("src/6458.jpg_wh860.jpg")); // 读取 src 目录下的图片文件
} catch (Exception e) {
e.printStackTrace();
}
Student student1 = new Student(1, "Alice", 20, "Female", photo1);
studentDao.addStudent(student1);
// 2. 保存图片
int savePhotoResult = studentDao.saveStudentPhoto(1, photo1);
System.out.println("保存学生 Alice 的图片，结果：" + (savePhotoResult > 0 ? "成功" : "失败"));
// 3. 读取图片
byte[] retrievedPhoto = studentDao.getStudentPhoto(1);
System.out.println("读取学生 Alice 的图片，结果：" + (retrievedPhoto != null ? "成功" : "失败"));
// 确保目标目录存在
Path outputPath = Paths.get("src/retrieved_photo.jpg");
try {
// 如果父目录不存在，创建它
if (!Files.exists(outputPath.getParent())) {
Files.createDirectories(outputPath.getParent());
}
Files.write(outputPath, retrievedPhoto); // 保存到 src 目录
System.out.println("已将读取的图片保存为 retrieved_photo.jpg");
} catch (IOException e) {
e.printStackTrace();
}
// 4. 查询所有学生信息
List<Student> allStudents = studentDao.findAllStudents();
System.out.println("当前所有学生：");
for (Student student : allStudents) {
System.out.println("学生 ID: " + student.getId() + ", 姓名: " + student.getName());
}
}
}
对于学生的增删查改：
package com.studentmanagement;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;
public class Main {
public static void main(String[] args) {
StudentDao studentDao = new StudentDao();
// 1. 添加学生
byte[] photo1 = null; // 假设这是学生1的图片
try {
photo1 = Files.readAllBytes(Paths.get("src/6458.jpg_wh860.jpg")); // 读取图片文件
} catch (Exception e) {
e.printStackTrace();
}
Student student1 = new Student(1, "Alice", 20, "Female", photo1);
studentDao.addStudent(student1);
// 2. 保存图片
int savePhotoResult = studentDao.saveStudentPhoto(1, photo1);
System.out.println("保存学生 Alice 的图片，结果：" + (savePhotoResult > 0 ? "成功" : "失败"));
// 3. 读取图片
byte[] retrievedPhoto = studentDao.getStudentPhoto(1);
System.out.println("读取学生 Alice 的图片，结果：" + (retrievedPhoto != null ? "成功" : "失败"));
// 可以将 retrievedPhoto 写入文件进行查看
try {
Files.write(Paths.get("path/to/retrieved_photo.jpg"), retrievedPhoto); // 保存到文件
System.out.println("已将读取的图片保存为 retrieved_photo.jpg");
} catch (Exception e) {
e.printStackTrace();
}
// 4. 查询所有学生信息
List<Student> allStudents = studentDao.findAllStudents();
System.out.println("当前所有学生：");
for (Student student : allStudents) {
System.out.println("学生 ID: " + student.getId() + ", 姓名: " + student.getName());
}
}
}
4.3CourseSelectionDao的实现
Main测试函数
package com.studentmanagement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
String url = "jdbc:sqlserver://localhost:1433;databaseName=StudentDB_New;encrypt=false;user=sa;password=123456";
try (Connection conn = DriverManager.getConnection(url)) {
// 清理现有数据
String deleteScoreSQL = "DELETE FROM score";
String deleteCourseSQL = "DELETE FROM course";
String deleteStudentSQL = "DELETE FROM student";
try (PreparedStatement pstmt = conn.prepareStatement(deleteScoreSQL)) {
pstmt.executeUpdate();
}
try (PreparedStatement pstmt = conn.prepareStatement(deleteCourseSQL)) {
pstmt.executeUpdate();
}
try (PreparedStatement pstmt = conn.prepareStatement(deleteStudentSQL)) {
pstmt.executeUpdate();
}
// 插入学生数据
String insertStudentSQL = "INSERT INTO student (id, name, age, gender) VALUES (?, ?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertStudentSQL)) {
pstmt.setInt(1, 1);
pstmt.setString(2, "Alice");
pstmt.setInt(3, 20);
pstmt.setString(4, "Female");
pstmt.executeUpdate();
pstmt.setInt(1, 2);
pstmt.setString(2, "Bob");
pstmt.setInt(3, 22);
pstmt.setString(4, "Male");
pstmt.executeUpdate();
}
// 插入课程数据
String insertCourseSQL = "INSERT INTO course (id, name, credits) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertCourseSQL)) {
pstmt.setInt(1, 101);
pstmt.setString(2, "Mathematics");
pstmt.setInt(3, 3);
pstmt.executeUpdate();
pstmt.setInt(1, 102);
pstmt.setString(2, "Physics");
pstmt.setInt(3, 4);
pstmt.executeUpdate();
}
// 插入成绩数据
String insertScoreSQL = "INSERT INTO score (student_id, course_id) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(insertScoreSQL)) {
pstmt.setInt(1, 1); // Alice
pstmt.setInt(2, 101); // Mathematics
pstmt.executeUpdate();
pstmt.setInt(1, 2); // Bob
pstmt.setInt(2, 102); // Physics
pstmt.executeUpdate();
}
System.out.println("数据插入成功！");
// 查询并展示学生数据
System.out.println("学生数据：");
String selectStudentSQL = "SELECT * FROM student";
try (PreparedStatement pstmt = conn.prepareStatement(selectStudentSQL);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String gender = rs.getString("gender");
System.out.printf("ID: %d, Name: %s, Age: %d, Gender: %s%n", id, name, age, gender);
}
}
// 查询并展示课程数据
System.out.println("课程数据：");
String selectCourseSQL = "SELECT * FROM course";
try (PreparedStatement pstmt = conn.prepareStatement(selectCourseSQL);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int credits = rs.getInt("credits");
System.out.printf("ID: %d, Name: %s, Credits: %d%n", id, name, credits);
}
}
// 查询并展示成绩数据
System.out.println("成绩数据：");
String selectScoreSQL = "SELECT * FROM score";
try (PreparedStatement pstmt = conn.prepareStatement(selectScoreSQL);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int studentId = rs.getInt("student_id");
int courseId = rs.getInt("course_id");
System.out.printf("Student ID: %d, Course ID: %d%n", studentId, courseId);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
定义一个course类
package com.studentmanagement;
public class Course {
private int id;
private String name;
private int credits;
public Course(int id, String name, int credits) {
this.id = id;
this.name = name;
this.credits = credits;
}
// Getter 和 Setter 方法
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 int getCredits() {
return credits;
}
public void setCredits(int credits) {
this.credits = credits;
}
}
核心CourseSelectionDao.java
package com.studentmanagement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class CourseSelectionDao {
// 保存单个学生选一门课程
public int save(Student student, Course course) {
String sql = "INSERT INTO score (student_id, course_id) VALUES (?, ?)";
try (Connection conn = DBUtil.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, student.getId());
ps.setInt(2, course.getId());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
// 保存单个学生选多门课程
public int save(Student student, List<Course> courses) {
int totalRows = 0;
for (Course course : courses) {
totalRows += save(student, course); // 调用上面的save方法
}
return totalRows;
}
// 保存多个学生选同一门课程
public int save(List<Student> students, Course course) {
int totalRows = 0;
for (Student student : students) {
totalRows += save(student, course); // 调用上面的save方法
}
return totalRows;
}
}
数据库连接代码封装在类里面
package com.studentmanagement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=StudentDB_New;encrypt=false";
private static final String USER = "sa"; // 替换为你的数据库用户名
private static final String PASSWORD = "123456"; // 替换为你的数据库密码
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
学生student类的创建：
package com.studentmanagement;
public class Student {
private int id;
private String name;
private int age;
private String gender;
private byte[] photo;
public Student(int id, String name, int age, String gender, byte[] photo) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.photo = photo;
}
// Getter 和 Setter 方法
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public byte[] getPhoto() {
return photo;
}
public void setPhoto(byte[] photo) {
this.photo = photo;
}
}
原文地址：https://blog.csdn.net/hxj_686/article/details/143652858
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！