JDBC
JAVA 连接 数据库。----JDBC
JAVA DataBase Connection
Mysql SqlServer Oracle PG ……
JAVA 写一个接口/类 --- 抽象
Mysql 实现 接口。重写方法。连接
SqlServer 实现 接口 重写方法。连接;
……
啥是JDBC: JDBC --- JAVA DataBase Connection 就是用JAVA操作数据库的连接;JDBC就是JAVA给我提供的一整套的 接口 或者 类; 然后各个数据库厂商 实现这些接口,达到连接自身的目的;
1.JDBC的基本概念;
2.JDBC的快速入门
3.JDBC的各个类或者接口 详解;
一.JDBC的快速入门
1.导入 mysql 的jar包; mysql-connector-java-5.1.44-bin.jar
2.注册驱动
3.获取这次连接;连接对象;
4.定义SQL语句
5.获取执行SQL语句的对象;
6.执行SQL语句
7.对结果做处理
8.释放资源;
1.Mysql准备
create database student;
create table student.stu
(
id int auto_increment
primary key,
name varchar(55) null,
pass varchar(55) null,
age int null,
sex varchar(4) null
);
DataGrip;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hMmKHFRN-1677924239751)(/Users/zhoubaopeng/Library/Application Support/typora-user-images/image-20230304093106126.png)]
- 创建一个 JAVA 项目
- 实施
1.导入 mysql 的jar包; mysql-connector-java-5.1.44-bin.jar
在你的JAVA项目中,创建一个文件夹lib,
把mysql-connector-java-5.1.44-bin.jar粘贴进去
右键,Add AS Lib……
2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
3.获取这次连接;连接对象;Connection 那台机器:ip。 那个厂商的数据库:端口 ; 那个数据库:student
连接MYSQL 5的;
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","tiger123");
得到连接的方法:使用 DriverManager 类下的 方法getConnection(url,userName,pass);
url: "jdbc:mysql://ip:端口号/数据库库名?附加条件"
userName: root
pass: tiger123
如果你的MYSQL 是 8的;
1.第一步导入的 jar 换为 mysql-connector-java-8.0.13.jar
2.url:jdbc:mysql://localhost:3306/student?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
这4个附加条件用 & 隔开;
4.定义SQL语句
String sql = "update stu set name = '老五' where id = 1";
5.获取执行SQL语句的对象; 用啥获取: connection
Statement statement = connection.createStatement();
6.执行SQL语句
//1.增删改: int i = statement.executeUpdate(sql); 返回值i,影响数据库表的行数;
//2.查询:ResultSet resultSet = statement.executeQuery(sql); 返回值 resultSet 结果集;
7.对结果做处理
8.释放资源;
System.out.println(i>0);
// 8.释放资源; 从下往上释放;
// 查询:
// resultSet.close();
statement.close();
connection.close();
package com.wdzl.jdbcdemo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo1 {
public static void main(String[] args) throws Exception {
// 1.导入 mysql 的jar包; mysql-connector-java-5.1.44-bin.jar
// 2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 3.获取这次连接;连接对象;
Connection connection
= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=Asia/Shanghai",
"root","tiger123");
// 4.定义SQL语句
String sql = "update stu set name = '老五' where id = 1";
// 5.获取执行SQL语句的对象;
Statement statement = connection.createStatement();
// 6.执行SQL语句
int i = statement.executeUpdate(sql);
// 7.对结果做处理
System.out.println(i>0);
// 8.释放资源; 从下往上释放;
// 查询:
// resultSet.close();
statement.close();
connection.close();
}
}
任务:
增加一条数据;
观察:增删改 就是 SQL 不一样;
接下来写个查询
package com.wdzl.jdbcdemo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo2 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn
= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=Asia/Shanghai",
"root","tiger123");
String sql = "select * from stu";
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery(sql);
while (rs.next()){
System.out.print(rs.getInt("id")+"\t");
System.out.print(rs.getString("name")+"\t");
System.out.print(rs.getString(3)+"\t");
System.out.print(rs.getInt(4)+"\t");
System.out.print(rs.getString("sex")+"\t");
System.out.println();
}
rs.close();
stat.close();
conn.close();
}
}
二.JDBC的各个类或者接口 详解;
DriverManager
1.得到连接对象
得到连接的方法:使用 DriverManager 类下的 方法getConnection(url,userName,pass);
url: "jdbc:mysql://ip:端口号/数据库库名?附加条件"
userName: root
pass: tiger123
2.注册驱动
翻看 com.mysql.jdbc.Driver
static {
try {
DriverManager.registerDriver(new Driver()); // 注册驱动
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
注册驱动这件事情,在你的代码还没执行的时候就完成了; 所以: Class.forName("com.mysql.jdbc.Driver"); 完全可以省略掉; 但是由于编码习惯,一般不省略;
Connection
1.获取执行SQL语句的对象 Statement/PreparedStatement
Statement stat = conn.createStatement(); // 不安全的 存在SQL注入的问题;
PreparedStatement preparedStatement = conn.prepareStatement(sql); // 安全的
2.管理事务;
// 开启事务
conn.setAutoCommit(boolean flag); // flag 为 false 表示开启了事务;
// 提交
conn.commit();
// 回滚
conn.rollback();
Statement
案例:登录案例;
package com.wdzl.jdbcdemo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class JdbcDemo3 {
public static void main(String[] args) throws Exception {
Scanner in = new Scanner(System.in);
System.out.println("请输入用户名:");
String userName = in.next();
System.out.println("请输入密码:");
String pass = in.next();
boolean login = login(userName, pass);
if (login){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
// 登录的方法
public static boolean login(String userName,String pass) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn
= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=Asia/Shanghai",
"root","tiger123");
String sql = "select * from stu where name = '"+userName + "' and pass = '" + pass +"'";
System.out.println(sql);
Statement stat = conn.createStatement();
ResultSet resultSet = stat.executeQuery(sql);
boolean flag = resultSet.next();
resultSet.close();
stat.close();
conn.close();
return flag;
}
}
pass = 123456
select * from stu where name = '李四' and pass = '123456'
这叫:SQL注入;
pass = 4324324'or'1'='1
select * from stu where name = '李四' and pass = '4324324'or'1'='1'
既然 Statement 存在 SQL 注入的问题,那么以后就不用他了; 改用 PreparedStatement ;
PreparedStatement 的使用 和 Statement 有一些差别;
PreparedStatement
1.导入 mysql 的jar包; mysql-connector-java-5.1.44-bin.jar
2.注册驱动
3.获取这次连接;连接对象;
4.定义 带占位符的 SQL语句;
String sql = "select * from stu where name = ? and pass = ?";
5.获取 PreparedStatement 对象; 将sql传入; // 预编译sql
6.给 占位符赋值; setXxx();
7.执行SQL语句; 不在传sql语句了
8.对结果做处理
9.释放资源;
package com.wdzl.jdbcdemo1;
import java.sql.*;
import java.util.Scanner;
public class JdbcDemo3 {
public static void main(String[] args){
Scanner in = new Scanner(System.in);
System.out.println("请输入用户名:");
String userName = in.next();
System.out.println("请输入密码:");
String pass = in.next();
boolean login = loginPstat(userName, pass);
if (login){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
// 登录的方法
public static boolean login(String userName,String pass) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection conn
= DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=Asia/Shanghai",
"root","tiger123");
String sql = "select * from stu where name = '"+userName + "' and pass = '" + pass +"'";
System.out.println(sql);
Statement stat = conn.createStatement();
ResultSet resultSet = stat.executeQuery(sql);
boolean flag = resultSet.next();
resultSet.close();
stat.close();
conn.close();
return flag;
}
public static boolean loginPstat(String userName,String pass){
Connection conn = null;
PreparedStatement pstat =null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=Asia/Shanghai", "root","tiger123");
String sql = "select * from stu where name = ? and pass = ?";
pstat = conn.prepareStatement(sql); // sql 预编译
// setXxx(int index,xxx args); xxx是数据类型
// index: 第index个?
// args: 第index个?的值
pstat.setString(1,userName);
pstat.setString(2,pass);
rs = pstat.executeQuery();
return rs.next();
}catch (Exception e){
e.printStackTrace();
}finally {
try {
if (rs != null){
rs.close();
}
if (pstat != null){
pstat.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
return false;
}
}
JDBC管理事务
事务:俩条或者以上的SQL语句组成一个执行单元,这个执行单元里的SQL语句,要么全部执行成功,要么全部失败; 当某些语句出现失败。需要将这之前的语句回滚;
// 开启事务
conn.setAutoCommit(boolean flag); // flag 为 false 表示开启了事务;
// 提交
conn.commit();
// 回滚
conn.rollback();
需求: 将老五的密码改为 123, 将李四的密码改为 321; 要求要么都成功,要么都失败;
package com.wdzl.jdbcdemo1;
import java.sql.*;
public class JdbcDemo4 {
public static void main(String[] args) {
boolean b = updatePass("123", "321", 1, 2);
System.out.println(b);
}
public static boolean updatePass(String passLW,
String passLS,
int id1,
int id2){
Connection conn = null;
PreparedStatement pstat1 =null;
PreparedStatement pstat2 =null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student?useSSL=false&serverTimezone=Asia/Shanghai", "root","tiger123");
// 开启事务
conn.setAutoCommit(false);
String sql1 = "update stu set pass = ? where id = ?";
String sql2 = "update stu set pass = ? where id = ?";
pstat1 = conn.prepareStatement(sql1); // sql 预编译
pstat2 = conn.prepareStatement(sql2);
pstat1.setString(1,passLW);
pstat1.setInt(2,id1);
pstat2.setString(1,passLS);
pstat2.setInt(2,id2);
int i1 = pstat1.executeUpdate();
int x = 2/0;
int i2 = pstat2.executeUpdate();
// 提交事务
conn.commit();
return i1>0 && i2>0;
}catch (Exception e){
try {
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}finally {
try {
if (pstat1 != null){
pstat1.close();
}
if (pstat2 != null){
pstat2.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
return false;
}
}
在 写之前的代码的时候,不停的 Class forName;
普通JDBC封装
1.在src下创建一个配置文件: db.properties
driverName = com.mysql.jdbc.Driver
url = jdbc:mysql:///student?useSSL=false&serverTimezone=Asia/Shanghai
userName = root
pass = tiger123
2.创建一个工具类 : JDBCUtil
package com.wdzl.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtil {
static String driverName = null;
static String url = null;
static String userName = null;
static String pass = null;
static {
try {
Properties properties = new Properties();
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
properties.load(is);
driverName = properties.getProperty("driverName");
url = properties.getProperty("url");
userName = properties.getProperty("userName");
pass = properties.getProperty("pass");
Class.forName(driverName);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,userName,pass);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public static void close(ResultSet rs, Statement stat,Connection conn){
try {
if (rs != null){
rs.close();
}
if (stat != null){
stat.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
- 使用 工具类
package com.wdzl.jdbcdemo1;
import com.wdzl.util.JDBCUtil;
import java.sql.*;
import java.util.Scanner;
public class JdbcDemo3 {
public static void main(String[] args){
Scanner in = new Scanner(System.in);
System.out.println("请输入用户名:");
String userName = in.next();
System.out.println("请输入密码:");
String pass = in.next();
boolean login = loginPstat1(userName, pass);
if (login){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
public static boolean loginPstat1(String userName,String pass){
Connection conn = null;
PreparedStatement pstat =null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from stu where name = ? and pass = ?";
pstat = conn.prepareStatement(sql); // sql 预编译
pstat.setString(1,userName);
pstat.setString(2,pass);
rs = pstat.executeQuery();
return rs.next();
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtil.close(rs,pstat,conn);
}
return false;
}
}
JDBC查询
1.从数据库查询一个对象
package com.wdzl.jdbcdemo1;
import com.wdzl.entity.Student;
import com.wdzl.util.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JdbcDemo5 {
public static void main(String[] args) {
Student student = queryStu(1);
System.out.println(student);
Student student2 = queryStu(2);
System.out.println(student2);
}
public static Student queryStu(int id){
Connection conn = null;
PreparedStatement pstat = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from stu where id = ?";
pstat = conn.prepareStatement(sql);
pstat.setInt(1,id);
rs = pstat.executeQuery();
Student student = new Student();
while (rs.next()){
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setPass(rs.getString("pass"));
student.setAge(rs.getInt("age"));
student.setSex(rs.getString("sex"));
}
return student;
}catch (Exception e){
System.out.println(e);
}finally {
JDBCUtil.close(rs,pstat,conn);
}
return null;
}
}
2.从数据库查询一堆对象
package com.wdzl.jdbcdemo1;
import com.wdzl.entity.Student;
import com.wdzl.util.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class JdbcDemo5 {
public static void main(String[] args) {
List<Student> list = queryStu();
// System.out.println(list);
// list.stream().forEach(System.out::println);
// list.forEach(System.out::println);
// for (Student student : list) {
// System.out.println(student);
// }
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
}
public static List<Student> queryStu(){
Connection conn = null;
PreparedStatement pstat = null;
ResultSet rs = null;
try {
conn = JDBCUtil.getConnection();
String sql = "select * from stu";
pstat = conn.prepareStatement(sql);
rs = pstat.executeQuery();
List<Student> list = new ArrayList<>();
while (rs.next()){
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setPass(rs.getString("pass"));
student.setAge(rs.getInt("age"));
student.setSex(rs.getString("sex"));
list.add(student);
}
return list;
}catch (Exception e){
System.out.println(e);
}finally {
JDBCUtil.close(rs,pstat,conn);
}
return null;
}
}
三、连接池 重点
之前写的所有的案例:
关于Connection
1.获取
2.使用
3.释放
这个连接对象 是一次性的;
连接池的意思: 在类加载完毕职之后,创建多个连接对象, 放在一个容器里面; 使用的时候,就从容器里面拿一个出来; 用完之后,在放回去; 下一次还能用;
1.从池子里拿出对象;
2.使用;
3.归还;
池子是一个对象: DataSource; 数据源对象; 可以理解为池子;
俩种连接池技术:
1.c3p0
比较老,比较经典;
2.Druid 德鲁伊 阿里巴巴;
1.c3p0
1.导入 c3p0 连接池的 jar;
c3p0-0.9.5.2.jar
mchange-commons-java-0.2.12.jar
2.定义配置文件: 在src 下
c3p0.properties 或者 c3p0-config.xml ; 建议粘贴; 然后修改;
3.获取DataSource
4.获取Connection
将c3p0的使用封装成一个工具:
package com.wdzl.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCC3p0Util {
static DataSource ds = null;
static {
try {
ds = new ComboPooledDataSource();
} catch (Exception e) {
e.printStackTrace();
}
}
// 从池子里区连接的方法
public static Connection getConnection(){
try {
return ds.getConnection();// 从池子里拿出一个连接;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public static void close(ResultSet rs, Statement stat,Connection conn){
try {
if (rs != null){
rs.close(); // 关闭
}
if (stat != null){
stat.close(); // 关闭
}
if (conn != null){
conn.close(); // 归还连接;
}
}catch (Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
2.Druid
1.导入jar包; druid-1.0.9.jar
2.定义配置文件; xxx.properties
文件夹中的 druid.properties 赋值到配置文件; 修改 : url,pass;
3.加载配置文件
4.获取数据源对象
5.获取连接对象
package com.wdzl.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCDruidUtil {
public static DataSource ds = null;
static {
try {
// 3.加载配置文件
Properties properties = new Properties();
InputStream is = JDBCDruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
// 4.获取数据源对象
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
// 5.获取连接对象
return ds.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
public static void close(ResultSet rs, Statement stat,Connection conn){
try {
if (rs != null){
rs.close();
}
if (stat != null){
stat.close();
}
if (conn != null){
conn.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) {
System.out.println(getConnection());
}
}
登录案例: 要求,用户可以知道是啥输错了;
1.SQL
select * from stu where name = ?;
1). 查到了 ---- 用户名正确 ------ Student 对象;
2). 没查到 ---- 用户名错误;
2. 用 pass 和 student.getPass()
1).相同: 成功
2).不相同: 密码错误;
package com.wdzl.druid;
import com.wdzl.entity.Student;
import com.wdzl.util.JDBCDruidUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class UserLogin {
public static void main(String[] args) {
Scanner in = new Scanner(System.in);
System.out.println("请输入用户名:");
String name = in.next();
Student student = getStudentByName(name);
if (student != null){
System.out.println("请输入密码:");
String pass = in.next();
if (pass.equals(student.getPass())){
System.out.println("登录成功!");
}else {
System.out.println("密码错误!");
}
}else {
System.out.println("用户名错误!");
}
}
public static Student getStudentByName(String name){
Connection conn = null;
PreparedStatement pstat = null;
ResultSet rs = null;
try {
conn = JDBCDruidUtil.getConnection();
String sql = "select * from stu where name = ?";
pstat = conn.prepareStatement(sql);
pstat.setString(1,name);
rs = pstat.executeQuery();
Student student = null;
while (rs.next()){
student = new Student();
student.setName(rs.getString("name"));
student.setPass(rs.getString("pass"));
}
return student;
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCDruidUtil.close(rs,pstat,conn);
}
return null;
}
}
有一个体力活:
while (rs.next()){
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setPass(rs.getString("pass"));
student.setAge(rs.getInt("age"));
student.setSex(rs.getString("sex"));
list.add(student);
}
四、JDBCTemplate & DdUtil
可以只关注sql语句; 其他的不用写了;