MySQl 数据库与java的增删改查

1：首先要在我们的Mysql数据中建库，建表

代码如下：

建库语句：
create database school；
建表语句：
CREATE TABLE school(
stuID int PRIMARY KEY auto_increment,
stuName VARCHAR(24),
stuSex VARCHAR(4),
stuAge int

);
INSERT INTO school(stuName,stuSex,stuAge)VALUES
('小明','男',18),
('小红','女',18),
('小兰','女',18),
('小黑','男',19)

在我们的打java代码段的应用程序中：

创建一个包：

里面建个java class文件：

定义我们在Mysql中的数据名称

代码段：

package com.hp.test;

public class Student {
    private  int stuID;
    private  String  stuName;
   private  String stuSex;
   private int stuAge;

    public Student() {

    }

    public int getStuID() {
        return stuID;
    }

    public String getStuName() {
        return stuName;
    }

    public String getStuSex() {
        return stuSex;
    }

    public int getStuAge() {
        return stuAge;
    }

    public Student(int stuID, String stuName, String stuSex, String stuAge) {
        this.stuID = stuID;
        this.stuName = stuName;
        this.stuSex = stuSex;
        this.stuAge = Integer.parseInt(stuAge);
    }

    public void setStuID(int stuID) {
        this.stuID = stuID;
    }

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

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

    public void setStuAge(String stuAge) {
        this.stuAge = Integer.parseInt(stuAge);
    }

    @Override
    public String toString() {
        return "Student{" +
                "stuID=" + stuID +
                ", stuName='" + stuName + '\'' +
                ", stuSex='" + stuSex + '\'' +
                ", stuAge='" + stuAge + '\'' +
                '}';
    }

}

在写增删改查代码段之前我们首先要把驱动jar包引入进来

jddc操作数据库的步骤

1.首先在项目根目录创建lib文件夹，放入jdbc驱动程序，然后Add As Library

//2.加载数据库驱动

//3.使用驱动管理器来获得连接---获得一个数据库连接对象Connection

//4.使用Connection创建PreparedStatement预处理对象---PreparedStatement对象可以 执行带 ? 的sql语句

//5.使用PreparedStatement对象执行SQL语句,获得ResultSet结果集对象

6.操作判断--增删改返回的是影响的行数（返回值是int），只有查询获得结果集(返回值 ResultSet) //让结果集的游标不断的往下移动，直到没有数据的时候结束循环

//7.回收资源,先关闭rs结果集对象 再pstm预处理对象 最后con连接对象

查找代码如下：

package com.hp.test;

import org.junit.Test;

import javax.swing.*;
import java.awt.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentList{
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/text";
    private String username = "root";
    private String password = "root";



    @Test
    public void  text() throws Exception{
       Class.forName(driver);
        Connection con= DriverManager.getConnection(url,username,password);
       String sql = "select * from school";
       PreparedStatement pstm=con.prepareStatement(sql);
         ResultSet rs= pstm.executeQuery();
        List<Student> studentlList=new ArrayList<>();
           while (rs.next()){
                int stuId=rs.getInt("stuID");
             String stuName= rs.getString("stuName");
             String stuSex= rs.getString("stuSex");
                int  stuAge=   rs.getInt("stuAge");

               Student student=new Student();
                 student.setStuID(stuId);
             student.setStuName(stuName);
            student.setStuSex(stuSex);
            student.setStuAge(String.valueOf(stuAge));
             studentlList.add(student);
          }
        System.out.println(studentlList);
           if(rs!=null){
               rs.close();
           }
           if(pstm!=null){
               pstm.close();
           }
           if(con!=null){
               con.close();
           }
    }



}

删除代码如下：

package com.hp.test;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestDelete {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/text";
    private String username = "root";
    private String password = "root";
    @Test
    public   void  select() throws Exception {
        Class.forName(driver);
   Connection con= DriverManager.getConnection(url,username,password);
        String sql="delete from school where stuID=?";
        PreparedStatement ptem= con.prepareStatement(sql);
        int stuID=2;
        ptem.setObject(1,stuID);
        int n=ptem.executeUpdate();
        if(n>0){
            System.out.println("删除成功");
        }else{
            System.out.println("删除失败");
        }
        if(con!=null){
            con.close();;
        }
        if(ptem!=null){
            ptem.close();
        }
    }
}

增加代码如下：

package com.hp.test;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestAdd {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/text";
    private String username = "root";
    private String password = "root";
   @Test
    public void text() throws Exception {
       Class.forName(driver);
        Connection con= DriverManager.getConnection(url,username,password);
       String sql="INSERT INTO school(stuName,stuSex,stuAge)VALUES(?,?,?)";
       PreparedStatement pstm= con.prepareStatement(sql);
          Student student=new Student();
          student.setStuName("小李");
           student.setStuSex("男");
           student.setStuAge(String.valueOf(18));
           pstm.setObject(1,student.getStuName());
            pstm.setObject(2,student.getStuSex());
        pstm.setObject(3,student.getStuAge());

      int n= pstm.executeUpdate();

        if(n>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
        if (pstm!=null){
            pstm.close();
        }
        if (con!=null){
            con.close();
        }



 }


}

修改代码如下：

package com.hp.test;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class TestUpdate {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/text";
    private String username = "root";
    private String password = "root";
    @Test
    public void update() throws Exception {
        Class.forName(driver);
        Connection con= DriverManager.getConnection(url,username,password);
        String sql="update school  set stuName=?, stuSex=? where stuID=?";
        PreparedStatement ptem= con.prepareStatement(sql);
        ptem.setObject(1,"攀攀");
        ptem.setObject(2,"女");
        ptem.setObject(3,1);
       int n=ptem.executeUpdate();
       if(n>0){
           System.out.println("修改成功");
       }else{
           System.out.println("修改失败");
       }
       if(con!=null){
           con.close();
       }
       if(ptem!=null){
           ptem.close();
       }
    }
}