JDBC
概念
Java DataBase Connectivity Java数据库连接,Java语言操作数据库
JDBC本质:其实是官方(sun公司)定义的一套操作所有关系型数据库的规则,即接口。各个数据库厂商去实现这套接口,提供数据库厂商去实现这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类。
快速入门:
- 步骤:
- 1.导入驱动jar包 mysql-connector-java-5.1.18-bin.jar
- 1)复制 mysql-connector-java-5.1.18-bin.jar到项目的libs目录下
- 2)右键 ==》
- 2.注册驱动
- 3.获取数据库连接对象 Connection
- 4.定义sql
- 5.获取执行sql语句的对象 Statement
- 6.执行sql,接收返回结果
- 7.处理结果
- 8.释放资源
- 1.导入驱动jar包 mysql-connector-java-5.1.18-bin.jar
- 代码实现:
public class JdbcDemo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.导入驱动jar包
//2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//3.获取数据库连接对象
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/province", "root", "root");
//4.定义sql语句
String sql = "update pro set name = '贵州' where id = 4";
//5.获取执行sql的对象 Statement
Statement statement = connection.createStatement();
//6.执行sql
int i = statement.executeUpdate(sql);
//7.处理结果
System.out.println(i);
//8.释放资源
statement.close();
connection.close();
}
}
详解各个对象
DriverManager:驱动管理对象
-
功能:
-
1.注册驱动:告诉程序该使用哪一个数据库jar包
- static void registerDriver(Driver driver):注册与给定的驱动程序 DriverManager。
- 写代码使用:Class.forName(“com.mysql.jdbc.Driver”);
- 通过查看源码发现:在com.mysql.jdbc.Driver类中存在静态代码块
static { try { DriverManager.registerDriver(new Driver()); } catch (SQLException var1) { throw new RuntimeException("Can't register driver!"); } }
- 注意mysql5之后的驱动jar包可以省略注册驱动的步骤
-
2.获取数据库连接:
- 方法:static Connection getConnection(String url,String user,String password)
- 参数:
- url:指定连接的路径
- 语法:jdbc:mysql://ip地址(域名:端口号)/数据库名称
- 例子:jdbc:mysql://localhost:3306/province
- 细节:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为:jdbc:mysql:///province
- user:用户名
- password:密码
- url:指定连接的路径
-
Connection:数据库连接对象
- 1.功能:
- 1.获取执行sql的对象
- Statement createStatement()
- PreparedStatement prepareStatement(String sql)
- 2.管理事务
- 开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即开启事务
- 提交事务:commit()
- 回滚事务:rollback()
- 1.获取执行sql的对象
Statement:执行sql的对象
-
1.执行sql
- 1)boolean execute(Sting sql):可以执行任意sql语句(了解)
- 2)int executeUpdate(Sting sql):执行DML(insert,update,delete)语句、DDL(create,alter,drop)语句
- 返回值:影响的行数,可以通过这个影响的行数判断判断DML语句是否执行成功,返回值>0的则执行成功,反之,则失败。
- 3)ResultSet executeQuery(String sql):执行DQL(select)语句
-
2.练习:
-
1)添加一条记录
/** * 添加一条记录 insert语句 */ public class JdbcDemo3 { public static void main(String[] args) { //1.导入jar包 Connection connection = null; Statement statement = null; try { //2.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //3.连接到数据库 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/province", "root","root"); //4.sql语句 String sql = "insert into pro value(null,'南充')"; //5.创建sql执行对象 statement = connection.createStatement(); //6.执行sql语句,接受结果 int count = statement.executeUpdate(sql); //7.处理结果 System.out.println(count); if(count > 0){ System.out.println("添加成功!"); }else { System.out.println("添加失败!"); } //8.释放资源 } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ //避免空指针异常 if(statement != null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection != null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
-
2)修改一条记录
/** * 修改一条记录 */ public class jdbcDemo4 { public static void main(String[] args) { //1.导入jar包 Connection connection = null; Statement statement = null; try { //2.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //3.注册连接 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/province", "root", "root"); //4.定义sql String sql = "update pro set name = '贵州' where id = 5"; //5.获取执行sql对象 statement = connection.createStatement(); //6.执行sql int count = statement.executeUpdate(sql); //7.处理结果 System.out.println(count); if(count > 0){ System.out.println("修改成功!"); }else{ System.out.println("修改失败!"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { //8.释放资源 //9.预防空指针异常 if(statement != null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if(connection != null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
-
3)删除一条记录
/** * 删除一条记录 */ public class jdbcDemo5 { public static void main(String[] args) { Statement statement = null; Connection connection = null; //1.导入jar包 try { //2.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //3.连接数据库 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/province", "root", "root"); //4.定义sql String sql = "delete from pro where id = 5"; //5.创建sql执行对象 statement = connection.createStatement(); //6.执行sql int count = statement.executeUpdate(sql); //7.处理结果 System.out.println(count); if(count > 0){ System.out.println("删除成功!"); }else{ System.out.println("删除失败!"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ //8.释放资源 //预防空指针异常 if(statement != null){ try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } if (connection != null){ try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
-
ResultSet:结果集对象,封装查询结果
- boolean next():游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据),如果是,则返回false,如果不是则返回true
- getXxx(参数):获取数据
- Xxx:代表数据类型 如:int getInt() ,String getString()
- 参数:
- 1.int:代表列的编号,从1开始。如:getString(1)
- 2.String:代表列的名称。如:getDouble(“name”)
- 注意:
- 使用步骤:
- 1.游标向下移动一行
- 2.判断是否有数据
- 3.获取数据
- 使用步骤:
//7.处理结果
//7.1让游标向下移动一行
//resultSet.next();
//7.2获取数据
/*int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println(id + ":" + name);*/
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
System.out.println(id + ":" + name);
}
- 练习:
- 定义一个方法,查询emp表的数据将其封装为对象,然后装载为集合,返回。
- 1.定义Emp类
- 2.定义方法public List findAll(){}
- 3.实现方法 select * from emp;
- 定义一个方法,查询emp表的数据将其封装为对象,然后装载为集合,返回。
Emp.java
package com.java.domain;
import java.util.Date;
public class Emp {
private int id;
private String pm25;
private String pm10;
private String co;
private String no2;
private String so2;
private String o3;
private Date brthday;
public Emp() {
}
public Emp(int id, String pm25, String pm10, String co, String no2, String so2, String o3, Date brthday) {
this.id = id;
this.pm25 = pm25;
this.pm10 = pm10;
this.co = co;
this.no2 = no2;
this.so2 = so2;
this.o3 = o3;
this.brthday = brthday;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getPm25() {
return pm25;
}
public void setPm25(String pm25) {
this.pm25 = pm25;
}
public String getPm10() {
return pm10;
}
public void setPm10(String pm10) {
this.pm10 = pm10;
}
public String getCo() {
return co;
}
public void setCo(String co) {
this.co = co;
}
public String getNo2() {
return no2;
}
public void setNo2(String no2) {
this.no2 = no2;
}
public String getSo2() {
return so2;
}
public void setSo2(String so2) {
this.so2 = so2;
}
public String getO3() {
return o3;
}
public void setO3(String o3) {
this.o3 = o3;
}
public Date getBrthday() {
return brthday;
}
public void setBrthday(Date brthday) {
this.brthday = brthday;
}
@Override
public String toString() {
return "Emp{" +
"id=" + id +
", pm25='" + pm25 + '\'' +
", pm10='" + pm10 + '\'' +
", co='" + co + '\'' +
", no2='" + no2 + '\'' +
", so2='" + so2 + '\'' +
", o3='" + o3 + '\'' +
", brthday=" + brthday +
'}';
}
}
JdbcEmp.java
package com.java.jdbc;
import com.java.domain.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcEmp {
public static void main(String[] args) {
List<Emp> list = new JdbcEmp().findAll();
System.out.println(list);
System.out.println(list.size());
}
public List<Emp> findAll(){
//1.导入jar包
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Emp> list = null;
try {
//2.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//3.连接到数据库
connection = DriverManager.getConnection("jdbc:mysql:///province", "root", "root");
//4.定义sql
String sql = "select * from emp";
//5.创建sql的执行对象
statement = connection.createStatement();
//6.执行sql
resultSet = statement.executeQuery(sql);
//7.处理结果
//创建一个Emp对象
Emp emp = null;
//创建一个装载集合list
list = new ArrayList<Emp>();
while (resultSet.next()){
int id = resultSet.getInt("id");
String pm25 = resultSet.getString("pm2.5");
String pm10 = resultSet.getString("pm10");
String co = resultSet.getString("co");
String no2 = resultSet.getString("no2");
String so2 = resultSet.getString("so2");
String o3 = resultSet.getString("o3");
Date time = resultSet.getDate("time");
//将数据封装为对象
emp = new Emp();
emp.setId(id);
emp.setPm25(pm25);
emp.setPm10(pm10);
emp.setCo(co);
emp.setNo2(no2);
emp.setSo2(so2);
emp.setO3(o3);
emp.setBrthday(time);
//装载为集合
list.add(emp);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
//8.释放资源
//预防空指针异常
if (resultSet != null){
try {
resultSet.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();
}
}
}
return list;
}
}
PreparedStatement:执行sql的对象
-
1.sql注入问题:在拼接sql时,有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题
- 1.输入用户随便,输入密码:a’ or ‘a’ = 'a
- 2.sql:select * from user where username = ‘fdsfsafsda’ and password = ‘a’ or ‘a’ = ‘a’ ";
-
2.解决sql注入问题:使用PreparedStatement对象来解决
-
3.预编译的sql:参数使用 ?作为占位符
-
4.步骤:
- 1.导入驱动jar包 mysql-connector-java-5.1.18-bin.jar
- 2.注册驱动
- 3.获取数据库连接对象 Connection
- 4.定义sql
- 注意:sql的参数使用?作为占位符。如:select * from user where username = ? and password = ?;
- 5.获取执行sql语句的对象 PreparedStatement Connection.prepareStatement(String sql)
- 6.给?赋值:
- 方法:setXxx(参数1,参数2)
- 参数1:?的位置编号,从1开始
- 参数2:?的值
- 方法:setXxx(参数1,参数2)
- 7.执行sql,接受返回结果
- 8.处理结果
- 9.释放资源
connection = JDBCUtils.getConnection(); //定义sql String sql = "select * from user where username = ? and password = ? "; //创建sql执行对象 preparedStatement = connection.prepareStatement(sql); //给?赋值 preparedStatement.setString(1,username); preparedStatement.setString(2,password); resultSet = preparedStatement.executeQuery();
-
5.注意:后期都会使用PreparedStatement来完成增删改查的所有操作
- 1.可以防止SQL注入
- 2.效率更高
抽取JDBC工具类:JDBCUtils
- 目的:简化书写
- 分析:
- 1.注册驱动
- 2.抽取一个方法获取连接对象
- 需求:不想传递参数(麻烦),还得保证工具类的通用性
- 解决:配置文件
- jdbc.properties
- url=
- user=
- password=
- driver=
- jdbc.properties
- 3.抽取一个方法释放资源
- 代码实现
package com.java.util;
import com.java.jdbc.Demo7;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
*JDBC工具类
*/
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/**
* 文件的读取,只需要读取一次即可拿到这些值。使用静态代码块
*/
static {
//读取资源文件,获取值
try {
//1.创建Properties集合类
Properties properties = new Properties();
//获取src路径下的文件的方式:ClassLoader:类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
String path = resource.getPath();
System.out.println(path);
//2.加载文件
// properties.load(new FileReader("D:\\IDEA\\advance\\eleven\\src\\jdbc.properties"));
properties.load(new FileReader(path));
//3.获取数据,赋值
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//4.注册驱动
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return 连接对象
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql:///province","root","root");
}
/**
* 释放资源
* @param statement
* @param connection
*/
public static void close(Statement statement,Connection connection){
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放资源
* @param resultSet
* @param statement
* @param connection
*/
public static void close(ResultSet resultSet, Statement statement, Connection connection){
if (resultSet != null){
try {
resultSet.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();
}
}
}
}
练习:
- 需求:
- 1.通过键盘录入用户名和密码
- 2.判断用户是否登录成功
- select * from user where username = “” and password = “”;
- 如果这个sql有查询结果,则成功,反之,则失败
- 实现步骤:
package com.java.jdbc;
import com.java.util.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
/**
* - 1.通过键盘录入用户名和密码
* - 2.判断用户是否登录成功
*/
public class Jdbclogin {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = scanner.nextLine();
System.out.println("请输入密码:");
String password = scanner.nextLine();
boolean flag = new Jdbclogin().login(username, password);
if (flag){
System.out.println("登录成功!");
}else {
System.out.println("登录失败!");
}
}
public boolean login(String username,String password){
Connection connection = null;
ResultSet resultSet = null;
Statement statement = null;
if (username == null || password == null){
return false;
}
try {
connection = JDBCUtils.getConnection();
//定义sql
String sql = "select * from user where username = '"+username+"' and password = '"+password+"' ";
//创建sql执行对象
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(resultSet,statement,connection);
}
return false;
}
}
JDBC控制事务:
1.事务:
一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败
2.操作:
- 1)开启事务
- 2)提交事务
- 3)回滚事务
3.使用Connection对象来管理事务
- 开启事务:setAutoCommit(boolean autoCommit):调用该方法设置参数为false,即开启事务
- 在执行sql之前开启事务
- 提交事务:commit()
- 当所有sql都执行完提交事务
- 回滚事务:rollback()
- 在catch中回滚事务
package com.java.jdbc;
import com.java.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JdbcDemo1 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
try {
//1.获取连接
connection = JDBCUtils.getConnection();
//开启事务
connection.setAutoCommit(false);
//2.定义sql
//2.1张三-500
String sql1 = "update account set balance = balance - ? where id = ?";
//2.2李四+500
String sql2 = "update account set balance = balance + ? where id = ?";
//3.获取执行sql对象
preparedStatement1 = connection.prepareStatement(sql1);
preparedStatement2 = connection.prepareStatement(sql2);
//4.设置参数
preparedStatement1.setInt(1,500);
preparedStatement1.setInt(2,1);
preparedStatement2.setInt(1,500);
preparedStatement2.setInt(2,2);
//5.执行sql
preparedStatement1.executeUpdate();
//手动制造异常
int i = 3 / 0;
preparedStatement2.executeUpdate();
connection.commit();
} catch (SQLException e) {
try {
if (connection != null){
connection.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtils.close(preparedStatement1,connection);
JDBCUtils.close(preparedStatement2,connection);
}
}
}