1、数据库驱动
2、JDBC
3、第一个JDBC程序
1️⃣ 建表
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','zhangsan','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')
2️⃣ 创建普通项目
3️⃣ 导入jar包
4️⃣ 编写测试代码
package com.cty.lesson1;
import java.sql.*;
public class JdbcDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2、用户信息和url
//三个参数
//useUnicode 编码支持中文
//characterEncoding 设定中文字符集
//userSSL 使用安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=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("id=" + resultSet.getObject("NAME"));
System.out.println("id=" + resultSet.getObject("PASSWORD"));
System.out.println("id=" + resultSet.getObject("email"));
System.out.println("id=" + resultSet.getObject("birthday"));
System.out.println("===================================================");
}
//6、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
4、statement对象
5、sql注入
sql存在漏洞,会被攻击导致泄露
本质:SQL会被拼接,实现一些别的命令
6、preparedStatement对象
可以防止sql注入
package com.cty.lesson2;
import java.sql.*;
/**
* @author caoyb
*/
public class PreparedStatementDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2、用户信息和url
//三个参数
//useUnicode 编码支持中文
//characterEncoding 设定中文字符集
//userSSL 使用安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
String username = "root";
String password = "123456";
//3、连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//使用?占位符代替参数
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
//预编译sql,先写sql,不执行
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//手动给参数赋值
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"cty");
preparedStatement.setString(3,"123456");
preparedStatement.setString(4,"13123@qq.com");
preparedStatement.setDate(5, new java.sql.Date(System.currentTimeMillis()));
//执行
int i = preparedStatement.executeUpdate();
if(i > 0){
System.out.println("插入成功");
}
preparedStatement.close();
connection.close();
}
}
7、事务
package com.cty.lesson3;
import java.sql.*;
public class Demo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2、用户信息和url
//三个参数
//useUnicode 编码支持中文
//characterEncoding 设定中文字符集
//userSSL 使用安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&userSSL=true";
String username = "root";
String password = "123456";
//3、连接成功,数据库对象 Connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement preparedStatement = null;
//关闭数据库的自动提交功能,自动会开启事务
connection.setAutoCommit(false);
String sql1 = "update account set money = money - 100 where name = 'A'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
String sql2 = "update account set money = money + 100 where name = 'B'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
connection.commit();
System.out.println("成功!");
try {
//如果失败就回滚
connection.rollback();
}catch (SQLException e){
e.printStackTrace();
}finally {
preparedStatement.close();
connection.close();
}
}
}