JDBC笔记
一、连接数据库
方式一：
//创建驱动，获取Driver的实现类对象
Driver driver=new com.mysql.jdbc.Driver();
//jdbc:mysql:协议
//localhost：ip地址
//3306：端口号
//test:数据库
String url="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";
//将用户名和密码封装在info中
Properties info=new Properties();
info.setProperty("user","root");
info.setProperty("password","abc123");
Connection connect = driver.connect(url, info);
方式二：
方式一的迭代:使得在以下程序中看不到第三放jar包，使得程序具有更好的移植性
//1.创建驱动，获取Driver的实现类对象，使用反射
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2.提供要连接的数据库
String url="jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
info.setProperty("user","root");
info.setProperty("password","abc123");
Connection connect = driver.connect(url, info);
方式三：
使用DriverManager代替Driver
//1.创建驱动
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver =(Driver) clazz.newInstance();
//注册驱动
DriverManager.deregisterDriver(driver);
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="abc123";
//获取链接
Connection connection = DriverManager.getConnection(url, user, password);
方式四：
优化，因为在加载Driver类时，就会创建Driver对象
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/test";
String user="root";
String password="abc123";
//获取链接
Connection connection = DriverManager.getConnection(url, user, password);
/**
* 可省略以下步骤：
Driver driver =(Driver) clazz.newInstance();
//注册驱动
DriverManager.deregisterDriver(driver);
因为：在com.mysql.jdbc.Driver中执行了以下代码
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
*/
方式五：final版
将信息放在文件中，通过流，将内容读取到代码中
//读取配置文件中的4个基本属性
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//获取驱动
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
说明：
因为通过JDBC连接数据库的方法是通用的，所以通常将其定义成静态方法封装在JDBCUtils类中，需要使用时，通过类名直接调用
同样的还有资源关闭
public class JDBCUtils {
/**
* 获取数据库的连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
//读取配置文件中的4个基本属性
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//获取驱动
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 关闭资源
* @param connection
* @param statement
*/
public static void closeResource(Connection connection, Statement statement){
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 关闭资源
* @param connection
* @param statement
*/
public static void closeResource(Connection connection, Statement statement, ResultSet resultSet){
if (statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet!=null){
try {
resultSet.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
二、对数据库进行操作
1.使用statement
问题1：存在拼串操作，繁琐
问题2：存在SQL注入，危险
使用Statement的弊端：需要拼写SQL语句，并且存在SQL注入的问题，
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '
// ='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
+ "'";
所以使用 PreparedStatement代替statement
Statement和PreparedStatement有什么区别？哪个性能更好？
答：与Statement相比，
①PreparedStatement接口代表预编译的语句，它主要的优势在于可以减少SQL的编译错误并增加SQL的安全性（减少SQL注射攻击的可能性）；
②PreparedStatement中的SQL语句是可以带参数的，避免了用字符串连接拼接SQL语句的麻烦和不安全；
③当批量处理SQL或频繁执行相同的查询时，PreparedStatement有明显的性能上的优势，由于数据库可以将编译优化后的SQL语句缓存起来，下次执行相同结构的语句时就会很快（不用再次编译和生成执行计划）。
2.使用PreparedStatement对数据库进行增删改查操作
2.1对数据库进行增删改操作
因为增删改没有返回值，所以将其归为一类，将查询归为另一类
增删改操作区别为SQL语句不同，所以以修改为例
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//连接数据库
connection = JDBCUtils.getConnection();
//预编译SQL
String sql="UPDATE customers SET NAME=? WHERE NAME=?";
preparedStatement = connection.prepareStatement(sql);
//填充占位符
preparedStatement.setString(1,"胡歌");
preparedStatement.setString(2,"霍建华");
//操作
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//资源关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
}
通用的增删改操作
/**
* 通用的增删改操作
*/
public void currencyUpdate(String sql,Object...args){//传入SQL语句，以及填充的占位符的内容
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//获取连接
connection = JDBCUtils.getConnection();
//预编译SQL
preparedStatement = connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i <args.length ; i++) {
preparedStatement.setObject(i+1,args[i]);
}
//执行操作
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(connection,preparedStatement);
}
}
2.2对数据库进行查询操作
通用查询,返回一条记录，将查询的结果赋值给相对应的一个Java对象
一条记录是个对象，是一个结果集，所以需要用到resultSet，使用完之后需要资源关闭
/**
* 通用查询,返回一条记录
* @param sql
* @param args
* @return
*/
public <T> T currencyQuery(Class<T> clazz,String sql,Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
//获取结果集
resultSet = preparedStatement.executeQuery();
//获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取列数
int columnCount = metaData.getColumnCount();
if (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columValue = resultSet.getObject(i + 1);
//获取列名：getColumnName：不推荐属性
//获取别名：getColumnLabel
// String columnName = metaData.getColumnName(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射，获取对应的字段名
Field field = clazz.getDeclaredField(columnLabel);
//如果属性是私有的则无法操作，所以将其改为可操作的
field.setAccessible(true);
//将数据库中的值，赋值给对应类的对应属性
field.set(t,columValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//资源关闭
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
通用查询,返回多条记录，将查询的结果赋值给相对应的多个Java对象
/**
* 通用查询，返回多条记录
* @param sql
* @param args
* @return
*/
public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
//获取结果集
resultSet = preparedStatement.executeQuery();
//获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取列数
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columValue = resultSet.getObject(i + 1);
//获取列名：getColumnName：不推荐属性
//获取别名：getColumnLabel
//String columnName = metaData.getColumnName(i + 1);
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射，获取对应的字段名
Field field = clazz.getDeclaredField(columnLabel);
//如果属性是私有的则无法操作，所以将其改为可操作的
field.setAccessible(true);
//将数据库中的值，赋值给对应类的对应属性
field.set(t,columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
//资源关闭
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
2.3 ORM编程思想
- ORM编程思想：（object relational mapping,对象关系映射）
- 一张数据表对应一个Java类
- 一条记录对应一个Java对象
3.对Blob的操作
插入一张图片到数据库表的字段中
/**
* 向数据表customers插入一条Blob字段
*/
@Test
public void testInsert(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql="insert into customers(name,email,birth,photo) values(?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
String name="晓晓";
String email="xx@162.com";
String birth="1998-01-02";
//使用文件输入流，将文件读取到程序中
FileInputStream photo = new FileInputStream(new File("1.jpg"));
preparedStatement.setString(1, name);
preparedStatement.setString(2, email);
preparedStatement.setString(3, birth);
//使用setBlob()将其填充到数据库中
preparedStatement.setBlob(4, photo);
int i = preparedStatement.executeUpdate();
if (i>0){
System.out.println("插入成功，影响"+i+"行");
}else {
System.out.println("插入失败");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
查询一条Blob数据，并将其存储在相应位置
/**
* 查询Blob
*/
@Test
public void testQuery(){
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
InputStream is= null;
FileOutputStream fos= null;
try {
connection = JDBCUtils.getConnection();
String sql="select id,name,email,birth,photo from customers where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,22);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
Date birth = resultSet.getDate("birth");
//获取Blob字段的信息
Blob photo = resultSet.getBlob("photo");
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
//使用photo.getBinaryStream()将其转换成流
is = photo.getBinaryStream();
//新建一个输出文件流
fos = new FileOutputStream("xiaoxiao.jpg");
//操作
byte[] buf = new byte[1024];
int len=0;
while ((len=is.read(buf))!=-1){
fos.write(buf,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
if (fos!=null){
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (is!=null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
4.使用PreparedStatement进行批量操作
4.1方式一：使用Statement
因为Statement操作批量数据太慢，且不能防止SQL注入，所以一般都是用PreparedStatement
4.2方式二：直接使用PreparedStatement
数据太多也会很慢，但是可以防SQL注入
/**
* 方式二：使用preparedStatement代替statement
*/
@Test
public void test(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
long start = System.currentTimeMillis();
connection = JDBCUtils.getConnection();
String sql="insert into goods(name) values (?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 20000;i++) {
preparedStatement.setString(1,"name_"+i);
preparedStatement.execute();
}
long end = System.currentTimeMillis();
System.out.println(end-start);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
4.3方式三：使用preparedStatement.addBatch();
攒SQL，等SQL语句到达一定数量时，再进行操作
/**
* 方式三：使用攒sql
*/
@Test
public void test1(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
long strat = System.currentTimeMillis();
String sql="insert into goods(name) values (?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 20000; i++) {
preparedStatement.setString(1,"name_"+i);
//1.攒
preparedStatement.addBatch();
if (i%100==0){
//达到条件执行
preparedStatement.executeBatch();
//清空
preparedStatement.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println(end-strat);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
4.4方式四：设置不提交：connection.setAutoCommit(false);
每次执行SQL都会提交一次，影响效率
等所有语句都执行完成后，再一起提交，数据越多节省的时间也就越多
/**
* 批量插入的方式四：设置不提交：connection.setAutoCommit(false);
*/
@Test
public void test2(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
long strat = System.currentTimeMillis();
//设置不提交，等所有操作完成之后，再进行提交
connection.setAutoCommit(false);
String sql="insert into goods(name) values (?)";
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <=100000; i++) {
preparedStatement.setString(1,"name_"+i);
preparedStatement.addBatch();
if (i%200==0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
}
}
connection.setAutoCommit(true);
long end = System.currentTimeMillis();
System.out.println(end-strat);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement);
}
}
三、事务
1.事务的ACID
事务的ACID是指什么？
原子性(Atomic)：事务中各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败；
一致性(Consistent)：事务结束后系统状态是一致的；
隔离性(Isolated)：并发执行的事务彼此无法看到对方的中间状态；
持久性(Durable)：事务完成后所做的改动都会被持久化，即使发生灾难性的失败。通过日志和同步备份可以在故障发生后重建数据。
2.数据库的隔离级别
1.对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
脏读: 对于两个事物T1, T2, T1读取了已经被T2更新但还没有被提交的字段。之后, 若T2回滚,T1读取的内容就是临时且无效的。
不可重复读: 对于两个事物 T1, T2, T1 读取了一个字段, 然后T2更新了该字段.之后, T1再次读取同一个字段,值就不同了。
幻读: 对于两个事物T1, T2, T1 从一个表中读取了一个字段,然后 T2在该表中插入了一些新的行。之后, 如果T1再次读取同一个表,就会多出几行.
2.数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响, 避免各种并发问题。
3.一个事务与其他事务隔离的程度称为隔离级别.数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高, 数据一致性就越好, 但并发性越弱。
4.数据库提供的4种事务隔离级别:READ UNCOMMITED（读已提交）、READ COMMITED（读已提交）、REPEATABLE READ（可重复读）、SERIALIZABLE（串行化）
Oracle 支持的2 种事务隔离级别：READ COMMITED（读已提交）, SERIALIZABLE（串行化）。 Oracle默认的事务隔离级别为: READ COMMITED。
MySQL支持 4 中事务隔离级别，MySQL默认的事务隔离级别为: REPEATABLE READ（可重复读）。
5.在MySQL设置隔离级别
每启动一个 MySQL程序,就会获得一个单独的数据库连接。每个数据库连接都有一个全局变量 @@tx_isolation ，表示当前的事务隔离级别。 MySQL默认的隔离级别为 REPEATABLE READ（可重复读）
查看当前的隔离级别: SELECT@@tx_isolation;
设置当前 MySQL连接的隔离级别: set transaction isolation level read committed;
设置数据库系统的全局的隔离级别: set global transaction isolation levelread committed;
6.隔离级别测试
public void testTransactionIsolationRead() {
String sql = "SELECT balance FROM users WHERE id = 1";
Integer balance = getForValue(sql);
System.out.println(balance);
}
public <E> E getForValue(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
//获取默认的隔离级别：connection.getTransactionIsolation()==4(TRANSACTION_REPEATABLE_READ)
//connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);//读未提交
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);//读已提交
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
// 1. 得到结果集
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
// 2. 取得结果
return (E) resultSet.getObject(1);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.closeResource(connection, null, preparedStatement, resultSet);
}
return null;
}
四、DAO(data access object 数据访问对象)
DAO的好处
1.隔离业务逻辑代码和数据访问代码
2.隔离不同数据库的实现的作用
DAO的组成
-
TableDAO接口 把对数据库的所有操作定义为抽象方法
-
TableDAOImpl实现类 不同数据库给出的DAO接口定义方法的具体实现
-
实体类 用于存放和传输对象数据
-
数据库工具类数据库连接和关闭工具
避免了数据库连接和关闭代码的重复
定义一个TableDAO接口，此接口针对指定的数据库表，定义常用操作（增删改查）的抽象方法
定义一个TableDAOImpl实现类,继承BaseDAO抽象类，实现TableDAO接口
定义一个实体类，此类用于接收返回的数据
定义一个工具类JDBCUtils,用于封装获取数据库连接，关闭资源
五、数据库连接池
C3P0
Druid(常用)：
//连接池创建一个就可以，所以定义为static
private static InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
public static Connection getDruidConnection() throws Exception {
//读取配置文件
Properties properties = new Properties();
properties.load(is);
//连接数据库
DataSource source = DruidDataSourceFactory.createDataSource(properties);
//返回连接
Connection connection = source.getConnection();
return connection;
}
六、Apache-connect-dbutils
commons-dbutils 是Apache组织提供的开源的JDBC类库，封装了正对数据库的增删改查操作
dbutils测试
@Test//测试插入
public void testInsert(){
Connection connection = null;
try {
QueryRunner queryRunner = new QueryRunner();
connection = JDBCUtils.getDruidConnection();
String sql="insert into customers(name,email,birth) values(?,?,?)";
int i = queryRunner.update(connection, sql, "范闲", "fanxian@qq.com", "1888-01-02");
System.out.println("插入了"+i+"条数据");
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test//测试查询，情况一，返回一个对象
public void testQuery1() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getDruidConnection();
String sql="select id,name,email,birth from customers where id=?";
//根据query参数ResultSetHandler(结果集)来确定第三个参数
//BeanHandler返回的是一个对象
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
Customer customer = runner.query(connection, sql, handler, 21);
System.out.println(customer);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test//测试查询，情况二，返回多个对象的集合
public void testQuery2() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getDruidConnection();
String sql = "select id,name,email,birth from customers where id<?";
//根据query参数ResultSetHandler(结果集)来确定第三个参数
//BeanListHandler返回的是多个对象的集合
BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
List<Customer> customers = runner.query(connection, sql, listHandler, 6);
customers.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, null);
}
}
@Test//测试查询，情况三，将字段及相应字段的值作为map中的key和value，返回一个map,
public void testQuery3() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getDruidConnection();
String sql="select id,name,email,birth from customers where id=?";
//根据query参数ResultSetHandler(结果集)来确定第三个参数
//MapHandler返回的是一个对象
MapHandler handler = new MapHandler();
Map<String, Object> map = runner.query(connection, sql, handler, 21);
System.out.println(map);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
@Test//测试查询，情况四，查询特殊值
public void testQuery4() {
Connection connection = null;
try {
QueryRunner runner = new QueryRunner();
connection = JDBCUtils.getDruidConnection();
String sql="select count(*) from customers";
//根据query参数ResultSetHandler(结果集)来确定第三个参数
//ScalarHandler返回的是一个特殊值
ScalarHandler handler = new ScalarHandler();
Long i=(Long)runner.query(connection, sql, handler);
System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,null);
}
}
扫一扫
3304
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
