一、什么是JDBC?
- JDBC是java database connection的缩写。
- 为了简化开发人员的操作,提供了一个Java操作数据库的规范,就叫做JDBC,这些规范的实现由厂商去做。开发人员只需要掌握JDBC接口操作即可。
- Java中需要用到java.sql包,和javax.sql包
二、创建数据库和表
#创建数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
#使用数据库
USE jdbcstudy;
#创建表
CREATE TABLE users(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(60),
email VARCHAR(60),
birthday DATE
);
#在表中插入数据
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'zhangsan','123456','1@qq.com','1978-12-01'),
(2,'lisi','123456','2@qq.com','1978-1-01'),
(3,'wangwu','123456','3@qq.com','1978-10-01');
三、第一个JDBC程序
package lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和url
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password = "123456";
//3.连接成功,数据库对象
//connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象
//statement是执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL
String sql = "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
//resultSet封装了查询出来的全部结果
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("passwd="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("-------------------------------------------------");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结
- 加载驱动
- 连接数据库DriverManager
- 获得执行SQL的对象statement
- 获得返回的结果集
- 释放连接
四、statement对象
- JDBC中的statement对象用于向数据库发送SQL语句,完成对数据库的增删改查操作。
- Statement对象的executeUpdate方法用于向数据库发送增、删、改的SQL语句。executeUpdate方法执行完毕之后将返回一个整数(即增删改的命令导致数据库中的表的几行发生了改变)
- Statement对象中的executeQuery方法用于向数据库发送查询的SQL语句,并返回一个代表查询结果的ResultSet对象。
配置文件
//这是一个配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
工具类
package lesson02;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//这是一个工具类
public class JdbcUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载1次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
测试插入数据
package lesson02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
Statement st =null;
ResultSet rs =null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//获得SQL的执行对象
st = conn.createStatement();
String sql="INSERT INTO users(id,`name`,`password`,email,birthday)\n" +
"VALUES(4,'zhouhao','123456','4@qq.com','2021-06-14')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
测试删除数据
package lesson02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn=null;
Statement st =null;
ResultSet rs =null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//获得SQL的执行对象
st = conn.createStatement();
String sql="DELETE FROM users WHERE id=4";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
测试查询数据
package lesson02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn=null;
Statement st =null;
ResultSet rs =null;
try {
//获取数据库连接
conn = JdbcUtils.getConnection();
//获得SQL的执行对象
st = conn.createStatement();
String sql="select * from users where id=1";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}