JDBC
JDBC
- 全称 Java DataBase Connectivity, 是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口
- 提供了一种接口基准，可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序
- 应用程序代码一般不能直接访问数据库，需要通过相应的数据库驱动程序才行，
- 数据库驱动就是数据库厂商对JDBC接口的实现
JDBC连接MySQL相关概念
- 数据库驱动：不同数据库开发商(比如oracle mysql等)为了某一种开发语言能够实现统一的数据库调用而开发的一个程序, 作用相当于一个翻译人员, 将某个语言（比如java）中对数据库的调用通过这个翻译成各个种类的数据库 自己的数据库语言
- Connection连接：特定数据库的连接（会话），在连接上下文中执行sql语句并返回结果
- Statement 语句: 创建执行SQL语句的statement, 有好几种实现类，用于执行对应的sql
- ResultSet结果集：SQL查询返回的结果信息
使用Java连接Mysql的步骤
- 加载JDBC驱动程序
- 建立数据库连接Connection
- 创建执行SQL的语句Statement
- 处理执行结果ResultSet
- 释放连接资源
驱动包导入和数据库准备
https://abc1024.oss-cn-shanghai.aliyuncs.com/Picture/Javaweb/mysql-connector-java-5.1.46.jar
数据库表
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`phone` varchar(32) DEFAULT NULL,
`pwd` varchar(128) DEFAULT NULL,
`sex` int(2) DEFAULT NULL,
`img` varchar(128) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`role` int(11) DEFAULT NULL COMMENT '1是普通用户，2是管理员',
`username` varchar(128) DEFAULT NULL,
`wechat` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `user` (`id`, `phone`, `pwd`, `sex`, `img`, `create_time`, `role`, `username`,`wechat`)
VALUES
(1,'123','666',1,'xdclass.net','2021-09-09 00:00:00',1,'jack','xdclass6'),
(2,'2323432','794666918',1,'wwwww','2020-05-20 04:54:01',1,'小滴Anna姐姐','xdclass-anna'),
(3,'2323432','xdclass-lw',1,'wwwww','2020-05-20 04:54:42',1,'二当家小D','xdclass1'),
(4,'2323432','3232323',1,'wwwww','2020-05-20 04:55:07',1,'老王','xdclass-lw');
编写一个JDBC程序
public class JDBCTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接Connection
String name = "root";
String password = "123456";
//协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
String url = "jdbc:mysql://192.168.31.101:50000/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
Connection connection = DriverManager.getConnection(url, name, password);
//创建执行SQL的语句Statement
Statement statement = connection.createStatement();
//处理执行结果ResultSet
ResultSet resultSet = statement.executeQuery("select * from user");
while (resultSet.next()) {
System.out.println("用户名称 name="+ resultSet.getString("username") +
" 联系方式 wechat="+ resultSet.getString("wechat"));
}
//释放连接
resultSet.close();
statement.close();
connection.close();
}
}
SQL注入攻击和Statement预编译语句
-
什么是SQL注入攻击
- 可以执行恶意SQL语句，将任意SQL代码插入数据库查询，使用SQL注入来添加，修改和删除数据库中的记录
-
PrepareStatement
- 字面可译为预声明，内部包含一个预编译的sql语句，参数采用占位符 ? 进行填充
- 为啥可以防注入
- 第一次操作数据库之前，SQL语句已经被数据库分析和编译，对应的执行计划也会缓存下来，之后数据库就会以参数化的形式进行查询
- 传入的值始终都是会作为一个值，而不是sql指令
-
好处：维护性好、提高sql效率、增加安全性
public class JDBCTest {
public static void main(String [] args) throws Exception{
//加载JDBC驱动程序
// Class.forName("com.mysql.jdbc.Driver");
//
// //建立数据库连接Connection
// String username = "root";
// String password = "xdclass.net";
// //协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
// String url = "jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false";
//
// Connection connection = DriverManager.getConnection(url,username,password);
//
// //创建执行SQL的语句Statement
// Statement statement = connection.createStatement();
//
// //处理执行结果ResultSet
// ResultSet resultSet = statement.executeQuery("select * from user");
//
//
//
// while (resultSet.next()){
//
// System.out.println("用户名称 name="+ resultSet.getString("username") + " 联系方式 wechat="+ resultSet.getString("wechat"));
// }
//
// //释放连接资源
// statement.close();
// connection.close();
testInjectSQL();
}
//攻击的例子
// private static void testInjectSQL()throws Exception{
// //加载JDBC驱动程序
// Class.forName("com.mysql.jdbc.Driver");
//
// //建立数据库连接Connection
// String username = "root";
// String password = "xdclass.net";
// //协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
// String url = "jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false";
//
// Connection connection = DriverManager.getConnection(url,username,password);
//
// //创建执行SQL的语句Statement
// Statement statement = connection.createStatement();
//
// String name = "jack";
//
// String pwd = "666' or 1=1 or'";
//
// String sql = "select * from user where username='"+name +"' and pwd='"+pwd +"'";
//
// System.out.println(sql);
//
// //处理执行结果ResultSet
// ResultSet resultSet = statement.executeQuery(sql);
//
//
// while (resultSet.next()){
//
// System.out.println("用户名称 name="+ resultSet.getString("username") + " 联系方式 wechat="+ resultSet.getString("wechat"));
// }
//
// //释放连接资源
// statement.close();
// connection.close();
// }
/**
* 防止攻击的例子
* @throws Exception
*/
private static void testInjectSQL()throws Exception{
//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接Connection
String username = "root";
String password = "xdclass.net";
//协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
String url = "jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false";
Connection connection = DriverManager.getConnection(url,username,password);
String name = "jack";
String pwd = "666 or 1=1 ";
//创建执行SQL的语句Statement
PreparedStatement preparedStatement = connection.prepareStatement("select * from user where username=? and pwd=?");
preparedStatement.setString(1,name);
preparedStatement.setString(2,pwd);
//处理执行结果ResultSet
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println("用户名称 name="+ resultSet.getString("username") + " 联系方式 wechat="+ resultSet.getString("wechat"));
}
//释放连接资源
preparedStatement.close();
connection.close();
}
}
增删功能
private static void testAdd() throws Exception {
//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接Connection
String username = "root";
String password = "xdclass.net";
//协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
String url = "jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false";
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement preparedStatement = connection.prepareStatement("insert into user(username, pwd,sex,role,create_time) values(?,?,?,?,?) ");
preparedStatement.setString(1,"二当家小D");
preparedStatement.setString(2,"123456");
preparedStatement.setInt(3,1);
preparedStatement.setInt(4,2);
preparedStatement.setTimestamp(5,new Timestamp(System.currentTimeMillis()));
//执行
preparedStatement.execute();
preparedStatement.close();
connection.close();
}
private static void testDelete() throws Exception {
//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接Connection
String username = "root";
String password = "xdclass.net";
//协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
String url = "jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false";
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement preparedStatement = connection.prepareStatement("delete from user where id=?");
preparedStatement.setInt(1,2);
//执行
preparedStatement.execute();
preparedStatement.close();
connection.close();
}
JDBC控制Mysql事务
-
事务：
- 一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务
- 例如银行账户转账业务，该业务就是一个最小的工作单元
-
四大特性
- 原子性(A)：事务是最小单位，不可再分
- 一致性(C)：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
- 隔离性(I)：事务A和事务B之间具有隔离性
- 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)
-
事务的一些术语
- 开启事务：Start Transaction
- 事务结束：End Transaction
- 提交事务：Commit Transaction
- 回滚事务：Rollback Transaction
-
JDBC事务控制实操
private static void testTransaction() throws Exception {
//加载JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
//建立数据库连接Connection
String username = "root";
String password = "xdclass.net";
//协议:子协议://ip:端口/数据库名称?参数1=值1&参数2=值2
String url = "jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false";
Connection connection = DriverManager.getConnection(url, username, password);
try (
PreparedStatement ps1 = connection.prepareStatement("insert into user(username, pwd) values(?,?) ");
PreparedStatement ps2 = connection.prepareStatement("insert into user(username, pwd) values(?,?) ")) {
//JDBC中默认事务是自动提交的，false就不会自动提交
connection.setAutoCommit(false);
ps1.setString(1, "1111tranc ps 1二当家小D");
ps1.setString(2, "123456");
ps2.setString(1, "2222tranc ps 2二当家小D");
ps2.setString(2, "123456");
ps1.execute();
//模拟异常
int i = 1/0;
ps2.execute();
} catch (Exception e) {
e.printStackTrace();
//事务回滚
connection.rollback();
} finally {
//事务提交
connection.commit();
connection.close();
}
}
封装DBUtils工具类
- 优化JDBC操作，提高效率
- javaweb项目中，使用jdbc需要添加mysql启动到tomcat里面
db.properties
url=jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false
username=root
password=xdclass.net
driver=com.mysql.jdbc.Driver
CustomDBUtil
public class CustomDBUtil {
private static String url;
private static String username;
private static String password;
private static String driver;
static {
try {
Properties properties = new Properties();
properties.load(CustomDBUtil.class.getClassLoader().getResourceAsStream("db.properties"));
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
driver = properties.getProperty("driver");
//加载JDBC驱动程序
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
/**
* 获取连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception{
Connection connection = DriverManager.getConnection(url,username,password);
return connection;
}
/**
* 关闭数据库资源
* @param resultSet
* @param ps
* @param connection
*/
public static void close(ResultSet resultSet, PreparedStatement ps, Connection connection){
try{
if(resultSet!=null){
resultSet.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
}catch (SQLException e){
throw new RuntimeException();
}
}
}
TestJDBCServlet
@WebServlet({"/jdbc"})
public class TestJDBCServlet extends HttpServlet {
public TestJDBCServlet() {
}
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String idStr = req.getParameter("id");
int id = Integer.parseInt(idStr);
try {
Connection connection = CustomDBUtil.getConnection();
PreparedStatement ps = connection.prepareStatement("select * from user where id=?");
ps.setInt(1, id);
ResultSet resultSet = ps.executeQuery();
while(resultSet.next()) {
System.out.println("用户名称 name=" + resultSet.getString("username") + " 联系方式 wechat=" + resultSet.getString("wechat"));
}
CustomDBUtil.close(resultSet, ps, connection);
} catch (Exception var8) {
var8.printStackTrace();
}
}
}
池化思想
-
为什么要用连接池
- 数据库建立Connection比较耗时，频繁的创建和释放连接引起的大量性能开销
- 如果数据库连接得到重用，避免这些开销，也提高了系统稳定
- 数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于池中备用，对于业务请求处理而言，直接利用现有可用连接，缩减了系统整体响应时间
- 统一的连接管理，避免数据库连接泄漏、超时占用等问题
-
同类对比其他池化思想
- Java线程池
- tomcat连接池
- 对象池（SpringIOC容器）
主流DB工具类、数据库连接池
-
数据库工具类 : Apache commens-dbutils
- Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装,能极大简化jdbc编码的工作量，同时也不会影响程序的性能
- 地址：https://commons.apache.org/proper/commons-dbutils/
- 导入
- 添加到IDEA
- 可以添加到web-inf的lib包(添加到tomcat效果是一样的)
-
数据库连接池：c3p0、druid、dbcp
- dbcp: 全称 DataBase connection pool，数据库连接池是 apache 上的一个Java连接池项目
- 地址：http://commons.apache.org/proper/commons-dbcp/
所有依赖包
commons-pool2-2.8.0 、commons-logging-1.2、commons-dbcp2-2.7.0、commons-dbutils-1.7、mysql-connector-java-5.1.46
https://abc1024.oss-cn-shanghai.aliyuncs.com/Picture/Javaweb/packages.rar
DBCP连接池、dbutils工具类
创建配置文件database.properties
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/xd_web?useUnicode=true&characterEncoding=utf-8&useSSL=false
username = root
password = xdclass.net
initialSize=10 //连接池建立时创建的连接的数量
maxActive=30 //连接池同一时间内最多能够分配的活动连接的数量
数据库连接池工具类（用于返回DataSource，更方便使用dbutils工具类）
/**
* 数据库连接池工具类
*/
public class DataSourceUtil {
//连接池
private static DataSource dataSource;
static {
try{
InputStream in = DataSourceUtil.class.getClassLoader().getResourceAsStream("database.properties");
Properties p = new Properties();
p.load(in);
//BasicDataSourceFactory用于创建连接池
dataSource = BasicDataSourceFactory.createDataSource(p);
}catch (Exception e){
e.printStackTrace();
throw new ExceptionInInitializerError("初始化DBPC失败");
}
}
/**
* 获取连接池
* @return
*/
public static DataSource getDataSource(){
return dataSource;
}
}
dbutils工具介绍
DbUtils 中的核心类/接口
-
QueryRunner
- 查询执行器，提供对sql语句操作的API
- update(String sql,Object...params) 可执行 增-INSERT、删-DELETE、改-UPDATE
- query(String sql,ResultSetHandler rsh,Object...params) 可执行 查询-SELECT
-
ResultSetHandler
- 结果集处理类，执行处理一个结果集对象，将数据转变并处理为任何一种形式
- BeanHandler 结果集中的第一行数据封装到一个对应的JavaBean实例
- BeanListHandler 结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里
- MapHandler 结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值
- MapListHandler 结果集中的每一行数据都封装到一个Map里，然后再存放到List
- ScalarHandler 结果集中第一行数据指定列的值,常用来进行单值查询
- 结果集处理类，执行处理一个结果集对象，将数据转变并处理为任何一种形式
public class UserDao {
private QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
//开启驼峰映射
private BeanProcessor bean = new GenerousBeanProcessor();
private RowProcessor processor = new BasicRowProcessor(bean);
/**
* 新增
* queryRunner.update(sql,params);
* @param user
* @return
* @throws Exception
*/
public int save(User user) throws Exception {
String sql = "insert into user (phone,pwd,sex,img,create_time,role,username,wechat) values(?,?,?,?,?,?,?,?)";
Object [] params = {
user.getPhone(),
user.getPwd(),
user.getSex(),
user.getImg(),
user.getCreateTime(),
user.getRole(),
user.getUsername(),
user.getWechat()
};
// String sql = "delete from user where id= ?";
// Object [] params = { 1};
int i = 0;
try{
i = queryRunner.update(sql,params);
}catch (Exception e){
e.printStackTrace();
throw new Exception();
}
return i;
}
/**
* BeanHandler 结果集中的第一行数据封装到一个对应的JavaBean实例
* @param id
* @return
*/
public User findById(int id){
String sql = "select * from user where id=?";
User user = null;
try {
user = queryRunner.query(sql,new BeanHandler<>(User.class,processor),id);
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
/**
* BeanListHandler 结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里
* @return
*/
public List<User> list(){
String sql = "select * from user";
List<User> list = null;
try {
list = queryRunner.query(sql,new BeanListHandler<>(User.class,processor));
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 根据id找用户
* MapHandler 结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值
* @param id
* @return
*/
public Map<String,Object> findByIdWithMap(int id){
String sql = "select * from user where id=?";
Map<String,Object> map = null;
try {
map = queryRunner.query(sql,new MapHandler(),id);
} catch (SQLException e) {
e.printStackTrace();
}
return map;
}
/**
* 小滴课堂jdbc实战 https://xdclass.net
*
* 查找全部用户
* MapListHandler 果集中的每一行数据都封装到一个Map里，然后再存放到List
* @return
*/
public List<Map<String,Object>> listWithMap(){
String sql = "select * from user";
List<Map<String,Object>> list = null;
try {
list = queryRunner.query(sql,new MapListHandler());
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* ScalarHandler 结果集中第一行数据指定列的值,常用来进行单值查询
* @return
*/
public int countUser(){
String sql = "select count(*) from user";
Long count = null;
try{
count = (Long)queryRunner.query(sql,new ScalarHandler<>());
}catch (Exception e){
e.printStackTrace();
}
return count.intValue();
}
}
Maven依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>net.xdclass</groupId>
<artifactId>xd_forum</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>xd_forum Maven Webapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.41</version>
</dependency>
<!-- JSP -->
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils -->
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.7.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-beanutils/commons-beanutils -->
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/taglibs/standard -->
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
<build>
<!--专门用于打包配置文件到类路径-->
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
<finalName>xd_forum</finalName>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.2.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>