java图书管理系统实训报告.doc

北京联合大学实验（实习、实训）报告 课程（项目）名称： 面向对象程序设计 学 院： 师范学院 专 业：计算机科学与技术 班 级： 11计本1 学 号： 2011020332129 姓 名： 张有金 成 绩： 2012 年12月27日一、任务与目的1.Mysql建库、建表、添、删、改、查等操作及Sql语句。2.Java与MYSql的JDBC连接；Java连接程序编写、JavaBean编写，数据库操作程序的编写。3.Java图形界面的编写，面向对象程序设计（抽象、封装、继承、多态）的思想二、功能模块的设计1数据库表设计Mydata（数据库：图书）：1、Survey（信息检索）SurveyIdSurveyNameSurveyFamilyNumberSurveySalary整型字符串字符串字符串 CREATE TABLE Survey ( SurveyId INTEGER AUTO_INCREMENT, SurveyName VARCHAR(50) NOT NULL, SurveyFamilyNumber VARCHAR(50) NOT NULL, SurveySalary VARCHAR(50) NOT NULL, PRIMARY KEY (SurveyId) );2、Card（会员卡）CardIdCardNameCardNumber整型字符串字符串CREATE TABLE Card ( CardId INTEGER AUTO_INCREMENT, CardName VARCHAR(50) NOT NULL, CardNumber VARCHAR(50) NOT NULL, PRIMARY KEY (CardId) ); 3、Book（书籍信息）BookIdBookNameBookPrice整型字符串字符串CREATE TABLE Book ( BookId INTEGER AUTO_INCREMENT, BookName VARCHAR(50) NOT NULL, BookPrice VARCHAR(50) NOT NULL, PRIMARY KEY (BookId) );2java数据库连接和添、删、改、查等操作的设计Java访问数据库过程如下： 装载数据库驱动； 通过JDBC建立数据库连接； 访问数据库，执行SQL语句； 断开数据库连接；数据库连接package DB;import java.sql.*;import javax.swing.JOptionPane;/数据库连接类MySqlConnnection。通过调用类的静态方法获得数据库连接。public abstract class MySqlConnnection /私有空构造方法,保证本类不能够被实例化。 private MySqlConnnection() /获得数据库连接public static Connection getConnection() Connection conn=null;try /加载MySQL JDBC 驱动程序名称Class.forName(org.gjt.mm.mysql.Driver);/数据库连接参数。String serverName = localhost; / 数据库主机名称String mydatabase = mydata; / 数据库名称String url = jdbc:mysql:/ + serverName + / + mydatabase;String username = root; /MySql用户名String password = 111111; /MySql密码conn = DriverManager.getConnection(url, username, password);/建立连接 catch (ClassNotFoundException e) e.printStackTrace();/找不到MySql驱动程序类时，打印异常 catch (SQLException e) e.printStackTrace();/获得数据库连接发生异常return conn;/关闭数据库连接public static void closeConnection(Connection conn) if (conn != null) /连接是否有效try if (!conn.isClosed() /连接是否已关闭conn.close(); /关闭连接 catch (SQLException ex1) ex1.printStackTrace();/测试连接类public static void main(String args) throws ExceptionConnection conn = MySqlConnnection.getConnection();String result=;if (conn = null) result=获得数据库连接错误.;else result=正常获得数据库连接. + conn;JOptionPane.showMessageDialog(null, result);1、DBBook/增加public static void addBook(Book a) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(INSERT INTO Book (BookName,BookPrice) VALUES (?,?);ps.setString(1, MyEncode.GBtoISO(a.getBookName();ps.setString(2, a.getBookPrice();ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/删除public static void deleteBook(int id) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(DELETE FROM Book WHERE BookId=?);ps.setInt(1, id); ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/修改public static void updateBook(Book a) Connection conn = null;try conn = MySqlConnnection.getConnection();String Sql = UPDATE Book SET BookName=?,BookPrice=? WHERE BookId =?;PreparedStatement ps = conn.prepareStatement(Sql);ps.setString(1, MyEncode.GBtoISO(a.getBookName();ps.setString(2, a.getBookPrice();ps.setInt(3, a.getBookId();ps.executeUpdate(); ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/查询public static Book getBook(int id) Book a = null;Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(SELECT * FROM Book WHERE BookId=?);ps.setInt(1, id); ResultSet rs = ps.executeQuery(); if (rs.next() a = new Book(rs.getInt(1), MyEncode.ISOtoGB(rs.getString(2),rs.getString(3);ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return a;/查询所有记录public static Iterator getAllBook() List l = new ArrayList();Connection conn = null;try conn = MySqlConnnection.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SELECT * FROM Book);String s = null;while (rs.next() s = MyEncode.ISOtoGB(rs.getString(2);l.add(new Book(rs.getInt(1), s, rs.getString(3);stmt.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return l.iterator();public static void main(String args) 2、DBCard/增加会员卡public static void addCard(Card a) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(INSERT INTO Card (CardName,CardNumber) VALUES (?,?);ps.setString(1, MyEncode.GBtoISO(a.getCardName();ps.setString(2, a.getCardNumber();ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/删除会员卡public static void deleteCard(int id) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(DELETE FROM Card WHERE CardId=?);ps.setInt(1, id); ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/修改会员卡public static void updateCard(Card a) Connection conn = null;try conn = MySqlConnnection.getConnection();String Sql = UPDATE Card SET CardName=?,CardNumber=? WHERE CardId =?;PreparedStatement ps = conn.prepareStatement(Sql);ps.setString(1, MyEncode.GBtoISO(a.getCardName();ps.setString(2, a.getCardNumber();ps.setInt(3, a.getCardId();ps.executeUpdate(); ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/查询会员卡public static Card getCard(int id) Card a = null;Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(SELECT * FROM Card WHERE CardId=?);ps.setInt(1, id); ResultSet rs = ps.executeQuery(); if (rs.next() a = new Card(rs.getInt(1), MyEncode.ISOtoGB(rs.getString(2),rs.getString(3);ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return a;/查询所有会员卡public static Iterator getAllCard() List l = new ArrayList();Connection conn = null;try conn = MySqlConnnection.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SELECT * FROM Card);String s = null;while (rs.next() s = MyEncode.ISOtoGB(rs.getString(2);l.add(new Card(rs.getInt(1), s, rs.getString(3);stmt.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return l.iterator();public static void main(String args) 3、DBSurvey/增加public static void addSurvey(Survey a) Connection conn = null;try conn = MySqlConnnection.getConnection(); PreparedStatement ps = conn.prepareStatement(INSERT INTO Survey (SurveyName,SurveyFamilyNumber,SurveySalary) VALUES (?,?,?);ps.setString(1, MyEncode.GBtoISO(a.getSurveyName();ps.setString(2, a.getSurveyFamilyNumber();ps.setString(3, a.getSurveySalary();ps.executeUpdate(); ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn); /删除public static void deleteSurvey(int id) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(DELETE FROM Survey WHERE SurveyId=?);ps.setInt(1, id); ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/修改public static void updateSurvey(Survey a) Connection conn = null;try conn = MySqlConnnection.getConnection();String Sql = UPDATE Survey SET SurveyName=?,SurveyFamilyNumber =? ,SurveySalary=? WHERE SurveyId =?;PreparedStatement ps = conn.prepareStatement(Sql);ps.setString(1, MyEncode.GBtoISO(a.getSurveyName();ps.setString(2, a.getSurveyFamilyNumber();ps.setString(3, a.getSurveySalary();ps.setInt(4, a.getSurveyId();ps.executeUpdate(); ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/查询public static Survey getSurvey(int id) Survey a = null;Connection conn = null;try conn = MySqlConnnection.getConnection();/ 获得数据连接/ 建立PreparedStatement用于执行SQL操作PreparedStatement ps = conn.prepareStatement(SELECT * FROM Survey WHERE SurveyId=?);ps.setInt(1, id); / 设置第一个占位符的内容ResultSet rs = ps.executeQuery(); / 执行查询，返回结果集if (rs.next() / 因为管理员id是惟一的，所以只返回一个结果既可a = new Survey(rs.getInt(1), MyEncode.ISOtoGB(rs.getString(2),rs.getString(3),rs.getString(4);ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return a;/查询所有public static Iterator getAllSurvey() List l = new ArrayList();Connection conn = null;try conn = MySqlConnnection.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SELECT * FROM Survey);String s = null;while (rs.next() s = MyEncode.ISOtoGB(rs.getString(2);l.add(new Survey(rs.getInt(1), s, rs.getString(3),rs.getString(4);stmt.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return l.iterator();/返回迭代器对象public static void main(String args) 3图形界面的设计1：登陆界面 Java图书大厦管理系统，首页提示“请输入登录密码”界面 密码输入正确后进入系统，否则提示“密码不对，登录失败”。三、功能模块的编码实现一、Java和Mysql连接程序编码实现 Java 和Mysql连接程序编码实现Connect连接是通过MySqlConnnection 的静态方法getConnection()来得到的，这个方法的实质是把参数传到实际的Mysql中的Connect方法中来获得数据库连接的。 当整个数据操作完毕后要用connection.close方法来关闭连接二、ZYJbaea模块 本模块分三个小模块，分别为Book模块、Card模块、Survey模块。定义每个模块的各个变量的类型。源代码（1） Book package ZYJbean; public class Book private int BookId; private String BookName; private String BookPrice; public Book( ) public int getBookId() return BookId; public void setBookId(int bookId) BookId = bookId; public String getBookName() return BookName; public void setBookName(String bookName) BookName = bookName; public String getBookPrice() return BookPrice; public void setBookPrice(String bookPrice) BookPrice = bookPrice; public Book(int bookId, String bookName, String bookPrice) super(); BookId = bookId; BookName = bookName; BookPrice = bookPrice; （2） Card package ZYJbean; public class Card private int cardId; private String cardName; private String cardNumber; public Card( ) public Card(int cardId, String cardName, String cardNumber) super(); this.cardId = cardId; this.cardName = cardName; this.cardNumber = cardNumber; public int getCardId() return cardId; public void setCardId(int cardId) this.cardId = cardId; public String getCardName() return cardName; public void setCardName(String cardName) this.cardName = cardName; public String getCardNumber() return cardNumber; public void setCardNumber(String cardNumber) this.cardNumber = cardNumber; （3） Survey package ZYJbean; public class Survey private int SurveyId; private String SurveyName; private String SurveyNumber; private String SurveySalary; private String SurveyFamilyNumber; public Survey( ) public Survey(int SurveyId, String SurveyName, String SurveyFamilyNumber, String SurveySalary) super(); this.SurveyId = SurveyId; this.SurveyName = SurveyName; this.SurveyFamilyNumber = SurveyFamilyNumber; this.SurveySalary = SurveySalary; public int getSurveyId() return SurveyId; public void setSurveyId(int SurveyId) this.SurveyId = SurveyId; public String getSurveyName() return SurveyName; public void setSurveyName(String SurveyName) this.SurveyName = SurveyName; public String getSurveyFamilyNumber() return SurveyFamilyNumber; public void setSurveyFamilyNumber(String SurveyFamilyNumber) this.SurveyFamilyNumber = SurveyFamilyNumber; public String getSurveySalary() return SurveySalary; public void setSurveySalary(String SurveySalary) this.SurveySalary = SurveySalary; 二、DB模块 本模块分别表示添加,删除，修改，查询的记录的语句。1、 DBBookpackage DB;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import ZYJbean.*;public abstract class DBBook private DBBook() /增加public static void addBook(Book a) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(INSERT INTO Book (BookName,BookPrice) VALUES (?,?);ps.setString(1, MyEncode.GBtoISO(a.getBookName();ps.setString(2, a.getBookPrice();ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/删除public static void deleteBook(int id) Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(DELETE FROM Book WHERE BookId=?);ps.setInt(1, id); ps.executeUpdate();ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/修改public static void updateBook(Book a) Connection conn = null;try conn = MySqlConnnection.getConnection();String Sql = UPDATE Book SET BookName=?,BookPrice=? WHERE BookId =?;PreparedStatement ps = conn.prepareStatement(Sql);ps.setString(1, MyEncode.GBtoISO(a.getBookName();ps.setString(2, a.getBookPrice();ps.setInt(3, a.getBookId();ps.executeUpdate(); ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);/查询public static Book getBook(int id) Book a = null;Connection conn = null;try conn = MySqlConnnection.getConnection();PreparedStatement ps = conn.prepareStatement(SELECT * FROM Book WHERE BookId=?);ps.setInt(1, id); ResultSet rs = ps.executeQuery(); if (rs.next() a = new Book(rs.getInt(1), MyEncode.ISOtoGB(rs.getString(2),rs.getString(3);ps.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return a;/查询所有记录public static Iterator getAllBook() List l = new ArrayList();Connection conn = null;try conn = MySqlConnnection.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(SELECT * FROM Book);String s = null;while (rs.next() s = MyEncode.ISOtoGB(rs.getString(2);l.add(new Book(rs.getInt(1), s, rs.getString(3);stmt.close(); catch (Exception ex) ex.printStackTrace(); finally MySqlConnnection.closeConnection(conn);return l.iterator();public static void main(String args) 2、 DBCardpackage DB;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import ZYJbean.Card;import ZYJbean.Survey;public abstract class DBCard private DBCard() /增