数据库的连接及长用的SQL语句

最新推荐文章于 2023-03-08 22:22:58 发布

小资女孩向前冲冲冲冲

最新推荐文章于 2023-03-08 22:22:58 发布

阅读量395

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/Xyzlyz/article/details/122107900

版权

package com.lyz.commodity.dao;

import com.lyz.commodity.commodityModel.CommodityModel;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class commoditySql {
    String url="jdbc:mysql://localhost/shopping";
    String name="root";
    String pass="123456";
    Connection connection=null;
    Statement statement=null;
    ResultSet resultSet=null;
    public Connection getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection(url,name,pass);


        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public Connection getConnection(String url,String name,String pass){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection= DriverManager.getConnection(url,name,pass);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    //这个是查询所有
    public List<CommodityModel> selecCommodity() throws Exception{
        String sql="select * from commodity ";
        statement= getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        List<CommodityModel> list=new ArrayList<>();
        CommodityModel com=null;
        while (resultSet.next()){
            com=new CommodityModel();
            com.setCid(resultSet.getInt(1));
            com.setCname(resultSet.getString(2));
            com.setCcount(resultSet.getInt(3));
            com.setCtype(resultSet.getString(4));
            com.setCprice(resultSet.getDouble(5));
            com.setCurl(resultSet.getString(6));
            list.add(com);
        }
        connection.close();

        return list;

    }

    //这个是分页
    public List<CommodityModel> Commoditylimit(int page,int size) throws Exception{
        String sql="select * from commodity limit "+page+" , "+size;
        System.out.println(sql);
        statement= getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        ArrayList<CommodityModel> list = new ArrayList<>();

        while (resultSet.next()){
            CommodityModel com=new CommodityModel();
            com.setCid(resultSet.getInt(1));
            com.setCname(resultSet.getString(2));
            com.setCcount(resultSet.getInt(3));
            com.setCtype(resultSet.getString(4));
            com.setCprice(resultSet.getDouble(5));
            com.setCurl(resultSet.getString(6));
            list.add(com);
        }
        connection.close();
        for (CommodityModel commodityModel : list) {
            System.out.println(commodityModel.toString());
        }
        return list;

    }




    public void Addcommodity(CommodityModel commodityModel) throws Exception{
        String sql="insert into commodity (cname,ccount,ctype,cprice,curl) values ('"+commodityModel.getCname()+"','"+commodityModel.getCcount()+"'," +
                "'"+commodityModel.getCtype()+"','"+commodityModel.getCprice()+"','"+commodityModel.getCurl()+"')";
        System.out.println(sql);
        statement= getConnection().createStatement();
        statement.execute(sql);
        connection.close();

    }

    public void delete(int id)throws Exception{
        String sql="delete from commodity where cid="+id;
        System.out.println(sql);
        statement= getConnection().createStatement();
        statement.executeUpdate(sql);
        connection.close();


       /// resultSet=statement.executeQuery(sql);
    //    ArrayList<CommodityModel> list = new ArrayList<>();



    }

public CommodityModel byID(int id) throws Exception{
    String sql="select * from commodity where cid="+id;
    statement= getConnection().createStatement();
    resultSet=statement.executeQuery(sql);
    List<CommodityModel> list=new ArrayList<>();
    CommodityModel com=null;
    while (resultSet.next()){
        com=new CommodityModel();
        com.setCid(resultSet.getInt(1));
        com.setCname(resultSet.getString(2));
        com.setCcount(resultSet.getInt(3));
        com.setCtype(resultSet.getString(4));
        com.setCprice(resultSet.getDouble(5));
        com.setCurl(resultSet.getString(6));
        //list.add(com);
    }
    connection.close();

    return com;

}


    public void updataCom(CommodityModel commodityModel) throws Exception{
        String sql="update commodity set cname=?, ccount=?, ctype=?, cprice=?, curl=? where cid=?";
        PreparedStatement ppst = getConnection().prepareStatement(sql);
        ppst.setString(1,commodityModel.getCname());
        ppst.setInt(2,commodityModel.getCcount());
        ppst.setString(3,commodityModel.getCtype());
        ppst.setDouble(4,commodityModel.getCprice());
        ppst.setString(5,commodityModel.getCurl());
        ppst.setInt(6,commodityModel.getCid());
        ppst.executeUpdate();
        ppst.close();
        connection.close();
        System.out.println("修改成功！");
    }





}//这个是最后的