• java18(MySQL JDBC)


    sc

    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包

        2. 

     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

  • 相关阅读:
    springboot对JPA的支持
    springboot整合redis
    spring boot整合mybatis
    mybatis与spring集成
    mybatis动态sql和分页
    mybatis入门
    使用java代码操作redis
    Redis安装和基本操作
    idea安装及使用
    爬虫
  • 原文地址:https://www.cnblogs.com/8386blogs/p/7606207.html
Copyright © 2020-2023  润新知