使用JDBC连接SQL数据库实现增删改查

一、项目介绍

最近闲得无聊，看到之前做的一个小作业，觉得自己进步了，所有拿出来和大家分享分享。也就是一个使用JDBC连接数据库从而实现增删改查。

1.开发环境

开发工具：eclipse

JDK版本：jdk1.8

SQLserver版本：2012

2.代码部分

首先我们先通过JDBC来与数据库进行连接，并获取到数据库中的数据，然后我们给定一些选项可以来对数据库中的数据进行操作。例如增删查改等

public static void main(String[] args) {
		// TODO Auto-generated method stub
		Scanner input = new Scanner(System.in);
		System.out.println("欢迎进入学生管理系统，请选择学生编号:");
		show();
		//选择需要查询的学生的学号
		try{
			int s_id = 0;
			while (true){
				s_id = input.nextInt();
				if(!query(s_id))
					System.out.println("查无此人，请重新输入！");
				else break;
			}
			String name = null;
			name = queryName(sno);
			System.out.println("欢迎查询" + name + "学生所选修课程，");
			while(true){
				System.out.println("\n1.查询课程\n2.新增课程\n3.删除课程\n4.课程信息修改\n5.返回首页\n请输入您的操作编号：");
				int num = 0;
				num = input.nextInt();
				switch(num) {
				case 1:
					queryCourse(sno);
					break;
				case 2:
					addCourse(sno);
					break;
				case 3:
					deleteCourse(sno);
					break;
				case 4:
					updateCourse(sno);
					break;
				case 5:
					main(args);
					break;
				}
			}
		}catch(Exception e){
			System.out.println("输入错误，请重新输入 !");
		}
		
	}

下面是运行效果

 下面是增加课程功能

//新增课程
	public static void addCourse(int sno) {
		Connection conn = null;
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
			conn = DriverManager.getConnection(url,"**","******");
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement("insert into cour values(?,?,?)");
			while(true) {
				Scanner input = new Scanner(System.in);
				System.out.println("请输入新增课程号：");
				String coursecode = input.nextLine();
				System.out.println("请输入新增课程名：");
				String coursename = input.nextLine();
				pstmt.setString(1, coursecode);
				pstmt.setString(2, coursename);
				pstmt.setString(3, String.valueOf(sno));
				pstmt.executeUpdate();
				System.out.println("数据插入成功！");
				System.out.println("是否继续输入：Y/N");
				String flag = input.next();
				if(flag.equals("n") || flag.equals("N")) {
					break;
				}
			}
			queryCourse(sno);
		}catch(Exception e){
			System.out.println("不行");
		}
	}

删除课程功能

	
//删除课程
	public static void deleteCourse(int son) {
		Connection conn = null;
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
			conn = DriverManager.getConnection(url,"**","******");
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement("delete from cour where cname=?");
			while(true) {
				Scanner input = new Scanner(System.in);
				System.out.println("请输入需要删除的课程名：");
				String coursename = input.nextLine();
				pstmt.setString(1, coursename);
				pstmt.executeUpdate();
				System.out.println("数据删除成功！");
				System.out.println("是否继续删除：Y/N");
				String flag = input.next();
				if(flag.equals("n") || flag.equals("N")) {
					break;
				}
			}
			queryCourse(son);
		}catch(Exception e){
			System.out.println("不行");
		}
	}

数据库中学生表的信息全部显示

//全部显示
	public static void show() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
			conn = DriverManager.getConnection(url,"**","******");
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from stu");
			while(rs.next()){
				System.out.print(rs.getString("sno")+" ");
				System.out.print(rs.getString("sname")+" ");
				System.out.print(rs.getString("ssex")+" ");
				System.out.println(rs.getString("scs")+" ");
				}
		}catch(Exception e){
			System.out.println("不行");
		}finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

查询

//查询
	public static boolean query(int sno) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
			conn = DriverManager.getConnection(url,"**","******");
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from stu");
			while(rs.next()){
				if(sno == Integer.parseInt(rs.getString("sno"))) {
					return true;
				}
			}
		}catch(Exception e){
			System.out.println("不行");
		}finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

查询姓名

//查询姓名
	public static String queryName(int sno) {
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			String name = null;
			try{
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
				conn = DriverManager.getConnection(url,"**","******");
				stmt = conn.createStatement();
				rs = stmt.executeQuery("select sname from stu where sno=" + "'" + sno + "'" );
				while(rs.next()){
					name = rs.getString("sname");
				}
			}catch(Exception e){
				System.out.println("不行");
			}finally {
				try {
					if (rs != null)
						rs.close();
					if (stmt != null)
						stmt.close();
					if (conn != null)
						conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return name;
		}

查询课程

//查询课程
	public static void queryCourse(int sno) {
				Connection conn = null;
				Statement stmt = null;
				ResultSet rs = null;
				String name = null;
				try{
					Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
					String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
					conn = DriverManager.getConnection(url,"**","******");
					stmt = conn.createStatement();
					rs = stmt.executeQuery("select cno,cname from course where S_id=" + "'" + sno + "'" );
					while(rs.next()){
						System.out.print(rs.getString("cno")+ " ");
						System.out.println(rs.getString("cname"));
					}
				}catch(Exception e){
					System.out.println("不行");
				}finally {
					try {
						if (rs != null)
							rs.close();
						if (stmt != null)
							stmt.close();
						if (conn != null)
							conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}

修改课程

//修改课程
	public static void updateCourse(int sno) {
		Connection conn = null;
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
			conn = DriverManager.getConnection(url,"**","******");
			PreparedStatement pstmt = null;
			pstmt = conn.prepareStatement("update cour set cname=? where cname=?");
			while(true) {
				Scanner input = new Scanner(System.in);
				System.out.println("请输入需要修改的课程名：");
				String coursename = input.nextLine();
				pstmt.setString(2, coursename);
				System.out.println("修改为：");
				String fixname = input.nextLine();
				pstmt.setString(1, fixname);
				pstmt.executeUpdate();
				System.out.println("数据修改成功！");
				System.out.println("是否继续输入：Y/N");
				String flag = input.next();
				if(flag.equals("n") || flag.equals("N")) {
					break;
				}
			}
			queryCourse(sno);
		}catch(Exception e){
			System.out.println("不行");
		}
	}
	

3.关于数据库部分的数据

数据库的部分没什么太大的难度，也就是创建两个表

一个学生表，一个课程表，里面的内容自己随便添加即可。

4.JDBC

想了想。还是把JDBC的连接部分说明一下吧。

以上述代码查询为例：

Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
            //JDBC驱动，SQL与MySQL不同，MySQL的连接方式大家可自行去查阅一下，大致差不多
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             //localhost:1433(端口号）DatabaseName=DB（数据库名字)
			String url = "jdbc:sqlserver://localhost:1433;DatabaseName=DB;";
            rul后面为自己的数据库登录账号与密码
			conn = DriverManager.getConnection(url,"**","******");
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from stu");
			while(rs.next()){
				System.out.print(rs.getString("sno")+" ");
				System.out.print(rs.getString("sname")+" ");
				System.out.print(rs.getString("ssex")+" ");
				System.out.println(rs.getString("scs")+" ");
				}
		}catch(Exception e){
			System.out.println("不行");
		}finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

4.结语

总体上差不多就是这样，这个算是比较简单的一种，只是涉及到增删改查方面，基本上都是按照格式来就行。刚开始学习java的时候这个作业也算是困扰了自己一段时间，但现在看来就觉得比较轻松了。也算是记录一下自己的成长吧，新博主，希望各位点点关注，以后会不断更文的。