用户中SQL长用的语句

最新推荐文章于 2022-08-10 08:29:04 发布

小资女孩向前冲冲冲冲

最新推荐文章于 2022-08-10 08:29:04 发布

阅读量150

点赞数

分类专栏： jsp 文章标签： sql mysql 数据库

本文链接：https://blog.youkuaiyun.com/Xyzlyz/article/details/122170088

版权

jsp 专栏收录该内容

6 篇文章

订阅专栏

这是一个Java实现的数据库操作类，包含了连接数据库、查询所有用户、用户登录、分页查询、用户注册、商品上传、删除用户、获取用户详情及更新用户信息等方法。主要使用了JDBC进行数据库交互，涉及用户信息管理和商品信息管理的基本操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

package com.lyz.user.dao;

import com.lyz.commodity.commodityModel.CommodityModel;
import com.lyz.user.usermodel.Userinfo;

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

//用的SQL
public class userSql {
    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<Userinfo> selectAll()throws Exception{
        System.out.println("查所有");
        String sql="select *from userinfo";

        statement=getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        List<Userinfo> list=new ArrayList();
        while (resultSet.next()){
            Userinfo userinfo=new Userinfo();
            userinfo.setUid(resultSet.getInt(1));
            userinfo.setUname(resultSet.getString(2));
            userinfo.setUpass(resultSet.getString(3));
            userinfo.setUsex(resultSet.getString(4));
            list.add(userinfo);
        }
        connection.close();

        return list;

    }


    public int allcount()throws Exception{
        System.out.println("查所有");
        String sql="select *from userinfo";

        statement=getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        List<Userinfo> list=new ArrayList();
        while (resultSet.next()){
            Userinfo userinfo=new Userinfo();
            userinfo.setUid(resultSet.getInt(1));
            userinfo.setUname(resultSet.getString(2));
            userinfo.setUpass(resultSet.getString(3));
            userinfo.setUsex(resultSet.getString(4));
            list.add(userinfo);
        }
        connection.close();
        int count=list.size();

        return count;

    }






//登录

    public boolean islogin(String uname,String upass) throws Exception{
        String sql="select * from userinfo where uname='"+uname+"' and upass='"+upass+"'";
        System.out.println(sql);
        statement=getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        boolean b=false;
        while (resultSet.next()){
            b=true;
        }
        System.out.println(b);
        connection.close();
        return b;
    }

//分页
    public List<Userinfo> selectlimi(int page,int size)throws Exception{
        System.out.println("分页");
        String sql="select *from userinfo limit "+page+","+size;

        statement=getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        List<Userinfo> list=new ArrayList();
        while (resultSet.next()){
            Userinfo userinfo=new Userinfo();
            userinfo.setUid(resultSet.getInt(1));
            userinfo.setUname(resultSet.getString(2));
            userinfo.setUpass(resultSet.getString(3));
            userinfo.setUsex(resultSet.getString(4));
            list.add(userinfo);
        }
        connection.close();
        return list;
    }

    public Userinfo registerUser(Userinfo userinfo) throws Exception{
        String sql="insert into userinfo (uname,upass,usex) values ('"+userinfo.getUname()+"','"+userinfo.getUpass()+"','"+userinfo.getUsex()+"')";
        statement=getConnection().createStatement();
       statement.executeUpdate(sql);
       connection.close();


        return userinfo;
    }



    public List<Userinfo> upload(String cname,int ccount, String ctype, int cprice, String curl)throws Exception{
        System.out.println("插入");
        String sql="insert into commodity set ";

        statement=getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        List<Userinfo> list=new ArrayList();
        while (resultSet.next()){
            Userinfo userinfo=new Userinfo();
            userinfo.setUid(resultSet.getInt(1));
            userinfo.setUname(resultSet.getString(2));
            userinfo.setUpass(resultSet.getString(3));
            userinfo.setUsex(resultSet.getString(4));
            list.add(userinfo);
        }
        connection.close();
        return list;
    }


public void deleteUser(int id) throws Exception{

    String sql="delete from userinfo where uid="+id;
    System.out.println(sql);

    statement=getConnection().createStatement();
   statement.execute(sql);

}



    public Userinfo byID(int id) throws Exception{

        String sql="select * from userinfo where uid="+id;
        System.out.println(sql);
        statement=getConnection().createStatement();
        resultSet=statement.executeQuery(sql);
        Userinfo userinfo=null;
     //   List<Userinfo> ls=new ArrayList<>();
        while (resultSet.next()){
            userinfo=new Userinfo();
            userinfo.setUid(resultSet.getInt(1));
            userinfo.setUname(resultSet.getString(2));
            userinfo.setUpass(resultSet.getString(3));
            userinfo.setUsex(resultSet.getString(4));
          //  ls.add(userinfo);


        }
        connection.close();


        return userinfo;


    }

public void updaUser(Userinfo userinfo) throws Exception{
        //这个是修改
    String sql="update userinfo set uname=?,upass=?,usex=? where uid=?";
    System.out.println(sql);
//    statement=getConnection().createStatement();

    PreparedStatement upuser= getConnection().prepareStatement(sql);

    upuser.setString(1,userinfo.getUname());
    upuser.setString(2,userinfo.getUpass());
    upuser.setString(3,userinfo.getUsex());
    upuser.setInt(4,userinfo.getUid());
    upuser.executeUpdate();
    upuser.close();
    connection.close();
}


//    public void updaUser(Userinfo userinfo) throws Exception{
//        String sql="update userinfo set uname=?, upass=?, usex=? where uid=?";
//        PreparedStatement ppst = getConnection().prepareStatement(sql);
//        ppst.setString(1,userinfo.getUname());
//        ppst.setString(2,userinfo.getUpass());
//        ppst.setString(3,userinfo.getUsex());
//
//       ppst.setInt(4,userinfo.getUid());
//        ppst.executeUpdate();
//        ppst.close();
//        connection.close();
//        System.out.println("修改成功！");
//    }



}//这个是最后的一个