Spring-JDBC

关联Jar包

若通过c3p0访问jdbc，则需要添加以下jar包

1）mchange-commons-java.jar

http://mvnrepository.com/artifact/com.mchange/mchange-commons-java/

2）c3p0-0.9.5.2.jar

http://mvnrepository.com/artifact/com.mchange/c3p0/

3)mysql-connector-java-5.1.22-bin.jar

4)spring-jdbc-4.2.6.RELEASE.jar

配置c3p0.properties

user=root
password=1234
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/high

initialPoolSize=5
maxPoolSize=10

配置jdbcconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">
	<context:property-placeholder location="c3p0.properties"/>
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${user}"></property>
		<property name="password" value="${password}"></property>
		<property name="driverClass" value="${driverClass}"></property>
		<property name="jdbcUrl" value="${jdbcUrl}"></property>
		<property name="initialPoolSize"  value="${initialPoolSize}"></property>
		<property name="maxPoolSize" value="${maxPoolSize}"></property>
	</bean>
</beans>

测试jdbc连接 JDBCTest.java

public class JDBCTest {
	public static void main(String[] args) {
		ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcconfig.xml");
        DataSource dataSource = ctx.getBean("dataSource", ComboPooledDataSource.class);  
        try {  
            System.out.println(dataSource.getConnection());  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } 
		
		//JdbcTemplate为线程安全类
		JdbcTemplate jdbcTemplate = ctx.getBean("jdbcTemplate", JdbcTemplate.class);
		
		//插入，更新，删除通用。
		String sql = "update student set name='high2' where id=?";
		jdbcTemplate.update(sql, 27);
		
		//批量插入举例,通过将参数放入list中，进行批量操作
		String insertSql = "insert into student (name, age) values(?, ?)";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		batchArgs.add(new Object[]{"lily", 20});
		batchArgs.add(new Object[]{"lucy", 21});
		
		jdbcTemplate.batchUpdate(insertSql, batchArgs);
		
		//查询一个对象,当查询的字段和类的属性不同时，可以使用别名，相同时则不需要
		String querySql = "select id as id, name, age from student where id=?";
		RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);
		Student student = jdbcTemplate.queryForObject(querySql, rowMapper, 30);
		System.out.println(student);
		
		//查询一个Scalar数据
		String querySqlForScalar = "select name from student where id=?";
		String name = jdbcTemplate.queryForObject(querySqlForScalar, String.class, 30);
		System.out.println(name);
		
		//查询一个列表
		String querySqlForList = "select id as id, name, age from student where id<?";
		RowMapper<Student> rowMapper2 = new BeanPropertyRowMapper<Student>(Student.class);
		List<Student> students = jdbcTemplate.query(querySqlForList, rowMapper2, 30);
		System.out.println(students);
		
		//做统计查询
		String querySqlForCount = "select count(name) from student where name=?";
		long count = jdbcTemplate.queryForObject(querySqlForCount, Long.class, "high");
		System.out.println(count);
	}
}

NamedParameterJdbcTemplate

NamedParameterJdbcTemplate翻译为中文是具名参数，即具有名字的参数，他是对JdbcTemplate的封装，所以，JdbcTemplate具有的功能他都有。

下面直接引用Spring官网的例子，可见带有名字的参数虽然比上面的问号要麻烦一点。

但是在使用的时候可以直接使用名字，这样在代码可读性方面，检查正确性方面相对比较好。

// some JDBC-backed DAO class...
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}

public int countOfActors(Actor exampleActor) {

    // notice how the named parameters match the properties of the above 'Actor' class
    String sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";

    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);

    return this.namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
}

<完>