wordpress中文网站,做网站去哪里可以找高清的图片,怎么上传wordpress,凡科快图网页版实验三 JDBC数据库操作编程#xff08;设计性#xff09;
实验目的 掌握JDBC的数据库编程方法。掌握采用JDBC完成数据库链接、增删改查#xff0c;以及操作封装的综合应用。实验要求 本实验要求每个同学单独完成#xff1b;调试程序要记录调试过程中出现的问题及解决办法…实验三 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, ListCourse courses) //一个学生选了多门课程save(ListStudent 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.jdbc12.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-f39b34dca841at mssql.jdbc12.8.1.jre8/com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:4271)at mssql.jdbc12.8.1.jre8/com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1965)at private static final String URL jdbc:sqlserver://localhost:1433;databaseNameStudentDB_New;encryptfalse;
在引入URL时加上encryptfalse就解决问题了 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;databaseNameStudentDB;encryptfalse; 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 ListStudent findAllStudents() { ListStudent 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. 查询所有学生信息 ListStudent 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. 查询所有学生信息 ListStudent 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;databaseNameStudentDB_New;encryptfalse;usersa;password123456; 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, ListCourse courses) { int totalRows 0; for (Course course : courses) { totalRows save(student, course); // 调用上面的save方法 } return totalRows; } // 保存多个学生选同一门课程 public int save(ListStudent 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;databaseNameStudentDB_New;encryptfalse; 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; }
}
