capstone训练营2018-2-7

完成教材管理系统项目

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.Arrays;
import java.util.List;
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?characterEncoding=utf-8", "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?characterEncoding=utf-8", "root", "root");

			String sql = "select bk.id,bk.book_name,bk.book_concern,bk.book_writer,bi.book_num,bi.indate from bookdetailed bk,bookinventory bi where bk.id=bi.book_id";
			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);
				String book_num= rs.getString(5);
				String book_indate = rs.getString(6);
				System.out.println(num + "\t" + book_name + "\t" + book_num+ "\t" + book_concern +"\t" + book_writer+ "\t" + book_indate);
			}
		} 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,int book_id,String book_num,String indate) {
		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?characterEncoding=utf-8", "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);
			ps.executeUpdate();
			
			sql = "insert into bookinventory(book_id,book_num,indate) values(?,?,?)";
			ps = cn.prepareStatement(sql);
			ps.setInt(1, book_id);
			ps.setString(2, book_num);
			ps.setString(3, indate);
			

			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(String name) {
		// 加载驱动
		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?characterEncoding=utf-8", "root", "root");
			String sql = "DELETE bookdetailed,bookinventory FROM bookdetailed LEFT JOIN bookinventory ON bookdetailed.id=bookinventory.book_id WHERE bookdetailed.book_name=?";
			ps = cn.prepareStatement(sql);
			ps.setString(1, name);
			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 show6(String book_name, String seting,String setname) {
		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?characterEncoding=utf-8", "root", "root");
			String [] tableA = {"id","book_name","book_concern","book_writer"};
			List<String> list=Arrays.asList(tableA);
			String [] tableB = {"book_id","book_num","indate"};
			List<String> list2=Arrays.asList(tableB);
			String settable ="bookdetailed";
			if(list.contains(setname)) {
				String bookdetailed ="bookdetailed";
				settable = bookdetailed;
			}else if(list2.contains(setname)) {
				 String bookinventory = "bookinventory";
				settable = bookinventory;
			}
			String sql = "update bookinventory,bookdetailed set "+settable+"."+setname+"=? where bookdetailed.id=bookinventory.book_id and bookdetailed.book_name=? ";
			ps = cn.prepareStatement(sql);
//			ps.setString(1, setname);
			ps.setString(1, seting);
			ps.setString(2,book_name);


			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();
		}
	}

}

package QAQ;

import java.util.Scanner;

//测试类文件
public class text {
	// 主程序入口
	public static void main(String[] args) {
			text();
	}
	public static void text() {
		// 实例化键盘输入
				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.查询所有书 \n2.添加书籍信息\n3.删除书籍 \n4.更改库存\n0.退出");
						System.out.print("请选择:");
						int num = input.nextInt();
						switch (num) {
						case 1:
							System.out.println("Id" + "\t" + "书名" + "\t" + "库存" + "\t" + "出版社" + "\t" + "作者" + "\t" + "入库日期");
							a.show();
							break;
						
						case 2:
							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();

							int book_id = Id;
							System.out.print("请输入书的数量:");
							String book_num = input.next();
							System.out.print("请输入时间:");
							String indate = input.next();

							a.show2(Id, book_name, book_concern, book_writer, book_id, book_num, indate);
							break;
						case 3:
							System.out.print("请输入书名:");
							String name = input.next();
							a.show3(name);
							break;
						
						case 4:
							System.out.print("请输入要更改的名字");
							String id1 = input.next();
							System.out.print("请输入要修改的项");
							String setname1 = input.next();
							System.out.print("请输入修改的值");
							String seting1 = input.next();
							a.show6(id1, seting1, setname1);
							break;
						case 0:
							num = 0;
							break;
						default:
							System.out.println("输入错误");
							break;
						}
						if (num == 0) {
							System.out.println("系统退出，谢谢使用！！！");
							break;
						}
					}

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