java spring框架 入门，spring 14-Spring框架JDBC操作

传统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() {@Overridepublic 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;}@Overridepublic 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>() {@Overridepublic 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]]