JDBC

connection：

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@192.168.0.202:1521:tarenadb
user=asd0606
password=asd0606

ConnectionFactory:

 

package jdbc;

import java.sql.*;
import java.util.*;
import java.io.*;

public class ConnectionFactory ...{
    private static ThreadLocal<Connection> con=new ThreadLocal<Connection>();
    
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    public static Connection getConnection() throws ClassNotFoundException,
            SQLException...{
        if (con.get() == null || con.get().isClosed())
            initConnection();
        return con.get();
    }

    public static void initConnection() throws ClassNotFoundException,
            SQLException...{
        loadFromConfig();
   Class.forName(driver);
        con.set(DriverManager.getConnection(url, user,password));
        con.get().setAutoCommit(false);
    }
    public static void beginWorkUnit() throws SQLException...{
        
        //
}
    public static void commitWorkUnit() throws SQLException...{
        if(existConnection())
            con.get().commit();
    }
    public static void roolbackWorkUnit() throws SQLException...{
        if(existConnection())
            con.get().rollback();
    }
    
    public static void release()throws SQLException...{
        if(existConnection())
            con.get().close();
    }
    
    private static void loadFromConfig()...{
        try...{
            Properties prop=new Properties();
        //从文件填充    Properties
        /**//*InputStream config=null;
     config=new FileInputStream("connection");
        prop.load(config);
        */
            ClassLoader classLoader=ConnectionFactory.class.getClassLoader();
          InputStream config=classLoader.getResourceAsStream("connection");
          prop.load(config);
        //
        driver=prop.getProperty("driver");
        url=prop.getProperty("url");
        user=prop.getProperty("user");
        password=prop.getProperty("password");
        }catch(Exception e)...{
            e.printStackTrace();
        }
        
    }
    private static boolean existConnection() throws SQLException...{
        return    con.get() != null&&!con.get().isClosed();
    }
}

OrderDAOImp:

 

package com.daosur;

import com.tarena.dao.OrderDAO;
import com.tarena.pojo.Order;
import com.tarena.pojo.OrderLine;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashSet;
import java.util.Set;

import net.xiaopi.dao.common.*;


public class OrderDAOImp implements OrderDAO...{

    public void add(Order order) ...{
        PreparedStatement pstmt=null;
        try...{
        String sql="insert into Orders(id,code,user_id,shippeddate,sum) values (?,?,?,?,?)";
      pstmt=ConnectionFactory.getConnection().prepareStatement(sql);
        pstmt.setInt(1, order.getId());
        pstmt.setString(2, order.getCode());
        pstmt.setInt(3, order.getUser().getId());
        pstmt.setDate(4, new java.sql.Date((order.getShippedDate()).getTime()));
        pstmt.setFloat(5, order.getSum());
        pstmt.executeUpdate();
        }catch(Exception e)...{
            e.printStackTrace();
        }finally...{try...{pstmt.close();}catch(Exception e)...{}
        }
    }

    public void delete(Order order) ...{
        PreparedStatement pstmt=null;
        try...{
            if(order.getOrderLines()!=null)
            removeOderLinesByoid(order);
            String sql="delete from orders where id=?";
            pstmt=ConnectionFactory.getConnection().prepareStatement(sql);
            pstmt.setInt(1, order.getId());
            pstmt.executeUpdate();
        }catch(Exception e)...{
            e.printStackTrace();
        }finally...{try...{pstmt.close();}catch(Exception e)...{}
        }
        
    }

    public Order load(Integer id) ...{
        Order order=new Order();
        UsersDAOImp usersdi=new UsersDAOImp();
        PreparedStatement pstmt=null;
        try...{
            String sql="select id,code,user_id,shippeddate,sum from orders where id=?";
            pstmt=ConnectionFactory.getConnection().prepareStatement(sql);
            pstmt.setInt(1, id);
            order.setId(id);
            ResultSet rs=pstmt.executeQuery();
            Set<OrderLine> set= displayOrderlinesByoid(id);
            order.setOrderLines(set);
            if(rs.next())...{
                order.setCode(rs.getString("code"));
                order.setUser(usersdi.load(rs.getInt("user_id")));
                order.setShippedDate(rs.getDate("shippeddate"));
                order.setSum(rs.getFloat("sum"));
                }
        }catch(Exception e)...{
            e.printStackTrace();
        }finally...{try...{pstmt.close();}catch(Exception e)...{}
        }
        
        return order;
    }

    public void save(Order order) ...{
        PreparedStatement pstmt=null;
        try...{
            String sql="update orders set code=?,user_id=?,shippeddate=?,sum=? where id=?";
            pstmt=ConnectionFactory.getConnection().prepareStatement(sql);
            pstmt.setString(1, order.getCode());
            pstmt.setInt(2, order.getUser().getId());
            pstmt.setDate(3, new java.sql.Date((order.getShippedDate()).getTime()));
            pstmt.setFloat(4, order.getSum());
            pstmt.executeUpdate();
        }catch(Exception e)...{
            e.printStackTrace();
        }finally...{try...{pstmt.close();}catch(Exception e)...{}
        }
        
    }
    private static Set<OrderLine> displayOrderlinesByoid(Integer id)...{
        OrderLine orderlines=new OrderLine();
        ProductDAOImp prod=new ProductDAOImp();
        Set<OrderLine> list=new HashSet<OrderLine>();
        PreparedStatement pstmt=null;
        try...{
            String sql="select id, product_id,quantity,total from orderlines where order_id=?";
            pstmt=ConnectionFactory.getConnection().prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs=pstmt.executeQuery();
            while(rs.next())...{
                orderlines.setId(rs.getInt("id"));
                orderlines.setProduct(prod.load(rs.getInt("product_id")));
                orderlines.setQuantity(rs.getInt("quantity"));
                orderlines.setTotal(rs.getFloat("total"));
                list.add(orderlines);
            }
        }catch(Exception e)...{
            e.printStackTrace();
        }finally...{try...{pstmt.close();}catch(Exception e)...{}
        }
        return list;
    }
private static void removeOderLinesByoid(Order order)...{
    PreparedStatement pstmt=null;
    try...{
        String sql="delete from orderlines where order_id=?";
        pstmt=ConnectionFactory.getConnection().prepareStatement(sql);
        pstmt.setInt(1, order.getId());
        pstmt.executeUpdate();
    }catch(Exception e)...{
        e.printStackTrace();
    }finally...{try...{pstmt.close();}catch(Exception e)...{}
    }
}
}