DAO 操作数据库

 

package dao;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

 

import javax.resource.cci.ResultSet;

import po.Customer;

 

public class CustomerDao {

    private Connection conn = null;

 

    // 连接数据库

    private void initConnection() {

          

           try{ 

                  Class.forName("oracle.jdbc.driver.OracleDriver"); 

                } 

                catch(ClassNotFoundException  e) 

                { 

                  System.out.println("数据库驱动类没找到");   

                }   

          try {     

               // 建立连接

               String serverName = "127.0.0.1";

               String portNumber = "1521";

               String sid = "oracle";

               String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;

               String username = "scott";

               String password = "tiger";

               conn = DriverManager.getConnection(url, username, password);

               System.out.println("数据库连接成功！");

           }  catch (SQLException e) {

             System.out.println("执行SQL语句异常！");             

                  e.printStackTrace();   

           }

    }

 

    // 插入数据

    public boolean insertCustomer(Customer cus) {

       String sql = "INSERT INTO T_CUSTOMER(ACCOUNT,PASSWORD,CNAME,CBALANCE)VALUES(1,1,1,1)";

       try {

           initConnection();

           PreparedStatement ps = conn.prepareStatement(sql);

           ps.setString(1, cus.getAccount());

           ps.setString(2, cus.getPassword());

           ps.setString(3, cus.getCname());

           ps.setDouble(4, cus.getCbalance());

           ps.executeUpdate();

           return true;

       } catch (Exception ex) {

           ex.printStackTrace();

       } finally {

           closeConnection();

       }

       return false;

 

    }

 

    // 修改数据

    public boolean updateCustomer(Customer cus) {

       String sql = "UPDATE T_CUSTOMER SET PASSWORD=?,CNAME=?,CBALANCE=? WHERE ACCOUNT=?";

       try {

           initConnection();

           PreparedStatement ps = conn.prepareStatement(sql);

           ps.setString(1, cus.getPassword());

           ps.setString(2, cus.getCname());

           ps.setDouble(3, cus.getCbalance());

           ps.setString(4, cus.getAccount());

           ps.executeUpdate();

       } catch (Exception ex) {

           ex.printStackTrace();

       } finally {

           closeConnection();

       }

       return false;

    }

    // 查询数据

    public Customer getCustomer(String account) {

       String sql = "SELECT ACCOUNT,PASSWORD,CNAME,CBALANCE FROM T_CUSTOMER WHERE ACCOUNT=?";

       try {

           initConnection();

           PreparedStatement ps = conn.prepareStatement(sql);

           ps.setString(1, account);

           ResultSet rs = (ResultSet) ps.executeQuery();

           if (rs.next()) {

              Customer cus = new Customer();

              cus.setAccount(rs.getString("ACCOUNT"));

              cus.setPassword(rs.getString("PASSWORD"));

              cus.setCname(rs.getString("CNAME"));

              cus.setCbalance(rs.getDouble("CBALANCE"));

              return cus;

           }

       } catch (Exception ex) {

           ex.printStackTrace();

       } finally {

           closeConnection();

       }

       return null;

 

    }

 

   

    // 断开连接

    public void closeConnection() {

       try {

           if (conn != null) {

              conn.close();

              conn = null;

 

           }

       } catch (Exception ex) {

           ex.printStackTrace();

       }

    }

}