jdbc连数据库增删改查

斜刘海遮住右眼的眼屎

已于 2023-05-30 14:44:51 修改

阅读量71

点赞数

文章标签： mysql java

于 2023-04-05 01:38:42 首次发布

本文链接：https://blog.csdn.net/weixin_65450526/article/details/129964350

版权

先创建一个工具包Base

package come.ulit;

import java.sql.*;

public class BaseDao {
    
    private static final String  DRIVER="com.mysql.jdbc.Driver";//驱动包路径(该变量只需要赋值一次所以使用final)
    private static final String  URL="jdbc:mysql://localhost:3306/数据库名字";//连接mysql数据库(该变量只需要赋值一次所以使用final)
    private static final String  UID="root";
    private static final String  PWO="123456";
    protected  Connection conn;//创建连接(该连接只需在本类和本类的子类访问所以使用protected修饰符)
    protected PreparedStatement sta;//jdbc的一个接口 PreparedStatement 实例包含已编译的 SQL 语句
    protected ResultSet rs;//遍历数据
//使用static修饰符是因为该方法只需要在程序中执行一次且最早执行BaseDao 是一个父类 执行顺序静态代码块>代码块>构造方法  子类构造先调用 后执行  父类构造 后调用 先执行
    static{
        try {
            Class.forName(DRIVER);//加载驱动
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
//创建连接
    public  void open_db(){
        try {
            conn=DriverManager.getConnection(URL,UID,PWO);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
//关闭连接
    public void close_db(){
        try {
            if (rs != null) {
               conn.close();
            }
            if (sta != null) {
                sta.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

枚举

public enum Op_type {
    入库,出库
}

实现类

package come.entitiy;

import come.ulit.Op_type;

public class DepotIon {

    private Integer dId ;
    private Integer dCId ;
    private Integer dept_count ;
    private String in_Timent ;
    private Op_type op_type ;

    
   
    public String toString() {

        return this.getdId() + "\t" +
                this.getdCId() + "\t" +
                this.getDept_count() + "\t" +
                this.getIn_Timent() + "\t" +
                this.getOp_type() ;
    }

    public DepotIon(Integer dId, Integer dcid, Integer dept_count, String in_Timent, Op_type op_type) {
        this.setdId(dId);
        this.setdCId(dcid);
        this.setDept_count(dept_count);
        this.setIn_Timent(in_Timent);
        this.setOp_type(op_type);
    }
    public DepotIon( Integer dcid, Integer dept_count, String in_Timent, Op_type op_type) {

        this.setdCId(dcid);
        this.setDept_count(dept_count);
        this.setIn_Timent(in_Timent);
        this.setOp_type(op_type);
    }

    public Integer getdId() {
        return dId;
    }

    public void setdId(Integer dId) {
        this.dId = dId;
    }

    public Integer getdCId() {
        return dCId;
    }

    public void setdCId(Integer dCId) {
        this.dCId = dCId;
    }

    public Integer getDept_count() {
        return dept_count;
    }

    public void setDept_count(Integer dept_count) {
        this.dept_count = dept_count;
    }

    public String getIn_Timent() {
        return in_Timent;
    }

    public void setIn_Timent(String in_Timent) {
        this.in_Timent = in_Timent;
    }

    public Op_type getOp_type() {
        return op_type;
    }

    public void setOp_type(Op_type op_type) {
        this.op_type = op_type;
    }

    public DepotIon() {
    }

}

接口

public interface DepotIonDao{
    int add(DepotIon depotIon);
    int modify(DepotIon depotIon);
    int remove(Object id);
    List<DepotIon> finAnd();
    DepotIon finbyId(Object id);
}

实现方法类

package come.dao.impl;

import come.dao.DepotIonDao;
import come.entitiy.DepotIon;
import come.ulit.BaseDao;
import come.ulit.Op_type;

import java.sql.SQLException;
import java.util.*;

public class DepotIonDaolim extends BaseDao implements DepotIonDao {
    @Override
    public int add(DepotIon depotIon) {
       
        return count;
    }

    @Override
    public int modify(DepotIon depotIon) {

        return count;
    }

    @Override
    public DepotIon finbyId(Object id) {
        
        return DepotIon ;
    }

    @Override
    public List<DepotIon> finAnd() {
        
        return List;
    }

}

使用JDBC技术对数据库新增数据


    public int add(DepotIon depotIon) {
        String sql="insert into depotIon(dCId,dept_count,in_Timent,op_type) values(?,?,?,?);";
        int count=0;
        try {
            super.open_db();
            super.sta=conn.prepareStatement(sql);
            super.sta.setInt(1,depotIon.getdCId());
            super.sta.setInt(2,depotIon.getDept_count());
            super.sta.setString(3,depotIon.getIn_Timent());
            super.sta.setString(4,depotIon.getOp_type().toString());
            count= sta.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            super.close_db();
        }
        return count;
    }

使用jdbc技术修改数据库信息

public int modify(DepotIon depotIon) {
       String  sql="update depotIon set dCId=?,dept_count=?,in_Timent=?,op_type=?  where dId =?;";
        int count=0;
        try {
            super.open_db();
            super.sta=conn.prepareStatement(sql);
            super.sta.setInt(1,depotIon.getdCId());
            super.sta.setInt(2,depotIon.getDept_count());
            super.sta.setString(3,depotIon.getIn_Timent());
            super.sta.setString(4,depotIon.getOp_type().toString());
            super.sta.setInt(5,depotIon.getdId());
            count=super.sta.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            super.close_db();
        }
        return count;
    }

使用jdbc技术删除数据库信息

public int remove(Object id) {
        String sql="delete from DepotIon where did=?;";
        int count =0;
        try {
            super.open_db();
            super.sta=super.conn.prepareStatement(sql);
            super.sta.setInt(1,(Integer)id);
            count=super.sta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.close_db();
        }
        return count;
    }

使用jdbc技术对数据库使用主键查询

public DepotIon finbyId(Object id) {
        String sql="select * from DepotIon where did=?;";
        DepotIon depotIon=null;
        try {
            super.open_db();
            super.sta=conn.prepareStatement(sql);
            super.sta.setObject(1,id);
            super.rs=super.sta.executeQuery();
            while(super.rs.next()){
                depotIon=new DepotIon();
                depotIon.setdId(rs.getInt("did"));
                depotIon.setdCId(rs.getInt("dCId"));
                depotIon.setDept_count(rs.getInt("dept_count"));
                depotIon.setIn_Timent(rs.getString("in_Timent"));
                depotIon.setOp_type(Op_type.valueOf(rs.getString("op_type")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.close_db();
        }

        return depotIon;  
}

使用jdbc技术对数据库的全查

public List<DepotIon> finAnd() {
       String sql="select * from depotIon;";
        List<DepotIon> list=new ArrayList<>();
        try {
            super.open_db();
            super.sta= conn.prepareStatement(sql);
            super.rs= sta.executeQuery();
            while (rs.next()){
                DepotIon depotIon=new DepotIon();
                depotIon.setdId(rs.getInt("dId"));
                depotIon.setdCId(rs.getInt("dCId"));
                depotIon.setDept_count(rs.getInt("dept_count"));
                depotIon.setIn_Timent(rs.getString("in_Timent"));
                depotIon.setOp_type(Op_type.valueOf(rs.getString("op_type")));
                list.add(depotIon);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            super.close_db();
        }
        return list;
    }

程序入口

package come.test;
import come.entitiy.Cosmetic;
import come.dao.impl.CosmeticDaolim;
import come.dao.impl.DepotIonDaolim;
import come.entitiy.DepotIon;
import come.ulit.Op_type;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

public class Test {
    public static void main(String[] args) {
        Scanner sc=new Scanner(System.in);
        DepotIonDaolim depotIon=new DepotIonDaolim();

        //新增
        //depotIon.add(new DepotIon(4,2,"2020-10-18", Op_type.出库));
        //修改
       // depotIon.modify(new DepotIon(9,1,4,"2023-03-23",Op_type.出库));
        //删除
        //depotIon.remove(9);
        单查
        System.out.println(depotIon.finbyId(3));
        //全查
        List<DepotIon> list= depotIon.finAnd();
        for (DepotIon d:list) {
            System.out.println(d);
        }
    }
}

数据库SQL建表

create table DepotIon(
dId int auto_increment primary key comment '仓库编号',
dept_count int not null comment '操作数量',
in_Timent date not null comment '操作时间',
op_type enum('出库','入库') not null comment '操作类型'
);