数据库增删改查

来源：互联网发布：小世界网络的度分布编辑：程序博客网时间：2024/05/17 09:05

//在数据库连接的基础上进行操作
//新建user类
package cn.edu.hpu.model;
public class User {
//定义私有变量

private int id; private String username;
private String password;
//定义变量后Alt+Shift+s显示出方法，选中Generate Getters and Setters直接出来以下方法
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
//创建接口
package cn.edu.hpu.service;
import java.util.List;
import cn.edu.hpu.model.User;
public interface Manager {
// 添加操作
public boolean addUser(User user);
// 删除操作
public boolean delUserById(int id);
// 修改操作
public boolean updateUserById(int id,User user);
// 查询操作
public List<User> getAllUser();
}

//接口实现

package cn.edu.hpu.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.spi.DirStateFactory.Result;

import com.mysql.jdbc.ResultSet;

import cn.edu.hpu.model.User;
import cn.edu.hpu.util.DBOperator;

public class ManagerImpl implements Manager{

@Override
//实现添加操作
public boolean addUser(User user) {
  // TODO Auto-generated method stub

  boolean flag=false;
  String sql="insert into user(username,password) value(?,?)";
  Connection conn=DBOperator.getconnection();

  try {
   PreparedStatement pst=conn.prepareStatement(sql);
   pst.setString(1, user.getUsername());
   pst.setString(2, user.getPassword());
   pst.executeUpdate();
   flag=true;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return flag;
}
//实现删除操作
@Override
public boolean delUserById(int id) {
  // TODO Auto-generated method stub

  boolean flag=false;
  String sql="delete from user where id= "+id;
  Connection conn=DBOperator.getconnection();

  try {
   PreparedStatement pst=conn.prepareStatement(sql);
   //数据更新操作
   pst.executeUpdate();
    flag=true;
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

  return flag;
}
//修改操作
@Override
public boolean updateUserById(int id, User user) {
  // TODO Auto-generated method stub

  boolean flag = true ;
  String sql = "update user set username=? , password=? where id = "+id ;
  Connection conn = DBOperator.getconnection() ;
  try {
   PreparedStatement pst = conn.prepareStatement(sql);

   pst.setString(1, user.getUsername());
   pst.setString(2, user.getPassword());
   //数据更新
   pst.executeUpdate() ;
   flag = true ;

  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return flag ;
}
//查询操作
@Override
public List<User> getAllUser() {
  // TODO Auto-generated method stub
  List<User>alluser = new ArrayList<>() ;
  Connection conn = null ;
  Statement st = null ;
  ResultSet rs = null ;

  String sql = "select * from user";
  conn = DBOperator.getconnection() ;
  try {
   st = conn.createStatement() ;
   rs = (ResultSet) st.executeQuery(sql);

   while(rs.next()){
    User user = new User () ;
    user.setId(rs.getInt("id"));
    user.setUsername(rs.getString("username"));
    user.setPassword(rs.getString("password"));

    alluser.add(user);

   }
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }


  return alluser;
}

}

//对接口实现的测试

//连接测试成功后进行其他连接测试

package cn.edu.hpu.test;

import java.sql.Connection;
import java.util.List;

import cn.edu.hpu.model.User;
import cn.edu.hpu.service.Manager;
import cn.edu.hpu.service.ManagerImpl;
import cn.edu.hpu.util.DBOperator;

public class test {

public static void main(String[] args) {
// TODO Auto-generated method stub

//连接测试
// Connection conn=DBOperator.getconnection();

//  if(conn!=null){
//   System.out.println("ok!");
//  }
//  else{
//   System.out.println("error!");
//  }
  //添加测试

//  Manager mng=new ManagerImpl();
//  User user=new User();
//  user.setUsername("whh");
//  user.setPassword("1223");

// boolean flag=mng.addUser(user);

//  if(flag){
//   System.out.println("ok!");
//  }
//  else{
//   System.out.println("error!");
//  }

//数据添加

//  Manager mng=new ManagerImpl();
//  User user=new User();
//  user.setUsername("whh");
//  user.setPassword("1223");

//添加数据循环的次数，控制添加数据的条数
// for(int i=1;i<20;i++)
// mng.addUser(user);

  //删除测试
//  Manager mng=new ManagerImpl();
//  boolean flag=mng.delUserById(1);

//  if(flag){
//   System.out.println("ok");
//  }else{
//   System.out.println("error");
//  }

//修改测试

//  Manager mng=new ManagerImpl();
//  User user=new User();
//  user.setUsername("wa");
//  user.setPassword("789");

// boolean flag = mng.updateUserById(2, user);

//  if(flag){
//   System.out.println("ok");
//  }else{
//   System.out.println("error");
//  }

//查询测试

Manager mng=new ManagerImpl();
List<User>alluser=mng.getAllUser();

  for(int i=0;i<alluser.size();i++){
   User user=alluser.get(i);
   System.out.println("id="+user.getId()+" "+"username="+user.getUsername()+" "+"password="+user.getPassword());
  }

}

}

0 0