java SQL语句增删改查

package cn.shichuang.dao;
import java.text.SimpleDateFormat;
import java.util.*;
import java.sql.*;
import java.util.Date;
public class Xiaofandb {
    //设备的增加 入库
    public static void insert() throws Exception {
        Scanner x = new Scanner(System.in);
        Shebei sb = new Shebei();
        Compy cm = new Compy();
        System.out.println("请输入设备编号");
        sb.setSid(x.nextInt());
        System.out.println("请输入设备名称");
        sb.setSname(x.next());
        System.out.println("请输入设备卡号");
        sb.setCard(x.next());
        System.out.println("请输入设备类型");
        sb.setType(x.next());
        System.out.println("请输入设备图片名称");
        sb.setSjpg(x.next());
        System.out.println("请输入设备生产商");
        sb.setBrand(x.next());
        System.out.println("请输入公司ID");
        cm.setCid(x.nextInt());
        System.out.println("请输入公司名称");
        cm.setName(x.next());
        System.out.println("请输入公司楼层");
        cm.setClou(x.next());
        System.out.println("请输入公司地址");
        cm.setCdress(x.next());
        System.out.println("请输入公司经纬度");
        cm.setCjw(x.next());
        String sql = "INSERT INTO shebei(sid,sname,card,stype,sjpg,brand) VALUES(?,?,?,?,?,?)"; //准备要执行的sql语句
        PreparedStatement pst = jdbc.getConnection1().prepareStatement(sql);
        pst.setInt(1, sb.getSid());
        pst.setString(2, sb.getSname());
        pst.setString(3, sb.getCard());
        pst.setString(4, sb.getType());
        pst.setString(5, sb.getSjpg());
        pst.setString(6, sb.getBrand());
        int i = pst.executeUpdate();
        System.out.println(i);
        String sql1 = "INSERT INTO compy(cid,cname,clou,cdress,cjw) VALUes(?,?,?,?,?)";
        PreparedStatement pst1 = jdbc.getConnection1().prepareStatement(sql1);
        pst1.setInt(1, cm.getCid());
        pst1.setString(2, cm.getName());
        pst1.setString(3, cm.getClou());
        pst1.setString(4, cm.getCdress());
        pst1.setString(5, cm.getCjw());
        int i1 = pst1.executeUpdate();
        System.out.println(i1);
        String sql2 = "INSERT INTO anz(sid,cid,date) VALUes(?,?,?)";
        PreparedStatement pst2 = jdbc.getConnection1().prepareStatement(sql2);
        pst2.setInt(1, sb.getSid());
        pst2.setInt(2, cm.getCid());
        Date ad = new Date();
        SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String str=df.format(ad);
        java.util.Date hire = df.parse(str);
        java.sql.Date  hr1 = new java.sql.Date(hire.getTime());
        System.out.println(hr1);
        pst2.setDate(3,hr1);
    }
    //设备的显示
    public static void findall() throws Exception {
        String sql = "SELECT a.sid,b.cid,sname,cname,stype,clou,cdress,cjw,sjpg,DATE FROM shebei a,compy b,anz c WHERE a.sid=c.sid AND b.cid=c.cid";
        PreparedStatement pst = jdbc.getConnection1().prepareStatement(sql);
        ResultSet rt = pst.executeQuery();
        while(rt.next()){
            System.out.println(rt.getInt(1)+"  "+rt.getInt(2)+"  "+rt.getString(3)+"  "+rt.getString(4)+"  "+rt.getString(5)+"  "+rt.getString(6)+"  "+rt.getString(7)+"  "+rt.getString(8)+"  "+rt.getString(9)+"  "+rt.getDate(10));

        }
    }
    //查找
    public static void find(int a) throws Exception {
        String sql = "SELECT a.sid,b.cid,sname,cname,stype,clou,cdress,cjw,sjpg,DATE FROM shebei a,compy b,anz c WHERE a.sid=c.sid AND b.cid=c.cid And a.sid=?";
        PreparedStatement pst = jdbc.getConnection1().prepareStatement(sql);
        pst.setInt(1,a);
        ResultSet rt = pst.executeQuery();
        while(rt.next()){
            System.out.println(rt.getInt(1)+"  "+rt.getInt(2)+"  "+rt.getString(3)+"  "+rt.getString(4)+"  "+rt.getString(5)+"  "+rt.getString(6)+"  "+rt.getString(7)+"  "+rt.getString(8)+"  "+rt.getString(9)+"  "+rt.getDate(10));

        }
        rt.close();
        pst.close();
    }
    //
    //分页
    public static void findall(int page,int pageSize) throws Exception {
        String sql = "SELECT a.sid,b.cid,sname,cname,stype,clou,cdress,cjw,sjpg,DATE FROM shebei a,compy b,anz c WHERE a.sid=c.sid AND b.cid=c.cid LIMIT ?,?";
        PreparedStatement pst = jdbc.getConnection1().prepareStatement(sql);
        pst.setInt(1,(page-1)*pageSize);
        pst.setInt(2,pageSize);
        ResultSet rt = pst.executeQuery();
        while(rt.next()){
            System.out.println(rt.getInt(1)+"  "+rt.getInt(2)+"  "+rt.getString(3)+"  "+rt.getString(4)+"  "+rt.getString(5)+"  "+rt.getString(6)+"  "+rt.getString(7)+"  "+rt.getString(8)+"  "+rt.getString(9)+"  "+rt.getDate(10));

        }
        rt.close();
        pst.close();
    }
    //删除
    public static void del(int a) throws Exception {
        String sql = "delete from anz  where sid = ?";
        String sql1 = "delete from shebei  where sid = ?";
        PreparedStatement pst = jdbc.getConnection1().prepareStatement(sql);
        pst.setInt(1,a);
        PreparedStatement pst1 = jdbc.getConnection1().prepareStatement(sql);
        pst1.setInt(1,a);
        pst1.close();
        pst.close();

    }
    //修改
    public static void alt(int a) throws Exception {
        String sql = "update anz set date=? where sid=?";
        PreparedStatement pst = jdbc.getConnection1().prepareStatement(sql);
        Date ad = new Date();
        SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String str=df.format(ad);
        java.util.Date hire = df.parse(str);
        java.sql.Date  hr1 = new java.sql.Date(hire.getTime());
        System.out.println(hr1);
        pst.setDate(1,hr1);
        pst.setInt(2,a);
        int i = pst.executeUpdate();
        pst.close();

    }
    //分页
    public static void select(int page,int pageSize) throws Exception {
        String sql1 = "SELECT * FROM day1 LIMIT ?,?";
        Connection con = jdbc.getConnection1();
        //创建执行sql的对象 statement
        PreparedStatement st = con.prepareStatement(sql1);
        st.setInt(1, (page - 1) * pageSize);
        st.setInt(2, pageSize);
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getString(1) + " " + rs.getInt(2));
        }
        jdbc.close(rs, st, con);
    }
        //菜单
    public static void Mean() throws Exception {
        Scanner x = new Scanner(System.in);
        int b = 0;
        while(true){
            System.out.println("1 安装设备");
            System.out.println("2 查看设备");
            System.out.println("3 查找设备");
            System.out.println("4 修改设备");
            System.out.println("5 分页查找");
            System.out.println("6 退出");
            int c = x.nextInt();
            switch (c){
                case 0:
                    b=1;
                    break;
                case 1:
                    insert();
                    break;
                case 2:
                    findall();
                    break;
                case 3:
                    System.out.println("请输入查找的设备号：");
                    int a = x.nextInt();
                    find(a);
                    break;
                case 4:
                    System.out.println("请输入修改的设备号：");
                    int a1 = x.nextInt();
                    alt(a1);
                case 5:
                    System.out.println("开始位置");
                    int a3 = x.nextInt();
                    System.out.println("分页大小");
                    int a4 = x.nextInt();
                    findall(a3,a4);
                    break;
            }
            if(b==1){
                break;
            }
        }
    }

    public static void main(String[] args) throws Exception{
        Mean();

    }
}