🚀进修MySQL~(伍)
PreparedStatement
SQL 注入问题
通过修改 SQL 指令字符串,使 SQL 服务器执行不正规或者恶意的 SQL 命令
比如客户端可以通过修改 SQL 字符串,可以使服务器传回原本不能显示的数据,或者修改非指定的数据.
区别用法
PreparedStatement 可以防止 SQL 注入,而且效率更高.
import java.sql.Connection; |
IDEA+SQL
图形化的,摸索摸索就差不多会了.
在学这个 SQL 课程时,个人开始用的是 sqlyog,后来卸载了,直接用的 idea 内嵌的.
JDBC+事务
创建表+插入数据
create TABLE jdbcstudy.account
(
id int primary key auto_increment,
name varchar(255),
money DECIMAL
);
insert into jdbcstudy.account(name, money)
VALUES ('A', 1000),
('B', 1000),
('C', 1000);事务
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import twenty_one.jdbc.demo2.utils.JDBCUtils;
public class TestTransaction {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) throws SQLException {
try {
connection = JDBCUtils.getConnection();
//关闭数据库的自动提交功能,同时开启事务
connection.setAutoCommit(false);
String sql = "update account set money = money-500 where id = 1";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
String sql2 = "update account set money = money-500 where id = 2";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//业务完毕,提交事务
connection.commit();
System.out.println("事务操作成功");
} catch (Exception e) {
//如果失败,默认会回滚,也可以在这里自定义
connection.rollback();
e.printStackTrace();
} finally {
JDBCUtils.releaseConnection(connection, preparedStatement, resultSet);
}
}
}
连接池
概念上与线程池相同,因为频繁创建和断开连接耗费大量资源,利用池化技术来
减轻资源损耗.
数据库连接池接口
DataSource
,开源数据源实现类:DBCP
C3P0
Druid: 阿里巴巴
无论使用什么数据源,本质是不变的,DateSource 接口不会变,方法就不会变
用法区别就是 utils 类中,加载驱动,连接 connection 和读取 properties 配置文件不需要我们自己写了
db.properties
# DBCP的设置文件,属性名不能错
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true & characterEncoding = utf8 & useSSL = true
username=root
password=123456utils.DbcpUtils
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DbcpUtils {
private static Properties properties = new Properties();
private static DataSource dataSource = null;
static {
// 加载连接信息
try {
properties.load(new FileInputStream("Java/src/main/java/twenty_one/jdbc/demo3/db.properties"));
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
// 释放资源
public static void releaseConnection(Connection connection, Statement statement, ResultSet resultSet)
throws SQLException {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
}
}TestDataSource
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import twenty_one.jdbc.demo3.utils.DbcpUtils;
public class TestDataSource {
private static Connection connection = null;
private static PreparedStatement preparedStatement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) throws SQLException {
// 连接
connection = DbcpUtils.getConnection();
// ! 插入
// 问号'?'为占位符
String insert = "INSERT INTO jdbcstudy.users(NAME, PASSWORD, email, birthday) VALUES (?,?,?,?);";
//预编译sql
preparedStatement = connection.prepareStatement(insert);
//手动赋值
preparedStatement.setString(1, "weidows");
preparedStatement.setInt(2, 123456);
preparedStatement.setString(3, "utsuko27@qq.com");
preparedStatement.setDate(4, new Date(new java.util.Date().getTime()));
// 执行
if (preparedStatement.executeUpdate() > 0) {
System.out.println("成功.");
}
// ! 查询
String query = "SELECT * From jdbcstudy.users where name = ?;";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, "weidows");
resultSet = preparedStatement.executeQuery();
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("===============================================================");
}
// 断连
DbcpUtils.releaseConnection(connection, preparedStatement, resultSet);
}
}
各名词区别
简单分析jdbc、datasource数据源、数据库驱动,连接池,jdbcTemplate,mybatis、JPA、Hibernate的区别
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ⭐️齐下无贰⭐️!
评论