JDBC-自定义数据库工具类（DBService）

 写在前面的话：

     （1）使用JDBC，必须要使用对应的jar包，该笔记中使用jar包：mysql-connector-java-5.1 .6-bin.jar

     （2）使用连接池，一定要导入对象的数据库连接池包，该笔记中使用jar包：c3p0-0.9.1.2.jar

               常用连接池：dbcp连接池、c3p0连接池、druid连接池

 

 

第一版：不用数据库连接池：

 

package cn;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class SqlService {
 
       // 四大金刚
      String driver = "com.mysql.jdbc.Driver" ;// 驱动名称
      String url = "jdbc:mysql:///mao" ;// 连接字符串
      String username = "root" ;// 用户名
      String password = "123456" ;// 密码
 
       // 三剑客
      Connection con = null ;// 连接对象
      PreparedStatement pstmt = null ;// 语句对象
      ResultSet rs = null ;// 结果集对象
 
       /**
       * 获得连接对象
       *
       * @return 连接对象
       * @throws ClassNotFoundException
       * @throws SQLException
       */
       public Connection getConnection() throws ClassNotFoundException,
                  SQLException {
            Class. forName( driver);
             con = DriverManager.getConnection( url , username , password );
             return con ;
      }
 
       /**
       * 关闭三剑客
       *
       * @throws SQLException
       */
       public void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
 
             try {
                   if (rs != null)
                        rs.close();
                   if (pstmt != null)
                        pstmt.close();
                   if (con != null)
                        con.close();
            } catch (SQLException e) {
                   // TODO: handle exception
                  e.printStackTrace();
            }
      }
 
       /**
       * 执行更新
       *
       * @param sql
       *            传入的预设的 sql语句
       * @param params
       *            问号参数列表
       * @return 影响行数
       */
       public int execUpdate(String sql, Object[] params) {
             try {
                   this .getConnection();// 获得连接对象
                   this .pstmt = this. con .prepareStatement(sql);// 获得预设语句对象
 
                   if (params != null) {
                         // 设置参数列表
                         for (int i = 0; i < params. length; i++) {
                               // 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
                               this .pstmt .setObject(i + 1, params[i] + "" );
                        }
                  }
 
                   return this .pstmt .executeUpdate(); // 执行更新，并返回影响行数
 
            } catch (ClassNotFoundException | SQLException e) {
                   // TODO Auto-generated catch block
                  e.printStackTrace();
            } finally {
                   this .close( this. rs, this. pstmt , this .con );
            }
             return 0;
      }
 
       /**
       * 执行查询
       *
       * @param sql
       *            传入的预设的 sql语句
       * @param params
       *            问号参数列表
       * @return 查询后的结果
       */
       public List<Map<String, Object>> execQuery(String sql, Object[] params) {
 
             try {
                   this .getConnection();// 获得连接对象
                   this .pstmt = this. con .prepareStatement(sql);// 获得预设语句对象
 
                   if (params != null) {
                         // 设置参数列表
                         for (int i = 0; i < params. length; i++) {
                               // 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
                               this .pstmt .setObject(i + 1, params[i] + "" );
                        }
                  }
 
                   // 执行查询
                  ResultSet rs = pstmt .executeQuery();
 
                  List<Map<String, Object>> al = new ArrayList<Map<String, Object>>();
 
                   // 获得结果集元数据（元数据就是描述数据的数据，比如把表的列类型列名等作为数据）
                  ResultSetMetaData rsmd = rs.getMetaData();
 
                   // 获得列的总数
                   int columnCount = rsmd.getColumnCount();
 
                   // 遍历结果集
                   while (rs.next()) {
                        Map<String, Object> hm = new HashMap<String, Object>();
                         for (int i = 0; i < columnCount; i++) {
                               // 根据列索引取得每一列的列名,索引从1开始
                              String columnName = rsmd.getColumnName(i + 1);
                               // 根据列名获得列值
                              Object columnValue = rs.getObject(columnName);
                               // 将列名作为key，列值作为值，放入 hm中，每个 hm相当于一条记录
                              hm.put(columnName, columnValue);
                        }
                         // 将每个 hm添加到al中, al相当于是整个表，每个 hm是里面的一条记录
                        al.add(hm);
                  }
 
                   return al;
 
            } catch (ClassNotFoundException | SQLException e) {
                   // TODO Auto-generated catch block
                  e.printStackTrace();
            } finally {
                   this .close( this. rs, this. pstmt , this .con );
            }
             return null ;
      }
}

代码解释：

一：为什么直接使用 “Class. forName( driver); ”注册驱动

          ①原始注册驱动写法：

   

  // 注册驱动：告诉 java 去连接哪种数据库
     //DriverManager ：驱动管理类，负责管理驱动，创建连接对象
     DriverManager. registerDriver( new com.mysql.jdbc.Driver());

static void

registerDriver ( Driver  driver)           向 DriverManager 注册给定驱动程序。

参数：Driver=====来哦子欲你要连接的数据库（如果连接orcale，来自于oracle，如果连接mysql，来自于mysql）

备注：查询 Drive源码

 

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!" );
       }
    }

可以看到： java.sql.DriverManager. registerDriver( new Driver());是写在静态代码块中，只要调用Driver这个方法，就会自动运行这段代码，这样会造成“注册两次驱动”

因此，我们可以改成如下版本：

  

          ②改进版

   

  // 注册驱动：告诉 java去连接哪种数据库
     //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
     new com.mysql.jdbc.Driver();

    但是这样写：

      1、静态初始化已经new了一个Driver对象，注册到DriverManager中去，在此再创建一个Driver对象则是完全没有必要的，浪费空间；

      2、不够灵活，如果需要换数据库的话，需要改动代码，此时最好把要变得内容改成字符串的形式，可以由变量读取外部配置文件进行传入（由此可以解释，为什么源代码中要直接写成静态代码块）     

        

           ③最终版   

     

 // 注册驱动：告诉 java 去连接哪种数据库
      //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //new com.mysql.jdbc.Driver();
      Class. forName( "com.mysql.jdbc.Driver");

     最终版的好处：

     1、Class.forName（）的作用是要求JVM查找并加载指定的类，也就是说JVM会执行该类的静态代码段。

     2、既然在静态初始化器中已经进行了注册，所以我们在使用JDBC时只需要Class.forName（XXX.XXX）就可以了

 

二：获得连接对象

 

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

url：连接到某一个具体的数据库

user：数据库的用户名

password：数据库用户名对应的密码

 

url = "jdbc:mysql://localhost:3306/mao"

jdbc：     jdbc协议

mysql：     jdbc子协议（表示mysql协议）

 

备注：

     url其他写法一：

url = jdbc:mysql://localhost:3306/mao?userUnicode=true&characterEncoding=utf8
//?userUnicode=true&characterEncoding=utf8
//加上这个可以解决数据库的乱码问题

/*

*1、存数据时：

*数据库在存放项目数据的时候会先用UTF-8格式将数据解码成字节码，然后再将解码后的字节码重新使用GBK编码存*放到数据库中。

*

*2.取数据时：

*     在从数据库中取数据的时候，数据库会先将数据库中的数据按GBK格式解码成字节码，然后再将解码后的字节*码重新按UTF-8格式编码数据，最后再将数据返回给客户端。

*

*/

     url其他写法二：

//如果连接的mysql是在本机，并且mysql的端口是3306，比如：
url："jdbc:mysql://localhost:3306/mao"
//那么url可以简写为：
url："jdbc:mysql:///mao"

 

三、语句对象

 

   

  // 创建语句对象Statement
     Statement stmt = con.createStatement();
 
     //创建预设语句对象PreparedStatement
     String sql = "update class set classDesc = ? where id = ?";
     PreparedStatement pstmt = con.prepareStatement(sql);

     接下来是重点：

      1：Statement的缺陷：

               ①容易被sql注入

 

sql注入：在页面表单中输入sql的片段。达到串改程序中sql语句。

 

 

正常情况：

 

select * from user where username='zhangsan' and password = '123456';

 

 

当sql被注入之后的语句：

 

select * from user where username='zhangsan' and password = '' or '1'='1'

                ②效率不高

               Statement每执行一次，sql进行一次编译。即使每次的sql语句格式都一样

     

     2：PreparedStatement的优势

               ①防止sql注入

                    PreparedStatement会对特殊字符进行转译，并通过参数形式设置到语句中，而不再是变量拼凑成sql语句

                 ②预编译功能

                 相同格式的sql语句只被编译一次，后一条格式相同的sql语句运行时，只需改变参数的值即可

 

 

第二版：使用数据库连接池

代码前备注：

     本工具类代码使用c3p0连接池

c3p0-config.xml

<? xml version ="1.0" encoding= "UTF-8" ?>
< c3p0-config>
       <!-- 默认配置，c3p0框架默认加载这段默认配置 -->
       < default-config>
             <!-- 配置JDBC 四个基本属性 -->
             < property name ="driverClass" > com.mysql.jdbc.Driver</ property >
             < property name ="jdbcUrl" > jdbc:mysql:///数据库名</ property >
             < property name ="user" > 数据库用户名</ property >
             < property name ="password" > 数据库密码</ property >
       </ default-config> <!-- This app is massive! -->
</ c3p0-config>

 

 

package cn.service;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import org.junit.Test;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.util.TestUtils;
 
public class DBService {
 
    // 三剑客
    Connection con = null;// 连接对象
    PreparedStatement pstmt = null;// 语句对象
    ResultSet rs = null;// 结果集对象
 
    /**
     * 获得连接对象
     *
     * @return 连接对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection() throws ClassNotFoundException,
            SQLException {
 
        // 创建c3p0连接池
        ComboPooledDataSource ds = new ComboPooledDataSource("itcast");
        // 通过连接池对象创建连接
        con = ds.getConnection();
        return con;
    }
 
    /**
     * 关闭三剑客
     *
     * @throws SQLException
     */
    public void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
 
        try {
            if (rs != null)
                rs.close();
            if (pstmt != null)
                pstmt.close();
            if (con != null)
                con.close();
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }
 
    /**
     * 执行更新
     *
     * @param sql
     *            传入的预设的sql语句
     * @param params
     *            问号参数列表
     * @return 影响行数
     */
    public int execUpdate(String sql, Object[] params) {
        try {
            this.getConnection();// 获得连接对象
            this.pstmt = this.con.prepareStatement(sql);// 获得预设语句对象
 
            if (params != null) {
                // 设置参数列表
                for (int i = 0; i < params.length; i++) {
                    // 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
                    this.pstmt.setObject(i + 1, params[i] + "");
                }
            }
 
            return this.pstmt.executeUpdate();// 执行更新，并返回影响行数
 
        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            this.close(this.rs, this.pstmt, this.con);
        }
        return 0;
    }
 
    /**
     * 执行查询
     *
     * @param sql
     *            传入的预设的sql语句
     * @param params
     *            问号参数列表
     * @return 查询后的结果
     */
    public List<Map<String, Object>> execQuery(String sql, Object[] params) {
 
        try {
            this.getConnection();// 获得连接对象
            this.pstmt = this.con.prepareStatement(sql);// 获得预设语句对象
 
            if (params != null) {
                // 设置参数列表
                for (int i = 0; i < params.length; i++) {
                    // 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
                    this.pstmt.setObject(i + 1, params[i] + "");
                }
            }
 
            // 执行查询
            ResultSet rs = pstmt.executeQuery();
 
            List<Map<String, Object>> al = new ArrayList<Map<String, Object>>();
 
            // 获得结果集元数据（元数据就是描述数据的数据，比如把表的列类型列名等作为数据）
            ResultSetMetaData rsmd = rs.getMetaData();
 
            // 获得列的总数
            int columnCount = rsmd.getColumnCount();
 
            // 遍历结果集
            while (rs.next()) {
                Map<String, Object> hm = new HashMap<String, Object>();
                for (int i = 0; i < columnCount; i++) {
                    // 根据列索引取得每一列的列名,索引从1开始
                    String columnName = rsmd.getColumnName(i + 1);
                    // 根据列名获得列值
                    Object columnValue = rs.getObject(columnName);
                    // 将列名作为key，列值作为值，放入hm中，每个hm相当于一条记录
                    hm.put(columnName, columnValue);
                }
                // 将每个hm添加到al中,al相当于是整个表，每个hm是里面的一条记录
                al.add(hm);
            }
 
            return al;
 
        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            this.close(this.rs, this.pstmt, this.con);
        }
 
        return null;
    }
 
}