capstone训练营2018-2-6

教材管理系统需求文档

第一步：在eclipse控制台上进行操作。（近期完成）

需要完成：

两个数据表：教材库存表、教材详细表

Java后台实现：从控制台输入教材信息，完成对教材的库存查询，对每一个教材进行增、删、改、查。

如：输入a教材100本；b教材100本；

1、查询a的库存

2、更改b的名字、数量、出版社（等属性）；

3、分别显示在控制台。

第二步：用web前端进行操作。（假期完成）

需要完成：

数据库和java后台与第一步相同，需要自学servlet、完成从web前台获取数据，把数据传到后台进行操作，在返回给前台显示到页面上。

完成一部分：

 

package QAQ;

import java.util.Scanner;

//测试类文件
public class text {
	// 主程序入口
	public static void main(String[] args) {
		// 实例化键盘输入
		Scanner input = new Scanner(System.in);
		// 实例化操作类
		Do a = new Do();
		System.out.print("请输入用户名:");
		String username = input.next();
		System.out.print("请输入密码");
		String password = input.next();
		if (a.found(username, password)) { // 输入用户名及密码与数据库中的用户名及密码作比较
			System.out.println("登陆成功！欢迎你！" + username);
			for (;;) {
				System.out.println("请选择操作:");
				System.out.println("1.查询库存  2.查询书籍信息  3.添加书籍  4.删除书籍  0.退出");
				System.out.print("请选择:");
				int num = input.nextInt();
				switch (num) {
				case 1:
					a.show();
					break;
				case 2:
					a.show1();
					break;
				case 3:
					System.out.print("请输入书的Id:");
					int Id = input.nextInt();
					System.out.print("请输入书的名字:");
					String book_name = input.next();
					System.out.print("请输入书的出版社:");
					String book_concern = input.next();
					System.out.print("请输入与书的作者:");
					String book_writer = input.next();
					a.show2(Id, book_name, book_concern, book_writer);
					break;
				case 4:
					System.out.print("请输入id号:");
					int num1 = input.nextInt();
					a.show3(num1);
					break;
				case 0:
					num = 0;
					break;
				default:
					System.out.println("输入错误");
					break;
				}
				if (num == 0) {
					System.out.println("系统退出，谢谢使用！！！");
					break;
				}
			}

		} else {
			System.out.println("用户名或者密码错误");
		}

	}
}

package QAQ;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Do {

	// 登陆界面
	public boolean found(String username, String password) {
		boolean find = false;
		// 加载驱动
		try { //
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 建立连接
		Connection cn = null; // 声明连接对象
		PreparedStatement ps = null; // 操作对象
		ResultSet rs = null; // 结果集对象

		try { //
			cn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookmanage", "root", "root");

			String sql = "select * from admin where username=?  and password=?";
			ps = cn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if (rs.next()) {
				find = true;
			} else {
				find = false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return find;
	}

	public void show() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 建立连接
		Connection cn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			cn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookmanage", "root", "root");

			String sql = "select * from bookinventory";
			ps = cn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				int num = rs.getInt(1);
				String book_name = rs.getString(2);
				int book_num = rs.getInt(3);
				System.out.println(num + "\t" + book_name + "\t" + book_num);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void show1() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 建立连接
		Connection cn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			cn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookmanage", "root", "root");

			String sql = "select * from bookdetailed ";
			ps = cn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int num = rs.getInt(1);
				String book_name = rs.getString(2);
				String book_concern = rs.getString(3);
				String book_writer = rs.getString(4);

				System.out.println(num + "\t" + book_name + "\t" + book_concern + "\t" + book_writer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 插入数据
	public void show2(int Id, String book_name, String book_concern, String book_writer) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection cn = null;
		PreparedStatement ps = null;
		try {
			cn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookmanage", "root", "root");
			String sql = "insert into bookdetailed(Id,book_name,book_concern,book_writer) values(?,?,?,?)";
			ps = cn.prepareStatement(sql);
			ps.setInt(1, Id);
			ps.setString(2, book_name);
			ps.setString(3, book_concern);
			ps.setString(4, book_writer);

			int i = ps.executeUpdate();
			if (i > 0) {
				System.out.println("恭喜你，添加成功！！！");
			} else {
				System.out.println("添加失败！！！");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 删除数据
	public void show3(int Id) {
		// 加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Connection cn = null;
		PreparedStatement ps = null;
		try {
			cn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookmanage", "root", "root");
			String sql = "DELETE FROM bookdetailed WHERE id=?";
			ps = cn.prepareStatement(sql);
			ps.setInt(1, Id);
			int i = ps.executeUpdate();
			if (i > 0) {
				System.out.println("恭喜你，删除成功！！！");
			} else {
				System.out.println("删除失败，你输入的ID不存咋！！！");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// 释放连接
		try {
			if (ps != null) {
				ps.close();
			}
			if (cn != null) {
				cn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}