SourcePost_______

このページのコンテンツ一覧（５個位までにしてください。上が最新で下が最古です）

宿題スレPart59 >>838への回答　図書館管理（利用者登録） †

JDK6.0, MySQL 5.0.30

	-- SQL
	--drop database Library;
	create database Library;
	use Library
	create table Book( ISBN varchar(30) primary key, author varchar(100) not null, title varchar(100) not null );
	create table BookStock( ID int, ISBN varchar(30) not null ,seq int not null, PRIMARY KEY (ID), CONSTRAINT Book_BookStock FOREIGN KEY(ISBN) REFERENCES Book(ISBN) ON DELETE CASCADE ON UPDATE CASCADE);
	create table UserGroup ( ID int, name varchar(20) not null, PRIMARY KEY(ID) );
	create table User( ID varchar(20), password varchar(100) not null, name varchar(100) not null, email varchar(100), groupID int, PRIMARY KEY(ID),
	 CONSTRAINT UserGroup_User FOREIGN KEY(groupID) REFERENCES UserGroup(ID) ON DELETE CASCADE ON UPDATE CASCADE );
	create table RentHistory( ID int, libraryUserID varchar(20), bookStockID int not null, rentDate Date, planedReturnDate Date, actualReturnDate Date, PRIMARY KEY(ID),
	 CONSTRAINT User_RentHistory FOREIGN KEY(libraryUserID) REFERENCES User(ID) ON DELETE CASCADE ON UPDATE CASCADE,
	 CONSTRAINT BookStock_RentHistory FOREIGN KEY(bookStockID) REFERENCES BookStock(ID)  ON DELETE CASCADE ON UPDATE CASCADE);
	 
	insert into Book values("ISBN111-1-1111-1111-1", "authorA", "titleA");
	insert into Book values("ISBN222-2-2222-2222-2", "authorB", "titleB");
	insert into Book values("ISBN333-3-3333-3333-3", "authorC", "titleC");
	insert into BookStock values(11, "ISBN111-1-1111-1111-1", 1);
	insert into BookStock values(12, "ISBN111-1-1111-1111-1", 2);
	insert into BookStock values(13, "ISBN111-1-1111-1111-1", 3);
	insert into BookStock values(21, "ISBN222-2-2222-2222-2", 1);
	insert into BookStock values(22, "ISBN222-2-2222-2222-2", 2);
	insert into BookStock values(31, "ISBN333-3-3333-3333-3", 1);
	insert into UserGroup values(1, "General User");
	insert into UserGroup values(2, "Librarian");
	insert into User values("suzuki", password("suzuki"), "Suzuki", "suzuki@on.the.net", 1);
	insert into User values("tanaka", password("tanaka"), "Tanaka", "tanaka@on.the.net", 1);
	insert into User values("sato", password("sato"), "Sato", "sato@on.the.net", 1);
	insert into User values("kimura", password("kimura"), "Kimura", "kimura@on.the.net", 2);

	// DBConnect.java
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	
	public class DBConnect {
	
		static Connection con = null;
		static Statement stmt = null;
		
		public static void common() throws ClassNotFoundException, SQLException {
			Class.forName("org.gjt.mm.mysql.Driver");
			String url = "jdbc:mysql:///Library?useUnicode=true&characterEncoding=SJIS";
			con = DriverManager.getConnection(url, "user", "pass");
			stmt = con.createStatement();
		}
		
		public static ResultSet executeQuery(String sql) throws ClassNotFoundException, SQLException {
			common();
			return stmt.executeQuery(sql);			
		}
		
		public static int executeUpdate(String sql) throws ClassNotFoundException, SQLException {
			common();
			return stmt.executeUpdate(sql);			
		}
	}

	// LibraryApplet.java
	import javax.swing.JApplet;
	
	public class LibraryApplet extends JApplet {
		private LibraryMenuFrame frame;
		
		public void init() {
			frame = new LibraryMenuFrame();
			frame.setTitle("Menu");
			frame.setSize(300, 300);
			frame.setVisible(true);
			frame.setDefaultCloseOperation( LibraryMenuFrame.DISPOSE_ON_CLOSE );
		}
	}

	// LibraryMenuFrame.java
	import java.awt.FlowLayout;
	import java.awt.event.ActionEvent;
	import java.awt.event.ActionListener;
	
	import javax.swing.JButton;
	import javax.swing.JFrame;
	
	public class LibraryMenuFrame extends JFrame implements ActionListener {
		private JButton registUserButton, registBookButton, checkRentButton;
		
		public LibraryMenuFrame() {
			registUserButton = new JButton("Resist User");
			registBookButton = new JButton("Resist Book");
			checkRentButton = new JButton("Check Rental");
			registBookButton.setEnabled(false);
			checkRentButton.setEnabled(false);
			registUserButton.addActionListener(this);
			registBookButton.addActionListener(this);
			checkRentButton.addActionListener(this);
			this.setLayout( new FlowLayout() );
			add(registUserButton);
			add(registBookButton);
			add(checkRentButton);
		}
	
		public void actionPerformed(ActionEvent e) {
			if( e.getSource() == registUserButton ) {
				LibraryRegistUserFrame nextFrame = new LibraryRegistUserFrame();
				nextFrame.setTitle("Regist User");
				nextFrame.setSize(300, 150);
				nextFrame.setVisible(true);
				nextFrame.setDefaultCloseOperation(LibraryRegistUserFrame.DISPOSE_ON_CLOSE);
				this.setVisible(false);
	//		} else if( e.getSource() == registBookButton ) {
	//			LibraryRegistBookFrame nextFrame = new LibraryRegistBookFrame();
	//			nextFrame.setTitle("Regist Book");
	//			nextFrame.setSize(300, 300);
	//			nextFrame.setVisible(true);
	//			nextFrame.setDefaultCloseOperation(LibraryRegistUserFrame.DISPOSE_ON_CLOSE);
	//			this.setVisible(false);			
	//		} else if( e.getSource() == checkRentButton ) {
	//			LibraryCheckRentFrame nextFrame = new LibraryCheckRentFrame();
	//			nextFrame.setTitle("Check Rental");
	//			nextFrame.setSize(300, 300);
	//			nextFrame.setVisible(true);
	//			nextFrame.setDefaultCloseOperation(LibraryRegistUserFrame.DISPOSE_ON_CLOSE);
	//			this.setVisible(false);
			}
		}
	}

	// LibraryRegistUserFrame.java
	import java.awt.GridLayout;
	import java.awt.event.ActionEvent;
	import java.awt.event.ActionListener;
	
	import javax.swing.JButton;
	import javax.swing.JComboBox;
	import javax.swing.JFrame;
	import javax.swing.JLabel;
	import javax.swing.JOptionPane;
	import javax.swing.JPasswordField;
	import javax.swing.JTextField;
	
	public class LibraryRegistUserFrame extends JFrame implements ActionListener {
		private JTextField idField, nameField, emailField;
		private JPasswordField passField;
		private JComboBox groupComboBox;
		private JButton backButton, registButton;
		
		public LibraryRegistUserFrame() {
			idField = new JTextField();
			passField = new JPasswordField();
			nameField = new JTextField();
			emailField = new JTextField();
			groupComboBox = new JComboBox();
			backButton = new JButton("Back");
			registButton = new JButton("Regist");
			backButton.addActionListener(this);
			registButton.addActionListener(this);
			addGroups();
			this.setLayout( new GridLayout(6, 2) );
			add( new JLabel("id") );
			add( idField );
			add( new JLabel("password") );
			add( passField );
			add( new JLabel("name") );
			add( nameField );
			add( new JLabel("email") );
			add( emailField );
			add( new JLabel("group") );
			add( groupComboBox );
			add( backButton );
			add( registButton );
		}
		
		private void addGroups() {
			groupComboBox.removeAll();
			UserGroup[] groups = UserGroup.getAllUserGroups();
			for( UserGroup group : groups ) {
				groupComboBox.addItem(group.getName());
			}
		}
	
		public void actionPerformed(ActionEvent e) {
			if( e.getSource() == backButton ) {
				LibraryMenuFrame frame = new LibraryMenuFrame();
				frame.setTitle("Menu");
				frame.setSize(300, 300);
				frame.setVisible(true);
				this.setVisible(false);
			} else if( e.getSource() == registButton ) {
				String id =  idField.getText() ;
				String password = new String(passField.getPassword());
				String name = nameField.getText();
				String email = emailField.getText();
				String groupName = (String)groupComboBox.getSelectedItem();
				UserGroup group = new UserGroup(groupName);
				User user = null;
				try {
					user = new User(id, password, name, email, group);
				} catch( IllegalArgumentException iae ) {
					JOptionPane.showMessageDialog(this, iae.getMessage());
					return;
				}
				String  errorMessage = user.insert();
				if( errorMessage == null ) {
					JOptionPane.showMessageDialog(this, "Successfully inserted!");
				} else {
					JOptionPane.showMessageDialog(this, errorMessage);
				}
			}
		}
	}

	// User.java
	import java.sql.SQLException;
	
	public class User {
		private String id;
		private String password;
		private String name;
		private String email;
		private UserGroup group;
		
		public User( String id, String password, String name, String email, UserGroup group ) throws IllegalArgumentException {
			if( id == null || id.length() == 0 || password == null || password.length() == 0 || name == null || name.length() == 0 || group == null ) {
				throw new IllegalArgumentException("some of arguments are invalid.");
			}
			this.id = id;
			this.password = password;
			this.name = name;
			this.email = email;
			this.group = group;
		}
	
		public String getEmail() {
			return email;
		}
	
		public UserGroup getGroup() {
			return group;
		}
	
		public String getId() {
			return id;
		}
	
		public String getName() {
			return name;
		}
	
		public String getPassword() {
			return password;
		}
	
		public String insert() {
			String errorMessage = null;
			try {
				DBConnect.executeUpdate("INSERT INTO User VALUES ( '" + id + "', password('" + password + "'), '" + name + "', '" + email + "', " + group.getId() + ")");
			} catch (ClassNotFoundException e) {
	//			e.printStackTrace();
				errorMessage = e.getMessage();
			} catch (SQLException e) {
	//			e.printStackTrace();
				errorMessage = e.getMessage();
			} finally {
				if( DBConnect.stmt != null ) try { DBConnect.stmt.close(); } catch( SQLException e ) {}
				if( DBConnect.con != null ) try { DBConnect.con.close(); } catch( SQLException e ) {}
			}
			return errorMessage;
		}
	}

	// UserGroup.java
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.util.ArrayList;
	
	public class UserGroup {
		private int id;
		private String name;
	
		public UserGroup(String name) {
			try {
				ResultSet rs = DBConnect.executeQuery("SELECT id FROM UserGroup where name = '" + name + "'");
				while(rs.next()) {
					int id = rs.getInt("ID");
					this.id = id;
					this.name = name;
				}
				
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if( DBConnect.stmt != null ) try { DBConnect.stmt.close(); } catch( SQLException e ) {}
				if( DBConnect.con != null ) try { DBConnect.con.close(); } catch( SQLException e ) {}
			}
		}
		
		public UserGroup(int id, String name) throws IllegalArgumentException {
			if( name == null || name.length() == 0 ) {
				throw new IllegalArgumentException("some of arguments are invalid.");
			}
			this.id = id;
			this.name = name;
		}
	
		public static UserGroup[] getAllUserGroups() {
			ArrayList<UserGroup> result = new ArrayList<UserGroup>();
			
			try {
				ResultSet rs = DBConnect.executeQuery("SELECT * FROM UserGroup");
				while (rs.next()) {
					int id = rs.getInt("ID");
					String name = rs.getString("name");
					result.add(new UserGroup(id, name));
				}
			} catch( ClassNotFoundException e ) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if( DBConnect.stmt != null ) try { DBConnect.stmt.close(); } catch( SQLException e ) {}
				if( DBConnect.con != null ) try { DBConnect.con.close(); } catch( SQLException e ) {}
			}
			return result.toArray(new UserGroup[0]);
		}
	
		public int getId() {
			return id;
		}
	
		public String getName() {
			return name;
		}
	}