文章流程图:主要介绍三部分内容:
再介绍数据库连接池
1、事务管理
事物:作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。
人话就是:事物就是必须串在一起执行的东西。
流程就是:
- 关闭自动提交(我猜是为了可以控制回滚)
- 回滚事物
- 提交事务
- 设置保存点等
java"> conn = DriverManager.getConnection(url, "root", "huang@hui1huang2");
conn.setAutoCommit(false);
insertBook(conn, "insert into t_book values(101, 'aaaa', 10)");
insertBook(conn, "insert into t_book values(102, 'bbbb', 10)");
insertBook(conn, "insert into t_book values(103, 'cccc', 10)");
Savepoint phase1 = conn.setSavepoint(); //设置一个保存点
insertBook(conn, "insert into t_book values(104, 'cccc', 10)");
insertBook(conn, "insert into t_book values(105, 'cccc', 10)");
conn.rollback(phase1); //回滚到phase1保存点,即上面2行无效
conn.commit();
2、PreparedStatement安全便捷执行SQL
为啥使用这个库:
1、防止恶意SQL语句破坏数据库
2、方便写SQL语句
3、预编译速度快
怎么用?
java"> public static void batchInsertBook()
{
//构建Java和数据库之间的桥梁介质
try{
Class.forName("com.sql>mysql.jdbc.Driver");
System.out.println("注册驱动成功!");
}catch(ClassNotFoundException e1){
System.out.println("注册驱动失败!");
e1.printStackTrace();
}
String url="jdbc:sql>mysql://localhost:3306/test?allowMultiQueries=true";
Connection conn = null;
try {
//构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";
//构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行SQL语句
String bookName = "aaaaaaaaaaaaaaaa";
int price = 50;
//values(1, 'Effective Java', 50)
for(int i=200;i<210;i++)
{
pstmt.setInt(1, i);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();
System.out.println("操作成功");
} catch (SQLException e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
}
catch (SQLException e){
e.printStackTrace();
}
}
}
3、获取数据库元数据
ResultSet可以用来承载所有的select语句返回的结果集.
ResultSetMetaData来获取ResultSet返回的属性(如,每一行的名字类型等)
java"> try {
//构建Java和数据库之间的桥梁:URL,用户名,密码
conn = DriverManager.getConnection(url, "root", "123456");
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功!");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//获取结果集的元数据
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
for(int i=1;i<=cols;i++)
{
System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));
}
rs.close();
stmt.close();
} catch (SQLException e){
e.printStackTrace();
}
4、数据库连接池
为啥要用数据库连接池呢?因为每次查询都需要构建连接、执行SQL、取消连接,其中构建连接和取消连接这部分太耗时了,执行SQL部分可能又没有太多任务量,所以构建连接池之后,已经构建的连接就可以重用了。
常见的数据连接池就三种,性能较好的就两种。
先从性能较好的来介绍吧。
4.1 Druid
阿里系,性能优秀。怎么用呢?
首先查找maven,这只是一个数据库连接管理工具。
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
java">import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
public class DruidFactory2 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception{
Properties properties = new Properties();
//使用类加载器来加载配置文件:.class返回当前类的Class对象,getClassLoader获取该Class对象的类装载器
//类装载器负责从java字符文件将字符流读入内存,并构造Class对象,得到文件输入流
//java的类装载器查找类的位置,并将java类的字节码装入内存,生成Class对象。java类装载器也是一个雷,由Bootstrap来装载
InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(in);
dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
in.close();
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
这里加载了一个卸载properties里的配置文件。
配置文件参数介绍。
可能会出现MySql Data source rejected establishment of connection, message from server: "Too many connections"错误,就是说你构建的连接太多了。解决办法。还有办法是,减少配置文件中连接池参数。
driverClassName=com.sql>mysql.jdbc.Driver
url=jdbc:sql>mysql://127.0.0.1:3306/xscj
username=root
password=xxx
filters=stat
initialSize=10
maxActive=150
maxWait=150
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=20
那具体怎么执行SQL呢?
java"> public static void main(String[] args)
{
Connection conn = null;
try {
int count = 0;
long startMillis = System.currentTimeMillis();
while(count<10000)
{
count++;
//获取连接
conn = DruidFactory2.getConnection();
//构建数据库执行者
Statement stmt = conn.createStatement();
//执行SQL语句
ResultSet rs = stmt.executeQuery("select kcm, kkxq,xf from kc order by kkxq");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getString("kcm")+","+rs.getInt("kkxq")+","+rs.getInt(3));
}
System.out.println(count);
rs.close();
stmt.close();
conn.close();
}
System.out.println(System.currentTimeMillis()-startMillis);
}
catch (Exception e)
{
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
} catch (SQLException e){
e.printStackTrace();
}
System.out.println("搞完了");
}
}
经过测试,设置连接池为150的话,执行10000次查询操作。普通连接花费105658ms而Druid花费3727ms,高出几个数量级。
Druid非加载文件用法,就是用语句写进去而已,没啥的。
java">import java.sql.Connection;
import com.alibaba.druid.pool.DruidDataSource;
public class DruidFactory1 {
private static DruidDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.sql>mysql.jdbc.Driver");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setUrl("jdbc:sql>mysql://127.0.0.1:3306/test");
dataSource.setInitialSize(5);
dataSource.setMinIdle(1);
dataSource.setMaxActive(10);
// 启用监控统计功能 dataSource.setFilters("stat");//
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
4.2、C3p0
直接贴代码吧,大差不差的
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
配置文件,文件名:c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config> <!-- 默认配置 -->
<property name="driverClass">com.sql>mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:sql>mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
基于文件加载参数的方法:
java">import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory2 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
//dataSource 自动加载c3p0-config.xml文件
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
非文件加载方法:
java">import java.sql.Connection;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Factory1 {
private static ComboPooledDataSource dataSource = null;
public static void init() throws Exception {
dataSource = new ComboPooledDataSource();
dataSource.setDriverClass( "com.sql>mysql.jdbc.Driver" );
dataSource.setJdbcUrl( "jdbc:sql>mysql://localhost:3306/test" );
dataSource.setUser("root");
dataSource.setPassword("123456");
// the settings below are optional -- c3p0 can work with defaults
dataSource.setMinPoolSize(5);
dataSource.setAcquireIncrement(5);
dataSource.setMaxPoolSize(20);
// The DataSource dataSource is now a fully configured and usable pooled DataSource
}
public static Connection getConnection() throws Exception {
if(null == dataSource)
{
init();
}
return dataSource.getConnection();
}
}
调用函数:
java">import java.sql.*;
public class SelectTest {
public static void main(String[] args){
Connection conn = null;
try {
//从c3p0获取
//conn = C3p0Factory1.getConnection();
//conn = C3p0Factory2.getConnection();
//从Druid获取
//conn = DruidFactory1.getConnection();
conn = DruidFactory2.getConnection();
//构建数据库执行者
Statement stmt = conn.createStatement();
System.out.println("创建Statement成功!");
//执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");
//开始遍历ResultSet数据
while(rs.next())
{
System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
}
rs.close();
stmt.close();
} catch (Exception e){
e.printStackTrace();
} finally {
try {
if(null != conn) {
conn.close();
}
} catch (SQLException e){
e.printStackTrace();
}
}
}
}
总结
诶,学习部分主要是创建思维导图,构建学习框架,方便自己复习用,可能对于读者来说没啥用,再想想要不要写这类blog。