一. 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();
}
//获取数据库连接驱动
driver = prop.getProperty("driver");
//获取数据库连接URL地址
url = prop.getProperty("url");
//获取数据库连接用户名
username = prop.getProperty("username");
//获取数据库连接密码
password = prop.getProperty("password");
}
//加载数据库驱动
public static Connection getConnection(){
Connection connection=null;
try {
Class.forName(driver);
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void release(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
//关闭存储查询结果的ResultSet对象
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
resultSet = null;
}
if (statement != null) {
try {
//关闭负责执行SQL命令的Statement对象
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
//关闭Connection数据库连接对象
connection.close();
} catch (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 {
@Test //插入数据
public void insert() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//获取一个数据库连接
connection = Demo.getConnection();
//通过connection对象获取负责执行SQL命令的Statement对象
//createStatement()：创建向数据库发送sql的statement对象
statement = connection.createStatement();
//要执行的SQL命令
String sql = "insert into abc(id,name,password) " +
"values(7,'csz','3098')";
//执行插入操作，executeUpdate方法返回成功的条数
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功！！");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//SQL执行完成之后释放相关资源
Demo.release(connection, statement, resultSet);
}
}
@Test //删除数据
public void delete() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = Demo.getConnection();
statement = connection.createStatement();
String sql = "delete from abc where id=4";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功！！");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
Demo.release(connection, statement, resultSet);
}
}
@Test //更新数据
public void update() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = Demo.getConnection();
statement = connection.createStatement();
String sql = "update abc set name='ss' where id=7";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("更新成功！！");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
Demo.release(connection, statement, resultSet);
}
}
@Test //查找
public void find() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = Demo.getConnection();
statement = connection.createStatement();
String sql = "select * from abc where id=7";
//executeQuery() ：用于向数据发送查询语句
resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
Demo.release(connection, statement, resultSet);
}
}
}
