Java使用jdbc操作SQLServer完成增删改查功能

「爱情、让人受尽委屈。」 2021-09-21 10:52 401阅读 0赞

#### 引言 ####

考虑到还是有一部分人使用jdbc操作SQLServer完成增删改查的功能，但网上写的鱼龙混杂不全，相对于Mysql，这方面的资料相对比较少很多，所以在此把完整的增删改查的Java代码展现给大家，代码是经过我测试的，一切ok，希望能帮到一些人。

#### 准备工作 ####

开发工具：eclipse  
数据库：SQLServer2014(其他版本也可以）  
所需jar包：sqljdbc4.jar

#### 创建表 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center]

#### 数据库实体类 ####

private Integer id;
     private String empno;
     private String name;
     private String createDate;//发工资日期
     private double sal;
     private double salDecrease;//应扣费用
     //setter和getter方法
     @Override
     public String toString() { 
      return "Sal [id=" + id + ", empno=" + empno + ", name=" + name + ", createDate=" + createDate + ", sal=" + sal
        + ", salDecrease=" + salDecrease + "]";
     }
    public Sal() { 
    //无参构造器
     }
     //有参构造器，用来初始化对象
     public Sal(Integer id, String empno, String name, String createDate, double sal, double salDecrease) { 
      this.id = id;
      this.empno = empno;
      this.name = name;
      this.createDate = createDate;
      this.sal = sal;
      this.salDecrease = salDecrease;
     }

#### 数据库连接 ####

public class DbConn { 
     private String Driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  
     private String URL = "jdbc:sqlserver://localhost:1433;DatabaseName=db_employee_sal";  //da_employee_sal是数据库
     private String Username="sa",Password="123456";  //输入你自己的密码
     protected Connection conn = null; 
     public DbConn() {   
      Connection con = null;  
      try  {    
       Class.forName(Driver);  
       con = DriverManager.getConnection(URL,Username,Password);   
     } 
      catch(java.lang.ClassNotFoundException e) {     
       System.err.println( e.getMessage()); 
      }
      catch(SQLException e) {     
       System.err.println( e.getMessage()); 
      }   
      this.conn=con;  
       } 
    public void close(){       
       try{         
        if (conn!=null)  
         conn.close();      
        }      
         catch (SQLException e) {        
         System.out.println(e.getMessage());      
        }     
       }
     public static void main(String[] args) { 
       System.out.println(new DbConn());
       }
    }

#### 增删改查接口 ####

public interface SalDao { 
     List<Sal> selectAll();//查询所有 
     List<Sal> findByMap(String empno,String createDate);
     Sal find(Integer id);//根据id查询实体类信息
     int save(Sal record);//添加
     int update(Sal record);//更改
     int delete(Integer id);//删除
    }

#### 接口实现类 ####

import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    import dao.SalDao;
    import entity.Sal;
    import utils.DbConn;
    public class SalDaoImpl extends DbConn implements SalDao  { //继承数据库连接并实现
    public List<Sal> selectAll() { //查询所有
      List<Sal> list =new ArrayList<Sal>();
      if(conn!=null) { 
       try{ 
        String sql="select * from [dbo].[sal]";
        Statement stmt = conn.createStatement();     
        ResultSet rs = stmt.executeQuery(sql);     
        while(rs.next()){ 
         Sal r =new Sal();
         r.setId(rs.getInt("id"));
         r.setEmpno(rs.getString("empno"));
         r.setName(rs.getString("name"));
         r.setCreateDate(rs.getString("createDate"));
         r.setSal(rs.getDouble("sal"));
         r.setSalDecrease(rs.getDouble("salDecrease"));
         list.add(r);
        }
        rs.close(); 
       }catch(Exception e){ 
        e.printStackTrace();
       }
        }
      return list;
        }
        
    public List<Sal> findByMap(String empno,String createDate) { //模糊查询，根据条件查询
      List<Sal> list =new ArrayList<Sal>();
      if(conn!=null) { 
       try{ 
        String sql="select * from [dbo].[sal] where empno like '%"+empno+"'and createDate like'%"+createDate+"'";
        Statement stmt = conn.createStatement();     
        ResultSet rs = stmt.executeQuery(sql);     
        while(rs.next()){ 
         Sal r =new Sal();
         r.setId(rs.getInt("id"));
         r.setEmpno(rs.getString("empno"));
         r.setName(rs.getString("name"));
         r.setCreateDate(rs.getString("createDate"));
         r.setSal(rs.getDouble("sal"));
         r.setSalDecrease(rs.getDouble("salDecrease"));
         list.add(r);
        }
        rs.close(); 
        }catch(Exception e){ 
        e.printStackTrace();
       }
      }
      return list;
      }
      public Sal find(Integer id) { //根据id查询实体类
      Sal r =new Sal();
      if(conn!=null) { 
       try { 
        String sql="select * from [dbo].[sal] where id=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setInt(1,id);
        ResultSet rs=pst.executeQuery();
        if(rs.next()){     
         r.setId(rs.getInt("id"));
         r.setEmpno(rs.getString("empno"));
         r.setName(rs.getString("name"));
         r.setCreateDate(rs.getString("createDate"));
         r.setSal(rs.getDouble("sal"));
         r.setSalDecrease(rs.getDouble("salDecrease"));     
        }else{ 
         return null;
        }
       } catch (Exception e) { 
        e.printStackTrace();
       }
       }
      return r;
      }
    public int save(Sal record) { //保存数据
      if(conn!=null) { 
       try { 
        String sql="insert into [dbo].[sal](empno,name,createDate,sal,salDecrease) values (?,?,?,?,?)";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,record.getEmpno());
        pst.setString (2,record.getName());
        pst.setString(3,record.getCreateDate());
        pst.setDouble(4,record.getSal());
        pst.setDouble(5,record.getSalDecrease());
        pst.execute();
        return 1;//更改成功则返回1
       } catch (Exception e) { 
        e.printStackTrace();
       }
       
      }
      return 0;
     }
    public int update(Sal record) { //更改数据
      if(conn!=null) { 
       try { 
        String sql="update [dbo].[sal] set empno=?,name=?,createDate=?,sal=?,salDecrease=? where id=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setString(1,record.getEmpno());
        pst.setString (2,record.getName());
        pst.setString(3,record.getCreateDate());
        pst.setDouble(4,record.getSal());
        pst.setDouble(5,record.getSalDecrease());
        pst.setInt(6,record.getId());
        pst.execute();
        return 1;//更改成功返回1，失败返回0
       } catch (Exception e) { 
        e.printStackTrace();
       }
     
      }
      return 0;
     }
    public int delete(Integer id) { //根据id删除
      if(conn!=null) { 
       try { 
        String sql="delete from [dbo].[sal] where id=?";
        PreparedStatement pst=conn.prepareStatement(sql);
        pst.setInt(1,id);
        pst.executeUpdate();
        return 1;//删除成功则返回一
       } catch (Exception e) { 
        e.printStackTrace();
       }
       
      }
      return 0;
     }
     }

#### 测试 ####

public static void main(String[] args) { 
    int a=new SalDaoImpl().save(new Sal(null,"10008","张三","2020-09-12",3000,2000));//id自增，所以设置为空，//调用构造器用于初始化对象
     System.out.println(a+"插入成功**************************");
    List<Sal> list=new SalDaoImpl().selectAll();
       //查询所有，使用迭代器集合遍历
      Iterator<Sal> it=list.iterator();
      while(it.hasNext()) { 
       System.out.println(it.next());
      }
      System.out.println("**************************");
      Sal sal=new Sal();
      sal.setEmpno("10008");
      sal.setName("张三");
      sal.setCreateDate("2020-09-11");
      sal.setSal(4000);
      sal.setSalDecrease(4000);
      sal.setId(12);
     int b=new SalDaoImpl().update(sal);//id自增，所以设置为空，//调用构造器用于初始化对象。
      System.out.println(b+"更改成功**************************");
      int c=new SalDaoImpl().delete(9);//根据删除
      System.out.println(c+"删除成功**************************");
      List<Sal> list1=new SalDaoImpl().findByMap("10006","2020-09-11");
      for(Sal sal1 : list1) { 
       System.out.println(sal1.toString());
      }
     
     }

#### 测试结果 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 1]

#### 数据库结果 ####

![在这里插入图片描述][20200912105732648.png_pic_center]

> 更多源码案例，尽在源码客栈（微信公众号）  
> ![在这里插入图片描述][20200912105959726.jpg_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center]: /images/20210920/5deee0a6eec14758b2bdd79fd9e0df41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpcXo2NjY_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210920/654aadc12f004fceb2574acf1f77ea34.png
[20200912105732648.png_pic_center]: /images/20210920/6efe08cde6b545849ed40cbbfd5e8d51.png
[20200912105959726.jpg_pic_center]: /images/20210920/215a250612d34dbcbea30a832ab9fe0a.png