Jdbc基础

原创于 2022-08-26 13:45:05 发布 · 326 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #java

1.1 Jdbc

JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存

取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，

（java.sql,javax.sql）使用这些类库可以以一种标准的方法、方便地访问数据库资源。

JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。

JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，

这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过

程。

1.2 JDBC接口（API）包括两个层次：

面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语

句，获得结果）。

面向数据库的API：Java Driver API，供开发商开发数据库驱动程序用。

1.3URL

JDBC URL 用于标识一个被注册的驱动程序，驱动程序管理器通过这个 URL 选择正确的驱动程序，从而建立到数据库的连接。

jdbc:子协议:子名称

协议：JDBC URL中的协议总是jdbc

子协议：子协议用于标识一个数据库驱动程序

子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名。

1.4链接数据库的最常用方式

@Test
public void testConnection5() throws Exception {
//1.加载配置文件
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);

//2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");

//3.加载驱动
Class.forName(driverClass);

//4.获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);

}

配置文件：jdbc.properties

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

使用配置文件的好处：

①实现了代码和数据的分离，如果需要修改配置信息，直接在配置文件中修改，不需要深入代码 ②如果修改了配置信息，省去重新编译的过程。

1.5使用Statement操作数据表的弊端

问题一：存在拼串操作，繁琐。

问题二：存在SQL注入问题。

1.6PreparedStatement

PreparedStatement介绍

可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象

PreparedStatement 接口是 Statement 的子接口，它表示一条预编译过的 SQL 语句

PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示，调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数，第一个参数

1.7 sql与java类型转换

1.8使用PreparedStatement实现增、删、改操作 public void update(String sql,Object ... args){

       Connection conn = null;
       PreparedStatement ps = null;
       try {
           //1.获取数据库的连接
           conn = JDBCUtils.getConnection();

           //2.获取PreparedStatement的实例 (或：预编译sql语句)
           ps = conn.prepareStatement(sql);
           //3.填充占位符
           for(int i = 0;i < args.length;i++){
               ps.setObject(i + 1, args[i]);
           }

           //4.执行sql语句
           ps.execute();
       } catch (Exception e) {

           e.printStackTrace();
       }finally{
           //5.关闭资源
           JDBCUtils.closeResource(conn, ps);

       }
   }

1.9使用PreparedStatement实现查询操作

public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

       Connection conn = null;
       PreparedStatement ps = null;
       ResultSet rs = null;
       try {
           // 1.获取数据库连接
           conn = JDBCUtils.getConnection();

// 2.预编译sql语句，得到PreparedStatement对象
ps = conn.prepareStatement(sql);

           // 3.填充占位符
           for (int i = 0; i < args.length; i++) {
               ps.setObject(i + 1, args[i]);
           }

// 4.执行executeQuery(),得到结果集：ResultSet
rs = ps.executeQuery();

// 5.得到结果集的元数据：ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();

           // 6.1通过ResultSetMetaData得到columnCount,columnLabel；通过ResultSet得到列值
           int columnCount = rsmd.getColumnCount();
           if (rs.next()) {
               T t = clazz.newInstance();
               for (int i = 0; i < columnCount; i++) {// 遍历每一个列

                   // 获取列值
                   Object columnVal = rs.getObject(i + 1);
                   // 获取列的别名:列的别名，使用类的属性名充当
                   String columnLabel = rsmd.getColumnLabel(i + 1);
                   // 6.2使用反射，给对象的相应属性赋值
                   Field field = clazz.getDeclaredField(columnLabel);
                   field.setAccessible(true);
                   field.set(t, columnVal);

}

return t;

}
} catch (Exception e) {

           e.printStackTrace();
       } finally {
           // 7.关闭资源
           JDBCUtils.closeResource(conn, ps, rs);
       }

return null;

}

1.10资源的释放

释放ResultSet, Statement,Connection。

数据库连接（Connection）是非常稀有的资源，用完后必须马上释放，如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建，尽量早的释放。

可以在finally中关闭，保证及时其他代码出现异常，资源也一定能被关闭。

1.11小结

两种思想

面向接口编程的思想ORM思想(object relational mapping)，表中的一个字段对应java类的一个属性，表中的一条记录对应java类的一个对象一个数据，表对应一个java类。

sql是需要结合列名和表的属性名来写。注意起别名。

两种技术

JDBC结果集的元数据：ResultSetMetaData

获取列的别名：getColumnLabel()

获取列数：getColumnCount()

通过反射，创建指定类的对象，获取指定的属性并赋值

1.12Blob类型

1.13向数据表中插入大数据类型

Connection conn = JDBCUtils.getConnection();

String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);

// 填充占位符
ps.setString(1, "徐海强");
ps.setString(2, "xhq@126.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
//执行
ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);

1.14从数据表中读取大数据类型

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if(rs.next()){
   Integer id = rs.getInt(1);
String name = rs.getString(2);
   String email = rs.getString(3);
Date birth = rs.getDate(4);
   Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
//读取Blob类型的字段
   Blob photo = rs.getBlob(5);
   InputStream is = photo.getBinaryStream();
   OutputStream os = new FileOutputStream("c.jpg");
   byte [] buffer = new byte[1024];
   int len = 0;
   while((len = is.read(buffer)) != -1){
       os.write(buffer, 0, len);
   }
JDBCUtils.closeResource(conn, ps, rs);

   if(is != null){
       is.close();
   }

   if(os != null){
       os.close();
   }

}

1.15批量插入

使用Connection 的 setAutoCommit(false) / commit()

@Test
public void testInsert2() throws Exception{
   long start = System.currentTimeMillis();

   Connection conn = JDBCUtils.getConnection();

   //1.设置为不自动提交数据
   conn.setAutoCommit(false);

   String sql = "insert into goods(name)values(?)";
   PreparedStatement ps = conn.prepareStatement(sql);

   for(int i = 1;i <= 1000000;i++){
       ps.setString(1, "name_" + i);

       //1.“攒”sql
       ps.addBatch();

       if(i % 500 == 0){
           //2.执行
           ps.executeBatch();
           //3.清空
           ps.clearBatch();
       }
   }

   //2.提交数据
   conn.commit();

   long end = System.currentTimeMillis();
   System.out.println("花费的时间为：" + (end - start));//1000000条:4978

   JDBCUtils.closeResource(conn, ps);
}

1.16事务

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。

当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。

关闭数据库连接，数据就会自动的提交。如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

查看当前的隔离级别:SELECT @@tx_isolation;

设置当前 mySQL 连接的隔离级别: set transaction isolation level read committed;

设置数据库系统的全局的隔离级别: set global transaction isolation level read committed;

创建mysql数据库用户：create user tom identified by 'abc123';

#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
grant all privileges on *.* to tom@'%' identified by 'abc123';

#给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';

1.17DAO

DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO

BaseDAO.java

public abstract class BaseDao<T> {
   private QueryRunner queryRunner = new QueryRunner();
   // 定义一个变量来接收泛型的类型
   private Class<T> type;

   // 获取T的Class对象，获取泛型的类型，泛型是在被子类继承时才确定
   public BaseDao() {
       // 获取子类的类型
       Class clazz = this.getClass();
       // 获取父类的类型
       // getGenericSuperclass()用来获取当前类的父类的类型
       // ParameterizedType表示的是带泛型的类型
       ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
       // 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
       // 这个方法会返回一个Type的数组
       Type[] types = parameterizedType.getActualTypeArguments();
       // 获取具体的泛型的类型·
       this.type = (Class<T>) types[0];
   }

   /**
   * 通用的增删改操作
   *
   * @param sql
   * @param params
   * @return
   */
   public int update(Connection conn,String sql, Object... params) {
       int count = 0;
       try {
           count = queryRunner.update(conn, sql, params);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return count;
   }

   /**
   * 获取一个对象
   *
   * @param sql
   * @param params
   * @return
   */
   public T getBean(Connection conn,String sql, Object... params) {
       T t = null;
       try {
           t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return t;
   }

   /**
   * 获取所有对象
   *
   * @param sql
   * @param params
   * @return
   */
   public List<T> getBeanList(Connection conn,String sql, Object... params) {
       List<T> list = null;
       try {
           list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return list;
   }

   /**
   * 获取一个但一值得方法，专门用来执行像 select count(*)...这样的sql语句
   *
   * @param sql
   * @param params
   * @return
   */
   public Object getValue(Connection conn,String sql, Object... params) {
       Object count = null;
       try {
           // 调用queryRunner的query方法获取一个单一的值
           count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return count;
   }
}

BookDaoImpl.java

public class BookDaoImpl extends BaseDao<Book> implements BookDao {

   @Override
   public List<Book> getBooks(Connection conn) {
       // 调用BaseDao中得到一个List的方法
       List<Book> beanList = null;
       // 写sql语句
       String sql = "select id,title,author,price,sales,stock,img_path imgPath from books";
       beanList = getBeanList(conn,sql);
       return beanList;
   }

   @Override
   public void saveBook(Connection conn,Book book) {
       // 写sql语句
       String sql = "insert into books(title,author,price,sales,stock,img_path) values(?,?,?,?,?,?)";
       // 调用BaseDao中通用的增删改的方法
       update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(),book.getImgPath());
   }

   @Override
   public void deleteBookById(Connection conn,String bookId) {
       // 写sql语句
       String sql = "DELETE FROM books WHERE id = ?";
       // 调用BaseDao中通用增删改的方法
       update(conn,sql, bookId);

   }

   @Override
   public Book getBookById(Connection conn,String bookId) {
       // 调用BaseDao中获取一个对象的方法
       Book book = null;
       // 写sql语句
       String sql = "select id,title,author,price,sales,stock,img_path imgPath from books where id = ?";
       book = getBean(conn,sql, bookId);
       return book;
   }

   @Override
   public void updateBook(Connection conn,Book book) {
       // 写sql语句
       String sql = "update books set title = ? , author = ? , price = ? , sales = ? , stock = ? where id = ?";
       // 调用BaseDao中通用的增删改的方法
       update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getId());
   }

   @Override
   public Page<Book> getPageBooks(Connection conn,Page<Book> page) {
       // 获取数据库中图书的总记录数
       String sql = "select count(*) from books";
       // 调用BaseDao中获取一个单一值的方法
       long totalRecord = (long) getValue(conn,sql);
       // 将总记录数设置都page对象中
       page.setTotalRecord((int) totalRecord);

       // 获取当前页中的记录存放的List
       String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books limit ?,?";
       // 调用BaseDao中获取一个集合的方法
       List<Book> beanList = getBeanList(conn,sql2, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
       // 将这个List设置到page对象中
       page.setList(beanList);
       return page;
   }

   @Override
   public Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice) {
       // 获取数据库中图书的总记录数
       String sql = "select count(*) from books where price between ? and ?";
       // 调用BaseDao中获取一个单一值的方法
       long totalRecord = (long) getValue(conn,sql,minPrice,maxPrice);
       // 将总记录数设置都page对象中
       page.setTotalRecord((int) totalRecord);

       // 获取当前页中的记录存放的List
       String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books where price between ? and ? limit ?,?";
       // 调用BaseDao中获取一个集合的方法
       List<Book> beanList = getBeanList(conn,sql2, minPrice , maxPrice , (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
       // 将这个List设置到page对象中
       page.setList(beanList);

       return page;
   }

}

1.8数据库连接池

在使用开发基于数据库的web程序时，传统的模式基本是按以下步骤：　

在主程序（如servlet、beans）中建立数据库连接

进行sql操作

断开数据库连接

存在问题：

普通的JDBC数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个，执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用。若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。

对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。（回忆：何为Java的内存泄漏？）

这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃

优点：

1. 资源重用

由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

2. 更快的系统反应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间

3. 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

4. 统一的连接管理，避免数据库连接泄漏

在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

1.9Druid（德鲁伊）数据库连接池

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

package com.atguigu.druid;

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
   public static void main(String[] args) throws Exception {
       Properties pro = new Properties();       pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
       DataSource ds = DruidDataSourceFactory.createDataSource(pro);
       Connection conn = ds.getConnection();
       System.out.println(conn);
   }
}

配置文件

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall

1.20Apache-DBUtils实现CRUD操作

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。

测试

@Test
public void testInsert() throws Exception {
   QueryRunner runner = new QueryRunner();
   Connection conn = JDBCUtils.getConnection3();
   String sql = "insert into customers(name,email,birth)values(?,?,?)";
   int count = runner.update(conn, sql, "何成飞", "he@qq.com", "1992-09-08");

   System.out.println("添加了" + count + "条记录");

   JDBCUtils.closeResource(conn, null);

}

@Test
public void testDelete() throws Exception {
   QueryRunner runner = new QueryRunner();
   Connection conn = JDBCUtils.getConnection3();
   String sql = "delete from customers where id < ?";
   int count = runner.update(conn, sql,3);

   System.out.println("删除了" + count + "条记录");

   JDBCUtils.closeResource(conn, null);

}