Java JDBC 操作MySQL，Properties，MyS

数据库基本操作

数据库
    RDBMS，relation database management system，关系型数据库管理系统。
        存放的是结构化数据
    
SQL：Structured Query Language，结构化查询语言

CRUD
    insert into table_name(field_name,...) values(value,...)
    
    select id,... from table_name where id=xxx,...
    
    update table_name set id=xxx,... where id=xxx
    
    delete from test where ...

JDBC介绍

 * JDBC是Java应用程序和数据库之间的通信桥梁，是Java应用程序访问数据库的通道

 * JDBC代表Java数据库连接(Java Database Connectivity)，它是用于Java编程语言和数据库之间的
 * 数据库无关连接的标准Java API，换句话说：JDBC是用于在Java语言编程中与数据库连接的API。
 * 
 * 1.JDBC标准主要由一组接口组成，其好处就是统一了各种数据库访问方式。
 * 2.JDBC接口的实现类称为数据库驱动，有各个数据库厂商提供，使用JDBC必须导入特定的驱动

JDBC与数据库通信就是进程间的通信，用的就是套接字。

对于数据库，我用的是phpstudy自带的MySQL数据库，phpstudy的安装十分简单，一直点击下一步就行，数据库账号密码默认都是root

java JDBC连接数据库的步骤

 * 1.导入JDBC驱动jar
 * 2.注册JDBC驱动
 *      -参数："驱动程序类名"
 *      -Class.forName("驱动程序类名")
 * 3.获得Connection对象
 *      -需要三个参数：url,username,password
 *      -连接到数据库
 * 4.创建Statement(语句)对象
 *      -conn.getStatement()方法创建对象
 *      -用于执行SQL语句
 *      -execute(ddl) 执行任何SQL，常用执行DDL，DCL
 *      -executeUpdate(dml) 执行DML语句，如：insert,update,delete
 *      -executeQuery(dql) 执行DQL语句，如：select
 * 5.处理SQL执行结果
 *      -execute(ddl) 如果没有异常则执行成功
 *      -executeUpdate 返回数字，表示更新"行"数量，抛出异常则失败
 *      -executeQuery(dql) 返回ResultSet(结果集)对象，代表执行结果
 *          使用for遍历处理，如果查询失败抛出异常！
 * 6.关闭数据连接，
 *      -conn.close();

maven导包，引入MySQL驱动

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

package com.libai;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Demo1 {
    public static void main(String[] args) {
        try {
             * 注册驱动程序
            Class.forName("com.mysql.jdbc.Driver");

             * 连接到数据库
             * getConnection()方法查找并且尝试连接到数据库
            String url = "jdbc:mysql://localhost:3306/test";
            String username = "root";
            String password = "root";
            Connection conn = DriverManager.getConnection(url, username, password);

             * 输出conn引用对象的实际类型
             * 证明：驱动程序提供了Connection接口的实现类
            System.out.println(conn.getClass());
            
             * 创建Statement语句对象
            Statement st = conn.createStatement();
            String ddl = "create table rabbit (id INT PRIMARY KEY auto_increment,name varchar(100))";
            String delsql = "drop table rabbit";
            boolean b = st.execute(delsql);

             * 返回结果：true 表示有结果集
             *          false 没有结果集
             *          创建失败抛出异常
             * 如果没有异常，则创建成功！--- 也就是false

            System.out.println(b);
            st.close();
            conn.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

打印：
class com.mysql.jdbc.JDBC4Connection
false

插入数据，使用executeUpdate()

String sql = "insert into rabbit set id=2,name='frame2'";
int n = st.executeUpdate(sql);
System.out.println(n);

打印：
1   --- 修改了一行，输出正确

select查询数据，使用executeQuery()

String sql = "select id,name from rabbit";
ResultSet n = st.executeQuery(sql);

* 处理结果····
* n.next()：移动结果集游标到下一行，默认在第一行之前
* 检查是否有数据，如果有返回true，否则false
while(n.next()) {
    * getXXX(列名)：返回结果集当前行指定列名的数据
    System.out.print(n.getInt("id"));
    System.out.print(n.getString("name"));
}

Properties 是Java专门读取配置文件的API

db.properties文件，建立在与main同级的resources下

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/test
jdbc.username=root
jdbc.password=root

package com.libai;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

* Properties 其目的就是为了读取 .properties文件，本身实现了Map接口
* 内部是散列表，限定了key和value都是String类型。
* 方法：load(流)将文件读取为散列表
* getProperty(key)查询value

public class Demo5 {
    public static void main(String[] args) {
        try {
            Properties prop = new Properties();
            System.out.println(prop);
            System.out.println(prop.size());
            System.out.println(prop.isEmpty());
            
             * 从包中加载资源
            InputStream input = Demo5.class.getClassLoader().getResourceAsStream("db.properties");

             * 将文件内容读取到散列表中
            prop.load(input);
            
             * 查询数据,读取文件内容
            String driver = prop.getProperty("jdbc.driver");
            System.out.println(driver);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}


修改数据参数后重新写入到文件中
Properties prop = new Properties();
prop.load(new FileInputStream(path));
prop.setProperty("name", "libai");
prop.setProperty("age", 22 + "");
prop.store(new FileOutputStream(path), "我的注释");

将Properties与JDBC查询数据库结合起来，方便之后修改数据库连接信息（总不能老是去修改java代码吧)

 * Properties读取配置文件,
 * 
 * Properties是java中专门用于读取配置文件的API
 * 管理数据库连接
 * 在软件中数据库连接使用非常频繁，如果每次都创建连接，就会造成代码的大量冗余，
 * 常规的做法是建立数据库连接工具类，封装数据库连接过程，统一数据库连接过程，使用时候就可以简化代码。
 * 
 * 
 * 实现步骤：
 * 1.创建数据库连接参数文件，db.properties
 * 2.创建DbUtils.java封装数据库连接方法
 *      -利用Properties读取配置文件中的数据库连接参数
 *      -创建方法 getConnection封装数据库连接过程
 * 3.使用getConnection方法

下面是封装了Properties读取数据库配置信息的和连接数据库的类

package com.libai;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class DbUtils {

     * 封装创建数据库连接的过程，简化数据库连接
     * 因为只需要一份，则定义为静态变量
    static String driver;
    static String url;
    static String username;
    static String password;
    static {
        try {
            Properties cfg = new Properties();
            InputStream input = DbUtils.class.getClassLoader().getResourceAsStream("db.properties");
            cfg.load(input);
            driver = cfg.getProperty("jdbc.driver");
            url = cfg.getProperty("jdbc.url");
            username = cfg.getProperty("jdbc.username");
            password = cfg.getProperty("jdbc.password");
            System.out.println(cfg);
----上面这句打印{jdbc.password=root, jdbc.username=root, jdbc.url=jdbc:mysql://localhost/test, initSize=5, jdbc.driver=com.mysql.jdbc.Driver, maxActive=10}
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url,username,password);
            return conn;
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
     * 关闭数据库的连接
    public static void close(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 * ResultSet代表DQL查询结果，是2维结果，其内部维护了一个读取数据的游标，
 * 默认情况，游标在第一行数据之前，当调用next()方法时候，游标会向下移动，
 * 并将返回结果集中是否包含数据，如果包含数据就返回true，
 * 结果集还提供了很好get方法用于获取结果集游标指向当前行数据。

调用上面的类实现连接数据库

package com.libai;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class Demo4 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DbUtils.getConnection();
            Statement st = conn.createStatement();
            String sql = "select * from rabbit";
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()) {
                System.out.println(rs.getInt("id")+", "+rs.getString("name"));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DbUtils.close(conn);
        }
    }
}

打印：
1, libai
2, ttt

数据库连接池DBCP

数据库连接池中存放一定数量的数据库连接，需要使用时从其中取，用完了就放回池中，循环使用（数据库连接的连接和关闭都是十分耗费资源的）

单例模式：singleton

池(集合)化模式：
    使用有限的对象数量服务于大量的客户端请求。
    
Datasource
    1.内部是连接池
    2.java.sql.Datasource, Connection getConnection();
    3.Connection.close()

使用DBCP

 * 使用DBCP
 * 
 * 1.导入连接池jar
 * 2.创建连接池对象
 * 3.设置数据库必须的连接参数
 * 4.设置可选的连接池管理策略参数
 * 5.从连接池中获得活动的数据库连接
 * 6.使用连接范围数据库
 * 7.使用以后关闭数据库连接，这个不是真的关闭连接，而是将使用过的连接归还到连接池。
 * 
 * 为了便捷的使用连接池，经常将连接池封装为一个连接管理工具类。

maven导入包DBCP

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>

一个数据库连接池的简单例子

package day02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbcp.BasicDataSource;

public class Demo1 {
    public static void main(String[] args) throws SQLException {
        BasicDataSource ds = new BasicDataSource();
        
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost/test";
        String username = "root";
        String password = "root";

         * 设置必须的参数
        ds.setDriverClassName(driver);
        ds.setUrl(url);
        ds.setUsername(username);
        ds.setPassword(password);
        
         * 设置连接池的管理策略参数
        ds.setInitialSize(2);  --- 一开始就设置池中存放两个连接
        ds.setMaxActive(100);   ---最多可以有100个连接
        
         * 使用连接池中的数据库连接，取不到就堵塞
        Connection conn = ds.getConnection();
        
         * 执行SQL
        Statement st = conn.createStatement();
        String sql = "select * from rabbit";
        ResultSet rs = st.executeQuery(sql);
        while(rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id+", "+name);
        }
        
         * 归还连接到数据库连接池！！
        conn.close();
    }
}

打印：
1, libai
2, talent
3, where
4, HOW

db.properties文件添加两条配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/test
jdbc.username=root
jdbc.password=root

# parameter for BasicDataSource，initSize是初始化建立1个连接，最大2个连接
initSize=1
maxActive=2

DBUtils.java 连接池模块，直接调用静态方法就行，配置参数依据上面的db.properties文件，下面所有的代码用到的全都是下面这个DBUtils.java 连接池模块。

package day02;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

 * 连接池版本的数据库连接管理工具类，适合于并发场合
public class DBUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    private static int initSize;
    private static int maxActive;
    
    private static BasicDataSource ds;
    
    static {        
        try {
            Properties cfg = new Properties();
            InputStream input = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
            cfg.load(input);
            driver = cfg.getProperty("jdbc.driver");
            url = cfg.getProperty("jdbc.url");
            username = cfg.getProperty("jdbc.username");
            password = cfg.getProperty("jdbc.password");
            initSize = Integer.parseInt(cfg.getProperty("initSize"));
            maxActive = Integer.parseInt(cfg.getProperty("maxActive"));
            
            ds = new BasicDataSource();
            
             * 初始化连接池
            ds.setDriverClassName(driver);
            ds.setUrl(url);
            ds.setUsername(username);
            ds.setPassword(password);
            ds.setInitialSize(initSize);
            ds.setMaxActive(maxActive);
            
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() {
        Connection conn = null;
        try {
             * getConnection()从连接池中获取重用的连接，如果连接池满了，
             * 则等待，如果有连接归还，则获取重用的连接
            conn = ds.getConnection();
            return conn;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    
     * 将用过的连接归还到连接池
    public static void close(Connection conn) {
        try {
            if(conn != null) {
                conn.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}

调用上面的静态方法连接池

package day02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo2 {
    public static void main(String[] args) {
        Connection conn = DBUtils.getConnection();
        Statement st;
        try {
            st = conn.createStatement();
            String sql = "select * from rabbit where id>2";
            ResultSet rs= st.executeQuery(sql);
            while(rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println(id+", "+name);
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(conn);
        }
    }
}

打印：
3, where
4, HOW

线程测试数据连接池的效果

package day02;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

 * 线程并发测试
public class Demo3 {
    public static void main(String[] args) {
        for(int i=0; i<5; i++) {
            Thread t = new DemoThread(5000);
            t.start();
        }
    }
}

class DemoThread extends Thread{
    int wait;

    public DemoThread(int wait) {
        this.wait = wait;
    }

    public void run() {
        Connection conn = null;
        try {
             * getConnection方法在连接池中没有连接可以使用的时候，会堵塞等待
            conn = DBUtils.getConnection();
            Statement st = conn.createStatement();
            String sql = "select 'Helllo' as a from dual";
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()) {
                System.out.println(Thread.currentThread().getName()+" "+rs.getString("a"));
            }
            Thread.sleep(wait);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            System.out.println(Thread.currentThread().getName()+" 归还连接");
            DBUtils.close(conn);
        }
    }
}

 * 连接池中最大只有两个连接时，第三个线程获取连接时会等待，
 * 等待到有连接归还为止

---- 上面的db.preproties文件中最大连接数设置为2了，所以此处两个两个执行
打印：
Thread-0 Helllo
Thread-3 Helllo
Thread-3 归还连接
Thread-0 归还连接
Thread-1 Helllo
Thread-2 Helllo
Thread-2 归还连接
Thread-1 归还连接
Thread-4 Helllo
Thread-4 归还连接

使用PreparedStatement提高数据库系统性能，重用执行计划

SQL语句翻译成执行计划，才能执行
SQL语句完全一样(大小写和空格都一样)的情况下，会重用执行计划
使用带参数的SQL语句可以使得重用执行计划，提高数据库性能

用法跟上面的Statement差不多

package day02;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class Demo4 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DBUtils.getConnection();

             * 带参数的SQL语句
            String sql = "insert into rabbit (id,name) values (?,?)";
            
             * 将SQL语句发送到数据库，创建执行计划，返回值ps就代表执行计划
            PreparedStatement ps= conn.prepareStatement(sql);

             * 替换[执行计划]中的参数，2个参数，按照序号发送参数
            ps.setInt(1, 5);
            ps.setString(2, "zhizhang");

             * 执行执行计划
            int n1 = ps.executeUpdate();
            System.out.println(n1);
            
             * 重用
            ps.setInt(1, 6);
            ps.setString(2, "hello");
            int n2 = ps.executeUpdate();
            System.out.println(n2);
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
}

打印：  --执行成功，返回修改的行数
1
1


换成update，也是类似的
conn = DBUtils.getConnection();
String sql = "update rabbit set name=? where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "demo change1");
ps.setInt(2, 5);
int n1 = ps.executeUpdate();
System.out.println(n1);

ps.setString(1, "demo change2");
ps.setInt(2, 6);
int n2 = ps.executeUpdate();
System.out.println(n2);

note

 * JDBC
 * 数据库连接池
 * 
 * DriverManager管理数据库连接适合单线程情况，而在多线程并发情况下，为了能够重用数据库连接，
 * 同时控制并发连接总数，避免数据库连接超载，一定要使用数据库连接池。
 * 
 * 连接池原理：
 * 数据库连接池的开源实现非常多，DBCP是常用的连接池之一。

只要用到了SQL语句拼接，要思考是否有SQL注入的风险，PreparedStatement可以避免SQL注入的风险，它将单引号等特殊字符转义。

* PreparedStatement 对象用于执行带参数的预编译执行计划
 * 
 * 关于执行计划：
 * 1.任何SQL执行过程都是先编译“执行计划”，再执行“执行计划”
 * 2.数据库为了优化性能，在SQL相同的时候，会重用执行计划
 *      -执行计划编译较慢
 *      -重用执行计划可以提提高数据库性能
 * 3.数据库只在SQL语句完全一样时候才重用相同的执行计划
 *      -如果SQL语句中有一个字符的更改，会创建不同的执行计划
 *      -SQL中一个空格或者一个大小写的不同也会创建不同的执行计划
 * 
 * PreparedStatement 好处是可以重复使用执行计划，提高DB效率
 * 
 * 使用步骤：
 * 1.将带参数的SQL发送到数据库创建执行计划
 * 2.替换执行计划中的参数
 * 3.执行执行计划，得到结果
 * 
 * PreparedStatement 可以避免SQL注入攻击，它将单引号等特殊字符加上反斜杠转义了

SQL注入例子

package day02;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Demo6 {
    public static void main(String[] args) {
        if(login()) {
            System.out.println("登陆成功");
        }else {
            System.out.println("账户名或密码错误");
        }
    }
    
    
    public static boolean login() {
        Scanner scan = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String username = scan.nextLine().trim();
        System.out.print("请输入密码:");
        String password = scan.nextLine().trim();
        
        Connection conn = null;
        int back = 0;
        try {
            conn = DBUtils.getConnection();
            String sql1 = "select count(*) as back from rabbit where username=? and password=?";
            
            --- 第一种方式，PreparedStatement
            PreparedStatement ps = conn.prepareStatement(sql1);
            ps.setString(1, username);      
            ps.setString(2, password);
            System.out.println("自动拼接："+ps);
            // ResultSet rs = ps.executeQuery();   ---未执行
            
            --- 第一种方式，Statement
            Statement st = conn.createStatement();
            String sql2 = "select count(*) as back from rabbit where username='"+ username +"' and password='"+ password +"'";
            System.out.println("手动拼接："+sql2);
            ResultSet rs = st.executeQuery(sql2);

            rs.next();
            back = rs.getInt("back");
            System.out.println("back = " + back);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(conn);
        }
        
        if(back >= 1) {
            return true;
        }else {
            return false;
        }
    }
}

打印;
请输入用户名：aa
请输入密码:123' or '1'='1
自动拼接：com.mysql.jdbc.JDBC42PreparedStatement@3daa422a: select count(*) as back from rabbit where username='aa' and password='123\' or \'1\'=\'1'
手动拼接：select count(*) as back from rabbit where username='aa' and password='123' or '1'='1'
back = 6
登陆成功

ResultSet中存在可滚动的结果集，但从来不用，其性能差

获取的ResultSet结果集中，存在获取的数据库信息的元数据，可用ResultSetMetaData API调用获取

 * 结果集元数据
 * ResultSetMetaData用于描述查询结果的相关性信息，其中包含列名称，列数量，类数据类型等

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Demo1 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DBUtils.getConnection();
            String sql = "select * from rabbit where id=1";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            --- 调用元数据，具体的信息都可以从接口中取得，具体可以查看java.sql文档
            System.out.println(rs.getMetaData());

            while(rs.next()) {
                String username = rs.getString("username");
                String password = rs.getString("password");
                System.out.println("username = "+username+" password = "+password);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
}

打印：
com.mysql.jdbc.ResultSetMetaData@654f0d9c - Field level information: 
    com.mysql.jdbc.Field@6a400542[catalog=test,tableName=rabbit,originalTableName=rabbit,columnName=id,originalColumnName=id,mysqlType=3(FIELD_TYPE_LONG),flags= AUTO_INCREMENT PRIMARY_KEY, charsetIndex=63, charsetName=US-ASCII]
    com.mysql.jdbc.Field@6580cfdd[catalog=test,tableName=rabbit,originalTableName=rabbit,columnName=username,originalColumnName=username,mysqlType=253(FIELD_TYPE_VAR_STRING),flags=, charsetIndex=8, charsetName=WINDOWS-1252]
    com.mysql.jdbc.Field@7e0b85f9[catalog=test,tableName=rabbit,originalTableName=rabbit,columnName=password,originalColumnName=password,mysqlType=253(FIELD_TYPE_VAR_STRING),flags=, charsetIndex=33, charsetName=UTF-8]
username = libai password = 111

JDBC事务控制

 * JDBC 事务控制，Transaction 一组不可分割的操作。
 * 
 * 数据库提供了事务控制功能，支持ACID特性。
 * JDBC提供了API，方便调用数据库的事务功能。
 * 
 * 相关API：
 *  - Connection.getAutoCommit():获得当前事务的提交方式，默认为true
 *  - Connection.setAutoCommit():设置事务的提交属性，参数是
 *      -true：自动提交； -false：不自动提交（默认自动提交）
 *  - Connection.commit()：提交事务
 *  - Connection.rollback()：回滚事务

特点：Atomic，原子性，不可分割，整体性。
     Consistent，一致性，数据不被破坏。
     isolate，隔离性，事务之间是独立的，不能被干扰的。
     Durable，持续性，数据被永久保存起来。

模拟转账，不符合条件抛出错误回滚，
注意：MySQL数据库测试表格的数据引擎要设置为InnoDB，默认的MyISAM不支持事务处理。

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Demo2 {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = DBUtils.getConnection();
            conn.setAutoCommit(false);
            String sql1 = "update myaccount set balance=balance+? where id=?";
            String sql2 = "select balance from myaccount where id=?";
            PreparedStatement ps = conn.prepareStatement(sql1);
            
            * 一个账户减少 1000
            ps.setInt(1, -1000);
            ps.setInt(2, 1);
            int n1 = ps.executeUpdate();
            System.out.println(n1);
            
            * 一个账户增加 1000
            ps.setInt(1, 1000);
            ps.setInt(2, 2);
            int n2 = ps.executeUpdate();
            System.out.println(n2);
            
            ps.close();
            
            * 检测余额是否大于等于0
            ps = conn.prepareStatement(sql2);
            ps.setInt(1, 1);
            ResultSet rs = ps.executeQuery();
            rs.next();
            int balance = rs.getInt("balance");
            if(balance<0) {
                throw new RuntimeException("余额不足，转账失败");
            }else {
                System.out.println("转账成功");
            }

            * 延时20s期间，在命令行中查看当前修改的表格时，是看不到本次交易结果的
            * 命令行中修改时，会堵塞到此次修改完毕
            System.out.println("延时中");
            Thread.sleep(20000);
            
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            DBUtils.close(conn);
        }
    }
}

打印：
1
1
转账成功
延时中

---

效果图
mysql事务测试
JDBC批量SQL处理

package day03;

import java.sql.Connection;
import java.sql.Statement;
import java.util.Arrays;

public class Demo3 {
    public static void main(String[] args) {
        String sql1 = "CREATE TABLE IF NOT EXISTS `account_01` (" + 
                "  `id` int(11) NOT NULL," + 
                "  `balance` int(11) NOT NULL," + 
                "  PRIMARY KEY (`id`)" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        String sql2 = "CREATE TABLE IF NOT EXISTS `account_02` (" + 
                "  `id` int(11) NOT NULL," + 
                "  `balance` int(11) NOT NULL," + 
                "  PRIMARY KEY (`id`)" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        String sql3 = "CREATE TABLE IF NOT EXISTS `account_03` (" + 
                "  `id` int(11) NOT NULL," + 
                "  `balance` int(11) NOT NULL," + 
                "  PRIMARY KEY (`id`)" + 
                ") ENGINE=InnoDB DEFAULT CHARSET=latin1;";
        Connection conn = null;
        try {
            conn = DBUtils.getConnection();
            Statement ps = conn.createStatement();

             * 将SQL语句添加到Statement缓存中
            ps.addBatch(sql1);
            ps.addBatch(sql2);
            ps.addBatch(sql3);

             * 执行一批SQL
            int[] n = ps.executeBatch();
            System.out.println(Arrays.toString(n));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
}

打印：
[0, 0, 0]      ---执行成功

Statement批量插入数据，注意，SQL语句中的并不是单引号，而是tab键上面的那个键的符号，不使用（`）也是可以的。

 * 批量执行
 * 批处理：发送到数据库作为一个单元执行的一组更新语句
 * 
 * 批处理降低了应用程序和数据库之间的网络调用，相比单个SQL语句的处理，批处理更为有效
 * 
 * API方法：
 * - addBatch(String sql)
 *      - Statement类的方法，可以将多条SQL语句添加到Statement对象的
 *        SQL语句列表中
 * - addBatch()
 *      - PreparedStatement类的方法，可以将多条预编译的SQL语句添加到
 *        PreparedStatement对象的SQL语句列表中
 * - executeBatch()
 *      - 把Statement对象或PreparedStatement对象语句列表中的所有SQL
 *       语句发送给数据库进行处理
 * - clearBatch()
 *      - 清空当前SQL语句列表
 * 
 * 
 * 防止批量过大出现OutOfMemory错误：
 * 如果PreparedStatement对象中的SQL列表包含过多的待处理SQL语句，可能会
 * 产生OutOfMemory错误，分段处理缓冲列表

package day03;

import java.sql.Connection;
import java.sql.Statement;
import java.util.Arrays;

public class Demo3 {
    public static void main(String[] args) {
        String sql1 = "insert into myaccount (`id`,`balance`) values (1,1000)";
        String sql2 = "insert into myaccount (`id`,`balance`) values (2,2000)";
        String sql3 = "insert into myaccount (`id`,`balance`) values (3,3000)";
        Connection conn = null;
        try {
            conn = DBUtils.getConnection();
            Statement ps = conn.createStatement();

             * 将SQL语句添加到Statement缓存中
            ps.addBatch(sql1);
            ps.addBatch(sql2);
            ps.addBatch(sql3);

             * 执行一批SQL
            int[] n = ps.executeBatch();
            System.out.println(Arrays.toString(n));
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
}

打印：
[1, 1, 1]     ---执行成功

PreparedStatement批量插入数据

package day03;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Arrays;

public class Demo4 {
    public static void main(String[] args) {
        String sql = "insert into myaccount (id,balance) values(?,?)";
        Connection conn = DBUtils.getConnection();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            for(int i=0; i<100; i++) {

                 * 替换参数
                ps.setInt(1, i+100);
                ps.setInt(2, i*1000);

                 * 将参数添加到ps缓冲区
                ps.addBatch();

                 * 清空缓冲区，一般是在数据量特别大的时候避免内存溢出
                if(i%8 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            int[] n = ps.executeBatch();
            System.out.println(Arrays.toString(n));
            
        } catch (Exception e) {
            e.printStackTrace();    
        } finally {
            DBUtils.close(conn);
        }
    }
}

打印：
[1, 1, 1]   --- 每8个一批已经被执行过了

返回自动主键

JDBC API提供了返回插入数据期间自动生成ID的API
PreparedStatement ps = conn.prepareStatement(sql,列名列表);
ResutSet rs = ps.getGeneratedKeys();

向数据库中插入/读取image和长文本

package test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.junit.Test;
import Utils.JDBCUtils;

public class TestCase2 {
     * 向数据库中插入image，长文本
     * 在数据库中image对应的存储类型是longblob，长文本对应的存储类型是longtext
    @Test
    public void test1() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        String sql = "INSERT INTO customers(name,sex,age,photo,memo) VALUES(?,?,?,?,?)";
        PreparedStatement ppst = conn.prepareStatement(sql);
        ppst.setString(1, "hello");
        ppst.setInt(2, 0);
        ppst.setInt(3, 23);
        ppst.setBinaryStream(4, new FileInputStream("E://TestCase//day20//demo.jpg"));
        ppst.setString(5, "aaaaaaaaaaaaaaaaaaaaa");
        
        ppst.executeUpdate();
        
        ppst.close();
        conn.close();
        System.out.println("over");
    }
    
     * 从数据库中读取image和长文本
    @Test
    public void test2() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        String sql = "SELECT * FROM customers WHERE id=?";
        PreparedStatement ppst = conn.prepareStatement(sql);
        ppst.setInt(1, 1);
        
        ResultSet rs = ppst.executeQuery();
        rs.next();
        int id = rs.getInt("id");
        String name = rs.getString("name");
        int sex = rs.getInt("sex");
        int age = rs.getInt("age");
        InputStream photo = rs.getBinaryStream("photo");
        String memo = rs.getString("memo");
        
        System.out.println(id + ", " + name + ", " + age + ", " + sex + "," + memo);
        System.out.println("photo length = " + photo.available());
        FileOutputStream fos = new FileOutputStream("E://TestCase//day24//demo.jpg");
        byte[] buffer = new byte[1024];
        int len = -1;
        while((len = photo.read(buffer)) != -1) {
            fos.write(buffer, 0, len);
        }
        fos.close();
        
        ppst.close();
        conn.close();
        System.out.println("over");
    }
}

存储过程

是在服务器中直接调用的，只需要调用时传递必要的参数，就可以直接执行，速度非常快。

前提是数据库不再需要其他的参数来执行数据库的操作了

在Java中用CallableStatement来负责调用数据库的存储过程

存储过程
    store procedure,存放在数据库中的一组SQL，在服务器端执行
    创建存储过程
        mysql>delimiter //  --声明结束符
    
    create procedure sp_biginsert(in num int)
    begin
        start transaction;
        declare i int default 0;
        while i < num do
            insert into customers(name,age,sex) values(concat('tom',i),i,i);
            set i = i + 1;
        end while;
        commit;
    end//
    
    mysql> show procedure status;//可以查看所有的存储过程

创建存储过程，作用是返回customers表的数据量
    mysql> create procedure sp_count(out param int)
        -> begin
        -> select count(*) into param from customers;
        -> end
        -> //
        Query OK, 0 rows affected (0.11 sec)

调用存储过程
    @Test
    public void test3() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        //输出的
        String sql = "{call sp_count(?)}";
        //创建cst对象
        CallableStatement cst = conn.prepareCall(sql);
        //注册输出参数
        cst.registerOutParameter(1, Types.INTEGER);
        //执行存储过程
        cst.execute();
        //取得输出参数
        int count = cst.getInt(1);
        System.out.println(count);
    }
打印结果：取得该表的数据量
10000

返回a+b的值

返回两个数的合
    mysql> create procedure sp_add(in a int, in b int,out c int)
        -> begin
        -> set c:=a+b;
        -> end
        -> //
        Query OK, 0 rows affected (0.00 sec)
调用该存储过程
    @Test
    public void test4() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        //输出的
        String sql = "{call sp_add(?,?,?)}";
        //创建cst对象
        CallableStatement cst = conn.prepareCall(sql);
        //对于输入参数需要绑定参数值
        cst.setInt(1, 1);
        cst.setInt(2, 5);
        //注册输出参数
        cst.registerOutParameter(3, Types.INTEGER);
        //执行存储过程
        cst.execute();
        //取得输出参数
        int count = cst.getInt(3);
        System.out.println(count);
    }
打印结果：
5

返回a-b的值，一个数可以作为输入量，也可以作为输出量

返回a-b的值，一个数可以作为输入量，也可以作为输出量
    mysql> create procedure sp_subtract(in a int, inout b int)
        -> begin
        -> set b:=a-b;
        -> end
        -> //
        Query OK, 0 rows affected (0.00 sec)

调用该存储过程
    @Test
    public void test5() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        //输出的
        String sql = "{call sp_subtract(?,?)}";
        //创建cst对象
        CallableStatement cst = conn.prepareCall(sql);
        //对于输入参数需要绑定参数值
        cst.setInt(1, 1);
        cst.setInt(2, 5);
        //注册输出参数
        cst.registerOutParameter(2, Types.INTEGER);
        //执行存储过程
        cst.execute();
        //取得输出参数
        int count = cst.getInt(2);
        System.out.println(count);
    }
打印结果：
-4

创建一个函数，并在JDBC中调用

该函数返回a+b的值
    mysql> create function f_add(a int, b int) returns int
        -> return a+b//
    Query OK, 0 rows affected (0.00 sec)

调用该函数
    @Test
    public void test6() throws Exception {
        Connection conn = JDBCUtils.getConnection();
        //输出的
        String sql = "{? = call f_add(?,?)}";
        //创建cst对象
        CallableStatement cst = conn.prepareCall(sql);
        //对于输入参数需要绑定参数值
        cst.setInt(2, 100);
        cst.setInt(3, 600);
        //注册输出参数
        cst.registerOutParameter(1, Types.INTEGER);
        //执行存储过程
        cst.execute();
        //取得输出参数
        int count = cst.getInt(1);
        System.out.println(count);
    }
打印结果：
700