一:JDBC
概念:Java DataBase Connectivity (Java数据库连接,Java语言操作数据库)
本质:由SUN公司定义的一套操作所有关系型数据库的规则(接口);
各数据库厂商去实现这套接口,提供数据库驱动jar包;
我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类。
使用步骤:
1,导入驱动jar包
2,注册驱动
3,获取数据库连接对象connection
4,定义SQL
5,获取执行SQL语句的对象Statement
6,执行SQL,接受返回结果
7,处理结果
8,释放资源
代码案例:
import java.sql.*;
public class JdbcTest01 {
public static void main(String[] args) {
//1,导入驱动JAR包,定义数据
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
String URL = "jdbc:mysql://localhost:3306/mysql";
String USERNAME = "root";
String PASSWORD = "123456";
try {
//2,注册驱动
Class.forName("com.mysql.jdbc.Driver");
//3,获取数据库连接对象 Connection
connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
//4,定义SQL语句
String SQL = "SELECT * FROM USER WHERE USER = 'root'";
//5,获取执行SQL的对象Statement
statement = connection.createStatement();
//6,执行SQL
resultSet = statement.executeQuery(SQL);
//7,处理结果集
while(resultSet.next()) {
String host = resultSet.getString("host");
String name = resultSet.getString("user");
System.out.println(host);
System.out.println(name);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
//8,关闭资源
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
使用对象详解:
DriverManager:驱动管理对象;
功能:
A:注册驱动(其意为告知程序具体该使用哪一个数据库驱动的jar):
查阅DriverManager类的源码可知,其提供了静态的方法 :
static void registerDriver(java.sql.Driver driver);
但上文代码案例中注册驱动却是:Class.forName("com.mysql.jdbc.Driver");
继续查阅 Driver 的源码,可发现:
在Driver中调用了DriverManager类的registerDriver方法;
因此实际进行注册驱动操作的是DriverManager。
在mysql5之后,注册驱动的代码可以省略,其底层已经帮助了我们自动注册驱动。
B:获取数据库连接:
在DriverManager中提供下面这个方法:
static Connection getConnection(String url, String user, String password) ;
其中三个参数解释为:url : 指定连接的路径;user:用户名;password:密码
Connection:数据库连接对象;
功能:
A:获取执行SQL的对象
提供了 Statement createStatement() ;
PreparedStatement prepareStatement(String sql)两个方法
B:管理事务
开启事务:setAutoCommit(boolean autoCommit) 调用该方法设置参数为false
提交事务:conmmit()
回滚事务:rollback()
Statement:执行SQL对象;
功能:用于执行静态SQL
A:boolean execute(String sql):可以执行任意的SQL语句
B:int executeUpdate(String sql):
执行DML语句(insert,update,delete);
DDL语句(create,alter,drop);
返回值为受到操作的行数
C:ResultSet executeQuery(String sql):执行DQL语句(select)
ResultSet:结果集对象;封装查询结果的对象
功能:封装查询结果的对象
A:next():游标向下移动一行,返回值为布尔类型,判断下一行是否有数据,
如果有返回true,如果没有返回false。
B:getXXX(参数):获取数据的方法,
例:getInt,getString,具体类型由数据库类型来对应;
参数有两种,一种为int类型,代表列的编号,这里从1开始,
另一种为String类型,代表列的名称,例:getString("name")
PreparedStatement:执行SQL对象,Statement的子类;
功能:执行预编译SQL的对象
A:SQL注入问题:在拼接SQL时,一些SQL的特殊关键字参与字符串的拼接,
会造成安全性问题,使用 PreparedStatement 来解决SQL注入问题。
预编译SQL:参数使用?作为占位符。
B:Connection 对象的 prepareStatement(sql) 方法;
注意:该方法需要传递带有占位符的SQL语句作为参数,
编写JDBC工具类:
Item实体类:
package pojo;
import java.math.BigDecimal;
import java.util.Date;
public class Item {
private Long id; //商品编号
private String title; //标题
private String sellPoint; //买点
private BigDecimal price; //价格
private Integer num; //数量
private String barcode; //条形码
private String image; //图片
private Long cid; //外键,详情ID
private Integer status; //状态 1:正常,2下架
private Date created; //创建时间
private Date updated; //修改时间
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSellPoint() {
return sellPoint;
}
public void setSellPoint(String sellPoint) {
this.sellPoint = sellPoint;
}
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
public String getBarcode() {
return barcode;
}
public void setBarcode(String barcode) {
this.barcode = barcode;
}
public String getImage() {
return image;
}
public void setImage(String image) {
this.image = image;
}
public Long getCid() {
return cid;
}
public void setCid(Long cid) {
this.cid = cid;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Date getCreated() {
return created;
}
public void setCreated(Date created) {
this.created = created;
}
public Date getUpdated() {
return updated;
}
public void setUpdated(Date updated) {
this.updated = updated;
}
@Override
public String toString() {
return "Item [id=" + id + ", title=" + title + ", sell_point=" + sellPoint + ", price=" + price + ", num="
+ num + ", barcode=" + barcode + ", image=" + image + ", cid=" + cid + ", status=" + status
+ ", created=" + created + ", updated=" + updated + "]";
}
}
JdbcUtils工具类:
package jdbcBasic;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* 目的:简化书写
* 1,抽取注册驱动方法
* 2,抽取获取连接对象方法
* 3,抽取释放资源方法
*/
public class JdbcUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
static {
//读取配置文件,获取配置值
try {
//创建 Properties集合类
Properties pro = new Properties();
//获取src路径下的文件
ClassLoader classLoader = JdbcUtils.class.getClassLoader();
URL res = classLoader.getResource("jdbc.properties");
String path = res.getPath();
System.out.println(path);
//加载文件
// pro.load(new FileReader("src/main/resources/jdbc.properties"));
pro.load(new FileReader(path));
url = pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return 连接对象
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
/**
* 释放资源
* @param preparedStatement
* @param connection
* @param resultSet
*/
public static void close(ResultSet resultSet,PreparedStatement preparedStatement,Statement statement,Connection connection){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放资源 事务案例
* @param preparedStatement0
* @param preparedStatement1
* @param preparedStatement2
* @param preparedStatement3
* @param connection
* @param resultSet1
* @param resultSet2
*/
public static void close(PreparedStatement preparedStatement0,
PreparedStatement preparedStatement1,
PreparedStatement preparedStatement2,
PreparedStatement preparedStatement3,
Connection connection,
ResultSet resultSet1,
ResultSet resultSet2){
if(resultSet1 != null){
try {
resultSet1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(resultSet2 != null){
try {
resultSet2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement0 != null){
try {
preparedStatement0.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement1 != null){
try {
preparedStatement1.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement2 != null){
try {
preparedStatement2.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(preparedStatement3 != null){
try {
preparedStatement3.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
jdbc.properties :
url=jdbc:mysql://localhost:3306/jtdb-small
user=root
password=123456
driver=com.mysql.jdbc.Driver
JdbcTest:(Statement版)
package jdbcBasic;
import pojo.Item;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcTest02 {
public static void main(String[] args) {
//1,导入驱动JAR包,定义数据
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//使用工具类获取对象
connection = JdbcUtils.getConnection();
//2,注册驱动
// Class.forName("com.mysql.jdbc.Driver");
//3,获取数据库连接对象 Connection
// connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
//4,定义SQL语句
String SQL = "SELECT * FROM tb_item";
//5,获取执行SQL的对象Statement
statement = connection.createStatement();
//6,执行SQL
resultSet = statement.executeQuery(SQL);
//7,处理结果集
List<Item> list = new ArrayList<Item>();
Item item = new Item();
while(resultSet.next()) {
Long id = resultSet.getLong("id");
String title = resultSet.getString("title");
item.setId(id);
item.setTitle(title);
list.add(item);
}
for (Item i : list) {
System.out.println(i);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(resultSet,null,statement,connection);
}
}
}
JdbcTest:(PreparedStatement版)
package jdbcBasic;
import pojo.Item;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class JdbcTest03 {
public static void main(String[] args) {
//1,导入驱动JAR包,定义数据
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//使用工具类获取对象
connection = JdbcUtils.getConnection();
//2,注册驱动
// Class.forName("com.mysql.jdbc.Driver");
//3,获取数据库连接对象 Connection
// connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
//4,定义SQL语句
String SQL = "SELECT * FROM tb_item WHERE 1 = 1 AND ID = ?";
//5,获取执行SQL的对象Statement
preparedStatement = connection.prepareStatement(SQL);
//用控制台出入替换占位符
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的ID:");
String name = scanner.nextLine();
preparedStatement.setString(1,name);
System.out.println("打印SQL语句:"+SQL);
//6,执行SQL
resultSet = preparedStatement.executeQuery(); //这里与Statement不同,preparedStatement不需要传入SQL
//7,处理结果集
List<Item> list1 = new ArrayList<Item>();
Item item = new Item();
while(resultSet.next()) {
Long id = resultSet.getLong("id");
String title = resultSet.getString("title");
item.setId(id);
item.setTitle(title);
list1.add(item);
}
for (Item i : list1) {
System.out.println(i);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//使用工具类关闭资源
JdbcUtils.close(resultSet,preparedStatement,null,connection);
}
}
}
JDBC事务:
事务概念:一个包含多个步骤的业务操作,若这个业务操作被事务管理;
则这多个步骤要么同时成功,要么同时失败。
事务操作:
A:开启事务:在执行SQL之前开启事务
B:提交事务:当业务操作所有SQL都执行完毕后提交事务
C:回滚事务:当出现异常,在catch中回滚事务
事务使用:使用Connection对象管理事务
开启事务:setAutoCommit(boolean autoCommit) 调用该方法设置参数为false
提交事务:conmmit()
回滚事务:rollback()
事务代码案例:
package jdbcBasic;
import pojo.Item;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class JdbcTest04 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement0 = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
PreparedStatement preparedStatement3 = null;
ResultSet resultSet1 = null;
ResultSet resultSet2 = null;
try {
//使用工具类获取对象
connection = JdbcUtils.getConnection();
connection.setAutoCommit(false); //开启事务
//事务案例:修改商品价格,1,查询原商品,2,加价格,3,减价格,4,查询修改后商品
//先加500,后减去200
//定义SQL:
String sql0 = "SELECT * FROM tb_item ti WHERE ti.id = ?";
String sql1 = "UPDATE tb_item ti set ti.price = ti.price + ? WHERE ti.id = ?";
String sql2 = "UPDATE tb_item ti set ti.price = ti.price - ? WHERE ti.id = ?";
String sql3 = "SELECT * FROM tb_item ti WHERE ti.id = ?";
//获取控制台输入
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要操作商品的ID:");
String id = scanner.nextLine();
System.out.println("请输入要增加的价格:");
int price1 = scanner.nextInt();
System.out.println("请输入要减去的价格:");
int price2 = scanner.nextInt();
//获取执行SQL的对象
preparedStatement0 = connection.prepareStatement(sql0);
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement2 = connection.prepareStatement(sql2);
preparedStatement3 = connection.prepareStatement(sql3);
//SQL语句动态赋值
preparedStatement0.setString(1,id);
preparedStatement1.setInt(1,price1);
preparedStatement1.setString(2,id);
preparedStatement2.setInt(1,price2);
preparedStatement2.setString(2,id);
preparedStatement3.setString(1,id);
//执行SQL
resultSet1 = preparedStatement0.executeQuery();
preparedStatement1.executeUpdate();
int i = 3/0; //手动制造异常,如果没有事务,会造成可以增加价格,但减少价格却没有操作
preparedStatement2.executeUpdate();
resultSet2 = preparedStatement3.executeQuery();
//显示数据
List<Item> list1 = new ArrayList<Item>();
List<Item> list2 = new ArrayList<Item>();
Item item1 = new Item();
Item item2 = new Item();
while(resultSet1.next()) {
Long ids = resultSet1.getLong("id");
String title = resultSet1.getString("title");
BigDecimal price = resultSet1.getBigDecimal("price");
item1.setId(ids);
item1.setTitle(title);
item1.setPrice(price);
list1.add(item1);
}
for (Item item : list1) {
System.out.println(item);
}
while(resultSet2.next()) {
Long ids = resultSet2.getLong("id");
String title = resultSet2.getString("title");
BigDecimal price = resultSet2.getBigDecimal("price");
item2.setId(ids);
item2.setTitle(title);
item2.setPrice(price);
list2.add(item2);
}
for (Item item : list2) {
System.out.println(item);
}
connection.commit(); //提交事务
} catch (Exception e) {
try {
if (connection != null){
connection.rollback(); //回滚事务
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
}finally {
//使用工具类关闭资源
JdbcUtils.close(preparedStatement0,preparedStatement1,preparedStatement2,preparedStatement3,connection,resultSet1,resultSet2);
}
}
}
本文只是个人学习内容整理的笔记,如有侵权,即联系870013269@qq.com删除