一、第一个JDBC程序
1.创建一个普通项目
2.导入数据库驱动
- 在项目下创建lib目录
- 导入mysql驱动的jar包
- 右键lib目录,将该jar包导入项目中
- 点击ok即可
3.编写测试代码
数据库的代码:
CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `jdbcStudy`;
CREATE TABLE `users`(
`id` INT PRIMARY KEY,
`NAME` VARCHAR(40),
`PASSWORD` VARCHAR(40),
`email` VARCHAR(60),
birthday DATE
);
INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
java代码:
package com.yang;
import java.sql.*;
public class JDBC01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.url和用户信息
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "root";
// 3.创建数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行sql的对象
Statement statement = connection.createStatement();
// 5.sql语句
String sql = "select * from users";
// 查询返回的结果集,里面包含所有的查询信息
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("name"));
System.out.println("password=" + resultSet.getObject("password"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birthday=" + resultSet.getObject("birthday"));
System.out.println("=======================");
}
// 6.释放资源
resultSet.close();
statement.close();
connection.close();
}
}
二、封装工具类
1.src目录下创建.properties配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root
2、创建工具类
package com.yang.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtil {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
// 释放资源
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
// 加载mysql驱动
static {
try {
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
3、测试
package com.yang;
import com.yang.utils.JDBCUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC02 {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultSet = null;
@Test
public void query(){
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "select * from users where id = 1";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getObject("name"));
}
} catch (SQLException e){
e.printStackTrace();
} finally {
JDBCUtil.release(connection,statement,resultSet);
}
}
@Test
public void update() {
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "update users set name = 'tom',email='tom@qq.com' where id = 1";
int rows = statement.executeUpdate(sql);
System.out.println(rows > 0 ? "修改成功" : "修改失败");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection,statement,resultSet);
}
}
@Test
public void delete() {
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "delete from users where id = 4";
int rows = statement.executeUpdate(sql);
System.out.println(rows > 0 ? "删除成功" : "删除失败");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection,statement,resultSet);
}
}
@Test
public void insert() {
try {
connection = JDBCUtil.getConnection();
statement = connection.createStatement();
String sql = "insert into users values (4,'jack','123456','jack@qq.com','2024-7-24')";
int rows = statement.executeUpdate(sql);
System.out.println(rows > 0 ? "添加成功" : "添加失败");
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(connection,statement,resultSet);
}
}
}
三、防止SQL注入
PreparedStatement对象
package com.yang;
import com.yang.utils.JDBCUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class JDBC03 {
private Connection connection;
private PreparedStatement preparedStatement;
private ResultSet resultSet;
@Test
public void query(){
try {
connection = JDBCUtil.getConnection();
String sql= "select `name`,`password` from users where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,3);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("password"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.release(connection,preparedStatement,resultSet);
}
}
@Test
public void update(){
try {
connection = JDBCUtil.getConnection();
String sql = "update users set `name` = ?,`email`= ? where `id` = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql); // 预编译
preparedStatement.setString(1,"milan");
preparedStatement.setString(2,"milan@qq.com");
preparedStatement.setInt(3,1);
int rows = preparedStatement.executeUpdate();// 执行sql
System.out.println(rows > 0 ? "修改成功":"修改失败");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.release(connection,preparedStatement,null);
}
}
@Test
public void delete(){
try {
connection = JDBCUtil.getConnection();
String sql = "delete from users where id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql); // 预编译
preparedStatement.setInt(1,5);
int rows = preparedStatement.executeUpdate();// 执行sql
System.out.println(rows > 0 ? "删除成功":"删除失败");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.release(connection,preparedStatement,null);
}
}
@Test
public void insert(){
try {
connection = JDBCUtil.getConnection();
String sql = "insert into users values (?,?,?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql); // 预编译
preparedStatement.setInt(1,5);
preparedStatement.setString(2,"smith");
preparedStatement.setString(3,"123456");
preparedStatement.setString(4,"smith@qq.com");
preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
int rows = preparedStatement.executeUpdate();// 执行sql
System.out.println(rows > 0 ? "添加成功":"添加失败");
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JDBCUtil.release(connection,preparedStatement,null);
}
}
}
四、JDBC实现事务
mysql代码:
CREATE TABLE account(
id Int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(40),
money FLOAT
);
/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('B',1000);
java代码:
package com.yang;
import com.yang.utils.JDBCUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBC04 {
private Connection connection;
private PreparedStatement preparedStatement;
private ResultSet resultSet;
@Test
public void test(){
try {
connection = JDBCUtil.getConnection();
connection.setAutoCommit(false); // 关闭自动提交,开启事务
String sql01 = "update account set `money`=`money`-100 where id = ?";
preparedStatement = connection.prepareStatement(sql01);
preparedStatement.setInt(1,1);
preparedStatement.executeUpdate();
// int b = 1 / 0;
String sql02 = "update account set `money`=`money`+100 where id = ?";
preparedStatement = connection.prepareStatement(sql02);
preparedStatement.setInt(1,2);
preparedStatement.executeUpdate();
connection.commit(); // 提交事务
System.out.println("转账成功!");
} catch (SQLException e) {
try {
// 出现错误后回滚
connection.rollback(); // jdbc开启事务后默认出现错误会回滚,可以省略
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
} finally {
JDBCUtil.release(connection,preparedStatement,resultSet);
}
}
}
注意:
- 开启事务 setAutoCommit(false)
- 一组业务执行完毕,提交事务
- JDBC实现事务会默认实现事务的回滚功能,也可以显示的定义回滚的语句