10、JDBC(重点)
10.1、数据库驱动
驱动:声卡、显卡、数据库
我们的程序会通过数据库驱动和数据库打交道!
10.2、JDBC
Sun公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的规范),称为JDBC
这些规范的实现由具体的厂商去做~
对于开发人员来说,我们只需要掌握JDBC接口的操作即可
java.sql javax.sql
还需要导入一个数据库驱动包:mysql-connector-java-5.1.47.jar
下载地址:https://mvnrepository.com/artifact/mysql/mysql-connector-java
10.3、第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
- 创建一个普通项目
- 导入数据库驱动,添加到项目的库中
- 编写测试代码
package com.jin.lesson01;
import java.sql.*;
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的对象 去执行SQL,可能存在结果,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询出来的结果
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("NAME"));
System.out.println("pwd="+resultSet.getObject("PASSWORD"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
}
//释放连接
resultSet.close();
statement.close();
connection.close();
}
- 步骤总结:
- 加载驱动
- 连接数据库DriverManager
- 获得执行的sql的对象Statement
- 获得返回的结果集
- 释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//Connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit(true);
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true" ;
//mysql -- 3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce -- 1521
//jdbc:oralce:thin:@lacalhost:1521:sid
Statement 执行SQL的对象 PreparedStatement 执行SQL的对象
statement.executeQuery();//查询操作返回resultSet
statement.execute(); //执行任何SQL 效率较慢
statement.executeUpdate();//更新,插入,删除都是用这个,返回一个受影响的行数
String sql = "SELECT * FROM users"; //编写SQL
ResultSet 查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行
释放资源
//释放连接
resultSet.close();
statement.close();
connection.close(); //耗资源,用完关掉
10.4、statement对象
JDBC中的statement对象用于向数据库中发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,exexuteUpdate执行完成后,将会返回一个整数(即增删改语句导致数据库发生了几行数据的变化)
statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回值代表查询结果的ResultSet对象
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("插入成功");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement statement = connection.createStatement();
String sql = "delete from user where id =1";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
CURD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement statement = connection.createStatement();
String sql = "update user set name ='' where name = ''";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
CURD操作-read
使用executeUpdate(String sql)方法完成数据查询操作,示例操作:
Statement statement = connection.createStatement();
String sql = "select * from user where id =1";
ResultSet rs= statement.executeQuery(sql);
if(rs.next()){
System.out.println("");
}
代码实现
- 编写工具类
package com.jin.lesson02.utils;
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. 驱动只用加载一次
Class.forName(driver);
} catch (Exception 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 com.jin.lesson02; import com.jin.lesson02.utils.JdbcUtils; 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 coon = null; Statement st = null; ResultSet rs = null; try { coon = JdbcUtils.getConnection(); st = coon.createStatement(); String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" + "VALUES(5,'sunwukong','123456','233223@qq.com','2020-01-01')"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(coon,st,rs); } } }
package com.jin.lesson02; import com.jin.lesson02.utils.JdbcUtils; 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 coon = null; Statement st = null; ResultSet rs = null; try { coon = JdbcUtils.getConnection(); st = coon.createStatement(); String sql = "DELETE FROM `users` WHERE id = 5"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("删除成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(coon,st,rs); } } }
package com.jin.lesson02; import com.jin.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestUpdate { public static void main(String[] args) { Connection coon = null; Statement st = null; ResultSet rs = null; try { coon = JdbcUtils.getConnection(); st = coon.createStatement(); String sql = "update users set name='zhaoliu' where id = 1"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(coon,st,rs); } } }
-
查询
package com.jin.lesson02;
import com.jin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
st = coon.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(coon,st,rs);
}
}
}
SQL注入的问题
SQL存在漏洞,会被攻击导致数据泄露SQL会被拼接
package com.jin.lesson02;
import com.jin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Sqlzr {
public static void main(String[] args) {
login(" 'or' 1=1"," 'or' 1=1");
}
//登录业务
public static void login(String username,String password){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection(); //获取连接
st = conn.createStatement();//获取SQL执行对象
//SQL
String sql = "select * from users where name= '"+username+"'and password = '"+password+"'";
rs = st.executeQuery(sql);//查询完毕返回结果集
while(rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.5、PreparedStatment对象
PreparedStatement 可以防止SQL注入 ,效率更高。
- 增加
package com.jin.lesson03;
import com.jin.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//区别
String sql = "insert into users(id,`NAME`) values(?,?)";
st = conn.prepareStatement(sql);//预编译SQL,先写SQL然后不执行
//手动给参数赋值
st.setInt(1,4);
st.setString(2,"wudi");
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
finally {
JdbcUtils.release(conn,st,rs);
}
}
}
-
删除
package com.jin.lesson03; import com.jin.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement pst = null; try { conn = JdbcUtils.getConnection(); String sql = "delete from users where id = ?"; pst = conn.prepareStatement(sql); pst.setInt(1, 4); int i = pst.executeUpdate(); if (i>0){ System.out.println("删除成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(conn,pst,null); } } }
-
修改
package com.jin.lesson03;
import com.jin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update users set `name` = ? where id = ? ";
pst = conn.prepareStatement(sql);
pst.setString(1,"caiji");
pst.setInt(2,2);
int i = pst.executeUpdate();
if (i>0){
System.out.println("更新成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,null);
}
}
}
- 查询
package com.jin.lesson03;
import com.jin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from users where id = ?";
pst = conn.prepareStatement(sql);
pst.setInt(1,2);
rs = pst.executeQuery();
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
防止SQL注入本质,传递字符 带有“ ”,转义字符会被转义
10.6、使用IDEA连接数据库
- 选择数据库
- 操作
10.7、事务
要么都成功,要么都失败
ACID原则
原子性:要么全完成,要么全失败
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久到数据库中了
隔离性产生的问题:
- 脏读:一个事务读取了另一个没有提交的事务
- 不可重复读:在一个事务内,重复读取表中的数据,表数据发生了变化
- 虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
- 开启事务
- 一组事务执行完毕提交事务
- 可以在catch语句中显示定义回滚语句,但默认失败就会回滚
package com.jin.lesson04;
import com.jin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交,自动会打开事务
conn.setAutoCommit(false);//开启事务
String sql1 = "update account set money=money-200 where name='A' ";
pst = conn.prepareStatement(sql1);
pst.executeUpdate();
String sql2 = "update account set money=money+200 where name='B' ";
pst = conn.prepareStatement(sql2);
pst.executeUpdate();
// 业务完毕,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException throwables) {
try {
conn.rollback();//如果失败则会默认回滚
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}
}
}