JDBC的DAO模式、ORM关系模型和Template

谁借莪１个温暖的怀抱￠ 2022-06-07 12:21 261阅读 0赞

本例利用JDBC的DAO模式和Template模板实现ORM——以订单和订单细明一对多的关系映射进行说明。

首先看文件目录

![Center][]

**一、数据库的设计**

在数据库创建实例类Order和OrderLine对应的表t\_order和t\_orderline

表相关的字段和类属性对应

![Center 1][]

![Center 2][]

这两张表中id为主键，设置为自增

**二、创建类**

创建Order.java

package com.cjx913.pojo;
    
    import java.io.Serializable;
    import java.sql.Date;
    import java.util.ArrayList;
    import java.util.List;
    
    public class Order implements Serializable {
    	
    	private Integer id;
    	private Date orderedDate;
    	private Date shippedDate;
    	private Double total;
    	private List<OrderLine> orderlines = new ArrayList<OrderLine>();//建立一对多的关系
    	
    	public Order(Integer id, Date orderedDate, Date shippedDate, Double total, List<OrderLine> orderlines) {
    		super();
    		this.id = id;
    		this.orderedDate = orderedDate;
    		this.shippedDate = shippedDate;
    		this.total = total;
    		this.orderlines = orderlines;
    	}
    	
    	
    	
    	public Order() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public Date getOrderedDate() {
    		return orderedDate;
    	}
    	public void setOrderedDate(Date orderedDate) {
    		this.orderedDate = orderedDate;
    	}
    	public Date getShippedDate() {
    		return shippedDate;
    	}
    	public void setShippedDate(Date shippedDate) {
    		this.shippedDate = shippedDate;
    	}
    	public Double getTotal() {
    		return total;
    	}
    	public void setTotal(Double total) {
    		this.total = total;
    	}
    	public List<OrderLine> getOrderlines() {
    		return orderlines;
    	}
    	public void setOrderlines(List<OrderLine> orderlines) {
    		this.orderlines = orderlines;
    	}
    
    	@Override
    	public String toString() {
    		return "Order [id=" + id + ", orderedDate=" + orderedDate + ", shippedDate=" + shippedDate + ", total=" + total
    				+ ", orderlines=" + orderlines + "]";
    	}
    	
    	
    
    }

创建OrderLine类

package com.cjx913.pojo;
    
    import java.io.Serializable;
    
    public class OrderLine implements Serializable {
    
    	private Integer id;
    	private Double price;
    	private Long quantity;
    	private String product;
    	private Order order;//建立对应关系
    	
    	public OrderLine() {
    		super();
    		// TODO Auto-generated constructor stub
    	}
    
    	public OrderLine(Integer id, Double price, Long quantity, String product, Order order) {
    		super();
    		this.id = id;
    		this.price = price;
    		this.quantity = quantity;
    		this.product = product;
    		this.order = order;
    	}
    
    	public Integer getId() {
    		return id;
    	}
    
    	public void setId(Integer id) {
    		this.id = id;
    	}
    
    	public Double getPrice() {
    		return price;
    	}
    
    	public void setPrice(Double price) {
    		this.price = price;
    	}
    
    	public Long getQuantity() {
    		return quantity;
    	}
    
    	public void setQuantity(Long quantity) {
    		this.quantity = quantity;
    	}
    
    	public String getProduct() {
    		return product;
    	}
    
    	public void setProduct(String product) {
    		this.product = product;
    	}
    
    	public Order getOrder() {
    		return order;
    	}
    
    	public void setOrder(Order order) {
    		this.order = order;
    	}
    
    	@Override
    	public String toString() {
    		//这里不输出Order，不然两个类同时输出会死循环
    		return "OrderLine [id=" + id + ", price=" + price + ", quantity=" + quantity + ", product=" + product + "]";
    	}
    	
    	
    }

**三、创建数据库连接**

配置数据库连接参数文件jdbcinfo.properties

sqlite.driver=org.sqlite.JDBC
    sqlite.url=jdbc:sqlite:E:/SQLite3/StudentManageSystem.db
    sqlite.user=
    sqlite.password= 
    
    oracle.driver=oracle.jdbc.driver.OracleDriver  
    oracle.url=jdbc:oracle:thin:@" + "host:port:databaseName  
    oracle.user=**  
    oracle.password=**  
      
    mysql.driver=com.mysql.jdbc.Driver  
    mysql.url=jdbc:mysql://host:port:databaseName  
    mysql.user=**  
    mysql.password=**

创建ConnectionFactory.java

package com.cjx913.connection;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.Properties;
    
    public class ConnectionFactory {
    	private static String DRIVER;
    	private static String URL;
    	private static String USER;
    	private static String PASSWORD;
    	
    	static{
    		Properties properties = new Properties();
    		InputStream is = 
    				ConnectionFactory.class.getResourceAsStream("jdbcinfo.properties");
    		try {
    			properties.load(is);//加载配置文件
    			DRIVER = properties.getProperty("sqlite.driver");//读取文件配置数据库驱动
    			URL = properties.getProperty("sqlite.url");//读取文件配置数据库URL
    			USER = properties.getProperty("sqlite.user");//读取文件配置数据库用户
    			PASSWORD = properties.getProperty("sqlite.password");//读取文件配置数据库用户密码
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    	}
    	
    	/**
    	 * 提供getConnection()方法
    	 * @return Connection
    	 */
    	public static Connection getConnection(){
    		Connection conn = null;
    		try {
    			Class.forName(DRIVER);
    			conn = DriverManager.getConnection(URL);
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    		return conn;
    	}
    
    }

创建工具类DBUtils.java

package com.cjx913.connection;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class DBUtils {
    
    	public static void close(ResultSet rs, Statement stmt,Connection conn){
    		try {
    			rs.close();
    			stmt.close();
    			conn.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    	}
    
    	public static void close(ResultSet rs, Statement stmt) {
    		try {
    			rs.close();
    			stmt.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    	}
    
    	public static void close(Connection conn) {
    		try {
    			conn.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    	}
    
    	public static void close(PreparedStatement pstmt) {
    		try {
    			pstmt.close();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		
    	}
    
    }

**四、创建DataAccessException.java**

用于抛出数据传输错误

package com.cjx913.exception;
    
    public class DataAccessException extends Exception {
    
    	public DataAccessException() {
    		// TODO Auto-generated constructor stub
    	}
    
    	public DataAccessException(String message) {
    		super(message);
    		// TODO Auto-generated constructor stub
    	}
    
    	public DataAccessException(Throwable cause) {
    		super(cause);
    		// TODO Auto-generated constructor stub
    	}
    
    	public DataAccessException(String message, Throwable cause) {
    		super(message, cause);
    		// TODO Auto-generated constructor stub
    	}
    
    	public DataAccessException(String message, Throwable cause, boolean enableSuppression, boolean writableStackTrace) {
    		super(message, cause, enableSuppression, writableStackTrace);
    		// TODO Auto-generated constructor stub
    	}
    
    }

**五、创建模板**

创建模板类JDBCTemplate.java

package com.cjx913.template;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import com.cjx913.connection.DBUtils;
    import com.cjx913.exception.DataAccessException;
    
    public class JDBCTemplate {
    	private Connection conn;
    
    	public JDBCTemplate(Connection conn) {
    		super();
    		this.conn = conn;
    	}
    	
    	//R(Retrieve)
    	public void query(String sql,RowCallBackHandler handler) throws DataAccessException{
    		Statement stmt = null;
    		ResultSet rs = null;
    		try{
    			stmt = conn.createStatement();
    			rs = stmt.executeQuery(sql);
    			if(handler!=null){
    				handler.processRow(rs);
    			}
    		}catch(SQLException e){
    			throw new DataAccessException(e.getMessage(),e);
    		}finally{
    			DBUtils.close(rs, stmt);
    		}
    	}
    	
    	public void query(String sql,PreaparedStatementSetter setter,
    			RowCallBackHandler handler) throws DataAccessException{
    		PreparedStatement pstmt = null;
    		ResultSet rs = null;
    		try {
    			pstmt = conn.prepareStatement(sql);
    			if(setter!=null){
    				setter.setValues(pstmt);
    			}
    			rs = pstmt.executeQuery();
    			if(handler!=null){
    				handler.processRow(rs);
    			}
    		} catch (SQLException e) {
    			throw new DataAccessException(e.getMessage(), e);
    		}finally{
    			DBUtils.close(rs, pstmt);
    		}
    	}
    	
    	//C(Create) U(Updata) D(Delete)
    	public void updata(String sql,PreaparedStatementSetter setter) throws DataAccessException{
    		PreparedStatement pstmt = null;
    		try {
    			pstmt = conn.prepareStatement(sql);
    			if(setter!=null){
    				setter.setValues(pstmt);
    			}
    			pstmt.executeUpdate();
    		} catch (SQLException e) {
    			throw new DataAccessException(e.getMessage(), e);
    		}finally{
    			DBUtils.close(pstmt);
    		}
    	}
    }

创建接口，用于现实数据库操作

PreaparedStatementSetter.java

package com.cjx913.template;
    
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public interface PreaparedStatementSetter {
    	//通过设置PreaparedStatementSetter来设置一些替换占位符“？”
    	void setValues(PreparedStatement pstmt) throws SQLException;
    }

RowCallBackHandler.java

package com.cjx913.template;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public interface RowCallBackHandler {
    	//处理结果集
    	void processRow(ResultSet rs) throws SQLException;
    }

**六、创建DAO接口IOrderDao，并创建实现类OrderDaoImpl**

package com.cjx913.dao;
    
    import java.sql.Connection;
    import java.util.List;
    
    import com.cjx913.exception.DataAccessException;
    import com.cjx913.pojo.Order;
    
    public interface IOrderDao {
    
    	void saveOrder(Order order) throws DataAccessException;
    	void deleteOrder(Integer id) throws DataAccessException;
    	void updateOrder(Order order) throws DataAccessException;
    	Order findOrder(Integer id) throws DataAccessException;
    	List<Order> findOrders() throws DataAccessException;
    	
    	//使用模板
    	void saveOrder(Order order,Connection conn) throws DataAccessException;
    	void deleteOrder(Integer id,Connection conn) throws DataAccessException;
    	void updateOrder(Order order,Connection conn) throws DataAccessException;
    	Order findOrder(Integer id,Connection conn) throws DataAccessException;
    	List<Order> findOrders(Connection conn) throws DataAccessException;
    }

package com.cjx913.dao;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    
    import com.cjx913.connection.ConnectionFactory;
    import com.cjx913.connection.DBUtils;
    import com.cjx913.exception.DataAccessException;
    import com.cjx913.pojo.Order;
    import com.cjx913.pojo.OrderLine;
    import com.cjx913.template.JDBCTemplate;
    import com.cjx913.template.PreaparedStatementSetter;
    
    public class OrderDaoImpl implements IOrderDao {
    
    	@Override
    	public void saveOrder(Order order) throws DataAccessException {
    		Connection conn = null;
    		PreparedStatement pstmt = null;
    		ResultSet rs = null;
    		
    		try{
    			conn = ConnectionFactory.getConnection();
    			conn.setAutoCommit(false);
    			
    			//先插入订单表记录
    			/*String selectSQL = "SELECT t_order_index.";//查询索引，作为主键
    			pstmt = conn.prepareStatement(selectSQL);
    			rs = pstmt.executeQuery();
    			Long orderid = 0L;
    			if(rs.next()){
    				orderid = rs.getLong(1);
    			}*/
    			//下面使用sqlite数据库，在sqlite_sequence表可以查询自增列的最大值
    			String selectSQL = "SELECT seq FROM sqlite_sequence WHERE name='t_order'";
    			pstmt = conn.prepareStatement(selectSQL);
    			rs = pstmt.executeQuery();
    			Integer orderid = 0;
    			if(rs.next()){
    					orderid = rs.getInt(1)+1;
    			}
    			
    			String insertSQL = "INSERT INTO t_order VALUES(?,?,?,?)";
    			pstmt = conn.prepareStatement(insertSQL);
    			pstmt.setInt(1, orderid);
    			pstmt.setDate(2, order.getOrderedDate());
    			pstmt.setDate(3, order.getShippedDate());
    			pstmt.setDouble(4, order.getTotal());
    			int orderRow = pstmt.executeUpdate();
    			
    			//插入订单明细表记录
    			insertSQL = "INSERT INTO t_orderline VALUES(?,?,?,?,?)";
    			pstmt = conn.prepareStatement(insertSQL);
    			int count=0;
    			for(OrderLine ol:order.getOrderlines()){
    				/*selectSQL = "SELECT t_order_index.";//查询t_orderline索引
    				PreparedStatement selectPstmt = conn.prepareStatement(selectSQL);
    				rs = selectPstmt.executeQuery();
    				Long olid = 0L;
    				if(rs.next()){
    					olid = rs.getLong(1);
    				}*/
    				selectSQL = "SELECT seq FROM sqlite_sequence WHERE name='t_orderline'";
    				PreparedStatement selectPstmt = conn.prepareStatement(selectSQL);
    				rs = selectPstmt.executeQuery();
    				Integer olid = 0;
    				if(rs.next()){
    						olid = rs.getInt(1)+1;
    				}
    				pstmt.setInt(1, olid);
    				pstmt.setDouble(2, ol.getPrice());
    				pstmt.setLong(3, ol.getQuantity());
    				pstmt.setString(4, ol.getProduct());
    				pstmt.setLong(5, orderid);
    				int rows = pstmt.executeUpdate();
    				count +=rows;
    			}
    			
    			conn.commit();
    			System.out.println("成功插入订单记录："+orderRow);
    			System.out.println("成功插入订单明细记录"+count);
    		}catch(Exception e){
    			e.printStackTrace();
    			try {
    				conn.rollback();
    			} catch (SQLException e1) {
    				e1.printStackTrace();
    				throw new DataAccessException("保存订单出错");
    			}
    		}finally{
    			DBUtils.close(rs,pstmt,conn);
    		}
    
    	}
    
    	@Override
    	public void deleteOrder(Integer id) throws DataAccessException {
    		// TODO Auto-generated method stub
    
    	}
    
    	@Override
    	public void updateOrder(Order order) throws DataAccessException {
    		// TODO Auto-generated method stub
    
    	}
    
    	@Override
    	public Order findOrder(Integer id) throws DataAccessException {
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public List<Order> findOrders() throws DataAccessException {
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	
    	//使用模板
    	@Override
    	public void saveOrder(Order order, Connection conn) throws DataAccessException {
    		
    	}
    
    	@Override
    	public void deleteOrder(Integer id, Connection conn) throws DataAccessException {
    		//创建模板
    		JDBCTemplate jt = new JDBCTemplate(conn);
    		//调用模板
    		String deleteSQL = "DELETE FROM t_order WHERE id=?";
    		jt.updata(deleteSQL , new PreaparedStatementSetter() {
    			
    			@Override
    			public void setValues(PreparedStatement pstmt) throws SQLException {
    				pstmt.setInt(1, id);
    			}
    		});
    		
    		deleteSQL = "DELETE FROM t_orderline WHERE order_id=?";
    		jt.updata(deleteSQL, new PreaparedStatementSetter() {
    			
    			@Override
    			public void setValues(PreparedStatement pstmt) throws SQLException {
    				pstmt.setInt(1, id);
    			}
    		});
    	}
    
    	@Override
    	public void updateOrder(Order order, Connection conn) throws DataAccessException {
    		// TODO Auto-generated method stub
    		
    	}
    
    	@Override
    	public Order findOrder(Integer id, Connection conn) throws DataAccessException {
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    	@Override
    	public List<Order> findOrders(Connection conn) throws DataAccessException {
    		// TODO Auto-generated method stub
    		return null;
    	}
    
    }

七、创建测试类Test

package com.cjx913.test;
    
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.SQLException;
    import java.util.Calendar;
    
    import com.cjx913.connection.ConnectionFactory;
    import com.cjx913.connection.DBUtils;
    import com.cjx913.dao.IOrderDao;
    import com.cjx913.dao.OrderDaoImpl;
    import com.cjx913.exception.DataAccessException;
    import com.cjx913.pojo.Order;
    import com.cjx913.pojo.OrderLine;
    
    public class Test1 {
    	
    	public static void main(String[] args) {
    		
    		Order order = new Order();
    		order.setOrderedDate(new Date(System.currentTimeMillis()));
    		Calendar cl =Calendar.getInstance();
    		cl.set(Calendar.DAY_OF_MONTH, cl.get(Calendar.DAY_OF_MONTH+3));
    		order.setShippedDate(new Date(cl.getTimeInMillis()));
    		
    		
    		OrderLine ol1 = new OrderLine();
    		ol1.setProduct("Java");
    		ol1.setPrice(20.5);
    		ol1.setQuantity(7L);
    		OrderLine ol2 = new OrderLine();
    		ol2.setProduct("JDBC");
    		ol2.setPrice(20.5);
    		ol2.setQuantity(7L);
    		
    		order.getOrderlines().add(ol1);
    		order.getOrderlines().add(ol2);
    		order.setTotal(ol1.getPrice()*ol1.getQuantity()+ol2.getPrice()*ol2.getQuantity());
    		ol1.setOrder(order);
    		ol2.setOrder(order);
    		
    		IOrderDao orderDao = new OrderDaoImpl();
    		
    		try {
    			orderDao.saveOrder(order);
    		} catch (DataAccessException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    		
    		//删除订单
    		//IOrderDao orderDao = new OrderDaoImpl();
    		Connection conn = ConnectionFactory.getConnection();
    		try {
    			conn.setAutoCommit(false);
    			orderDao.deleteOrder(1, conn);
    			conn.commit();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (DataAccessException e) {
    			e.printStackTrace();
    			try {
    				conn.rollback();
    			} catch (SQLException e1) {
    				// TODO Auto-generated catch block
    				e1.printStackTrace();
    			}
    		}finally{
    			DBUtils.close(conn);
    		}
    	}
    	
    }

[Center]: /images/20220607/ae8050ad430648f1b8a4233144ec5489.png
[Center 1]: /images/20220607/f3813ec630ce4785b96a2c6441aadad1.png
[Center 2]: /images/20220607/c4820c24e89844f288fbab6364ed693f.png