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

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

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

首先看文件目录

一、数据库的设计

在数据库创建实例类Order和OrderLine对应的表t_order和t_orderline

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

这两张表中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;}@Overridepublic 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;}@Overridepublic String toString() {//这里不输出Order，不然两个类同时输出会死循环return "OrderLine [id=" + id + ", price=" + price + ", quantity=" + quantity + ", product=" + product + "]";}}

三、创建数据库连接

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

sqlite.driver=org.sqlite.JDBCsqlite.url=jdbc:sqlite:E:/SQLite3/StudentManageSystem.dbsqlite.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");//读取文件配置数据库URLUSER = properties.getProperty("sqlite.user");//读取文件配置数据库用户PASSWORD = properties.getProperty("sqlite.password");//读取文件配置数据库用户密码} catch (IOException e) {// TODO Auto-generated catch blocke.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 {@Overridepublic 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);}}@Overridepublic void deleteOrder(Integer id) throws DataAccessException {// TODO Auto-generated method stub}@Overridepublic void updateOrder(Order order) throws DataAccessException {// TODO Auto-generated method stub}@Overridepublic Order findOrder(Integer id) throws DataAccessException {// TODO Auto-generated method stubreturn null;}@Overridepublic List<Order> findOrders() throws DataAccessException {// TODO Auto-generated method stubreturn null;}//使用模板@Overridepublic void saveOrder(Order order, Connection conn) throws DataAccessException {}@Overridepublic 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() {@Overridepublic void setValues(PreparedStatement pstmt) throws SQLException {pstmt.setInt(1, id);}});deleteSQL = "DELETE FROM t_orderline WHERE order_id=?";jt.updata(deleteSQL, new PreaparedStatementSetter() {@Overridepublic void setValues(PreparedStatement pstmt) throws SQLException {pstmt.setInt(1, id);}});}@Overridepublic void updateOrder(Order order, Connection conn) throws DataAccessException {// TODO Auto-generated method stub}@Overridepublic Order findOrder(Integer id, Connection conn) throws DataAccessException {// TODO Auto-generated method stubreturn null;}@Overridepublic List<Order> findOrders(Connection conn) throws DataAccessException {// TODO Auto-generated method stubreturn 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 blocke.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 blocke.printStackTrace();} catch (DataAccessException e) {e.printStackTrace();try {conn.rollback();} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}}finally{DBUtils.close(conn);}}}