package drinkMachine.list;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.security.auth.message.callback.PrivateKeyCallback.Request;

//import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;

public class T001ItemDao {
//获得访问数据库的许可
private Connection conn =null;
//是为了对数据库执行SQL语句(增删改查)而使用的东西
private PreparedStatement pstmt =null;
//连接数据库
public T001ItemDao() throws ClassNotFoundException,SQLException{
//读取oracleJDBCDriver
Class.forName("oracle.jdbc.driver.OracleDriver");
//经由jdbc.连接DB连接方法--->
//getConnection("jdbc:oracle:thin:@主机名:端口:SID", "连接数据库名", "密码(口令)");
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "EDU_0007_xu", "yyy");

}       
public List<ItemBean> searchItem(String code,String name){
    List<ItemBean> itemList= new ArrayList<ItemBean>();
    try {
        String query="SELECT ITEM_NO,ITEM_NM,UNIT_PRICE,STOCK_COUNT"+
                "FROM TOO1_ITEM"+
                "WHERE ITEM_NO LIKE '"+code+"%'" +
                "AND ITEM_NM LIKE '%"+name+"%'"+
                "ORDER BY ITEM_NO,TTEM_NM,UNIT_PRICE";
        Statement statement = conn.createStatement();
        ResultSet rs=statement.executeQuery(query);                         
         while(rs.next()) {
             ItemBean selectedItm= new ItemBean();
             selectedItm.setCode(rs.getString("ITEM_NM"));
             selectedItm.setName(rs.getString("ITEM_NM"));
             selectedItm.setUnitPrice(rs.getString("UNIT_PRICE"));
             selectedItm.setCount(rs.getString("STOCK_COUNT"));
             itemList.add(selectedItm); 
         }
         System.out.println(""+itemList.size()+"种");     
}catch (SQLException  e) {
        e.printStackTrace();
        }   
    return itemList;
    
}
         

// return System.out.println("搜索结果:"+itemList.size()+"种");

//断开数据库
//针对数据库的处理（增删改查）结束以后必须要有数据库的断开处理
//如果不断开资源无法被解放会发生预期以外的错误
public void close() {
    if (pstmt!=null) {
        try {
            pstmt.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }
        }
    if (conn!=null) {
        try {
            conn.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}

}