书店交易信息简单管理模型

Books类：对应数据库中的books表格

public class Books {
	private String bookid;
	private String bookname;
	private  float bookprice;//单价
	public String getBookid() {
		return bookid;
	}
	public void setBookid(String bookid) {
		this.bookid = bookid;
	}
	public String getBookname() {
		return bookname;
	}
	public void setBookname(String bookname) {
		this.bookname = bookname;
	}
	public float getBookprice() {
		return bookprice;
	}
	public void setBookprice(float bookprice) {
		this.bookprice = bookprice;
	}
	
	
}

Customer类：对应数据库中的customer类

public class Customer {

	private String customerid;
	private String customername;
	private float money_amount;//余额
	
	public String getCustomerid() {
		return customerid;
	}
	public void setCustomerid(String customerid) {
		this.customerid = customerid;
	}
	public String getCustomername() {
		return customername;
	}
	public void setCustomername(String customername) {
		this.customername = customername;
	}
	public float getMoney_amount() {
		return money_amount;
	}
	public void setMoney_amount(float money_amount) {
		this.money_amount = money_amount;
	}
	
	
}

Inventory类：对应数据库中的库存表

public class Inventory {
	
	private String bookid;
	private int book_amount;//剩余书本数量
	public String getBookid() {
		return bookid;
	}
	public void setBookid(String bookid) {
		this.bookid = bookid;
	}
	public int getBook_amount() {
		return book_amount;
	}
	public void setBook_amount(int book_amount) {
		this.book_amount = book_amount;
	}
	

}

Deating类：对应数据库中的交易表

import java.util.Date;
/**
 * 交易表类
 * @author dingshuangen
 *
 */
public class Dealing {

	private String bookid;
	private String customerid;
	private int sal_amount;
	private float money;
	public float getMoney() {
		return money;
	}
	public void setMoney(float money) {
		this.money = money;
	}
	private Date sal_date;//交易时间
	
	public String getBookid() {
		return bookid;
	}
	public void setBookid(String bookid) {
		this.bookid = bookid;
	}
	public String getCustomerid() {
		return customerid;
	}
	public void setCustomerid(String customerid) {
		this.customerid = customerid;
	}
	public float getSal_amount() {
		return sal_amount;
	}
	public void setSal_amount(int sal_amount) {
		this.sal_amount = sal_amount;
	}
	public Date getSal_date() {
		return sal_date;
	}
	public void setSal_date(Date sal_date) {
		this.sal_date = sal_date;
	}
	@Override
	public String toString() {
		return "Dealing [bookid=" + bookid + ", customerid=" + customerid + ", 购买数量=" + sal_amount + ", 销售金额="
				+ money + ", 交易日期=" + sal_date + "]";
	}

}

BookDao类：对数据库中所有表格进行操作的方法类

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
	/**
	 * 查询所有书本的剩余信息
	 * @return
	 */
	public List<String> selectAll() {
		ArrayList<String> list=new ArrayList<String>();
		String sql="select * from books b join inventory i on b.bookid=i.bookid";
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				list.add(rs.getString("bookid")+"\t"+rs.getString("bookname")+"\t剩余："+rs.getInt("book_amount"));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询具体某一本书的剩余信息
	 * @param b
	 * @return
	 */
	public List<String> select(Books b) {
		
		ArrayList<String> list=new ArrayList<String>();
		String sql="select * from books b join inventory i on b.bookid=i.bookid and b.bookid=?";
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, b.getBookid());
			rs=ps.executeQuery();
			while(rs.next()) {
				list.add(rs.getString("bookid")+"\t"+rs.getString("bookname")+"\t剩余："+rs.getInt("book_amount"));
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	/**
	 * 销售书籍
	 * @param c买书的客户
	 * @param b购买的书
	 * @param 购买数量
	 */
	public void salBook(Customer c,Books b,Inventory i,int num) {
		Connection conn=JdbcUtil.getConnection();
		
		PreparedStatement ps1=null;
		PreparedStatement ps2=null;
		PreparedStatement ps3=null;
		try {
			
			conn.setAutoCommit(false);//设置为不自动提交事务
			
			//客户余额不足时抛出异常提示余额不足
			if(c.getMoney_amount()<b.getBookprice()*num) {
				System.out.println("交易失败");
				throw new Exception("余额不足");
			}
			//对客户信息进行修改的sql语句
			String sql1="update customer set money_amount=? where customerid=?";
			ps1=conn.prepareStatement(sql1);
			//更新用户个人信息
			c.setMoney_amount(c.getMoney_amount()-num*b.getBookprice());
			ps1.setFloat(1, c.getMoney_amount());
			ps1.setString(2, c.getCustomerid());
			ps1.execute();
			
			//对书籍库存是否满足条件进行判断
			if(i.getBook_amount()<num) {
				System.out.println("交易失败");
				throw new Exception("书籍库存不足");
			}
			//对库存信息进行修改的sql语句
			String sql2="update inventory set book_amount= ? where bookid=?";
			//更新库存信息
			ps2=conn.prepareStatement(sql2);
			i.setBook_amount(i.getBook_amount()-num);
			ps2.setInt(1,i.getBook_amount());
			ps2.setString(2, b.getBookid());
			ps2.execute();
			
			//对交易信息进行记录的sql语句
			String sql3="insert into dealing values(?,?,?,?,?)";
			//记录交易信息
			ps3=conn.prepareStatement(sql3);
			ps3.setString(1, b.getBookid());
			ps3.setString(2, c.getCustomerid());
			ps3.setInt(3, num);
			ps3.setFloat(4, num*b.getBookprice());
			ps3.setDate(5, new Date(System.currentTimeMillis()));
			ps3.execute();
			//提交所有数据信息
			conn.commit();
			System.out.println("交易成功");
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			//关闭所有资源
			try {
				ps1.close();
				ps2.close();
				ps3.close();
			} catch (SQLException e) {
				System.err.println("资源关闭异常");
			}
		}
	}
	/**
	 * 查询交易信息
	 * @return返回值为一个Dealing类型的List集合
	 */
	public List<Dealing> selectDealing(){
		
		String sql="select * from dealing";
		
		ArrayList<Dealing> list=new ArrayList<Dealing>();
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Dealing d=new Dealing();
				d.setBookid(rs.getString("bookid"));
				d.setCustomerid(rs.getString("customerid"));
				d.setSal_amount(rs.getInt("sal_amount"));
				d.setMoney(rs.getFloat("money"));
				d.setSal_date(rs.getDate("sal_date"));
				list.add(d);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 根据书本id获得书的信息
	 * @param bookid
	 * @return
	 */
	public Books getBooks(String bookid) {
		
		String sql="select * from books where bookid=?";
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		Books b=new Books();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, bookid);
			rs=ps.executeQuery();
			while(rs.next()) {
				b.setBookid(rs.getString("bookid"));
				b.setBookname(rs.getString("bookname"));
				b.setBookprice(rs.getFloat("bookprice"));
			}
			return b;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	/**
	 * 根据顾客id获得顾客的信息
	 * @param customerid
	 * @return
	 */
	public Customer getCustomer(String customerid) {
		
		String sql="select * from customer where customerid=?";
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		Customer c=new Customer();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, customerid);
			rs=ps.executeQuery();
			while(rs.next()) {
				c.setCustomerid(rs.getString(1));
				c.setCustomername(rs.getString(2));
				c.setMoney_amount(rs.getFloat(3));
			}
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 获得当前booksid的库存信息
	 * @param bookid
	 * @return
	 */
	public Inventory getInventory(String bookid) {
		
		String sql="select * from inventory where bookid=?";
		Connection conn=JdbcUtil.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		Inventory in=new Inventory();
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, bookid);
			rs=ps.executeQuery();
			while(rs.next()) {
				in.setBookid(rs.getString(1));
				in.setBook_amount(rs.getInt(2));
			}
			return in;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}


}

JdbcUtil类：包括了获得连接的静态方法以及测试方法 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class JdbcUtil {
	
	private static final String URL="jdbc:mysql://localhost:3306/jdbc";
	private static final String USER="root";
	private static final String PASSWORD="0325";
	private static Connection conn=null;
	//静态块，只加载一次
	static {
	
			try {
				//加载驱动
				Class.forName("com.mysql.jdbc.Driver");
				//获得连接
				conn=DriverManager.getConnection(URL, USER, PASSWORD);
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
	}
	//获得连接的方法
	public static Connection getConnection() {
		return conn;
	}
	//测试方法
	public static void main(String[] args) {
		
		BookDao bd=new BookDao();
		//获得id为B02的书籍信息
		Books b=bd.getBooks("B01");
		//获得id为B02的书籍库存信息
		Inventory in=bd.getInventory("B01");
		//获得客户C02的信息
		Customer c=bd.getCustomer("C01");
		//查询编号为书籍b的库存信息
		List<String> list1=bd.select(b);
		//输出信息
		System.out.println("========当前查询书籍库存信息为：========");
		for (String string : list1) {
			System.out.println(string);
		}
		//查询出库存剩余的书本信息
		System.out.println("========当前所有书籍库存信息为：========");
		List<String> list=bd.selectAll();
		//输出信息
		for (String string : list) {
			System.out.println(string);
		}
		//查询客户余额
		System.out.println("###你的余额为："+c.getMoney_amount());
		//顾客C02买了5本编号为B01的书
		System.out.println("========交易状态：========");
		bd.salBook(c, b, in, 3);
		//查询交易信息
		System.out.println("\n\n========所有的交易信息为：========");
		List<Dealing> l=bd.selectDealing();
		for (Dealing de : l) {
			System.out.println(de.toString());
		}
		//查询交易后的库存信息
		System.out.println("\n\n========交易后所有书籍库存信息为：========");
		List<String> list2=bd.selectAll();
		for (String string : list2) {
			System.out.println(string);
		}
		System.out.println("###交易成功后你的余额为："+c.getMoney_amount());
		//所有操作执行完成后关闭连接
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}