MySQL数据库干货_30——【精选】JDBC常用操作
JDBC批量添加数据
批量添加数据简介
在JDBC中通过PreparedStatement的对象的addBatch()和executeBatch()方法进行数据的批量插入。
addBatch()把若干SQL语句装载到一起，然后一次性传送到数据库执行，即是批量处理sql数据的。executeBatch()会将装载到一起的SQL语句执行。
注意：
MySql默认情况下是不开启批处理的。
数据库驱动从5.1.13开始添加了一个对rewriteBatchStatement的参数的处理，该参数能够让MySql开启批处理。在url中添加该参数：rewriteBatchedStatements=true
Mysql的URL参数说明
| useUnicode | [true | false] | 是否使用编码集,需配合 characterEncoding 参数使用。 |
| characterEncoding | [utf-8 | gbk | …] | 编码类型。 |
| useSSL | [true | false] | 是否使用SSL协议。 |
| rewriteBatchedStatements | [true | false] | 可以重写向数据库提交的SQL语句。 |
实现数据的批量添加
在url中开启批量添加
rewriteBatchedStatements=true
实现数据的批量添加方式一
/**
* 批量添加数据方式一
*/
public void addBatch1(){
Connection conn = null;
PreparedStatement ps =null;
try{
//创建连接
conn = JdbcUtils.getConnection();
//创建PreparedStatement
ps = conn.prepareStatement("insert into users values(default ,?,?)");
//参数绑定
for(int i=0;i<1000;i++){
//绑定username
ps.setString(1,"java"+i);
//绑定userage
ps.setInt(2,20);
//缓存sql
ps.addBatch();
}
//执行sql
ps.executeBatch();
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtils.closeResource(ps,conn);
}
}
实现数据的批量添加方式二
/**
* 批量添加数据方式二
*/
public void addBatch2(){
Connection conn = null;
PreparedStatement ps =null;
try{
//创建连接
conn = JdbcUtils.getConnection();
//创建PreparedStatement
ps = conn.prepareStatement("insert into users values(default ,?,?)");
//参数绑定
for(int i=1;i<=1000;i++){
//绑定username
ps.setString(1,"java"+i);
//绑定userage
ps.setInt(2,20);
//缓存sql
ps.addBatch();
if(i%500 == 0){
//执行sql
ps.executeBatch();
//清除缓存
ps.clearBatch();
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtils.closeResource(ps,conn);
}
}
JDBC事务处理
事务简介
-
事务：
事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
-
事务操作流程：
- 开启事务
- 提交事务
- 回滚事务
JDBC中事务处理特点
在JDBC中，使用Connection对象来管理事务，默认为自动提交事务。可以通过setAutoCommit(boolean autoCommit)方法设置事务是否自动提交，参数为boolean类型，默认值为true，表示自动提交事务，如果值为false则表示不自动提交事务，需要通过commit方法手动提交事务或者通过rollback方法回滚事务。
JDBC事务处理实现
/**
* 批量添加数据方式二
* 支持事务处理
*/
public void addBatch2(){
Connection conn = null;
PreparedStatement ps =null;
try{
//创建连接
conn = JdbcUtils.getConnection();
//设置事务的提交方式，将自动提交修改为手动提交
conn.setAutoCommit(false);
//创建PreparedStatement
ps = conn.prepareStatement("insert into users values(default ,?,?)");
//参数绑定
for(int i=1;i<=1000;i++){
//绑定username
ps.setString(1,"java"+i);
//绑定userage
ps.setInt(2,20);
//缓存sql
ps.addBatch();
if(i%500 == 0){
//执行sql
ps.executeBatch();
//清除缓存
ps.clearBatch();
}
if(i==501){
String str = null;
str.length();
}
}
//提交事务
JdbcUtils.commit(conn);
}catch(Exception e){
e.printStackTrace();
JdbcUtils.rollback(conn);
}finally{
JdbcUtils.closeResource(ps,conn);
}
}
Blob类型的使用
MySql Blob类型简介
Blob(全称：Binary Large Object 二进制大对象)。在MySql中，Blob是一个二进制的用来存储图片，文件等数据的数据类型。==操作Blob类型的数据必须使用PreparedStatement，==因为Blob类型的数据无法使用字符串拼接。大多数情况，并不推荐直接把文件存放在 MySQL 数据库中，但如果应用场景是文件与数据高度耦合，或者对文件安全性要求较高的，那么将文件与数据存放在一起，即安全，又方便备份和迁移。
Mysql中的Blob类型
MySql中有四种Blob类型，它们除了在存储的最大容量上不同，其他是一致的。
| 类型 | 大小 |
|---|---|
| TinyBlob | 最大255字节 |
| Blob | 最大65K |
| MediumBlob | 最大16M |
| LongBlob | 最大4G |
Blob类型使用的注意事项
- 实际使用中根据需要存入的数据大小定义不同的Blob类型。
- 如果存储的文件过大，数据库的性能会下降。
插入Blob类型数据
创建表
CREATE TABLE `movie` (
`movieid` int(11) NOT NULL AUTO_INCREMENT,
`moviename` varchar(30) DEFAULT NULL,
`poster` mediumblob,
PRIMARY KEY (`movieid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
通过PreparedStatement存储Blob类型数据
/**
* Blob类型操作测试类
*/
public class BlobTest {
/**
* 向Movie表中插入数据
*/
public void insertMovie(String moviename, InputStream is){
Connection conn =null;
PreparedStatement ps =null;
try{
//获取连接
conn = JdbcUtils.getConnection();
//创建PreparedStatement对象
ps = conn.prepareStatement("insert into movie values(default,?,?)");
//绑定参数
ps.setString(1,moviename);
ps.setBlob(2,is);
ps.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtils.closeResource(ps,conn);
}
}
public static void main(String[] args) throws FileNotFoundException {
BlobTest bt = new BlobTest();
//创建读取文件的IO流
InputStream is = new FileInputStream(new File("d:/1.jpg"));
bt.insertMovie("java",is);
}
}
解除文件大小限制
虽然MediumBlob允许保存最大值为16M，但MySql中默认支持的容量为4194304即4M。我们可以通过修改Mysql的my.ini文件中max_allowed_packet属性扩大支持的容量，修改完毕后需要重启MySql服务。
读取Blob类型数据
/**
* 根据影片ID查询影片信息
* @param movieid
*/
public void selectMovieById(int movieid){
Connection conn =null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
//获取连接
conn =JdbcUtils.getConnection();
//创建PreparedStatement对象
ps = conn.prepareStatement("select * from movie where movieid = ?");
//绑定参数
ps.setInt(1,movieid);
//执行sql
rs = ps.executeQuery();
while(rs.next()){
int id = rs.getInt("movieid");
String name = rs.getString("moviename");
System.out.println(id+" "+name);
//获取blob类型的数据
Blob blob = rs.getBlob("poster");
//获取能够从Blob类型的列中读取数据的IO流
InputStream is = blob.getBinaryStream();
//创建文件输出字节流对象
OutputStream os = new FileOutputStream(id+"_"+name+".jpg");
//操作流完成文件的输出处理
byte[] buff = new byte[1024];
int len;
while((len = is.read(buff)) != -1){
os.write(buff,0,len);
}
os.flush();
is.close();
os.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtils.closeResource(rs,ps,conn);
}
}
其他查询方式
模糊查询
实现模糊查询
/**
* 模糊查询测试类
*/
public class FuzzyQueryTest {
/**
* 根据用户名称模糊查找用户信息
*/
public List<Users> fuzzyQuery(String username){
List<Users> list= new ArrayList<>();
Connection conn =null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
//获取数据库连接
conn = JdbcUtils.getConnection();
//创建PreparedStatement对象
ps = conn.prepareStatement("select * from users where username like ?");
//参数绑定
ps.setString(1,username);
//执行sql语句
rs = ps.executeQuery();
while(rs.next()){
Users user = new Users();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setUserage(rs.getInt("userage"));
list.add(user);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtils.closeResource(rs,ps,conn);
}
return list;
}
public static void main(String[] args) {
FuzzyQueryTest ft = new FuzzyQueryTest();
List<Users> users = ft.fuzzyQuery("%d%");
for(Users user1:users){
System.out.println(user1.getUserid()+" "+user1.getUsername()+" "+user1.getUserage());
}
}
}
动态条件查询
动态条件查询实现
/**
* 动态条件查询测试类
*/
public class DynamicConditionQueryTest {
/**
* 动态条件查询Users
*/
public List<Users> queryUsers(Users users){
List<Users> list= new ArrayList<>();
Connection conn =null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
//获取数据库连接
conn = JdbcUtils.getConnection();
//拼接查询SQL语句
String sql = this.generateSql(users);
System.out.println(sql);
//创建PreparedStatement对象
ps = conn.prepareStatement(sql);
//执行sql语句
rs = ps.executeQuery();
while(rs.next()){
Users user = new Users();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setUserage(rs.getInt("userage"));
list.add(user);
}
}catch(Exception e){
e.printStackTrace();
}finally{
JdbcUtils.closeResource(rs,ps,conn);
}
return list;
}
/**
* 生成动态条件查询sql
*/
private String generateSql(Users users){
StringBuffer sb = new StringBuffer("select * from users where 1=1 ");
if(users.getUserid() > 0){
sb.append(" and userid = ").append(users.getUserid());
}
if(users.getUsername() ! =null &&users.getUsername().length() > 0){
sb.append(" and username = '").append(users.getUsername()).append("'");
}
if(users.getUserage() > 0){
sb.append(" and userage = ").append(users.getUserage());
}
return sb.toString();
}
public static void main(String[] args) {
DynamicConditionQueryTest dt = new DynamicConditionQueryTest();
Users users = new Users();
users.setUsername("java");
users.setUserage(20);
List<Users> list = dt.queryUsers(users);
for(Users user1:list){
System.out.println(user1.getUserid()+" "+user1.getUsername()+" "+user1.getUserage());
}
}
}
分页查询
分页查询简介
当一个操作数据库进行查询的语句返回的结果集内容如果过多，那么内存极有可能溢出，所以在查询中含有大数据的情况下分页是必须的。
分页查询分类：
- 物理分页：
- 在数据库执行查询时（实现分页查询），查询需要的数据—依赖数据库的SQL语句
- 在SQL查询时，从数据库只检索分页需要的数据
- 通常不同的数据库有着不同的物理分页语句
- MySql物理分页采用limit关键字
- 逻辑分页：
- 在sql查询时，先从数据库检索出所有数据的结果集，在程序内，通过逻辑语句获得分页需要的数据
如何在MySql中实现物理分页查询
使用limit方式。
limit的使用
select * from tableName limit m,n
其中m与n为数字。n代表需要获取多少行的数据项，而m代表从哪开始(以0为起始)。
例如我们想从users表中先获取前两条数据SQL为：
select * from users limit 0,2;
那么如果要继续看下两条的数据则为：
select * from users limit 2,2;
以此类推
分页公式：(当前页-1)*每页大小
创建Page模型
Page
/**
* 分页查询实体类
*/
public class Page<T> {
//当前页
private int currentPage;
//每页显示的条数
private int pageSize;
//总条数
private int totalCount;
//总页数
private int totalPage;
//结果集
private List<T> result;
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public List<T> getResult() {
return result;
}
public void setResult(List<T> result) {
this.result = result;
}
}
实现分页查询
分页实现
/**
* 分页查询测试类
*/
public class PageTest {
/**
* 实现Users的分页查询
*/
public Page<Users> selectPage(Page page){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
List<Users> list =new ArrayList<>();
try{
//与数据库建立连接
connection=JdbcUtils.getConnection();
//获取PrepareStatement对象
preparedStatement =connection.prepareStatement("select * from users limit ?,?");
//绑定m参数 m=（当前页-1）*每页的总条数
preparedStatement.setInt(1,(page.getCurrentPage()-1)*(page.getPageSize()));
//绑定n参数 n = (每页总条数)
preparedStatement.setInt(2,page.getPageSize());
//执行查询
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
//实现ORM映射
Users users=new Users();
users.setUserid(resultSet.getInt("userid"));
users.setUserName(resultSet.getString("userName"));
users.setUserAge(resultSet.getInt("userAge"));
//将Users对象放入容器
list.add(users);
}
//将结果集赋值给Page对象中的结果集
page.setResult(list);
//获取总条数
preparedStatement=connection.prepareStatement("select count(*) from users");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
//总条数
int totalCount = resultSet.getInt(1);
//赋值总条数
page.setTotalCount(totalCount);
}
//获取总页数 总页数=总条数 / 每页的条数 （如果有余数，向上取整）
int totalPage = (int)Math.ceil(1.0*page.getTotalCount()/page.getPageSize());
//赋值总页数
page.setTotalPage(totalPage);
}catch (Exception e){
e.printStackTrace();
}finally {
JdbcUtils.closeResource(preparedStatement,connection,resultSet);
}
return page;
}
public static void main(String[] args) {
Page page=new Page();
PageTest pageTest=new PageTest();
page.setCurrentPage(2);
page.setPageSize(2);
Page<Users> usersPage = pageTest.selectPage(page);
System.out.println("当前页："+page.getCurrentPage());
System.out.println("总页数："+page.getTotalPage());
System.out.println("总条数："+page.getTotalCount());
System.out.println("每页条数："+page.getPageSize());
for(Users users:usersPage.getResult()){
System.out.println(users);
}
}
}
数据库连接池
数据库连接池简介
什么是数据库连接池
数据库连接池（Connection pooling）是程序启动时建立足够的数据库连接，并将这些连接组成一个连接池，由程序动态地对池中的连接进行申请，使用，释放。
它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。
不使用数据库连接池存在的问题
- 普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection加载到内存中，再验证用户名和密码，所以整个过程比较耗时。
- 需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
- 对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
JDBC数据库连接池的必要性
- **数据库连接池的基本思想：**为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
- 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
- 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
数据库连接池的优点
- 资源重用：由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。
- 更快的系统反应速度：数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间。
- 新的资源分配手段：对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置实现某一应用最大可用数据库连接数的限制避免某一应用独占所有的数据库资源.
- 统一的连接管理：避免数据库连接泄露在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。
常用的数据库连接池
- c3p0：是一个开源组织提供的数据库连接池，速度相对较慢，稳定性还可以。
- DBCP：是Apache提供的数据库连接池。速度相对c3p0较快，但自身存在bug。
- Druid：是阿里提供的数据库连接池，据说是集DBCP、c3p0优点于一身的数据库连接池，目前经常使用。
Druid连接池
Druid简介
Druid是阿里提供的数据库连接池，它结合了C3P0、DBCP等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况。
Druid使用步骤
-
导入druid-1.2.8.jar包到lib目录下，并引入到项目中
-
在src下创建一个druid.properties类型的文件，并写入
url = jdbc:mysql://localhost:3306/java?useSSL=false driverClassName = com.mysql.jdbc.Driver username = root password = java initialSize = 10 maxActive = 20druid配置信息：
配置 缺省值 说明 name 配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：“DataSource-” + System.identityHashCode(this) url 连接数据库的url。 username 连接数据库的用户名。 password 连接数据库的密码。 driverClassName 根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下) initialSize 0 初始化时建立物理连接的个数。 maxActive 8 最大连接池数量 minIdle 最小连接池数量 maxWait 获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。 poolPreparedStatements false 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。 maxOpenPreparedStatements -1 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100 validationQuery 用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。 testOnBorrow true 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 testOnReturn false 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能 testWhileIdle false 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 timeBetweenEvictionRunsMillis 有两个含义： 1) Destroy线程会检测连接的间隔时间2) testWhileIdle的判断依据，详细看testWhileIdle属性的说明 numTestsPerEvictionRun 不再使用，一个DruidDataSource只支持一个EvictionRun minEvictableIdleTimeMillis connectionInitSqls 物理连接初始化的时候执行的sql exceptionSorter 根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接 filters 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall proxyFilters 类型是List<com.alibaba.druid.filter.Filter>，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系 -
加载配置文件
-
获取连接池对象
-
通过连接池对象获取连接
通过数据库连接池获取连接
/**
* Druid连接池测试类
*/
public class DruidTest {
public static void main(String[] args) throws Exception {
//加载配置文件资源
InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
//获取Properties对象
Properties properties=new Properties();
//通过Properties对象的load方法加载资源
properties.load(is);
//连接数据库连接池
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//通过数据库连接池创建程序与数据库之间的连接(获取Connection对象)
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
封装Druid数据库连接池工具类
/**
* 封装Druid数据库连接池工具类
*/
public class JdbcDruidUtiles {
private static DataSource dataSource;
static{
try {
//通过类加载器加载配置文件
InputStream is = JdbcDruidUtiles.class.getClassLoader().getResourceAsStream("druid.properties");
//创建Properties对象
Properties properties=new Properties();
//通过Properties对象的load方法加载配置文件
properties.load(is);
//通过Druid数据库连接池获取数据库连接对象Con
dataSource=DruidDataSourceFactory.createDataSource(properties);
}catch (Exception e){
e.printStackTrace();
}
}
//获取数据库连接对象
public static Connection getConnection(){
Connection connection = null;
try {
connection=dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//关闭数据库连接对象
public static void closeConnection(Connection connection){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//提交事务
public static void commit(Connection connection){
try {
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
//事务回滚
public static void rollBack(Connection connection){
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭Statement对象
public static void closeStatement(Statement statement){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭ResultSet对象
public static void closeResultSet(ResultSet resultSet){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//DML操作关闭资源
public static void closeResource(Statement statement,Connection connection){
//先关闭Statement对象
closeStatement(statement);
//再关闭Connection对象
closeConnection(connection);
}
//查询操作关闭资源
public static void closeResource(Statement statement,Connection connection,ResultSet resultSet){
//关闭ResultSet对象
closeResultSet(resultSet);
closeResource(statement,connection);
}
}
