package com.itcast.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class ConnectionUtil {

 public Connection openConnection() {
  String driver = "";
  String url = "";
  String user = "";
  String password = "";
  Properties prop = new Properties();
  Connection conn = null;
  try {
   //加載屬性文件   
   prop.load(this.getClass().getClassLoader().getResourceAsStream(
     "DBConfig.properties"));
   driver = prop.getProperty("driver");
   url = prop.getProperty("url");
   user = prop.getProperty("user");
   password = prop.getProperty("password");
   //Class.forName加載驅動   
   Class.forName(driver);
   //DriverManager獲得連接   
   conn = DriverManager.getConnection(url, user, password);
   return conn;
  } catch (Exception e) {
   e.printStackTrace();
  }
  return null;
 }

}


3.業務實現類

package com.itcast.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.itcast.db.ConnectionUtil;
import com.itcast.db.DBConnection;
import com.itcast.model.User;
import com.itcast.service.IUser;

/**
 * 業務邏輯處理
 *
 * @author Administrator
 *
 */
public class UserImpl implements IUser {

// DBConnection db = new DBConnection();

 private PreparedStatement pstm;
 
 /**
  * 添加用戶
  */
 public void  add_User(User user) {
  try{
//  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//  conn= DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=school", "sa", "sa");
   Connection conn = new ConnectionUtil().openConnection();
  pstm=conn.prepareStatement("insert into userTable(user_name , user_password ,user_age,user_sex,user_address,user_telephone) values(?,?,?,?,?,?)");
  pstm.setString(1, user.getUsername());
  pstm.setString(2, user.getPassword());
  pstm.setInt(3, user.getAge());
  pstm.setString(4, user.getSex());
  pstm.setString(5, user.getAddress());
  pstm.setString(6, user.getTelephone());
  pstm.executeUpdate();
  }catch(SQLException e){
   e.printStackTrace();
  }catch(Exception e){
   e.printStackTrace();
  }
//  String sql="insert into userTable(user_name , user_password ,user_age,user_sex,user_address,user_telephone) values(?,?,?,?,?,?)";
//  
//  db.GetConnection();
  

 }

 /**
  * 刪除用戶
  */
 public boolean del_User(int id) {
  boolean result=false;
  try{
   Connection conn = new ConnectionUtil().openConnection();
   pstm=conn.prepareStatement("delete from  userTable where user_id="+id);
   
   pstm.executeUpdate();
   result=true;
   }catch(SQLException e){
    e.printStackTrace();
   }catch(Exception e){
    e.printStackTrace();
   }
   return result;
 }

 /**
  * 查找用戶
  */
 public List<User> find_User() {
 
  Connection conn = new ConnectionUtil().openConnection();
  List<User> list = new ArrayList();
  User user = new User();
  
  String sql = "select * from userTable ";
  try {
   pstm=conn.prepareStatement(sql);
//   pstm.setInt(1, id);
   ResultSet rs=pstm.executeQuery();
   while (rs.next()) {
    user.setId(rs.getInt(1));
    user.setUsername(rs.getString(2));
    user.setPassword(rs.getString(3));
    user.setAge(rs.getInt(4));
    user.setSex(rs.getString(5));
    user.setAddress(rs.getString(6));
    user.setTelephone(rs.getString(7));
    user.setTime(rs.getDate(8));
//    System.out.println(rs.getString(7));
    list.add(user);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return list;
  
  
 }

 /**
  * 按照id查詢用戶信息
  */
 public List find_Users(int id) {
  Connection conn = new ConnectionUtil().openConnection();
  List list = new ArrayList();
  User user = new User();
  
  String sql = "select * from userTable where user_id=?";
  try {
   pstm=conn.prepareStatement(sql);
   pstm.setInt(1, id);
   ResultSet rs=pstm.executeQuery();
   while (rs.next()) {
    user.setId(rs.getInt(1));
    user.setUsername(rs.getString(2));
    user.setPassword(rs.getString(3));
    user.setAge(rs.getInt(4));
    user.setSex(rs.getString(5));
    user.setAddress(rs.getString(6));
    user.setTelephone(rs.getString(7));
    user.setTime(rs.getDate(8));
//    System.out.println(rs.getString(7));
    list.add(user);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return list;
 }

 /**
  * 修改用戶信息
  */
 public IUser modify_User() {
  // TODO Auto-generated method stub
  return null;
 }

}