java18(MySQL JDBC)

service console,服务控制台程序，可以启动，停止，删除等服务

sc start MySQL

sc stop MySQL

sc delete MySQL //删除，从注册表删除服务记录

MySQL常用指令

mysql -h hostname -u root -p password　　　　　　//连接远程数据库

mysql -u root -p password　　　　　　　　　　　　//连接localhost

mysql --help　　　　　　　　　　　　　　　　　　//查看帮助

mysql>select current_date;　　　　　　　　　　　　//

mysql>select now();　　　　　　　　　　　　　　　//函数

mysql>-- 这是注释

mysql>show databases;　　　　　　　　　　　　　//显示所有数据库

mysql>drop database mydatabase;　　　　　　　　//删除某个数据库

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

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

mysql>show tables;　　　　　　　　　　　　　　　//显示表

mysql>create table tablename testtable(id varchar(20), psw varchar(20));//创建表

mysql>describe testtable; 或 desc testtable;　　　　//查看表结构

mysql>drop table testtable;　　　　　　　　　　　　//删除表

mysql>select * from testtable;　　　　　　　　　　　//全字段，全表扫描

mysql>select * from testtable where id > 3;

mysql>select * from testtable where id >3 and id <5;

mysql> select * from testtable where id = '1';

mysql> select * from testtable where psw like '1%'; //以1开头的，模糊查询

mysql> select * from testtable where psw not like '1%'; //不是以1开头的

mysql> select * from testtable where psw like '1_'; //以1开头的接着是任意一个字符

mysql> select * from testtable where psw like '1\_'; //以1开头的接着是任意一个_(转义字符查询特殊字面量)

mysql> select * from testtable order by id desc;　　　　//j降序查询

mysql> select * from testtable order by id asc;

mysql> select * from testtable order by age asc, id desc;　　//组合排序

mysql>select id, psw from testtable;　　　　　　　　//投影查询

mysql>select * from testtable where psw is null;　　//查询psw为null

mysql>select * from testtable where psw is not null; //查询psw非null

mysql>select count(*) from testtable where psw = "123456";//聚集函数查询

mysql>select max(age) from testtable;

mysql>select min(age) from testtable;

mysql>select avg(age) from testtable;

mysql>select sum(age) from testtable where id < 5;

mysql>select * from testtable limit 1, 3;

mysql>select * from testtable limit 0, 3; =====select * from testtable limit 0. 3;//分页查询

mysql>insert into testtable(id, psw) values('1', 'pass');//插入

mysql>insert into testtable(id) values('1');//插入部分字段

mysql>insert into testtable values('1', 'pass');//插入全部字段

mysql>update testtable set psw = '123456' where id = '1';//修改

mysql>update testtable set age = 20 where id in (5, 6); //in

CRUD

[create]

insert into table_name(field_name,...) values(value,...);

[retrieve]

selete id,... from table_name where id = xxx,...;

[update]

update table_name set id = xxx, ... where id = xxx, ...;

[delete]

delete from table_name where ...;

source d:/findtest.sql　　　　//执行脚本文件

MySQL约束

1.primary key （非空，不能重复）

create tabe testtable(id int primary key, ...);

2.自增

create tabe testtable(id int primary key auto_increment, ...);

3.带条件创建

create database if not exists testbase;

create table if not exists test;

drop database if exists;

JDBC

1. java database connection, socket

2. 规范，都是接口

3. 引入驱动程序

　　jar , 类库，对SQL规范的实现

4. Connection

　　连接， Session == Socket

5. Statement

　　语句，InputStream

6. 使用JDBC的过程

　　　　a. 注册驱动程序

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

　　　　b. 获得连接

　　　　　　String url = "";

　　　　　　DriverManager.getConnection(url, user, pass);

　　　　c. 创建Statement语句

　　　　　　Statement st = cnn.cerateStatement();

　　　　d. 调用Statement执行sql语句

　　　　　　st.execute(sql);　　　　　　　　//insert delete update

　　　　　　ResultSet rs = st.executeQuery(sql); //select

　　　　e. 遍历ResultSet

　　　　　　while (rs.next()) {

　　　　　　　　// 从1开始

　　　　　　　　rs.getString(1)；

　　　　　　　　rs.getInt("id");

　　　　　　}

//JDBCDemo.java

 1 import java.sql.Connection;
 2 import java.sql.DriverManager;
 3 import java.sql.Statement;
 4 
 5 public class JDBCDemo {
 6 
 7     public static void main(String[] args) {
 8         // 注册驱动程序
 9         String url = "jdbc:mysql://localhost:3306/mytestbase";
10         String user = "root";
11         String password = "root";
12         // 驱动器管理器
13         try {
14             Class.forName("com.mysql.jdbc.Driver");
15             Connection connec = DriverManager.getConnection(url, user, password);
16             // 创建语句对象
17             Statement statement = connec.createStatement();
18             String sql = "insert into testtable values('6', 'opii')";
19             statement.execute(sql);
20             System.out.println("over");
21             // 释放资源
22             statement.close();
23             connec.close();
24         } catch (Exception e) {
25             e.printStackTrace();
26         }
27     }
28 
29 }

//TestCRUD.java

  1 import java.sql.Connection;
  2 import java.sql.DriverManager;
  3 import java.sql.Statement;
  4 
  5 import org.junit.Before;
  6 import org.junit.Test;
  7 
  8 import com.mysql.jdbc.ResultSet;
  9 
 10 /**
 11  * 测试增删查改
 12  * 
 13  */
 14 public class TestCRUD {
 15     
 16     private Connection connec;
 17 
 18     /**
 19      * 先执行
 20      */
 21     @Before
 22     public void initConn() {
 23                 String url = "jdbc:mysql://localhost:3306/mytestbase";
 24                 String user = "root";
 25                 String password = "root";
 26                 // 驱动器管理器
 27                 try {
 28                     // 注册驱动程序
 29                     Class.forName("com.mysql.jdbc.Driver");
 30                     connec = DriverManager.getConnection(url, user, password);
 31                 } catch (Exception e) {
 32                     e.printStackTrace();
 33                 }
 34     }
 35     /**
 36      * insert
 37      */
 38     @Test
 39     public void insert() {
 40         try {
 41             // 创建语句对象
 42             Statement statement = connec.createStatement();
 43             String sql = "insert into testtable values('7', 'opiill')";
 44             statement.execute(sql);
 45             System.out.println("over");
 46             // 释放资源
 47             statement.close();
 48             connec.close();
 49         } catch (Exception e) {
 50             e.printStackTrace();
 51         }
 52     }
 53     
 54     /**
 55      * update
 56      */
 57     @Test
 58     public void update() {
 59         try {
 60             // 创建语句对象
 61             Statement statement = connec.createStatement();
 62             String sql = "update testtable set psw='newpass' where id = '7'";
 63             statement.execute(sql);
 64             System.out.println("over");
 65             // 释放资源
 66             statement.close();
 67             connec.close();
 68         } catch (Exception e) {
 69             e.printStackTrace();
 70         }
 71     }
 72     
 73     /**
 74      * delete
 75      */
 76     @Test
 77     public void delete() {
 78         try {
 79             // 创建语句对象
 80             Statement statement = connec.createStatement();
 81             String sql = "delete from testtable where id = '7'";
 82             statement.execute(sql);
 83             System.out.println("over");
 84             // 释放资源
 85             statement.close();
 86             connec.close();
 87         } catch (Exception e) {
 88             e.printStackTrace();
 89         }
 90     }
 91     
 92     /**
 93      * query
 94      */
 95     @Test
 96     public void query() {
 97         try {
 98             // 创建语句对象
 99             Statement statement = connec.createStatement();
100             String sql = "select * from testtable";
101             ResultSet rs = (ResultSet) statement.executeQuery(sql);
102             while (rs.next()) {
103                 String id = rs.getString("id");
104                 String psw = rs.getString("psw");
105                 System.out.println(id + ":" + psw);
106             }
107             System.out.println("over");
108             // 释放资源
109             rs.close();
110             statement.close();
111             connec.close();
112         } catch (Exception e) {
113             e.printStackTrace();
114         }
115     }
116 }

DAO

　　1. Data Access Object, 数据访问对象

//App.java

 1 import com.hzg.jdbc.dao.PersonDao;
 2 
 3 public class App {
 4 
 5     @SuppressWarnings("unused")
 6     public static void main(String[] args) {
 7         PersonDao personDao = new PersonDao();
 8         /*Person p = new Person();
 9         p.setId(7);
10         p.setName("jerry");
11         personDao.insert(p );
12         */
13         // 2
14         
15         /*List<Person> all = personDao.findAll();
16         for (Person p0: all) {
17             System.out.println(p0.getId() + "," + p0.getName());
18         }*/
19         
20         /*Person p = new Person();
21         p.setId(6);
22         p.setName("tomas");
23         personDao.update(p);*/
24         
25         System.out.println("over");
26     }
27 
28 }

//Person.java

 1 package com.hzg.jdbc.domain;
 2 
 3 public class Person {
 4     private int id;
 5     private String name;
 6     public int getId() {
 7         return id;
 8     }
 9     public void setId(int id) {
10         this.id = id;
11     }
12     public String getName() {
13         return name;
14     }
15     public void setName(String name) {
16         this.name = name;
17     }
18     
19 }

//JDBCUtil.java

 1 package com.hzg.jdbc.util;
 2 
 3 
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.sql.ResultSet;
 7 import java.sql.Statement;
 8 
 9 /**
10  * 工具类
11  * @author zhengguohuang
12  *
13  */
14 public class JDBCUtil {
15     
16     static {
17         // 注册驱动
18         try {
19             Class.forName("com.mysql.jdbc.Driver");
20         } catch (ClassNotFoundException e) {
21             e.printStackTrace();
22         }
23     }
24     /**
25      * 开启连接
26      * @return
27      */
28     public static Connection connectDB() {
29         try {
30             // 获得连接
31             String url = "jdbc:mysql://localhost:3306/mytestbase";
32             String user = "root";
33             String pass = "root";
34             return (Connection) DriverManager.getConnection(url, user, pass);
35         } catch (Exception e) {
36             e.printStackTrace();
37         }
38         return null;
39     }
40     
41     /**
42      * 关闭连接
43      * @param connection
44      */
45     public static void closeConnection(Connection connection) {
46         try {
47             if (connection != null && !connection.isClosed())
48                 connection.close();
49         } catch (Exception e) {
50             e.printStackTrace();
51         }
52     }
53     
54     /**
55      * 关闭语句
56      * @param st
57      */
58     public static void closeStatement(Statement st) {
59         try {
60             if (st != null )
61                 st.close();
62         } catch (Exception e) {
63             e.printStackTrace();
64         }
65     }
66     
67     /**
68      * 关闭ResultSet
69      * @param resultSet
70      */
71     public static void closeResultSet(ResultSet resultSet) {
72         try {
73             if (resultSet != null )
74                 resultSet.close();
75         } catch (Exception e) {
76             e.printStackTrace();
77         }
78     }
79     
80     
81 }

//PersonDao.java

  1 package com.hzg.jdbc.dao;
  2 
  3 
  4 
  5 import java.sql.Connection;
  6 import java.sql.ResultSet;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import com.hzg.jdbc.domain.Person;
 11 import com.hzg.jdbc.util.JDBCUtil;
 12 
 13 
 14 /**
 15  * Person表的Dao类
 16  * 
 17  * @author zhengguohuang
 18  *
 19  */
 20 public class PersonDao {
 21 
 22     private Connection connection;
 23     private java.sql.Statement st;
 24     private ResultSet resultSet;
 25 
 26     /**
 27      * insert
 28      * 
 29      * @param name
 30      */
 31     public void insert(Person p) {
 32         try {
 33 
 34             connection = JDBCUtil.connectDB();
 35             st = connection.createStatement();
 36             String sql = "insert into persons(name) values('" + p.getName() + "')";
 37             st.execute(sql);
 38         } catch (Exception e) {
 39             e.printStackTrace();
 40         } finally {
 41             JDBCUtil.closeConnection(connection);
 42             JDBCUtil.closeStatement(st);
 43         }
 44     }
 45 
 46     /**
 47      * update
 48      * 
 49      * @param name
 50      */
 51     public void update(Person p) {
 52         try {
 53             connection = JDBCUtil.connectDB();
 54             st = connection.createStatement();
 55             String sql = "update persons set name = '" + p.getName() + "' where id = " + p.getId();
 56             st.execute(sql);
 57         } catch (Exception e) {
 58             e.printStackTrace();
 59         } finally {
 60             JDBCUtil.closeConnection(connection);
 61             JDBCUtil.closeStatement(st);
 62         }
 63     }
 64 
 65     /**
 66      * delete
 67      * 
 68      * @param name
 69      */
 70     public void deleteById(Integer id) {
 71         try {
 72             connection = JDBCUtil.connectDB();
 73 
 74             // 创建st对象
 75             st = connection.createStatement();
 76             String sql = "delete from persons where id = " + id;
 77             st.execute(sql);
 78         } catch (Exception e) {
 79             e.printStackTrace();
 80         } finally {
 81             JDBCUtil.closeConnection(connection);
 82             JDBCUtil.closeStatement(st);
 83         }
 84     }
 85 
 86     /**
 87      * query
 88      * 
 89      * @param name
 90      */
 91     public Person findById(Integer id) {
 92         try {
 93             connection = JDBCUtil.connectDB();
 94 
 95             // 创建st对象
 96             st = connection.createStatement();
 97             String sql = "select * from person where id = " + id;
 98             resultSet = st.executeQuery(sql);
 99             
100             while (resultSet.next()) {
101                 Person p = new Person();
102                 p.setId(resultSet.getInt("id"));
103                 p.setName(resultSet.getString("name"));
104                 return p;
105             }
106         } catch (Exception e) {
107             e.printStackTrace();
108         } finally {
109             JDBCUtil.closeResultSet(resultSet);
110             JDBCUtil.closeConnection(connection);
111             JDBCUtil.closeStatement(st);
112         }
113         return null;
114     }
115     
116     /**
117      * query
118      * 
119      * @param name
120      */
121     public List<Person> findAll() {
122         try {
123             connection = JDBCUtil.connectDB();
124 
125             // 创建st对象
126             st = connection.createStatement();
127             String sql = "select * from persons";
128             resultSet = st.executeQuery(sql);
129             List<Person> persons = new ArrayList<Person>();
130             Person p = null;
131             while (resultSet.next()) {
132                 p = new Person();
133                 p.setId(resultSet.getInt("id"));
134                 p.setName(resultSet.getString("name"));
135                 persons.add(p);
136                 
137             }
138             return persons;
139         } catch (Exception e) {
140             e.printStackTrace();
141         } finally {
142             JDBCUtil.closeResultSet(resultSet);
143             JDBCUtil.closeConnection(connection);
144             JDBCUtil.closeStatement(st);
145         }
146         return null;
147     }
148 }

SQL注入

String sql = "select * from persons where name = ' 1 ' or 1=1 -- ' and password = 'xxxxx' ";

 1 /**
 2      * 测试SQL注入
 3      */
 4     @Test
 5     public void testSQLInject1() {
 6         try {
 7             // 正常登录
 8             /*String id = "2";
 9             String psw = "123";*/
10             // 注入
11             
12             String id = "1' or 1=1 -- ";
13             String psw = "123";
14             Statement st = connec.createStatement();
15             String sql = "select * from testtable where id = '" + id + "' and psw = '"+ psw+"'";
16             ResultSet rs = st.executeQuery(sql);
17             if (rs.next()) {
18                 String sid = rs.getString(1);
19                 System.out.println("success");
20             } else {
21                 System.out.println("用户名/密码错误");
22             }
23             
24             // 释放资源
25             st.close();
26             connec.close();
27         } catch (Exception e) {
28             e.printStackTrace();
29         }
30     }

 1 /**
 2      * 测试SQL注入
 3      */
 4     @Test
 5     public void testSQLInject2() {
 6         try {
 7             // 正常登录
 8             /*String id = "2";
 9             String psw = "123";*/
10             // 注入
11             
12             String id = "'1' or 1=1 -- ";
13             String psw = "123";
14             String sql = "select * from testtable where id = ? and psw = ?";
15             PreparedStatement ppst = connec.prepareStatement(sql);
16             ppst.setString(1, id);
17             ppst.setString(2, psw);
18             ResultSet rs = ppst.executeQuery();
19             if (rs.next()) {
20                 String sid = rs.getString(1);
21                 System.out.println("success");
22             } else {
23                 System.out.println("用户名/密码错误");
24             }
25             
26             // 释放资源
27             ppst.close();
28             connec.close();
29         } catch (Exception e) {
30             e.printStackTrace();
31         }
32     }

用Statement插入10万条记录

 1 /**
 2      * insert use Statement
 3      */
 4     @Test
 5     public void insertInStatement() {
 6         try {
 7             // 创建语句对象
 8             Statement st = connec.createStatement();
 9             String sql = null;
10             for (int i = 1; i <= 100000; i++) {
11                 sql = "insert into testtable(id,psw) values('" + "" + i + "','"+ "" +(i % 50)+"')";
12                 st.execute(sql);
13             }
14             
15             // 释放资源
16             st.close();
17             connec.close();
18         } catch (Exception e) {
19             e.printStackTrace();
20         }
21     }

PreparedStateent

 1 /**
 2      * insert use PrepareStatement
 3      */
 4     @Test
 5     public void insertInPreparedStatement() {
 6         try {
 7             // 创建语句对象
 8             
 9             String sql = "insert into testtable(id,psw) values(?,?)";
10             PreparedStatement ppst = connec.prepareStatement(sql);
11             for (int i = 1; i <= 100000; i++) {
12                 ppst.setString(1, i + "");
13                 ppst.setString(2, i % 50 + "");
14                 ppst.executeUpdate();
15             }
16             
17             // 释放资源
18             ppst.close();
19             connec.close();
20         } catch (Exception e) {
21             e.printStackTrace();
22         }
23     }

事务

Transaction 一组不可分割的操作

事务的特点

ACID

Atomic:原子性，不可分割。整体

Consistent:一致性，数据不被破坏

Isolate:隔离性，事务之间独立，不能干扰

Durable:永久性，数据被永久保存起来

commit() 提交

rollback() 回滚

 1 /**
 2      * 测试commit
 3      */
 4     @Test
 5     public void test1() {
 6         try {
 7             // 创建语句对象
 8 
 9             String sql = "insert into testtable(id,psw) values(?,?)";
10             PreparedStatement ppst = connec.prepareStatement(sql);
11             connec.setAutoCommit(false);
12             ppst.setString(1, "4");
13             ppst.setString(2, 50+"");
14             ppst.executeUpdate();
15             // 手动提交
16             //connec.commit();
17             // 释放资源
18             ppst.close();
19             connec.close();
20         } catch (Exception e) {
21             e.printStackTrace();
22         }
23     }

 1 /**
 2      * 测试commit
 3      */
 4     @Test
 5     public void test1() {
 6         try {
 7             // 创建语句对象
 8 
 9             String sql = "insert into testtable(id,psw) values(?,?)";
10             PreparedStatement ppst = connec.prepareStatement(sql);
11             connec.setAutoCommit(false);
12             ppst.setString(1, "4");
13             ppst.setString(2, 50+"");
14             ppst.executeUpdate();
15             // 手动提交
16             connec.commit();
17             // 释放资源
18             ppst.close();
19             connec.close();
20         } catch (Exception e) {
21             e.printStackTrace();
22         }
23     }

 1 /**
 2      * insert use Statement
 3      */
 4     @Test
 5     public void insertInStatement() {
 6         try {
 7             // 创建语句对象
 8             Statement st = connec.createStatement();
 9             String sql = null;
10             connec.setAutoCommit(false);
11             for (int i = 1; i <= 100000; i++) {
12                 sql = "insert into testtable(id,psw) values('" + "" + i + "','" + "" + (i % 50) + "')";
13                 st.execute(sql);
14             }
15             connec.commit();
16             // 释放资源
17             st.close();
18             connec.close();
19         } catch (Exception e) {
20             e.printStackTrace();
21         }
22     }
23 
24     /**
25      * insert use PrepareStatement
26      */
27     @Test
28     public void insertInPreparedStatement() {
29         try {
30             // 创建语句对象
31             
32             String sql = "insert into testtable(id,psw) values(?,?)";
33             PreparedStatement ppst = connec.prepareStatement(sql);
34             connec.setAutoCommit(false);
35             for (int i = 1; i <= 100000; i++) {
36                 ppst.setString(1, i + "");
37                 ppst.setString(2, i % 50 + "");
38                 ppst.executeUpdate();
39             }
40             connec.commit();
41             // 释放资源
42             ppst.close();
43             connec.close();
44         } catch (Exception e) {
45             e.printStackTrace();
46         }
47     }

 1 /**
 2      * insert use PrepareStatement
 3      */
 4     @Test
 5     public void insertInPreparedStatement() {
 6         try {
 7             // 创建语句对象
 8             
 9             String sql = "insert into testtable(id,psw) values(?,?)";
10             PreparedStatement ppst = connec.prepareStatement(sql);
11             
12             connec.setAutoCommit(false);
13             
14             for (int i = 1; i <= 100000; i++) {
15                 ppst.setString(1, i + "");
16                 ppst.setString(2, i % 50 + "");
17                 // 添加批量命令
18                 ppst.addBatch();
19                 if (i % 4000 == 0) {
20                     ppst.executeBatch();
21                     ppst.clearBatch();
22                 }
23                 
24             }
25             ppst.executeBatch();
26             connec.commit();
27             // 释放资源
28             ppst.close();
29             connec.close();
30         } catch (Exception e) {
31             e.printStackTrace();
32         }
33     }

存储过程

store procedure 存放在数据库

1. 创建存储过程

mysql> delimiter //

mysql>create procedure simpleproc (OUT param1 INT)

BEGIN

select count(*) into param1 from t;

END

1 mysql> create procedure sp_count(out param int)
2     -> begin
3     -> select count(*) into param from testtable;
4     -> end
5     -> //

2. 在java中调用存储过程

 1 /**
 2      * query
 3      */
 4     @Test
 5     public void test1() {
 6         try {
 7             String sql = "{call sp_count(?)}";
 8             // 创建cst对象
 9             CallableStatement cst = connec.prepareCall(sql);
10             // 注册输出参数
11             cst.registerOutParameter(1, Types.INTEGER);
12             // 执行存储过程
13             cst.execute();
14             // 取得输出参数
15             int count = cst.getInt(1);
16             System.out.println(count);
17         } catch (Exception e) {
18             e.printStackTrace();
19         }
20     }

3.两个整数加法的存储过程

mysql>delimiter //

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

mysql>begin

mysql> set c:=a+b;

mysql>end

mysql>//

create procedure sp_biginsert(in num int)

begin

declare i int default 0;

start transaction;

while i < num do

insert into testtable(id, psw) values (concat('tom', i), concat('', i));

set i = i + 1;

end while;

commit;

end

 1 /**
 2      * biginsert in sp
 3      */
 4     @Test
 5     public void test2() {
 6         try {
 7             String sql = "{call sp_biginsert(?)}";
 8             // 创建cst对象
 9             CallableStatement cst = connec.prepareCall(sql);
10             cst.setInt(1, 100000);
11             // 执行存储过程
12             cst.execute();
13             
14             
15         } catch (Exception e) {
16             e.printStackTrace();
17         }
18     }

函数

create function f_hello() returns varchar(20)

return 'hello'//

调用:select function f_hello()

tinyint //1 byte

smallint //2 short

MEDIUMINT//3

int //4 int

bigint //8 long

char(20)

varchar(255)

blob //binary large object,二进制大对象

longblob

longtext //大文本

mysql大字段处理

1. 增加longblob字段

alter table persons add column photo longblob;

alter table persons add column memo longtext;

 1 /**
 2      * insert
 3      */
 4     @Test
 5     public void blob() {
 6         try {
 7             // 创建语句对象
 8             String sql = "insert into persons (name,photo,memo) values(?,?,?)";
 9             PreparedStatement ppst = connec.prepareStatement(sql);
10             // 绑定参数
11             ppst.setString(1, "tom");
12             FileInputStream fis = new FileInputStream("d:/2.png");
13             ppst.setBinaryStream(2, fis, fis.available());
14             ppst.setString(3, "hjhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
15             ppst.executeUpdate();
16 
17             // 释放资源
18             ppst.close();
19             connec.close();
20         } catch (Exception e) {
21             e.printStackTrace();
22         }
23     }

 1 /**
 2      * 读取blob
 3      */
 4     @Test
 5     public void findBlob() {
 6         try {
 7             // 创建语句对象
 8             String sql = "select * from persons where id = ?";
 9             PreparedStatement ppst = connec.prepareStatement(sql);
10             // 绑定参数
11             ppst.setInt(1, 8);
12             ResultSet rs = ppst.executeQuery();
13             if (rs.next()) {
14                 String name = rs.getString("name");
15                 InputStream is = rs.getBinaryStream("photo");
16                 byte[] buffer = new byte[1024];
17                 int len = -1;
18                 FileOutputStream fos = new FileOutputStream("d:/"+name+".png");
19                 while ((len = is.read(buffer)) != -1) {
20                     fos.write(buffer, 0, len);
21                 }
22                 fos.close();
23                 is.close();
24             }
25 
26             // 释放资源
27             ppst.close();
28             connec.close();
29         } catch (Exception e) {
30             e.printStackTrace();
31         }
32     }

在cli下关闭mysql的自动提交

mysql>set autocommit = 0;

mysql>start transaction; //开启事务

mysql>rollback; //回滚

mysql>commit; //提交

事务并发执行导致的3个现象

1. 脏读

读脏，dirty read

读未提交

一个事务读取了另一个事务改写还未提交的数据，如果另外一个事务在稍后时刻回滚，则该事务出现了脏读。

2. 不可重复读

一个事务在进行相同条件的查询，连续两次或两次以上每次结果不同

读不回去

3. 幻读

一个事务在进行相同条件的查询，连续两次或两次以上，在稍后的查询中会出现一些原来没有的记录

ANSI SQL隔离级别

1-read uncommitted,读未提交 --0001

2-read committed,读已提交 --0010（oracle 默认是2）

4-repeatable read,可重复读 --0100（mysql 默认是4）

8-Serializable,串行化 --1000

设置mysql隔离级别

SET[GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

脏读演示

1. 关闭A B的 autocommit

mysql>set autocommit=0;

2. A开启事务

mysql>start transaction; --开启事务

mysql>update persons set name = 'tomas' where id = 8; --更新

3. B设置隔离级别（read uncommitted）

mysql>set session transaction isolation level read uncommitted; --设置隔离级别

mysql>select * from persons where id = 8; -- 查询

4. A回滚

mysql>rollback;

mysql repetable read 可以避免幻读

Oracle不支持repetable read

查看mysql事务隔离级别

select @@global.tx_isolation; --全局的隔离级别

select @@session.tx_isolation;　　　　　　　　　　--当前会话的隔离级别

select @@tx_isolation; --当前会话

行级锁

表级锁

lock tables xxx write , yyy read; --上锁

unlock tables; --解锁

分组查询

mysql>create table customers(id int primary key auto_increatement, name varchar(20), int age, int salary )

mysql>select * from customers group by age;

mysql>select min(salary), max(salary), count(*) from customers group by age;

mysql> select max(salary), min(salary), age from customers group by age having age > 10; --用having进行过滤

连接查询

1. customers表，插入数据

2. 订单表

create table orders (id int primary key auto_increment, orderno varchar(20), price float, cid int);

3. 添加一个外键约束

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

4. select a.*, b.* from customers a, orders b （where a.id = b.cid）;

select a.*, b.* from customers a, orders b where a.id = b.cid order by a.id, b.id;（等值连接）

笛卡尔积

5. 使用join连接查询(内连接)

mysql>select a.id, a.name, b.id, b.orderno from customers a (inner) join orders b on a.id = b.cid;

6. 外连接

[可以补齐]

mysql>-- 有些customers没有orders,有些orders没有customers

mysql>-- 左外连接（向左看齐）

mysql>select a.id, a.name, b.id, b.orderno, b.price from customers a left outer join orders b on a.id = b.cid;

+----+----------+------+----------------+-------+
| id | name | id | orderno | price |
+----+----------+------+----------------+-------+
| 1 | tom | 1 | 201710121404kl | 4.5 |
| 3 | tomas | 3 | 201710121304kl | 4.5 |
| 4 | tomaslee | 4 | 201710121304dm | 4.5 |
| 5 | jerry | 5 | 201710121404dm | 4.5 |
| 2 | tom1 | NULL | NULL | NULL |
| 6 | cherry | NULL | NULL | NULL |
| 7 | alice | NULL | NULL | NULL |
| 8 | su | NULL | NULL | NULL |
| 9 | petter | NULL | NULL | NULL |
| 10 | hobe | NULL | NULL | NULL |
+----+----------+------+----------------+-------+

mysql> insert into orders (orderno, price, cid) values('201710122020ke', 20.8,NULL);

mysql>-- 右外连接(向右看齐)

mysql>select a.id, a.name, b.id, b.orderno, b.price from customers a left outer join orders b on a.id = b.cid;

+------+----------+----+----------------+-------+
| id | name | id | orderno | price |
+------+----------+----+----------------+-------+
| 4 | tomaslee | 1 | 201710121404kl | 4.5 |
| 10 | hobe | 3 | 201710121304kl | 4.5 |
| 1 | tom | 4 | 201710121304dm | 4.5 |
| 2 | tom1 | 5 | 201710121404dm | 4.5 |
| NULL | NULL | 6 | 201710122020ke | 20.8 |
+------+----------+----+----------------+-------+

mysql>-- 全外连接（mysql不支持）

集合查询

包括union, union all , minus(差集A-B mysql不支持), intersect mysql不支持

union:联合查询

1. 纵向合成若干查询的结果

2. mysql>select id, name from customers union select id, orderno from orders;

mysql>select * from customers where id <= 3 union select * from customers where id >= 3；-- 自动过滤重复

mysql>select * from customers where id <= 3 union all select * from customers where id >= 3；-- 防止自动过滤重复

范围运算

1. and or

2. between ... and ...

mysql>-- 闭区间

mysql>select * from customers where id between 4 and 8;

3.in 等价于 or or or or

mysql>select * from orders where id in (1,2,3,4);

4.any集合中的任何一个

mysql> select * from orders where cid > any(select id from customers where name like 't%');

5. all集合中的全部元素

mysql> select * from orders where cid > all(select id from customers where name like 't%');

去除重复元素

1. distinct

mysql>select distinct age from customers;

mysql> select count(distinct age) as agecount from customers;

池化模式

使用有限的对象数量服务于大量的客户端请求

Datasource

1. 内部是连接池

2. java.sql.Datasource

Connection.getConnection();

Connection.close();

使用装饰模式实现数据源

// MyDataSource.java

  1 package com.hzg.jdbc;
  2 
  3 import java.io.PrintWriter;
  4 import java.sql.Connection;
  5 import java.sql.DriverManager;
  6 import java.sql.SQLException;
  7 import java.sql.SQLFeatureNotSupportedException;
  8 import java.util.logging.Logger;
  9 
 10 import javax.sql.DataSource;
 11 /**
 12  * 自定义数据源（连接池）
 13  * @author zhengguohuang
 14  *
 15  */
 16 public class MyDataSource implements DataSource {
 17 
 18     static {
 19         try {
 20             Class.forName("com.mysql.jdbc.Driver");
 21         } catch (Exception e) {
 22             e.printStackTrace();
 23         }
 24     }
 25     private String url = "jdbc:mysql://localhost:3306/mytestbase";
 26     private String user = "root";
 27     private String pass = "123456";
 28     private static final int MAX = 3;
 29     
 30     public MyDataSource() {
 31         initPool();
 32     }
 33     
 34     /**
 35      * 初始化连接池
 36      */
 37     private void initPool() {
 38         try {
 39             for (int i = 0; i < MAX ; i++) {
 40             Connection conn = DriverManager.getConnection(url, user, pass);
 41             pool.addConnection(new MyConnectionWrapper(conn, pool));
 42         }
 43         } catch (Exception e) {
 44             e.printStackTrace();
 45         }
 46     }
 47 
 48     private ConnectionPool pool = new ConnectionPool();
 49     
 50     @Override
 51     public PrintWriter getLogWriter() throws SQLException {
 52         return null;
 53     }
 54 
 55     @Override
 56     public void setLogWriter(PrintWriter out) throws SQLException {
 57 
 58     }
 59 
 60     @Override
 61     public void setLoginTimeout(int seconds) throws SQLException {
 62 
 63     }
 64 
 65     @Override
 66     public int getLoginTimeout() throws SQLException {
 67         return 0;
 68     }
 69 
 70     @Override
 71     public Logger getParentLogger() throws SQLFeatureNotSupportedException {
 72         return null;
 73     }
 74 
 75     @Override
 76     public <T> T unwrap(Class<T> iface) throws SQLException {
 77         return null;
 78     }
 79 
 80     @Override
 81     public boolean isWrapperFor(Class<?> iface) throws SQLException {
 82         return false;
 83     }
 84 
 85     /**
 86      * 重点实现
 87      */
 88     @Override
 89     public Connection getConnection() throws SQLException {
 90         return pool.getConnection();
 91     }
 92 
 93     /**
 94      * 重点实现
 95      */
 96     @Override
 97     public Connection getConnection(String username, String password) throws SQLException {
 98         return pool.getConnection();
 99     }
100 
101 }

// ConnectionPool.java

 1 package com.hzg.jdbc;
 2 
 3 import java.sql.Connection;
 4 import java.util.LinkedList;
 5 import java.util.List;
 6 /**
 7  * 连接池
 8  * @author zhengguohuang
 9  *
10  */
11 public class ConnectionPool {
12 
13     private List<Connection> pool = new LinkedList<Connection>();
14     private static final int MAX = 3;
15     
16     /**
17      * 从连接池中取连接
18      */
19     public synchronized Connection getConnection() {
20         try {
21             while (pool.isEmpty()) {
22                 this.wait();
23             }
24             return pool.remove(0);
25         } catch (Exception e) {
26             e.printStackTrace();
27         }
28         return null;
29     }
30     
31     /**
32      * 放置连接
33      */
34     public synchronized void addConnection(Connection conn) {
35         try {
36             while (pool.size() >= MAX ) {
37                 this.wait();
38             }
39         } catch (Exception e) {
40             e.printStackTrace();
41         }
42         pool.add(conn);
43         this.notifyAll();
44     }
45 }

// MyConnectionWrapper.java

  1 package com.hzg.jdbc;
  2 
  3 import java.sql.Array;
  4 import java.sql.Blob;
  5 import java.sql.CallableStatement;
  6 import java.sql.Clob;
  7 import java.sql.Connection;
  8 import java.sql.DatabaseMetaData;
  9 import java.sql.NClob;
 10 import java.sql.PreparedStatement;
 11 import java.sql.SQLClientInfoException;
 12 import java.sql.SQLException;
 13 import java.sql.SQLWarning;
 14 import java.sql.SQLXML;
 15 import java.sql.Savepoint;
 16 import java.sql.Statement;
 17 import java.sql.Struct;
 18 import java.util.Map;
 19 import java.util.Properties;
 20 import java.util.concurrent.Executor;
 21 
 22 /**
 23  * 连接包装类
 24  * @author zhengguohuang
 25  *
 26  */
 27 public class MyConnectionWrapper implements Connection {
 28 
 29     private Connection conn;
 30     
 31     private ConnectionPool pool;
 32     public MyConnectionWrapper(Connection conn,ConnectionPool pool ) {
 33         this.conn = conn;
 34         this.pool = pool;
 35     }
 36     @Override
 37     public <T> T unwrap(Class<T> iface) throws SQLException {
 38         return null;
 39     }
 40 
 41     @Override
 42     public boolean isWrapperFor(Class<?> iface) throws SQLException {
 43         return false;
 44     }
 45 
 46     @Override
 47     public Statement createStatement() throws SQLException {
 48         return conn.createStatement();
 49     }
 50 
 51     @Override
 52     public PreparedStatement prepareStatement(String sql) throws SQLException {
 53         return conn.prepareStatement(sql);
 54     }
 55 
 56     @Override
 57     public CallableStatement prepareCall(String sql) throws SQLException {
 58         return null;
 59     }
 60 
 61     @Override
 62     public String nativeSQL(String sql) throws SQLException {
 63         return null;
 64     }
 65 
 66     @Override
 67     public void setAutoCommit(boolean autoCommit) throws SQLException {
 68 
 69     }
 70 
 71     @Override
 72     public boolean getAutoCommit() throws SQLException {
 73         return false;
 74     }
 75 
 76     @Override
 77     public void commit() throws SQLException {
 78         conn.commit();
 79     }
 80 
 81     @Override
 82     public void rollback() throws SQLException {
 83         conn.rollback();
 84     }
 85 
 86     /**
 87      * 重写关闭方法
 88      */
 89     @Override
 90     public void close() throws SQLException {
 91         // 把自己重新放到池子中
 92         pool.addConnection(this);
 93     }
 94 
 95     @Override
 96     public boolean isClosed() throws SQLException {
 97         return false;
 98     }
 99 
100     @Override
101     public DatabaseMetaData getMetaData() throws SQLException {
102         return null;
103     }
104 
105     @Override
106     public void setReadOnly(boolean readOnly) throws SQLException {
107 
108     }
109 
110     @Override
111     public boolean isReadOnly() throws SQLException {
112         return false;
113     }
114 
115     @Override
116     public void setCatalog(String catalog) throws SQLException {
117 
118     }
119 
120     @Override
121     public String getCatalog() throws SQLException {
122         return null;
123     }
124 
125     @Override
126     public void setTransactionIsolation(int level) throws SQLException {
127 
128     }
129 
130     @Override
131     public int getTransactionIsolation() throws SQLException {
132         return 0;
133     }
134 
135     @Override
136     public SQLWarning getWarnings() throws SQLException {
137         return null;
138     }
139 
140     @Override
141     public void clearWarnings() throws SQLException {
142 
143     }
144 
145     @Override
146     public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
147         return conn.createStatement(resultSetType, resultSetConcurrency);
148     }
149 
150     @Override
151     public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
152             throws SQLException {
153         return conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
154     }
155 
156     @Override
157     public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
158         return null;
159     }
160 
161     @Override
162     public Map<String, Class<?>> getTypeMap() throws SQLException {
163         return null;
164     }
165 
166     @Override
167     public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
168 
169     }
170 
171     @Override
172     public void setHoldability(int holdability) throws SQLException {
173 
174     }
175 
176     @Override
177     public int getHoldability() throws SQLException {
178         return 0;
179     }
180 
181     @Override
182     public Savepoint setSavepoint() throws SQLException {
183         return null;
184     }
185 
186     @Override
187     public Savepoint setSavepoint(String name) throws SQLException {
188         return null;
189     }
190 
191     @Override
192     public void rollback(Savepoint savepoint) throws SQLException {
193 
194     }
195 
196     @Override
197     public void releaseSavepoint(Savepoint savepoint) throws SQLException {
198 
199     }
200 
201     @Override
202     public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
203             throws SQLException {
204         return null;
205     }
206 
207     @Override
208     public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
209             int resultSetHoldability) throws SQLException {
210         return null;
211     }
212 
213     @Override
214     public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
215             int resultSetHoldability) throws SQLException {
216         return null;
217     }
218 
219     @Override
220     public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
221         return null;
222     }
223 
224     @Override
225     public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
226         return null;
227     }
228 
229     @Override
230     public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
231         return null;
232     }
233 
234     @Override
235     public Clob createClob() throws SQLException {
236         return null;
237     }
238 
239     @Override
240     public Blob createBlob() throws SQLException {
241         return null;
242     }
243 
244     @Override
245     public NClob createNClob() throws SQLException {
246         return null;
247     }
248 
249     @Override
250     public SQLXML createSQLXML() throws SQLException {
251         return null;
252     }
253 
254     @Override
255     public boolean isValid(int timeout) throws SQLException {
256         return false;
257     }
258 
259     @Override
260     public void setClientInfo(String name, String value) throws SQLClientInfoException {
261 
262     }
263 
264     @Override
265     public void setClientInfo(Properties properties) throws SQLClientInfoException {
266 
267     }
268 
269     @Override
270     public String getClientInfo(String name) throws SQLException {
271         return null;
272     }
273 
274     @Override
275     public Properties getClientInfo() throws SQLException {
276         return null;
277     }
278 
279     @Override
280     public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
281         return null;
282     }
283 
284     @Override
285     public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
286         return null;
287     }
288 
289     @Override
290     public void setSchema(String schema) throws SQLException {
291 
292     }
293 
294     @Override
295     public String getSchema() throws SQLException {
296         return null;
297     }
298 
299     @Override
300     public void abort(Executor executor) throws SQLException {
301 
302     }
303 
304     @Override
305     public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
306 
307     }
308 
309     @Override
310     public int getNetworkTimeout() throws SQLException {
311         return 0;
312     }
313 
314 }

// App2.java

 1 package com.hzg.jdbc;
 2 
 3 import java.sql.Connection;
 4 import java.sql.ResultSet;
 5 import java.sql.Statement;
 6 
 7 public class App2 {
 8 
 9     public static void main(String[] args) {
10         try {
11             MyDataSource ds = new MyDataSource();
12             Connection conn = ds.getConnection();
13             Statement st = conn.createStatement();
14             ResultSet rs = st.executeQuery("select * from customers");
15             while (rs.next()) {
16                 System.out.println(rs.getString("name"));
17             }
18             rs.close();
19             st.close();
20             conn.close();
21             System.out.println("over");
22         } catch (Exception e) {
23             e.printStackTrace();
24         } finally {
25             
26         }
27         
28     }
29 
30 }

使用第三方数据源（c3p0）

1. 下载jar包

 1 package com.hzg.jdbc.test;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 
 6 import org.junit.Test;
 7 
 8 import com.mchange.v2.c3p0.ComboPooledDataSource;
 9 
10 public class TestC3p0 {
11 
12     public static void main(String[] args) throws Exception{
13         insert();
14     }
15     
16     
17     public static void insert() throws Exception {
18         System.out.println("over");
19         ComboPooledDataSource ds = new ComboPooledDataSource();
20         ds.setDriverClass("com.mysql.jdbc.Driver");
21         ds.setJdbcUrl("jdbc:mysql://localhost:3306/mytestbase");
22         ds.setUser("root");
23         ds.setPassword("root");
24         
25         Connection conn = ds.getConnection();
26         /*PreparedStatement ppst = conn.prepareStatement("insert into customers(name, age) values('alice2', 12)");
27         ppst.executeUpdate();
28         
29         ppst.close();
30         conn.close();*/
31         System.out.println(conn+"slf;dkfj;sl");
32         System.out.println("over");
33     }
34 }

控制台输出：

十月 14, 2017 5:27:33 下午 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
十月 14, 2017 5:27:33 下午 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1-pre6 [built 23-March-2006 16:11:59 +0200; debug? true; trace: 10]
十月 14, 2017 5:27:33 下午 com.mchange.v2.c3p0.PoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource@3941a79c[ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, allUsers -> [], autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 312b1dae, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 3941a79c, idleConnectionTestPeriod -> -1, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/mytestbase, maxIdleTime -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@7a4f0f29