数据库开发（十一 JDBC篇三）ORM和JDBC练习

ORM思想（了解）

O/R Mapping(Object Relational Mapping，对象/关系映射)

一种将内存中的对象保存到关系型数据库中的技术

Java对象<-->数据库映射

对象模型	关系模型
实体类(pojo)	表
实体类属性	列（字段）
OID	主键
实体类对象	记录
实体类之间的关联关系	外键

 

下面的映射中，并非一定要写成这样，只是ORM思想规定如此

• 一对一：

  • 映射到Java中

Person	Passport
id	id
name	...
password	person

• 一对多

  Order	OrderDetail
  List<OrderDetail>	Order

   

• 多对多

  Teacher	Student
  List<Student>	List<Teacher>

   

=================================

练习1

定义一个方法，

• 查询一个订单，及其对应的明细并返回
• 要求，返回订单对象，里面包含对应的明细集合对象

分步操作：

1. 定义一个OrderJDBC，

• 定义getOrder getOrder(long id){}

2. 定义一个OrderDetailJDBC

• 定义 List<OrderDetail> getDetails(long orderId){}

3.定义一个方法，

• 查询一个订单，及其对应的明细并返回
• 要求，返回订单对象，里面包含对应的明细集合对象

结果如下：

Order类：

package com.zhang.entity;

import java.sql.Date;
import java.util.List;

public class Order {
	/*
	 * id NUMBER(11),
	user_id NUMBER(11),
	total NUMBER(12,2),
	order_date DATE,
	name VARCHAR2(25),
	tel VARCHAR2(11),
	addr VARCHAR2(100),
	status NUMBER(3) DEFAULT 0,
	 */
	private long id;
	private long userId;
	private double total;
	//sql下的date是util下的date的子类
	private Date orderDate;
	private String name;
	private String tel;
	private String addr;
	private int status;
	private List<OrderDetail> detailList;

	public Order() {}

	public Order(long id, long userId, double total, Date orderDate, String name, String tel, String addr, int status) {
		super();
		this.id = id;
		this.userId = userId;
		this.total = total;
		this.orderDate = orderDate;
		this.name = name;
		this.tel = tel;
		this.addr = addr;
		this.status = status;
	}

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public long getUserId() {
		return userId;
	}

	public void setUserId(long userId) {
		this.userId = userId;
	}

	public double getTotal() {
		return total;
	}

	public void setTotal(double total) {
		this.total = total;
	}

	public Date getOrderDate() {
		return orderDate;
	}

	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}
	
	public List<OrderDetail> getDetailList() {
		return detailList;
	}

	public void setDetailList(List<OrderDetail> detailList) {
		this.detailList = detailList;
	}

	@Override
	public String toString() {
		return "Order [id=" + id + ", userId=" + userId + ", total=" + total + ", orderDate=" + orderDate + ", name="
				+ name + ", tel=" + tel + ", addr=" + addr + ", status=" + status + "]";
	}	
	
}

OrderDetail类：

package com.zhang.entity;

public class OrderDetail {
	/*
	 * id NUMBER(11),
	order_id NUMBER(11),
	product_id NUMBER(11),
	price NUMBER(11,2),
	quantity NUMBER(9),
	cost NUMBER(11,2),
	 */
	private long id;
	private long orderId;
	private long productId;
	private double price;
	private int quantity;
	private double cost;
	
	public OrderDetail() {}

	public OrderDetail(long id, long orderId, long productId, double price, int quantity, double cost) {
		super();
		this.id = id;
		this.orderId = orderId;
		this.productId = productId;
		this.price = price;
		this.quantity = quantity;
		this.cost = cost;
	}

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public long getOrderId() {
		return orderId;
	}

	public void setOrderId(long orderId) {
		this.orderId = orderId;
	}

	public long getProductId() {
		return productId;
	}

	public void setProductId(long productId) {
		this.productId = productId;
	}

	public double getPrice() {
		return price;
	}

	public void setPrice(double price) {
		this.price = price;
	}

	public int getQuantity() {
		return quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	public double getCost() {
		return cost;
	}

	public void setCost(double cost) {
		this.cost = cost;
	}

	@Override
	public String toString() {
		return "OrderDetail [id=" + id + ", orderId=" + orderId + ", productId=" + productId + ", price=" + price
				+ ", quantity=" + quantity + ", cost=" + cost + "]";
	}
	
	
}

OrderJDBC类：

package com.zhang.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zhang.util.ConnectionFactory;
import com.zhang.util.DBUtils;

public class OrderJDBC {
	public static void main(String[] args) {
		/*
		 * 该main方法仅用于测试下列方法是否正确
		 */
		OrderJDBC orderJDBC = new OrderJDBC();
		try {
			Order order = orderJDBC.getOrder(1);
			System.out.println(order);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//定义getOrder getOrder(long id){}
	public Order getOrder(long id) throws SQLException {
		Order order = null;
		Connection conn = ConnectionFactory.getConnection();
		String sql = "SELECT id,user_id,total,order_date,name,tel,addr,status FROM t_order" 
				+ " WHERE id=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, id);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			order = new Order(rs.getLong(1),rs.getLong(2),rs.getDouble(3),rs.getDate(4),
					rs.getString(5),rs.getString(6),rs.getString(7),rs.getInt(8));
		}
		DBUtils.close(rs,pstmt,conn);
		return order;
	}
}

OrderDetailJDBC类：

package com.zhang.jdbc;

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

import com.zhang.util.ConnectionFactory;
import com.zhang.util.DBUtils;

public class OrderDetailJDBC {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		/*
		 * 该main方法仅用于测试下列方法是否正确
		 */
		OrderDetailJDBC detailJDBC = new OrderDetailJDBC();
		try {
			List<OrderDetail> list = detailJDBC.getDetails(1);
			for(OrderDetail orderDetail:list) {
				System.out.println(orderDetail);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//定义 List<OrderDetail> getDetails(long orderId){}
	public List<OrderDetail> getDetails(long orderId) throws SQLException {
		List<OrderDetail> list = new ArrayList<>();
		Connection conn = ConnectionFactory.getConnection();
		String sql = "SELECT id,order_id,product_id,price,quantity,cost"
				+ " FROM t_order_detail WHERE order_id=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, orderId);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()) {
			OrderDetail detail = new OrderDetail(rs.getLong(1),rs.getLong(2),rs.getLong(3)
					,rs.getDouble(4),rs.getInt(5),rs.getDouble(6));
			list.add(detail);
		}
		DBUtils.close(rs,pstmt,conn);
		return list;
	}

}

TestOrder类：

package com.zhang.jdbc;

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

public class OrderTest {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		OrderTest ot = new OrderTest();
		Order order = ot.getOrderWithDetail(1);
		if(order!=null) {
			System.out.println(order);
			List<OrderDetail> list = order.getDetailList();
			for (OrderDetail orderDetail : list) {
				System.out.println("\t" + orderDetail);
			}
		}

	}
	
	public Order getOrderWithDetail(long id) {
		Order order = null;
		OrderJDBC orderJDBC = new OrderJDBC();
		OrderDetailJDBC detailJDBC = new OrderDetailJDBC();
		try {
			//查询订单
			order = orderJDBC.getOrder(id);
			//查询明细
			List<OrderDetail> detailList = detailJDBC.getDetails(id);
			//把明细设置到订单中
			order.setDetailList(detailList);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//返回订单
		return order;
	}

}

 

 

练习2

• 前提：一个表的操作写一个对应的类，比如用户操作UserJDBC
  • 使用封装好的工具类
• 1.添加一个用户到数据库
• 2.通过用户名查找用户
• 3.定义一个方法生成一个订单序列值，
  • SELECT t_order_id_seq.NEXTVAL FROM dual;
• 4.定义一个方法，添加一个订单记录到订单表
  • public int addOrder(Order order, long orderId) {}
• 5.定义一个方法，添加一个订单明细到明细表
  • public int addOrderDetail(OrderDetail detail){}
• 6.定义一个下单业务方法，类似于上面练习的第三步

  • public int takeOrder(Order order, List<orderDetail> detailList){
        //1.先调用方法3生成订单号
    
        //2.调用添加订单方法生成的订单
    
        //3.调用添加订单明细方法生成所有的明细
    }

     

结果展示

 

Order和OrderDetail类与上面练习中相同

User类

package com.zhang.entity;

public class User {
	/*
	 * id NUMBER(11),
	username VARCHAR2(25),
	password VARCHAR2(25),
	sex NUMBER(1) DEFAULT 0,
	id_number VARCHAR2(18),
	tel VARCHAR2(11),
	addr VARCHAR2(100),
	 */
	
	private long id;
	private String username;
	private String password;
	private int sex;
	private String idNumber;
	private String tel;
	private String addr;
	
	public User() {}

	public User(long id, String username, String password, int sex, String idNumber, String tel, String addr) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
		this.sex = sex;
		this.idNumber = idNumber;
		this.tel = tel;
		this.addr = addr;
	}

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public int getSex() {
		return sex;
	}

	public void setSex(int sex) {
		this.sex = sex;
	}

	public String getIdNumber() {
		return idNumber;
	}

	public void setIdNumber(String idNumber) {
		this.idNumber = idNumber;
	}

	public String getTel() {
		return tel;
	}

	public void setTel(String tel) {
		this.tel = tel;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + ", sex=" + sex + ", idNumber="
				+ idNumber + ", tel=" + tel + ", addr=" + addr + "]";
	}
	
	

}

 

UserJDBC类

package com.zhang.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zhang.entity.User;
import com.zhang.util.ConnectionFactory;
import com.zhang.util.DBUtils;

public class UserJDBC {
	
	/**
	 * 添加一个用户到用户表
	 * @param user：用户信息
	 * @return 影响的行数  1：添加成功   0：添加失败
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public int addUser(User user) throws ClassNotFoundException, SQLException {
		Connection conn = ConnectionFactory.getConnection();
		
		//第三步：创建可以执行SQL语句的PreparedStatement
		String sql = "INSERT INTO t_user(id,username,password,sex,id_number,tel,addr)" 
				+ "VALUES(t_user_id_seq.NEXTVAL,?,?,?,?,?,?)";
		PreparedStatement pstmt =conn.prepareStatement(sql);
		//第四步：执行SQL语句
		//先设置占位符的值
		pstmt.setString(1, user.getUsername());
		pstmt.setString(2, user.getPassword());
		pstmt.setInt(3, user.getSex());
		pstmt.setString(4, user.getIdNumber());
		pstmt.setString(5, user.getTel());
		pstmt.setString(6, user.getAddr());
		//执行
		int rows = pstmt.executeUpdate();
		
		DBUtils.close(pstmt,conn);
		return rows;
	}
	
	
	/**
	 * 通过用户名查找用户
	 * @param username：用户名
	 * @return 查找到的用户信息  null：没有对应的用户
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public User queryUser(String username) throws ClassNotFoundException, SQLException {
		User user = null;
		Connection conn = ConnectionFactory.getConnection();
		String sql = "SELECT id,username,password,sex," 
				+ "id_number,tel,addr FROM t_user WHERE username=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery(sql);
		if(rs.next()) {
			user = new User(rs.getLong(1),rs.getString(2),rs.getString(3),
					rs.getInt(4),rs.getString(5),rs.getString(6),rs.getString(7));
		}
		DBUtils.close(rs,pstmt,conn);
		return user;
	}

}

OrderJDBC类：

package com.zhang.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zhang.entity.Order;
import com.zhang.util.ConnectionFactory;
import com.zhang.util.DBUtils;

public class OrderJDBC {
	public static void main(String[] args ) {
        /*
		 * 该main方法仅用于测试下列方法是否正确
		 */
		OrderJDBC orderJDBC = new OrderJDBC();
		try {
			//System.out.println(orderJDBC.generateOrderId());
			
			
			long orderId = orderJDBC.generateOrderId();
			//System.currentTimeMillis() 当前系统毫秒数
			Order order = new Order(orderId, 3, 18, new Date(System.currentTimeMillis()), "aa", "111","111",0);
			int rows = orderJDBC.addOrder(order);
			System.out.println(rows);
			//可用SELECT to_char(order_date, 'yyyy-mm-dd hh24:mi:ss'), id FROM t_order;在数据库中查询结果
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 生成订单号
	 * @return 订单号
	 * @throws SQLException
	 */
	public long generateOrderId() throws SQLException {
		long orderId = 0;
		Connection conn = ConnectionFactory.getConnection();
		//此序列在sql数据库文件中已经定义
		String sql = "SELECT t_order_id_seq.NEXTVAL FROM dual";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()) {
			orderId = rs.getLong(1);
		}
		DBUtils.close(rs,pstmt, conn);
		return orderId;
	}
	
	
	/**
	 * 添加订单
	 * @param order:订单信息
	 * @return 1：添加成功   0：添加失败
	 * @throws SQLException
	 */
	public int addOrder(Order order) throws SQLException {
		Connection conn = ConnectionFactory.getConnection();
		/*
		 * id NUMBER(11),
	user_id NUMBER(11),
	total NUMBER(12,2),
	order_date DATE,
	name VARCHAR2(25),
	tel VARCHAR2(11),
	addr VARCHAR2(100),
	status NUMBER(3) DEFAULT 0,
		 */
		/*String sql = "INSERT INTO t_order (id,user_id,total,order_date,"
				+ "name,tel,addr,status) VALUES(?,?,?,?,?,?,?,?)";*/
		String sql = "INSERT INTO t_order (id,user_id,total,order_date,"
				+ "name,tel,addr,status) VALUES(?,?,?,sysdate,?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, order.getId());
		pstmt.setLong(2, order.getUserId());
		pstmt.setDouble(3, order.getTotal());
		//pstmt.setDate(4, order.getOrderDate()); //java.sql.Date添加时间不包括时分秒
		pstmt.setString(4, order.getName());
		pstmt.setString(5, order.getTel());
		pstmt.setString(6, order.getAddr());
		pstmt.setInt(7, order.getStatus());
		int rows = pstmt.executeUpdate();
		DBUtils.close(pstmt, conn);
		return rows;
		
	}
}

OrderDetailJDBC类：

package com.zhang.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.zhang.entity.OrderDetail;
import com.zhang.util.ConnectionFactory;
import com.zhang.util.DBUtils;

public class OrderDetailJDBC {
	public int addOrderDetail(OrderDetail detail) throws SQLException {
		Connection conn = ConnectionFactory.getConnection();	
		/*
		 * id NUMBER(11),
	order_id NUMBER(11),
	product_id NUMBER(11),
	price NUMBER(11,2),
	quantity NUMBER(9),
	cost NUMBER(11,2),
		 */
		//序列t_order_detail_id_seq在数据库的sql文件中已经设置好
		String sql = "INSERT INTO t_order_detail(id,order_id,product_id,price,quantity,cost)"
			+ "VALUES(t_order_detail_id_seq.NEXTVAL,?,?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, detail.getOrderId());
		pstmt.setLong(2, detail.getProductId());
		pstmt.setDouble(3, detail.getPrice());
		pstmt.setInt(4, detail.getQuantity());
		pstmt.setDouble(5, detail.getCost());
		int rows = pstmt.executeUpdate();
		DBUtils.close(pstmt, conn);
		return rows;
	}
}

TestOrder类：

package com.zhang.jdbc;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zhang.entity.Order;
import com.zhang.entity.OrderDetail;

public class TestOrder {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		/*INSERT INTO t_product(id, name, price, stock, cate_id, cate_child_id)
			VALUES(2, '金号毛巾条纹', 15, 5, 1, 7);
		INSERT INTO t_product(id, name, price, stock, cate_id, cate_child_id)
			VALUES(4, '晨光黑色中性笔', 2, 100, 2, 10);*/
		TestOrder to = new TestOrder();
		Order order = new Order(0,5,19,null,"xiaoming","111","111",0);
		List<OrderDetail> list = new ArrayList<>();
		list.add(new OrderDetail(0,0,2,15,1,15));
		list.add(new OrderDetail(0,0,4,2,3,6));
		System.out.println(to.takeOrder(order, list));
		/*可用SELECT to_char(order_date, 'yyyy-mm-dd hh24:mi:ss'), id FROM t_order;
		和SELECT id,order_id,product_id,quantity FROM t_order_detail;在数据库中查询结果*/

	}
	
	public boolean takeOrder(Order order, List<OrderDetail> list) {
		
		OrderJDBC orderJDBC = new OrderJDBC();
		OrderDetailJDBC detailJDBC = new OrderDetailJDBC();
		
		try {
			//完成下单业务
			//1.生成订单编号
			long orderId = orderJDBC.generateOrderId();
			//2.生成订单
			//把订单号赋给order
			order.setId(orderId);
			orderJDBC.addOrder(order);
		    //3.生成订单明细
			for(int i = 0; i < list.size();i++) {
				list.get(i).setOrderId(orderId);
				detailJDBC.addOrderDetail(list.get(i));
			}
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	    
	}

}