2021-04-10

使用JDBC对数据库进行增删改查操作

1.增加实例操作

package jdbc_project;

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

public class add {
    //添加实例
    public static void  add() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //使用DriverManager来连接数据库
        Connection connection= DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/study?useSSL=true","root","root");
        //创建sql语句
        String sql="INSERT INTO tb_stu VALUES(DEFAULT,'小花','女','2000-10-10','北京市','huahua@163.com',152295534,20)";
        //创建预处理对象
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //发送并执行SQL语句(执行增删改语句),返回受影响行数
        int num=preparedStatement.executeUpdate();
        if (num>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
        //释放资源
        preparedStatement.close();
        connection.close();


    }
}

2.删除操作

package jdbc_project;

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

public class delete {
    //删除实例
    public static void  delete() throws ClassNotFoundException, SQLException {
        //连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection= DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/study?useSSL=true","root","root");
        //创建sql语句
        String sql="delete from tb_stu where stu_name='强哥'";
        //创建预处理对象
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //发送并执行sql语句，返回受影响行数
        int num=preparedStatement.executeUpdate();
        if(num>0){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
        //释放资源
        preparedStatement.close();
        connection.close();
    }
}

3.修改操作

package jdbc_project;

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

public class update {

    //修改操作
    public static void update() throws ClassNotFoundException, SQLException {
        //连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection= DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/study?useSSL=true","root","root");
        //创建sql语句
        String sql="update tb_stu set stu_address='上海市',stu_age=30 where stu_name='崽崽'";
        //创建预处理对象
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //发送并执行sql语句，返回影响行数
        int num=preparedStatement.executeUpdate();
        if(num>0){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
        //释放资源
        preparedStatement.close();
        connection.close();
    }
}

4.查询数据库一张表的所有数据

package jdbc_project;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class FindAll {
    //查看数据库所有信息
    public  static void findAll() throws ClassNotFoundException, SQLException {
        //连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection= DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/study?useSSL=true","root","root");
        //创建执行SQL语句的对象
        Statement statement=connection.createStatement();
        //创建sql语句
        String sql="select * from tb_stu";
        /**
        *创建执行sql语句的对象和创建与处理对象可替换使用
       * 创建预处理对像
        *PreparedStatement preparedStatement=connection.prepareStatement(sql);
        */
        //创建结果集
        ResultSet rs=statement.executeQuery(sql);
        while(rs.next()) {
            int stuid = rs.getInt(1);
            String stuname = rs.getString(2);
            String stusex = rs.getString(3);
            String birth = rs.getString(4);
            String address = rs.getString(5);
            String email = rs.getString(6);
            int phone = rs.getInt(7);
            int age = rs.getInt(8);
            System.out.println(
                    "stu_id:"+stuid+",stu_name:"+stuname+",stu_sex:"+stusex+",stu_birth"+birth
                            +",stu_address:"+address+",stu_email:"+email+",stu_phone:"+phone+",stu_age:"+age
            );
        }
        //释放资源
        rs.close();
        statement.close();
        connection.close();

    }
    

}

5.查询数据库一张表的一条数据

package jdbc_project;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class FindById {
    //查找一条数据
    public static void findDBById() throws ClassNotFoundException, SQLException {
        //创建链接
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection
                ("jdbc:mysql://localhost:3306/study?useSSL=true", "root", "root");
        //创建执行sql语句的对象
        Statement statement = connection.createStatement();
        //创建sql语句
        String sql = "select stu_name,stu_sex,stu_age from tb_stu where stu_id=10";
        //创建结果集
        ResultSet rs = statement.executeQuery(sql);
        if (rs.next()) {
            String name = rs.getString(1);
            String sex = rs.getString(2);
            int age = rs.getInt(3);
            System.out.println("stu_name:" + name + ",stu_sex:" + sex + ",stu_age:" + age);
        }
        //释放资源
        rs.close();
        statement.close();
        connection.close();
    }
}

操作步骤：

    /**
     * 对数据库进行操作（增删改查）：
     * 操作步骤：
     *      1.连接数据库
     *      2.创建SQL语句
     *      3.创建预处理对象（通过连接对象和Sql语句进行创建）PreparedStatement
     *      4.发送并执行SQL语句
     *          4.1 返回值有以下两种
     *                (1) 执行增、删、改语句返回的是数据库的受影响行数(executeUpdate)
     *                (2) 执行查询语句返回的是：结果集(executeQuery)
     *      5.根据执行结果进行处理
     *      6.释放资源
     */