jdbc

我就是我 2022-06-11 06:58 162阅读 0赞

1．Jbdc

1.1      DriverManager

**java.sql  
类 DriverManager**

java.lang.Object

**java.sql.DriverManager**

public class **DriverManager**

extends Object

管理一组 JDBC 驱动程序的基本服务。  
**注：**DataSource 接口是 JDBC2.0 API 中的新增内容，它提供了连接到数据源的另一种方法。使用 DataSource 对象是连接到数据源的首选方法。

作为初始化的一部分，DriverManager 类会尝试加载在"jdbc.drivers" 系统属性中引用的驱动程序类。这允许用户定制由他们的应用程序使用的 JDBC Driver。例如，在 ~/.hotjava/properties 文件中，用户可以指定：

jdbc.drivers=foo.bah.Driver:wombat.sql.Driver:bad.taste.ourDriver

DriverManager 类的方法 getConnection 和 getDrivers 已经得到提高以支持 Java Standard Edition ServiceProvider 机制。 JDBC 4.0 Drivers 必须包括 META-INF/services/java.sql.Driver 文件。此文件包含java.sql.Driver 的 JDBC 驱动程序实现的名称。例如，要加载 my.sql.Driver 类，META-INF/services/java.sql.Driver文件需要包含下面的条目：

my.sql.Driver

应用程序不再需要使用 Class.forName() 显式地加载JDBC 驱动程序。当前使用 Class.forName() 加载 JDBC 驱动程序的现有程序将在不作修改的情况下继续工作。

在调用 getConnection 方法时，DriverManager会试着从初始化时加载的那些驱动程序以及使用与当前 applet 或应用程序相同的类加载器显式加载的那些驱动程序中查找合适的驱动程序。

从 Java 2 SDK 标准版本 1.3 版开始，只有当已授予适当权限时设置日志流。通常这将使用工具 PolicyTool 完成，该工具可用于授予 permissionjava.sql.SQLPermission "setLog" 权限。

**另请参见：**

Driver,  Connection

1.2      Example

1.2.1    使用DriverManger的registerDriver方法

DriverManager

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>static&nbsp;void</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">registerDriver</a></strong><code>(</code><a rel="nofollow">Driver</a><code>&nbsp;driver)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;向 <code>DriverManager</code> 注册给定驱动程序。</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>static&nbsp;</code><a rel="nofollow">Connection</a></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">getConnection</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;url, </code><a rel="nofollow">String</a><code>&nbsp;user, </code> <a rel="nofollow">String</a><code>&nbsp;password)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;试图建立到给定数据库 URL 的连接。</p> </td> 
  </tr> 
 </tbody> 
</table>

Connection

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;</code><a rel="nofollow">Statement</a></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">createStatement</a></strong><code>()</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;创建一个 <code>Statement</code> 对象来将 SQL 语句发送到数据库。</p> </td> 
  </tr> 
 </tbody> 
</table>

Statement

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;</code><a rel="nofollow">ResultSet</a></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">executeQuery</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;sql)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;执行给定的 SQL 语句，该语句返回单个 <code>ResultSet</code> 对象。</p> </td> 
  </tr> 
 </tbody> 
</table>

RusultSet

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;boolean</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">next</a></strong><code>()</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;将光标从当前位置向前移一行。</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;double</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">getDouble</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;columnLabel)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;以 Java 编程语言中 <code>double</code> 的形式获取此 <code>ResultSet</code> 对象的当前行中指定列的值。</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;int</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">getInt</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;columnLabel)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;以 Java 编程语言中 <code>int</code> 的形式获取此 <code>ResultSet</code> 对象的当前行中指定列的值。</p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;</code><a rel="nofollow">String</a></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">getString</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;columnLabel)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;以 Java 编程语言中 <code>String</code> 的形式获取此 <code>ResultSet</code> 对象的当前行中指定列的值。</p> </td> 
  </tr> 
 </tbody> 
</table>

public void jdbc1() throws Exception{
    
          //1.向DriverManager注册给定驱动程序
    
          DriverManager.registerDriver(new Driver());
    
          //2.建立到给定数据库的url链接
    
          //在xml中设置编码需要&
    
          String url="jdbc:mysql://127.0.0.1:3306/example?useUnicode=true&characterEncoding=utf8";
    
          //String url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
          String username="root";
    
          String password="";
    
          Connection connection=DriverManager.getConnection(url, username, password);
    
          //3.创建向数据库发送sql语句的Statement对象
    
          Statement statement=connection.createStatement();
    
          //4.执行指定的sql语句
    
          String sql="select *from example";
    
          ResultSet resultset=statement.executeQuery(sql);
    
          //5.处理执行的结果
    
          while(resultset.next()){
    
          System.out.println(resultset.getInt("arg0")+resultset.getDouble("arg1")+resultset.getString("arg2"));
    
          }
    
          //6.关闭资源
    
          resultset.close();
    
          statement.close();
    
          connection.close();
    
       }

1.2.2    使用Class类的forName初始化类的方法，也就是创建类的对象

Class

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>static&nbsp;</code><a rel="nofollow">Class</a><code>&lt;?&gt;</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">forName</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;className)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;返回与带有给定字符串名的类或接口相关联的 <code>Class</code> 对象。</p> </td> 
  </tr> 
 </tbody> 
</table>

*/
    package com.mysql.jdbc;
    
    import java.sql.SQLException;
    
    /**
     * The Java SQL framework allows for multiple database drivers. Each driver
     * should supply a class that implements the Driver interface
     * 
     * <p>
     * The DriverManager will try to load as many drivers as it can find and then
     * for any given connection request, it will ask each driver in turn to try to
     * connect to the target URL.
     * 
     * <p>
     * It is strongly recommended that each Driver class should be small and
     * standalone so that the Driver class can be loaded and queried without
     * bringing in vast quantities of supporting code.
     * 
     * <p>
     * When a Driver class is loaded, it should create an instance of itself and
     * register it with the DriverManager. This means that a user can load and
     * register a driver by doing Class.forName("foo.bah.Driver")
     * 
     * @see org.gjt.mm.mysql.Connection
     * @see java.sql.Driver
     * @author Mark Matthews
     * @version $Id: Driver.java 3726 2005-05-19 15:52:24Z mmatthews $
     */
    public class Driver extends NonRegisteringDriver implements java.sql.Driver {
            // ~ Static fields/initializers
            // ---------------------------------------------
    
            //
            // Register ourselves with the DriverManager
            //
            static {
                   try {
                           java.sql.DriverManager.registerDriver(new Driver());
                   } catch (SQLException E) {
                           throw new RuntimeException("Can't register driver!");
                   }
            }
    
            // ~ Constructors
            // -----------------------------------------------------------
    
            /**
             * Construct a new driver and register it with DriverManager
             * 
             * @throws SQLException
             *             if a database error occurs.
             */
            public Driver() throws SQLException {
                   // Required for Class.forName().newInstance()
            }
    }

public void jdbc1() throws Exception{
    
          //1.向DriverManager注册给定驱动程序
    
          //DriverManager.registerDriver(new Driver());
    
           Class.forName("com.mysql.jdbc.Driver");
    
          //2.建立到给定数据库的url链接
    
          //在xml中设置编码需要&
    
          String url="jdbc:mysql://127.0.0.1:3306/example?useUnicode=true&characterEncoding=utf8";
    
          //String url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
          String username="root";
    
          String password="";
    
          Connection connection=DriverManager.getConnection(url, username, password);
    
          //3.创建向数据库发送sql语句的Statement对象
    
          Statement statement=connection.createStatement();
    
          //4.执行指定的sql语句
    
          String sql="select *from example";
    
          ResultSet resultset=statement.executeQuery(sql);
    
          //5.处理执行的结果
    
          while(resultset.next()){
    
          System.out.println(resultset.getInt("arg0")+resultset.getDouble("arg1")+resultset.getString("arg2"));
    
          }
    
          //6.关闭资源
    
          resultset.close();
    
          statement.close();
    
          connection.close();
    
       }

1.2.3    其他sql操作，改，增，删。

Statement

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;int</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">executeUpdate</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;sql)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;执行给定 SQL 语句，该语句可能为 <code>INSERT</code>、<code>UPDATE</code> 或 <code>DELETE</code> 语句，或者不返回任何内容的 SQL 语句（如 SQL DDL 语句）。</p> </td> 
  </tr> 
 </tbody> 
</table>

update   insert     delete

public void jdbcUpdate() throws Exception{
    
          //1.向DriverManager注册给定驱动程序
    
          DriverManager.registerDriver(new Driver());
    
          //Class.forName("com.mysql.jdbc.Driver");
    
          //2.建立到给定数据库的url链接
    
          //在xml中设置编码需要&
    
          String url="jdbc:mysql://127.0.0.1:3306/example?useUnicode=true&characterEncoding=utf8";
    
          //String url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
          String username="root";
    
          String password="";
    
          Connection connection=DriverManager.getConnection(url, username, password);
    
          //3.创建向数据库发送sql语句的Statement对象
    
          Statement statement=connection.createStatement();
    
          //4.执行指定的sql语句
    
          //Stringsql="delete from example where id=2";
    
          //String sql="insert into examplevalues (null,'hhh',12.2)";
    
          String sql="updateexample set name='sssssss' where id=1";
    
          int resultset=statement.executeUpdate(sql);
    
          //5.处理执行的结果
    
          System.out.println("成功执行了"+resultset+"条sql语句");
    
          //6.关闭资源
    
          statement.close();
    
          connection.close();
    
       }

1.2.4    ResultSet使用execute方法综合处理sql语句

ResultSet

<table> 
 <tbody> 
  <tr> 
   <td style="background-color:#FFFFFF;"> <p><code>&nbsp;boolean</code></p> </td> 
   <td style="background-color:#FFFFFF;"> <p><strong><a rel="nofollow">execute</a></strong><code>(</code><a rel="nofollow">String</a><code>&nbsp;sql)</code><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;执行给定的 SQL 语句，该语句可能返回多个结果。</p> </td> 
  </tr> 
 </tbody> 
</table>

public void jdbcExexute() throws Exception{
    
          //1.向DriverManager注册给定驱动程序
    
          DriverManager.registerDriver(new Driver());
    
          //Class.forName("com.mysql.jdbc.Driver");
    
          //2.建立到给定数据库的url链接
    
          //在xml中设置编码需要&
    
          String url="jdbc:mysql://127.0.0.1:3306/example?useUnicode=true&characterEncoding=utf8";
    
          //String url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
          String username="root";
    
          String password="";
    
          Connection connection=DriverManager.getConnection(url, username, password);
    
          //3.创建向数据库发送sql语句的Statement对象
    
          Statement statement=connection.createStatement();
    
          //4.执行指定的sql语句
    
          String sql="select *from example";
    
          //String sql="insert into examplevalues (null,'hhh',12.2)";
    
          //String sql="delete from examplewhere id=2";
    
          //String sql="update example setname='aaaa' where id=1";
    
          boolean flag=statement.execute(sql);
    
          //5.处理执行的结果
    
          ResultSet resultset=null;
    
          if(flag){
    
             resultset=statement.executeQuery(sql);
    
             while(resultset.next()){
    
                System.out.println(resultset.getString("name"));
    
             }
    
          }else{
    
             int columns=statement.executeUpdate(sql);
    
             System.out.println("成功执行了"+columns+"条sql语句");
    
          }
    
         
    
          //6.关闭资源
    
          if(resultset!=null){
    
             resultset.close();
    
          }
    
          statement.close();
    
          connection.close();
    
       }

1.2.5    捕捉异常版jdbc流程（相对以上流程，更适合分层设计模式，因为抛异常的处理方式在该类被调用时，将需要多米诺抛出）

public void jdbcTryCatch() throws ClassNotFoundException{
    
          //1.向DriverManager注册给定驱动程序
    
          //DriverManager.registerDriver(new Driver());
    
          Class.forName("com.mysql.jdbc.Driver");
    
          //2.建立到给定数据库的url链接
    
          //在xml中设置编码需要&
    
          String url="jdbc:mysql://127.0.0.1:3306/example?useUnicode=true&characterEncoding=utf8";
    
          //String url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
          String username="root";
    
          String password="";
    
          Connection connection=null;
    
          Statement statement=null;
    
          ResultSet resultset=null;
    
          try{
    
             connection=DriverManager.getConnection(url, username, password);
    
             //3.创建向数据库发送sql语句的Statement对象
    
             statement=connection.createStatement();
    
             //4.执行指定的sql语句
    
             String sql="select *from example";
    
             resultset=statement.executeQuery(sql);
    
             //5.处理执行的结果
    
             while(resultset.next()){
    
             System.out.println(resultset.getInt("id")+resultset.getString("name")+resultset.getDouble("price"));
    
             }
    
          }catch(Exception e){
    
             e.printStackTrace();
    
          }finally{
    
             //6.关闭资源
    
             try{
    
                if(resultset!=null){
    
                    resultset.close();
    
                }
    
                resultset=null;
    
             }catch(Exception e){
    
                e.printStackTrace();
    
             }
    
             try {
    
                if(statement!=null){
    
                    statement.close();
    
                }
    
                statement=null;
    
             } catch (Exception e) {
    
                e.printStackTrace();
    
             }
    
             try {
    
                if(connection!=null){
    
                    connection.close();
    
                }
    
                connection=null;
    
             } catch (Exception e) {
    
                e.printStackTrace();
    
             }
    
          }
    
       }

2   JdbcUtils

2.1 没有利用配置文件的JdbcUtils

public class JdbcUtils {
    
       static String url;
    
       static String username;
    
       static String password;
    
       static{
    
          try {
    
             Class.forName("com.mysql.jdbc.Driver");
    
          } catch (ClassNotFoundException e) {
    
             // TODO Auto-generated catchblock
    
             e.printStackTrace();
    
          }
    
          url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
          username="root";
    
          password="";
    
       }
    
       public static Connection getConnection()throws SQLException{
    
          Connection connection=DriverManager.getConnection(url,username,password);
    
          return connection;
    
       }
    
       public static void release(Connection connection,Statement statement,ResultSet resultset){
    
          try{
    
             if(resultset!=null){
    
                resultset.close();
    
             }
    
             resultset=null;
    
          }catch(Exception e){
    
             e.printStackTrace();
    
          }
    
          release(connection,statement);
    
       }
    
       public static void release(Connection connection,Statement statement){
    
          try {
    
             if(statement!=null){
    
                statement.close();
    
             }
    
             statement=null;
    
          } catch (Exception e) {
    
             e.printStackTrace();
    
          }
    
          try {
    
             if(connection!=null){
    
                connection.close();
    
             }
    
             connection=null;
    
          } catch (Exception e) {
    
             e.printStackTrace();
    
          }
    
       }
    
    }

2.2     使用配置文件的JdbcUtils

JdbcUtils.properties

driverClass=com.mysql.jdbc.Driver
    
    url=jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8
    
    username=root
    
    password=

public class JdbcUtils {
    
       static String url;
    
       static String username;
    
       static String password;
    
       static{
    
          Properties properties=new Properties();
    
          InputStream inputstream=JdbcUtils.class.getClassLoader().getResourceAsStream("JdbcUtils.properties");
    
          try {
    
             properties.load(inputstream);
    
          } catch (IOException e) {
    
             e.printStackTrace();
    
          }
    
          try {
    
             Class.forName(properties.getProperty("driverClass"));
    
          } catch (ClassNotFoundException e) {
    
             e.printStackTrace();
    
          }
    
          url=properties.getProperty("url");
    
          username=properties.getProperty("username");
    
          password=properties.getProperty("password");
    
    //    url="jdbc:mysql://localhost:3306/example?useUnicode=true&characterEncoding=utf8";
    
    //    username="root";
    
    //    password="";
    
       }
    
       public static Connection getConnection()throws SQLException{
    
          Connection connection=DriverManager.getConnection(url,username,password);
    
          return connection;
    
       }
    
       public static void release(Connection connection,Statement statement,ResultSet resultset){
    
          try{
    
             if(resultset!=null){
    
                resultset.close();
    
             }
    
             resultset=null;
    
          }catch(Exception e){
    
             e.printStackTrace();
    
          }
    
          release(connection,statement);
    
       }
    
       public static void release(Connection connection,Statement statement){
    
          try {
    
             if(statement!=null){
    
                statement.close();
    
             }
    
             statement=null;
    
          } catch (Exception e) {
    
             e.printStackTrace();
    
          }
    
          try {
    
             if(connection!=null){
    
                connection.close();
    
             }
    
             connection=null;
    
          } catch (Exception e) {
    
             e.printStackTrace();
    
          }
    
       }
    
    }

2.3     测试JdbcUtils

public void jdbcTestUtils(){
    
          Connection connection=null;
    
          Statement statement=null;
    
          ResultSet resultset=null;
    
          try {
    
             //1.利用JdbcUtils工具类获得连接对象
    
             connection = JdbcUtils.getConnection();
    
             //2.创建向数据库发送sql语句的Statement对象
    
             statement=connection.createStatement();
    
             //3.执行指定的sql语句
    
             String sql="select *from example";
    
             //String sql="insertinto example values (null,'hhh',12.2)";
    
             //String sql="deletefrom example where id=2";
    
             //String sql="updateexample set name='aaaa' where id=1";
    
             boolean flag=statement.execute(sql);
    
             //4.处理执行的结果
    
             resultset=null;
    
             if(flag){
    
                resultset=statement.executeQuery(sql);
    
                while(resultset.next()){
    
                    System.out.println(resultset.getString("name"));
    
                }
    
             }else{
    
                int columns=statement.executeUpdate(sql);
    
                System.out.println("成功执行了"+columns+"条sql语句");
    
             }
    
          } catch (SQLException e) {
    
             // TODO Auto-generated catchblock
    
             e.printStackTrace();
    
          }finally{
    
             //6.关闭资源
    
             JdbcUtils.release(connection, statement, resultset);
    
          } 
    
       }