一、JDBC简介
- JDBC(Java DataBase Connectivity, java数据库连接)是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范,可以为不同的关系型数据库提供统一访问,它由一组用Java语言编写的接口(大部分)和类组成。
- JDBC需要连接驱动,驱动的作用是实现JDBC接口,在实现类中可以对应的操作数据库。JDBC与数据库驱动的关系如图所示,JDBC作为Java的接口提供了操作数据库的统一规范,各数据库只要实现这套接口,就可以完成Java与数据库的对接。也就是说,驱动一般都由数据库生产厂商提供。
- 因此在进行Java与数据库通信时首先要将供应商提供的数据库驱动程序包含进项目中
二、准备:导入MySQL的驱动
导入MySQL的驱动jar包到工程中。过程参考下图:
驱动:mysql-connection-java-5.1.30.jar。
MySQL连接驱动下载地址:https://dev.mysql.com/downloads/connector/j/
三、jdbc的增删改查的普通实现
/**
* 查询多条记录
*/
private static void select() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//注册驱动(采用反射机制可以保证驱动只注册一次。)
Class.forName("com.mysql.jdbc.Driver");
//获取和数据库的连接对象:
conn = DriverManager.getConnection("jdbc:mysql:///web_test","root","123456");
//创建执行SQL语句对象
stmt = conn.createStatement();
//编写SQL
String sql = "select * from user";
//执行SQL语句
rs = stmt.executeQuery(sql);
//遍历结果集
while(rs.next()) {
System.out.println(rs.getInt("id") + "," + rs.getString("username") + "," + rs.getString("password") + "," + rs.getString("nickname") + "," + rs.getInt("age"));
}
}catch(Exception e){
e.printStackTrace();
}finally {
//释放资源
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
/**
* 删除
*/
private static void delete() {
Connection conn = null;
Statement stmt = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接
conn = DriverManager.getConnection("jdbc:mysql:///web_test","root","123456");
//创建执行SQL语句对象
stmt = conn.createStatement();
//编写SQL
String sql = "delete from user where id = 5";
//执行SQL
int num = stmt.executeUpdate(sql);
if(num >0) {
System.out.println("删除用户成功!");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
/**
* 修改
* @throws SQLException
*/
public static void update(){
Connection conn = null;
Statement stmt = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接
conn = DriverManager.getConnection("jdbc:mysql:///web_test","root","123456");
//执行操作
//创建执行SQL语句的对象
stmt = conn.createStatement();
//编写SQL语句
String sql = "update user set password='abc',nickname='旺财' where id = 5";
//执行SQL语句
int num = stmt.executeUpdate(sql);
if(num>0) {
System.out.println("用户修改成功!");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
/**
* 保存
*/
public static void insert(){
Connection conn = null;
Statement stmt = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接
conn = DriverManager.getConnection("jdbc:mysql:///web_test","root","123456");
//执行操作
//创建执行SQL语句对象
stmt = conn.createStatement();
//编写SQL语句
String sql = "insert into user values (null,'eee','123','阿黄','21')";
//执行SQL语句
int num = stmt.executeUpdate(sql);
if(num>0) {
System.out.println("保存用户成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally {
//资源释放
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
四、jdbc的增删改查的升级版
1、准备:
配置文件:db.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///web_test?rewriteBatchedStatements=True
username=root
password=123456
JDBCUtils工具类:
/**
* JDBC的工具类
* @author wanzhaocheng
*
*/
public class JDBCUtils {
private static final String driverClassName;
private static final String url;
private static final String username;
private static final String password;
static {
//获取配置文件中的内容
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src/db.properties"));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
driverClassName = properties.getProperty("driverClassName");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
}
// static {
// driverClassName = "com.mysql.jdbc.Driver";
// url = "jdbc:mysql:///web_test";
// username = "root";
// password = "123456";
// }
/**
* 注册驱动的方法
*/
public static void loadDriver() {
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获得连接的方法
*/
public static Connection getConnection() {
Connection conn = null;
try {
//将驱动一并注册
loadDriver();
//获得连接
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放资源的方法
* @return
*/
public static void release(Statement stmt,Connection conn) {
//释放资源
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
public static void release(ResultSet rs, Statement stmt,Connection conn) {
//释放资源
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
2、jdbc的增删改查利用自定义工具类的实现
- PreparedStatement的优点:防止SQL注入,执行效率高
- Statement主要用于执行静态的SQL语句。如果操作动态的SQL语句,则可以使用PreparedStatement来提高执行效率。可以使用Connection的preparedStatement()方法来建立一个预先编译的SQL语句,其余需要改变的参数使用占位符“?”来代替。等需要指定真正的参数时,再使用相对应的setxxxx(int parameterIndex,值)的方法,指定“?”处对应的参数值。
@Test
/**
* 查询单条记录
*/
private static void select2() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//获得连接
conn = JDBCUtils.getConnection();
//编写SQL语句
String sql = "select * from user where id = ?";
//预编译SQL
pstmt = conn.prepareStatement(sql);
//填充变量
pstmt.setString(1,"3");
//执行SQL语句
rs = pstmt.executeQuery();
//遍历结果集
while(rs.next()) {
System.out.println(rs.getInt("id") + "," + rs.getString("username") + "," + rs.getString("password") + "," + rs.getString("nickname") + "," + rs.getInt("age"));
}
}catch(Exception e){
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.release(rs, pstmt, conn);
}
}
/**
* 保存
*/
public static void insert(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
//获得连接
conn = JDBCUtils.getConnection();
//编写SQL语句
String sql = "insert into user values (null,?,?,?,?)";
//预编译SQL
pstmt = conn.prepareStatement(sql);
//添加数据
pstmt.setString(1, "abcd");
pstmt.setString(2, "123");
pstmt.setString(3, "小黄");
pstmt.setString(4, "20");
//执行SQL
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally {
//资源释放
JDBCUtils.release(pstmt, conn);
}
}
五、批处理的操作
/**
* 批处理的操作
* @author wanzhaocheng
*
*/
public class JDBCDemo5 {
@Test
/**
* 批量插入记录
* *默认情况下,MySQL批处理没有开启,
* 需要在url后面拼接一个参数即可:rewriteBatchedStatements=True
*/
public void demo() {
//记录开始时间
long startTime = System.currentTimeMillis();
Connection conn = null;
PreparedStatement pstmt = null;
try {
//获得连接
conn = JDBCUtils.getConnection();
//编写SQL语句
String sql = "insert into user values (null,?,?,?,?)";
//预编译SQL
pstmt = conn.prepareStatement(sql);
for(int i=1;i<=10000;i++) {
pstmt.setString(1, "name"+i);
pstmt.setString(2, "123");
pstmt.setString(3, "nickname");
pstmt.setString(4, "20");
//添加到批处理
pstmt.addBatch();
//注意问题:一次执行数据太多,容易导致内存溢出
//每一千条数据执行一次批处理
if(i % 1000 == 0) {
//执行批处理
pstmt.executeBatch();
//清空批处理
pstmt.clearBatch();
}
}
}catch(Exception e){
e.printStackTrace();
}finally {
JDBCUtils.release(pstmt, conn);
}
//记录结束时间
long endTime = System.currentTimeMillis();
System.out.println((endTime - startTime)+"毫秒");
}
}
六、事务管理
/**
* 事务管理的测试类
* @author wanzhaocheng
*
*/
public class TransactionDemo1 {
@Test
/**
* 完成转账的案例
*/
public void demo() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
/**
* 完成转账代码:
* * 扣除A账户的钱
* * 给另一个账号加钱
*/
//获得连接
conn = JDBCUtils.getConnection();
//1、开启事务
conn.setAutoCommit(false);
//编写SQL语句
String sql = "update account set money = money + ? where name = ?";
//预编译SQL
pstmt = conn.prepareStatement(sql);
//用aaa账号给bbb账号转1000元
pstmt.setDouble(1, 1000);
pstmt.setString(2, "bbb");
//执行SQL
pstmt.executeUpdate();
//给bbb加钱
pstmt.setDouble(1, 1000);
pstmt.setString(2, "bbb");
pstmt.executeUpdate();
//2、提交事务
conn.commit();
}catch(Exception e){
//3、回滚事务
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JDBCUtils.release(pstmt, conn);
}
}
}
代码下载: https://pan.baidu.com/s/1CBSQIvOhJRJGKtmdVSXRpA
提取码: fci7