JDBC(2)：JDBC对数据库进行CRUD

2023-02-27 08:12:21

一. statement对象

JDBC程序中的Connection用于代表数据库的链接；Statement对象用于向数据库发送SQL语句；ResultSet用于代表Sql语句的执行结果

JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象

二. 使用JDBC对数据库增删改查

1. 搭建实验环境

1. 创建一个名为jdbc的数据库，并且创建一个abc表

CREATE DATABASE jdbc;

USE jdbc;


CREATE TABLE abc(


id INT(5) PRIMARY KEY,


NAME VARCHAR(40),


PASSWORD VARCHAR(40)


);ENGINE=INNODB DEFAULT CHARSET=utf8

2.新建一个JavaWeb工程，并导入MySQL数据库驱动，MySQL的下载官网：https://mvnrepository.com/artifact/mysql/mysql-connector-java

3. 在src目录下创建一个database.properties文件，编写MySQL数据库的连接信息

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/jdbc

username=root

password=123456

4. 编写一个Demo工具类，用于连接数据库，获取数据库连接和释放数据库连接

import java.io.IOException;

import java.io.InputStream;

import java.sql.*;

import java.util.Properties;

public class Demo {


private static String driver=null;


private static String url=null;


private static String username=null;


private static String password=null;


static {

//读取database.properties文件中的数据库连接信息


InputStream in = Demo.class.getClassLoader().getResourceAsStream("database.properties");


Properties prop = new Properties();


try {


prop.load(in);


} catch (IOException e1) {


e1.printStackTrace();


}
    <span class="hljs-comment">//获取数据库连接驱动</span>

    driver = prop.getProperty(<span class="hljs-string">"driver"</span>);

    <span class="hljs-comment">//获取数据库连接URL地址</span>

    url = prop.getProperty(<span class="hljs-string">"url"</span>);

    <span class="hljs-comment">//获取数据库连接用户名</span>

    username = prop.getProperty(<span class="hljs-string">"username"</span>);

    <span class="hljs-comment">//获取数据库连接密码</span>

    password = prop.getProperty(<span class="hljs-string">"password"</span>);

}

    <span class="hljs-comment">//加载数据库驱动</span>

<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection getConnection(){

    Connection connection=<span class="hljs-literal">null</span>;

    <span class="hljs-keyword">try</span> {

        Class.forName(driver);

        connection = DriverManager.getConnection(url, username, password);

    } <span class="hljs-keyword">catch</span> (Exception e) {

        e.printStackTrace();

    }

    <span class="hljs-keyword">return</span> connection;

}

<span class="hljs-comment">//释放资源</span>

<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> release(Connection connection, Statement statement, ResultSet resultSet) {

    <span class="hljs-keyword">if</span> (resultSet != <span class="hljs-literal">null</span>) {

        <span class="hljs-keyword">try</span> {

            <span class="hljs-comment">//关闭存储查询结果的ResultSet对象</span>

            resultSet.close();

        } <span class="hljs-keyword">catch</span> (Exception e) {

            e.printStackTrace();

        }

       resultSet = <span class="hljs-literal">null</span>;

    }

    <span class="hljs-keyword">if</span> (statement != <span class="hljs-literal">null</span>) {

        <span class="hljs-keyword">try</span> {

            <span class="hljs-comment">//关闭负责执行SQL命令的Statement对象</span>

            statement.close();

        } <span class="hljs-keyword">catch</span> (Exception e) {

            e.printStackTrace();

        }

    }

    <span class="hljs-keyword">if</span> (connection != <span class="hljs-literal">null</span>) {

        <span class="hljs-keyword">try</span> {

            <span class="hljs-comment">//关闭Connection数据库连接对象</span>

            connection.close();

        } <span class="hljs-keyword">catch</span> (Exception e) {

            e.printStackTrace();

        }

    }

}

}

2. 使用statement对象完成对数据库的CRUD操作

import org.junit.Test;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

public class DemoTest {
<span class="hljs-meta">@Test</span> <span class="hljs-comment">//插入数据</span>

public <span class="hljs-keyword">void</span> insert() {

    Connection connection = <span class="hljs-keyword">null</span>;

    Statement statement = <span class="hljs-keyword">null</span>;

    ResultSet resultSet = <span class="hljs-keyword">null</span>;

    <span class="hljs-keyword">try</span> {

        <span class="hljs-comment">//获取一个数据库连接</span>

        connection = Demo.getConnection();

        <span class="hljs-comment">//通过connection对象获取负责执行SQL命令的Statement对象</span>

        <span class="hljs-comment">//createStatement()：创建向数据库发送sql的statement对象</span>

        statement = connection.createStatement();

        <span class="hljs-comment">//要执行的SQL命令</span>

        <span class="hljs-built_in">String</span> sql = <span class="hljs-string">"insert into abc(id,name,password) "</span> +

                <span class="hljs-string">"values(7,'csz','3098')"</span>;

        <span class="hljs-comment">//执行插入操作，executeUpdate方法返回成功的条数</span>

        <span class="hljs-built_in">int</span> <span class="hljs-built_in">num</span> = statement.executeUpdate(sql);

        <span class="hljs-keyword">if</span> (<span class="hljs-built_in">num</span> &gt; <span class="hljs-number">0</span>) {

            System.out.println(<span class="hljs-string">"插入成功！！"</span>);

        }

    } <span class="hljs-keyword">catch</span> (Exception e) {

        e.printStackTrace();

    } <span class="hljs-keyword">finally</span> {

        <span class="hljs-comment">//SQL执行完成之后释放相关资源</span>

        Demo.release(connection, statement, resultSet);

    }

}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//删除数据</span>

public <span class="hljs-keyword">void</span> delete() {

    Connection connection = <span class="hljs-keyword">null</span>;

    Statement statement = <span class="hljs-keyword">null</span>;

    ResultSet resultSet = <span class="hljs-keyword">null</span>;

    <span class="hljs-keyword">try</span> {

        connection = Demo.getConnection();

        statement = connection.createStatement();

        <span class="hljs-built_in">String</span> sql = <span class="hljs-string">"delete from abc where id=4"</span>;

        <span class="hljs-built_in">int</span> <span class="hljs-built_in">num</span> = statement.executeUpdate(sql);

        <span class="hljs-keyword">if</span> (<span class="hljs-built_in">num</span> &gt; <span class="hljs-number">0</span>) {

            System.out.println(<span class="hljs-string">"删除成功！！"</span>);

        }

    } <span class="hljs-keyword">catch</span> (Exception e) {

        e.printStackTrace();

    } <span class="hljs-keyword">finally</span> {

        Demo.release(connection, statement, resultSet);

    }

}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//更新数据</span>

public <span class="hljs-keyword">void</span> update() {

    Connection connection = <span class="hljs-keyword">null</span>;

    Statement statement = <span class="hljs-keyword">null</span>;

    ResultSet resultSet = <span class="hljs-keyword">null</span>;

    <span class="hljs-keyword">try</span> {

        connection = Demo.getConnection();

        statement = connection.createStatement();

        <span class="hljs-built_in">String</span> sql = <span class="hljs-string">"update abc set name='ss' where id=7"</span>;

        <span class="hljs-built_in">int</span> <span class="hljs-built_in">num</span> = statement.executeUpdate(sql);

        <span class="hljs-keyword">if</span> (<span class="hljs-built_in">num</span> &gt; <span class="hljs-number">0</span>) {

            System.out.println(<span class="hljs-string">"更新成功！！"</span>);

        }

    } <span class="hljs-keyword">catch</span> (Exception e) {

        e.printStackTrace();

    } <span class="hljs-keyword">finally</span> {

        Demo.release(connection, statement, resultSet);

    }

}

<span class="hljs-meta">@Test</span> <span class="hljs-comment">//查找</span>

public <span class="hljs-keyword">void</span> find() {

    Connection connection = <span class="hljs-keyword">null</span>;

    Statement statement = <span class="hljs-keyword">null</span>;

    ResultSet resultSet = <span class="hljs-keyword">null</span>;

    <span class="hljs-keyword">try</span> {

        connection = Demo.getConnection();

        statement = connection.createStatement();

        <span class="hljs-built_in">String</span> sql = <span class="hljs-string">"select * from abc where id=7"</span>;

        <span class="hljs-comment">//executeQuery() ：用于向数据发送查询语句</span>

        resultSet = statement.executeQuery(sql);

        <span class="hljs-keyword">if</span> (resultSet.next()) {

            System.out.println(resultSet.getString(<span class="hljs-string">"name"</span>));

        }

    } <span class="hljs-keyword">catch</span> (Exception e) {

        e.printStackTrace();

    } <span class="hljs-keyword">finally</span> {

        Demo.release(connection, statement, resultSet);

    }

}

}

码农公寓

一. statement对象

二. 使用JDBC对数据库增删改查

1. 搭建实验环境

2. 使用statement对象完成对数据库的CRUD操作

相关文章