jdbc实现增删改查

//学生实体类

package com.work.jdbc;

public class StudentEntity {
    private int stuNo;
    private String stuName;
    private String stuSex;
    private String stuDate;
    private String tel;
    private String address;
    private int gradeid;

    public int getStuNo() {
        return stuNo;
    }

    public void setStuNo(int stuNo) {
        this.stuNo = stuNo;
    }

    public String getStuName() {
        return stuName;
    }

    public void setStuName(String stuName) {
        this.stuName = stuName;
    }

    public String getStuSex() {
        return stuSex;
    }

    public void setStuSex(String stuSex) {
        this.stuSex = stuSex;
    }

    public String getStuDate() {
        return stuDate;
    }

    public void setStuDate(String stuDate) {
        this.stuDate = stuDate;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getGradeid() {
        return gradeid;
    }

    public void setGradeid(int gradeid) {
        this.gradeid = gradeid;
    }

    @Override
    public String toString() {
        return "StudentEntity [stuNo=" + stuNo + ", stuName=" + stuName + ", stuSex=" + stuSex + ", stuDate=" + stuDate
                + ", tel=" + tel + ", address=" + address + ", gradeid=" + gradeid + "]";
    }

}
 

package com.work.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Driver;

public class JdbcTest {
public static void main(String[] args) throws Exception {
    //queryAll();
    /*List<StudentEntity> queryAll = queryAll();
    for (StudentEntity studentEntity : queryAll) {
        System.out.println(studentEntity);
    }*/
    //query();
    //querys();
    //update();
    //delete();
    //insert();
}

//1、查询全部学生
 public static List<StudentEntity> queryAll() throws Exception{
     List<StudentEntity> student = new ArrayList<StudentEntity>();
     //加载数据库驱动
     Class.forName("com.mysql.jdbc.Driver");
        //获取连接对象
        //url 路径 数据库在哪里
        //user 填写的 是连接数据库的用户名 root
        //password 填写的 是连接数据库的密码 root
        //jdbc:mysql://localhost:3306/数据库名字
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "root");
        //3. sql语句执行
          // execute():用来执行sql语句
          // executeUpdate()：执行增删改语句
          // executeQuery(): 用来执行查询语句
          //String sql = "DELETE FROM student WHERE stuid='s001';";
        String sql = "select * from student ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet query = ps.executeQuery();
         // 结果集处理
          while (query.next()) {
              /*Object no = query.getObject("stuNo");
              Object name = query.getObject("stuName");
              Object sex = query.getObject("stuSex");
              Object date = query.getObject("stuDate");
              Object tel = query.getObject("tel");
              Object address = query.getObject("address");
              Object gradeid = query.getObject("gradeid");
              System.out.println("no:"+no+"\tname:"+name +"\tsex:"+sex+"\tDate"+date+"\ttel:"+tel+"\taddress:"+address+"\tgradeid"+gradeid);
              */
              //实体类查询
              StudentEntity se = new StudentEntity();
              se.setStuNo(query.getInt(1));
              se.setStuName(query.getString(2));
              se.setStuSex(query.getString(2));
              se.setStuDate(query.getString(2));
              se.setTel(query.getString(2));
              se.setAddress(query.getString(2));
              se.setGradeid(query.getInt(7));
              student.add(se);
        }
            // 5.关闭释放资源
             ps.close();
             connection.close();
             return student;
 }
//2、根据姓名进行模糊查询(查询所有姓王的同学的信息)
public static void query() throws Exception{
     Class.forName("com.mysql.jdbc.Driver");
       Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "root");
       String sql = "SELECT * FROM student WHERE stuName LIKE '%王%'";
       PreparedStatement ps = connection.prepareStatement(sql);
       ResultSet query = ps.executeQuery();
         while (query.next()) {
             Object no = query.getObject("stuNo");
              Object name = query.getObject("stuName");
              Object sex = query.getObject("stuSex");
              Object date = query.getObject("stuDate");
              Object tel = query.getObject("tel");
              Object address = query.getObject("address");
              Object gradeid = query.getObject("gradeid");
            System.out.println("no:"+no+"\tname:"+name +"\tsex:"+sex+"\tDate"+date+"\ttel:"+tel+"\taddress:"+address+"\tgradeid:"+gradeid);
        }
            ps.close();
            connection.close();
  }

//3、根据姓名+性别查询
public static void querys() throws Exception{
     Class.forName("com.mysql.jdbc.Driver");
      Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "root");
      String sql = "SELECT * FROM student WHERE stuName LIKE '%王%' and stuSex='男'";
      PreparedStatement ps = connection.prepareStatement(sql);
      ResultSet query = ps.executeQuery();
        while (query.next()) {
            Object no = query.getObject("stuNo");
              Object name = query.getObject("stuName");
              Object sex = query.getObject("stuSex");
              Object date = query.getObject("stuDate");
              Object tel = query.getObject("tel");
              Object address = query.getObject("address");
              Object gradeid = query.getObject("gradeid");
            System.out.println("no:"+no+"\tname:"+name +"\tsex:"+sex+"\tDate"+date+"\ttel:"+tel+"\taddress:"+address+"\tgradeid:"+gradeid);
        }
           ps.close();
           connection.close();
 }

//4、将万丽丽的性别改成男
public static void update() throws Exception{
     Class.forName("com.mysql.jdbc.Driver");
     Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "root");
     String sql = "UPDATE student SET stuSex='男' WHERE stuName='王丽丽'";
     PreparedStatement ps = connection.prepareStatement(sql);
          int num = ps.executeUpdate();
        if(num>0){
            System.out.println("修改成功！！！");
        }else{
            System.out.println("修改失败！！！");
        }
          ps.close();
          connection.close();
   }

//5、删除名字为老王的学生
public static void delete() throws Exception{
     Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "root");
    String sql = "delete from student where stuName='老王'";
    PreparedStatement ps = connection.prepareStatement(sql);
         int num = ps.executeUpdate();

       if(num>0){
           System.out.println("删除成功！！！");
       }else{
           System.out.println("删除失败！！！");
       }

         ps.close();
         connection.close();
  }
//6、新增一位学生
public static void insert() throws Exception{
     Class.forName("com.mysql.jdbc.Driver");
   Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myschool", "root", "root");
   String sql = "insert into student values(?,?,?,?,?,?,?)";
   PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, 12);
        ps.setObject(2, "王老五");
        ps.setObject(3, "男");
        ps.setObject(4, "2018-10-01");
        ps.setObject(5, "1934785665");
        ps.setObject(6, "澳门山庄");
        ps.setObject(7, 1);
        int num = ps.executeUpdate();
        if(num>0){
               System.out.println("新增成功！！！");
           }else{
               System.out.println("新增失败！！！");
           }
        ps.close();
        connection.close();
 }
}