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

一. statement对象

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

1. JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可
2. Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
3. 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);
    }
}

}