[05]Java的JDBC编程(基础操作)

最新推荐文章于 2023-07-10 21:24:11 发布

Specium.

最新推荐文章于 2023-07-10 21:24:11 发布

阅读量124

点赞数 2

分类专栏： MySQL 文章标签： java maven spring

本文链接：https://blog.csdn.net/qq_33866936/article/details/130366750

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.新建项目

①创建项目,构建系统选择maven

②pom.xml里面,<dependencies>里面加上以下内容(针对MySQL5)

<dependencies>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.49</version>
    </dependency>

</dependencies>

2.建立数据库连接

①先新建类,匹配上要操作的表

package org.example.数据库连接使用;
//构造一个学生类,来接收数据库的数据
/**
 表结构
 CREATE TABLE student(
    id BIGINT,
    `name` VARCHAR(100),
    sn BIGINT,
    email VARCHAR(100),
    classId BIGINT
 )
 表内容
 INSERT INTO student VALUES
 (1,'吴一',20408030101,'wuyi@qq.com',1),
 (2,'王二',20408030102,'wanger@qq.com',1),
 (3,'杨三',20408030103,'yangsan@qq.com',1),
 (4,'李四',20408030104,'lisi@qq.com',1),
 (5,'赵五',20408030201,'zhaowu@qq.com',2),
 (6,'孙六',20408030202,'sunliu@qq.com',2)
 */
public class Student {
    private int id;
    private String name;
    private long sn;
    private String email;
    private int classId;

    @Override
    public String toString(){
        return "[" + id + ',' + name + ',' + sn + ',' + email + ',' + classId + "]\n";
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public long getSn() {
        return sn;
    }
    public void setSn(long sn) {
        this.sn = sn;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public int getClassId() {
        return classId;
    }
    public void setClassId(int classId) {
        this.classId = classId;
    }
}

②连接数据库,并查询数据

package org.example.数据库连接使用;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Scanner;

public class Demo01_Connection {
    //先定义数据源对象
    private static DataSource dataSource = null;
    // 数据库的用户名
    private static final String USER = "root";
    // 数据库的密码
    private static final String PASSWORD = "123456";
    // 数据库连接字符串(针对MySQL5)
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/java78?characterEncoding=utf-8&useSSL=false";

    public static void main(String[] args) {
        // 1. 初化始数据源
        MysqlDataSource myDataSource = new MysqlDataSource();
        // 2. 设置连接的参数
        myDataSource.setURL(URL);
        myDataSource.setUser(USER);
        myDataSource.setPassword(PASSWORD);
        // 3. 把构建好的Mysql数据源赋值给JDBC中的datasource
        dataSource = myDataSource;
        //下面三个声明在try{}之外,方便finally也能识别
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try{
            //通过数据源获取一个数据库连接
            connection = dataSource.getConnection();
            //接收用户输入的值
            System.out.print("请输入姓名:");
            Scanner scanner = new Scanner(System.in);
            String sn = scanner.next();
            //定义SQL语句
            String sql = "select * from student where name = ?";
            // 获取一个预处理对象
            statement = connection.prepareStatement(sql);
            // 处理占位符的值,要匹配上类型,参数1代表第几个占位符,后者表示要被替换的内容
            statement.setString(1, sn);
            // 4. 执行SQL
            resultSet = statement.executeQuery();
            if (resultSet.next()) { //如果读取到了内容
                // 创建表示结果的JAVA对象
                Student student = new Student();
                student.setId(resultSet.getInt(1));
                student.setName(resultSet.getString(2));
                student.setSn(resultSet.getLong(3));
                student.setEmail(resultSet.getString(4));
                student.setClassId(resultSet.getInt(5));
                System.out.println(student);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            // 依次关闭资源,从后往前关
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

3.数据库连接封装成工具类

package org.example.数据库连接使用;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.*;

public class DBUtil {
    // 先定义一个数据源对象
    private static DataSource dataSource = null;
    // 数据库的用户名
    private static final String USER = "root";
    // 数据库的密码
    private static final String PASSWORD = "123456";
    // 数据库连接字符串
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/java78?characterEncoding=utf-8&useSSL=false";
    static {
        // 初始化数据源,类加载时运行
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setURL(URL);
        mysqlDataSource.setUser(USER);
        mysqlDataSource.setPassword(PASSWORD);
        dataSource = mysqlDataSource;
    }
    // 用private 修饰构造方法，使外部不能new 这个类的对象
    private DBUtil() {}
    //获取数据库连接
    public static Connection getConnection () throws SQLException {
        return dataSource.getConnection();
    }
    //关闭对象并释放资源
    public static void close (ResultSet resultSet, PreparedStatement statement, Connection connection) {
        // 依次关闭对象，并释放资源
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

4.接收多个查询结果

package org.example.数据库连接使用;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Demo02_SelectAll {
    public static void main(String[] args) {
        // 查询到的所有学生信息
        List<Student> students = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1. 创建数据源并获取数据库连接
            connection = DBUtil.getConnection();
            // 2. 构造SQL语句
            String sql = "select id, name, sn, email, classId from student order by id ASC";
            // 使用SQL预处理对象处理SQL
            statement = connection.prepareStatement(sql);
            // 3. 执行SQL并获取结果，如果是结果集把结果集转成java对象
            resultSet = statement.executeQuery();
            // 遍历结果集，处理查询到数据
            while (resultSet.next()) {
                // 如果List为空则创建
                if (students == null) {
                    students = new ArrayList<>();
                }
                // 解析结果集并封装成Student对象
                Student student = new Student();
                student.setId(resultSet.getInt(1));
                student.setName(resultSet.getString(2));
                student.setSn(resultSet.getLong(3));
                student.setEmail(resultSet.getString(4));
                student.setClassId(resultSet.getInt(5));
                // 加入到集合中
                students.add(student);
            }
            // 打印结果
            System.out.println(students);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 4. 释放资源
            DBUtil.close(resultSet, statement, connection);
        }
    }
}

5.按姓名删除(可以改)

package org.example.数据库连接使用;

import java.sql.*;
import java.util.Scanner;

public class Demo03_Delete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            // 1. 获取数据库连接
            connection = DBUtil.getConnection();
            // 2. 构建SQL
            String sql = "delete from student where name = ?";
            // 接收用户的输入
            System.out.print("请输入要删除的同学姓名:");
            Scanner scanner = new Scanner(System.in);
            String name = scanner.next();
            // 3. 对SQL进行处理，并替换占位符
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            // 4. 执行SQL并获取结果
            int row = statement.executeUpdate();
            if (row <= 0) {
                System.out.println("删除失败.");
            } else {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.close(null, statement, connection);
        }
    }
}

6.插入

package org.example.数据库连接使用;

import java.sql.*;
import java.util.Scanner;

public class Demo04_Insert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            // 1. 获取数据库连接
            connection = DBUtil.getConnection();
            // 2. 构建SQL
            String sql = "insert into student values (?, ?, ?, ?, ?)";
            // 接收用户的输入
            Scanner scanner = new Scanner(System.in);
            System.out.print("请输入要添加的同学Id:");
            int id = scanner.nextInt();
            // 姓名
            System.out.print("请输入学生的姓名:");
            String name = scanner.next();
            // 学号
            System.out.print("请输入学生的学号->");
            long sn = scanner.nextLong();
            // 邮箱
            System.out.print("请输入学生的邮箱->");
            String email = scanner.next();
            // 班级编号
            System.out.print("请输入学生的班级编号->");
            int classId = scanner.nextInt();

            // 3. 对SQL进行处理，并替换占位符
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.setString(2, name);
            statement.setLong(3, sn);
            statement.setString(4, email);
            statement.setInt(5, classId);
            // 4. 执行SQL并获取结果
            int row = statement.executeUpdate();
            if (row <= 0) {
                System.out.println("添加失败.");
            } else {
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.close(null, statement, connection);
        }
    }
}