JDBC
1.使用目的
我们要做到的是同一套Java代码操作不同的关系型数据库，而此时sun公司就指定了一套标准接口（JDBC），JDBC中定义了所有操作关系型数据库的规则。可随时替换底层数据库，访问数据库的Java代码基本不变。
2.安装mysql，JDBC
3.使用
1.注册驱动（mysql5之后不需要）
class.forName("com.mysql.jdbc.Driver");
2.获取连接
String url = "jdbc:mysql://localhost:3306/student";
String url1 = "jdbc:mysql://127.0.0.1:3306/student";
String url2 = "jdbc:mysql://192.168.43.64:3306/student";
注意：使用url2时，需要在mysql下执行
要想让别人远程访问自己的数据库
use mysql;
update user set host ='%' where user = "root" and host ="localhost";
flush privileges;
Connection conn=DriverManager.getConnection(url,username,password);
3.定义sql语句
String sql = "select * from 表名"
执行SQL语句需要SQL执行对象，而这个执行对象就是Statement对象
4.获取sql对象
Statement stmt = conn.createStatement();
5.执行sql
stmt.excute(sql);
6.处理返回结果
7.释放资源
stmt.close();
conn.close();
4.注意
DriverMAnager：获取数据库连接
connection：获取执行sql的对象，管理事务
statement：执行sql：DDL，DML，DQL（ResultSet）
ResultSet：封装了SQL查询语句的结果，常用方法：next()，getXXX()
5.事务管理
开启事务：BEGIN 或者 START TRANSACTION;
提交事务：COMMIT;
回滚事务：ROLLBACK;
/**
* JDBC API 详解：Connection
*/
public class JDBCDemo3_Connection {
public static void main(String[] args) throws Exception {
//1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql1 = "update account set money = 3000 where id = 1";
String sql2 = "update account set money = 3000 where id = 2";
//4. 获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
try {
// ============开启事务==========
conn.setAutoCommit(false);
//5. 执行sql
int count1 = stmt.executeUpdate(sql1);//受影响的行数
//6. 处理结果
System.out.println(count1);
int i = 3/0;
//5. 执行sql
int count2 = stmt.executeUpdate(sql2);//受影响的行数
//6. 处理结果
System.out.println(count2);
// ============提交事务==========
//程序运行到此处，说明没有出现任何问题，则需求提交事务
conn.commit();
} catch (Exception e) {
// ============回滚事务==========
//程序在出现异常时会执行到这个地方，此时就需要回滚事务
conn.rollback();
e.printStackTrace();
}
//7. 释放资源
stmt.close();
conn.close();
}
}
6.Rsultset使用
/**
* 查询account账户表数据，封装为Account对象中，并且存储到ArrayList集合中
* 1. 定义实体类Account
* 2. 查询数据，封装到Account对象中
* 3. 将Account对象存入ArrayList集合中
*/
@Test
public void testResultSet2() throws Exception {
//1. 注册驱动
//Class.forName("com.mysql.jdbc.Driver");
//2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
//3. 定义sql
String sql = "select * from account";
//4. 获取statement对象
Statement stmt = conn.createStatement();
//5. 执行sql
ResultSet rs = stmt.executeQuery(sql);
// 创建集合
List<Account> list = new ArrayList<>();
// 6.1 光标向下移动一行，并且判断当前行是否有数据
while (rs.next()){
Account account = new Account();
//6.2 获取数据 getXxx()
int id = rs.getInt("id");
String name = rs.getString("name");
double money = rs.getDouble("money");
//赋值
account.setId(id);
account.setName(name);
account.setMoney(money);
// 存入集合
list.add(account);
}
System.out.println(list);
//7. 释放资源
rs.close();
stmt.close();
conn.close();
}
7.防止sql注入（PreparedStatement）
SQL注入：通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行攻击的方法
SQL注入场景：有一个用户表，在登入时，需要使用sql语句查询表中的用户名和密码与输入的用户名和密码是否匹配
@Test
public void testLogin() throws Exception {
//2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// 接收用户输入 用户名和密码
String name = "sjdljfld";
String pwd = "' or '1' = '1";
String sql = "select * from tb_user where username = '"+name+"' and password = '"+pwd+"'";
// 获取stmt对象
Statement stmt = conn.createStatement();
// 执行sql
ResultSet rs = stmt.executeQuery(sql);
// 判断登录是否成功
if(rs.next()){
System.out.println("登录成功~");
}else{
System.out.println("登录失败~");
}
//7. 释放资源
rs.close();
stmt.close();
conn.close();
}
解决sql注入：将SQL执行对象 Statement 换成 PreparedStatement 对象。本质：进行转义
@Test
public void testPreparedStatement() throws Exception {
//2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写
String url = "jdbc:mysql:///db1?useSSL=false";
String username = "root";
String password = "1234";
Connection conn = DriverManager.getConnection(url, username, password);
// 接收用户输入 用户名和密码
String name = "zhangsan";
String pwd = "' or '1' = '1";
// 定义sql
String sql = "select * from tb_user where username = ? and password = ?";
// 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 设置？的值
pstmt.setString(1,name);
pstmt.setString(2,pwd);
// 执行sql
ResultSet rs = pstmt.executeQuery();
// 判断登录是否成功
if(rs.next()){
System.out.println("登录成功~");
}else{
System.out.println("登录失败~");
}
//7. 释放资源
rs.close();
pstmt.close();
conn.close();
}
8.数据库连接池使用
官方提供标准接口：DataSource获取Connection
那么以后就不需要通过 DriverManager 对象获取 Connection 对象，而是通过连接池（DataSource）获取 Connection 对象
常见的数据库连接池：DBCP，C3P0，Druid
使用Driud
/**
* Druid数据库连接池演示
*/
public class DruidDemo {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3. 加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. 获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. 获取数据库连接 Connection
Connection connection = dataSource.getConnection();
System.out.println(connection); //获取到了连接后就可以继续做其他操作了
//System.out.println(System.getProperty("user.dir"));
}
}
9.使用JDBC实现mysql的增删改查
9.1环境准备
-- 创建student表
create table student(
id int,
name varchar(10),
birthday date,
score double(5,2),
email varchar(64),
tel varchar(15),
status tinyint
);
-- 添加数据
insert into student (id,name,birthday,score,email,tel,status)
values (2020,"wjc1","2020-01-01",120.5,"120.com","150274-681",0),
(2021,"wjc2","2021-01-01",130.5,"130.com","150245-681",1),
(2022,"wjc3","2022-01-01",140.5,"140.com","150212-681",0),
(2023,"wjc4","2023-01-01",150.5,"150.com","150296-681",1);
9.2构建Student类
class student{
int id;
String name;
java.sql.Date birthday;
double score;
String email;
String tel;
int stutus;
public student(int id,String name, java.sql.Date birthday, double score, String email, String tel, int stutus){
this.id=id;
this.name=name;
this.birthday=birthday;
this.score=score;
this.email=email;
this.tel=tel;
this.stutus=stutus;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public int getStutus() {
return stutus;
}
public void setStutus(int stutus) {
this.stutus = stutus;
}
@Override
public String toString() {
return "student{" +
"id=" + id +
", name='" + name + '\'' +
", birthday=" + birthday +
", score=" + score +
", email='" + email + '\'' +
", tel='" + tel + '\'' +
", stutus=" + stutus +
'}';
}
}
9.3查询所有
// 查询数据
public static void SelectAllData() throws Exception {
// 加载配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("java_web_JDBC/src/druid.properties"));
// 获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 获取数据库连接
Connection connection = dataSource.getConnection();
// 定义sql
String sql = "select * from student";
// 使用PPreparedStatement防注入,获取pstmt
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 执行sql
ResultSet resultSet = preparedStatement.executeQuery();
// 将获取的数据封装成Student对象
ArrayList<student> st = new ArrayList<>();
// 获取数据
while (resultSet.next()){
int id=resultSet.getInt("id");
String name=resultSet.getString("name");
java.sql.Date birthday=resultSet.getDate("birthday");
double score=resultSet.getDouble("score");
String email=resultSet.getString("email");
String tel=resultSet.getString("tel");
int stutus=resultSet.getInt("status");
// 分装成student对象
student student = new student();
student.setId(id);
student.setName(name);
student.setBirthday(birthday);
student.setScore(score);
student.setEmail(email);
student.setTel(tel);
student.setStutus(stutus);
// 将封装的对象放入集合
st.add(student);
}
System.out.println(st);
// 释放资源
resultSet.close();
preparedStatement.close();
connection.close();
}
9.4添加数据
public static void AddData(Connection connection) throws Exception {
String sql ="insert into student (id,name,birthday,score,email,tel,status) values (?,?,?,?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,2025);
preparedStatement.setString(2,"wjc5");
String stringDate="2024-01-01";
preparedStatement.setDate(3,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
preparedStatement.setDouble(4,160.5);
preparedStatement.setString(5,"160.com");
preparedStatement.setString(6,"1568999-456");
preparedStatement.setInt(7,0);
int count = preparedStatement.executeUpdate();
System.out.println(count > 0);
// preparedStatement.close();
// connection.close();
}
9.5修改数据
public static void UpdateData(Connection connection) throws Exception {
String sql="update student set name=?,birthday=?,score=?,email=?,tel=?,status=? where id =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"wjc25");
String stringDate="2025-01-01";
preparedStatement.setDate(2,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
preparedStatement.setDouble(3,250.5);
preparedStatement.setString(4,"250.com");
preparedStatement.setString(5,"1568988-456");
preparedStatement.setInt(6,1);
preparedStatement.setInt(7,2025);
int count = preparedStatement.executeUpdate();
System.out.println(count>0);
// preparedStatement.close();
// connection.close();
}
9.6删除数据
public static void DeleteData(Connection connection) throws Exception{
String sql="delete from student where id =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,2025);
int count = preparedStatement.executeUpdate();
System.out.println(count>0);
preparedStatement.close();
connection.close();
}
9.7汇总：
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Properties;
public class JDBC6 {
// JDBC操作数据库实现增删改查
public static void main(String[] args) throws Exception {
// 加载配置文件
Properties properties = new Properties();
properties.load(new FileInputStream("java_web_JDBC/src/druid.properties"));
// 获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 获取数据库连接
Connection connection = dataSource.getConnection();
// 查询数据
SelectAllData(connection);
// 添加数据
AddData(connection);
// 修改数据
UpdateData(connection);
// 删除数据
DeleteData(connection);
}
// 查询数据
public static void SelectAllData(Connection connection) throws Exception {
// 定义sql
String sql = "select * from student";
// 使用PPreparedStatement防注入,获取pstmt
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 执行sql
ResultSet resultSet = preparedStatement.executeQuery();
// 将获取的数据封装成Student对象
ArrayList<student> st = new ArrayList<>();
// 获取数据
while (resultSet.next()){
int id=resultSet.getInt("id");
String name=resultSet.getString("name");
java.sql.Date birthday=resultSet.getDate("birthday");
double score=resultSet.getDouble("score");
String email=resultSet.getString("email");
String tel=resultSet.getString("tel");
int stutus=resultSet.getInt("status");
// 分装成student对象
student student = new student();
student.setId(id);
student.setName(name);
student.setBirthday(birthday);
student.setScore(score);
student.setEmail(email);
student.setTel(tel);
student.setStutus(stutus);
// 将封装的对象放入集合
st.add(student);
}
System.out.println(st);
// 释放资源
resultSet.close();
// preparedStatement.close();
// connection.close();
}
// 添加数据
public static void AddData(Connection connection) throws Exception {
String sql ="insert into student (id,name,birthday,score,email,tel,status) values (?,?,?,?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,2025);
preparedStatement.setString(2,"wjc5");
String stringDate="2024-01-01";
preparedStatement.setDate(3,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
preparedStatement.setDouble(4,160.5);
preparedStatement.setString(5,"160.com");
preparedStatement.setString(6,"1568999-456");
preparedStatement.setInt(7,0);
int count = preparedStatement.executeUpdate();
System.out.println(count > 0);
// preparedStatement.close();
// connection.close();
}
// 修改数据
public static void UpdateData(Connection connection) throws Exception {
String sql="update student set name=?,birthday=?,score=?,email=?,tel=?,status=? where id =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"wjc25");
String stringDate="2025-01-01";
preparedStatement.setDate(2,new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse(stringDate).getTime()));
preparedStatement.setDouble(3,250.5);
preparedStatement.setString(4,"250.com");
preparedStatement.setString(5,"1568988-456");
preparedStatement.setInt(6,1);
preparedStatement.setInt(7,2025);
int count = preparedStatement.executeUpdate();
System.out.println(count>0);
// preparedStatement.close();
// connection.close();
}
// 删除数据
public static void DeleteData(Connection connection) throws Exception{
String sql="delete from student where id =?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,2025);
int count = preparedStatement.executeUpdate();
System.out.println(count>0);
preparedStatement.close();
connection.close();
}
}
class student{
int id;
String name;
java.sql.Date birthday;
double score;
String email;
String tel;
int stutus;
public student(){
}
public student(int id,String name, java.sql.Date birthday, double score, String email, String tel, int stutus){
this.id=id;
this.name=name;
this.birthday=birthday;
this.score=score;
this.email=email;
this.tel=tel;
this.stutus=stutus;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public double getScore() {
return score;
}
public void setScore(double score) {
this.score = score;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public int getStutus() {
return stutus;
}
public void setStutus(int stutus) {
this.stutus = stutus;
}
@Override
public String toString() {
return
"id=" + id +"\n"+
"name='" + name + "\n"+
"birthday=" + birthday +"\n"+
"score=" + score +"\n"+
"email='" + email + "\n" +
"tel='" + tel + "\n" +
"stutus=" + stutus +"\n";
}
}