13、DBUtilsを使用してデータベースを操作する

1、DBUtilsは、jdbcの操作をカプセル化する.jdbcの操作を簡素化
2、実装されたMyDBUtilsではC 3 P 0データベースが使用されており、C 3 P 0のプロファイルは以下の通りである

<?xml version="1.0" encoding="UTF-8"?>


<c3p0-config>
	<default-config name="mysql">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">
			jdbc:mysql://localhost:3306/testdatabase
		</property>
		<property name="user">root</property>
		<property name="password">1234</property>

		<property name="acquireIncrement">5</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>


	</default-config>



</c3p0-config>

3、実体クラスUserのコード
       

package com.jdbc.entity;

public class User {
	private int id;
	private String uname;
	private int age;

	public User() {
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getUname() {
		return uname;
	}

	public void setUname(String uname) {
		this.uname = uname;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "User [id=" + id + ", uname=" + uname + ", age=" + age + "]";
	}

}

4、簡略化されたMyDBUtilsのコードは以下の通りである.
     

package com.jdbc.dbutils;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class MyDBUtils {
	/**
	 *   C3P0      ,          ,    
	 */
	private static ComboPooledDataSource dataSource = null;

	/**
	 *       GenericityJDBCUtils    ,  C3P0,   dataSource
	 *     dataSource,              
	 */
	static {
		try {
			dataSource = new ComboPooledDataSource("mysql");
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 *            dataSource
	 */
	public static DataSource getDataSource() {
		return dataSource;
	}

	/**
	 *           
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

}

5、UserDaoを利用してデータベースのコードを操作し、以下のように実現する
    

package com.jdbc.dbutils.userdao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.jdbc.dbutils.MyDBUtils;
import com.jdbc.entity.User;

/**
 *     user 
 * 
 */
public class UserDao {
	/**
	 *     id     
	 * 
	 * @param id
	 * @return
	 */
	public User findById(int id) {
		QueryRunner qr = null;
		User user = null;
		try {
			qr = new QueryRunner(MyDBUtils.getDataSource());
			String sql = "select * from user where id = ?";
			user = (User) qr.query(sql, id, new BeanHandler(User.class));
			return user;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 *        
	 * 
	 * @return
	 */
	public List<User> findAll() {
		QueryRunner qr = null;
		List<User> users = new ArrayList<User>();
		try {
			qr = new QueryRunner(MyDBUtils.getDataSource());
			String sql = "select * from user ";
			users = (List<User>) qr.query(sql, new BeanListHandler(User.class));
			return users;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 *            
	 * 
	 * @param start
	 * @param size
	 * @return
	 */
	public List<User> findFenYe(int start, int size) {
		QueryRunner qr = null;
		List<User> users = new ArrayList<User>();
		try {
			qr = new QueryRunner(MyDBUtils.getDataSource());
			String sql = "select * from user limit ? , ? ";
			users = (List<User>) qr.query(sql, new Object[] { start, size },
					new BeanListHandler(User.class));
			return users;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 *           
	 * 
	 * @param user
	 */
	public void add(User user) {
		QueryRunner qr = null;
		try {
			qr = new QueryRunner(MyDBUtils.getDataSource());
			String sql = "insert into user( uname , age ) values( ? , ? ) ";
			qr.update(sql, new Object[] { user.getUname(), user.getAge() });
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 *   sql paras      
	 * 
	 * @param sql
	 * @param paras
	 */
	public void update(String sql, Object[] paras) {
		QueryRunner qr = null;
		try {
			qr = new QueryRunner(MyDBUtils.getDataSource());
			qr.update(sql, paras);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

}

6、テストコードは以下の通り
       

package com.jdbc.dbutils.userdao;

import java.util.List;

import org.junit.Test;

import com.jdbc.entity.User;

public class UserDaoTest {

	@Test
	public void testFindById() {
		UserDao userDao = new UserDao();
		User user = userDao.findById(1);
		System.out.println(user);
	}

	@Test
	public void testFindAll() {
		UserDao userDao = new UserDao();
		List<User> users = userDao.findAll();
		for (User user : users)
			System.out.println(user);
	}

	@Test
	public void testFindFenYe() {
		UserDao userDao = new UserDao();
		List<User> users = userDao.findFenYe(0, 2);
		for (User user : users)
			System.out.println(user);
	}

	@Test
	public void testAdd() {
		UserDao userDao = new UserDao();
		User user = new User();
		user.setAge(18);
		user.setUname("  ");
		userDao.add(user);
	}

	@Test
	public void testDelete() {
		UserDao userDao = new UserDao();
		String sql = "delete from user where id = ? ";
		Object[] paras = { 4 };
		userDao.update(sql, paras);
	}

	@Test
	public void testUpdate() {
		UserDao userDao = new UserDao();
		String sql = "update user set uname = ? where id = ? ";
		Object[] paras = { "haha", 5 };
		userDao.update(sql, paras);
	}
}