当前位置：Gxlcms > 数据库问题 > spring 14-Spring框架JDBC操作

spring 14-Spring框架JDBC操作

时间：2021-07-01 10:21:17 帮助过：13人阅读

传统JDBC开发

开发步骤：

  1. 进行数据库驱动程序的加载
  2. 取得数据库的连接对象
  3. 声明要操作的SQL语句（需要使用预处理）
  4. 创建数据库操作对象
  5. 执行SQL语句
  6. 处理返回的操作结果（ResultSet）
  7. 关闭结果集对象
  8. 关闭数据库的操作对象（Statement）
  9. 如果执行的是更新则应该进行事务提交和回滚
  10. 关闭数据库连接

优点：

  • 具备固定的操作流程，代码结构简单
  • JDBC是一个Java的公共服务，属于标准
  • 由于没有涉及到过于复杂对象操作，所以性能是最高的

缺点：

  • 代码的冗余度太高了，每次都需要编写大量的重复操作
  • 用户需要自己手工进行事务的处理操作
  • 所有的操作必须严格按照既定的步骤执行
  • 如果出现了执行的异常，则需要用户自己处理

使用Spring的JDBC模板

开发步骤：

  1. 声明要操作的SQL语句（需要使用预处理）
  2. 执行SQL语句
  3. 处理返回的操作结果（ResultSet）

优点：

  • 代码简单，但是又不脱离JDBC形式
  • 由于有SpringAOP的支持，用户只关心核心
  • 对于出现的程序异常可以采用统一的方式进行处理
  • 与JDBC的操作步骤或形式几乎雷同

缺点：

  • 与重度包装的Hibernate框架不同，不够智能
  • 处理返回结果的时候不能够自动转化为VO类对象，需要由用户自己手工处理结果集

使用Spring的JDBC针对MySQL的数据库连接

1、通过Maven配置数据库驱动程序

<dependency> 
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.25</version>
</dependency>

2、配置C3P0数据库连接池软件包

<dependency>
  <groupId>c3p0</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.1.2</version>
</dependency>
<dependency>
  <groupId>com.mchange</groupId>
  <artifactId>mchange-commons-java</artifactId>
  <version>0.2.12</version>
</dependency>

3、在CLASSPATH配置database.properties的属性文件

db.driverClass=org.gjt.mm.mysql.Driver
db.url=jdbc:mysql://192.168.209.31:3306/springdb
db.user=root
db.password=123456
db.maxPoolSize=1
db.maxIdleTime=1
db.minPoolSize=1
db.initialPoolSize=1

4、配置applicationContext.xml文件

<context:property-placeholder location="classpath:database.properties"/>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
  <!-- 配置数据库的驱动程序 -->
  <property name="driverClass" value="${db.driverClass}"/>
  <!-- 配置JDBC的连接地址 -->
  <property name="jdbcUrl" value="${db.url}"/>
  <!-- 定义连接用户名 -->
  <property name="user" value="${db.user}"/>
  <!-- 定义连接密码 -->
  <property name="password" value="${db.password}"/>
  <!-- 配置项目之中最大的数据库可用连接数 -->
  <property name="maxPoolSize" value="${db.maxPoolSize}"/>
  <!-- 当数据库连接已满时，其它线程最大等待时间 -->
  <property name="maxIdleTime" value="${db.maxIdleTime}"/>
  <!-- 最小的数据库连接可用数，即：如果没有人连接，保持的连接数量 -->
  <property name="minPoolSize" value="${db.minPoolSize}"/>
  <!-- 项目刚刚运行之后的连接数量 -->
  <property name="initialPoolSize" value="${db.initialPoolSize}"/>
</bean>

5、编写测试代码

package cn.liang.test;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestDS {
  public static void main(String[] args) {
      ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
      DataSource source = ctx.getBean("dataSource", DataSource.class);
      try {
          System.out.println(source.getConnection());
      } catch (SQLException e) {
          e.printStackTrace();
      }
  }
}

6、输出结果

2018-12-06 14:31:44,405 INFO [com.mchange.v2.log.MLog] - MLog clients using log4j logging.
2018-12-06 14:31:44,504 INFO [com.mchange.v2.c3p0.C3P0Registry] - Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2018-12-06 14:31:44,710 INFO [com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource] - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kflt9z104oj045e3oij|7692d9cc, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt9z104oj045e3oij|7692d9cc, idleConnectionTestPeriod -> 0, initialPoolSize -> 1, jdbcUrl -> jdbc:mysql://10.123.209.31:3306/springdb, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 1, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
com.mchange.v2.c3p0.impl.NewProxyConnection@1623b78d

使用Spring的JDBC实现CRUD操作

1、通过依赖注入的关系实现DataSource对象的注入控制

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="dataSource"/>
</bean>

2、数据追加

package cn.liang.test;
import java.util.Date;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
public class TestDS {
  public static void main(String[] args) {
      ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
      JdbcTemplate jTemplate = ctx.getBean("jdbcTemplate", JdbcTemplate.class);
      String sql = "INSERT INTO news(title,pubdate,note) VALUES (?,?,?)" ;
      int len = jTemplate.update(sql, "titletest",new Date(),"contextliang") ;
      System.out.println("修改行数："+ len);
  }
}

输出结果

2018-12-06 14:40:32,188 INFO [com.mchange.v2.log.MLog] - MLog clients using log4j logging.
2018-12-06 14:40:32,278 INFO [com.mchange.v2.c3p0.C3P0Registry] - Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2018-12-06 14:40:32,466 INFO [com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource] - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kflt9z104zu8mj6lui9|3967e60c, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt9z104zu8mj6lui9|3967e60c, idleConnectionTestPeriod -> 0, initialPoolSize -> 1, jdbcUrl -> jdbc:mysql://10.123.209.31:3306/springdb, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 1, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
修改行数：1

3、自动获取自动增长列当前值

使用低版本的数据库驱动程序

<dependency> 
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.0.8</version>
</dependency>

测试代码

package cn.liang.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
public class TestDS3 {
  public static void main(String[] args) {
      ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
      JdbcTemplate jTemplate = ctx.getBean("jdbcTemplate", JdbcTemplate.class);
      String sql = "INSERT INTO news(title,pubdate,note) VALUES (?,?,?)" ;
      PreparedStatementCreator pCreator = new PreparedStatementCreator() {
          
          @Override
          public PreparedStatement createPreparedStatement(Connection arg0) throws SQLException {
              PreparedStatement pStatement = arg0.prepareStatement(sql);
              pStatement.setString(1, "titletest2");
              pStatement.setDate(2, new java.sql.Date(new Date().getTime()));
              pStatement.setString(3, "contextliang2");
              return pStatement;
          }
      };
      KeyHolder keyHolder = new GeneratedKeyHolder();
      int len = jTemplate.update(pCreator,keyHolder) ;
      System.out.println("修改行数："+ len);
  }
}

输出结果

2018-12-06 14:48:51,489 INFO [com.mchange.v2.log.MLog] - MLog clients using log4j logging.
2018-12-06 14:48:51,601 INFO [com.mchange.v2.c3p0.C3P0Registry] - Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2018-12-06 14:48:51,816 INFO [com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource] - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kflt9z105aji71ay4jql|3967e60c, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt9z105aji71ay4jql|3967e60c, idleConnectionTestPeriod -> 0, initialPoolSize -> 1, jdbcUrl -> jdbc:mysql://10.123.209.31:3306/springdb, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 1, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
修改行数：1

4、数据分页查询

定义News的vo类

package cn.liang.vo;
import java.util.Date;
public class News {
  private Integer nid ;
  private String title ;
  private Date pubdate ;
  private String note ;
  public Integer getNid() {
      return nid;
  }
  public void setNid(Integer nid) {
      this.nid = nid;
  }
  public String getTitle() {
      return title;
  }
  public void setTitle(String title) {
      this.title = title;
  }
  public Date getPubdate() {
      return pubdate;
  }
  public void setPubdate(Date pubdate) {
      this.pubdate = pubdate;
  }
  public String getNote() {
      return note;
  }
  public void setNote(String note) {
      this.note = note;
  }
  
  @Override
  public String toString() {
      return "News [nid=" + nid + ", title=" + title + ", pubdate=" + pubdate + ", note=" + note + "]";
  }
}

测试代码：

package cn.liang.test;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import cn.liang.vo.News;
public class TestDS4 {
  public static void main(String[] args) {
      ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
      JdbcTemplate jt = ctx.getBean("jdbcTemplate", JdbcTemplate.class);
      int currentPage = 2;
      int lineSize = 5;
      
      String sql = "SELECT nid,title,pubdate,note FROM news WHERE title LIKE ? LIMIT ?,?";
      Object object[] = new Object[] { "%%", (currentPage - 1) * lineSize, lineSize } ;
      RowMapper<News> rowMapper = new RowMapper<News>() {
          @Override
          public News mapRow(ResultSet rs, int rowNum) throws SQLException {
              News vo = new News();
              vo.setNid(rs.getInt(1));
              vo.setTitle(rs.getString(2));
              vo.setPubdate(rs.getDate(3));
              vo.setNote(rs.getString(4));
              return vo;
          }
      };
      List<News> all = jt.query(sql, object, rowMapper);
      System.out.println("结果输出："+ all); 
  }
}

输出结果：

2018-12-06 15:00:05,128 INFO [com.mchange.v2.log.MLog] - MLog clients using log4j logging.
2018-12-06 15:00:05,229 INFO [com.mchange.v2.c3p0.C3P0Registry] - Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2018-12-06 15:00:05,473 INFO [com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource] - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kflt9z105ozaggnhuk0|3967e60c, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> org.gjt.mm.mysql.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kflt9z105ozaggnhuk0|3967e60c, idleConnectionTestPeriod -> 0, initialPoolSize -> 1, jdbcUrl -> jdbc:mysql://10.123.209.31:3306/springdb, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 1, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 1, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
结果输出：[News [nid=6, title=titletest, pubdate=2018-12-06, note=contextliang], News [nid=7, title=titletest, pubdate=2018-12-06, note=contextliang], News [nid=8, title=titletest, pubdate=2018-12-06, note=contextliang], News [nid=9, title=titletest, pubdate=2018-12-06, note=contextliang], News [nid=10, title=titletest, pubdate=2018-12-06, note=contextliang]]

spring 14-Spring框架JDBC操作

标签：idle   default   key   etc   数据库连接池   分页   url   获取   rownum   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！