java编程思想读书笔记3——jdbc

JDBC

---------------

java database connection,java数据库连接。

 

MySQL

-------------------

1.卸载mysql

...

2.安装MySQL

...

custom安装->自己安装目录(不要中文和空格).

配置向导.

->detail configuration(默认)

->Developer machine(默认)

->Innodb Tablespace setting(默认)

->decision support(默认)

->enable tcp/ip networking(默认)

port number:3306(默认)

勾选"add firewall exception ..."(不是默认)

->选择"manual Select default character set / ..."(非默认)

下拉选择"utf8"

->勾选"install as windows server"(默认)

默认...

->修改安全设置密码

new Root password : root

confirm : root

勾选"enable root access from remote machine"(非默认)

->next

->execute.

 

卸载mysql

-----------------------

1.控制面板卸载mysql

2.删除mysql安装目录

3.删除C:\ProgramData\MySQL数据文件夹

4.删除注册表HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL

5.删除注册表HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL

 

 

RDBMS

------------------

relation database management system,关系型数据库管理系统。

二维表格。

结构严谨。

 

MySQL //sun -> oracle

Oracle //

SQLServer //microsoft

DB2 //IBM

TD //

Sysbase //

PostGre //java

 

去IOE

------------------

IBM //

Oracle //

EMC //共享存储

 

MySQL

------------------

database //数据库

table //表

column //列,field

record //记录

 

 

unique //唯一性约束

not null //非空约束

primary key //主键, 不重复 + not null.

 

MySQL命令

-------------------

[DDL:Data define language,数据定义语言。create drop alter]

mysql>show databases ; //查询哪些数据库,库是表的集合。

mysql>exit ; //退出client

mysql>use mhive ; //使用指定的数据库

mysql>show tables ; //查看库中含有哪些表

mysql>create database big3 ; //创建数据库

mysql>drop database big3 ; //创建数据库

 

[table]

 

mysql>create table mytable(id int,name varchar(20)); //创建表

mysql>show tables; //显式表

mysql>drop table mytable; //删除表

 

[SQL:structure query language,结构化查询语言。]

mysql>select * from test ; //查询表中的所有列,所有行.全表扫描

//* 通配所有列.

mysql>select * from test where id = 1; //where子句查询

 

mysql>insert into test(id,name) values(1,'tom'); //插入数据

mysql>update test set name='jerry'; //更新数据

mysql>update test set id = 3,name='jerry' where id = 1; //使用where子句更新数据

mysql>delete * from test ; //删除所有记录

mysql>delete * from test where id = 1; //

mysql>delete * from test where id < 1; //

mysql>delete * from test where id <= 1; //

mysql>delete * from test where id != 1; //不等于

mysql>delete * from test where id <> 1; // OK,不等于

mysql>delete * from test where id >< 1; // not OK

mysql>delete * from test where id >< 1; // not OK

 

mysql>select * from test where name = 'tom'; // MySQL不区分大小写

mysql>select id,name from test where name = 'tom'; // 投影查询 projection

mysql>select id,name from test where name like 't%'; // 模糊查询 以t开头

mysql>select id,name from test where name like '%t'; // 模糊查询 以t结尾

mysql>select id,name from test where name like '%t%'; // 模糊查询 含有t %通配符多个字符.

mysql>select id,name from test where name like '%t__'; // 模糊查询 含有t,_匹配一个字符.

 

mysql>desc test ; //describe test ,查看表结构

mysql>describe test ; //describe test ,查看表结构

 

mysql>select distinct * from test ; //去重查询

mysql>select * from test where name is null; //null值查询 is not null.

 

mysql>create table users(id int primary key auto_increment , //创建主键(primary key)，字段自增(auto_increment)

name varchar(20) ,

age int);

[复制表]

mysql>create table t2 like t1; //类似创建表结构,不含数据

mysql>create table t2 as select * from users; //类似创建表结构,含数据.

mysql>create table test.t2 as select * from big3.users; //复制不同库中的表.

 

mysql>

CRUD

----------

create //insert

retrieve //select

update //update

delete //delete

 

taskkill /? //杀死进程

 

启动MySQL服务

-------------------

cmd>services.msc //打开服务界面

找到mysql

启动

 

cmd>netstat -ano //查看端口

 

JDBC

---------------

java database connection,java数据库连接。

0.Driver

驱动程序.

com.mysql.jdbc.Driver //mysql驱动类

 

1.引入mysql驱动.

复制mysql-connector-java-5.1.17.jar到项目的lib下.

2.添加jar文件到build path(class path)中。

3.

4.

 

C/S

-------------

client / server

Socket / ServerSocket

用户体验.

 

B/S

----------------

browser / server.

ie|firefox / web server.(tomcat)

 

SQL

-----------------

structure query language.

insert into t(f1,f2,...) values(v1,v2,...) ;

delete from t where ...

update t set f1=v1,f2=v2 ,... where ...

select * from t ;

select id,name,... from t ; //projection,投影查询

 

 

 

JDBC

---------------

java database connection.

java数据库连接

规范(接口)。

Driver //驱动程序

driverclass //com.mysql.jdbc.Driver

url //jdbc:mysql://localhost:3306/big3

username //root

password //root

 

//

Class.forName("com.mysql.jdbc.Driver"); //

conn = DriverManager.getConnection(...); //

Statement st = conn.createStatement(); //语句

st.execute("delete from users where id < 4");//

st.close();

 

 

 

 

sql

----------------

select * from users order by id desc ; //默认asc

select * from users order by id asc,name desc,age asc ; //

select * from users order by 1 asc,2 desc,3 asc ; //

 

事务性

----------------

Transaction:事务

commit //提交

rollback //回滚.

和db之间一组操作。

四个特性acid:

 

atomic //原子性,不可分割.

consistent //一致性,不能破坏掉.

isolate //隔离型,事务独立的。

durable //永久性,永久有效.

 

jdbc的事务处理

-----------------

conn.setAutocommit(false); //关闭自动提交

 

conn.commit(); //提交事务

 

conn.rollback(SavePoint sp); //回滚事务

 

 

聚集函数查询

---------------

select count(1) from users;

查询mysql的系统表

---------------------

//查询指定的库中是否含有指定的表,通过查询系统库实现的。

select table_name from information_schema.tables where table_name = 'users' and table_schema = 'big3';

 

use

[DDL]

create

drop

alter

 

分页查询

---------------------

mysql: //limit

// 3:offset, 10:length

//select ... limit 3,10;

oracle: //嵌套子查询rownum

sqlserver //top

 

修改表

--------------------

alter table users add column password varchar(20);

desc users;

update users set password = '123456' ;

 

sql注入:

-------------

select * from users where name = 'tom0' and password='123456'

 

 

Statement

-----------------

执行静态语句.

导致sql注入问题。

PreparedStatement

-----------------

 

 

截断表,数据无法回滚。

-------------------------

truncate table users;

 

Statement < PreparedStatement < CallableStatement

 

 

LOB:large Object.

 

MySQL

-----------------

LongBlob //long binary large object.

Text //

 

 

 

操作大对象

-------------------

1.增加两个字段

alter table users add column pic longblob ;

alter table users add column info text ;

//

conn = getConn(); //连接

conn.setAutoCommit(false); //自动提交

String sql = "select pic from users where id = ?" ;

ppst = conn.prepareStatement(sql);

ppst.setInt(1,1);

ResultSet rs = ppst.executeQuery();

if(rs.next()){

byte[] bytes = rs.getBytes(1);

FileOutputStream fos = new FileOutputStream("d:/KKK.jpg");

fos.write(bytes);

fos.close();

}

conn.commit();

ppst.close();

conn.close();

2.

 

mysql赋值语法

---------------

1.set x := a + b ;

 

2.select count(*) into x from ... ;

 

 

 

存储过程

-------------------------

1.创建add存储过程

mysql>delimiter //

mysql>create procedure up_add(in a int,in b int ,out c int)

mysql>begin

mysql>set c := a + b ;

mysql>end

mysql>//

 

mysql>call up_add(1,2,@x) //

mysql>select @x //

 

2.java调用存储过程

Connection conn = null ;

CallableStatement cst = null ;

try {

conn = getConn(); //连接

cst = conn.prepareCall("{call up_add(?,?,?)}");

cst.setInt(1, 1);

cst.setInt(2, 3);

cst.registerOutParameter(3, Types.INTEGER);

//执行存储过程

cst.execute();

int r = cst.getInt(3);

System.out.println(r);

cst.close();

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

 

3.处理游标

CREATE PROCEDURE curdemo()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE a CHAR(16);

DECLARE b,c INT;

DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;

DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;

OPEN cur2;

REPEAT

FETCH cur1 INTO a, b;

FETCH cur2 INTO c;

IF NOT done THEN

IF b < c THEN

INSERT INTO test.t3 VALUES (a,b);

ELSE

INSERT INTO test.t3 VALUES (a,c);

END IF;

END IF;

UNTIL done END REPEAT;

CLOSE cur1;

CLOSE cur2;

END

 

 

通过存储过程实现百万记录插入

-----------------------------

1.创建存储过程

create procedure up_biginsert(in num int)

begin

declare i int default 0;

start transaction ;

while i < num do

insert into users(name,password,age) values(concat('tom',i),concat('',i),i % 30);

set i := i + 1 ;

end while ;

commit ;

end

 

2.java代码

Connection conn = null ;

CallableStatement cst = null ;

try {

conn = getConn(); //连接

long start = System.currentTimeMillis() ;

cst = conn.prepareCall("{call up_biginsert(?)}");

cst.setInt(1, 1000000);

//执行存储过程

cst.execute();

System.out.println(System.currentTimeMillis() - start);

cst.close();

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

 

3.java执行DDL语句.

Connection conn = null ;

try {

conn = getConn(); //连接

long start = System.currentTimeMillis() ;

//删除过程

conn.prepareStatement("drop procedure up_biginsert").executeUpdate();

System.out.println(System.currentTimeMillis() - start);

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

 

 

MySQL函数,有返回值

-------------------

1.创建函数

mysql>create function uf_add(a int, b int) returns int return a + b ; //

2.java

3.

4..

 

JDBC

--------------

Connection //

Statement //

PreparedStatement //

CallableStatement //

 

MySQL

--------------

驱动程序.jar

Class.forName("com.mysql.jdbc.Driver");

url //schema://domainname:port/path

//jdbc:mysql://localhost:3306/big3

driverclass //com.mysql.jdbc.Driver

username //root

password //root

 

executeUpdate();

executeQuery(); //ResultSet 1

 

-- 注释

insert into users(name,age) values('tom',12); //插入

concat(',',123); //"," + 123 //

-- 更新

update users set name='xxx',age = 23 where id = 124 //更新

 

-- 删除

delete from users where ...

 

-- 查询

select * from users where ... order by id asc , name desc ;

 

-- 连接查询

 

连接处理

----------------

1.准备数据

-- 执行脚本

-- 删除子表

drop table if exists orders ;

 

-- 删除customers(主表)

drop table if exists customers ;

 

-- 创建customers表

create table customers(id int primary key auto_increment,name varchar(20));

 

-- 创建orders

create table orders(id int primary key auto_increment ,

orderno varchar(20),

price float ,

cid int);

 

-- 给orders添加外检约束

alter table orders add constraint foreign key (cid) references customers (id)

 

-- 添加数据

insert into customers(name) values('tom');

insert into customers(name) values('tomas');

insert into customers(name) values('tomasLee');

insert into orders(orderno,price,cid) values('no001',100.5,1);

insert into orders(orderno,price,cid) values('no002',100.5,1);

insert into orders(orderno,price,cid) values('no003',100.5,2);

insert into orders(orderno,price,cid) values('no004',100.5,2);

2.关联查询

a.内连接

-- (笛卡尔积)

-- 无条件连接.

select a.id aid , a.name aname , b.id bid,b.orderno borderno from customers a ,

orders b order by a.id,b.id;

 

-- 有条件

select a.id aid , a.name aname , b.id bid,b.orderno borderno from customers a ,orders b

where a.id = b.cid order by a.id,b.id;

 

b.外链接(left outer join)

-- 补全

 

-- left outer join

select a.*,b.* from customers a left outer join orders b on a.id = b.cid ;

 

-- right outer join

select a.*,b.* from customers a right outer join orders b on a.id = b.cid ;

 

-- full outer join

select a.*,b.* from customers a full outer join orders b on a.id = b.cid ;

 

3.聚集函数

a.max

最大值

select max(price) from orders ;

b.min

最小值

select min(price) from orders ;

c.avg

平均值

select avg(price) from orders ;

d.count

总数

select count(price) from orders ;

e.sum

总和

select sum(price) from orders ;

f.group by

select cid,max(price) from orders group by cid ;

 

g.group by ... having

-- 对分组之后的记录在进行过滤

select cid,max(price) from orders group by cid ;

h.select ... from ... where ... group by ... having ... order by ...

 

3.OLTP

on line transaction process,在线事务处理。

 

 

4.OLAP

on line analyze process,在线分析处理.

 

transaction

---------------

A:atomic

C:consistent

I:isolate

D:durable

 

commit()

rollback()

rollback(savepoint);

 

事务操作

-------------

读锁(共享)

写锁(独占)

 

 

事务并发执行,三个现象

------------

脏读 //读未提交,

//一个事务读取了另外一个事务改写还未提交的数据，

//如果另外一个事务在稍后时刻回滚则脏读发生了。

 

不可重复读 //读不回去

//一个事务在进行相同条件的查询连续的两次或者两次以上，

//每次结果都不同。

//查询期间，有其他事务进行update操作.

 

 

幻读 //读多了

//一个进行相同条件查询，在后来的查询中会发现原来的记录。

//查询期间，有其他事务进行insert操作.

 

 

ANSI(美国国家标准机构) SQL

---------------------------

1.

2.

3.

4.

 

隔离级别 : isolation level

--------------------------

1.1,read uncommitted,读未提交,导致脏读.

2.2,read committed,读已提交，避免了脏读，不可重复和幻读还能发生。

3.4,repeatable read,可以重复读，避免了不可重复读，幻读还能发生。

4.8,serializable,串行，避免了了以上现象。

 

MVCC

------------------------------

Multiple version concurrent control.

 

设置事务隔离级别

---------------------

1.set session transaction

设置当前回话的所有事务都是该级别。

2.set global transanction ...

设置全局的隔离级别，

3.set transaction ...

设置下次的事务隔离

4.之前的连接的级别不受影响

 

通过mysql客户端验证事务隔离级别

-------------------------------

准备.

-- 关闭自动提交

set autocommit=0 ;

 

[脏读]

[A]

2.关闭自动提交

set autocommit=0;

3.开始事务

start transaction ;

4.执行更新

udpate customers set name = 'jerry' where id = 1 ;

[B]

1.设置B的隔离级别

set session transaction isolation level read uncommitted ;-- read uncommitted | read committed | repeatable read | serializable

 

5.查询customers

select * from customers where id = 1 ; //读到jerry,脏读发生了.

 

[不可重复读]

注释:mysql的repeatable read隔离级别不仅能够避免不可重复读，还能避免幻读。

[A]

1.设置隔离级别 read repeatable read.

set session transaction isolation level repeatable read ;

 

2.关闭自动提交

set autocommit = 0;

3.开始事务

start transaction ;

4.查询

select name from customers where id = 1 ;

6.二次查询

select name from customers where id = 1 ; //jerry

[B]

5.开启事务，更新数据，提交数据。(默认是自动提交)

update customers set name = 'jerry' where id = 1 ;

 

[幻读]

将隔离级别设置为read uncommitted或者read committed均可。

 

mysql默认隔离级别是4---repeatable read.

oracle默认隔离级别是2---read committed,不支持repeatable read。

 

隔离级别

---------------

serializable

[A]

1.set autocommit=0;

2.set transaction isolation level serializable;

3.start transaction;

4.select * from customers;

 

7.select * from customers ; //

 

 

[B]

5.insert into customers(name) values('ttt') ;

6.commit();

 

查询是列转行显式

---------------------

select * from customers \G ;

 

 

锁

-------------------

[悲观锁]

不支持并发。//serializabe就是悲观锁

业务层面上实现:

 

customers

------------------------

| ...| ... | lock|

-------------------------

1 0/1

update ...

 

1.select lock

 

[乐观锁]支持并发，但是感知数据被篡改了。

使用标记位实现。

时间戳:同意

版本:(首选)

update ...,version=? where id = ? and version = ?

 

查询隔离级别

-----------------

select @@global.tx_islation; //全局

select @@session.tx_islation; //当前回话

select @@tx_islation; //下次

 

读操作时上写锁

----------------

1.使用serializable

2.select ... for update，类似于serializable隔离级别。

锁级别

----------------------

行级锁。

 

 

[表级锁]

lock tables customers read local ;

... //其他事务无法插入.

unlock tables;

 

设计模式

----------------

单例模式.

工厂模式

适配器模式

装饰模式

builder模式

 

池化模式(多例模式):使用有效的对象服务于大量的客户端。连接池。

 

对象

------------------

有状态:

无状态:

 

自定义数据源

------------------

 

 

使用第三方开源的连接池实现

---------------------------

1.c3p0

mchage包.

2.