JDBC
1、数据库驱动
-
驱动:声卡,显卡,数据库
-
程序会通过数据库驱动,和数据库打交道
2、JDBC
sun公司为了简化开发人员(对数据库的统一),提供了一个(Java操作数据库)的规范,俗称JDBC,这些规范发的实现由具体的厂商去做~
对于开发人员来说,只需掌握JDBC接口的操作即可
架构:没有什么是加一层解决不了的
java.sql
javax.sql
导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
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(40),
birthday DATE
)
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,"zhamgsan",'123456','zs@qq.com','1998-11-03'),
(2,"lisi",'123456','zs@qq.com','1998-11-03'),
(3,"wangwu",'123456','zs@qq.com','1998-11-03')
- 创建一个普通项目
- 导入数据库驱动
- 编写测试代码
package com.zzx;
import java.sql.*;
//我的第一个jdbc程序
public class JDBCfirst {
public static void main(String[] args) throws SQLException {
//1、加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");//固定写法
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2、用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
String username = "root";
String password = "zzx";
//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"));
System.out.println("--------------------------");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection代表数据库
//数据库设置自动提交
//事物提交
//事物回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
Statement 执行SQL对象 PrepareStatemaent执行SQL的对象
String sql = "SELECT* FROM`users`";//编写sql
statement.executeQuery();//查询操作返回 request
statement.execute();//执行任何sql
statement.executeUpdate();//更新,插入,删除都是这个,返回一个受影响的行数
ResultSet查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的时候使用,知道用指定类型
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();//耗资源
4、statement对象
Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行完后,将会返回一个数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例:
Statement st=conn.createStatement();
string sql="insert into user(...)values(...)";
int num=st.executeUpdate(sq1);
if(num>CD){
system.out.println("插入成功!!!");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据添加操作,示例:
statement st=conn.createStatement();
string sql="delete from user where id=1";
int num=st.executeipdate (sql);
if(num>0){
system.out.println(“删除成功!!!");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据修改操作,示例:
Statement st=conn.createStatement();
string sql="update user set name='' where name=''";
int num = st.executeUpdate(sq1);
if(num>0){
system.out.println("修改成功!!!");
}
CRUD操作-read
使用executeUpdate(String sql)方法完成数据查询操作,示例:
Statement st=conn.createStatement();
string sql="select *from user where id=1";
int num = st.executeQuery(sq1);
while(rs.next){
//根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
代码实现
- 提取工具类
- 配置文件db.properties
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=zzx
package com.zzx.lesson02.utils;
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、驱动只用加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection connection, Statement statement,ResultSet resultSet){
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 编写增删改的方法。excuteUpdate
- 增
package com.zzx.lesson02;
import com.zzx.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获得数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)" +
"VALUES(5,'zzx','123456','64846@qq.com','2020-09-19')";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
- 删
package com.zzx.lesson02;
import com.zzx.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获得数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "DELETE FROM `users`WHERE `id`=5";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
- 改
package com.zzx.lesson02;
import com.zzx.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获得数据库连接
statement = connection.createStatement();//获得SQL的执行对象
String sql = "UPDATE `users`SET `name`='zhangzixiong',`email`='2311111@qq.com' WHERE id = 4";
int i = statement.executeUpdate(sql);
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
- 查询executeQuery
package com.zzx.lesson02;
import com.zzx.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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
//SQL
String sql = "select *from users where id = 1";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
sql注入的问题
sql存在漏洞,会被攻击导致数据泄露,SQL会被拼接 or
package com.zzx.lesson03;
import com.zzx.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
Login("zhamgsan","123456");
Login(" 'or'1=1"," 'or'1=1");//技巧
}
//登录业务
public static void Login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获得数据库连接
statement = connection.createStatement();//获得SQL的执行对象
//select *from users where `name`='zhamgsan' and `password` = '123456'
//select *from users where `name`=''or'1=1' and `password` = ''or'1=1'
String sql = "select *from users where `name` = '"+username+"' AND `password`='"+password+"'";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
5、PreparedStatement对象
PreparedStatement可以防止SQL注入,效率更高
- 增
package com.zzx.lesson03;
import com.zzx.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
// 区别
//使用?占位符代替参数
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)" ;
preparedStatement = connection.prepareStatement(sql);//预编译SQL,然后不执行
//手动给参数赋值
preparedStatement.setInt(1,6);
preparedStatement.setString(2,"zixiong");
preparedStatement.setString(3,"35535");
preparedStatement.setString(4,"1643687@qq.com");
//注意点:sql.Date java.sql.Date()
// util.Date java new Date().getTime()获得时间戳
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
- 删
package com.zzx.lesson03;
import com.zzx.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "delete from users where id =?" ;
preparedStatement = connection.prepareStatement(sql);//预编译SQL,然后不执行
//手动给参数赋值
preparedStatement.setInt(1,6);
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
- 改
package com.zzx.lesson03;
import com.zzx.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "update users set name = ? where id = ?" ;
preparedStatement = connection.prepareStatement(sql);//预编译SQL,然后不执行
//手动给参数赋值
preparedStatement.setString(1,"张三");
preparedStatement.setInt(2,4);
//执行
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
- 查询
package com.zzx.lesson03;
import com.zzx.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 connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
String sql = "select *from users where id = ?" ;
preparedStatement = connection.prepareStatement(sql);//预编译SQL,然后不执行
preparedStatement.setInt(1,4);//传递参数
//执行
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}
- SQL语句注入问题
package com.zzx.lesson03;
import com.zzx.lesson02.utils.JdbcUtils;
import java.sql.*;
public class SQL注入2 {
public static void main(String[] args) {
Login("lisi","123456");
Login(" 'or'1=1"," 'or'1=1");//不显示
}
//登录业务
public static void Login(String username,String password){
Connection connection = null;
PreparedStatement statement = null;
//PreparedStatement防止SQL注入的本质,把传递进来的参数当作字符
//假设其中存在转义字符,就直接忽略,比如说'会被直接抓不要
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//获得数据库连接
String sql = "select *from users where`name` =? AND`password`=?";//Mybatis
statement = connection.prepareStatement(sql);
statement.setString(1,username);
statement.setString(2,password);
resultSet = statement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getString("password"));
System.out.println("------------");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,resultSet);
}
}
}
6、使用IDEA连接数据库
- idea连接数据库
- 连接成功后,可以连接数据库
- 查看数据库内容,双击数据库
- 修改数据要提交
- 打开编辑器
- 在idea创建账户表,
/*创建账户表*/
create table account(
id int primary key auto_increment,
name varchar(40),
money float
);
/*插入测试数据*/
insert into account(name,money) values ('A',1000);
insert into account(name,money) values ('B',1000);
insert into account(name,money) values ('C',1000);
- 如果在IDEA中连接不上数据库,则在这里修改为数据库对应的版本sql
7、事务
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆
隔离性的问题:
脏读:一个事物读取了另一个没有提交的事物
不可重复读:在同一个事物内,重复读取表中的数据,表数据发生了改变
虚读(幻读):在一个事物内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
- 开启事务 connection.setAutoCommit(false);//开启事务
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义回滚语句,但默认失败就会回滚
package com.zzx.lesson04;
import com.zzx.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransAction02 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//关闭数据库的自动提交功能,自动会开启事务
connection.setAutoCommit(false);//开启事务
String sql1 ="update account set money = money-100 where name = 'A'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
//int x = 1/0;//报错
String sql2 ="update account set money = money+100 where name = 'B'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//业务完毕,
connection.commit();
System.out.println("成功!");
} catch (SQLException e) {
//如果失败默认回滚
// try {
// connection.rollback();//如果失败就回滚事务
// } catch (SQLException e1) {
// e1.printStackTrace();
// }
e.printStackTrace();
}finally {
JdbcUtils.release(connection,preparedStatement,resultSet);
}
}
}