jdbc的简单案例

package day2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import day1.JdbcUtil;

/*
 * 封装 java访问数据库的代码 ----> User表代码 
 */
public class UserDAOImpl implements UserDAO {
   //1 java 代码 对 user 插入操作
  public void save(User user){
  Connection conn = null;
  PreparedStatement pstmt = null;
  try{
   conn = JdbcUtil.getConnection();
  
   String sql = "insert into t_user (name,password) values (?,?)";
   pstmt = conn.prepareStatement(sql);
   pstmt.setString(1,user.getName());
   pstmt.setString(2,user.getPassword());
  
   pstmt.executeUpdate();
  
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   JdbcUtil.release(conn, pstmt);
  }
  }
 
  public void update(User u){
      Connection conn = null;
   PreparedStatement pstmt = null;
   try{
    conn = JdbcUtil.getConnection();
   
    String sql = "update t_user set name=?, password=? where id = ?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setString(1,u.getName());
    pstmt.setString(2, u.getPassword());
    pstmt.setInt(3,u.getId());
    pstmt.executeUpdate();
   
   }catch(Exception e){
    e.printStackTrace();
   }finally{
    JdbcUtil.release(conn, pstmt);
   }
  }
 
  public void delete(User u){
      Connection conn = null;
   PreparedStatement pstmt = null;
   try{
    conn = JdbcUtil.getConnection();
   
    String sql = "delete from t_user where id = ?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, u.getId());
    pstmt.executeUpdate();
   
   }catch(Exception e){
    e.printStackTrace();
   }finally{
    JdbcUtil.release(conn, pstmt);
   }
  }
 
  // 通过 User id进行查询
  public User queryUserById(int id){
   Connection conn = null;
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   User u = null;
  
   try{
    conn = JdbcUtil.getConnection();
   
    String sql = "select id,name,password from t_user where id = ?";
    pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, id);
    rs = pstmt.executeQuery();
   
    if(rs.next()){
     u = new User();
     u.setId(rs.getInt(1));
     u.setName(rs.getString(2));
     u.setPassword(rs.getString(3));
   }
    return u;
   }catch(Exception e){
    e.printStackTrace();
    return null;
   }finally{
    JdbcUtil.release(conn, pstmt, rs);
   }
  }  
 // 查询user全表数据  
 public List<User>  queryAllUsers(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    List<User> users = new ArrayList<User>();
    try{
     conn = JdbcUtil.getConnection();
    
     String sql = "select id,name,password from t_user order by name asc";
     pstmt = conn.prepareStatement(sql);
     rs = pstmt.executeQuery();
    
     while(rs.next()){
      User u = new User();
      u.setId(rs.getInt(1));
      u.setName(rs.getString(2));
      u.setPassword(rs.getString(3));
      users.add(u);
     }
     return users;
    }catch(Exception e){
     e.printStackTrace();
     return null;
    }finally{
     JdbcUtil.release(conn, pstmt, rs);
    }
 }
  
 
 public boolean queryUserByNameAndPassword(String name,String password){
     Connection conn = null;
     PreparedStatement pstmt = null;
     ResultSet rs = null;
     try{
      conn = JdbcUtil.getConnection();
     
      String sql = "select id,name,password from t_user where name = ? and password = ? ";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, name);
      pstmt.setString(2,password);
      rs = pstmt.executeQuery();
     
      if(rs.next()){
       return true;
      }
      return false;
     }catch(Exception e){
      e.printStackTrace();
      return false;
   }finally{
      JdbcUtil.release(conn, pstmt, rs);
     }
 }
 
 
 public User qureyUserByName(String name){
     Connection conn = null;
     PreparedStatement pstmt = null;
     ResultSet rs = null;
     User u = null;
     try{
      conn = JdbcUtil.getConnection();
     
      String sql = "select id,name,password from t_user where name = ? ";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, name);
      rs = pstmt.executeQuery();
     
      if(rs.next()){
       u = new User();
       u.setId(rs.getInt(1));
       u.setName(rs.getString(2));
       u.setPassword(rs.getString(3));
      }
      return u;
     }catch(Exception e){
      e.printStackTrace();
      return null;
   }finally{
      JdbcUtil.release(conn, pstmt, rs);
     }
 }
 
}