自写的jdbc模板
实现增删改查：
AccountDao.java ---模板
package cn.lcp.dao;
import java.sql.SQLException;
import java.util.List;
import cn.lcp.domain.Account;
import cn.lcp.utils.BeanHandler;
import cn.lcp.utils.BeanListHandler;
public class AccountDao {
public void add(Account account) throws SQLException{
String sql = "insert into account(name,money) values(?,?)";
Object params[] = {account.getName(),account.getMoney()};
JdbcUtils.update(sql, params);
}
public void delete(int id) throws SQLException{
String sql = "delete from account where id=?";
Object params[] = {id};
JdbcUtils.update(sql, params);
}
public void update(Account account) throws SQLException{
String sql = "update account set name=?,money=? where id=?";
Object params[] = {account.getName(),account.getMoney(),account.getId()};
JdbcUtils.update(sql, params);
}
public Account find(int id) throws SQLException{
String sql = "select * from account where id=?";
Object params[] = {id};
return (Account) JdbcUtils.query(sql, params, new BeanHandler(Account.class));
}
public List getAll() throws SQLException{
String sql = "select * from account";
Object params[] = {};
return (List) JdbcUtils.query(sql, params,new BeanListHandler(Account.class));
}
}
JdbcUtils.java ---模板
package cn.lcp.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import cn.lcp.utils.ResultSetHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JdbcUtils {
private static ComboPooledDataSource ds = null;
static{
try{
ds = new ComboPooledDataSource("lcp");
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException{
return ds.getConnection();
}
public static void release(Connection conn,Statement st,ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if(st!=null){
try{
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
//替换dao中的增删改方法
public static void update(String sql,Object params[]) throws SQLException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = getConnection();
//预编译
st = conn.prepareStatement(sql);
for(int i=0;i<params.length;i++){
st.setObject(i+1, params[i]);
}
st.executeUpdate();
}finally{
release(conn, st, rs);
}
}
//替换所有dao中的查询 策略模式
public static Object query(String sql,Object params[],ResultSetHandler rsh) throws SQLException{
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
conn = getConnection();
st = conn.prepareStatement(sql);
for(int i=0;i<params.length;i++){
st.setObject(i+1, params[i]);
}
rs = st.executeQuery();
//结果集处理器
return rsh.handler(rs);
}finally{
release(conn, st, rs);
}
}
}
Account.java
package cn.lcp.domain;
public class Account {
private int id;
private String name;
private float 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 float getMoney() {
return money;
}
public void setMoney(float money) {
this.money = money;
}
}
BeanHandler.java ---模板
package cn.lcp.utils;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class BeanHandler implements ResultSetHandler {
private Class clazz;
public BeanHandler(Class clazz){
this.clazz = clazz;
}
public Object handler(ResultSet rs) {
try{
if(!rs.next()){
return null;
}
Object bean = clazz.newInstance();
ResultSetMetaData metadata = rs.getMetaData();
int columnCount = metadata.getColumnCount(); //得到结果集中有几列数据
for(int i=0;i<columnCount;i++){
String coulmnName = metadata.getColumnName(i+1); //得到每列的列名
Object coulmnData = rs.getObject(i+1);
Field f = clazz.getDeclaredField(coulmnName);//反射出类上列名对应的属性
f.setAccessible(true);
f.set(bean, coulmnData);
}
return bean;
}catch (Exception e) {
throw new RuntimeException(e);
}
}
}
BeanListHandler.java ---模板
package cn.lcp.utils;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
public class BeanListHandler implements ResultSetHandler {
private Class clazz;
public BeanListHandler(Class clazz){
this.clazz = clazz;
}
public Object handler(ResultSet rs) {
try{
List list = new ArrayList();
while(rs.next()){
Object bean = clazz.newInstance();
ResultSetMetaData metadata = rs.getMetaData();
int count = metadata.getColumnCount();
for(int i=0;i<count;i++){
String name = metadata.getColumnName(i+1);
Object value = rs.getObject(name);
Field f = bean.getClass().getDeclaredField(name);
f.setAccessible(true);
f.set(bean, value);
}
list.add(bean);
}
return list.size()>0?list:null;
}catch (Exception e) {
throw new RuntimeException(e);
}
}
}
ResultSetHandler.java ---模板
package cn.lcp.utils;
import java.sql.ResultSet;
public interface ResultSetHandler {
public Object handler(ResultSet rs);
}
c3p0-config.xml ---模板
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
-->
<named-config name="lcp">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
<property name="user">root</property>
<property name="password">lcp8090</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
<!--
<named-config name="oracle">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
-->
</c3p0-config>
demo.java
package cn.lcp.demo;
import java.sql.SQLException;
import java.util.List;
import cn.lcp.dao.AccountDao;
public class Demo7 {
/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
AccountDao dao = new AccountDao();
List list = dao.getAll();
System.out.println(list.size());
}
}
