`

spring日记（八）：使用Spring JDBC访问数据库

spring 
阅读更多

本人博客已搬家，新地址为：http://yidao620c.github.io/

在xml中配置jdbcTemplate：

<context:component-scan base-package="com.springzoo"/>
     
<context:property-placeholder location="classpath:jdbc.properties" />
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close"
    p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.url}"
    p:username="${jdbc.username}"
    p:password="${jdbc.password}" />
 
<bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate"
      p:dataSource-ref="dataSource"/>

下面是DAO的示例：

@Repository
public class ForumDao {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
 
    public void initDb() {
        String sql = "create table t_user(user_id int primary key,user_name varchar(60))";
        jdbcTemplate.execute(sql);
    }
 
    /**
     * 更新一条数据
     * 
     * @param forum
     */
    public void addForum(final Forum forum) {
        final String sql = "INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?)";
        Object[] params = new Object[] { forum.getForumName(),
                forum.getForumDesc() };
        // 方式1
        // jdbcTemplate.update(sql, params);
 
        // 方式2
        // jdbcTemplate.update(sql, params,new
        // int[]{Types.VARCHAR,Types.VARCHAR});
 
        // 方式3
        /*
         * jdbcTemplate.update(sql, new PreparedStatementSetter() { public void
         * setValues(PreparedStatement ps) throws SQLException { ps.setString(1,
         * forum.getForumName()); ps.setString(2, forum.getForumDesc()); } });
         */
 
        // 方式4
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection conn)
                    throws SQLException {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
                return ps;
            }
        }, keyHolder);
        forum.setForumId(keyHolder.getKey().intValue());
    }
 
    public void addForumByNamedParams(final Forum forum) {
        final String sql = "INSERT INTO t_forum(forum_name, forum_desc) VALUES(:forumName,:forumDesc)";
        SqlParameterSource sps = new BeanPropertySqlParameterSource(forum);
        namedParameterJdbcTemplate.update(sql, sps);
    }
 
    /**
     * 批量更新数据
     * 
     * @param forums
     */
    public void addForums(final List<Forum> forums) {
        final String sql = "INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return forums.size();
            }
 
            public void setValues(PreparedStatement ps, int index)
                    throws SQLException {
                Forum forum = forums.get(index);
                ps.setString(1, forum.getForumName());
                ps.setString(2, forum.getForumDesc());
            }
        });
    }
 
    /**
     * 根据ID获取Forum对象
     * 
     * @param forumId
     * @return
     */
    public Forum getForum(final int forumId) {
        String sql = "SELECT forum_name,forum_desc FROM t_forum WHERE forum_id=?";
        final Forum forum = new Forum();
 
        jdbcTemplate.query(sql, new Object[] { forumId },
                new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        forum.setForumId(forumId);
                        forum.setForumName(rs.getString("forum_name"));
                        forum.setForumDesc(rs.getString("forum_desc"));
                    }
                });
        return forum;
    }
 
    public List<Forum> getForums(final int fromId, final int toId) {
        String sql = "SELECT forum_id,forum_name,forum_desc FROM t_forum WHERE forum_id between ? and ?";
        // 方法1：使用RowCallbackHandler接口
        /*
         * final List<Forum> forums = new ArrayList<Forum>();
         * jdbcTemplate.query(sql,new Object[]{fromId,toId},new
         * RowCallbackHandler(){ public void processRow(ResultSet rs) throws
         * SQLException { Forum forum = new Forum();
         * forum.setForumId(rs.getInt("forum_id"));
         * forum.setForumName(rs.getString("forum_name"));
         * forum.setForumDesc(rs.getString("forum_desc")); forums.add(forum);
         * }}); return forums;
         */
 
        return jdbcTemplate.query(sql, new Object[] { fromId, toId },
                new RowMapper<Forum>() {
                    public Forum mapRow(ResultSet rs, int index)
                            throws SQLException {
                        Forum forum = new Forum();
                        forum.setForumId(rs.getInt("forum_id"));
                        forum.setForumName(rs.getString("forum_name"));
                        forum.setForumDesc(rs.getString("forum_desc"));
                        return forum;
                    }
                });
 
    }
 
    int getForumNum() {
        return 0;
    }
}

返回数据库的自增主键方法：

Statement stmt = conn.createStatement();
String sql = "INSERT INTO t_topic(....) values(...)";
stmt.executeUpate(sql, Statement.RETURN_GENERATED_KYES);
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
    int key = rs.getInt();
}

spring利用这一技术，提供了一个可以返回新增记录主键的方法，就是上面的方式4：

// 方式4
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
    public PreparedStatement createPreparedStatement(Connection conn)
            throws SQLException {
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, forum.getForumName());
        ps.setString(2, forum.getForumDesc());
        return ps;
    }
}, keyHolder);
forum.setForumId(keyHolder.getKey().intValue());

不过要注意的是，采用数据库的自增主键是不安全的，可以通过查询sequence或者在应用程序中提供主键两种方式保证安全性。

从功能上讲，RowCallbackHandler和RowMapper<T>没有太大区别，在返回多行时候RowMapper<T>更适合，因为不需要手动去往List里面添加。spring宣称RowCallbackHandler接口实现类可以使有状态的，如果它有状态我们就不能在多个地方重用。而RowMapper<T>实现类是没有状态的。

但是注意：如果处理大结果集的时候，使用RowMapper可能会内存泄露，最好使用RowCallbackHandler的实现类RowCountCallbackHandler，在processRow方法中处理结果集数据。一句话，RowMapper是将所有结果返回到一个List中，再去处理。而RowCallbackHandler是一行一行的去处理。

单值查询：

@Repository
public class TopicDao {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    public double getReplyRate(int userId) {
        String sql = "SELECT topic_replies,topic_views FROM t_topic WHERE user_id=?";
        double rate = jdbcTemplate.queryForObject(sql, new Object[] { userId },
                new RowMapper<Double>() {
                    public Double mapRow(ResultSet rs, int index)
                            throws SQLException {
                        int replies = rs.getInt("topic_replies");
                        int views = rs.getInt("topic_views");
                        if (views > 0)
                            return new Double((double) replies / views);
                        else
                            return new Double(0.0);
                    }
                });
        return rate;
    };
 
    public int getUserTopicNum(final int userId) {
        String sql = "{call P_GET_TOPIC_NUM(?,?)}";
        //方式1
/*      Integer num = jdbcTemplate.execute(sql,
                new CallableStatementCallback<Integer>() {
                    public Integer doInCallableStatement(CallableStatement cs)
                            throws SQLException, DataAccessException {
                        cs.setInt(1, userId);
                        cs.registerOutParameter(2, Types.INTEGER);
                        cs.execute();
                        return cs.getInt(2);
                    }
                });
        return num;*/
         
        //方式2
        CallableStatementCreatorFactory fac = new CallableStatementCreatorFactory(sql); 
        fac.addParameter(new SqlParameter("userId",Types.INTEGER)); 
        fac.addParameter(new SqlOutParameter("topicNum",Types.INTEGER)); 
        Map<String,Integer> paramsMap = new HashMap<String,Integer>();
        paramsMap.put("userId",userId);
        CallableStatementCreator csc = fac.newCallableStatementCreator (paramsMap); 
        Integer num = jdbcTemplate.execute(csc,new CallableStatementCallback<Integer>(){
            public Integer doInCallableStatement(CallableStatement cs) 
                throws SQLException, DataAccessException {
                cs.execute();
                return cs.getInt(2);
            }   
        });
        return num;
    }
 
    public int getUserTopicNum2(int userId) {
        return 0;
    };
 
    public SqlRowSet getTopicRowSet(int userId) {
        String sql = "SELECT topic_id,topic_title FROM t_topic WHERE user_id=?";
        return jdbcTemplate.queryForRowSet(sql,userId);
 
    };
}

处理BLOB/COLB的大数据：

spring定义了一个独立于java.sql.Blob/Clob接口，以统一方式操作各种数据库Lob类型的LobCreator接口。为了方便在PreparedStatement中使用LobCreator，可以直接使用JDBCTemplate#execute(String sql, AbstractLobCreatingPreparedStatementCallback lcpsc)方法。用来set大字段的值

LobHandler接口为操作大二进制字段和大文本字段提供了统一访问接口，不管底层数据库究竟以大对象还是以一般数据类型方式进行操作，此外，LobHandler还充当LobCreator的工厂类。一般使用DefaultLobHandler即可。用来get返回的大字段的值

xml配置，注意lazy-init要设置成true，因为nativeJdbcExtractor需要通过运行时反射机制获取底层JDBC对象：

 

<bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate"
      p:dataSource-ref="dataSource"/>
 
<bean id="nativeJdbcExtractor"
    class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
    lazy-init="true" />
 
<bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
    lazy-init="true" />

DAO中的用法，往数据库中插入或更新大数据操作：

@Repository
public class PostDao {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    @Autowired
    private LobHandler lobHandler;
 
    public void addPost(final Post post){
        String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"
                   + " VALUES(?,?,?,?)";
        jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
          protected void setValues(PreparedStatement ps,LobCreator lobCreator)
                  throws SQLException {
                                //1：固定主键
                                ps.setInt(1,1);
                   
                    ps.setInt(2, post.getUserId()); 
                    lobCreator.setClobAsString(ps, 3, post.getPostText());
                    lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
                }
            });
 
    }
    .....
}

以块数据方式读取数据库中大数据：

public List<Post> getAttachs(final int userId) {
    String sql = " SELECT post_id,post_attach FROM t_post where user_id =? and post_attach is not null ";
    return jdbcTemplate.query(sql, new Object[] { userId },
            new RowMapper<Post>() {
                public Post mapRow(ResultSet rs, int rowNum)
                        throws SQLException {
                    int postId = rs.getInt(1);
                    byte[] attach = lobHandler.getBlobAsBytes(rs, 2);
                    Post post = new Post();
                    post.setPostId(postId);
                    post.setPostAttach(attach);
                    return post;
                }
            });
 
}

以流数据方式读取Lob数据：

对于体积很大的Lob数据，比如超过50M，我们可以使用流的方式进行访问，减少内存占用。

public void getAttach(final int postId, final OutputStream os) {
    String sql = "SELECT post_attach FROM t_post WHERE post_id=? ";
    jdbcTemplate.query(sql, new Object[] {postId},
        new AbstractLobStreamingResultSetExtractor() {          
            protected void handleNoRowFound() throws LobRetrievalFailureException {
                    System.out.println("Not Found result!");
                }
            
            public void streamData(ResultSet rs) throws SQLException,IOException {
                    InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
                    if (is != null) {
                        FileCopyUtils.copy(is, os);
                    }
            }
        }
    );
 
}

自增键和行集RowSet：

之前说过依靠数据库提供的自增键功能，比如mysql的auto_increment是不安全的。spring允许用户在应用层产生主键值，为此定义了org.springframework.jdbc.support.incrementer.DataFieldMaxValueIncrementer接口，提供两种产生主键的方案：一个是通过序列sequence产生，另一个是通过表产生主键。

* AbstractDataFieldMaxValueIncrementer：使用sequence或者模拟序列表产生主键，被下面两个类继承。

* AbstractSequenceMaxValueIncrementer使用标准的sequence产生主键

* AbstractColumnMaxValueIncrementer使用模拟序列表产生主键，可以指定cacheSize一次性缓冲多少个主键值，减少数据库访问次数。

首先在DAO中加入这一行：

@Autowired
private DataFieldMaxValueIncrementer incre;

然后再xml中定义：

<!-- 1：基于数据库序列的自增器 -->
<!-- 
<bean id="incre" 
 class="org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer"
 p:incrementerName="seq_post_id"
 p:dataSource-ref="dataSource"/>  
-->
 
<!-- 1：基于数据表的自增器 -->
<bean id="incre"
    class="org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer"
    p:incrementerName="t_post_id"
    p:columnName="sequence_id"
    p:cacheSize="10"
    p:dataSource-ref="dataSource"/>

用法：

public void addPost(final Post post){
String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"
               + " VALUES(?,?,?,?)";
jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
      protected void setValues(PreparedStatement ps,LobCreator lobCreator)
      throws SQLException {
            //2：通过自增键指定主键值        
            ps.setInt(1, incre.nextIntValue());
                ps.setInt(2, post.getUserId()); 
                lobCreator.setClobAsString(ps, 3, post.getPostText());
                lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
            }
        });
 
}

规划主键方案：

所有主键全部采用应用层主键产生方案，使用UUID或者DataFieldMaxValueIncrementer生产主键。

以行集返回数据：

RowSet会一次性装载所有的匹配数据，同时会断开数据库的连接。而不是像ResultSet那样，分批次返回一批数据，一批的行数为fetchSize。所以，对于大结果集的数据，使用SqlRowSet会造成很大的内存消耗。

其他的JDBCTemplate：

NamedParameterJDBCTemplate：提供命名参数绑定功能

SimpleJDBCTemplate：将常用的API开放出来

先看一下xml配置文件：

<bean id="namedParamJdbcTemplate"
      class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
      <constructor-arg ref="dataSource"/>
</bean>

用法如下：

public void addForumByNamedParams(final Forum forum) {
    final String sql = "INSERT INTO t_forum(forum_name, forum_desc) VALUES(:forumName,:forumDesc)";
    // SqlParameterSource sps = new BeanPropertySqlParameterSource(forum);
    MapSqlParameterSource sps = new MapSqlParameterSource().
        addValue("forumName", forum.getForumName()).
        addValue("forumDesc", forum.getForumDesc());
    namedParameterJdbcTemplate.update(sql, sps);
}
分享到：
| spring日记（七）：声明式事务机制
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    spring boot 入门项目

    6. **测试**：使用 JUnit 和 Spring Boot 的测试支持，可以方便地进行单元测试和集成测试。 7. **打包部署**：可以生成可执行的 JAR 或 WAR 文件，然后在生产环境中部署。 在学习过程中，推荐阅读官方文档，参与...

    计算机java实习日记.pdf

    7. **JDBC**：Java Database Connectivity，Java数据库连接，是Java访问数据库的标准API。在使用Hibernate之前，Java程序员通常需要通过JDBC来编写SQL语句进行数据库操作。 8. **问题解决**：实习生通过查找资料、...

    springboot学习日记(一-六)

    第四部分，Spring Boot的数据访问层可能被讨论，包括JDBC、MyBatis或Spring Data JPA的使用。Spring Data JPA允许我们通过简单的接口方法定义CRUD操作，而无需编写大量的DAO代码。 第五部分，可能涉及Spring Boot的...

    Java 程序 日记本

    13. **数据库操作**：JDBC的使用，SQL语言的基础和进阶，事务的处理，以及数据库连接池的配置和使用。 14. **Maven或Gradle构建工具**：项目管理和构建工具的使用，配置 pom.xml 或 build.gradle 文件，以及依赖...

    计算机实习日记范文3篇.pdf

    6. **JDBC和SQL**：JDBC（Java Database Connectivity）是Java访问数据库的标准接口，而SQL（Structured Query Language）是用于管理和操作关系数据库的语言。在实习过程中，作者从JDBC转向使用Hibernate，以减少...

    spring案例.zip|spring案例.zip

    Spring JDBC和JPA（Java Persistence API）都是常见的数据访问技术，前者提供了简单的SQL操作，后者则基于ORM（对象关系映射）框架，如Hibernate或MyBatis，将Java对象与数据库表进行映射。 综上所述，"spring案例....

    基于jsp+mysql的JSP个人日记本源码.zip

    9. **JDBC连接**: 项目中与数据库交互的部分可能使用了Java Database Connectivity (JDBC) API，用于建立Java程序和MySQL之间的连接，执行SQL命令和处理结果集。 10. **文件上传与下载**: 如果日记本允许用户上传...

    程序员实习日记,计算机专业实习日记.pdf

    总的来说，这篇实习日记揭示了软件开发过程中常见的问题，如框架的理解与应用、数据库的兼容性、服务器配置、以及GUI组件的使用等，这些都是计算机专业实习生在实际工作中需要掌握和解决的关键技能。通过这样的实践...

    spring boot + maven + mybatis + layui

    MyBatis与Spring Boot结合使用时，可以通过Spring的注解驱动实现数据访问层的事务管理和其他服务。 **LayUI** LayUI是一个采用自身模块规范编写的前端UI框架，强调轻量、高性能、优雅的代码结构。LayUI提供了丰富的...

    Java 日记本系统

    Spring Security或者Apache Shiro等框架可以帮助实现这些功能，防止未授权访问和跨站脚本攻击（XSS）等安全问题。 7. **前端技术**： 在用户界面方面，HTML、CSS和JavaScript用于构建交互式的网页。现代前端框架如...

    框架struts2.5.2+spring4.1.6+mybatis3.11整合附带jar包

    综上所述，这个整合项目提供了一个基础的开发环境，开发者可以直接在MyEclipse中导入使用，快速搭建基于Struts2、Spring和MyBatis的Web应用。通过这三个框架的协同工作，开发者可以高效地处理HTTP请求、管理业务逻辑...

    【整合汇编】2021年计算机实习日记四篇.pdf

    3. **JDBC与Hibernate**：JDBC（Java Database Connectivity）是Java与数据库交互的标准接口，但直接使用JDBC编写SQL语句会较为繁琐。Hibernate作为ORM框架，为开发者提供了更高级别的抽象，减少了直接操作数据库的...

    最新计算机实习日记范文.docx

    4. **数据库管理**：实习中遇到数据库连接问题，可能的原因包括未安装正确的JDBC驱动、数据库版本兼容性、密码不一致等。解决这些问题需要对数据库管理和服务器配置有一定的了解。 5. **开源框架**：Struts、Spring...

    【整合汇编】2021年计算机实习日记四篇.docx

    【整合汇编】2021年计算机实习日记四篇主要涵盖了Java Web开发中的核心技术，包括Struts、Spring和Hibernate框架的使用，以及页设计和数据库管理的基础知识。以下是这些知识点的详细说明： 1. **Struts框架**： - ...

Magicbox
Global site tag (gtag.js) - Google Analytics