jdbc入门到精通4实操完整封装jdbc

前言:

本次实战是对一张用户表进行业务操作,在业务实现的过程中,对jdbc部分冗余的代码进行重构并达到最优设计.
本实战遵循#开闭原则,涉及工厂设计模式
封装完成#创建,删除,修改,查一条,查所有

[1]创建table和配制文件

/cn/net/trimmer/jdbc/sql/jdbc-max-demo.sql

-- Create table
create table T_USER(
  id       NUMBER(10) not null,
  name     VARCHAR2(11),
  password VARCHAR2(11),
  sex      VARCHAR2(8),
  birthday DATE
);
-- Add comments to the table 
comment on table T_USER is 'baizhi jdbc的测试项目用的表';
-- Create/Recreate primary, unique and foreign key constraints 
alter table T_USER add constraint ID primary key (ID);
-- Create sequence 
create sequence SEQ_USER
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

--------------------------------------------------------------------
insert into T_USER (ID, NAME, PASSWORD, SEX, BIRTHDAY)
values (41, '张无忌', '121212', '男', to_date('30-05-2020', 'dd-mm-yyyy'));

insert into T_USER (ID, NAME, PASSWORD, SEX, BIRTHDAY)
values (43, '百晓生', '123456', '男', to_date('30-05-2020', 'dd-mm-yyyy'));

/cn/net/trimmer/jdbc/conf/db.properties

jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.230.10:1521:orcl
jdbc.username=username
jdbc.password=password

/cn/net/trimmer/jdbc/conf/factory.properties

userDAO = cn.net.trimmer.jdbc.dao.impl.UserDAOImpl
userService =cn.net.trimmer.jdbc.service.impl.UserServiceImpl

[2]封装实体类引入工具类

User.java

package cn.net.trimmer.jdbc.entity;

import java.util.Date;
import java.text.SimpleDateFormat;

/**
 * 用户表
 * 
 * @author wl
 */
public class User {
	private int id;
	private String name;
	private String password;
	private String sex;
	private Date birthday;
	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 String getPassword() {return password;}
	public void setPassword(String password) {this.password = password;}
	public String getSex() {return sex;}
	public void setSex(String sex) {this.sex = sex;}
	public void setBirthday(Date birthday) {this.birthday = birthday;}
	public java.sql.Date getBirthday() {
		//时间处理
		if (birthday != null) {
			return new java.sql.Date(birthday.getTime());
		}
		return null;
	}
	public void setBirthday(String birthday) {
		//时间格式化
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			this.birthday = sdf.parse(birthday);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	public User() {super();}
	public User(String name, String password, String sex, Date birthday) {
		this.name = name;
		this.password = password;
		this.sex = sex;
		this.birthday = birthday;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", password=" + password + ", sex=" + sex + ", birthday="
				+ birthday + "]";
	}
}

JdbcUtils.java

package cn.net.trimmer.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * jdbc 工具类简单抽取
 * 
 * @author wl
 *
 */
public class JdbcUtils {
	private static final Properties prop = new Properties();
	private static final ThreadLocal<Connection> tol = new ThreadLocal<>();
	// 类加载时读取配置文件
	static {
		InputStream in = null;
		try {
			// 1. 从类路径加载配置文件
			in = JdbcUtils.class.getResourceAsStream("/cn/net/trimmer/jdbc/conf/db.properties");
			// 2. 解析配置文件
			prop.load(in);
			System.out.println("数据库配置文件加载完成...");
		} catch (IOException e) {
			e.printStackTrace();
			if (in != null) {
				try {
					in.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}
			}
		}
	}

	/**
	 * 获取数据库连接对象
	 * 
	 * @return conn 数据库连接对象
	 */
	public static Connection getConn() {
		// 从线程局部变量中取连接对象
		Connection conn = tol.get();
		try {
			// 连接对象不存在则创建
			if (conn == null) {
				String driver = prop.getProperty("oracle.driver");
				String url = prop.getProperty("oracle.url");
				String username = prop.getProperty("oracle.username");
				String password = prop.getProperty("oracle.password");
				Class.forName(driver);
				conn = DriverManager.getConnection(url, username, password);
				// 存入连接对象到线程局部变量中
				tol.set(conn);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return conn;
	}

	/**
	 * 释放资源
	 * 
	 * @param rs   执行结果集
	 * @param stm  sql操作对象
	 * @param conn 数据库连接
	 */
	public static void release(ResultSet rs, Statement stm, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		if (stm != null) {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		if (conn != null) {
			try {
				// 关闭连接
				conn.close();
				// 清除线程局部变量
				tol.remove();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
	}
}

Factory.java

package cn.net.trimmer.jdbc.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * 简单工厂,用于管理类的创建
 * 
 * @author wl
 *
 * @param <T> 被工厂管理的类的类型
 */
public class Factory<T> {
	private static final Properties p = new Properties();
	static {
		InputStream is = null;
		try {
			// 加载并解析工厂配置文件
			is = JdbcUtils.class.getResourceAsStream("/cn/net/trimmer/jdbc/conf/factory.properties");
			p.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 通过类名创建类
	 * 
	 * @param className 类名称
	 * @return 类加载后的实例
	 */
	@SuppressWarnings("unchecked")
	public T createBean(String className) {
		T object = null;
		try {
			// 从配置文件中读取该类名称对应的全类名,通过反射获取类对象
			Class<?> c = Class.forName(p.getProperty(className));
			// 实例化类对象
			object = (T) c.newInstance();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return object;
	}
}

TransactionManager.java

package cn.net.trimmer.jdbc.util;

import java.sql.Connection;

/**
 * 事务管理类
 * 
 * @author wl
 */
public class TransactionManager {
	/**
	 * 关闭自动提交
	 */
	public static void begin() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConn();
			conn.setAutoCommit(false);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	/**
	 * 提交事务
	 */
	public static void commit() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConn();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.release(null, null, conn);
		}
	}

	/**
	 * 回滚事务
	 */
	public static void rollback() {
		Connection conn = null;
		try {
			conn = JdbcUtils.getConn();
			conn.rollback();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			JdbcUtils.release(null, null, conn);
		}
	}
}

RowMapper.java

package cn.net.trimmer.jdbc.util;

import java.sql.ResultSet;
/**
 * 行映射接口
 * @author wl
 *
 * @param <T> 映射类类型
 */
public interface RowMapper<T> {
	public T mapRow(ResultSet rs);
}

jdbcTemplate.java

package cn.net.trimmer.jdbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * jdbc模板,封装增删改查方法
 * 
 * @author wl
 *
 * @param <T> 对象类型
 */
public class jdbcTemplate<T> {
	
	/**
	 * 查询一条
	 * @param sql sql集
	 * @param rowMapper 结果集映射对象
	 * @param args 参数表
	 * @return 结果集对象
	 */
	public T query(String sql, RowMapper<T> rowMapper, Object... args) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		T list = null;
		try {
			//获取连接			
			conn = JdbcUtils.getConn();
			//创建操作对象
			pst = conn.prepareStatement(sql);
			//给占位符赋值
			if (hasUpdateParam(args)) {
				for (int i = 0; i < args.length; i++) {
					pst.setObject(i + 1, args[i]);
				}
			}
			//执行sql
			rs = pst.executeQuery();
			//处理结果集
			if (rs.next()) {
				list = rowMapper.mapRow(rs);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			//释放资源
			JdbcUtils.release(rs, pst, null);
		}
		return list;
	}

	/**
	 * 修改一条数据
	 * @param sql sql语句
	 * @param args 参数集
	 */
	public void update(String sql, Object... args) {
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			//获取连接对象
			conn = JdbcUtils.getConn();
			//创建sql操作对象
			pst = conn.prepareStatement(sql);
			//给占位符赋值
			if (hasUpdateParam(args)) {
				for (int i = 0; i < args.length; i++) {
					pst.setObject(i + 1, args[i]);
				}
			}
			//执行sql
			pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			//释放资源
			JdbcUtils.release(null, pst, null);
		}
	}

	/**
	 * 查询所有
	 * @param sql sql语句
	 * @param rowMapper 结果集映射对象
	 * @param args 参数集
	 * @return 结果集集合
	 */
	public List<T> queryAll(String sql, RowMapper<T> rowMapper, Object... args) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		List<T> lists = null;
		try {
			//获取连接
			conn = JdbcUtils.getConn();
			//创建操作对象
			pst = conn.prepareStatement(sql);
			//给占位符赋值
			if (hasUpdateParam(args)) {
				for (int i = 0; i < args.length; i++) {
					pst.setObject(i + 1, args[i]);
				}
			}
			//执行sql
			rs = pst.executeQuery();
			//处理结果集
			lists = new ArrayList<>();
			while (rs.next()) {
				T list = rowMapper.mapRow(rs);				
				lists.add(list);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			//释放资源
			JdbcUtils.release(rs, pst, null);
		}
		return lists;
	}
	//参数长度判断
	private boolean hasUpdateParam(Object... args) {
		return args.length != 0;
	}
}

[3]定义DAO接口

UserDAO.java

package cn.net.trimmer.jdbc.dao;


import java.util.List;

import cn.net.trimmer.jdbc.entity.User;

public interface UserDAO {
	/**
	 * 根据用户名和密码查询用户信息
	 * @param name 用户名
	 * @param password 密码
	 * @return 是否查询成功标识符
	 */
	public boolean queryUser(String name,String password);
	/**
	 * 保存用户信息
	 * @param user 用户信息
	 */
	public void save(User user);
	/**
	 * 查询所有用户信息
	 * @return 用户信息集合
	 */
	public List<User> queryAllUser();
	/**
	 * 通过id删除用户信息
	 * @param id 用户编号
	 */
	public void deleteById(int id);
	/**
	 * 修改用户信息
	 * @param user 用户信息
	 */
	public void update(User user);
	/**
	 * 通过id查询用户信息
	 * @param id 用户编号
	 * @return 用户信息
	 */
	public User querUserById(int id);
}

[4]写DAO和RowMapper接口的实现

UserDAOImpl.java

package cn.net.trimmer.jdbc.dao.impl;

import java.util.List;

import cn.net.trimmer.jdbc.dao.UserDAO;
import cn.net.trimmer.jdbc.entity.User;
import cn.net.trimmer.jdbc.util.jdbcTemplate;

/**
 * 用户信息dao
 * 
 * @author wl
 *
 */
public class UserDAOImpl implements UserDAO {
	private jdbcTemplate<User> jdbcTemplate = new jdbcTemplate<User>();

	@Override
	public boolean queryUser(String name, String password) {
		String sql = "select * from t_user where name = ? and password = ?";
		User user = jdbcTemplate.query(sql, new UserRowMapper(), name, password);
		if (user == null) {
			return false;
		}
		return true;
	}

	@Override
	public void save(User user) {
		String sql = "insert into t_user (id,name,password,sex,birthday) values( seq_user.nextval,?,?,?,?)";
		jdbcTemplate.update(sql, user.getName(), user.getPassword(), user.getSex(), user.getBirthday());
	}

	@Override
	public List<User> queryAllUser() {
		String sql = "select id,name,password,sex,birthday from t_user";
		List<User> lists = jdbcTemplate.queryAll(sql, new UserRowMapper());
		return lists;
	}

	@Override
	public void deleteById(int id) {
		String sql = "delete from t_user where id = ? ";
		jdbcTemplate.update(sql, id);
	}

	@Override
	public void update(User user) {
		String sql = "update t_user set name =?,password=?,sex=?,birthday=? where id = ? ";
		jdbcTemplate.update(sql, user.getName(), user.getPassword(), user.getSex(), user.getBirthday(), user.getId());
	}

	@Override
	public User querUserById(int id) {
		String sql = "select id,name,password,sex,birthday from t_user where id =?";
		User user = jdbcTemplate.query(sql, new UserRowMapper(), id);
		return user;
	}
}

UserRowMapper.java

package cn.net.trimmer.jdbc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import cn.net.trimmer.jdbc.entity.User;
import cn.net.trimmer.jdbc.util.RowMapper;

/**
 * 用户表映射
 * 
 * @author wl
 */
public class UserRowMapper implements RowMapper<User> {
	
	// 结果集处理
	@Override
	public User mapRow(ResultSet rs) {
		User user = new User();
		try {
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setSex(rs.getString(4));
			user.setBirthday(rs.getDate(5));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return user;
	}
}

[5]定义service接口

UserService.java

package cn.net.trimmer.jdbc.service;


import java.util.List;

import cn.net.trimmer.jdbc.entity.User;

public interface UserService {
	/**
	 * 登录
	 * @param name 用户名
	 * @param password 密码
	 * @return 是否登录成功标识
	 */
	boolean login(String name,String password);
	/**
	 * 注册
	 * @param user 用户信息
	 */
	void register(User user);
	/**
	 * 展示用户
	 * @return 用户信息集合
	 */
	List<User> showUser();
	/**
	 * 注销
	 * @param id 用户编号
	 */
	void remove(int id);
	/**
	 * 更改用户信息
	 * @param user 用户信息
	 */
	void modify(User user);
	/**
	 * 查询用户
	 * @param id 用户编号
	 * @return 用户信息
	 */
	User query(int id);
}	

[6]写service的实现

UserServiceImpl.java

package cn.net.trimmer.jdbc.service.impl;

import java.util.List;

import cn.net.trimmer.jdbc.dao.UserDAO;
import cn.net.trimmer.jdbc.entity.User;
import cn.net.trimmer.jdbc.service.UserService;
import cn.net.trimmer.jdbc.util.Factory;
import cn.net.trimmer.jdbc.util.TransactionManager;

public class UserServiceImpl implements UserService {
	Factory<UserDAO> f = new Factory<UserDAO>();
	UserDAO userDAO = f.createBean("userDAO");

	@Override
	public boolean login(String name, String password) {
		boolean flag = false;
		try {
			flag = userDAO.queryUser(name, password);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return flag;
	}

	@Override
	public void register(User user) {
		try {
			TransactionManager.begin();
			userDAO.save(user);
			TransactionManager.commit();
		} catch (Exception e) {
			e.printStackTrace();
			TransactionManager.rollback();
		}
	}

	@Override
	public List<User> showUser() {
		List<User> list = null;
		try {
			TransactionManager.begin();
			list = userDAO.queryAllUser();
			TransactionManager.commit();
		} catch (Exception e) {
			e.printStackTrace();
			TransactionManager.rollback();
		}
		return list;
	}

	@Override
	public void remove(int id) {
		try {
			TransactionManager.begin();
			userDAO.deleteById(id);
			TransactionManager.commit();
		} catch (Exception e) {
			e.printStackTrace();
			TransactionManager.rollback();
		}
	}

	@Override
	public void modify(User user) {
		try {
			TransactionManager.begin();
			userDAO.update(user);
			TransactionManager.commit();
		} catch (Exception e) {
			e.printStackTrace();
			TransactionManager.rollback();
		}

	}

	@Override
	public User query(int id) {
		User user = null;
		try {
			TransactionManager.begin();
			user = userDAO.querUserById(id);
			TransactionManager.commit();
		} catch (Exception e) {
			e.printStackTrace();
			TransactionManager.rollback();
		}
		return user;
	}
}

[7]单元测试

package cn.net.trimmer.jdbc.test;

import java.util.Date;
import java.util.List;

import org.junit.Test;

import cn.net.trimmer.jdbc.entity.User;
import cn.net.trimmer.jdbc.service.UserService;
import cn.net.trimmer.jdbc.util.Factory;

public class UserServiceImplTest {
	private Factory<UserService> f = new Factory<>();
	private UserService us = f.createBean("userService");
	
	@Test
	public void testRegister() {
		User user = new User("百晓生","123456","男",new Date());
		us.register(user );
	}
	
	@Test
	public void testLogin() {
		boolean login = us.login("百晓生", "123456");
		System.out.println(login);
	}

	

	@Test
	public void testShowUser() {
		List<User> list = us.showUser();
		for (User user : list) {
			System.out.println(user);
		}
	}

	@Test
	public void testRemove() {		
		us.remove(42);
	}

	@Test
	public void testModify() {
		User user = new User("张无忌","121212","男",new Date());
		user.setId(41);
		us.modify(user);
	}

	@Test
	public void testQuery() {
		User user = us.query(41);
		System.out.println(user);
	}

}

[8]demo代码下载地址

https://github.com/wanglei199809/jdbc-max-demo.git