数据库实验六-通过嵌入式SQL访问数据库

最新推荐文章于 2023-05-16 23:32:50 发布

我是蒸饺吖～

最新推荐文章于 2023-05-16 23:32:50 发布

阅读量1.2k

点赞数 3

分类专栏：数据库系统文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/qq_45755193/article/details/124767035

版权

数据库系统专栏收录该内容

7 篇文章 4 订阅

订阅专栏

通过嵌入式SQL访问数据库

实验环境：Windows 10操作系统、MySQL 8.0、eclipse2021-09

实验内容与完成情况：

实验目的

熟悉通过嵌入式SQL（主语言为Java语言）编程访问数据库。

实验平台和实验工具

在MySQL数据库管理系统上，通过Java语言编写访问数据库的应用程序来对数据库进行各种数据操作。

实验内容和要求

熟悉RDBMS的预编译程序，通过嵌入式SQL编程访问数据库的基本步骤。

对学生课程数据库中的表，使用Java编程完成如下任务：

(1)查询某一门课程的信息。要查询的课程由用户在程序运行过程中指定，放在主函数中。

(2)查询选修某一门课程的选课信息，要查询的课程号由用户在程序运行过程中指定，放在主函数中，然后根据用户的要求修改其中某些记录的成绩字段。

(3)完成操作后使用java语句显示数据库中表。

实验步骤

在eclipse中新建一个java项目

2.完成Java编程连接数据库操作

在test6项目下新建一个JDBC包，用于数据库连接

【数据库连接】

package JDBC;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.io.*;

//连接MySQL数据库

public class JDBCcon {

public Connection connection = null;

public String JDBC_DRIVER = "";

public String URL = "";

public String USER = "";

public String PWD = "";

//读取配置文件

public void conf() throws IOException {

try (BufferedReader br = new BufferedReader(new FileReader( "conf.txt"))) {

String s = br.readLine();

JDBC_DRIVER=s.substring(s.indexOf('"')+1, s.lastIndexOf('"'));

s = br.readLine();

URL=s.substring(s.indexOf('"')+1, s.lastIndexOf('"'));

s = br.readLine();

USER=s.substring(s.indexOf('"')+1, s.lastIndexOf('"'));

s = br.readLine();

PWD=s.substring(s.indexOf('"')+1, s.lastIndexOf('"'));

}

}

//连接数据库

public void insert() {

//导入MySQL驱动

//1.加载驱动

try {

Class.forName(JDBC_DRIVER);

//2.建立连接

connection = DriverManager.getConnection(URL, USER, PWD);

System.out.println("数据库连接成功！");

}catch(ClassNotFoundException |SQLException e) {

System.out.println(e.getLocalizedMessage());

System.out.println("数据库连接失败！");

}

}

//断开数据库

public void disconnect()

{

try {

connection.close();

System.out.println("数据库关闭成功！");

} catch (SQLException e) {

e.printStackTrace();

System.out.println("数据库关闭失败！");

}

}

}

同时注意，需要在MySQL官网下载实验需要的数据库连接文件并添加到项目环境中

完成SQL操作写成嵌入到Java编程中，实现对学生表的操作。

package JDBC;

import java.io.IOException;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Student_OP {

public Statement state=null;

public void set_state(JDBCcon Database) throws IOException {

try {

state= Database.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

} catch (SQLException e) {

e.printStackTrace();

}

}

//创建学生表

public void create_student() throws IOException {

String sql="create table student(Sno varchar(10) PRIMARY KEY,Sname varchar(10))";

try {

state.executeUpdate(sql);

System.out.println("创建学生表成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//删除学生表

public void delete_student_table() throws IOException {

String sql="drop table student;";

try {

state.executeUpdate(sql);

} catch (SQLException e) {

e.printStackTrace();

}

}

//添加学生

public void add_student(String Sno,String Sname) throws IOException {

String sql="insert into student values('"+Sno+"','"+Sname+"')";

try {

state.executeUpdate(sql);

System.out.println("添加学生"+ Sname + "成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//删除学生

public void delete_student(String Sno) throws IOException {

String sql="delete from student where Sno='"+Sno+"'";

try {

state.executeUpdate(sql);

System.out.println("删除学生"+ Sno + "成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//显示学生

public void show_student() throws IOException, SQLException {

System.out.println("student表数据如下：");

System.out.println("+----------+----------+");

System.out.println("| Sno | Sname |");

System.out.println("+----------+----------+");

String sql="select * from student";

ResultSet rs=null;

try {

rs = state.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

while (true) {

try {

if (!rs.next())

break;

String s1 = rs.getString(1);

String s2 = rs.getString(2);

System.out.printf("|%-10s|%-10s|\n",s1 ,s2);

} catch (SQLException e) {

e.printStackTrace();

}

}

System.out.println("+----------+----------+");

rs.close();

}

}

完成SQL操作写成嵌入到Java编程中，实现对课程表的操作。

package JDBC;

import java.io.IOException;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Class_OP {

public Statement state=null;

public void set_state(JDBCcon Database) throws IOException {

try {

state= Database.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

} catch (SQLException e) {

e.printStackTrace();

}

}

//创建课程表

public void create_class() throws IOException {

String sql="create table class(Cno varchar(10) PRIMARY KEY,Cname varchar(10))";

try {

state.executeUpdate(sql);

System.out.println("创建课程表成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//删除课程表

public void delete_class_table() throws IOException {

String sql="drop table class;";

try {

state.executeUpdate(sql);

} catch (SQLException e) {

e.printStackTrace();

}

}

//添加课程

public void add_class(String Cno,String Cname) throws IOException {

String sql="insert into class values('"+Cno+"','"+Cname+"')";

try {

state.executeUpdate(sql);

System.out.println("添加课程"+ Cname + "成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//删除课程

public void delete_class(String Cno) throws IOException {

String sql="delete from class where Cno='"+Cno+"'";

try {

state.executeUpdate(sql);

System.out.println("删除课程"+ Cno + "成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//显示具体某一课程的信息

public void show_class(String Cno) throws IOException, SQLException {

System.out.println("查询到该课程信息如下：");

System.out.println("+----------+--------------------+");

System.out.println("| Cno | Cname |");

System.out.println("+----------+--------------------+");

String sql="select * from class where Cno='"+Cno+"'";

ResultSet rs=null;

try {

rs = state.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

while(rs.next()) {

System.out.printf("|%-10s|%-15s\t|\n",rs.getString("Cno"),rs.getString("Cname"));

}

System.out.println("+----------+--------------------+");

}

//显示课程表

public void show_class() throws IOException, SQLException {

System.out.println("class表数据如下：");

System.out.println("+----------+--------------------+");

System.out.println("| Cno | Cname |");

System.out.println("+----------+--------------------+");

String sql="select * from class";

ResultSet rs=null;

try {

rs = state.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

while (true) {

try {

if (!rs.next())

break;

String s1 = rs.getString(1);

String s2 = rs.getString(2);

System.out.printf("|%-10s|%-15s\t|\n",s1 ,s2);

} catch (SQLException e) {

e.printStackTrace();

}

}

System.out.println("+----------+--------------------+");

rs.close();

}

}

完成SQL操作写成嵌入到Java编程中，实现对学生选课表的操作。

package JDBC;

import java.io.IOException;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class SC_op {

public Statement state=null;

public void set_state(JDBCcon Database) throws IOException {

try {

state= Database.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

} catch (SQLException e) {

e.printStackTrace();

}

}

//创建学生选课表

public void create_SC() throws IOException {

String sql="create table SC(Sno varchar(10),Cno varchar(10),Cscore int, PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno) REFERENCES student(Sno),FOREIGN KEY(Cno) REFERENCES class(Cno))";

try {

state.executeUpdate(sql);

System.out.println("创建学生选课表成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//删除学生选课表

public void delete_SC_table() throws IOException {

String sql="drop table SC;";

try {

state.executeUpdate(sql);

} catch (SQLException e) {

e.printStackTrace();

}

}

//添加选课

public void add_SC(String Sno,String Cno,int Cscore) throws IOException {

String sql="insert into SC values('"+Sno+"','"+Cno+"',"+Cscore+")";

try {

state.executeUpdate(sql);

System.out.println("添加选课成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//删除选课

public void delete_SC(String Sno,String Cno) throws IOException {

String sql="delete from SC where Sno='"+Sno+"' and Cno='"+Cno+"'";

try {

state.executeUpdate(sql);

System.out.println("删除选课成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//修改这一课程学生的成绩

public void update_SC(String Sno,String Cno,int Cscore) throws IOException {

String sql="update SC set Cscore="+Cscore+" where Sno='"+Sno+"' and Cno='"+Cno+"'";

try {

state.executeUpdate(sql);

System.out.println("修改学生"+ Sno+"成绩成功。");

} catch (SQLException e) {

e.printStackTrace();

}

}

//显示某一课程的信息

public void show_SC(String Cno) throws IOException, SQLException {

System.out.println("查询到该课程信息如下：");

System.out.println("+----------+----------+----------+");

System.out.println("| Sno | Cno | Cscore |");

System.out.println("+----------+----------+----------+");

String sql="select * from SC where Cno='"+Cno+"'";

ResultSet rs=null;

try {

rs = state.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

while (true) {

try {

if (!rs.next())

break;

String s1 = rs.getString(1);

String s2 = rs.getString(2);

int s3 = rs.getInt(3);

System.out.printf("|%-10s|%-10s|%-10s|\n",s1 ,s2 ,s3);

} catch (SQLException e) {

e.printStackTrace();

}

}

System.out.println("+----------+----------+----------+");

rs.close();

}

//显示选课表

public void show_SC() throws IOException, SQLException {

System.out.println("SC表数据如下：");

System.out.println("+----------+----------+----------+");

System.out.println("| Sno | Cno | Cscore |");

System.out.println("+----------+----------+----------+");

String sql="select * from SC";

ResultSet rs=null;

try {

rs = state.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

while (true) {

try {

if (!rs.next())

break;

String s1 = rs.getString(1);

String s2 = rs.getString(2);

int s3 = rs.getInt(3);

System.out.printf("|%-10s|%-10s|%-10s|\n",s1 ,s2 ,s3);

} catch (SQLException e) {

e.printStackTrace();

}

}

System.out.println("+----------+----------+----------+");

rs.close();

}

}

在主函数中初始化数据，并完成实验要求。

package main;

import java.io.IOException;

import java.sql.SQLException;

import java.util.Scanner;

import JDBC.*;

public class main {

public static void main(String[] args) throws IOException, SQLException{

//与库建立连接

System.out.println(System.getProperty("user.dir"));

JDBCcon Database = new JDBCcon();

Database.conf();

Database.insert();

Student_OP student_op = new Student_OP();

student_op.set_state(Database);

Class_OP class_op = new Class_OP();

class_op.set_state(Database);

SC_op sc_op = new SC_op();

sc_op.set_state(Database);

//初始化数据库

sc_op.delete_SC_table();

student_op.delete_student_table();

class_op.delete_class_table();

student_op.create_student();

student_op.add_student("1","张三");

student_op.add_student("2","李四");

student_op.add_student("3","王五");

student_op.add_student("4","赵六");

student_op.add_student("5","孙七");

class_op.create_class();

class_op.add_class("1","软件工程");

class_op.add_class("2","计算机科学");

class_op.add_class("3","数学");

class_op.add_class("4","物理");

class_op.add_class("5","化学");

sc_op.create_SC();

sc_op.add_SC("1","1",60);

sc_op.add_SC("2","1",100);

sc_op.add_SC("3","1",80);

sc_op.add_SC("4","1",90);

sc_op.add_SC("5","1",70);

sc_op.add_SC("1","2",60);

sc_op.add_SC("2","2",100);

sc_op.add_SC("3","2",80);

sc_op.add_SC("4","2",90);

sc_op.add_SC("5","2",70);

sc_op.add_SC("1","3",60);

System.out.println("数据库初始化成功！");

//第四步输出每个表格的信息

student_op.show_student();

class_op.show_class();

sc_op.show_SC();

try (//第一步查询某一门课程的信息

var sc = new Scanner(System.in)) {

System.out.println("第一步查询某一门课程的信息\n请输入要查询的课程号：");

String class_id=sc.nextLine();

class_op.show_class(class_id);

//第二步查询选修某一门课程的选课信息

System.out.println("第二步查询选修某一门课程的选课信息\n请输入要查询的课程号：");

class_id=sc.nextLine();

sc_op.show_SC(class_id);

//第三步修改这一课程学生的成绩

System.out.println("请输入要修改的学生学号：");

String student_id=sc.nextLine();

System.out.println("请输入要修改的成绩：");

String s=sc.nextLine();

int score=Integer.parseInt(s);

sc_op.update_SC(student_id,class_id,score);

} catch (NumberFormatException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

//第四步输出每个表格的信息

student_op.show_student();

class_op.show_class();

sc_op.show_SC();

Database.disconnect();

}

}