SQL基础

MYSQL基础

 数据库·····语言
 DDL定义    DML操作（增删改）    DQL查询    DCL控制

库与表的sql语句

  CREATE创建
  DROP删除
  ALTER修改    
  对象：database数据库 table表

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) NOT NULL DEFAULT '学生姓名' COMMENT '姓名',
	`password` VARBINARY(30) NOT NULL DEFAULT'123456789' COMMENT '密码',
	`sex` VARCHAR(5) NOT NULL DEFAULT '男' COMMENT '性别',
	`age` INT(5) NOT NULL DEFAULT '18' COMMENT '年龄',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

ENGINE 引擎
 -INNODB	现在默认使用，安全性高。
 -MYISAM	早期使用，节约空间。
CHARSET 字符集 一般选择utf8。

SHOW CREATE TABLE XXX;-- 查看表的sql创建语句 database对应查看数据库
DESC XXX;--查看表的结构

ALTER TABLE XXX RENAME AS ···	-- 修改表名
ALTER TABLE XXX ADD ···		--增加表的字段
ALTER TABLE XXX DROP ···	--删除字段
ALTER TABLE XXX MODIFY ···	--修改表的字段属性、约束
ALTER TABLE XXX CHANGE ···	-- 表的字段重命名

表数据管理

 insert插入    update修改    delete删除
 对象一般是`表名` 
 注意字段用``，写常量赋值时用' '

外键

ALTER TABLE XXX
ADD CONSTRAINT `FK_xxxid` FOREIGN KEY(`xxxid`) REFERENCES `xxx`(`xxxid`);
-- 不常用，因为外键删除时，父表和子表均要处理，联动关系很麻烦

插入

insert into `表名`(`字段名`) values ('值');
-- 将值赋予相应字段，位置一一对应

修改

update `表名` set `字段`= '值' where 条件
-- 条件即指定一些判定语句，没有where来判断，则表内数据全更改

删除

delete from `表名` where 条件
-- 条件是指定删除对象，无where判断则表数据全删，自增量不变
-- 使用INNODB，在delete后 重启数据库，自增量从1开始，而使用MYISAM重启后自增量不变
truncate table `表名`
-- 清表，自增量归0

DQL查询

select ····· from ·····、
	where ··· -- 条件
	group by ··· -- 按字段分组
	having ··· -- 过滤分组的二级条件
	order by ··· -- 排序
	limit ··· -- 分页
	union ··· -- 结合多个查询语句结果成一个结果集
-- 查询标准格式，功能关键词要按序使用，不可乱序使用。

DQL

select * from `表名` -- *代表查询全部内容
select `字段1` as 别名,`字段2` as 别名 from `表名` as 别名
-- 别名``，''带不带都行，不要别名，查询出来即设置的字段名

-- concat(a,b) 连接a，b两个字符串
select concat('我的名字是 ',`名字字段`) as 别名 from `表名`

-- distinct 去重
select distinct `字段` from `表名`

SELECT VERSION(); -- 查询版本
SELECT 455*8484 AS 结果; -- 基本计算
SELECT `studentresult`+1 AS 成绩加1分 FROM `result`; -- 改变字段数据

where及条件语句

简单查询

-- 简单逻辑运算符
and &&  or||  not！

-- where 返回布尔值
SELECT `studentresult` AS 成绩 FROM `result` WHERE `studentresult`>=80 AND `studentresult`<=90;
SELECT `studentresult` AS 成绩 FROM `result` WHERE `studentresult` BETWEEN 80 AND 100;

模糊查询

语句	描述
a not null	若为空则真
a is not null	不为空则真
a between b and c	a在b与c之间为真
a like b	a匹配b成功则为真
a in (a1,a2,a3···)	括号内值有a则为真

select `字段` from `表名` where `字段` like '常量'
-- 其中%代表0或多个任意字符，一个_为单个字符
select `字段` from `表名` where `字段` in ('范围',' ',' ')
-- 找出范围内的数据

连接查询

参考网页：https://www.cnblogs.com/mafeng/p/10150013.html

/*
连接不同表来查询数据;
连接查询 INNER/LEFT/RIGHT JOIN(连接表名) ON (判断条件);
这里是连接查询使用join on配套语句，条件语句不用where;
必须要有on;
多个表内有相同字段，需要标明对应的表名;
一般on条件为相同量。
*/  
-- 1、内连接 inner join(和join相同)
SELECT s.studentno,studentname FROM student AS s 
INNER JOIN result AS r
ON s.studentno = r.studentno
-- 返回符合条件语句的查询字段

-- 2、外连接 left join/right join/full join
select 字段1,字段2,字段3 from 表名1
left join 表名2
on 条件
-- 返回左表的全部需要字段，且返回右表符合条件语句的字段，没有的字段为null(right join:左->右),full join相当于前两个的并集。

-- 3、多表连接
SELECT s.studentno,s.studentname,subjectname 
FROM student AS s 
INNER JOIN result AS r
ON s.studentno = r.studentno

RIGHT JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
-- 用join语句连接多表，每次连接均与join语句左半部分连接。

自连接

/*
一种逻辑连接，可对一个表查询进行自身连接。
例子：以下语句给每一个种类给了两个相关字段，一些字段pid为1，说明其为父类，而另一些字段pid=父类字段的  子类字段，这便有了父子关系，于是同一张表的信息也有了联系。
*/
CREATE TABLE `school`.`category`(
 `categoryid` INT(3) NOT NULL COMMENT 'id',
 `pid` INT(3) NOT NULL COMMENT '父id 没有父则为1',
 `categoryname` VARCHAR(10) NOT NULL COMMENT '种类名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB COLLATE=utf8_general_ci CHARSET=utf8; 

INSERT INTO `category` (`categoryid`, `pid`, `categoryname`)
VALUES ('2', '1', '信息技术'),
('3', '1', '软件开发'),
('5', '1', '美术设计'),
('4', '3', '数据库'),
('8', '2', '办公信息'),
('6', '3', 'web开发'),
('7', '5', 'ps技术');

/*
以下便是自连接，where判断条件即该表中子字段=父字段，查询出父子关系
自连接并没有新的语句，算是一种逻辑连接。
*/
SELECT a.`categoryname` AS father, b.`categoryname` AS son
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

排序和分页

order by 字段 asc -- asc升序 desc降序
limit a,b -- a即第n-1个数据，从0开始；b即每页限制内容大小；0,b即第一页，b,b第二页，2b,b第三页···

子查询

SELECT studentno,studentname,gradeid
FROM student
WHERE gradeid = (
	SELECT gradeid FROM `subject` 
	WHERE `subjectname`='高等数学-3'
)
/*
inner join是直接连接的，而自连接类似嵌套写法，比方这里，想查询高等数学-3课程的gradeid，但student里没有gradename，所以嵌套一个查询在subject中找到高等数学-3的gradeid
*/

函数

select 函数名(参数)
建议直接查函数库

数学相关

abs() -- 绝对值
ceiling() -- 向上取整
floor() -- 向下取整
round() -- 四舍五入
rand() -- 随机数
sign() -- 取符号，0对应0，负数对应-1，正数对应1
sqrt() -- 取平方根
·····

字符串相关

char_length() -- 无论是英文、汉字还是数字，均算1字符
length() -- 汉字算3字符，英文、数字算1字符
concat() -- 连接字符串
lower() -- 转小写
upper() -- 转大写
instr(a,b) -- b字符串第一次在a字符串中出现的位置 
reverse() -- 反转
·····

时间日期相关

curdate() -- 年月日
now() -- 年月日时分秒
year() month() day() hour() minute() second() -- 年月日时分秒

聚合函数

count() -- 返回满足条件的记录数
	count(*) -- 含所有行列查询，但结果计算时不忽略NULL
	count(1) -- 统计表中所有记录数，不忽略NULL，会包含NULL的记录
	count(列名) -- 统计表中字段次数，忽略NULL，不统计NULL字段
sum() avg() min() max() -- 求和、平均值、最小值、最大值

分组+过滤

SELECT subjectno,AVG(`studentresult`) 
FROM `result` AS r
GROUP BY `subjectno`
HAVING AVG(`studentresult`)>=70
-- group by 字段分组
-- having 分组条件

事务

事务即多方同步操作
eg:转账，a给b转账，a-5000同时b+5000
遵守ACID原则：
Atomicity 原子性
	事务要么都成功，要么都失败，即操作同步。
	当执行事务时有失败操作，可进行回滚，回到未改变的时候。这个还得靠我们主观判断。
Consistency 一致性
	事务完成前后，数据库总量不变，执行前与执行后一致。
Isolation 隔离性
	一个事务执行时不受其他事务影响。事务间隔离。
Durability 持久性
	事务提交后不可逆，数据持久（永久）保存于数据库。
	
-- mysql默认开启事务
-- 手动处理事务，要先关闭自闭提交事务。 不然没法回滚。
SET autocommit = 0 -- 关闭自动提交
SET autocommit = 1 -- 开启自动提交(默认，即随时保存)

START TRANSACTION; -- 开启事务
UPDATE information SET money=money-5 WHERE `name`='tang';
UPDATE information SET money=money+5 WHERE `name`='xxx';

COMMIT -- 提交
ROLLBACK -- 回滚
SAVEPOINT -- 设置保存点
ROLLBACK TO SAVEPOINT -- 回退到保存点
RELEASE SAVEPOINT -- --撤销、删除保存点

在并发下事务会产生的问题：
1、脏读
2、不可重复读
3、幻读

事务的隔离级别：
1、序列化
2、可重复读
3、提交读
4、未提交读

索引（INDEX）

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引是为了提高数据检索能力而产生的。提高性能。

6.1、索引分类

主键索引（PRIMARY KEY）
	唯一标识，不可重复。主键索引只能有一个。
唯一索引（UNIQUE）
	索引的字段不重复，唯一索引可有多个。
普通索引（INDEX）
	索引的字段可重复。
全文索引（FULLTEXT）
	提高查询速度。
	char、varchar、text类型 + MyISAM独有  #可能现在新版本没限制了

100万数据测试

-- 大数据可明显看出索引提高了搜索性能
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0'  COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT = 'app用户表'

-- 插入100万数据.
DELIMITER $$ -- 写函数之前必须要写，标志

CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
WHILE i<num DO
	INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
	VALUES(CONCAT('用户',i),'1262306856@qq.com'
	,CONCAT('13',FLOOR(RAND() * ((999999999-100000000)+100000000)))
	,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100) );
	SET i=i+1;
	END WHILE;
	RETURN i;
END;
-- 先写函数，再执行。
SELECT mock_data() -- 执行此函数 生成一百万条数据

-- 大数据对比
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 未加索引前查询1s
CREATE INDEX id_app_user_name ON app_user(`name`); -- 加索引，然后再查询，发现查询约为0s

数据库安全

用户管理

create user `用户名` identified by `密码` -- 创建用户
set password [for `用户`] = password(`密码`) -- 修改[当前]用户密码
rename user `旧名` to `新名` -- 修改用户名
drop user `用户` -- 删除用户

用户权限

-- 一般用可视化界面完成
grant all privileges on `数据库名.表名` to `用户` -- 授予所on有权限，除了给他人授权
show grants for `用户` -- 查询权限
revoke all privileges on `数据库名.表名` from `用户` -- 收回所有权限

数据库备份

1.物理备份
2.命令行指令：
mysqldump -u `用户` -h `主机` -p `数据库` `表名` [表1 表2 表3] > `文件路径`
3.导入：source `文件路径`

三大范式

参考：https://www.cnblogs.com/wsg25/p/9615100.html

第一范式（1NF）：
	原子性：每一个字段不可再拆分。
第二范式（2NF）：
	前提：1NF
	数据库表中每一个字段均和主键相关。
第三范式（3NF）：
	前提：1NF、2NF
	数据表中每一个字段都和主键有直接相关，而不是间接相关。

三大范式用来规范数据库的设计

关联查询的表不超过3张：
	考虑商业化的需求，数据库性能考虑更重要。
	适当考虑规范性。
	故意给某些表添加冗余字段。

JDBC

Java database connect

相关jar包下载：

https://downloads.mysql.com/archives/c-j/

1、创建新工程，新建lib文件夹（library）
2、copy粘贴jar包到lib
3、右键lib，add as Library
导入成功后便可使用

模板

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
class.forName("com.mysql.jdbc.Driver");//加载驱动的固定写法，死记

Connection connection = DriverManager.getConnection(url,username,password);
/*
 *connection 即数据库
 *connection.commit(); 事务提交
 *connection.rollback(); 事务回滚
 *connection.setAutoCommit(); 数据库自动提交
 */

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

//协议://主机地址:端口号/数据库?参数1&参数2&参数3;
//mysql:jdbc:mysql localhost:3306

Statement（重要！！！）

//使用数据库对象，增删改查 
//···(sql),String sql对象，然后传对象即可。
statement.executeQuery();//查询返回ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//增、删、改，返回会影响的函数

ResultSet

//查询结果集，封装所有查询结果
//获得指定数据类型
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
·····
    
//遍历
resultSet.beforeFirst();//光标移至最前
resultSet.afterLast();//光标移至最后
resultSet.next();//移到下一数据
resultSet.previous();//移至第一行
resultSet.absolute(row);//移至指定行

释放资源

//对象.close() 及时释放，connwction很占空间
resultSet.close();
statement.close();
connection.close();

Java代码

public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2、用户信息 url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

        String username = "root";

        String password = "111111";
        //3、连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //4、执行SQL对象
        Statement statement = connection.createStatement();
        //5、执行SQL对象的结果
        String sql =  "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);

        while(resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("NAME=" + resultSet.getObject("NAME"));
            System.out.println("PASSWORD=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birthday=" + resultSet.getObject("birthday"));
        }
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

工具类实现及其他应用

工具类

###db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=111111

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {

        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //只用加载一次驱动
            Class.forName(driver);

        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        if(statement!=null){
            try{
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try{
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}

增删改：executeUpdate

模板几乎一样，编写不同的sql语句然后调用即可。

//增
public class TEST {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //数据库连接
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)"+
                "VALUES(7,'s','sss','8888888','2555-08-24')";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("成功插入");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

//删
public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //数据库连接
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "delete from users where id=7";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("删除了");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

//改
public class TestUpdata {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //数据库连接
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "update users set `NAME`='ttttt' where id=1";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

查：executeQuery

//查
public class TestSelect {
    public static void main(String[] args){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try{
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            String sql = "select * from users where id=3";
            resultSet = statement.executeQuery(sql);//查询完返回ResultSet

            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME") +"\n"+ resultSet.getString("PASSWORD"));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

SQL注入

拼接sql语句，非法攻击，极其不安全。

解释：https://baike.baidu.com/item/sql%E6%B3%A8%E5%85%A5/150289?fr=aladdin

public static void main(String[] args){
        //login("wangwu","123456");
        login("' or '1=1","' or '1=1");//没有输入正确用户密码，就能访问全部数据。
    }

    public static void login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try{
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            //select * from users where `NAME`='wangwu' and `PASSWORD`='123456';
            //select * from users where `NAME`= '' or '1=1' and `PASSWORD`='' or '1=1';
            String sql = "select * from users where `NAME`='" +username+ "'and `PASSWORD` ='" +password+ "'";
            resultSet = statement.executeQuery(sql);//查询完返回ResultSet

            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }

PreparedStatement（更推荐使用）

PreparedStatement可防止SQL注入，且执行效率更高。

//修改的模板，增删改就参数变动即可，和Statement类似.
public static void main(String[] args) {
        Connection c = null;
        PreparedStatement p = null;
        ResultSet r = null;
        try {
            c = JdbcUtils.getConnection();
            //PreparedStatement和Statement区别
            //这里是预编译，使用?占位符代替参数
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            p = c.prepareStatement(sql);//预编译sql，先写不执行
            
            //手动赋值,参数1和sql中x位置对应
            p.setInt(1,8);
            p.setString(2,"xxxx");
            p.setString(3,"sdsdsdsd");
            p.setString(4,"88888@qq");
            //sql.Date     数据库  java.sql.Date
            //util.Date    java   new Date().getTime()
            p.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = p.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(c,p,r);
        }
    }

//查询的模板，大致一样，使用的是executeQuery
public static void main(String[] args) {
        Connection c = null;
        PreparedStatement p = null;
        ResultSet r = null;
        try {
            c = JdbcUtils.getConnection();
            //PreparedStatement和Statement区别
            //这里是预编译，使用?占位符代替参数
            String sql = "select * from users where id =?";
            p = c.prepareStatement(sql);//预编译sql，先写不执行

            //赋值
            p.setInt(1,1);

            //执行
            r = p.executeQuery();
            if(r.next()){
                System.out.println(r.getString("NAME")+"\n"+r.getString("email"));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(c,p,r);
        }
    }

防止SQL注入

/*
 *PreparedStatement防止SQL注入本质是：将传入的参数当作字符，语句中使用了占位符，规定了sql语句的结
 *构。用户可以设置"?"的值，但是不能改变sql语句的结构。参数中的引号会被转义。
 */
public static void main(String[] args){
        //login("wangwu","123456");
        login("'' or '1=1'","'' or '1=1'");
    }

    public static void login(String username,String password){
        Connection c = null;
        PreparedStatement p = null;
        ResultSet r = null;

        try{
            c = JdbcUtils.getConnection();
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
            p=c.prepareStatement(sql);

            p.setString(1,username);
            p.setString(2,password);

            r = p.executeQuery();//查询完返回ResultSet
            while(r.next()){
                System.out.println(r.getString("NAME"));
                System.out.println(r.getString("email"));
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(c,p,r);
        }
    }

JDBC操作事务

public static void main(String[] args) {
        Connection c = null;
        PreparedStatement p = null;
        ResultSet r = null;

        try {
            c = JdbcUtils.getConnection();
            //关闭数据库自动提交，java会自动开启事务，所以不用再写开启事务语句
            c.setAutoCommit(false);//开启事务

            String sql1 = "update account set money=money+100 where `name`='A'";
            p = c.prepareStatement(sql1);
            p.executeUpdate();

            String sql2 = "update account set money=money-100 where `name`='B'";
            p = c.prepareStatement(sql2);
            p.executeUpdate();

            c.commit();
            System.out.println("修改好了");
        }catch (SQLException e){
            //失败默认回滚，不用自己写
//            try {
//                c.rollback();
//            }catch (SQLException e1){
//                e1.printStackTrace();
//            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(c,p,r);
        }
    }

数据库连接池

使用连接池，不用自己编写连接数据库相关代码。

DBCP

commons-dbcp-1.4.jar

commons-pool-1.6.jar

###dbcpconfig.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=111111

#初始化连接数
initialSize=10

#最大连接数
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#最长等待超时时间 以毫秒为单位
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=UTF8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

工具类

使用连接池，不用自己去连接数据库

private static DataSource dataSource = null;

    static {

        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //获得连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();//数据源获得连接
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        if(statement!=null){
            try{
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try{
                connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }

dbcp数据库功能使用

public static void main(String[] args) {
        Connection c = null;
        PreparedStatement p = null;
        try {
            c = DbcpUtils.getConnection();
            //PreparedStatement和Statement区别
            //使用?占位符代替参数
            String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            p = c.prepareStatement(sql);//预编译sql，先写不执行
            //手动赋值
            p.setInt(1,10);
            p.setString(2,"x");
            p.setString(3,"sd");
            p.setString(4,"888@qq");
            //sql.Date     数据库  java.sql.Date
            //util.Date    java   new Date().getTime()
            p.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = p.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DbcpUtils.release(c,p,null);
        }
    }

C3P0

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

###c3p0-config.xml 注意c3p0配置文件是xml


<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
 
  <default-config>
    <property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf-8</property>
	<property name="user">root</property>
	<property name="password">111111</property>
	
	<property name="acquireIncrement">5</property>
	<property name="initialPoolSize">10</property>
	<property name="minPoolSize">5</property>
	<property name="maxPoolSize">20</property>
  </default-config>
  
  <named-config name="MYSQL"> 
   	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf-8</property>
	<property name="user">root</property>
	<property name="password">111111</property>
	
	<property name="acquireIncrement">5</property>
	<property name="initialPoolSize">10</property>
	<property name="minPoolSize">5</property>
	<property name="maxPoolSize">20</property>
  </named-config>
  
</c3p0-config>

小结

搞了半个月？总算是弄完数据库了，增删改查还要在今后的实践中熟练，下一站前端套餐走起。