一、JDBC编程必备条件
- 编程语言,如Java,C、C++、Python等 ;
- 数据库,如Oracle,MySQL,SQL Server等 ;
- 数据库驱动包:不同的数据库,对应不同的编程语言提供了不同的数据库驱动包,如:MySQL提 供了Java的驱动包mysql-connector-java,需要基于Java操作MySQL即需要该驱动包 。
二、JDBC及其工作原理
1、JDBC含义:Java数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
2、JDBC 为多种关系数据库提供了统一访问方式,作为特定厂商数据库访问API的一种高级抽象,它主要包含一些通用的接口类。JDBC访问数据库层次结构如下图所示:
3、JDBC使用步骤:
(1)创建数据库连接Connection,有三种方式:
①DriverManager(驱动管理类)静态方法方式
/**
* DriverManager方式获取数据库连接
* @return
*/
public static Connection getConnection1() {
Connection connection = null;
try {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager
.getConnection(URL,USERNAME,PASSWORD);
} finally {
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
return connection;
}
②DataSource(数据源)静态变量加静态代码块的方式
/**
* DataSource静态变量+静态代码块方式
* @return
*/
private static final String URL = "jdbc:mysql://localhost:3306/students";
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
private static DataSource DATASOURCE = new MysqlDataSource();
static {
((MysqlDataSource)DATASOURCE).setURL(URL);
((MysqlDataSource)DATASOURCE).setUser(USERNAME);
((MysqlDataSource)DATASOURCE).setPassword(PASSWORD);
}
private static DataSource DATASOURCE2;
public static Connection getConnection2(){
try {
return DATASOURCE.getConnection();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
}
以上两种方式的区别为:
1. DriverManager类来获取的Connection连接,是无法重复利用的,每次使用完以后释放资源 时,通过connection.close()都是关闭物理连接。
2. DataSource提供连接池的支持。连接池在初始化时将创建一定数量的数据库连接,这些连接 是可以复用的,每次使用完数据库连接,释放资源调用connection.close()都是将 Conncetion连接对象回收。
③单例模式
/**
* 单例的方式
* @return
*/
public static DataSource getDataSource(){
if(DATASOURCE2 == null){
DATASOURCE2 = new MysqlDataSource();
((MysqlDataSource)DATASOURCE2).setURL(URL);
((MysqlDataSource)DATASOURCE2).setUser(USERNAME);
((MysqlDataSource)DATASOURCE2).setPassword(PASSWORD);
}
return DATASOURCE2;
}
(2)创建操作命令Statement,Statement对象主要是将SQL语句发送到数据库中,有两种方式:
①Statement,用于执行不带参数的SQL语句
Statement statement = connection.createStatement();
②PerpareStatement,带不带参数都可以,会预编译在数据库系统,速度快于Statement。
String sql = "select id,name,chinese,math,english from exam_result" +
" where name=?";
statement = connection.prepareStatement(sql);
(3)使用操作命令来执行SQL,有两种方法(包括增删改查)
① executeQuery() 方法执行后返回单个结果集的,通常用于select语句
resultSet = statement.executeQuery(sql);
② executeUpdate()方法返回值是一个整数,指示受影响的行数,通常用于update、insert、delete 语句
resultSet = statement.executeQuery();
(4)处理结果集ResultSet。
(5)释放资源,后创建的先释放。
四、附完整代码如下
DBUtil类
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.*;
public class DBUtil {
//URL:jdbc:mysql://服务器名称:端口/数据库名?参数名=参数值
private static final String URL = "jdbc:mysql://localhost:3306/students";
private static final String USERNAME = "root";
private static final String PASSWORD = "root";
private static DataSource DATASOURCE = new MysqlDataSource();
static {
((MysqlDataSource)DATASOURCE).setURL(URL);
((MysqlDataSource)DATASOURCE).setUser(USERNAME);
((MysqlDataSource)DATASOURCE).setPassword(PASSWORD);
}
private static DataSource DATASOURCE2;
private DBUtil(){
}
public static void main(String[] args) {
System.out.println(getConnection1());
System.out.println(getConnection2());
System.out.println(getDataSource());
}
/**
* DriverManager方式获取数据库连接
* @return
*/
public static Connection getConnection1() {
Connection connection = null;
try {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager
.getConnection(URL,USERNAME,PASSWORD);
} finally {
connection.close();
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
return connection;
}
/**
* DataSource静态变量+静态代码块方式
* @return
*/
public static Connection getConnection2(){
try {
return DATASOURCE.getConnection();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("数据库连接失败");
}
}
/**
* 单例的方式
* @return
*/
public static DataSource getDataSource(){
if(DATASOURCE2 == null){
DATASOURCE2 = new MysqlDataSource();
((MysqlDataSource)DATASOURCE2).setURL(URL);
((MysqlDataSource)DATASOURCE2).setUser(USERNAME);
((MysqlDataSource)DATASOURCE2).setPassword(PASSWORD);
}
return DATASOURCE2;
}
/**
* 关闭结果集
* @return
*/
public static void close(Connection connection,
Statement statement, ResultSet resultSet){
try {
if(resultSet != null)
resultSet.close();
if(statement != null)
statement.close();
if(connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("数据库释放资源失败");
}
}
}
Query类(使用Statement方式创建Statement对象)
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Query {
public static void main(String[] args) {
queryById("小紫' or '1'='1");
}
public static void queryById(String stuName) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
try {
// 创建数据库连接Connection
connection = DBUtil.getConnection2();
// 根据数据库连接创建操作命令对象Statement
statement = connection.createStatement();
String sql = "select id,name,chinese,math,english from exam_result" +
" where name='"+stuName+"'";
// 操作命令对象执行sql语句,返回结果集resultSet
// resultSet类似 List<Map<String, Object>>
resultSet = statement.executeQuery(sql);
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
BigDecimal chinese = resultSet.getBigDecimal("chinese");
BigDecimal math = resultSet.getBigDecimal("math");
BigDecimal english = resultSet.getBigDecimal("english");
System.out.printf("id=%s, name=%s, chinese=%s, " +
"math=%s, english=%s", id, name, chinese,
math, english);
System.out.println();
}
} finally {
// 释放资源
DBUtil.close(connection, statement, resultSet);
}
}catch (Exception e){
e.printStackTrace();
}
}
}
Query2类(使用PrepareStatement方式创建Statement对象)
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Query2 {
public static void main(String[] args) {
//防止sql注入,所以查询不到数据
queryById("小紫' or '1'='1");
queryById("小紫");
}
public static void queryById(String stuName) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
try {
connection = DBUtil.getConnection2();
String sql = "select id,name,chinese,math,english from exam_result" +
" where name=?";
statement = connection.prepareStatement(sql);
statement.setString(1, stuName);
resultSet = statement.executeQuery();
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
BigDecimal chinese = resultSet.getBigDecimal("chinese");
BigDecimal math = resultSet.getBigDecimal("math");
BigDecimal english = resultSet.getBigDecimal("english");
System.out.printf("id=%s, name=%s, chinese=%s, " +
"math=%s, english=%s", id, name, chinese,
math, english);
System.out.println();
}
} finally {
DBUtil.close(connection, statement, resultSet);
}
}catch (Exception e){
e.printStackTrace();
}
}
}
Update类
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Update {
public static void main(String[] args) {
updateById("小紫", 96);
}
public static void updateById(String stuName, int updateMath) {
Connection connection = null;
PreparedStatement statement = null;
try {
try {
connection = DBUtil.getConnection2();
String sql = "update exam_result set math=?" +
" where name=?";
statement = connection.prepareStatement(sql);
statement.setInt(1, updateMath);
statement.setString(2, stuName);
// SQL新增、修改、删除操作,都使用executeUpdate()方法
int num = statement.executeUpdate();
if(num>0)
System.out.println("修改成功");
else
System.out.println("修改失败");
} finally {
DBUtil.close(connection, statement, null);
}
}catch (Exception e){
e.printStackTrace();
}
}
}
Insert类
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Insert {
public static void main(String[] args) {
insert(8,"小花", 89);
}
public static void insert(int stuId, String stuName, int stuMath) {
Connection connection = null;
PreparedStatement statement = null;
try {
try {
connection = DBUtil.getConnection2();
String sql = "insert into exam_result(id,name,math) values" +
"(?,?,?)";
statement = connection.prepareStatement(sql);
statement.setInt(1, stuId);
statement.setString(2, stuName);
statement.setInt(3, stuMath);
int num = statement.executeUpdate();
if(num>0)
System.out.println("修改成功");
else
System.out.println("修改失败");
} finally {
DBUtil.close(connection, statement, null);
}
}catch (Exception e){
e.printStackTrace();
}
}
}
Delect类
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Delete {
public static void main(String[] args) {
delete(8);
}
public static void delete(int stuId) {
Connection connection = null;
PreparedStatement statement = null;
try {
try {
connection = DBUtil.getConnection2();
String sql = "delete from exam_result" +
" where id=?";
statement = connection.prepareStatement(sql);
statement.setInt(1, stuId);
int num = statement.executeUpdate();
if(num>0)
System.out.println("修改成功");
else
System.out.println("修改失败");
} finally {
DBUtil.close(connection, statement, null);
}
}catch (Exception e){
e.printStackTrace();
}
}
}
补充:JDBC事务的使用
- 执行多个操作:connection.setAutoCommit(flase);
- 提交:connection.commit;
- 异常捕获部分回滚:connection.rollback();