Web笔记-MySQL

MySQL远程登录
1
2
3
4
5
6
7
修改数据表:将Host的localhost值改为%,%代表所有主机均允许
->mysql use mysql;
->mysql update user set Host = '%' where User = 'root';
授权:允许所有主机以root用户密码登录
->mysql grant all privileges on *.* to 'root'@'%' identified by '密码' with grant option;
刷新授权
->mysql flush privileges;
MySQL MAC5.7.17及以上版本中文显示乱码问题
1
2
使用命令查看编码信息
show variables like "char%"

编码信息

若编码信息如图,则无需设置。若database及server等编码不为utf8则按如下方式配置:

  1. 前往/usr/local/mysql/support-files/目录下
  2. 执行copy命令:gaopengfeideMacBook-Pro:~ gaopengfei$ cp my-default.cnf /etc/ 将my-default.cnf拷贝到/etc目录下
  3. 修改etc下my-default.cnf为my.cnf并在my.cnf文件末尾添加如下代码:
    1
    2
    3
    4
    5
    6
    [client]
    default-character-set=utf8
    [mysqld]
    default-storage-engine=INNODB
    character-set-server=utf8
    collation-server=utf8_general_ci

完整my.cnf内容:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
default-character-set=utf8
[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci


数据类型
数据类型
字符串型 VARCHAR CHAR
大数据类型 BLOB TEXT
数值型 TINYINT SMALLINT INT BIGINT FLOAT DOUBLE
逻辑型 BIT
日期型 DATE TIME DATETIME TIMESTAMP
数据库语言
  • DDL语言:数据定义语言
  • DML语言:数据操作语言(插入、更新、删除)
  • DCL语言:数据控制语言
  • DQL语言:数据查询语言
聚集函数
  • COUNT:总数
  • SUM:求和
  • AVG:平均值
  • MAX/MIN:最大/最小值

分组查询:
关键字:group by
添加条件:使用having关键字 后可使用聚集函数,而where条件不能使用聚集函数

排序:
关键字:order by
排序方式:后加关键字ASC(升序) DESC(倒序)

Select语句小结:
S-F-W-G-H-O:SELECT…FROM…WHERE…GROUP BY…HAVING…ORDER BY…
having和where均可实现过滤,但在having后可以使用聚集函数,where后则不能使用。having通常跟在group by后,它作用于分组


多表操作

多表设计:外键约束
作用:保证数据的完整性
语法:alter table 表名 add foreign key 表名(外键) references 关联表名(主键);

表与表之间的关系(数据库设计):

一对多:一个员工只能属于一个部门,一个部门下可以有多个员工
【注】:建表原则,都是看多方表(在多方表添加字段,作为外键指向一方表的主键)

多对多:一门课程可以由多个学生选择,一个学生也可以选择多门课程
【注】:建表原则,建立一张第三方表,该表中至少有俩个字段,这个俩个字段,作为外键,分别指向俩个表的主键

一对一:一个公司只有一个具体地址,而一个地址只能对应一个公司(用的最少)
【注】:建表原则,可以将其写入一张表,但如果因公司业务需要,要拆分成两张表,那么就会有两种建表方式。
a)主键对应:公司信息表的主键和公司地址表主键值必须一致
b)唯一外键对应:给公司表添加一个单独字段作为外键指向公司信息表,但必须给公司表的外键添加唯一约束(unique)保证它的值是唯一的

栗子🌰

多表查询

笛卡尔积(乘积):select * from A,B;

内连接(查询出的一定是最准确的数据):
a)普通内连接:
关键字:inner join…on
select * from A inner join B on A.aname = B.bname;
b)隐式内连接:
关键字:去除inner join…on
select * from A a,B b where a.name = b.name;

1
2
3
4
5
6
7
8
9
+-----+--------+-----+-------+------+---------+------+
| did | dname | eid | ename | esex | esalary | did |
+-----+--------+-----+-------+------+---------+------+
| 1 | 行政部 | 4 | 萌萌 | 1 | 5000 | 1 |
| 2 | 财务部 | 5 | 敏敏 | 1 | 8000 | 2 |
| 3 | 技术部 | 1 | 飞飞 | 0 | 15000 | 3 |
| 3 | 技术部 | 2 | 亮亮 | 0 | 18000 | 3 |
| 3 | 技术部 | 3 | 丁丁 | 0 | 12000 | 3 |
+-----+--------+-----+-------+------+---------+------+

外连接(会将多余无用数据查询出来,若数据不全):
a)左外连接:(看左表,把左表的数据全部查询出来):
关键字:left [outer] join…on 条件
select * from A left outer join B on A.aname = B.bname;

1
2
3
4
5
6
7
8
9
10
11
+-----+--------+------+-------+------+---------+-----+
| did | dname | eid | ename | esex | esalary | did |
+-----+--------+------+-------+------+---------+------+
| 1 | 行政部 | 4 | 萌萌 | 1 | 5000 | 1 |
| 2 | 财务部 | 5 | 敏敏 | 1 | 8000 | 2 |
| 3 | 技术部 | 1 | 飞飞 | 0 | 15000 | 3 |
| 3 | 技术部 | 2 | 亮亮 | 0 | 18000 | 3 |
| 3 | 技术部 | 3 | 丁丁 | 0 | 12000 | 3 |
| 4 | NULL | NULL | NULL | NULL | NULL | NULL|
| 5 | NULL | NULL | NULL | NULL | NULL | NULL|
+-----+--------+------+-------+------+---------+------+

b)右外连接:(看右表,把右表的数据全部查询出来):
关键字:right [outer] join…on 条件
select * from A right outer join B on A.aname = B.bname;

1
2
3
4
5
6
7
8
9
10
11
+------+-------+-------+-------+------+---------+------+
| did | dname | eid | ename | esex | esalary | did |
+------+-------+-------+-------+------+---------+------+
| 3 | 技术部 | 1 | 飞飞 | 0 | 15000 | 3 |
| 3 | 技术部 | 2 | 亮亮 | 0 | 18000 | 3 |
| 3 | 技术部 | 3 | 丁丁 | 0 | 12000 | 3 |
| 1 | 行政部 | 4 | 萌萌 | 1 | 5000 | 1 |
| 2 | 财务部 | 5 | 敏敏 | 1 | 8000 | 2 |
| NULL | NULL | 6 | 妞妞 | 1 | 4000 | NULL |
| NULL | NULL | 7 | 彤彤 | 1 | 4000 | NULL |
+------+--------+-----+-------+------+---------+-------+

子查询

SQL语句中,select…from…语句为一个查询块,将一个查询块儿嵌套在另一个查询块儿中作为条件称为嵌套查询,也称作子查询。
外层的查询块儿称为父查询,内层查询称块称作子查询
语法:select * from table where 条件 > (select 字段 from table where 条件);
子查询运算符:

  • <小于
  • &gt大于
  • <=小于等于
  • &gt=大于等于
  • =等于
  • <>或
  • !=不等于
  • in范围any或all
    >any:大于子查询中的最小值
    >all:大于子查询中的最大值
    语法:select * from table where 条件 >any (select 字段 from table where 条件); 
    

JDBC
  1. JDBC(Java DataBase Connectivity)Java数据库连接,它是由SUN公司为了简化Java对数据库操作而定义的一套规范
  2. 用法

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    //注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取连接对象
    Connection conn = DriverManager.getConnection(url, username, password);
    //执行Statement对象
    Statement stmt = conn.createStatement(sql);
    //执行SQL语句
    ResultSet rs = stmt.executeQuery(sql);
    //解析获取到的数据
    ...
    //关闭资源
    rs.close();
    stmt.close();
    conn.close();
  3. DriverManager详解

  • 注册驱动
    registerDriver(Driver driver)
    而这种方式注册驱动有俩个弊端:不方便切换数据库;这样会注册俩次驱动
    正确写法:Class.forName("com.mysql.jdbc.Driver");通过反射注册驱动
    
  • 获取连接对象
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    getConnection(String url, String user, String password)
    url:数据库连接地址
    jdbc:mysql://localhost:3306/test
    jdbc:数据库连接的协议
    mysql:数据库连接的协议的子协议
    localhost:数据库服务器地址
    3306:MySQL服务器默认端口
    test:数据库名称
    user:数据库用户名
    password:数据库密码
    如果是MySQL数据库,连接的是自己本地的数据库,简写条件:jdbc:mysql:///数据库名
  1. Connection详解:
  • 创建执行SQL语句的对象

    1
    2
    3
    Statement createStatement()//执行SQL语句的对象
    PreparedStatement prepareStatement(String sql)//执行SQL语句对象,支持SQL预编译(防止SQL注入漏洞)
    CallableStatement prepareCall(String slq)//执行数据库存储过程
  • 管理事物

    1
    2
    3
    void setAutoCommit(boolean autoConmmit)//设置是否自动提交事物
    void commit()//提交事物
    void rollback()//回滚

事物:是一组逻辑的操作,要么全都成功,要么全部失败,事物支持回滚

  1. Statement详解:
  • 执行SQL语句

    1
    2
    3
    ResultSet executeQuery(String sql)//执行查询语句的时候使用该方法
    int executeUpdate(String sql)//执行insert update delete语句时调用该方法,返回int为受影响行数
    boolean execute(String sql)//执行select insert update delete语句调用该方法,如果返回Result,则返回true,返回影响行数或者无结果则返回false
  • 执行批处理

    1
    2
    3
    void addBatch(String sql)//添加批处理
    int[] executeBatch()//执行批处理
    void clearBatch()//清除批处理
    1. ResultSet详解:
      表示数据库结果集的数据表,通常通过执行查询数据库的语句生成。它也可以标识数据库中的游标,默认指定到第一行数据的上一行(表头)
      ResultSet它维护了一个指向表格行的游标cursor,可以使用它进行数据的获取
  • API

    1
    2
    3
    4
    5
    6
    boolean next()//将游标向下移动一行,判断是否有下一条数据
    boolean absolute(int row)//将光标移动到此 ResultSet 对象的给定行编号
    void afterLast()//将光标移动到此 ResultSet 对象的末尾,正好位于最后一行之后
    boolean previous()//将光标移动到此 ResultSet 对象的上一行。
    void beforeFirst()//将光标移动到此 ResultSet 对象的开头,正好位于第一行之前。
    void updateRow()//用此 ResultSet 对象的当前行的新内容更新底层数据库。
  • 设置滚动结果集:

    1
    2
    3
    Statement stmt = con.createStatement(int resultSetType, int resultSetConcurrency);
    resultSetType:结果集的类型
    resultSetConcurrency:结果集并发策略
  • 结果集类型:

    1
    2
    3
    ResultSet.TYPE_FORWARD_ONLY:只能向下
    ResultSet.TYPE_SCROLL_INSENSITIVE:可以滚动,但不能修改数据
    ResultSet.TYPE_SCROLL_SENSITIVE:可以滚动,也可以修改数据
  • 结果集并发策略取值

    1
    2
    ResultSet.CONCUR_READ_ONLY:只读(不能修改)
    ResultSet.CONCUR_UPDATABLE:可读可写
  • 组合到一起,不能滚动策略:

    1
    2
    3
    ResultSet.TYPE_FORWARD_ONLY ResultSet.CONCUR_READ_ONLY:只能向下,不能修改(默认结果集)
    ResultSet.TYPE_SCROLL_INSENSITIVE ResultSet.CONCUR_READ_ONLY:可以滚动,数据不能修改
    ResultSet.TYPE_SCROLL_SENSITIVE ResultSet.CONCUR_UPDATABLE:可以滚动,也可以修改数据
  • 示例代码

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    //加载驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取连接
    Connection conn = DriverManager.getConnection("jdbc:mysql:///test", "root", "root");
    //获取可以执行SQL语句的对象
    Statement stmt = conn.createStatement(Result.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    //编写SQL语句
    String sql = "SELECT * FROM user";
    //设置可以滚动的结果集,修改数据
    ResultSet rs = stmt.executeQuery(sql);
    //定位到第3行
    rs.absolute(3);
    //修改数据
    rs.updateString("username", "小飞");
    //执行
    rs.updateRow();
    //释放资源
    rs.close();
    stmt.close();
    conn.close();
    1. 释放资源
      使用JDBC时,执行完毕后,一定要释放资源(ResultSet、Statement、Connection对象)。
      特别是Connection对象,它是非常稀有的资源,用完必须释放,如果不及时正确关闭,极容易导致系统宕机。
      Connection对象的使用原则:尽量晚创建,尽量早释放
      为确保资源释放代码可以运行,资源释放代码一定写在finally语句中
  1. JavaEE-DAO(DataAccessObject)模式
    DAO模式:处理持久层,封装数据源和单个操作。对外提供一组接口,供业务层访问。业务层调用持久层传递一个对象参数。
  • 数据源可能是文件、数据库等任意存储方式
  • 负责管理与数据源的连接
  • 负责数据的存取(CRUD)

DAO模式创建:提供接口–>提供实现类

JavaEE体系:

  • 客户端:HTML、CSS、JS
  • WEB层:Servlet JSP
  • 业务层:EJB
  • 持久层: JDBC

JavaEE经典三层结构:

  • WEB层:Servlet-接收请求 JSP-显示结果 Struts SpringMVC
  • 业务层:JavaBean-封装和处理数据 Spring
  • 持久层:JDBC-操作数据库 Hibernate MyBatis
    早期采用:Servlet + JSP + JavaBean + JDBC框架,偏于底层
    现在采用:SSH(Struts + Spring + Hibernate)框架
    1. 数据库事务

      事务 指逻辑上的一组操作,组成这组操作的各个单元,要不全部成功,要不全部失败

数据库开启事务命令:

  • start transaction 开启事务
  • rollback 回滚事务
  • commit 提交事务

关闭MySQL事务自动提交(MySQL数据库事物是默认提交的):

  1. 查询是否默认提交:show variables like ‘%commit%’;

查询数据库是否默认提交事物

  1. 设置不默认提交:set autocommit=off或者0;如果设置autocommit为off,意味着以后每条SQL 都会处于一个事务中,相当于每条SQL执行前 都执行 start transaction

关闭mysql默认事物提交

注:Oracle中 autocommit 默认就是 off

代码执行事务:
当JDBC程序向数据库获得一个Connection对象时,默认情况下这个Connection对象会自动向数据库提交SQL语句
若想关闭这种默认提交方式,让多条SQL在一个事务中执行,可使用下列语句:

1
2
3
void setAutoCommit(boolean autoCommit) //将此连接的自动提交模式设置为给定状态。
void commit() //使所有上一次提交/回滚后进行的更改成为持久更改,并释放此 Connection 对象当前持有的所有数据库锁
void rollback() //取消在当前事务中进行的所有更改,并释放此 Connection 对象当前持有的所有数据库锁

设置事务回滚点:

1
2
3
Savepoint setSavepoint() //在当前事务中创建一个未命名的保存点 (savepoint),并返回表示它的新 Savepoint 对象。
void rollback(Savepoint savepoint) //取消所有设置给定 Savepoint 对象之后进行的更改。
void commit() //使所有上一次提交/回滚后进行的更改成为持久更改,并释放此 Connection 对象当前持有的所有数据库锁。

事务的丢失更新问题(lost update)
俩个或多个事务同时更新同一行,但这些事务彼此之间都不知道其它事务进行修改,因此第二个更改会覆盖第一个修改

  • 第一类丢失更新:A事务回滚时,把已提交的B事务数据覆盖
  • 第二类丢失更新:A事务提交时,把已提交的B事务
    1. 解决方案一:悲观锁-假如丢失更新的问题一直存在,使用数据库锁机制(排他锁)
    2. 解决方案二:乐观锁-假如丢失更新的问题不一定存在,提供在表中添加一个字段
    

四大特性(ACID):

  1. 原子性(Atomicity):原子性是指事物是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
  2. 一致性(Consistency):事务前后数据的完整性必须保持一致
  3. 隔离性(Isolation):事物的隔离性是指多个用户并发访问数据库时,一个用户的事务不能被其他用户的事务所干扰,多个并发事务之间数据要相互隔离
  4. 持久性(Durability):持久性是指一个事务一旦被提交,它对数据库中的数据改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

隔离性的重要性:

忽略隔离性可能会导致如下问题:

  • 数据脏读:在一个事务中读取到了另一个事务未提交的数据
  • 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同
  • 虚读(幻读):在一个事务中读取到了别的事务插入的数据,导致前后读取不一致(insert)

隔离性解决方案:
隔离级别:

  • read uncommitted(未提交读):级别最低。脏读、不可重复读、虚读都有可能发生
  • read committed(提交读):可避免脏读。不可重复读、虚读都有可能发生
  • repeatable read(可重复读):可避免脏读、不可重复读。虚度可能发生
  • serializable(串行化):可避免脏读、不可重复读、虚读的情况

设置事务隔离级别:
set session transaction isolation level XXX (XXX代表隔离级别)

查询事务隔离级别:
select @@tx_isolatioin

安全性:read uncommitted < read committed < repeatable read < serializable
效率: read uncommited > read committed > repeatable read > serializable

数据库设置这些隔离级别:考虑安全性和效率,一般不使用read uncommitted和serializable

MySQL的默认值:repeatable read Oracle的默认值:read committed

JDBC中设置隔离级别:

在Connection对象中有 void setTransactionIsolation(int level)设置隔离级别;level提供四种隔离级别的常量

模拟脏读:
1、同时开启俩个CMD窗口,第一个窗口设置事务隔离级别为最低。
2、俩个窗口各自开启一个事务
3、窗口1先查询一次数据
4、窗口2update数据
5、窗口1再次执行数据查询
6、窗口2执行事务回滚
7、窗口1再次执行数据查询

模拟脏读

解决脏读:设置窗口1的隔离级别为read committed;但是在窗口1再次执行查询会出现不可重复读的情况
解决不可重复读:设置窗口1的隔离级别为repeatable read;如果在窗口2中插入一条新的数据,则在窗口1中会出现虚读情况
解决虚读:设置窗口1的隔离级别为serializable;


数据库连接池

连接池:为了解决高并发导致频繁创建和销毁线程,而提供的一种解决方案。原理:默认创建N个连接,存入内存,当用户使用时则从池中取,用完之后将连接归还池中

DBCP连接池:
DBCP是Apache的开源连接池实现,使用DBCP数据源,应用程序在系统中需要导入如下4个jar文件:
commons-dbcp2-2.1.1和commons-pool2-2.4.2(依赖包下载)以及commons-collections4-4.1jar(依赖包下载)和commons-logging-1.2.jar(依赖包下载

参数 缺省值 描述
username(用户名) 用户名
password(密码) 密码
url(JDBC连接串) 数据库地址
driverClassName(JDBC驱动程序) 数据库驱动
connectionProperties(连接属性 )
defaultAutoCommit(自动提交) driver default 默认自动提交事务
defaultReadOnly(只读设置) driver default 此池创建的连接的默认只读状态。如果没有设置则setReadOnly方法不会被调用。
defaultTransactionIsolation(事务隔离级别) driver default NONE READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ SERIALIZABLE
defaultCatalog DATE 由该池创建的连接的默认目录。
cacheState(缓存状态) true
defaultQueryTimeout(查询超时时间) null 查询超时时间,值为int类型,“空”意味着将使用驱动程序默认值
enableAutocommitOnReturn(连接归还到池时,设置为自动提交) true 如果设置为true,则连接被归还到连接池时,会指定设置为autoCommit = true
rollbackOnReturn(连接归还到池时,回滚所有操作) true 如果设置为true,则连接被归还到连接池时,会自动执行一次rollback();前提是自动提交 = true and 非只读。
initialSize 0 这个池被启动时初始化的创建的连接个数,起始生效版本:1.2
maxTotal(最大活动连接数) 8 可以在这个池中同一时刻被分配的有效连接数的最大值,如设置为负数,则不限制
maxIdle(最大空闲连接数) 8 如设置为负数,则不限制将被释放,在归还到连接池时在池中,可以保持空闲状态的最大连接数,超出设置值之外的空闲连接
minIdle(最小空闲连接数) 0 如设置为负数,则不限制将被释放,在归还到连接池时在池中,可以保持空闲状态的最大连接数,超出设置值之外的空闲连接
maxWaitMillis(从连接池获取连接时,最大等待时间) indefinitely 可以在池中保持空闲的最小连接数,低于设置值时,空闲连接将被创建,以努力保持最小空闲连接数>=minIdle,如设置为0,则不创建 这里设置的数值生效的前提是:timeBetweenEvictionRunsMillis(空闲对象驱逐线程运行时的休眠毫秒数)被设置为正数。

使用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
//加载配置文件
Properties properties = new Properties();
InputStream in = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
properties.load(in);
//设置连接参数
BasicDataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
//获取连接
conn = dataSource.getConnection();
//执行SQL语句
String sql = "SELECT * FROM user";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString("username") + "\t" + rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.close(rs, stmt, conn);
}

C3P0连接池:
C3P0是一个开源的JDBC连接池,它实现了数据源和JNDI绑定,支持JDBC3规范和JDBC2的标准扩展,目前使用它的开源项目有Hibernate,Spring等
C3P0使用:
使用过程如果出现异常,则还需依赖该库mchange-commons-java-0.2.12.jar
首先在src目录下创建名称为:c3p0-config.xml的配置文件,具体配置如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement">3</property>
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts">30</property>
<!--两次连接中间隔时间,单位毫秒。Default: 1000 -->
<property name="acquireRetryDelay">1000</property>
<!--连接关闭时默认将所有未提交的操作回滚。Default: false -->
<property name="autoCommitOnClose">false</property>
<!--c3p0将建一张名为Test的空表,并使用其自带的查询语句进行测试。如果定义了这个参数那么
属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作,它将只供c3p0测试
使用。Default: null-->
<property name="automaticTestTable">Test</property>
<!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效
保留,并在下次调用getConnection()的时候继续尝试获取连接。如果设为true,那么在尝试
获取连接失败后该数据源将申明已断开并永久关闭。Default: false-->
<property name="breakAfterAcquireFailure">false</property>
<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间,超时后将抛出
SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->
<property name="checkoutTimeout">100</property>
<!--通过实现ConnectionTester或QueryConnectionTester的类来测试连接。类名需制定全路径。
Default: com.mchange.v2.c3p0.impl.DefaultConnectionTester-->
<property name="connectionTesterClassName"></property>
<!--指定c3p0 libraries的路径,如果(通常都是这样)在本地即可获得那么无需设置,默认null即可
Default: null-->
<property name="factoryClassLocation">null</property>
<!--Strongly disrecommended. Setting this to true may lead to subtle and bizarre bugs.
(文档原文)作者强烈建议不使用的一个属性-->
<property name="forceIgnoreUnresolvedTransactions">false</property>
<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
<property name="idleConnectionTestPeriod">60</property>
<!--初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="initialPoolSize">3</property>
<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime">60</property>
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize">15</property>
<!--JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements
属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
如果maxStatements与maxStatementsPerConnection均为0,则缓存被关闭。Default: 0-->
<property name="maxStatements">100</property>
<!--maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
<property name="maxStatementsPerConnection"></property>
<!--c3p0是异步操作的,缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能
通过多线程实现多个操作同时被执行。Default: 3-->
<property name="numHelperThreads">3</property>
<!--当用户调用getConnection()时使root用户成为去获取连接的用户。主要用于连接池连接非c3p0
的数据源时。Default: null-->
<property name="overrideDefaultUser">root</property>
<!--与overrideDefaultUser参数对应使用的一个参数。Default: null-->
<property name="overrideDefaultPassword">password</property>
<!--密码。Default: null-->
<property name="password"></property>
<!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度。注意:
测试的表必须在初始数据源的时候就存在。Default: null-->
<property name="preferredTestQuery">select id from test where id=1</property>
<!--用户修改系统配置参数执行前最多等待300秒。Default: 300 -->
<property name="propertyCycle">300</property>
<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的
时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
等方法来提升连接测试的性能。Default: false -->
<property name="testConnectionOnCheckout">false</property>
<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->
<property name="testConnectionOnCheckin">true</property>
<!--用户名。Default: null-->
<property name="user">root</property>
</c3p0-config>
<!-- 常用配置-->
<c3p0-config>
<!-- 默认连接数据库的配置 -->
<default-config>
<!-- jdbcUrl:表示连接数据库的URL -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<!-- driverClass:表示连接数据库的驱动类 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- user:表示连接数据库使用的用户名 -->
<property name="user">root</property>
<!-- password:表示连接数据库使用的密码 -->
<property name="password">1314</property>
<!-- acquireIncrement:表示连接池中的连接数达到上限,每次增加几个连接. -->
<property name="acquireIncrement">3</property>
<!-- initialPoolSize:表示初始化连接池中的连接数 -->
<property name="initialPoolSize">10</property>
<!-- minPoolSize:表示设置连接池中最小连接数 -->
<property name="minPoolSize">4</property>
<!-- maxPoolSize:表示设置连接池中最大连接数 -->
<property name="maxPoolSize">10</property>
</default-config>
</c3p0-config>

  • 使用

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
    //获取连接池对象
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    /获取连接
    conn = cpds.getConnection();
    String sql = "SELECT * FROM user";
    //执行预编译
    stmt = conn.prepareStatement(sql);
    //执行SQL
    rs = stmt.executeQuery();
    while (rs.next()) {
    System.out.println(rs.getInt("id") + "\t" + rs.getString("username") + "\t" + rs.getString("password"));
    }
    } catch (Exception e) {
    e.printStackTrace();
    }finally {
    JDBCUtils.close(rs, stmt, conn);
    }
  • C3P0优势:

  1. dbcp没有自动回收空闲连接的功能。
  2. c3p0有自动回收空闲连接功能。
  3. dbcp只能在配置文件中配置一个数据库
  4. c3p0可以在配置文件中配置多个数据库

JNDI(Java Naming and Directory Interface)连接池:

JNDI连接池是SUN公司提供的一种标准的Java命名系统接口,JNDI提供统一的客户端API。它目前只能用于Web应用中,而不能应用在Java应用

JNDI连接池使用步骤:
配置使用Tomcat服务器内置的连接池JNDI,配置文件名为context的xml文件,该文件有三种常见配置位置:

  • Tomcat服务器安装目录的conf目录中:表示Tomcat服务器下的所有Web工程都可以访问该连接池
  • Tomcat服务器安装目录的conf目录/Catalina目录/loacalost目录中:表示当前虚拟主机(localhost)下的所有Web工程都可以访问该连接池
  • 当前Web工程根目录/META-INF目录中:表示只有当前Web工程可以访问该连接池

1、context.xml文件具体配置如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<!--
Resouce标签:表示配置连接池相关信息
name属性:为当前连接池绑定一个名称
type属性:指定当前连接池使用的类型
driverClassName属性:指定数据库驱动类
url属性:指定数据库连接
username属性:指定数据库登录用户名
password属性:指定数据库登录密码
maxActive属性:设置连接池最大活动连接数量
maxIdle属性:设置连接池最大空闲数量
-->
<Resource name=""
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/jdbc"
username="root"
password="1314"
maxActive="8"
maxIdle="4" />
</Context>

2、将MySQL数据库驱动jar包拷贝到Tomcat服务器的安装目录的lib目录中,是由Tomcat服务器内置连接池JNDI连接数据库
3、通过运行在JNDI容器内部的程序(Servlet/JSP)访问Tomcat服务器的内置连接池JNDI
ComboPooledDataSource dataSource = new ComboPolledDataSource("mysql");
4、代码中使用JNDI数据库连接池

1
2
3
4
5
6
7
8
9
//1、通过Context接口,读取META-INF目录下的contxt.xml配置文件
Context context = new InitialContext();
//2、java:comp/env路径固定
Context envCtx = (Context) context.lookup("java:comp/env");
//3、通过绑定的连接池名称查找对应的连接池对象
DataSource dataSource = (DataSource) envCtx.lookup("jdbc/jdbc");
//4、通过连接池对象获取连接对象
Connection conn = dataSource.getConnection();
InitialContext被配置为一个Web应用程序的最初部署,并提供给Web应用组件(只读访问)。所有配置项和资源放在java的JNDI命名空间:java:comp/env部分

DBUtils工具类

DBUtils是操作数据库的组件,对传统操作数据库的类进行二次封装,可以把结果转换成List。是Java编程中数据库操作的实用工具,小巧实用

DBUtils的特点:

  • 对于数据表的读操作,它可以把结果转换为List,Array,Set等Java集合,便于程序员操作
  • 对于数据表写的操作,也变的简单(只需写SQL语句)
  • 可以使用数据源,使用JNDI,数据库连接池等技术来优化性能,重用已经构建好的数据库连接对象,而不像PHP,ASP哎呀,费时费力的不断重复构建和析构这样的对象
  1. DBUtils类:主要提供一系列close()方法,commit()方法及rollback()方法
  2. QueryRunner类:主要提供了两组方法,一组是需要手动管理事务,一种是工具管理事务

需要手动设置事务:

  • QueryRunner()
  • public int update(Connection conn, String sql, Object… params)
  • query(Connection conn, String sql, ResutlSetHandler rsh, Object… params)

DBUtils工具管理事务:

  • QueryRunner(DataSource ds)
  • public int update(String sql, Object… params)
  • public Object query(Strin sql, ResultSetHandler rsh, Object… params)

ResultSetHandler接口:
作用:将查询数据库的结果集封装成集合
DBUtils提供众多的实现类
实际开发中,不需要实现该接口,只需要继承实现类即可
实现类:

  • AbstractKeyedHandler:
  • AbstractListHandler
  • BaseResultSetHandler
  • ArrayHandler:将结果集中的第一行数据转成对象数组
  • ArrayListHandler:将结果集中的每一行数据都转成一个对象数组,再存放到集合中
  • BeanHandler:将结果集单条记录存放到JavaBean中
  • BeanListHandler:将结果集中的每一条记录都转换成JavaBean后再存放到集合中
  • BeanMapHandler:将结果集中的每一条记录转成JavaBeam,再将JavaBean封装到Map集合中
  • ColumnListHandler:将结果集的某一列数据存放到List集合中
  • KeyedHandler:将结果集中的每一行数据都封装到一个Map里(List),再把这些Map存放到一个Map里,外层Map的key为指定的列
  • MapHandler:将结果集中的第一行数据封装到一个Map集合中,key为列名,value即为值
  • MapListHandler:将结果集中的每一行数据封装到一个Map集合中,再将每个Map添加到List集合中
  • ScalarHandler:进行单行查询

使用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
public static void main(String[] args) {
QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
String sql = "SELECT * FROM user";
try {
System.out.println("------------------------ArrayHandler------------------------");
//ArrayHandler:将结果集中的第一行数据封装到数组中
Object[] arr = queryRunner.query(sql, new ArrayHandler());
for (Object object : arr) {
System.out.println(object);
}
System.out.println("------------------------ArrayListHandler------------------------");
//ArrayListHandler:将结果集的每一行数据封装到Array中,再将Array封装到List中
List<Object[]> arrList = queryRunner.query(sql, new ArrayListHandler());
for (Object[] objects : arrList) {
for (Object object : objects) {
System.out.println(object);
}
}
System.out.println("------------------------BeanHandler------------------------");
//BeanHandler:将结果集的第一行数据封装到JavaBean中
User user = queryRunner.query(sql, new BeanHandler<>(User.class));
System.out.println(user.toString());
System.out.println("------------------------BeanListHandler------------------------");
//BeanListHandler:将结果集的每一行数据封装到JavaBean中,再将每个JavaBean封装到List中
List<User> userList = queryRunner.query(sql, new BeanListHandler<>(User.class));
for (User user2 : userList) {
System.out.println(user2.toString());
}
System.out.println("------------------------MapHandler------------------------");
Map<String, Object> map = queryRunner.query(sql, new MapHandler());
Set<String> keySet = map.keySet();
Iterator<String> iterator = keySet.iterator();
while (iterator.hasNext()) {
String key = (String) iterator.next();
System.out.println(key + "," + map.get(key));
}
System.out.println("------------------------MapListHandler------------------------");
List<Map<String, Object>> mapList = queryRunner.query(sql, new MapListHandler());
for (Map<String, Object> map2 : mapList) {
Set<String> keySet2 = map2.keySet();
Iterator<String> iterator2 = keySet2.iterator();
while (iterator2.hasNext()) {
String key = (String) iterator2.next();
System.out.println(key + "," + map2.get(key));
}
}
System.out.println("------------------------ColumnListHandler------------------------");
//ColumnListHandler:将查询的列名封装到List集合中
List<String> columnList = queryRunner.query(sql, new ColumnListHandler<String>("username"));
for (String columnName : columnList) {
System.out.println(columnName);
}
System.out.println("------------------------KeyedHandler------------------------");
//KeyedHandler:将结果集的每一行数据封装到一个Map集合中,再将这些Map集合统一封装到另一个外层Map中,而外层的Map的键则是我们创建KeyedHandler时构造传入的column的值
Map<Object, Map<String, Object>> keyedMap = queryRunner.query(sql, new KeyedHandler<>("username"));
System.out.println(keyedMap);
System.out.println("------------------------ScalarHandler------------------------");
String sql2 = "SELECT count(*) FROM user";
long value = queryRunner.query(sql2, new ScalarHandler<Long>());
System.out.println(value);
} catch (Exception e) {
e.printStackTrace();
}

如果帮到了你,想打赏支持,喏~