数据库和JDBC编程

JDBC是一种数据库连接，它是一种可执行SQL语句的Java API。程序可以通过JDBC API连接到关系型数据库，并使用结构化查询语言来完成对数据库的查询，更新。通过使用JDBC，就可以使用同一种API访问不同的数据库系统，开发人员面向JDBC API编写应用程序，然后根据不同的数据库，安装不同数据库的驱动程序即可。数据库驱动程序是JDBC程序和数据库之间的转换层，数据库驱动程序负责将JDBC调用映射成特定的数据库调用。当需要连接某个特定的数据库时，必须有相应的数据库驱动程序。JDBC驱动的常见类型：直接与数据库实例交互。这种驱动是智能的，它知道数据库使用的底层协议。这种驱动避开了本地代码，减少了应用开发的复杂性，也减少了产生冲突和出错的可能性。

程序可以使用JDBC API以统一的方式来连接不同的数据库，然后通过Statement对象来执行标准的SQL语句，并可以获得SQL语句访问数据库的结果。通过使用JDBC，java程序可以非常方便的操作各种主流数据库，由于java语言的跨平台性，可以使得JDBC编写的程序不仅可以实现跨数据库，还可以垮平台，具有很好的移植性。

SQL是一种结构化查询语言，是操作和检索关系数据库的标准语言。DML指数据库操作语言，主要由insert 、update 和delete三个关键字完成。DDL是指数据定义语言，操作数据库对象的语句，主要由create、alter、drop和Truncate四个关键字完成，最基本的数据库对象是数据表，数据表表示存储数据的逻辑单元。DCL指数据控制语言，主要由grant和revoke两个关键字完成，DCL语句为数据库用户授权，或收回指定用户权限。

Mysql数据库一般的约束类型：not null；UNIQUE约束（当建立唯一约束时，Mysql在唯一约束所在列或列组合上建立对应的唯一索引）。Primary key:主键约束相当于非空约束和唯一约束的结合，每一个表中最多允许有一个主键，但这个主键约束可由多个数据列组合而成。

FOREIGN KEY约束：主要用于保证一个或者两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。当主表的记录被从表记录参照时，主表记录不允许被删除。必须先把从表里所有参照该记录的所有记录全部删除后，才可以删除主表的记录。建立外键约束时，Mysql也会为该列建立索引。外建约束通常用于定义两个实体之间的一对一和一对多的关系。如果要使MySql中的外键约束生效，则应该使用表级约束语法。如果需要显示地指定外键约束的名字，则可以使用constraint来指定名字。

如果想定义删除主表记录时，从表记录也会随之删除，则需要建立外键约束后添加 on delete cascade 或添加on delete set null，第一种是指删除主表记录时，参照主表记录的从表记录全部级联删除。第二种是指定当删除主表记录时，把参照该主表记录的从表记录设为null

值得指出的是，外键约束不仅可以参照其他表，而且可以参照自身，这种参照自身的情况通常被称为自关联。

索引：索引是存放在模式（schema）中的一个数据库对象，虽然索引总是从属于数据表，但它也和数据表一样属于数据库对象。创建索引的唯一作用就是加快对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少磁盘的I/O;

创建索引的方法：

（1） 自动：当在表上定义主键约束，唯一约束和外检约束时，自动创建索引；

（2  Create index…语句来创建索引。Create index index_name on

Table_name(column);

（3)Drop index来删除索引；

     数据库维护索引需要一定的系统开销,因此创建索引的时候要特别注意，一般对需要经常查询的表字段创建索引。

注意：delete from语句可以一次删除多行，采用where子句来限定。

Select 查询时，后面不仅可以是数据列，也可以是表达式，还可以是变量，常量等。对于Mysql而言，如果算术表达式中出现null，将会导致整个算术表达式的返回值为null；因此可以使用如下方法，将为null的记录设为0，来进行算术运算：

Select avg(ifnull(java_teacher,0)) fromstudent_table;

注意：

   如果需要对分组进行过滤，则应该使用having子句。Having子句和where有些区别:

   不能在where子句中过滤组，where子句仅用于过滤行。过滤组必须用having子句；

   不能在where子句中使用函数，having子句才可以使用函数。

如下语句：

       Select* from student_table group by java_teacher having count(*)>2;

外连接： 所谓外连接就是在连接条件的列名后增加括号包起来的外连接符，当外连接符出现在左边时称为左外连接，出现在右边时称为右外连接。外连接就是在外连接符所在的表中增加一个“万能行”，这行记录的所有数据都是null，而且该行可以与另一个表中不满足条件的记录进行匹配，通过这种方式就可以把另一个表中的所有记录选出来，不管这些记录是否满足连接条件。

所谓自连接就是把一个表当成两个表来用，这就需要为一个表起两个别名，而且查询中用的所有数据列都要加表别名前缀，因为两个表的数据列完全一样。

左，右，全外连接：这三种分别使用left join，right join和full join，这三种外连接的连接条件一样通过on 子句来指定，既可以是等值连接条件，也可以是非等值连接条件。Sql99左外连接将会把左边表中所有不满足连接条件的记录全部列出，右外连接会把右边不满足连接条件的记录全部列出。全外连接会把两个表中所有不满足连接条件的记录全部列出。

   JDBC的典型用法

   DriverManager：用于管理JDBC的驱动服务类，通过使用该类来获取Connection 对象。Connection ：代表数据库连接对象，每个Connection代表一个物理连结会话。Statement：用于执行SQL语句的工具接口。该对象可以执行DDL和DML语句。PreparedStatement ：预编译的Statement对象。该方法返回预编译的Statement对象，即将SQL语句提交到数据库进行预编译。它允许数据库预编译SQL语句，以后每次改变SQL命定的参数，避免数据库每次都要编译SQL语句，因此性能更好。相对statement而言，它执行SQL语句时，无需再传入sql语句，只要为预编译的SQL语句传入参数即可。ResultSet：结果集对象。

JDBC编程的一般步骤：

（1）   加载数据库驱动

Class.forName(driverClass)

Class.forName(“com.mysql.jdbc.Driver”);//加载mysql的驱动代码：

Class.forName(“oracle.jdbc.driver.oracleDriver”);//加载oracle的驱动代码

(2) 通过DriverManager获取数据库连接。

DriverManager.getConnection(String url,String user,String pass);//MySql的URL写法如下：jdbc:mysql://hostname:port/databasename;

使用PreparedStatement相对于Statement来说，除了能提高执行效率外，还有一个优势，当SQL语句中要使用参数时，无需拼接sql字符串，使用PreparedStatement则只需要使用问号占位符来替代这些参数即可，降低了编程的复杂度，另外还有一个好处---用于防止SQL注入。

比如如下validate（）方法使用PreparedStatement来执行验证：

Private Boolean validate(String username,String userpass){
try{

Connection conn=DriverManager.getConnection(url,user,pass);

PreparedStatement pstmt=conn.prepareStatement(

“select * from jdbc_test where jdbc_name=?and jdbc_desc=?”))

{

  psmt.setString(1,username);

  psmt.setString(2,password);

   try(

       ResultSet rs=pstmt.executeUpdate())

{

     if(rs.next()){

        return true;

      }

}

   Catch(Exceptione){

     e.printStackTrace();

}

   Returnfalse;

}

Blob:是二进制长对象的意思，通常用于存储大文件。典型的Blob内容是一张图片或者声音文件。

可以使用CachedRowSet实现分页。

Mysql中的事物处理：

事物是由一步或几步数据库操作序列所组成的逻辑执行单元，这些系列操作要么全部执行，要么全部放弃，一般而言，一段程序中可能包含多个事务。对于任何数据库而言，事物都是非常重要的，事务是保证底层数据完整的主要手段，没有事物支持的数据库应用，那将非常脆弱。

事物具备四个特性：

原子性：事物是程序中最小的执行单元，不可再分。

一致性：事物执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。

隔离性：各个事物的执行互不干扰，任意一个事物的内部操作对其它并发的事务都是隔离的。

持续性：指事务一旦提交，对数据所做的任何改变都要记录到永久存储器中。通常就是保存进物理数据库中；

这几个特性简称为ACID性。

Mysql默认关闭事务（即打开自动提交），为了开启Mysql事务，可以显示调用如下命定：set AUTOCOMMIT={0|1} ,0为关闭自动提交。自动提交和开启事务正好相反，如果开启自动提交就是关闭事务，关闭自动提交就是开启事务。

提交，不管是显示提交还是隐示提交，都会结束当前事务；回滚，不管是显示回滚还是隐式回滚，都会结束当前事务。

可以调用Connection提供的setAutoCommit（）方法来关闭自动提交，开启事务。如：conn.setAutoCommit(false);

如果所有的sql语句都执行成功，程序可以调用conn.commit()语法来提交事务。如果任意一条sql语句执行失败，则通过 conn.rollback()方法来回滚事务。

大部分时候，只需要对指定的数据表进行插入（C），查询（R），修改（U），删除（D）等CRUD操作。在sql里的模式字符串，用百分号（%）表示任意多个字符，使用下划线（_）代表一个字符。

使用连接池管理连接：

数据库的连接的建立和关闭是很消耗系统资源的操作，频繁的打开关闭连接将导致系统性能低下。数据库连接池的解决方案是：当应用程序启动时，系统自动建立足够的数据库连接，并将这些连接组成一个连接池，每次应用程序请求数据库连接时，无须重新打开连接，而是从连接池中取出已有的连接使用，使用完后不再关闭数据库连接，而是直接将连接归还给连接池，这样可以极大的提高程序的运行效率。

在多个连接池程序里，相比之下，C3P0的性能更好，它不仅可以自动清理不再使用的Connection，还可以自动清理Statement和ResultSet对象。

（1）C3P0连接池的操作实例如下：

  publicclass C3P0Test{

    public static void main(String[] args) {

        Connection conn=null;

        PreparedStatement pstm=null;

        ResultSet rs=null;

        ComboPooledDataSource cpds=new ComboPooledDataSource();

        try{

            conn=cpds.getConnection();

            pstm=conn.prepareStatement("select * from account wherename=?");

            pstm.setString(1, "a");

            rs=pstm.executeQuery();

            rs.next();

            System.out.println(rs.getDouble("money"));

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JDBCUtils.close(conn, pstm,rs);

        }

    }

}

（2）JDBC还提供了一个批量更新的功能，使用批量更新时，多条sql语句将被作为一批操作被同时收集，并同时提交。如下实例：

 public class StatementBatchTest {

    publicstaticvoid main(String[] args) {

        Connection conn=null;

        Statement stat=null;

        ResultSet rs=null;

        try{

            conn=JDBCUtils.getConnection();

            stat=conn.createStatement();

            stat.addBatch("create databasedb_batch");

            stat.addBatch("use db_batch");

            stat.addBatch("create table tb_batch"+"(id int primary key  auto_increment, name      varchar(20))");

            stat.addBatch("insert into tb_batchvalues(null,'a')");

            stat.addBatch("insert into tb_batchvalues(null,'b')");

            stat.addBatch("insert into tb_batchvalues(null,'c')");

            stat.addBatch("insert into tb_batchvalues(null,'d')");

            stat.executeBatch();

            System.out.println("执行成功");

        }catch(Exception e){

            e.printStackTrace();

        }finally{

            JDBCUtils.close(conn, stat,rs);

        }

    }

}

（3）在preparedStatement中进行批量更新的例子如下：

   publicclass PreparedStatementBatch {

     public static void main(String[] args) {

        Connection conn = null;

        PreparedStatement ps = null;

        ResultSet rs = null;

        try {

//          conn =JDBCUtils.getConnection();

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

            conn = DriverManager.getConnection("jdbc:mysql:///db_batch","root", "root");

            ps = conn.prepareStatement("insert into tb_batch values(null, ?)");

            for(int i=0; i<10000; i++){

                ps.setString(1, "name"+i);

                ps.addBatch();

            }

            ps.executeBatch();

            System.out.println("执行成功~!");

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            if(rs != null){

                try {

                    rs.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }finally{

                    rs = null;

                }

            }

            if(ps != null){

                try {

                    ps.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }finally{

                    ps = null;

                }

            }

            if(conn != null){

                try {

                    conn.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }finally{

                    conn = null;

                }

            }

        }

    }

}

（4）翻页是JDBC中的常用技术，本代码中利用执行计划处理分页，数据库的连接和关闭用已经另外一个类DBUtil实现，代码如下：

public class PageDemo {

   public static void main(String[] args) {

       PageDemo demo = new PageDemo();

       demo.printPage(5, 3);

    }

   public void printPage(int pageSize,int page){

       int begin = (page - 1)*pageSize + 1;

       int end = page*pageSize ;

       String query = "select * from dept";

       String sql = "select * from (select a.*, rownum rn from ("+query+")a where rownum<= ?) where rn>= ?";

       System.out.println(sql);

       Connection conn = null;

       try {

           conn = DBUtil.getConnection();

           PreparedStatement ps = conn.prepareStatement(sql);

           ps.setInt(1, end);

           ps.setInt(2, begin);

           ResultSet rs = ps.executeQuery();

           while(rs.next()){

               System.out.println(rs.getString("dname"));

           }

           rs.close();

           ps.close();

       }catch(Exception e){

           e.printStackTrace();

       }finally{

           DBUtil.close(conn);

       }

    }

}