增删改查，连接数据库UsersDao

package com.abc.dao;

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

import com.abc.entity.Student;
import com.abc.entity.Users;

public class UsersDao extends BaseDao {
 
 public Users findByNumber(int number)
 {
  
  Users users=null;
  Connection conn=null;
  PreparedStatement stat=null;
  ResultSet rs=null;  
  conn=this.getConn();
  String sql="select number,password,name,sex,email,tel,card from user where number=?";
  try {
   stat=conn.prepareStatement(sql);
   stat.setInt(1, number);
   rs=stat.executeQuery();
   while(rs.next())
   {    
    String password=rs.getString(2);
    String name=rs.getString(3);
    String sex=rs.getString(4);
    String email=rs.getString(5);
    String tel=rs.getString(6);
    String card=rs.getString(7);
    users=new Users(number,password,name,sex,email,tel,card);
    
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   this.closeAll(rs, stat, conn);
  }
  return users;
 }
 
 
 public int adduser(Users users)
 {
  int i=0;
  Connection conn=null;
  PreparedStatement stat=null;
  conn=this.getConn();
  String sql="insert into user(number,password,name,sex,email,tel,card) values(?,?,?,?,?,?,?)";
  try {
   stat=conn.prepareStatement(sql);
   stat.setInt(1, users.getNumber());
   stat.setString(2,users.getPassword());
   stat.setString(3, users.getName());
   stat.setString(4,users.getSex());
   stat.setString(5, users.getEmail());
   stat.setString(6, users.getTel());
   stat.setString(7, users.getCard());
   i=stat.executeUpdate();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   this.closeAll(null, stat, conn);
  }
  
  return i;
 }

 public List<Users> findAll()
 {
  List<Users> list=new ArrayList<Users>();
  Connection conn=null;
  PreparedStatement stat=null;
  ResultSet rs=null;  
  conn=this.getConn();
  String sql="select number,password,name,sex,email,tel,card from user";
  try {
   stat=conn.prepareStatement(sql);
   rs=stat.executeQuery();
   while(rs.next())
   {
    int number=rs.getInt(1);
    String password=rs.getString(2);
    String name=rs.getString(3);
    String sex=rs.getString(4);
    String email=rs.getString(5);
    String tel=rs.getString(6);
    String card=rs.getString(7);
    //String name="zhu";
    Users stu=new Users(number,password,name,sex,email,tel,card);
    list.add(stu);
   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   this.closeAll(rs, stat, conn);
  }  
  return list;
 }
 
 
 public int updateusers(Users users)
 {
  int i=0;
  Connection conn=null;
  PreparedStatement stat=null;
  conn=this.getConn();
  String sql="update user set password=?,name=?,sex=?,email=?,tel=?,card=? where number=?";
  try {
   stat=conn.prepareStatement(sql);
   stat.setString(1, users.getPassword());
   stat.setString(2, users.getName());
   stat.setString(3, users.getSex());
   stat.setString(4, users.getEmail());
   stat.setString(5, users.getTel());
   stat.setString(6, users.getCard());
   stat.setInt(7, users.getNumber());
   i=stat.executeUpdate();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   this.closeAll(null, stat, conn);
  }
  
  return i;
 }
 
 public int deleteUsersByNumber(int number)
 {
  int i=0;
  Connection conn=null;
  PreparedStatement stat=null;
  conn=this.getConn();
  String sql="delete from user where number=?";
  try {
   stat=conn.prepareStatement(sql);
   stat.setInt(1, number);
   i=stat.executeUpdate();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }finally{
   this.closeAll(null, stat, conn);
  }
  
  return i;
 }

}

转载于:https://www.cnblogs.com/zjiacun/p/3350140.html