sqlmethod.java

图书管理系统。 借书

package shujuku;

import java.sql.*;
import java.util.Vector;

public class SQLMethod {
	public static String sd[] = new String[8];

	public static String dd[] = new String[8];
	public static String sdz[]=new String[6];
	public static boolean b = false;

	// set
	

	public void addbookData(String bookid, String bookname, String authorname,
			String publisher, String bookprice, String info, String classfy,
			String booksituation) throws ClassNotFoundException, SQLException {
		Connection con = LinkToDB.buildlinktodb();
		String sql = "insert into 图书 values ('" + bookid + "','" + bookname
				+ "','" + authorname + "','" + publisher + "','" + bookprice
				+ "','" + info + "','" + classfy + "','" + booksituation + "')";// System.out.println(sql);
		Statement stat = con.createStatement();
		stat.execute(sql);
		stat.close();
		con.close();
	}

	public void addreaderData(String id, String name, String level,
			String address, String tel, String email)
			throws ClassNotFoundException, SQLException {
		Connection con = LinkToDB.buildlinktodb();
		Statement stat = con.createStatement();
		stat.execute("insert into 读者 values ('" + id + "','" + name + "','"
				+ level + "','" + address + "','" + tel + "','" + email + "')");
		stat.close();
		con.close();
	}
	

	

	

	
	

	public void persionalUpdate(String id, String name, String sex,
			String major, String address, String phone, String email,
			String addition) throws ClassNotFoundException, SQLException {
		Connection con = LinkToDB.buildlinktodb();
		Statement stmt = con.createStatement();
		stmt.executeUpdate("UPDATE Reader SET ReaderSex = '" + sex
				+ "' WHERE ReaderID = '" + id + "'");
		stmt.executeUpdate("UPDATE Reader SET ReaderName = '" + name
				+ "' WHERE ReaderID = '" + id + "'");
		stmt.executeUpdate("UPDATE Reader SET ReaderMajor= '" + major
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.executeUpdate("UPDATE Reader SET ReaderAddress= '" + address
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.executeUpdate("UPDATE Reader SET ReaderTelphone= '" + phone
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.executeUpdate("UPDATE Reader SET Email= '" + email
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.executeUpdate("UPDATE Reader SET Addition= '" + addition
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.executeUpdate("UPDATE Reader SET ReaderTelphone= '" + phone
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.executeUpdate("UPDATE Reader SET ReaderAddress= '" + address
				+ "' WHERE (ReaderID =" + id + ")");
		stmt.close();
		con.close();
	}

	public void bookUpdate(String id, String name, String author,
			String publisher, String price, String info, String classfy,
			String status) throws ClassNotFoundException, SQLException {
		Connection con = LinkToDB.buildlinktodb();
		Statement stmt = con.createStatement();
		stmt.executeUpdate("UPDATE 图书 SET BOOKID图书编号 = '" + id
				+ "' WHERE BookID图书编号 = '" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET BOOKNAME图书名 = '" + name
				+ "' WHERE BookID图书编号= '" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET AUTHOR图书作者= '" + author
				+ "' WHERE BookID图书编号='" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET PUBLISHER图书出版社= '" + publisher
				+ "' WHERE BookID图书编号='" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET BOOKPRICE图书单价= '" + price
				+ "' WHERE BookID图书编号='" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET ABSTRACT图书摘要= '" + info
				+ "' WHERE BookID图书编号='" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET CLASS图书分类= '" + classfy
				+ "' WHERE BookID图书编号='" + id + "'");
		stmt.executeUpdate("UPDATE 图书 SET BOOKSTATUS图书状态= '" + status
				+ "' WHERE BookID图书编号='" + id + "'");
		stmt.close();
		con.close();
	}

	public void ReaderUpdate(String id, String name, String level,
			String address, String tel, String email)
			throws ClassNotFoundException, SQLException {
		Connection con = LinkToDB.buildlinktodb();
		Statement stmt = con.createStatement();
		 stmt.executeUpdate("UPDATE 读者 SET READERID读者识别号= '"+ id +
				 "' WHERE READERID读者识别号 = '"+id+" '");
		stmt.executeUpdate("UPDATE 读者 SET NAME读者姓名 = '" + name
				+ "' WHERE READERID读者识别号= '" + id + " '");
		stmt.executeUpdate("UPDATE 读者 SET LEVEL读者权限= '" + level
				+ "' WHERE READERID读者识别号='" + id + "'");
		stmt.executeUpdate("UPDATE 读者 SET ADDRESS读者联系方法= '" + address
				+ "' WHERE READERID读者识别号='" + id + "'");
		stmt.executeUpdate("UPDATE 读者 SET TELEPHONE读者号码= '" + tel
				+ "' WHERE READERID读者识别号='" + id + "'");
		stmt.executeUpdate("UPDATE 读者 SET EMAIL读者邮箱= '" + email
				+ "' WHERE READERID读者识别号='" + id + "'");
		stmt.close();
		con.close();
	}

	
	public void setnewpassword(String id, String password) throws SQLException,
			ClassNotFoundException {
		Connection con = LinkToDB.buildlinktodb();
		String sql = "UPDATE Reader SET ReaderPassword ='" + password
				+ "' WHERE ReaderID='" + id + "'";
		Statement stmt = con.createStatement();
		stmt.executeUpdate(sql);

	}

	
	

	

	public void deletebook(String id) throws ClassNotFoundException,
			SQLException {
		Connection con = LinkToDB.buildlinktodb();
		Statement stmt = con.createStatement();
		stmt.executeUpdate("DELETE FROM 图书 WHERE BOOKID图书编号='" + id + "'");
		con.close();
		stmt.close();
	}
	public void deletereader(String id) throws ClassNotFoundException,
	SQLException {
        Connection con = LinkToDB.buildlinktodb();
        Statement stmt = con.createStatement();
        stmt.executeUpdate("DELETE FROM 读者 WHERE READERID读者识别号='" + id + "'");
        con.close();
        stmt.close();
}
	

	public void borrowBook(String BookID, String ReaderID)
			throws ClassNotFoundException, SQLException {
		String a = BookID;
		String b = ReaderID;
		Connection con = LinkToDB.buildlinktodb();
		SQLMethod2 l = new SQLMethod2();
		String c = l.year + "-" + l.month + "-" + l.day;
		Statement state1 = con.createStatement();
		String sql = "insert into 图书访问记录 values('" + a + "','" + b + "','" + c
				+ "')";
		state1.execute(sql);

		Statement state2 = con.createStatement();
		String ssss="update 图书 set BOOKSTATUS图书状态='0' where BOOKID图书编号='" + a + "'";
			state2.execute(ssss);  
		state1.close();
		state2.close();
		con.close();// System.out.println(aa);
	}

	public void returnBook(String BookID) throws ClassNotFoundException,
			SQLException {
		Connection con = LinkToDB.buildlinktodb();
		String a = BookID;

		SQLMethod2 l = new SQLMethod2();

		Statement state2 = con.createStatement();
		Boolean result2 = state2.execute("update 图书 set BOOKSTATUS图书状态='1'"
				+ " where BOOKID图书编号='" + a + "'");

		state2.close();
		con.close();
	}

	//
	public static String cha(int n, String sss, boolean bb) {
		String ds = " ";
		b = bb;
		boolean nn = true;
		int in = 0;
		String dbUrl = "jdbc:odbc:db1";
		String sa = null;
		String user = "            ";
		String password = "";
		Statement s;
		ResultSet results;
		try {

			Connection c = LinkToDB.buildlinktodb();
			s = c.createStatement();
			results = s.executeQuery("SELECT * FROM 图书");
			while (results.next()) {
				sd[2] = results.getString("BOOKID图书编号");
				sd[0] = results.getString("BOOKNAME图书名");
				sd[1] = results.getString("AUTHOR图书作者");				
				sd[3] = results.getString("PUBLISHER图书出版社");
				sd[4] = results.getString("ABSTRACT图书摘要");
				sd[5] = results.getString("CLASS图书分类");
				sd[6] = results.getString("STAUS图书状态");
				if (sss.equals(sd[n])) {
					dd[in] = /*" 书号:  " +*/ sd[2] + " 图书书名:  " + sd[0] + " 图书作者:  "
							+ sd[1] + " 图书出版社:   " + sd[3] + " 关键字:  " + sd[4]
							+ " 类别:  " + sd[5] + " 图书状态:  " + sd[6];
					ds = ds + dd[in];// "\n\n"+
					b = true;
					in++;

					// System.out.println(ds);

				}
			}
			c.close();
			s.close();
			results.close();
		} catch (Exception e) {
			// System.out.println("ERROR");
			// System.out.println(e.getMessage());
		} // if(!ds.equals(""))
		// {
		// ds=ds+"\n\n**********************";
		// }
		return ds;

	}
	public static String xitongcha(int n, String sss, boolean bb) {
		String ds = "";
		b = bb;
		boolean nn = true;
		int in = 0;
		String dbUrl = "jdbc:odbc:db1";
		String sa = null;
		String user = "            ";
		String password = "";
		Statement s;
		ResultSet results;
		try {

			Connection c = LinkToDB.buildlinktodb();
			s = c.createStatement();
			results = s.executeQuery("SELECT * FROM 图书");
			while (results.next()) {
				sd[2] = results.getString("BOOKID图书编号");
				sd[0] = results.getString("BOOKNAME图书名");
				sd[1] = results.getString("AUTHOR图书作者");				
				sd[3] = results.getString("PUBLISHER图书出版社");
				sd[4] = results.getString("ABSTRACT图书摘要");
				sd[5] = results.getString("CLASS图书分类");
				sd[6] = results.getString("STAUS图书状态");
				sd[7] = results.getString("BOOKPRICE图书单价");
				if (sss.equals(sd[n])) {
					dd[in] = /*" 书号:  " +*/ sd[2] + "图书书名:  " + sd[0] + " 图书作者:  "
							+ sd[1] + " 图书出版社:   " + sd[3] +" 图书单价:   " + sd[7] + " 关键字:  " + sd[4]
							+ " 类别:  " + sd[5] + " 图书状态:  " + sd[6];
					ds = ds + dd[in];// "\n\n"+
					b = true;
					in++;

					// System.out.println(ds);

				}
			}
			c.close();
			s.close();
			results.close();
		} catch (Exception e) {
			// System.out.println("ERROR");
			// System.out.println(e.getMessage());
		} // if(!ds.equals(""))
		// {
		// ds=ds+"\n\n**********************";
		// }
		return ds;

	}


	public static boolean n() {
		return b;
	}

	/* 查询读者信息 */
	public static String du(String ts,int ww) {
		String tri = " ";
		String qw = null;
		String dd = "";
		String dbUrl = "jdbc:odbc:db1";
		String sa = null;
		String user = "";
		String password = "";
		Statement s;
		ResultSet results;
		String tss=ts.substring(6);
		try {

			Connection c = LinkToDB.buildlinktodb();
			s = c.createStatement();
			String df = "SELECT * FROM 读者";
			System.out.println(tss);
					
			results = s.executeQuery(df);
			while (results.next()) {
			
				
			sdz[0]=results.getString("READERID读者识别号");
			sdz[1]=results.getString("NAME读者姓名");
			sdz[2]=results.getString("LEVEL读者权限");
			sdz[3]=results.getString("ADDRESS读者联系方法");
			sdz[4]=results.getString("TELEPHONE读者号码");
			sdz[5]=results.getString("EMAIL读者邮箱");;
			if (tss.equals(sdz[ww])){
			
				qw =sdz[0] + "读者姓名:  " + sdz[1] + " 读者权限:  "
				+ sdz[2] + " 联系方式:   " + sdz[3] + " 号码:  " + sdz[4]
				+ " 邮箱:  " + sdz[5] ;
			
			tri = tri + qw;//System.out.println(tri);
			}}
			c.close();
			s.close();
			results.close();
		} catch (Exception e) {
			// System.out.println("ERROR");
			// System.out.println(e.getMessage());
		}
		return tri;
	}
	public static String xitongdu(String ts,int ww) {
		String tri = "";
		String qw = null;
		String dd = "";
		String dbUrl = "jdbc:odbc:db1";
		String sa = null;
		String user = "";
		String password = "";
		Statement s;
		ResultSet results;
		try {

			Connection c = LinkToDB.buildlinktodb();
			s = c.createStatement();
			String df = "SELECT * FROM 读者";
			
					
			results = s.executeQuery(df);
			while (results.next()) {
			
				
			sdz[0]=results.getString("READERID读者识别号");
			sdz[1]=results.getString("NAME读者姓名");
			sdz[2]=results.getString("LEVEL读者权限");
			sdz[3]=results.getString("ADDRESS读者联系方法");
			sdz[4]=results.getString("TELEPHONE读者号码");
			sdz[5]=results.getString("EMAIL读者邮箱");;
			if (ts.equals(sdz[ww])){
			
				qw =sdz[0] + "读者姓名:  " + sdz[1] + " 读者权限:  "
				+ sdz[2] + " 联系方式:   " + sdz[3] + " 号码:  " + sdz[4]
				+ " 邮箱:  " + sdz[5] ;
			
			tri = tri + qw;//System.out.println(tri);
			}}
			c.close();
			s.close();
			results.close();
		} catch (Exception e) {
			// System.out.println("ERROR");
			// System.out.println(e.getMessage());
		}
		return tri;
	}


	public void changepass(Connection t, String az, String ay, String ax,
			String aw) throws ClassNotFoundException, SQLException {

		Connection con = t;
		Statement state1 = con.createStatement();
		ResultSet result1 = state1
				.executeQuery("SELECT Password FROM 管理员 WHERE WorkerId='" + az
						+ "'");

		while (result1.next()) {
			String x = result1.getString("Password");
			if (x.equals(ay)&&ax.equals(aw)) {
				
					Statement state2 = con.createStatement();
					boolean result2 = state2
							.execute("UPDATE 管理员 SET Password='" + ax
									+ "'WHERE WorkerId='" + az + "'");

				}
			

		}

	}
}