JDBC与数据库的链接、JDBC的全查、添加、删除、修改操作

首先要有链接参数、自己定义的数据库

1.MySQL 5.0版本：
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/databaseName?useSSL=false&serverTimezone=UTC"
username="root"
password="123"

2.MySQL 8.0版本：
driver="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/databaseName?useSSL=false&serverTimezone=UTC"

注：3306/后面跟的是自己的数据库名字
username="root"
password="123"

JDBC操作数据库的步骤
1.首先在项目根目录创建lib文件夹，放入jdbc驱动程序，然后Add As Library

 

2.加载数据库驱动

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

    public text() {

    }

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

​
Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);

​

4.使用Connection创建PreparedStatement语句对象执行sql语句---使用PreparedStatement对象执行SQL语句

 

  PreparedStatement pstm = con.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();

5:进行封装 

 List<Student> studentList=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");
            String stuAddr = rs.getString("stuAddr");
            //把以上数据封装到Student对象中

        Student student=new Student(); //一行数据就封装成了一个Student对象

        student.setStuId(stuId);
        student.setStuName(stuName);
        student.setStuSex(stuSex);
        student.setStuAge(stuAge);
        student.setStuAddr(stuAddr);
 //把当前行封装后的Student对象装载到 List集合中

 studentList.add(student);

6.操作判断--增删改返回的是影响的行数（返回值是int），只有查询获得结果集(返回值ResultSet)

 

int n = pstm.executeUpdate();
            //6判断影响的行数 n > 0 表示插入成功，否则插入失败

            if (n > 0) {
                System.out.println("插入数据成功");
            } else {
                System.out.println("插入数据失败");
            }

7.回收资源

            if (pstm != null) {
                pstm.close();
            }

            if (con != null) {
                con.close();
            }

 

相关代码：

 JDBC 全查操作：

mySQL全查语句

 SELECT * FROM 表名 WHERE 字段名='字段值';

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package com.hp.lib;

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 org.junit.Test;

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

    public text() {

    }

    @Test
    public void testSelectAll() throws Exception {

        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);
        String sql = "select * from student";

        PreparedStatement pstm = con.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();

        List<Student> studentList=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");
            String stuAddr = rs.getString("stuAddr");
            //把以上数据封装到Student对象中

        Student student=new Student(); //一行数据就封装成了一个Student对象

        student.setStuId(stuId);
        student.setStuName(stuName);
        student.setStuSex(stuSex);
        student.setStuAge(stuAge);
        student.setStuAddr(stuAddr);
 //把当前行封装后的Student对象装载到 List集合中

 studentList.add(student);

  }
 System.out.println(studentList);
        if (rs != null) {
            rs.close();
        }

        if (pstm != null) {
            pstm.close();
        }

        if (con != null) {
            con.close();
        }
    }

    }

JDBC添加操作

mySQL添加语句

INSERT INTO 表名（字段名）VALUES(字段值)；“字符及字符串需用单引号或双引号括起来”

package com.hp.lib;


import org.junit.Test;

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

public class TianJia{
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/kedo";
    private String user = "root";
    private String password = "root";

    public TianJia() {

    }

    @Test
    public void testSelectAll() throws Exception {

        Class.forName(driver);

        Connection con = DriverManager.getConnection(url, user, password);

            //4.通过连接对象，获取SQ预处理对象
            String sql = "insert into student(stuName,stuSex,stuAge,stuAddr)values(?,?,?,?)";


            PreparedStatement pstm = con.prepareStatement(sql);
            //实际开发是前端页面传递过来的数据，此处我们直接模拟一组数据

            Student student = new Student();
            student.setStuName("小李子");
            student.setStuSex("男");
            student.setStuAge(18);
            student.setStuAddr("南阳");
            //5.1预处理对象的sql语句有 ? 所以需要进行传参

            pstm.setObject(1, student.getStuName());
            pstm.setObject(2, student.getStuSex());
            pstm.setObject(3, student.getStuAge());
            pstm.setObject(4, student.getStuAddr());
            //5.2执行更新（增删改都叫做数据库的更新,更新返回的是影响的行数）

            int n = pstm.executeUpdate();
            //6判断影响的行数 n > 0 表示插入成功，否则插入失败

            if (n > 0) {
                System.out.println("插入数据成功");
            } else {
                System.out.println("插入数据失败");
            }
            //7释放资源

            if (pstm != null) {
                pstm.close();
            }

            if (con != null) {
                con.close();
            }
        }
    }

 

JDBC删除操作

mySQL删除语句 

DELETE FROM 表名 WHERE 字段名=字段值 “字符及字符串需用单引号或双引号括起来”

package com.hp.lib;

import org.junit.Test;

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

public class ShanChu {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/kedo";
    private String user = "root";
    private String password = "root";

    public ShanChu() {

    }

    @Test
    public void testSelectAll() throws Exception {

        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);
        String sql = "delete  from student where stuId=?";
        PreparedStatement pstm = con.prepareStatement(sql);
        //5.1预处理对象的sql语句有 ? ,所以需要传参

        int stuId = 3; //实际开发是从前端页面获取要删除的id

        pstm.setObject(1, stuId);
        //5.2执行更新操作（增删改都是更新操作，返回的结果是影响的行数）

        int n = pstm.executeUpdate();
        //6判断影响的行数 n>0 表示删除成功，否则删除失败

        if (n > 0) {
            System.out.println("删除成功");
        } else {
            System.out.println("删除失败");
        }
        //7释放资源

        if (pstm != null) {
            pstm.close();
        }
        if (con != null) {
            con.close();

        }
    }
}

JDBC修改操作

mySQL修改语句 

  UPDATE 表名 SET 字段名=字段值(改成后字段值) WHERE 字段名='字段值'(条件字段);

package com.hp.lib;

import org.junit.Test;

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

public class Update {
    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/kedo";
    private String user = "root";
    private String password = "root";

    public Update() {

    }

    @Test
    public void testUpdate() throws Exception {

        Class.forName(driver);
        Connection con = DriverManager.getConnection(url, user, password);

        String sql = "update  student set stuName=? where stuId=?";

        PreparedStatement pstm = con.prepareStatement(sql);

       pstm.setObject(1,"小小");
       pstm.setObject(2,4);
        //5.2执行更新操作（增删改都是更新操作，返回的结果是影响的行数）

        int n = pstm.executeUpdate();
        //6判断影响的行数 n>0 表示删除成功，否则删除失败

        if (n > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
        //7释放资源

        if (pstm != null) {
            pstm.close();
        }
        if (con != null) {
            con.close();

        }

    }



}

注：得创建自己的数据库和使用自己的数据库