MySQL的一些用法（二）

一、JDBC

1、JDBC入门

JDBC的全称为：java database connectivity (java和 数据库的连接 )。

Sun公司为了简化、统一对数据库的操作，定义了一套java操作数据库的接口的规范，称之为JDBC。

2、JDBC的常用类和接口：JDBC有关的类，都在java.sql 和 javax.sql 包下。

3、JDBC快速入门：

注：jdbc:mysql://localhost:3306/user为固定格式   

       jdbc:mysql://数据库的ip地址:数据库的端口号/数据库名称

       root为登录数据库的用户名,

      123位登录数据库的密码。

public static void main(String[] args) throws Exception {

//1.注册驱动

DriverManager.registerDriver(new Driver());

//2.获取连接 

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/user", "root", "123");

//3.创建发送sql的statement对象

Statement statement = con.createStatement();

//4.发送sql到数据库执行

String sql = "select * from user";

ResultSet rs = statement.executeQuery(sql);

//5.遍历结果集

while(rs.next()){

String id = rs.getString("id");

String name = rs.getString("name");

int age = rs.getInt("age");

System.out.println(id+"==="+name+"==="+age);

}

//6.释放资源

rs.close();

statement.close();

con.close();

}

4、JDBC-API详解:

①DriverManager 驱动管理类

作用：Jdbc程序中的DriverManager用于加载驱动，并创建与数据库的链接。

A、注册一个JDBC驱动程序

Class.forName("com.mysql.jdbc.Driver");

B、建立数据库的连接

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/user", "root", "123");

C、URL介绍：指定一个具体的数据库

作用：URL用于标识数据库的位置，程序员通过URL地址告诉JDBC程序连接哪个数据库

语法格式：jdbc:mysql:// localhost:3306/数据库名?key=value

②Connection 数据库连接类

应用一：获得SQL的操作对象。

应用二：对数据库事务进行管理。

③Statement 详解

A、Statement.executeQuery(sql)

B、Statement.executeUpdate(sql)

C、Statement.execute(Sql)

例子：

public static void main(String[] args) throws Exception {

       //1.注册驱动

       // DriverManager.registerDriver(new Driver());

      Class.forName("com.mysql.jdbc.Driver");

      //2.获取连接

      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/user", "root", "123");

      //3.创建发送sql的statement对象

      Statement statement = con.createStatement();

     /*statement.execute(sql)方法 返回值是boolean类型

      * true :说明sql语句是select语句，只能通过getResultSet()方法来获取select语句的结果集

      * false:说明sql是非select语句，通过getUpdateCount()方法来获取sql影响的行数

      * */

     // String sql = "select * from user";

     String sql = "insert into user values('004','zhaoliu',26)";

    boolean flag = statement.execute(sql);

    if(flag){

         //当前执行的是select语句

         System.out.println("执行的是select语句");

         //获取select语句的结果集

         ResultSet rs = statement.getResultSet();

         while(rs.next()){

         String id = rs.getString("id");

         String name = rs.getString("name");

         int age = rs.getInt("age");

        System.out.println(id+"=="+name+"=="+age);

        }

        rs.close();

    }else{

       //执行的不是select语句

       System.out.println("执行的非select语句");

       int updateCount = statement.getUpdateCount();

       System.out.println("sql语句影响的行数是"+updateCount);

    }

       statement.close();

       con.close();

}

④Resultset 结果集

表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。

ResultSet 对象具有指向其当前数据行的光标。最初，光标被置于第一行之前。next 方法将光标移动到下一行；

因为该方法在ResultSet 对象没有下一行时返回false，所以可以在while循环中使用它来迭代结果集。

例子：

public static void main(String[] args) throws Exception {

//1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

//获取连接

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/user", "root", "123");

//创建statement对象

Statement statement = con.createStatement();

//发送并执行sql

String sql = "select * from user";

ResultSet rs = statement.executeQuery(sql);

//遍历

while(rs.next()){

String id = rs.getString("id");

String name = rs.getString(2);

int age = rs.getInt(3);

System.out.println(id+"+++"+name+"++++"+age);

}

//释放资源

rs.close();

statement.close();

con.close();

}

⑤资源释放

finally{

          //释放资源

          if(rs!=null){

            try {

            rs.close();

            } catch (SQLException e) {

               // TODO Auto-generated catch block

               e.printStackTrace();

            }

          }

          if(statement!=null){

            try {

               statement.close();

             } catch (SQLException e) {

               // TODO Auto-generated catch block

               e.printStackTrace();

              }

            }

              if(con!=null){

                 try {

                    con.close();

                  } catch (SQLException e) {

                     // TODO Auto-generated catch block

                     e.printStackTrace();

                   }

                }

}

5、总结：

①注册驱动

Class.forname(“com.mysql.jdbc.Driver”);

②获取连接

Connection con = DriverManager.getConnection(url,user,password);

③创建statement对象

Statement st = con.createStatement();

④发送sql并且执行

ResultSet rs = st.executeQuery();

⑤遍历结果集

While(rs.next()){

}

⑥释放资源 放在finally中

6、JDBC工具类抽取：

①获得连接的初步抽取

②获得连接第二次优化

③在src目录下创建jdbc.properties

jdbc.properties文件中的配置信息：

driverClass=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/user

user=root

password=123

④获得连接的最终优化版

public class JbdcUtils {

public static String driverClass="";

public static String url = "";

public static String user = "";

public static String password = "";

static{

//首先创建一个Properties对象

try {

Properties properties = new Properties();

//直接加载properties文件

properties.load(new FileInputStream("src/com/hxc/jdbc/jdbc.properties"));

//获取properties文件中的参数

 driverClass = properties.getProperty("driverClass");

 url = properties.getProperty("url");

 user = properties.getProperty("user");

 password = properties.getProperty("password");

 //1.注册驱动

Class.forName(driverClass);

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static Connection getConnection(){

Connection con = null;

try {

//2.获取连接

con = DriverManager.getConnection(url, user, password);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return con;

}

public static void release(ResultSet rs,Statement st,Connection con){

if(rs!=null){

try {

rs.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if(st!=null){

try {

st.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if(con!=null){

try {

con.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

⑤关闭资源

7、sql注入问题：

①问题：

sql注入：由于没有对用户输入进行充分检查，而SQL又是拼接而成，在用户输入参数时，在参数中添加一些SQL 关键字，达到改变SQL运行结果的目的，也可以完成恶意攻击。

②PreparedStatement解决sql注入

③原理：PreparedStatement 解决SQL注入原理，运行在SQL中参数以?占位符的方式表示。

④PreparedStatement优点：

相对于Statement对象而言：

A、PreperedStatement可以避免SQL注入的问题。

B、Statement对象每次执行SQL语句时，都会对其进行编译。当相同的SQL语句被执行被执行多次时，Statement对象就会使数据库频繁编译相同的SQL语句，从而降低数据库的效率。

PreparedStatement对象可对SQL语句进行预编译。也就是说，当相同的SQL语句再次执行时，数据库只需使用缓冲区中的数据，而不需要对SQL语句在次编译，从而提高访问效率。

C、并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。可读性变强。

二、数据库连接池和DButils

1、连接池：

①自定义连接池

数据库连接池的实现步骤：

A、我们自定义的数据库连接池需要实现java.sql.DataSource这个接口。这是sun公司规定。

备注:   DriverManager 是JDBC 1.0规范,可以创建连接,但是不支持连接池

DataSrouce是JDBC 2.0规范,支持连接池

B、我们使用linkedList来存放连接。当做连接池。

C、在dataSource的构造方法中初始化数据库连接池。向池子中创建一定数量的数据库连接对象。

D、重写DataSource这个接口中的getConnection方法。注意：这个方法是从我们数据库连接池中获取数据，从连接池中获取之后需要将池子中的对象给删掉

E、提供释放资源的方法。注意：这个释放资源是将连接放回连接池中，而不是关闭连接。

②常用开源连接池：

A、Apache DBCP

B、C3P0

2、DBUtils 框架：DAO 层代码书写： 封装的过程很繁琐, 可以使用DBUtils工具类完成 DAO 的封装。

A、QueryRunner 框架核心类 ，所有数据库操作都是必须通过 QueryRunner 进行的

B、ResultSetHandler 结果集封装接口，完成将ResultSet 结果集 封装为一个Java对象

C、DbUtils 工具类 提供驱动管理、事务管理、释放资源等一系列公共方法

QueryRunner 与 ResultSetHandler 一起使用，完成数据表增删改查。

ResultSetHandler 的实现类使用：

说明 : 结果集处理类, 将从数据库中获取的数据进行封装处理, 从Dao层返回数据给 Service 层。

ResultSetHandler 在DBUtils 框架中提供九个默认 实现类，直接使用九个默认实现类，可以完成常规操作，而不需要自定义结果集封装

1) ArrayHandler 和 ArrayListHandler  将数据表的每行记录保存Object[] 中；

2) BeanHandler 和 BeanListHandler 将数据表每行记录 保存JavaBean对象中；

* 封装javabean属性时，必须保证数据表列名与 javabean属性名一致，否则无法封装；

3) MapHandler和 MapListHandler 将结果每行记录保存到一个Map集合，key是列名，value是值；

4) ColumnListHandler 查询结果集中指定一列数据；

5) KeyedHandler(name) 结果集每行数据封装map，再将map存入另一个map 作为value，指定一列作为key；

6) ScalarHandler 进行单值查询 select count(*) from account。

3、JavaEE 经典三层结构 ：web层、业务层和持久层(data access object)

作用：web层，获取浏览器提交的数据，或者将数据返回给浏览器；

           service层，专门用来处理业务逻辑的；

           dao层，专门和数据库打交道，用来获取数据库中的数据，或者将数据保存到数据库中。

三、jdbc事务

1、概述：

事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败.

事务作用：保证在一个事务中多次操作要么全都成功,要么全都失败.

2、mysql事务操作：

sql语句	描述
start transaction;	开启事务
commit;	提交事务
rollback;	回滚事务

A、MYSQL中可以有两种方式进行事务的管理：

      自动提交：MySql默认自动提交。及执行一条sql语句提交一次事务。

      手动提交：先开启，再提交

扩展：Oracle数据库事务不自动提交。

3、JDBC事务操作：

Connection对象的方法名	描述
conn.setAutoCommit(false)	开启事务
conn.commit()	提交事务
conn.rollback()	回滚事务

注意：在jdbc事务操作中，事务的控制都是通过Connection对象完成的，当一个完整的业务操作前，我们首先使用connection.setAutoCommit(false)来开启事务，当业务操作完成之后，我们需要使用connection.commit()来提交事务。当然了，如果出现了异常，我们需要撤销所有的操作，所以出现异常，需要进行事务的回滚。

4、DBUtils事务操作：

Connection对象的方法名	描述
conn.setAutoCommit(false)	开启事务
new QueryRunner()	创建核心类，不设置数据源(手动管理连接)
query(conn , sql , handler, params )  或 update(conn, sql , params)	手动传递连接
DbUtils.commitAndCloseQuietly(conn)  或 DbUtils.rollbackAndCloseQuietly(conn)	提交并关闭连接 回滚并关闭连接

注意：过程与JDBC操作一样，只不过需要注意的是，connection必须手动控制，不能交给DBUtils去控制。

5、事务的特性 ACID：

数据库的事务必须具备ACID特性，ACID是指 Atomic（原子性）、Consistensy（一致性）、Isolation（隔离型）和Durability（持久性）的英文缩写。

A、原子性（Atomicity）

      事务包装的一组sql，要么都执行成功，要么都失败。这些操作是不可分割的。

B、一致性（Consistency）

      数据库的数据状态是一致的。

      事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性。

C、持久性：（Durability）

      事务成功提交之后，对于数据库的改变是永久的。哪怕数据库发生异常，重启之后数据亦然存在。

D、隔离性（Isolation）

      一个事务的成功或者失败对于其他的事务是没有影响。2个事务应该相互独立。

①事务的隔离级别：

如果不考虑事务的隔离性，由于事务的并发，将会出现以下问题：

a、脏读 -- 最严重，杜绝发生：指一个事务读取了另外一个事务 未提交的数据

b、不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同，是读取的已提交数据

c、幻读（虚读）：虚读和不可重复读的区别：

虚读 强调的是数据表 记录数 的变化，主要是 insert 和 delete 语句。

不可重复读 强调的是数据表 内容 的变化，主要是 update 语句。

②隔离级别：解决问题

数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

read uncommitted 读未提交，一个事务读到另一个事务没有提交的数据。

read committed 读已提交，一个事务读到另一个事务已经提交的数据。

repeatable read（）: 可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。

serializable 串行化，同时只能执行一个事务，相当于事务中的单线程。

a、安全和性能对比

安全性：serializable > repeatable read > read committed > read uncommitted

性能 ： serializable < repeatable read < read committed < read uncommitted

b、常见数据库的默认隔离级别：

MySql：repeatable read

Oracle：read committed

serializable 串行化：可以避免所有的问题。数据库执行这个事务，其他事务必须等待当前事务执行完毕，才能被执行。