目录
一、JDBC快速入门
package com.feng.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) throws Exception {
//1.注册驱动(jdk1.8之后此步骤可以跳过)
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接
String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";
String user = "root";
String password = "******";
Connection conn = DriverManager.getConnection(url, user, password);
//3.定义Sql语句
String sql = "UPDATE user set age=27 WHERE id=1;";
//4.获取执行SQL的对象statement
Statement stmt = conn.createStatement();
//5.执行sql
int count = stmt.executeUpdate(sql);
//6.处理结果
System.out.println(count);
//7.释放资源
stmt.close();
conn.close();
}
}
二、JDBC-API
1、DriverManager
(1)、注册驱动
(2)、获取连接
2、Connection
(1)、获取执行sql的对象
(2)、事务管理
package com.feng.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 Exception {
//1.获取数据库连接
String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";
String user = "root";
String password = "*******";
Connection conn = DriverManager.getConnection(url, user, password);
//2.定义Sql语句
String sql1 = "UPDATE user set age=27 WHERE id=1;";
String sql2 = "UPDATE user set age=27 WHERE id=2;";
//3.获取执行SQL的对象statement
Statement stmt = conn.createStatement();
try {
//开启事务
conn.setAutoCommit(false);
//4.执行sql
int count1 = stmt.executeUpdate(sql1);
System.out.println(count1);
//设置异常
int i = 3/0;
int count2 = stmt.executeUpdate(sql2);
//5.处理结果
System.out.println(count2);
//提交事务
conn.commit();
} catch (Exception e) {
//回滚事务
conn.rollback();
e.printStackTrace();
}
//7.释放资源
stmt.close();
conn.close();
}
}
//使用事务管理出现异常时会回滚到之前的状态。
3、Statement:执行SQL语句
(1)、执行DML和DDL语句
package com.feng.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JdbcDemo2 {
public static void main(String[] args) throws Exception {
//1.获取数据库连接
String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";
String user = "root";
String password = "100228";
Connection conn = DriverManager.getConnection(url, user, password);
//2.定义Sql语句:以DML语句为例
String sql = "INSERT INTO `user`(name,age) value('赵六','52');";
//3.获取执行SQL的对象statement
Statement stmt = conn.createStatement();
//4.执行sql语句
int count = stmt.executeUpdate(sql);
//根据执行结果判断是否操作成功
if (count>=1){
System.out.println("添加成功");
}else {
System.out.println("添加失败");
}
//7.释放资源
stmt.close();
conn.close();
}
}
(2)、执行DQL语句
4、ResultSet
代码示例
package com.feng.jdbc;
import javax.sound.midi.Soundbank;
import java.sql.*;
public class JdbcDemo2 {
public static void main(String[] args) throws Exception {
//1.获取数据库连接
String url = "jdbc:mysql://127.0.0.1:3306/learn_jdbc";
String user = "root";
String password = "100228";
Connection conn = DriverManager.getConnection(url, user, password);
//2.定义Sql语句
String sql = "SELECT * from user;";
//3.获取执行SQL的对象statement
Statement stmt = conn.createStatement();
//4.执行sql语句,获的res对象
ResultSet res = stmt.executeQuery(sql);
//5.处理结果,遍历res中的所有数据
//5.1光标下移一行,并判断当前行是否有效
while (res.next()){
//5.2获取数据
int id = res.getInt(1);
String name = res.getString(2);
int age = res.getInt(3);
System.out.println(id);
System.out.println(name);
System.out.println(age);
System.out.println("***************");
}
//6.释放资源
res.close();
stmt.close();
conn.close();
}
}
结果
5、PreparedStatement
三、数据库连接池
1、数据库连接池简介
2、数据库连接池实现
3、Druid数据库连接池
(1)、使用步骤
A、在src目录下配置druid.properties文件,配置内容如下
# 数据库驱动
driverClassName=com.mysql.cj.jdbc.Driver
# 数据库连接
url=jdbc:mysql:///learn_jdbc?useSSL=false&useServerPrepStmts=true
# 数据库用户名
username=root
# 数据库密码
password=*******
#初始化连接数量
initialSize=5
#最大连接数
maxActive=10
#获取连接最大等待时间
maxWait=3000
B、Java代码
package 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 Demo1 {
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 conn = dataSource.getConnection();
System.out.println(conn);
}
}
结果如下
四、添加数据代码(删除和修改与之类似)
//加载配置文件
Properties pro = new Properties();
pro.load(new FileInputStream("C:\\Users\\fw\\Desktop\\jdbc\\jdbc-demo\\src\\druid.properties"));
//创建连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(pro);
//获取连接
Connection conn = dataSource.getConnection();
//定义sql语句
String sql = "INSERT INTO tb_brand(brand_name,company_name,ordered,description,status) VALUES(?,?,?,?,?);";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//传入参数
String brandName = "oppo";
String companyName = "oppo有限公司";
int order = 20;
String description = "充电5分钟,通话两小时";
int status = 1;
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, order);
pstmt.setString(4, description);
pstmt.setInt(5, status);
//执行sql
int count = pstmt.executeUpdate();
System.out.println(count>0);
//关闭资源
pstmt.close();
conn.close();