01-JDBC简介&快速入门
- JDBC概念:
- JDBC就是使用Java语言操作关系型数据库的一套API
- 全称：(Java DataBase Connectivity）Java数据库连接
- JDBC本质：
- 官方定义的一套操作所有关系型数据库的规则，接口
- 各个数据库厂商去实现这套接口，提供数据库驱动jar包
- 我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类
- JDBC好处：
- 各数据库厂商使用相同的接口，Java代码不需要针对不同数据库分别开发
- 可随时替换底层数据库，访问数据库的Java代码基本不变
-
JDBC的快速入门
0. 创建工程，导入驱动jar包 mysql-connectior-java-5.1.48.jar
- 注册驱动
Class.forName(“com.mysql.jdbc.Driver”);
2. 获取连接 Connection conn = DriverManger.getConnection(url,usename,password);
3. 定义SQL语句 String sql =“update…”;
4. 获取执行SQL对象 Statement stmt=conn.createStatement();
5. 执行SQL stmt.executeUpdate(sql);
6. 处理返回结果
7. 释放资源
package com.itheima.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/class";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "UPDATE score set grade =50 where id=1";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
System.out.println(count);
stmt.close();
conn.close();
}
}
-
JDBC API详解
- DriverManager
- Connection
- Statement
- ResultSet
- PreparedStatement
-
DriverManager (驱动管理类)作用：
- 注册驱动
Class.forName("com.mysql.jdbc.Driver");
查看源码 Static{
try{
DriverManger.registerDriver(new Driver());
}catch(SQLException var1){
throw new RuntimeException("can't register driver!");
}
}
提示：
- Mysql 5 之后的驱动包，可以省略注册驱动的步骤
- 自动加载jar包中的驱动类
- 获取数据库连接
static Connection getConnerction(String url,String user,String password)
- url：连接路径
语法：jdbc:mysql://ip地址域名:端口号/数据库名称?参数键值对1&参数键值对2...
实例：jdbc:mysql:///127.0.0.1:3306/db1
细节：1.如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称?参数键值对
2.配置useSSL=false参数，禁止用安全连接方式，解决警告提示
- user：用户名
- password：密码
- 获取执行SQL的对象
-
普通执行SQL对象 Statement createStatement()
PreparedStatement prepareStatement(sql)
CallableStatement prepareCall(sql)
- 管理事务
* Mysql事务管理
开启事务：BEGIN;/START TRANSACTION;
提交事务：COMMIT;
回滚事务：ROLLBACK;
MySQL默认自动提交事务
* JDBC事务管理：Connection接口中定义了3个对应的方法
开启事务：setAutoCommit(boolean autoCommit):true为自动提交事务；false为手动提交事务，即为开启事务
提交事务：commit()
回滚事务：rollback()
package com.itheima.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo3_Connection {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url = "jdbc:mysql://127.0.0.1:3306/class?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql1 = "UPDATE score set grade =50 where id=1";
String sql2 = "UPDATE score set grade =50 where id=2";
Statement stmt = conn.createStatement();
try {
conn.setAutoCommit(false);
int count1 = stmt.executeUpdate(sql1);
System.out.println(count1);
int i=3/0;
int count2 = stmt.executeUpdate(sql2);
System.out.println(count2);
conn.commit();
} catch (Exception throwable) {
conn.rollback();
throwable.printStackTrace();
}
stmt.close();
conn.close();
}
}
JDBC API详解
- Statement
- 执行SQL语句
- 执行SQL语句
int executeUpdate(sql): 执行DDL（主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等），DML语句（主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等）
返回值：1.DML语句影响的行数 2.DDL语句执行后，执行成功也可能返回0
DDL语句
public class JDBCDemo4_Statement {
@Test
public void testDML() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "UPDATE score set grade =80 where id=20";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
if(count>0){
System.out.println("修改成功");
}
else{
System.out.println("修改失败");
}
stmt.close();
conn.close();
}
DML语句
@Test
public void testDDL() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "drop database class2";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
System.out.println(count);
stmt.close();
conn.close();
}
ResultSet executeQuery(sql): 执行DQL语句
返回值：ResultSet结果集对象
-
ResultSet(就是Statment执行executeQuery返回值)
- Result(结果集对象)作用：
封装DQL查询语句的结果 ResultSet stmt.executeQuery(sql):执行DQL语句，返回ResultSet对象
- 获取查询结果
boolean next():1.将光标从当前位置向前移动一行 2.判断当前行是否为有效行
返回值：
true:有效行，当前行有效数据
false:无效行，当前行没有数据
xxx getXxx(参数):获取数据
xxx:数据类型; 如int getInt(参数); String getString(参数)
参数：
int:列的编号，从1开始
String:列的名字
-
使用步骤：
- 游标向下移动一行，并判断该行是否有数据：next()
- 获取数据:getXxx(参数)
while(rs.next()){
rs.getXxx(参数);
}
package com.itheima.jdbc;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo4_Result {
@Test
public void testResultSet() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
String sql = "select * from score";
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int number = rs.getInt(3);
int grade =rs.getInt(4);
System.out.println(id);
System.out.println(name);
System.out.println(number);
System.out.println(grade);
System.out.println("-------------------");
while(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("c_name");
int number = rs.getInt("stu_id");
int grade =rs.getInt("grade");
System.out.println(id);
System.out.println(name);
System.out.println(number);
System.out.println(grade);
System.out.println("-------------------");
}
rs.close();
conn.close();
stmt.close();
}
}
需求：查询account账户表数据，封装Account对象中，并且存储ArrayList集合中 表中数据->java对象->ArrayList package com.itheima.jdbc;
import com.itheima.pojo.Account;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class JDBCDemo4_Result {
@Test
public void testResultSet2() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
String sql = "select * from account";
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
List<Account> list = new ArrayList<Account>();
while (rs.next()) {
Account account = new Account();
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);
rs.close();
conn.close();
stmt.close();
}
-
PreparedStatement
- 利用密码登入sql 演示SQL注入 需求：完成用户登入
package com.itheima.jdbc;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo5_UserLogin {
@Test
public void testResultSet2() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
String name ="zhangsan";
String pwd ="123";
String sql ="select * from tb_user where username =' "+name+" ' and passward =' "+pwd+" ' ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println("登入成功");
}
else{
System.out.println("登入失败");
}
stmt.close();
rs.close();
conn.close();
}
@Test
public void testLogin_Inject() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
String name ="asdadjfo";
String pwd =" ' or ' 1 ' = ' 1 ";
String sql ="select * from tb_user where username =' "+name+" ' and passward =' "+pwd+" ' ";
System.out.println(sql);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println("登入成功");
}
else{
System.out.println("登入失败");
}
stmt.close();
rs.close();
conn.close();
}
}
```
* PreparedStatement作用:
1. 预编译SQL语句并执行：预防SQL注入问题
* 获取PreparedStatement对象
```java
String sql="select * from user where username=? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
```
* 设置参数值
```java
PreparedStatement对象:setXxx(参数1，参数2):给？赋值
Xxx:数据类型;如setInt(参数1，参数2)
参数：
参数1:?的位置编号，从1开始
参数2:?的值
```
```java
@Test
public void testPrepared() throws Exception {
String url = "jdbc:mysql:///class?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
String name = "zhangsan";
String pwd = "123";
String sql = "select * from tb_user where username =? and passward =? ";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setString(2,pwd);
ResultSet rs = pstmt.executeQuery();
if(rs.next()){
System.out.println("登入成功");
}
else{
System.out.println("登入失败");
}
pstmt.close();
rs.close();
conn.close();
}
}
```
* 执行SQL
```java
executeUpdate();
```
* SQL注入
* SQL注入是通过操作输入来修改事先定义好的SQL语句，用以达到执行代码对服务器进行**攻击**的方法。
* PreparedStatement原理：
* 好处：
1. 预编译SQL，性能更高
2. 防止SQL注入，将敏感字符进行转移
1. 预编译功能开启：useServerPrepStmts=true;
2. 原理：
* 在获取PreparedStatement对象时，将sql语句发送给mysql服务器进行检查，编译
* 执行时就不用再进行这些步骤了，速度更快
* 如果sql模板一样，则只需要进行一次检查，编译
```java
select * from tb_user where username='zhangsan'
select * from tb_user where username='lisi'
select * from tb_user where username =?
setString(1,"zhangsan");
setString(2,"lisi");
数据库连接池
-
数据库连接池介绍
-
数据库连接池就是一个容器，负责分配，管理数据库连接 -
它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个 -
释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏 -
好处：
-
数据库连接池实现
-
标准接口：DataSource
- 官方SUN提供的数据库连接池标准接口，由第三方组织实现此接口
- 功能：获取连接
Connection getConnection()
-
常见的数据库连接池
- DBCP
- C3P0
- Druid(德鲁伊)
- 是阿里巴巴开源的数据库连接池项目
- 功能强大，性能优秀，是java语言最好的数据库连接池之一
-
Druid数据库连接池
使用步骤
package druid.DruidDemo;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
public class DruidDemo {
public static void main(String[] args) throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-dmeo\\src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
JDBC-练习
- 完成商品品牌数据的增删改查操作
- 查询：查询所有的数据
- 添加：添加品牌
- 修改：根据id修改
- 删除：根据id删除
- 准备环境：
-
数据库表tb_brand -
实体类Brand
package com.itheima.pojo;
public class Brand {
private Integer id;
private String brandName;
private String companyName;
private Integer ordered;
private String description;
private Integer status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", brandName='" + brandName + '\'' +
", companyName='" + companyName + '\'' +
", ordered=" + ordered +
", description='" + description + '\'' +
", status=" + status +
'}';
}
}
-
测试用例
- 获取Connection
- 定义SQL select * from tb_brand;
- 获取PreparedStatement对象
- 设置参数 不需要
- 执行SQL
- 处理结果 List
- 释放资源
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class BrandText {
@Test
public void testSelectAll() throws Exception {
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "select * from tb_brand";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
Brand brand = null;
List<Brand> brands = new ArrayList<Brand>();
while(rs.next()) {
int id= rs.getInt("id");
String brandName = rs.getString("brand_name");
String companyName = rs.getString("company_name");
int ordered = rs.getInt("orderd");
String description = rs.getString("descrption");
int status = rs.getInt("status");
brand = new Brand();
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
brands.add(brand);
}
System.out.println(brands);
rs.close();
pstmt.close();
conn.close();
}
}
- 编写SQL语句
insert into tb_brand(brand_name,company_name,orderd,descrption,status)
values(?,?,?,?,?);
- 是否需要? 需要：除了id之外的所有数据
- 返回结果如何封装? boolean
@Test
public void testadd() throws Exception {
String brandName="香飘飘";
String companyName="香飘飘";
int ordered=1;
String description = "绕地球一圈";
int status=1;
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "insert into tb_brand(brand_name,company_name,orderd,descrption,status) values(?,?,?,?,?);";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
int count = pstmt.executeUpdate();
System.out.println(count>0);
pstmt.close();
conn.close();
}
}
- 编写SQL语句
update tb_brand set brand_name=?,company_name=?,orderd=?,descrption=?,status=? where id = ?
- 是否需要参数？，需要：Brand对象所有数据
- 返回结果如何封装？boolean
@Test
public void testUpdate() throws Exception {
String brandName="香飘飘";
String companyName="香飘飘";
int ordered=1000;
String description = "绕地球三圈";
int status=1;
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "update tb_brand set brand_name=?,company_name=?,orderd=?,descrption=?,status=? where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,brandName);
pstmt.setString(2,companyName);
pstmt.setInt(3,ordered);
pstmt.setString(4,description);
pstmt.setInt(5,status);
pstmt.setInt(6,id);
int count = pstmt.executeUpdate();
System.out.println(count>0);
pstmt.close();
conn.close();
}
- 编写SQL语句
delete from tb_brand where id=?
- 是否需要参数? 需要：id
- 返回结果如果封装？boolean
@Test
public void testDelete() throws Exception {
int id = 4;
Properties prop = new Properties();
prop.load(new FileInputStream("src\\druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
Connection conn = dataSource.getConnection();
String sql = "delete from tb_brand where id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
int count = pstmt.executeUpdate();
System.out.println(count>0);
pstmt.close();
conn.close();
}
|