Java JDBC 操作MySQL，Properties，MyS
2018-08-12 本文已影响33人
两分与桥
数据库基本操作
数据库
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
