上机实验-10 JDBC数据库编程

一、实验目的

1、掌握Java连接数据库的操作。

2、掌握DriverManager、Connection、Statement、PreparedStatement、ResultSet五大类的使用方法。

二、实验内容 

1、创建一个测试类，连接student数据库，测试连接数据库是否成功，并显示学生信息表中数据。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Connect {
	public static void main(String[] args) throws SQLException,ClassNotFoundException {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("驱动加载成功");
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("驱动加载失败");
		}
		String url="jdbc:mysql://localhost:3306/mysql";
		String user="root";
		String password="root";
		try {
			conn = DriverManager.getConnection(url,user,password);
			System.out.println("数据库连接成功");
			//创建命令对象
			Statement stmt = conn.createStatement();
			//给出sql语句，查询test表中的所有数据
			String sql = "select * from stuinfo";
			//执行sql语句，得到多条记录，结果集ResultSet
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
			//getString(columIndex) columIndex表示列号，从1开始
			System.out.println("学号："+rs.getInt(1));
			System.out.println("姓名："+rs.getString(2));
			System.out.println("性别："+rs.getString(3));
			System.out.println("班级："+rs.getString(4));
			System.out.println("电话："+rs.getInt(5));	
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("连接失败");
		}
	}
}

2、设计一个实现学生信息管理的程序，要求合法的学生才能进入系统，在系统中可以查看学生的基本信息和成绩，并能对各个信息进行简单的增删改操作。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import com.mysql.jdbc.PreparedStatement;
public class Manage {
	public static void main(String[] args) throws SQLException,ClassNotFoundException {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("驱动加载成功");
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("驱动加载失败");
		}
		String url="jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=gbk";
		String user="root";
		String password="root";
		try {
			conn = DriverManager.getConnection(url,user,password);
			System.out.println("数据库连接成功");
			//创建命令对象
			Statement stmt = conn.createStatement();
			System.out.println("请输入选择：1--查看 2--增加 3--删除 4--修改");
		    Scanner val = new Scanner(System.in);
		    int op = val.nextInt();
		    switch(op) {
		    case 1:
		    //给出sql语句，查询test表中的所有数据
			String sql1 = "select * from score";
			//执行sql语句，得到多条记录，结果集ResultSet
			ResultSet rs = stmt.executeQuery(sql1);
			while(rs.next()) {
			//getString(columIndex) columIndex表示列号，从1开始
			System.out.println("学号："+rs.getInt(1));
			System.out.println("课程名："+rs.getString(2));
			System.out.println("分数："+rs.getInt(3));
			}
			rs.close();
			stmt.close();
			conn.close();
			break;
		    case 2:
		    Statement stmt2 = conn.createStatement();
		    int adsno = 0;
			String adcname = null;
			int adscore = 0;
			System.out.println("请输入要增加的内容：");
			adsno = val.nextInt();
			adcname = val.next();
			adscore = val.nextInt();
			String sql2 = "insert into score (sno,cname,score) value("+adsno+",'"+adcname+"',"+adscore+")";
			int result2 = stmt.executeUpdate(sql2);
			System.out.println("增加成功");
			break;
		    case 3:
			int sno = val.nextInt();
			System.out.println("请输入要删除的学号：");
			String sql3 = "delete from score where sno = "+sno;
			int result3 = stmt.executeUpdate(sql3);
			System.out.println("删除成功");
			break;
		    case 4:
		    Statement stmt4 = conn.createStatement();
		    int upsno = 0;
		    String upcname = null;
		    int upscore = 0;
		    System.out.println("请输入要修改的学号和内容（如：1 化学 88）：");
			upsno = val.nextInt();
			upcname = val.next();
			upscore = val.nextInt();
		    String sql4 = "update score set sno = ? where cname = ? and score = ?";
		    PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql4);
		    pstmt.setInt(1, upsno);
		    pstmt.setString(2,upcname);
		    pstmt.setInt(3, upscore);
		    pstmt.executeUpdate();
		    pstmt.close();
		    System.out.println("修改成功"); 
		    break;
		    }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("连接失败");
		}
		
	}
}