spring-jdbc访问数据库

介绍jdbc访问数据库

依赖：

spring-context
spring-jdbc
c3p0
mysql-connector-java

详细：

User.java:

package com.shuoeasy.test;

public class User {
	private int id;
	private String name;
	private double money;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public double getMoney() {
		return money;
	}
	public void setMoney(double money) {
		this.money = money;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", money=" + money + "]";
	}
	
}

JDBCTest.java:

package com.shuoeasy.test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import junit.framework.TestCase;

public class JDBCTest extends TestCase {
	
	private ApplicationContext ctx = null;
	private JdbcTemplate jdbcTemplate;
	
	{
		this.ctx = new ClassPathXmlApplicationContext("bean.xml");
		this.jdbcTemplate = (JdbcTemplate) this.ctx.getBean("jdbcTemplate");
	}
	
	/**
	 * 测试连接数据库
	 */
	@Test
	public void testDataSource() throws SQLException{
		DataSource dataSource = (DataSource) ctx.getBean("dataSource");
		
		System.out.println(dataSource.getConnection());
	}
	
	/**
	 * 测试修改一条数据
	 */
	@Test
	public void testUpdate(){
		String sql = "UPDATE user SET money=money+1 WHERE id=?";
		jdbcTemplate.update(sql,1);
	}
	
	/**
	 * 测试插入修改jdbcTemplate
	 */
	@Test
	public void testBatchInsert(){
		System.out.println("批量插入数据");
		String sql = "INSERT INTO user(name,money) VALUES(?,?)";
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		
		batchArgs.add(new Object[]{"用户1",0});
		batchArgs.add(new Object[]{"用户2",0});
		batchArgs.add(new Object[]{"用户3",0});
		
		int[] num = jdbcTemplate.batchUpdate(sql,batchArgs);
		System.out.println("受影响行数：" + Arrays.asList(num));
		
	}
	
	/**
	 * 从数据库中获取一条记录，并返回对象
	 */
	@Test
	public void testQueryForObject(){
		String sql = "SELECT id,name,money FROM user WHERE id=?";
		BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
		User user = jdbcTemplate.queryForObject(sql, rowMapper, 1);
		System.out.println("user:" + user);
	}
	
	/**
	 * 获取集合
	 */
	@Test
	public void testQueryForList(){
		String sql = "SELECT id,name,money FROM user WHERE id>?";
		BeanPropertyRowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
		 
		List<User> users = jdbcTemplate.query(sql,rowMapper,1);
		System.out.println("users:" + users.size());
	}
	
	/**
	 * 获取一个值，例如总数
	 */
	@Test
	public void testQueryCount(){
		String sql = "SELECT count(*) FROM user";
		long count = jdbcTemplate.queryForObject(sql, long.class);
		System.out.println("count:" + count);
	}
}

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.shuoeasy</groupId>
  <artifactId>test</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>test</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>4.2.6.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>4.2.6.RELEASE</version>
    </dependency>
    <dependency>
	    <groupId>c3p0</groupId>
	    <artifactId>c3p0</artifactId>
	    <version>0.9.1.2</version>
	</dependency>
	<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.2</version>
</dependency>
  </dependencies>
</project>

db.properties:

jdbc.user=root
jdbc.password=111111
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false

jdbc.initialPoolSize=5
jdbc.maxPoolSize=5

bean.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:p="http://www.springframework.org/schema/p"
	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="classpath:db.properties"/>
	
	<!-- 配置C3P0数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>

		<property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property>
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>	
	</bean>

	<!-- 配置spring的JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
</beans>