10.JDBC
需要jar包的支持：
- java.sql
- javax.sql
- mysql-conneter-java… 连接驱动（必须要导入）
实验sql
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,'张三','123456','zs@qq.com','2000-01-01');
INSERT INTO users(id,name,password,email,birthday)
VALUES(2,'李四','123456','ls@qq.com','2000-01-01');
INSERT INTO users(id,name,password,email,birthday)
VALUES(3,'王五','123456','ww@qq.com','2000-01-01');
导入数据库依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.10</version>
</dependency>
IDEA中连接数据库：
JDBC 固定步骤：
- 加载驱动
- 连接数据库,代表数据库
- 向数据库发送SQL的对象Statement : CRUD
- 编写SQL （根据业务，不同的SQL）
- 执行SQL
- 关闭连接（先开的后关）
package com.lxw.test;
import java.sql.*;
/**
* jdbc查询
* statement
*/
public class JdbcTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url = "jdbc:mysql://localhost:3306/lxw001?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
//1.加载驱动-两种方式
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库，connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//3.向数据库发送SQL的对象statement（普通的）或者PreparedStatement（安全的）
Statement statement = connection.createStatement();
//4.sql语句
String sql = "select * from users";
String sql1 = "delete from users where id = 4";
//5.执SQL查询，返回一个ResultSet
//增删改都使用executeUpdate()
//查用executeQuery()
int i = statement.executeUpdate(sql1);
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("password=" + resultSet.getObject("password"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birthday=" + resultSet.getObject("birthday"));
}
//6.关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
预编译PreparedStatement
package com.lxw.test;
import java.sql.*;
/**
* JDBC PreparedStatement插入操作
* Statement和PreparedStatement的区别
* Statement操作数据库时，如果执行相同的sql语句，每次都会对sql语句进行编译，效率太低。
* PreparedStatement操作数据库时，会提前编译，所以执行相同的sql时，效率会提高很多。
*/
public class JdbcTest2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url = "jdbc:mysql://localhost:3306/lxw001?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
//1.加载驱动-两种方式
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库，connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//3.sql语句
String sql = "insert into users(id, name, password, email, birthday) values (?,?,?,?,?);";
//4.预编译PreparedStatement（安全的）
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,4);//给第一个占位符赋值1
preparedStatement.setString(2,"牛啊");//给第一个占位符赋值1
preparedStatement.setString(3,"123456");//给第一个占位符赋值1
preparedStatement.setString(4,"12364@qq.com");//给第一个占位符赋值1
preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));//给第一个占位符赋值1
//5.执SQL查询，返回一个ResultSet
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
//6.关闭连接
preparedStatement.close();
connection.close();
}
}
事务
要么都成功，要么都失败！
ACID原则：保证数据的安全。
sql
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);
事务
package com.lxw.test;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JdbcTest3 {
@Test
public void test1(){
String url = "jdbc:mysql://localhost:3306/lxw001?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
Connection connection = null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库，代表数据库
connection = DriverManager.getConnection(url, username, password);
//3.通知数据库开启事务，false开启
connection.setAutoCommit(false);
String sql1 = "update account set money = money-100 where name = 'A';";
connection.prepareStatement(sql1).executeUpdate();
//制造错误
int i=1/0;
String sql2 = "update account set money = money+100 where name = 'B';";
connection.prepareStatement(sql2).executeUpdate();
connection.commit();//最后提交事务
System.out.println("提交成功");
} catch (Exception e) {
try{
connection.rollback();//出现错误就回滚
}catch (SQLException e1){
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
connection.close();//关闭连接
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
jdbc中的事务：
connection.setAutoCommit(false);//开启事务
connection.commit();//提交事务
connection.rollback();//回滚
mysql命令端事务：
start transaction; #开启事务
update account set money = money-100 where name = 'A';
update account set money = money+100 where name = 'B';
commit ; #提交事务
rollback ; #回滚
评论区