数据库操作案例

整个案例的代码结构：

好吧，废话不多说，直接上代码！

1.表结构如下

/*员工表*/
create table t_employee{
	id_ int primary key auto_increment,
	name_ varchar(20) not null,
	dept_name varchar(30),
	hiredate date not null
);

/*薪水表*/
create table t_salary (
	id_ int primary key auto_increment,
	emp_id int,
	salary_ float not null
	foreign key(emp_id) references t_employee(id_)
);

2.实体类Employee

public class Employee {
	
	private Integer id;
	private String name;
	private String gender;
	private String deptName;
	private Date hireDate;
	
	public Employee() {
	}
	public Employee(String name, String gender, String deptName,Date hireDate) {
		this.name = name;
		this.gender = gender;
		this.deptName = deptName;
		this.hireDate = hireDate;
	}
	public Employee(Integer id, String name, String gender, String deptName,Date hireDate) {
		this.id = id;
		this.name = name;
		this.gender = gender;
		this.deptName = deptName;
		this.hireDate = hireDate;
	}

......省略 setXxx,getXxx方法

3.工具包中类的介绍

①Configuration类

/**
 * 读取连接数据库的配置信息
 * 配置信息说明如下：
 * ①默认配置文件位于src目录下的db.properties文件
 * ②配置文件内容格式：
 * driver_class=com.mysql.jdbc.Driver
 * url=jdbc\:mysql\://localhost\:3306/dbname
 * username=root
 * password=root
 * 
 * @author Geek_ymv
 */
public final class Configuration {
	/**
	 * 读取默认配置文件(位于src目录下的db.properties)
	 * @return
	 */
	public Map<String, String> configure() {
		return configure("/db.properties");
	}
	
	/**
	 * 指定配置文件
	 * @param resource
	 * @return
	 */
	private Map<String, String> configure(String resource){
		
		Map<String, String> dbInfos = new HashMap<String, String>();
		
		Class<?> clazz = Configuration.class;
		InputStream inStream = clazz.getResourceAsStream(resource);
		
		Properties properties = new Properties();
		try {
			properties.load(inStream);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if(null != inStream){
					inStream.close();
				}
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}

		String driver_class = properties.getProperty("driver_class");
		String url = properties.getProperty("url");
		String username = properties.getProperty("username");
		String password = properties.getProperty("password");
		
		dbInfos.put("driver_class", driver_class);
		dbInfos.put("url", url);
		dbInfos.put("username", username);
		dbInfos.put("password", password);

		return dbInfos;
	}
}

②DBSource类

public final class DBSource {
	
	private static DBSource source = new DBSource();
	private DBSource(){}
	
	private static Map<String, String> dbinfos = null;
	
	static{
		Configuration configuration = new Configuration();
		dbinfos = configuration.configure();
		try {
			Class.forName(dbinfos.get("driver_class"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static DBSource getInstance(){
		return source;
	}
	
	public Connection getConnection(){
		try {	
			return DriverManager.getConnection(dbinfos.get("url"), 	//
					dbinfos.get("username"), dbinfos.get("password"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}

}

③DBUtil类

public class DBUtil {
	
	/**
	 * 查询操作
	 * @param sql	要执行的SQL语句
	 * @param handler	类型转换
	 * @param paras 可变参数
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List queryObject(String sql, Convert2Object handler, Object... paras){
		
		List datas = new ArrayList();
		
		Connection ct = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		ct = DBSource.getInstance().getConnection();
		
		try {
			ct.setAutoCommit(false);
			
			ps = ct.prepareStatement(sql);
			
			//参数绑定
			if(null != paras && paras.length > 0){
				for(int i = 0; i < paras.length; i++){
					Object obj = paras[i];
					ps.setObject(i+1, obj);
				}
			}
			rs = ps.executeQuery();
			
			while(rs.next()) {
				Object obj = handler.convert(rs);
				datas.add(obj);
			}
			
			ct.commit();
			
		} catch (Exception e) {
			try {
				if(null != ct){
					ct.rollback();
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			close(ct, ps, rs);
		}
		
		return datas;
	}
	
	/**
	 * 增、删、改操作
	 * @param sql
	 * @param paras
	 * @return
	 */
	public static boolean execute(String sql, Object...paras){
		boolean res = true;
		
		Connection ct = null;
		PreparedStatement ps = null;

		ct = DBSource.getInstance().getConnection();
		try {
			ct.setAutoCommit(false);
			ps = ct.prepareStatement(sql);
			//参数绑定
			if(null != paras && paras.length > 0){
				for(int i = 0; i < paras.length; i++){
					Object obj = paras[i];
					ps.setObject(i+1, obj);
				}
			}
			
			ps.executeUpdate();
			
			ct.commit();
			
		} catch (Exception e) {
			res = false;
			try {
				if(null != ct){
					ct.rollback();
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			close(ct, ps, null);
		}
		
		return res;
	}
	
	
	/**
	 * 关闭资源
	 * @param ct
	 * @param ps
	 * @param rs
	 */
	private static void close(Connection ct, PreparedStatement ps, ResultSet rs) {
		try {
			if(null != rs){
				rs.close();
			}
			if(null != ps){
				ps.close();
			}
			if(null != ct){
				ct.close();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

④Convert2Object接口

/**
 * 定义将表中的一行记录转换成一个实体对象的接口
 * @author Geek_ymv
 */
public interface Convert2Object {
	
	public Object convert(ResultSet rs);
	
}

⑤DateUtil 日期处理工具类

public class DateUtil {
	
	private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
	
	/**
	 * 将日期对象转换成字符串
	 * @param date
	 * @return
	 */
	public static String convert2String(Date date){
		
		return format.format(date);
	}
	
	/**
	 * 将日期格式的字符串转换成日期
	 * @param source
	 * @return
	 */
	public static Date convert2Date(String source){
		try {
			return format.parse(source);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return null;
	}

}

db.properties文件内容

EmployeeService类

注：由于案例较小，就不写dao层了

public class EmployeeService {
	
	private Convert2Object handler = new Convert2Object(){
		@Override
		public Object convert(ResultSet rs) {
			
			try {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String gender = rs.getString(3);
				String deptName = rs.getString(4);
				Date hireDate = rs.getDate(5);
				
				Employee emp = new Employee(id, name, gender, deptName, hireDate);
				
				return emp;
				
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
	};
	
	
	/**
	 * 分页查询Employee信息
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Employee> queryEmpByPage(int pageNo, int pageSize){
		
		int startIndex = (pageNo-1) * pageSize;
		
		String sql = "select * from t_employee limit ?, ?";
		
		return (List<Employee>)DBUtil.queryObject(sql, handler, startIndex, pageSize);
	}
	
	
	/**
	 * 添加员工
	 * @param emp
	 * @return
	 */
	public boolean addEmp(Employee emp){
		
		String hireDate = DateUtil.convert2String(emp.getHireDate());
		
		String sql = "insert into t_employee(name_, gender_, dept_name, hiredate) values(?,?,?,?)";
		Object[] paras = {emp.getName(), emp.getGender(), emp.getDeptName(), hireDate};
		
		
		return DBUtil.execute(sql, paras);
	}
	
	
	
	
	/**
	 * 获得t_employee表中记录数
	 * @return
	 */
	public int rowCounts(){
		String sql = "select count(*) from t_employee";
		
		Integer rows = (Integer)DBUtil.queryObject(sql, new Convert2Object(){
			@Override
			public Object convert(ResultSet rs) {

				try {
					return rs.getInt(1);
					
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				return null;
			}
		}).get(0);
		
		return rows;
	}

}

Client测试类

public class Client {
	
	public static void main(String[] args) {
		
		EmployeeService service = new EmployeeService();
//		
//		
//		List<Employee> emps = service.queryEmpByPage(1, 3);
//		
//		
//		for(Employee e : emps) {
//			
//			System.out.println(e.getId());
//			
//		}

		Employee emp = new Employee("笑笑", "男", "信息部", new Date());
		service.addEmp(emp);
	}
}