黑马JDBC
1.JDBC 简介
2.JDBC 快速入门
2.1步骤
0.创建工程,导入驱动jar
1.注册驱动
Class .forName(“com.mysql.jdbc.Driver”);
2.获取连接
Connection conn = DriverManager.getConnection(url,username,password);
3.定义SQL语句
String sql = “update····”;
4.获取执行SQL对象
Statement stmt = conn.createStatement();
5.执行SQL
stmt.executeUpdate(sql);
6.处理返回结果
7.释放资源
2.2应用
package com.kuangshen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
//快速入门
public class demo01 {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
/// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义SQL语句
String sql = "UPDATE account SET money = 2000 WHERE id = 1";
// 4.获取执行SQL对象
Statement stmt = conn.createStatement();
// 5.执行SQL
int count = stmt.executeUpdate(sql);//受影响的行数
// 6.处理返回结果
System.out.println(count);
//7.释放资源
stmt.close();
conn.close();
}
}
数据库样式
3.JDBC API详解
3.1DriverManager(驱动管理类)
作用:
-
注册驱动
-
获取数据库连接
package com.kuangshen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
//jdbc api 的详解
public class demo02 {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
/// 2.获取连接
String url = "jdbc:mysql:///jdbc?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义SQL语句
String sql = "UPDATE account SET money = 2000 WHERE id = 1";
// 4.获取执行SQL对象
Statement stmt = conn.createStatement();
// 5.执行SQL
int count = stmt.executeUpdate(sql);//受影响的行数
// 6.处理返回结果
System.out.println(count);
//7.释放资源
stmt.close();
conn.close();
}
}
3.2Connection(数据库连接对象)
作用:
- 获取执行SQL的对象
- 管理事务
package com.kuangshen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
//jdbc api 详解 connection
public class demo03_Connection {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
/// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "root";
String password = "123456";
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 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 throwables) {
//回滚事务
conn.rollback();
throwables.printStackTrace();
}
//7.释放资源
stmt.close();
conn.close();
}
}
3.3Statement
作用:
package com.kuangshen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class demo04_Statement {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
/// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义SQL语句
String sql = "UPDATE account SET money = 3000 WHERE id = 1";
// 4.获取执行SQL对象
Statement stmt = conn.createStatement();
// 5.执行SQL
int count = stmt.executeUpdate(sql);//执行完DML函数,受影响的行数
// 6.处理返回结果
if(count > 0) {
System.out.println("成功");
}else{
System.out.println("失败");
}
//7.释放资源
stmt.close();
conn.close();
}
}
package com.kuangshen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class demo05_Statement {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
/// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//3.定义SQL语句
String sql = "drop database db1";
// 4.获取执行SQL对象
Statement stmt = conn.createStatement();
// 5.执行SQL
int count = stmt.executeUpdate(sql);//执行完DDL函数,可能是0
// 6.处理返回结果
System.out.println(count);
// if(count >= 0) {
// System.out.println("成功");
// }else{
// System.out.println("失败");
// }
//7.释放资源
stmt.close();
conn.close();
}
}
3.4ResultSet
package com.kuangshen;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class demo06_ResultSet {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
/// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "123456";
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);
// //6.处理结果,遍历rs中的所有数据
// //6.1光标向下移动一行,并且判断当前行是否有数据
// while(rs.next()){
// //6.2获取数据 getXxx()
// int id = rs.getInt(1);
// double money = rs.getDouble(2);
// String name = rs.getString(3);
// System.out.println(id);
// System.out.println(money);
// System.out.println(name);
// System.out.println("------------------------------------");
//
//
// }
//6.处理结果,遍历rs中的所有数据
//6.1光标向下移动一行,并且判断当前行是否有数据
while(rs.next()){
//6.2获取数据 getXxx()
int id = rs.getInt("id");
double money = rs.getDouble("money");
String name = rs.getString("name");
System.out.println(id);
System.out.println(money);
System.out.println(name);
System.out.println("------------------------------------");
}
//7.释放资源
rs.close();
stmt.close();
conn.close();
}
}
案例
package com.kuangshen;
import 案例.Account;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
//需求:ResultSet案例
/*
1.定义实体类Account
2.查询数据,封装到Account对象中
3.将Account对象存入ArrayList集合中
*/
public class demo07_ResultSet {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
/// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "123456";
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.处理结果,遍历rs中的所有数据
//6.1光标向下移动一行,并且判断当前行是否有数据
while(rs.next()){
Account account = new Account();
//6.2获取数据 getXxx()
int id = rs.getInt("id");
double money = rs.getDouble("money");
String name = rs.getString("name");
//赋值
account.setId(id);
account.setMoney(money);
account.setName(name);
//存入集合
list.add(account);
}
//打印集合
System.out.println(list);
//7.释放资源
rs.close();
stmt.close();
conn.close();
}
}
package 案例;
public class Account {
private int id;
private String name;
private double money;
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 double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
3.5PreparedStatement
以下代码有误,只帮助看懂PreparedStatement应用即可
package com.kuangshen;
import java.sql.*;
public class demo09_PreparedStatement {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");// mysql5之后的驱动包 这行代码可以不写
// 2.获取连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
//接受用户输入的 用户名和密码
String name = "张三";
String pwd = "' or '1' = '1";
//定义sql
String sql = "select * from account where usename = ? and password = ?";
//获取stmt对象
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.释放资源
pstmt.close();
rs.close();
conn.close();
}
}
4.数据库连接池
4.1简介
4.2Druid数据库连接池
以下代码有误,只帮助看懂druid连接池应用即可
package druid;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
//数据库连接池演示
public class demo01 {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("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"));
}
}
//配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql///db1?useSSL=false&useServerPreStmts=true
username=root
password=123456
# 初始化连续数量
initialSize=5
# 最大连续数
maxActive=10
# 最大等待时间
maxWait=3000