MySQL05：JDBC
为了简化Java对数据库的统一操作，在数据库驱动和应用程序之间增加了一个JDBC接口
CURD操作：create、update、read、delete
架构：没有什么是加一层解决不了的
IDEA连接数据库
需要用到的包：java.sql、javax.sql
导入数据库驱动：mysql-connector-java-5.1.47.jar
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, 'zhansan', '123456', 'zhangsan@qq.com', '1990-12-12'), (2, 'lisi', '123456', 'lisi@qq.com', '1993-1-12'), (3, 'wangwu', '123456', 'wangwu@qq.com', '1994-5-12');
import java.sql.*;
public class Main {
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=false&serverTimezone=GMT%2B8";
String username = "root";
String password = "123456";
/**
* 3、连接数据库，获得数据库对象，DriverManager.getConnection()
* connection对象可以设置自动提交、事务提交、事务回滚
*/
Connection connection = DriverManager.getConnection(url, username, password);
/**
* 4、获得执行SQL的Statement对象，createStatement()
* 用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可
*/
Statement statement = connection.createStatement();
/**
* 5、执行SQL的语句
* executeQuery()，查询，返回结果集
* executeUpdate()，更新、插入、删除都是这个方法
* execute()，可以执行任何sql语句，需要进行判断，效率低
*/
String sql = "SELECT * FROM `users`;";
ResultSet resultSet = statement.executeQuery(sql);
/**
* 6、取出结果集
* getObject、getString()、getInt()、getFloat()、getDate()...
*/
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"));
}
/**
* 7、释放资源
*/
resultSet.close();
statement.close();
connection.close();
}
}
封装工具类
/**
* db.properties
* 配置文件
*/
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username=root
password=123456
/**
* 工具类
*/
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
/**
* 1、驱动只用加载一次
*/
Class.forName(driver);
} catch (Exception e){
e.printStackTrace();
}
}
/**
* 2、获取连接
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
/**
* 3、释放资源
*/
public static void release(Connection connection, Statement statement, ResultSet resultSet){
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();
}
}
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
/**
* 连接数据库，获得数据库对象和执行SQL的Statement对象
*/
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
/**
* 执行SQL的语句
*/
String sql = "SELECT * FROM `users`;";
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"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
/**
* 在finally语句中释放资源
*/
JdbcUtils.release(connection, statement, resultSet);
}
}
}
防止SQL注入
使用PreparedStatement对象预编译SQL语句，再执行
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
/**
* prepareStatement()方法预编译SQL语句，使用占位符?
* setInt()、setString()、setDate()等方法传参
*/
String sql = "INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`) VALUES (?, ?, ?, ?, ?);";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 4);
preparedStatement.setString(2, "ty");
preparedStatement.setString(3, "3456");
preparedStatement.setString(4, "123@qq.com");
preparedStatement.setDate(5, new java.sql.Date(System.currentTimeMillis()));
/**
* 再执行SQL语句
*/
int res = preparedStatement.executeUpdate();
if (res > 0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
}
提交事务
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
/**
* 1、关闭数据库自动提交，同时开启事务
*/
connection.setAutoCommit(false);
/**
* 2、执行事务
*/
String sql1 = "UPDATE `account` SET `money` = `money` - 500 WHERE `name` = 'A';";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
/**
* 如果在提交事务之前报错，会自动回滚
*/
int a = 1/0;
String sql2 = "UPDATE `account` SET `money` = `money` + 500 WHERE `name` = 'B';";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
/**
* 3、提交事务
*/
connection.commit();
System.out.println("事务执行成功！");
} catch (SQLException e) {
/**
* 4、提交失败，自动回滚
* 默认会自动回滚，可以不写
*/
try {
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JdbcUtils.release(connection, preparedStatement, null);
}
}
}
浙公网安备 33010602011771号