[Java基础]—JDBC

其实学Mybatis前就该学了&＃xff0c;但是寻思目前主流框架都是用mybatis和mybatis-plus就没再去看&＃xff0c;结果在代码审计中遇到了很多cms是使用jdbc的因此还是再学一下吧。

sql文件

INSERT INTO &＃96;users&＃96;(&＃96;id&＃96;, &＃96;NAME&＃96;, &＃96;PASSWORD&＃96;, &＃96;email&＃96;, &＃96;birthday&＃96;) VALUES (1, &＃39;zhansan&＃39;, &＃39;123456&＃39;, &＃39;zs&＃64;sina.com&＃39;, &＃39;1980-12-04&＃39;);
INSERT INTO &＃96;users&＃96;(&＃96;id&＃96;, &＃96;NAME&＃96;, &＃96;PASSWORD&＃96;, &＃96;email&＃96;, &＃96;birthday&＃96;) VALUES (2, &＃39;lisi&＃39;, &＃39;123456&＃39;, &＃39;lisi&＃64;sina.com&＃39;, &＃39;1981-12-04&＃39;);
INSERT INTO &＃96;users&＃96;(&＃96;id&＃96;, &＃96;NAME&＃96;, &＃96;PASSWORD&＃96;, &＃96;email&＃96;, &＃96;birthday&＃96;) VALUES (3, &＃39;wangwu&＃39;, &＃39;123456&＃39;, &＃39;wangwu&＃64;sina.com&＃39;, &＃39;1979-12-04&＃39;);


HelloJDBC

import java.sql.*;
public class HelloJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2. 用户信息和url
String url &＃61; "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode&＃61;true&characterEncoding&＃61;utf8&useSSL&＃61;true";
String name &＃61; "root";
String password &＃61; "123456";
//3. 连接数据库 connection是数据库对象
Connection connection &＃61; DriverManager.getConnection(url, name, password);
//4. 执行sql的对象 statement是执行sql的对象
Statement statement &＃61; connection.createStatement();
//5. 用statement对象执行sql语句
String sql &＃61; "select * from users";
ResultSet resultSet &＃61; statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id:"&＃43;resultSet.getObject("id")&＃43;",name:"&＃43;resultSet.getObject("name")&＃43;",password:"&＃43;resultSet.getObject("password"));
System.out.println("&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;");
}
//6.释放资源
resultSet.close();
statement.close();
connection.close();
}
}

Statement对象

工具类

package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
public static Connection connection() throws ClassNotFoundException, SQLException, IOException {
InputStream in &＃61; JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties &＃61; new Properties();
properties.load(in);
String driver &＃61; properties.getProperty("driver");
String url &＃61; properties.getProperty("url");
String username &＃61; properties.getProperty("username");
String password &＃61; properties.getProperty("password");
Class.forName(driver);
return DriverManager.getConnection(url,username,password);
}
public static void relese(ResultSet resultSet, Statement statement, Connection connection) throws SQLException {
if (resultSet!&＃61;null){
resultSet.close();
}
if (statement!&＃61;null){
statement.close();
}
if (connection!&＃61;null){
connection.close();
}
}
}


Demo

import utils.JdbcUtils;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcStatement {
public static void main(String[] args) throws Exception {
// query();
insert();
}
public static void query() throws SQLException, ClassNotFoundException, IOException{
Connection connection &＃61; JdbcUtils.connection();
String sql &＃61; "select * from users";
Statement statement &＃61; connection.createStatement();
ResultSet resultSet &＃61; statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id:"&＃43;resultSet.getObject("id"));
}
JdbcUtils.relese(resultSet,statement,connection);
}
public static void insert() throws Exception{
Connection connection &＃61; JdbcUtils.connection();
String sql &＃61; "insert into users values(4,&＃39;Sentiment&＃39;,123456,&＃39;Sentiment&＃64;qq.com&＃39;,&＃39;1980-12-04&＃39;)";
Statement statement &＃61; connection.createStatement();
int i &＃61; statement.executeUpdate(sql);
System.out.println(i);
JdbcUtils.relese(null,statement,connection);
}
}


查询用executeQuery()&＃xff0c;增、删、改用executeUpdate()

用statement的话会有sql注入问题&＃xff0c;因此可以用preparedstatement进行预处理来进行防御

主要是通过占位符来执行查询语句

public class JdbcPreparedStatement {
public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
Connection connection &＃61; JdbcUtils.connection();
String sql &＃61; "insert into users values(?,?,?,null,null)";
PreparedStatement ps &＃61; connection.prepareStatement(sql);
ps.setInt(1,5);
ps.setString(2,"Sentiment");
ps.setInt(3,123456);
ps.execute();
}
}

操作事务

mybatis中学过&＃xff0c;不过连含义都忘了。。。。

其实就是执行语句时要不都成功执行&＃xff0c;一个不能执行则全部都不执行

主要就是关闭自动提交事务

connection.setAutoCommit(false); //开启事务


Demo

PreparedStatement ps &＃61; null;
Connection connection &＃61; null;
try {
connection &＃61; JdbcUtils.connection();
//关闭数烟库的自动提交&＃xff0c;自动会开启事务connection
connection.setAutoCommit(false); //开启事务
String sql1 &＃61; "update users set name &＃61; &＃39;Sentiment&＃39; where id&＃61;3";
ps &＃61; connection.prepareStatement(sql1);
ps.executeUpdate();
int x &＃61; 1 / 0;
String sql2 &＃61; "update users set name &＃61; &＃39;Sentiment&＃39; where id&＃61;1";
ps &＃61; connection.prepareStatement(sql2);
ps.executeUpdate();
connection.commit();
System.out.println("Success!");
}catch (SQLException e){
connection.rollback(); //执行失败后&＃xff0c;事务回滚
}finally {
JdbcUtils.relese(null,ps,connection);
}
}

数据库连接池

在上述工具类中&＃xff0c;是通过以下方法来获取配置文件参数&＃xff0c;并连接连接池

String driver &＃61; properties.getProperty("driver");
String url &＃61; properties.getProperty("url");
String username &＃61; properties.getProperty("username");
String password &＃61; properties.getProperty("password");
Class.forName(driver);
return DriverManager.getConnection(url,username,password);


而我们可以用开源的数据库连接池如DBCP、C3P0、Druid等

使用了这些数据库连接池之后&＃xff0c;我们在项目开发中就不需要编写连接数据库的代码了!

DBCP


commons-dbcp
commons-dbcp
1.4


commons-pool
commons-pool
1.5.4



配置文件

driverClassName&＃61;com.mysql.jdbc.Driver
url&＃61;jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode&＃61;true&characterEncoding&＃61;utf8&useSSL&＃61;true
username&＃61;root
password&＃61;123456
#<!-- 初始化连接 -->
initialSize&＃61;10
#最大连接数量
maxActive&＃61;50
#<!-- 最大空闲连接 -->
maxIdle&＃61;20
#<!-- 最小空闲连接 -->
minIdle&＃61;5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait&＃61;60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样&＃xff1a;【属性名&＃61;property;】
#注意&＃xff1a;"user" 与 "password" 两个属性会被明确地传递&＃xff0c;因此这里不需要包含他们。
connectionProperties&＃61;useUnicode&＃61;true;characterEncoding&＃61;utf8
#指定由连接池所创建的连接的自动提交&＃xff08;auto-commit&＃xff09;状态。
defaultAutoCommit&＃61;true
#driver default 指定由连接池所创建的连接的只读&＃xff08;read-only&＃xff09;状态。
#如果没有设置该值&＃xff0c;则“setReadOnly”方法将不被调用。&＃xff08;某些驱动并不支持只读模式&＃xff0c;如&＃xff1a;Informix&＃xff09;
defaultReadOnly&＃61;true
#driver default 指定由连接池所创建的连接的事务级别&＃xff08;TransactionIsolation&＃xff09;。
#可用值为下列之一&＃xff1a;&＃xff08;详情可见javadoc。&＃xff09;NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation&＃61;READ_COMMITTED


Demo

使用DBCP后&＃xff0c;utils就可以简化为&＃xff1a;

public static Connection connection() throws Exception {
InputStream in &＃61; DBCP_Utils.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties &＃61; new Properties();
properties.load(in);
//创建数据源
DataSource dataSource &＃61; BasicDataSourceFactory.createDataSource(properties);
return dataSource.getConnection();
}


读取配置文件&＃xff0c;交给数据源即可

C3P0

这个更简单

<dependency>
<groupId>com.mchangegroupId>
<artifactId>c3p0artifactId>
<version>0.9.5.5version>
dependency>
<dependency>
<groupId>com.mchangegroupId>
<artifactId>mchange-commons-javaartifactId>
<version>0.2.19version>
dependency>


配置文件

这里设置了两个数据源&＃xff1a;

:默认值创建数据源时不需要形参&＃xff0c;ComboPooledDataSource ds&＃61;new ComboPooledDataSource();

: 非默认要指定数据源&＃xff0c;ComboPooledDataSource ds&＃61;new ComboPooledDataSource(“MySQL”);

<?xml version&＃61;"1.0" encoding&＃61;"UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省&＃xff08;默认&＃xff09;配置
如果在代码中"ComboPooledDataSource ds&＃61;new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省&＃xff08;默认&＃xff09;
-->
<default-config>
<property name&＃61;"driverClass">com.mysql.jdbc.Driver</property>
<property name&＃61;"jdbcUrl">jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode&＃61;true&amp;characterEncoding&＃61;utf8&amp;useSSL&＃61;true</property>
<property name&＃61;"user">root</property>
<property name&＃61;"password">123456</property>
<property name&＃61;"acquiredIncrement">5</property>
<property name&＃61;"initialPoolSize">10</property>
<property name&＃61;"minPoolSize">5</property>
<property name&＃61;"maxPoolSize">20</property>
</default-config>
<!--
c3p0的命名配置
如果在代码中"ComboPooledDataSource ds&＃61;new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省&＃xff08;默认&＃xff09;
-->
<named-config name&＃61;"MySQL">
<property name&＃61;"driverClass">com.mysql.jdbc.Driver</property>
<property name&＃61;"jdbcUrl">jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode&＃61;true&amp;characterEncoding&＃61;utf8&amp;useSSL&＃61;true</property>
<property name&＃61;"user">root</property>
<property name&＃61;"password">123456</property>
<property name&＃61;"acquiredIncrement">5</property>
<property name&＃61;"initialPoolSize">10</property>
<property name&＃61;"minPoolSize">5</property>
<property name&＃61;"maxPoolSize">20</property>
</named-config>
</c3p0-config>


Demo

xml文件默认能读取到

public static Connection connection() throws Exception {
ComboPooledDataSource dataSource&＃61;new ComboPooledDataSource();
return dataSource.getConnection();
}


自带日志