[Java基础]—JDBC

前言

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

第一个JDBC程序

sql文件

INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES (1, 'zhansan', '123456', '[e-mail]', '1980-12-04');
INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES (2, 'lisi', '123456', '[e-mail]', '1981-12-04');
INSERT INTO `users`(`id`, `NAME`, `PASSWORD`, `email`, `birthday`) VALUES (3, 'wangwu', '123456', '[e-mail]', '1979-12-04');

HelloJDBC

import java.sql.*;public class HelloJDBC {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1. 加载驱动Class.forName("com.mysql.jdbc.Driver");//2. 用户信息和urlString url = "jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";String name = "root";String password = "123456";//3. 连接数据库 connection是数据库对象Connection connection = DriverManager.getConnection(url, name, password);//4. 执行sql的对象 statement是执行sql的对象Statement statement = connection.createStatement();//5. 用statement对象执行sql语句String sql = "select * from users";ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){System.out.println("id:"+resultSet.getObject("id")+",name:"+resultSet.getObject("name")+",password:"+resultSet.getObject("password"));System.out.println("=============================");}//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 = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");Properties properties = new Properties();properties.load(in);String driver = properties.getProperty("driver");String url = properties.getProperty("url");String username = properties.getProperty("username");String password = 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!=null){resultSet.close();}if (statement!=null){statement.close();}if (connection!=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 = JdbcUtils.connection();String sql = "select * from users";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while(resultSet.next()){System.out.println("id:"+resultSet.getObject("id"));}JdbcUtils.relese(resultSet,statement,connection);}public static void insert() throws Exception{Connection connection = JdbcUtils.connection();String sql = "insert into users values(4,'Sentiment',123456,'[e-mail]','1980-12-04')";Statement statement = connection.createStatement();int i = statement.executeUpdate(sql);System.out.println(i);JdbcUtils.relese(null,statement,connection);}
}

查询用executeQuery()，增、删、改用executeUpdate()

PreparedStatement对象

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

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

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

操作事务

mybatis中学过，不过连含义都忘了。。。。

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

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

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

Demo

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

数据库连接池

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

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

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

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

DBCP

<dependency><groupId>commons-dbcp</groupId><artifactId>commons-dbcp</artifactId><version>1.4</version>
</dependency><dependency><groupId>commons-pool</groupId><artifactId>commons-pool</artifactId><version>1.5.4</version>
</dependency>

配置文件

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

Demo

使用DBCP后，utils就可以简化为：

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

读取配置文件，交给数据源即可

C3P0

这个更简单

<dependency><groupId>com.mchange</groupId><artifactId>c3p0</artifactId><version>0.9.5.5</version>
</dependency>
<dependency><groupId>com.mchange</groupId><artifactId>mchange-commons-java</artifactId><version>0.2.19</version>
</dependency>

配置文件

这里设置了两个数据源：

<default-config>:默认值创建数据源时不需要形参，ComboPooledDataSource ds=new ComboPooledDataSource();

<named-config name="MySQL">: 非默认要指定数据源，ComboPooledDataSource ds=new ComboPooledDataSource(“MySQL”);

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

Demo

xml文件默认能读取到

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

自带日志