學習筆記專區

package model.dao;

//tempate

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

import model.CustomerBean;

import model.CustomerDAO;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementSetter;

import org.springframework.jdbc.core.ResultSetExtractor;

public class CustomerDAOJdbc implements CustomerDAO {

    // private DataSource dataSource;

    private JdbcTemplate template;

     public static void main(String[] args) {

     ApplicationContext context = new ClassPathXmlApplicationContext(

     "beans.xml");

     CustomerDAO dao = (CustomerDAO) context.getBean("CustomerDAOJdbc");

     CustomerBean bean = dao.select("Alex");

     System.out.println(bean);

     boolean result = dao.update("E".getBytes(), "ellen@yahoo.com",

     new java.util.Date(0), "Ellen");

     System.out.println(result);

     }

    public CustomerDAOJdbc(DataSource dataSource) {

       this.template = new JdbcTemplate(dataSource);

       // Construct a new JdbcTemplate, given a DataSource to obtain

       // connections from.

    }

    private static final String SELECT = "select * from customer where custid=?";

    @Override

    public CustomerBean select(String custid) {

       return template.query(SELECT, new PreparedStatementSetter(){

           @Override

           public void setValues(PreparedStatement stmt) throws SQLException {

              stmt.setString(1, custid);

           }

       },new ResultSetExtractor<CustomerBean>() {

              @Override

              public CustomerBean extractData(ResultSet rset) throws SQLException,

                     DataAccessException {

                  CustomerBean result = new CustomerBean();

                  if (rset.next()) {

                     result = new CustomerBean();

                     result.setCustid(rset.getString("custid"));

                     result.setPassword(rset.getBytes("password"));

                     result.setEmail(rset.getString("email"));

                     result.setBirth(rset.getDate("birth"));

              }return result;}

           });

//     CustomerBean result = null;

//     Connection conn = null;

//     PreparedStatement stmt = null;

//     ResultSet rset = null;

//    

//     try {

//         conn = dataSource.getConnection();

//         stmt = conn.prepareStatement(SELECT);

//         stmt.setString(1, custid);

//         rset = stmt.executeQuery();

//         if (rset.next()) {

//            result = new CustomerBean();

//            result.setCustid(rset.getString("custid"));

//            result.setPassword(rset.getBytes("password"));

//            result.setEmail(rset.getString("email"));

//            result.setBirth(rset.getDate("birth"));

//         }

//     } catch (SQLException e) {

//         e.printStackTrace();

//     } finally {

//         if (rset != null) {

//            try {

//                rset.close();

//            } catch (SQLException e) {

//                e.printStackTrace();

//            }

//         }

//         if (stmt != null) {

//            try {

//                stmt.close();

//            } catch (SQLException e) {

//                e.printStackTrace();

//            }

//         }

//         if (conn != null) {

//            try {

//                conn.close();

//            } catch (SQLException e) {

//                e.printStackTrace();

//            }

//         }

//     }

//     return result;

    }

   private static final String UPDATE = "update customer set password=?, email=?, birth=? where custid=?";

    @Override

    public boolean update(byte[] password, String email, java.util.Date birth,

           String custid) {

       int i = template.update(UPDATE, new PreparedStatementSetter() {

           public void setValues(PreparedStatement stmt) throws SQLException {

              stmt.setBytes(1, password);

              stmt.setString(2, email);

              if (birth != null) {

                  long time = birth.getTime();

                  stmt.setDate(3, new java.sql.Date(time));

              } else {

                  stmt.setDate(3, null);

              }

              stmt.setString(4, custid);

           }

       });

       if (i == 1) {

           return true;

       } else {

           return false;

       }

       // try (

       // Connection conn = dataSource.getConnection();

       // PreparedStatement stmt = conn.prepareStatement(UPDATE);) {

       //

       // stmt.setBytes(1, password);

       // stmt.setString(2, email);

       // if (birth != null) {

       // long time = birth.getTime();

       // stmt.setDate(3, new java.sql.Date(time));

       // } else {

       // stmt.setDate(3, null);

       // }

       // stmt.setString(4, custid);

       // int i = stmt.executeUpdate();

       // if (i == 1) {

       // return true;

       // }

       // } catch (SQLException e) {

       // e.printStackTrace();

       // }

    }

}