jdbc复习笔记

JDBC:java Data Base Connectivity,java 数据库连接

应用程序--->JDBC( 定义接口规范)---> 驱动( 实现接口规范)--->MySQL 数据库

涉及到数据库的操作首先要引入第三方驱动包

 

操作基本步骤：

   

注册驱动：

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

 ( 推荐此方式注册驱动)

   

建立连接：

Connection conn = DriverManager
     .getConnection("jdbc:mysql://localhost:3306/test","root","123456");

 

创建语句：

Statement stm=conn.createStatement();

    
执行语句： 
ResultSet rs=stm.executeQuery("select * from user");

    

处理结果： 

 

    释放资源：反序关闭连接，一定要关闭连接。

 

注册细节补充：

    注册方式二：

       

注册方式三： 

        

    注册驱动的实质就是把实现了java.sql.Driver 接口的类加载进jvm ，java 应用程序调用jdbc 接口时，会动态地去调用这些驱动，通过这些驱动获取数据库资源。

 

 

   

SQL 注入：

    拼串：' or 1 or '

    使用PreparedStatement 预处理解决，除此之外，它的效率远高于Statement ，特别是执行多次SQL 操作时。效率问题会因版本、数据库的不同存在差异，只是大多数数据库ps 要高于stm 。说明ps 继承自stm ，特别要注意在使用ps 执行sql 时，只能是ps.execute(); 这种无参的形式。 否则它等于调用父类stm 的执行方法。

    通常建议使用PreparedStatement 接口  

   

数据类型：日期

    ps.getDate("birthday"); ---> 得到的是java.sql.Date 类型( 继承自java.util.Date) 。而且在ps.setDate(1,java.sql.Date)---> 如果传递过来参数是java.util.Date 则要进行new java.sql.Date(birthday.getTime()); 的操作，即ps.setDate(1,new java.sql.Date(birthday.getTime()));

区别：

    java.sql.Date: 纯日期       

    java.util.Date: 日期和时间  

 

数据类型：大文本串

    写操作：

   

 

ps = conn.prepareStatement(sql);
File f = new File("src/com/asm/jdbc/dataType/test/DateTest.java");
Reader reader = new BufferedReader(new FileReader(f));
ps.setCharacterStream(1, reader, f.length());
ps.executeUpdate();
reader.close();

 

    读操作：

while (rs.next()) {
   System.out.println(rs.getString(1)); //处理结果，打印到控制台。
   
   //下面的操作是把结果写到DateTest.java.bak文件中去。
   Clob clob = rs.getClob(1);
   Reader reader = clob.getCharacterStream();
   // Reader reader=rs.getCharacterStream(1); //等价于上面两句
   
   File f = new File("DateTest.java.bak");
   Writer writer = new BufferedWriter(new FileWriter(f));
   char[] buff = new char[1024];
   for (int i = 0; (i = reader.read(buff)) > 0;) {
    writer.write(buff, 0, i);
   }
   writer.close();
   reader.close();
} 

 

 

 

数据类型：二进制数据

    说明：如果存储的数据大于64kb ，那么我们应该把数据库中的blob 类型改为longblob 类型

    写操作： 基本和clob 相似，只是ps.setBinaryStream(1, is, f.length()); 中的第二个参数为InputStream ，所 以要构建出一个InputStream 对象。

    读操作： 基本和clob 相似，只是选取的是字节流进行操作。   

 

数据类型：常用数据类型总结

    BIT(1) 、BOOLEAN        -------       boolean

    BIT(>1)                -------       byte[]

    INTEGER 、INT           -------       int

    TINTYINT 、SMALLINT     -------       short

    BIGINT                 -------       long

    DECIMAL 、NUMERIC       -------       java.math.Bigdecimal

    CHAR 、VARCHAR          -------       string

    FLOAT 、DOUBLIE         -------       floate 、double    

    DATE                   -------       java.sql.Date

    DATETIME               -------       java.sql.Time        

    TIMESTAMP               -------       java.sql.Timestamp 

    BLOB/CLOB|TEXT/ARRAY   -------        java.sql.Blob/Clob/Array

 

如何解决jdbc 操作SQLExcettion 异常:

    最好的做法是利用SQLException 异常来构建出RuntimeException 异常，并向上抛。 

 

事务基本知识

    原子性(atomicity) ：组成事务处理的语句形式了一个逻辑单元，不能只执行其中的一部分。

    一致性(consistency) ：在事务处理执行前后，数据库是一致的( 数据库数据完整性约束) 。

    隔离性(isolocation) ：一个事务处理对另一个事务处理的影响。

    持续性(durability) ：事务处理的效果能够被永久保存下来。

    涉及事务的三个操作：打开事务、提交事务、回滚事务

    注意：事务跟引擎有关，有的引擎不支持事务。mySQL 支持事务时一般选择InnoDB 引擎

 

使用保存点，进行合理回滚  

 

跨越多个数据源的事务：JTA 容器实现事务( 了解)

 

事务的隔离级别          

    更新丢失：两个事务都同时更新一行数据，但是第二个事务却中途失败退出，导致对数据的两个修改都失效了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来

    脏读：一个事务读取到了另一个并行事务未提交的数据。

    不可重复读取：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。例如，在第二次读取前，有另外一个事务对该数据进行了修改，并提交。不可重复读特例：两次更新问题：无法重复读取的特例。有两并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。

    幻读（Phantom Reads ）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据（这里并不要求两次查询的SQL 语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。

    针对以上四种现象，提出了隔离级别来不同程度的解决上面的问题

        无事务权：不能解决上面的任何问题，TRANSACTION_NONE

        读未提交：解决“更新丢失”问题，TRANSACTION_READ_UNCOMMITTED

        读已提交：解决“读未提交+ 脏读”问题，TRANSACTION_READ_COMMITTED

        可重复读：解决“读已提交+ 不可重复读”问题，TRANSACTION_REPEATABLE_READ

        可序列化：解决“可重复读+ 幻读问题”问题，TRANSACTION_SERIALIZABLE

说明：可串行化的隔离级别最高，隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以采用“读已提交”+ 悲观锁、乐观锁进行控制。通常至少要使用“读未提交”以上的隔离级别，因为“更新丢失”问题必须解决。

 

使用存储过程：Stored Procedure

    存储过程是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数( 如果该存储过程带有参数) 来执行它。

    示例：

步骤一，创建存储过程：

DELIMITER $$

 

DROP PROCEDURE IF EXISTS `test`.`add_user` $$

CREATE PROCEDURE `test`.`add_user` (in pname varchar(45), in birthday date, in money float ,out pid int)

BEGIN

  insert into user (name,birthday,money) values (pname, birthday, money);

  select last_insert_id() into pid;

END $$

 

DELIMITER ;

步骤二，使用存储过程:

String sql="{call add_user(?,?,?,?)}";         

cs=conn.prepareCall(sql);

cs.setString(1, "richie");

cs.setDate(2, new java.sql.Date(System.currentTimeMillis()));

cs.setFloat(3,700.00f);        

cs.registerOutParameter(4, Types.INTEGER);// 注册输出参数

cs.executeUpdate();    

int id=cs.getInt(4);

System.out.println(id);

 

返回主键

    只需要创建PreparedStatement 时，多增加一个参数，即

    ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        ...    

     rs = ps.getGeneratedKeys()// 直接到得主键，处理后可以返回。

说明：mySql 不使用Statement.RETURN_GENERATED_KEYS ，也能直接getGenertedKeys ，但其它数据库不一定具有此功能。

 

使用批处理

    ps.addBatch(); // 加载进批处理包

    ps.executeBatch(); // 开始批处理

 

    stm.addBatch(sql) // 加载进批处理包

    ps.executeBatch(); // 开始批处理

说明：批处理尽管效率上有所提高，但是如果所执行的数据量过大，可能会造成内存溢出。

 

可滚动结果集与分页技术

    通过ResultsSet rs 中的相关方法可以定位查询结果，实现分页。比如：rs.absolute(); 等 

    一般，主流数据库都具有分页查询语句，所以利用可滚动结果集分页很少使用。

 

可更新的结果集：

　　创建stm/ps 时：如

ps=conn.prepareCall(sql,ResultSet.FETCH_FORWARD,ResultSet.CONCUR_UPDATABLE): ．．．　　while(rs.next()){ if("zhangsan".equals(rs.getString("name"))){ float money=rs.getFloat("money"); rs.updateFloat("money",money+10);　　//更新 rs.updateRow();　　　　　　　　　　　//保存更新生效．　　　 System.out.print("利用Result更新数据成功"); } }

 

说明：如果要使用rs 结果集更新，创建时ResultSet.CONCUR_UPDATABLE 类型一定要先在最后，否则会出错．

 

获取数据库的信息

　　Connection conn = JdbcUtil.getConnection();

　　DatabaseMetaData dbmd=(DatabaseMetaData) conn.getMetaData();

　　System.out.println(" 驱动名称:"+dbmd.getDriverName());

说明：DatabaseMetaData 可以详细的获知当前连接数据库的详细信息．包括版本号，驱动名，等等．　

应用：hibeante 等框架就利用了此信息判断数据库类型来进行细致的处理．

 

使用ParameterMetaData 获取元信息

    String sql = "Select * from user where id>? and id <?";       

    ps = conn.prepareStatement(sql);

    获取：

    ParameterMetaData pmd = ps.getParameterMetaData()

    使用：

        pmd.getParameterType(int i)
//i 表示第i 个参数，即是说为sql 语句中的第i 个参数

        pmd.getParameterTypeName(int i)

　　注意：使用mySql 时，不同版本可能对此信息的默认支持情况可能没．如果在运行时，出现“Parameter metadata not available for the given statement ”这样的情况，请在创建sql 连接时的uri 后面如此写：“jdbc:mysql://localhost:3306/DbName?generateSimpleParameterMetadata=true ”。

 

封装结果集

    问题：如果我们希望把查询结果返回给上层，该如何处理？由于ResultSet 是不能返回的，所有唯一的办法是对ResultSet 进行封装处理。下面介绍思想：

    一条记录总会有字段+ 字段值，我们可以把每条记录存在Map 对象中，形式为map.put(String 字段名，Object 字段值);

    如果查询结果只有一条记录，我们可以这个Map 对象返回。   

    如果查询结果不止一条记录，我们可以把所有记录封装成一个Map 对象，然后再把这些Map 对象添加到一个List 中，最后返回List 即可。

    ORM 初步：如果我们希望封装的对象是明确的，即是说我们希望返回的list 所装的对象是明确得。那么我们可以在查询时把我们希望list 封装的对象类型作为参数进行传递，这样我们可以根据传递的类型参数，利用反射技术构建这个对象，同样利用反射调用它的方法，主要是完成构建对象的setXXX 操作。然后再把构建出的对象添加到list 中去。

 

连接池思想：

    由于创建连接是一个非常耗时，耗资源的工作。所以我们希望在初始化服务器时，就创建好一定数量的连接，并把它们存在连接池中，当用户请求时，从连接池中取出连接供用户使用，当用户使用完毕，再把连接放到连接池中。这个连接池一般使用LinkedList<Connection> pools 存放连接。

    一个细节问题，当用户使用完连接，不再是关闭连接，而是把连接放回连接池中，这一点要求我们使用动态代理技术实现对连接的动态创建及对用户调用conn.close 方法进行拦截后再进行“放回连接池”的操作。

    上面只是简要概述连接池原理，实际开发中我们只需要使用已有的连接池技术、数据源即可。

    说明：标准数据源要都实现DateSource 接口，而且都会对close 方法进行拦截修改。数据源实质就是持有一个连接池对象，利用连接池对象快速创建连接，而且通过数据源取得的连接都经过一定的包装。

 

使用DBCP 实现连接池功能

    步骤一，导入所需的三个jar 包：commons-dbcp-1.2.2.jar 、commons-pool.jar 、commons-collections-3.1.jar

    步骤二，配置Properties 文件，当然也可以用创建的Properties 对象直接设置属性。

    步骤三，得到一个DataSource ds

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

Properties pro = new Properties();

InputStream is = JdbcUtilWithDBCP.class.getClassLoader().getResourceAsStream"dbcpconfig.properties");

pro.load(is);

ds = BasicDataSourceFactory.createDataSource(pro);

   步骤四，利用ds.getConnection() ，得到一个连接.

补充：Properties 文件的基本配置：

***********************************************************************************************************

# 连接设置

driverClassName=com.mysql.jdbc.Driver

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

username=root

password=123456

 

#<!-- 初始化连接 -->

initialSize=10

 

# 最大连接数量

maxActive=50

 

#<!-- 最大空闲连接 -->

maxIdle=20

 

#<!-- 最小空闲连接 -->

minIdle=5

 

#<!-- 超时等待时间以毫秒为单位 6000 毫秒/1000 等于60 秒 -->

maxWait=6000

 

 

#JDBC 驱动建立连接时附带的连接属性属性的格式必须为这样：[ 属性名=property;]

# 注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。

connectionProperties=useUnicode=true;characterEncoding=gbk

 

# 指定由连接池所创建的连接的自动提交（auto-commit ）状态。

defaultAutoCommit=true

 

#driver default 指定由连接池所创建的连接的只读（read-only ）状态。

# 如果没有设置该值，则“setReadOnly ”方法将不被调用。（某些驱动并不支持只读模式，如：Informix ）

defaultReadOnly=

 

#driver default 指定由连接池所创建的连接的事务隔离级别（TransactionIsolation ）。

# 可用值为下列之一：NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE

defaultTransactionIsolation=READ_UNCOMMITTED

***********************************************************************************************************   

template 优化CRUD 操作

CRUD 的操作，大致模式如下：

Connection conn = null;

PreparedStatement ps = null;

ResultSet rs = null;

try {

    conn = JdbcUtil.getConnection();

    ps = conn.prepareStatement(sql);

   

    系列set 方法

    ps.setXXX(XXX, XXX);   

 

    ps.executeUpdate();

        //ps.executeQuery();

}catch (SQLException e) {

    e.printStackTrace();

    throw new RuntimeException(e);

} finally {

    JdbcUtil.free(rs, ps, conn);

}

除了R 查询操作，CUD 操作中ps 最后执行的都基本是executeUpdate(); 从上面代码可以得知：

变一，sql 语句，变二，参数设置。

抽取固定不变的操作，写成一个方法如下：

protected int updateDelAdd(String sql, Object[] args) {

    Connection conn = null;

    PreparedStatement ps = null;

    ResultSet rs = null;

    try {

        conn = JdbcUtil.getConnection();

        ps = conn.prepareStatement(sql);

        for (int i = 0; i < args.length; i++) {

            ps.setObject(i, args[i]);

        }

        return ps.executeUpdate();

    } catch (SQLException e) {

        e.printStackTrace();

        throw new RuntimeException(e);

    } finally {

        JdbcUtil.free(rs, ps, conn);

    }

}

然后只有继续此方法所在的类，然后即可使用此方法。假定此方法所在的类为：AbstractBaseDao ，则可以：

class UserDao extends AbstractBaseDao{

    public void update(User user) {

        String sql = "update user set name=? , birthday=? , money=? where id=?";

        Object[] args = new Object[] { user.getName(), user.getBirthday(),

                user.getMoney(), user.getId() };

        int i = super.updateDelAdd(sql, args);

        System.out.println(" 更新 " + i + " 条记录成功");

    }

}

难点：R 查询操作

查询操作的一个重点对查询结果进行处理。下面以两种方式展现：

简单模板方式：