首页 >数据库 >mysql教程 >Java进阶学习第十九天dbutils与案例

Java进阶学习第十九天dbutils与案例

WBOY原创: 2016-06-07 14:49:501185浏览

文档版本开发工具测试平台工程名字日期作者备注 V1.0 2016.05.15 lutianfei none 元数据元数据(metaData) 指数据库中库、表、列的定义信息 DataBaseMetaData 数据库元数据获取一个DataBaseMetaData Connection接口中定义了一个方法 getMetaDat

文档版本	开发工具	测试平台	工程名字	日期	作者	备注
V1.0				2016.05.15	lutianfei	none

元数据

元数据(metaData) 指数据库中库、表、列的定义信息

DataBaseMetaData 数据库元数据

获取一个DataBaseMetaData
- Connection接口中定义了一个方法 getMetaData();
- Connection.getMetaData()
通过DataBaseMetaData获得 数据库连接的基本参数
- getURL()：返回一个String类对象，代表数据库的URL。
- getUserName()：返回连接当前数据库管理系统的用户名。
- getDriverName()：返回驱动驱动程序的名称。
- getPrimaryKeys(String catalog, String schema, String table)：返回指定表主键列的结果集
获得数据库、表、列、主键、外键 定义信息
- getTables
- getColumns
- getPrimaryKeys
常用API
- String driverName = dmd.getDriverName(); //获取驱动名称
- String userName = dmd.getUserName();//获取用户名
- String url = dmd.getURL();//获取url
- String databaseProductName = dmd.getDatabaseProductName(); //获取数据库名称
- String databaseProductVersion = dmd.getDatabaseProductVersion();//获取数据库版本.
- ResultSet getPrimaryKeys(String catalog,String schema,String table)
  - 获取表中主键相关描述，每个主键列描述都有以下列：
    - TABLE_CAT String => 表类别（可为 null）
    - TABLE_SCHEM String => 表模式（可为 null）
    - TABLE_NAME String => 表名称
    - COLUMN_NAME String => 列名称
    - KEY_SEQ short => 主键中的序列号（值 1 表示主键中的第一列，值 2 表示主键中的第二列）。
    - PK_NAME String => 主键的名称（可为 null）

ParameterMetaData 参数元数据

参数元数据主要用于获取:sql语句中占位符的相关信息.
PreparedStatement . getParameterMetaData()
- 获得代表PreparedStatement元数据的ParameterMetaData对象。
常用API
- getParameterCount()：获得指定参数的个数
- getParameterTypeName(int param) ：获得指定参数的sql类型
注意：在获取参数类型时会产生异常
- java.sql.SQLException: Parameter metadata not available for the given statement
解决方案:
- 在url后添加参数：jdbc : mysql:///day18?generateSimpleParameterMetadata=true
- 原因:是mysql驱动的支持问题。

ResultSetMetaData 结果集元数据（重点）

ResultSet. getMetaData()
- 获得代表ResultSet对象元数据的ResultSetMetaData对象。
常用API
- getColumnCount() 返回resultset对象的列数
- getColumnName(int column) 获得指定列的名称
- getColumnTypeName(int column) 获得指定列的类型

<code class=" hljs avrasm">    public static void main(String[] args) throws SQLException {

        Connection con = JdbcUtils<span class="hljs-preprocessor">.getConnection</span>()<span class="hljs-comment">;</span>
        ResultSet rs = con<span class="hljs-preprocessor">.createStatement</span>()<span class="hljs-preprocessor">.executeQuery</span>(
                <span class="hljs-string">"select * from account"</span>)<span class="hljs-comment">;</span>

        // 得到结果集元数据
        ResultSetMetaData rsmd = rs<span class="hljs-preprocessor">.getMetaData</span>()<span class="hljs-comment">;</span>

        // System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.println</span>(rsmd<span class="hljs-preprocessor">.getColumnCount</span>())<span class="hljs-comment">;//获取结果集中列数量</span>
        //
        // System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.println</span>(rsmd<span class="hljs-preprocessor">.getColumnName</span>(<span class="hljs-number">2</span>))<span class="hljs-comment">;//获取结果集中指定列的名称.</span>
        //
        // System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.println</span>(rsmd<span class="hljs-preprocessor">.getColumnTypeName</span>(<span class="hljs-number">3</span>))<span class="hljs-comment">;//获取结果集中指定列的类型。</span>

        int count = rsmd<span class="hljs-preprocessor">.getColumnCount</span>()<span class="hljs-comment">;</span>

        for (int i = <span class="hljs-number">1</span><span class="hljs-comment">; i <= count; i++) {</span>

            System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.print</span>(rsmd<span class="hljs-preprocessor">.getColumnName</span>(i)+<span class="hljs-string">"("</span>+rsmd<span class="hljs-preprocessor">.getColumnTypeName</span>(i)+<span class="hljs-string">")"</span> + <span class="hljs-string">"\t"</span>)<span class="hljs-comment">;</span>
        }
        System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.println</span>()<span class="hljs-comment">;</span>

        while (rs<span class="hljs-preprocessor">.next</span>()) {
            for (int i = <span class="hljs-number">1</span><span class="hljs-comment">; i <= count; i++) {</span>
                System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.print</span>(rs<span class="hljs-preprocessor">.getObject</span>(i) + <span class="hljs-string">"\t\t"</span>)<span class="hljs-comment">;</span>
            }
            System<span class="hljs-preprocessor">.out</span><span class="hljs-preprocessor">.println</span>()<span class="hljs-comment">;</span>
        }
    }</code>

dbutils工具

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
简单说，它就是一个简单的jdbc封装工具，使用dbutils可以简化操作，要使用dbutils需要导入jar包。
JAR包: commons.dbutils-1.4.jar
API介绍：
- org.apache.commons.dbutils.QueryRunner — 核心
- org.apache.commons.dbutils.ResultSetHandler
工具类
- org.apache.commons.dbutils.DbUtils。

DBUtils学习

1、QueryRunner 框架核心类，所有数据库操作都是必须通过 QueryRunner 进行的,用于执行sql语句的类。
- 1.query 用于执行select
- 2.update 用于执行update delete insert
- 3.batch 批处理
2、ResultSetHandler 结果集封装接口，完成将ResultSet 结果集封装为一个Java对象
- 用于定义结果集的封装
- 它提供九个实现类，可以进行不同的封装。
3、DbUtils 工具类提供驱动管理、事务管理、释放资源等一系列公共方法
- 它提供关于关闭资源以及事务rollback,commit操作。
- DbUtils里面的所有方法都是静态的。主要方法如下：
  - public static void close(…) throws java.sql.SQLException
    - DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
  - public static void closeQuietly(…)
    - 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLException。
  - public static void commitAndCloseQuietly(Connection conn)
    - 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
  - public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册*JDBC驱动程序*，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

Dbutlis详解

1.QueryRunner怎样获取
- 1.new QueryRunner()
  - 如果是使用这种构造创建的QueryRunner,它的事务是手动控制。
- 2.new QueryRunner(DataSource ds);
  - 如果是使用这种构造，它的事务是自动事务，简单说，一条sql一个事务。
2.QueryRunner中的三个核心方法
- query
- update
- batch
- 对于上述三个方法，它们提供很多重载。
  - 如果QueryRunner在创建时，没有传递DataSource参数，那么在使用query,update,batch方法时，要传递Connection参数
  - 如果QueryRunner在创建时，传递了Dataource参数，那么在使用query,update,batch方法时，不需要传递Connection参数。
总结:
怎样配套使用:
- QueryRunner runner=new QueryRunner();
  - runner.query(Connection,sql,ResultSetHandler,Object… param);
  - runner.update(Connection,sql,Object…param);
  - runner.batch(Connection con,sql,Object[][] objs);
- QueryRunner runner=new QueryRunner(DataSource ds);
  - runner.query(sql,ResultSetHandler,Object… param);
  - runner.update(sql,Object…param);
  - runner.batch(sql,Object [][] objs);
模仿QueryRunner
1.query方法模仿

<code class=" hljs cs"><span class="hljs-keyword">public</span> <T> T <span class="hljs-title">query</span>(Connection con, String sql, MyResultSetHandler<T> mrs,Object... <span class="hljs-keyword">params</span>) throws SQLException {

        PreparedStatement pst = con.prepareStatement(sql); <span class="hljs-comment">// 得到一个预处理的Statement.</span>
        <span class="hljs-comment">// 问题:sql语句中可能存在参数，需要对参数赋值。</span>

        ParameterMetaData pmd = pst.getParameterMetaData();
        <span class="hljs-comment">// 可以得到有几个参数</span>
        <span class="hljs-keyword">int</span> count = pmd.getParameterCount();
        <span class="hljs-keyword">for</span> (<span class="hljs-keyword">int</span> i = <span class="hljs-number">1</span>; i <= count; i++) {
            pst.setObject(i, <span class="hljs-keyword">params</span>[i - <span class="hljs-number">1</span>]);
        }

        ResultSet rs = pst.executeQuery(); <span class="hljs-comment">// 得到了结果集，要将结果集封装成用户想要的对象，但是，工具不可能知道用户需求。</span>

        <span class="hljs-keyword">return</span> mrs.handle(rs);
}</code>

2.update方法模仿

<code class=" hljs cs">    <span class="hljs-keyword">public</span> <span class="hljs-keyword">int</span> <span class="hljs-title">update</span>(Connection con, String sql, Object... <span class="hljs-keyword">params</span>) throws SQLException {

        PreparedStatement pst = con.prepareStatement(sql); <span class="hljs-comment">// 得到一个预处理的Statement.</span>
        <span class="hljs-comment">// 问题:sql语句中可能存在参数，需要对参数赋值。</span>

        ParameterMetaData pmd = pst.getParameterMetaData();
        <span class="hljs-comment">// 可以得到有几个参数</span>
        <span class="hljs-keyword">int</span> count = pmd.getParameterCount();
        <span class="hljs-keyword">for</span> (<span class="hljs-keyword">int</span> i = <span class="hljs-number">1</span>; i <= count; i++) {
            pst.setObject(i, <span class="hljs-keyword">params</span>[i - <span class="hljs-number">1</span>]);
        }

        <span class="hljs-keyword">int</span> row = pst.executeUpdate();
        <span class="hljs-comment">// 关闭资源</span>
        pst.close();
        <span class="hljs-keyword">return</span> row;
    }</code>

ResulsetHandler九个实现类

ResultSetHandler接口：用于封装结果集.
该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
- ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。
ArrayHandler, 将结果集中第一条记录封装到Object[]数组,数组中的每一个元素就是记录中的字段值。
ArrayListHandler, 将结果集中每一条记录封装到Object[],数组中的每一个元素就是记录中的字段值。在将这些数组装入到List集合。
BeanHandler（重点）, 将结果集中第一条记录封装到一个javaBean中。
BeanListHandler(重点), 将结果集中每一条记录封装到javaBean中，在将javaBean封装到List集合。
ColumnListHandler, 将结果集中指定列的值封装到List集合.
MapHandler, 将结果集中第一条记录封装到Map集合中，集合的 key就是字段名称，value就是字段值
MapListHandler, 将结果集中每一条记录封装到Map集合中，集合的 key就是字段名称，value就是字段值，在将这些Map封装到List集合
KeyedHandler,在使用指定的列的值做为一个Map集合的key,值为每一条记录的Map集合封装。
ScalarHandler 进行单值查询 select count(*) from account;

<code class=" hljs java"><span class="hljs-comment">//介绍ResultSetHandler的九个实现类.</span>
<span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">ResultSetHandlerImplTest</span> {</span>

    <span class="hljs-comment">// ArrayHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun1</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        Object[] obj = runner
                .query(<span class="hljs-string">"select * from account"</span>, <span class="hljs-keyword">new</span> ArrayHandler());

        System.out.println(Arrays.toString(obj));
    }

    <span class="hljs-comment">// ArrayListHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun2</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        List<Object[]> objs = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> ArrayListHandler());

        <span class="hljs-keyword">for</span> (Object[] obj : objs) {
            System.out.println(Arrays.toString(obj));
        }
    }

    <span class="hljs-comment">// BeanHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun3</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        Account obj = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> BeanHandler<Account>(Account.class));

        System.out.println(obj);
    }

    <span class="hljs-comment">// BeanListHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun4</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        List<Account> obj = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> BeanListHandler<Account>(Account.class));

        System.out.println(obj);
    }

    <span class="hljs-comment">// ColumnListHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun5</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        List<Object> obj = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> ColumnListHandler(<span class="hljs-string">"name"</span>));

        System.out.println(obj);
    }

    <span class="hljs-comment">// MapHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun6</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        Map<String, Object> obj = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> MapHandler());

        System.out.println(obj);
    }

    <span class="hljs-comment">// MapListHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun7</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        List<Map<String, Object>> obj = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> MapListHandler());

        System.out.println(obj);
    }

    <span class="hljs-comment">//KeyedHandler</span>

    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun8</span>() <span class="hljs-keyword">throws</span> SQLException {

        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        Map<Object,Map<String,Object>> obj = runner.query(<span class="hljs-string">"select * from account"</span>,
                <span class="hljs-keyword">new</span> KeyedHandler(<span class="hljs-string">"name"</span>));

        System.out.println(obj);
    }

    <span class="hljs-comment">//ScalarHandler</span>
    <span class="hljs-annotation">@Test</span>
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title">fun9</span>() <span class="hljs-keyword">throws</span> SQLException{
        QueryRunner runner = <span class="hljs-keyword">new</span> QueryRunner(DataSourceUtils.getDataSource());

        <span class="hljs-keyword">long</span> obj = (Long) runner.query(<span class="hljs-string">"select count(*) from account"</span>,<span class="hljs-keyword">new</span> ScalarHandler());

        System.out.println(obj);
    }</code>

实现BeanHandler

<code class=" hljs java"><span class="hljs-keyword">public</span> <span class="hljs-class"><span class="hljs-keyword">class</span> <span class="hljs-title">MyBeanHandler</span> <span class="hljs-keyword">implements</span> <span class="hljs-title">MyResultSetHandler</span> {</span>

    <span class="hljs-keyword">private</span> Class clazz;

    <span class="hljs-keyword">public</span> <span class="hljs-title">MyBeanHandler</span>(Class clazz) {
        <span class="hljs-keyword">this</span>.clazz = clazz;
    }

    <span class="hljs-keyword">public</span> Object <span class="hljs-title">handle</span>(ResultSet rs) <span class="hljs-keyword">throws</span> SQLException {
        Object obj = <span class="hljs-keyword">null</span>;

        Map<String, String[]> map = <span class="hljs-keyword">new</span> HashMap<String, String[]>();

        ResultSetMetaData md = rs.getMetaData();
        <span class="hljs-keyword">int</span> count = md.getColumnCount();

        <span class="hljs-keyword">if</span> (rs.next()) {
            <span class="hljs-keyword">try</span> {
                obj = clazz.newInstance();
                <span class="hljs-keyword">for</span> (<span class="hljs-keyword">int</span> i = <span class="hljs-number">1</span>; i <= count; i++) {
                    map.put(md.getColumnName(i),
                            <span class="hljs-keyword">new</span> String[] { rs.getString(md.getColumnName(i)) });
                }
                BeanUtils.populate(obj, map);
            } <span class="hljs-keyword">catch</span> (InstantiationException e) {
                e.printStackTrace();
            } <span class="hljs-keyword">catch</span> (IllegalAccessException e) {
                e.printStackTrace();
            } <span class="hljs-keyword">catch</span> (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        <span class="hljs-keyword">return</span> obj;
    }

    <span class="hljs-comment">// 从结果集的方向去封装数据</span>
    <span class="hljs-comment">// public Object handle(ResultSet rs) throws SQLException {</span>
    <span class="hljs-comment">// Object obj = null;</span>
    <span class="hljs-comment">// // 1.得到结果集元数据</span>
    <span class="hljs-comment">// ResultSetMetaData md = rs.getMetaData();</span>
    <span class="hljs-comment">// // 2.得到所有字段名称</span>
    <span class="hljs-comment">// int count = md.getColumnCount();</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// if (rs.next()) {//遍历结果集</span>
    <span class="hljs-comment">// try {</span>
    <span class="hljs-comment">// BeanInfo bif = Introspector.getBeanInfo(clazz); //得到BeanInfo</span>
    <span class="hljs-comment">// PropertyDescriptor[] pds = bif.getPropertyDescriptors();</span>
    <span class="hljs-comment">// //得到javaBean的所有属性描述器</span>
    <span class="hljs-comment">// obj = clazz.newInstance();</span>
    <span class="hljs-comment">// for (int i = 1; i <= count; i++) {</span>
    <span class="hljs-comment">// String name = md.getColumnName(i); //得到每一列的名称</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// for(PropertyDescriptor pd:pds){</span>
    <span class="hljs-comment">// if(name.equals(pd.getName())){ //与javaBean的属性比较</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// pd.getWriteMethod().invoke(obj,</span>
    <span class="hljs-comment">// rs.getObject(name));//使用setXxx方法将结果集中的字段值封装到JavaBean的对应属性上。</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">// } catch (InstantiationException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (IllegalAccessException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (IntrospectionException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (IllegalArgumentException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (InvocationTargetException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">// return obj;</span>
    <span class="hljs-comment">// }</span>

    <span class="hljs-comment">// 从javaBean 的方向去封装数据</span>
    <span class="hljs-comment">// public Object handle(ResultSet rs) throws SQLException {</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// Object obj = null;</span>
    <span class="hljs-comment">// // 1.得到clazz所有bean属性.</span>
    <span class="hljs-comment">// try {</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// BeanInfo bif = Introspector.getBeanInfo(clazz);</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// PropertyDescriptor[] pds = bif.getPropertyDescriptors();</span>
    <span class="hljs-comment">// if (rs.next()) {</span>
    <span class="hljs-comment">// obj = clazz.newInstance();</span>
    <span class="hljs-comment">// for (PropertyDescriptor pd : pds) {</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// // 得到所有属性名称</span>
    <span class="hljs-comment">// String name = pd.getName();</span>
    <span class="hljs-comment">// System.out.println(name);</span>
    <span class="hljs-comment">// // 得到所有属性对应的set方法</span>
    <span class="hljs-comment">// Method setMethod = pd.getWriteMethod();</span>
    <span class="hljs-comment">// if (setMethod != null) { // 就可以将class对应的写方法去掉</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// setMethod.invoke(obj, rs.getObject(name));</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// } catch (IntrospectionException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (InstantiationException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (IllegalAccessException e) {</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (IllegalArgumentException e) {</span>
    <span class="hljs-comment">// // TODO Auto-generated catch block</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// } catch (InvocationTargetException e) {</span>
    <span class="hljs-comment">// // TODO Auto-generated catch block</span>
    <span class="hljs-comment">// e.printStackTrace();</span>
    <span class="hljs-comment">// }</span>
    <span class="hljs-comment">//</span>
    <span class="hljs-comment">// return obj;</span>
    <span class="hljs-comment">// }</span>
}</code>

dbutils综合练习

以下均为项目day19_2 代码说明。
登录成功后，访问到一个页面success.jsp，在页面上添加一个连接,就是客户信息的CRUD操作。
1.客户信息

字段名	说明	类型
Id	编号	varchar(40)
name	客户姓名	varchar(20)
gender	性别	varchar(10)
birthday	生日	date
cellphone	手机	varchar(20)
email	电子邮件	varchar(40)
preference	客户爱好	varchar(100)
type	客户类型	varchar(40)
description	备注	varchar(255)

<code class=" hljs sql">    <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> customer(
       id <span class="hljs-keyword">varchar</span>(<span class="hljs-number">40</span>) <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>,
       name <span class="hljs-keyword">varchar</span>(<span class="hljs-number">20</span>),
       gender <span class="hljs-keyword">varchar</span>(<span class="hljs-number">10</span>),
       birthday <span class="hljs-keyword">date</span>,
       cellphone <span class="hljs-keyword">varchar</span>(<span class="hljs-number">20</span>),
       email <span class="hljs-keyword">varchar</span>(<span class="hljs-number">40</span>),
       preference <span class="hljs-keyword">varchar</span>(<span class="hljs-number">100</span>),
       type <span class="hljs-keyword">varchar</span>(<span class="hljs-number">40</span>),
       description <span class="hljs-keyword">varchar</span>(<span class="hljs-number">255</span>)
    );</span></code>

2.搭建环境
- JavaEE 三层结构
- Servlet + JSP + JavaBean+jstl + DBUtils+ DAO + MySQL
- 导入jar包：JSTL 、BeanUtils、DBUtils、C3P0、mysql驱动
- 创建包结构
  - cn.itcast.customer.web 表现层
  - cn.itcast.customer.service 业务层
  - cn.itcast.customer.dao 持久层
  - cn.itcast.customer.utils 工具包
  - cn.itcast.customer.domain 实体类 javaBean
应用的jar文件
- mysql驱动包
- dbutils包
- BeanUtil包
- JSTL包
- c3p0的配置文件
编写代码:
1.创建Customer这个javaBean

<code class=" hljs lasso">    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> id;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> name;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> gender;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">Date</span> birthday;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> cellphone;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> email;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> preference;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> <span class="hljs-keyword">type</span>;
    <span class="hljs-keyword">private</span> <span class="hljs-built_in">String</span> description;</code>

2.为了测试方便，向customer表中插入数据

<code class=" hljs sql">    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> customer <span class="hljs-keyword">values</span>(<span class="hljs-string">"a11"</span>,<span class="hljs-string">"tom"</span>,<span class="hljs-string">"男"</span>,<span class="hljs-string">"2010-10-10"</span>,<span class="hljs-string">"13888888888"</span>,<span class="hljs-string">"tom@163.com"</span>,<span class="hljs-string">"吃,喝,玩"</span>,<span class="hljs-string">"vip"</span>,<span class="hljs-string">"good man"</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> customer <span class="hljs-keyword">values</span>(<span class="hljs-string">"a11"</span>,<span class="hljs-string">"fox"</span>,<span class="hljs-string">"男"</span>,<span class="hljs-string">"2000-10-10"</span>,<span class="hljs-string">"13888888888"</span>,<span class="hljs-string">"tom@163.com"</span>,<span class="hljs-string">"吃,喝,玩"</span>,<span class="hljs-string">"vip"</span>,<span class="hljs-string">"good man"</span>);</span>
    <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> customer <span class="hljs-keyword">values</span>(<span class="hljs-string">"a11"</span>,<span class="hljs-string">"james"</span>,<span class="hljs-string">"男"</span>,<span class="hljs-string">"1990-10-10"</span>,<span class="hljs-string">"13888888888"</span>,<span class="hljs-string">"tom@163.com"</span>,<span class="hljs-string">"吃,喝,玩"</span>,<span class="hljs-string">"vip"</span>,<span class="hljs-string">"good man"</span>);</span></code>

3.实现查询所有客户信息操作
- 1.在success.jsp页面添加连接
  <a href="%24%7BpageContext.request.contextPath%7D/findAll">查看所有客户信息</a>
- 2.在CustomerFindAllServlet中调用service,在service中调用dao,最后得到一个List<customer></customer>。
- 3.在showCustomer.jsp页面展示客户信息

<code class=" hljs xml">        <span class="hljs-tag"><<span class="hljs-title">c:forEach</span> <span class="hljs-attribute">items</span>=<span class="hljs-value">"${cs}"</span> <span class="hljs-attribute">var</span>=<span class="hljs-value">"c"</span>></span>
            <span class="hljs-tag"><<span class="hljs-title">tr</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span><span class="hljs-tag"><<span class="hljs-title">input</span> <span class="hljs-attribute">type</span>=<span class="hljs-value">"checkbox"</span>></span>
                <span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.id }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.name}<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.gender }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.birthday }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.cellphone }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.email }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.preference }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.type }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span>${c.description }<span class="hljs-tag"></<span class="hljs-title">td</span>></span>
                <span class="hljs-tag"><<span class="hljs-title">td</span>></span><span class="hljs-tag"><<span class="hljs-title">a</span>></span>编辑<span class="hljs-tag"></<span class="hljs-title">a</span>></span>   <span class="hljs-tag"><<span class="hljs-title">a</span>></span>删除<span class="hljs-tag"></<span class="hljs-title">a</span>></span><span class="hljs-tag"></<span class="hljs-title">td</span>></span>
            <span class="hljs-tag"></<span class="hljs-title">tr</span>></span>
        <span class="hljs-tag"></<span class="hljs-title">c:forEach</span>></span></code>

4.删除操作
- 1.在showCustomer.jsp页面的删除连接上添加参数客户的id
  - <a href="%24%7BpageContext.request.contextPath%7D/delByid?id=%24%7Bc.id%7D">删除</a>
- 2.创建一个CustomerDelByIdServlet,获取请求参数,调用service中删除方法.
- 问题:如果删除完成后，怎样处理?
  - 需要重新跳转到查询所有的servlet中，在重新查询数据。

5.编辑
- 1.查询，做回显示
  - <a href="%24%7BpageContext.request.contextPath%7D/findById?id=%24%7Bc.id%7D">编辑</a>
  - 1.创建CustomerFindByIdServlet,得到要查询的id,调用service，得到Custonmer对象。
  - 2.将customer对象存储到request域，请求转发到customerInfo.jsp页面。
  - 3.在customerInfo.jsp页面展示客户信息
  - 注意:客户的id不能修改，所以使用<input type="hidden">
- 2.修改
  - 1.注意使用BeanUtils时的类型转换问题
  - 2.注意编码问题
    - post: request.setCharacterEncoding("utf-8");
    - get:手动转换 new String(request.getParameter(name).getBytes("iso8859-1"),"utf-8");
  - 3.进行修改操作

<code class=" hljs avrasm">    public void update(Customer c) throws SQLException {

        String sql = <span class="hljs-string">"update customer set name=?,gender=?,birthday=?,cellphone=?,email=?,preference=?,type=?,description=? where id=?"</span><span class="hljs-comment">;</span>

        QueryRunner runner = new QueryRunner(DataSourceUtils<span class="hljs-preprocessor">.getDataSource</span>())<span class="hljs-comment">;</span>

        runner<span class="hljs-preprocessor">.update</span>(sql, c<span class="hljs-preprocessor">.getName</span>(), c<span class="hljs-preprocessor">.getGender</span>(), c<span class="hljs-preprocessor">.getBirthday</span>(),
                c<span class="hljs-preprocessor">.getCellphone</span>(), c<span class="hljs-preprocessor">.getEmail</span>(), c<span class="hljs-preprocessor">.getPreference</span>(), c<span class="hljs-preprocessor">.getType</span>(),
                c<span class="hljs-preprocessor">.getDescription</span>(), c<span class="hljs-preprocessor">.getId</span>())<span class="hljs-comment">;</span>
    }
</code>

修改完成后，在重新查询一次
- response.sendRedirect(request.getContextPath() + "/findAll");
解决关于回显示时的问题:
1.性别应该使用radio
- 使用自定义标签
  - 1.定义标签类 extends SimpleTagSupport
  - 2.定义tld文件

<code class=" hljs xml">    <span class="hljs-tag"><<span class="hljs-title">tag</span>></span>
        <span class="hljs-tag"><<span class="hljs-title">name</span>></span>sex<span class="hljs-tag"></<span class="hljs-title">name</span>></span><span class="hljs-comment"><!-- 标签名称 --></span>
        <span class="hljs-tag"><<span class="hljs-title">tag-class</span>></span>cn.itcast.customer.tag.GenderTag<span class="hljs-tag"></<span class="hljs-title">tag-class</span>></span><span class="hljs-comment"><!-- 标签类 --></span>
        <span class="hljs-tag"><<span class="hljs-title">body-content</span>></span>empty<span class="hljs-tag"></<span class="hljs-title">body-content</span>></span><span class="hljs-comment"><!-- 标签体中内容 --></span>

        <span class="hljs-tag"><<span class="hljs-title">attribute</span>></span>
            <span class="hljs-tag"><<span class="hljs-title">name</span>></span>gender<span class="hljs-tag"></<span class="hljs-title">name</span>></span> <span class="hljs-comment"><!-- 属性名称 --></span>
            <span class="hljs-tag"><<span class="hljs-title">required</span>></span>true<span class="hljs-tag"></<span class="hljs-title">required</span>></span> <span class="hljs-comment"><!-- 属性必须有 --></span>
            <span class="hljs-tag"><<span class="hljs-title">rtexprvalue</span>></span>true<span class="hljs-tag"></<span class="hljs-title">rtexprvalue</span>></span><span class="hljs-comment"><!-- 属性值可以接收el表达式 --></span>
        <span class="hljs-tag"></<span class="hljs-title">attribute</span>></span>
    <span class="hljs-tag"></<span class="hljs-title">tag</span>></span></code>

<code>    * 3.在页面上使用
        * 1.使用taglib导入
        * 2.使用`<sex gender="${c.gender}"></sex>`
</code>

虚拟主机

使用虚拟主机可以将项目部署成顶级域名
1.在service.xml文件
- 1.端口修改为80
- 2.配置主机

<code class=" hljs perl"><Host name=<span class="hljs-string">"www.customer.com"</span>  appBase=<span class="hljs-string">"D:\java1110\workspace\day19_2"</span>
      unpackWARs=<span class="hljs-string">"true"</span> autoDeploy=<span class="hljs-string">"true"</span>>


  <Valve className=<span class="hljs-string">"org.apache.catalina.valves.AccessLogValve"</span> directory=<span class="hljs-string">"logs"</span>
         prefix=<span class="hljs-string">"localhost_access_log."</span> suffix=<span class="hljs-string">".txt"</span>
         pattern=<span class="hljs-string">"<span class="hljs-variable">%h</span> <span class="hljs-variable">%l</span> <span class="hljs-variable">%u</span> <span class="hljs-variable">%t</span> "<span class="hljs-variable">%r</span>" <span class="hljs-variable">%s</span> <span class="hljs-variable">%b</span>"</span> />

         <Context path=<span class="hljs-string">""</span> docBase=<span class="hljs-string">"D:\java1110\workspace\day19_2\WebRoot"</span> />
<<span class="hljs-regexp">/Host></span></code>

3.在hosts文件中配置
- 127.0.0.1 www.customer.com

声明：

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：TimesTen的操作系统，数据库和虚拟化软件支持列表下一篇：SQL Server数据库操作

查看更多