Java经典封装JDBC模板(充分体现面向对象思想)

程序清单一览

bean类

package com.software.usermanager.bean;

public class Users {
	private String id;
	private String name;
	private String age;
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	

}

dao类

package com.software.usermanager.dao;


import com.software.usermanager.bean.Users;
import com.software.usermanager.util.PageModel;
import java.util.List;

public interface UsersDAO {
	public boolean insert(Users user);	//增
	public boolean delete(String id);   //单条删除
	public boolean delete(String[] userIds);  //批量删除
	public boolean update(Users user);  //修改
	public List<Users> query();     //全部查询
	public Users query(String id);  //单记录查询
	public PageModel query(int pageNo, int pageSize); //分页查询
	public PageModel query(int pageNo, int pageSize,String condition); //分页模糊查询
	public boolean Login(String name,String password); //登录

}

daoimpl类

package com.software.usermanager.dao;
import com.software.usermanager.util.OptTemplate;
import java.sql.ResultSet;
import java.util.List;

import com.software.usermanager.bean.Users;
import com.software.usermanager.util.*;

public class UsersDAOImpl implements UsersDAO {

	private OptTemplate optTemplate = null;

	public UsersDAOImpl(OptTemplate optTemplate) {
		super();
		this.optTemplate = optTemplate;
	}
	public boolean Login(String name, String password) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean delete(String id) {
		String sql = "delete from users where id=?";
		Object[] obj = { id };
		return optTemplate.update(sql, obj, false);
	}

	public boolean delete(String[] userIds) {
		StringBuffer sbStr = new StringBuffer();
		Object[] obj = userIds;
		;
		for (int i = 0; i < userIds.length; i++) {
			sbStr.append("?,");
		}
		String sql = "delete from users where id in("
				+ sbStr.substring(0, sbStr.length() - 1) + ")";
		return optTemplate.update(sql, obj, false);
	}

	public boolean insert(Users user) {
		String sql = "insert into users(id,name,age) values(?,?,?)";
		Object[] obj = {user.getId(),user.getName(),user.getAge()};
		return optTemplate.update(sql, obj, false);
	}

	@SuppressWarnings("unchecked")
	public List<Users> query() {
		String sql = "select * from users";
		Object[] obj = {};
		return (List<Users>) optTemplate.query(sql, obj, new UsersDAOObjectMapper());
		
	}

	public Users query(String id) {
		String sql = "select * from users";
		Object[] obj = {};
		return (Users) optTemplate.query(sql, obj, new UsersDAOObjectMapper()).get(0);
	}

	public PageModel query(int pageNo, int pageSize) {
		String sql1 = "select * from users";
		Object[] obj1 = {};
		List<Users> list1 = (List<Users>) optTemplate.query(sql1, obj1,
				new UsersDAOObjectMapper());
		int i = list1.size();
		String sql="select * from (select j.*,rownum rn from (select * from users) j where rownum<=?) where rn>?";
		Object[] obj = {pageNo * pageSize, (pageNo - 1) * pageSize};
		List<Users> list = (List<Users>) optTemplate.query(sql, obj,
				new UsersDAOObjectMapper());
		PageModel pagemodel = new PageModel();
		pagemodel.setPageNo(pageNo);
		pagemodel.setPageSize(pageSize);
		pagemodel.setList(list);
		pagemodel.setTotalRecords(i);
		return pagemodel;
	}

	public PageModel query(int pageNo, int pageSize, String condition) {
		String sql1 = "select * from users";
		Object[] obj1 = {};
		List<Users> list1 = (List<Users>) optTemplate.query(sql1, obj1,
				new UsersDAOObjectMapper());
		int i = list1.size();
		String sql="select * from (select j.*,rownum rn from (select * from users where id like '"+condition+"%' or name like '"+condition+"%') j where rownum<=?) where rn>?";
		Object[] obj = {pageNo * pageSize, (pageNo - 1) * pageSize};
		List<Users> list = (List<Users>) optTemplate.query(sql, obj,
				new UsersDAOObjectMapper());
		PageModel pagemodel = new PageModel();
		pagemodel.setPageNo(pageNo);
		pagemodel.setPageSize(pageSize);
		pagemodel.setList(list);
		pagemodel.setTotalRecords(i);
		return pagemodel;
	}

	public boolean update(Users user) {
		String sql = "update users set name=?,age=? where id=?";
		Object[] obj = {user.getName(),user.getAge(),user.getId()};
		return optTemplate.update(sql, obj, false);
	}


}
class UsersDAOObjectMapper implements ObjectMapper{
	public Object mapping(ResultSet rs){
		Users u=new Users();	
			try{
				
				u.setId(rs.getString("id"));
				u.setName(rs.getString("age"));
				u.setName(rs.getString("name"));

				
			}catch(Exception ex){
				ex.printStackTrace();
			}

		return u;
	}
	
}

 

util类

package com.software.usermanager.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.software.usermanager.db.DBConnection;

public class OptTemplate {
	public Object find(String sql,Object[] obj,ObjectMapper mapper){
		Object o=null;
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=DBConnection.getConn();
			pstmt=conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				pstmt.setObject(i+1, obj[i]);
				ResultSet rs=pstmt.executeQuery();
				if(rs.next()){
					o=mapper.mapping(rs);
				}
			}
			}catch(Exception ex){
				ex.printStackTrace();
			}finally{
				try{
					pstmt.close();
					conn.close();
				}catch(SQLException ex){
					ex.printStackTrace();
				}
			}
			return o;
		}

	public List<? extends Object> query(String sql,Object[] obj,ObjectMapper mapper){
		Object o=null;
		List<Object> list=new ArrayList<Object>();
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=DBConnection.getConn();
			pstmt=conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				pstmt.setObject(i+1, obj[i]);
			}
				ResultSet rs=pstmt.executeQuery();

				while(rs.next()){
					
					o=mapper.mapping(rs);
					list.add(o);
				}
			
			
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try{
				pstmt.close();
				conn.close();
			}catch(SQLException ex){
				ex.printStackTrace();
			}
		}
		return list;
	}
	public boolean update(String sql,Object[] obj,boolean isGenerateKey){
		Connection conn=null;
		PreparedStatement pstmt=null;
		boolean bFlag=false;
		try{
			conn=DBConnection.getConn();
			pstmt=isGenerateKey ? conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS):conn.prepareStatement(sql);
			for(int i=0;i<obj.length;i++){
				pstmt.setObject(i+1, obj[i]);
			}
			conn.setAutoCommit(false);
			int i=pstmt.executeUpdate();
			conn.commit();
			if(i>0)
				bFlag=true;
		}catch(SQLException ex){
			ex.printStackTrace();
		}finally{
			try{
				conn.close();
				pstmt.close();
				
			}catch(SQLException ex){
				ex.printStackTrace();
			}
		}
		return bFlag;
	}
	

}

 

package com.software.usermanager.util;

import java.sql.ResultSet;

public interface ObjectMapper {
	public Object mapping(ResultSet rs);

}

 

 

 

分页封装类

 

package com.software.usermanager.util;

import java.util.List;

public class PageModel<T> {
	
	//结果集
	private List<T> list;
	
	//记录数
	private int totalRecords;
	
	//每页多少条数据
	private int pageSize;
	
	//第几页
	private int pageNo;

	/**
	 * 返回总页数
	 * @return
	 */
	public int getTotalPages() {
		return (totalRecords + pageSize - 1) / pageSize;
	}
	
	/**
	 * 首页
	 * @return
	 */
	public int getTopPageNo() {
		return 1;
	}
	
	/**
	 * 上一页 
	 * @return
	 */
	public int getPreviousPageNo() {
		if (this.pageNo <= 1) {
			return 1;
		}
		return this.pageNo - 1;
	}
	
	/**
	 * 下一页
	 * @return
	 */
	public int getNextPageNo() {
		if (this.pageNo >= getButtomPageNo()) {
			return getButtomPageNo();
		}
		return this.pageNo + 1;
	}
	
	/**
	 * 尾页
	 * @return
	 */
	public int getButtomPageNo() {
		return getTotalPages();
	}
	
	public List<T> getList() {
		return list;
	}

	public void setList(List<T> list) {
		this.list = list;
	}

	public int getTotalRecords() {
		return totalRecords;
	}

	public void setTotalRecords(int totalRecords) {
		this.totalRecords = totalRecords;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public int getPageNo() {
		return pageNo;
	}

	public void setPageNo(int pageNo) {
		this.pageNo = pageNo;
	}
}

 

数据库配置文件dbconf.properties

#oracle连接实例
driverClass = oracle.jdbc.driver.OracleDriver
url = jdbc:oracle:thin:@192.168.137.23:1521:orcl

username = 用户名

password = 密码

数据库封装类

package com.software.usermanager.db;


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


public class DBConnection {

	private static Connection conn = null;
	private static Properties props = null;

	static {
		props = new Properties();
		try {
			props.load(DBConnection.class.getResourceAsStream("/dbconf.properties"));
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		try {
			Class.forName(props.getProperty("driverClass"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	

	public static Connection getConn(){
		try {
			conn = DriverManager.getConnection(props.getProperty("url"), props.getProperty("username"), props.getProperty("password"));
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	
	
	public void closeConn(){
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
}

junit测试类

package com.software.usermanager.test;

import java.util.List;

import com.software.usermanager.bean.Users;

import org.apache.tomcat.jni.User;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.software.usermanager.dao.UsersDAO;
import com.software.usermanager.dao.UsersDAOImpl;
import com.software.usermanager.db.DBConnection;
import com.software.usermanager.util.OptTemplate;
import com.software.usermanager.util.PageModel;

public class UserTest {
	DBConnection dbConn = null;

	@Before
	public void setUp() {
		dbConn = new DBConnection();
	}

	@After
	public void tearDown() {
		dbConn.closeConn();

	}
	/************测试插入记录***************/

//	@Test
//	public void testinsert() {
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		for(int i=0;i<20;i++){
//		Users u = new Users();
//		u.setId(""+i);
//		u.setName("郑六");
//		u.setAge("2"+i);
//		boolean b=usersdao.insert(u);
//		if(b==false){
//			System.out.println("插入失败");
//		}else{
//			System.out.println("插入成功");
//		}}
//		
//
//	}
	/************测试修改记录***************/

//	@Test
//	public void testupdate() {
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		Users u = new Users();
//		u.setId("5");
//		u.setName("郑六");
//		u.setAge("21");
//		boolean b=usersdao.update(u);
//		if(b==false){
//			System.out.println("更新失败");
//		}else{
//			System.out.println("更新成功");
//		}
//		
//
//	}
	/************测试删除单条记录***************/

//	@Test
//	public void testdeleteById() {
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		boolean b=usersdao.delete("2");
//		if(b==false){
//			System.out.println("删除失败");
//		}else{
//			System.out.println("删除成功");
//		}
//		
//	}
	/************测试批量删除记录***************/
//
//	@Test
//	public void testdeleteByArray() {
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		String[] s={"3","4","5"};
//		boolean b=usersdao.delete(s);
//		if(b==false){
//			System.out.println("删除失败");
//		}else{
//			System.out.println("删除成功");
//		}
//		
//	}
	/*********查询全部记录结果集为泛型 ************/
//	@Test
//	public void testqueryAll(){
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		List<Users> list=usersdao.query();
//		for(Users u:list){
//			System.out.println(u.getId());
//		}
//	}
	/*********查询单条记录结果集为对象 ************/
//	@Test
//	public void testqueryAll(){
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		Users u=usersdao.query("7");
//		System.out.println(u.getName());
//		
//	}
//	/*********分页查询全部记录结果集为pagemodel************/
//	@Test
//	public void testqueryAll(){
//		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
//		PageModel pml=usersdao.query(2,2);
//		List<Users> list=pml.getList();
//		for(Users u:list){
//			System.out.println(u.getId());
//		}
//	}
	/*********分页模糊查询全部记录结果集为pagemodel************/
	@Test
	public void testqueryAll(){
		UsersDAO usersdao = new UsersDAOImpl(new OptTemplate());
		PageModel pml=usersdao.query(1,2,"2");
		List<Users> list=pml.getList();
		for(Users u:list){
			System.out.println(u.getId());
		}
	}

}

 

注意:以下代码非本程序必须代码,仅供自己笔记之用

Filter

package com.software.usermanager.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

	public class FilterEncoding implements Filter {

		private String encoding = "utf-8";
		public void destroy() {

		}

		public void doFilter(ServletRequest request, ServletResponse response,
				FilterChain arg2) throws IOException, ServletException {
			request.setCharacterEncoding(encoding);
			response.setCharacterEncoding(encoding);
			arg2.doFilter(request, response);

		}

		public void init(FilterConfig arg0) throws ServletException {
			encoding = arg0.getInitParameter("encoding");
		}

	}

Listener

 

package com.software.usermanager.listener;

import java.util.Date;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

public class Listener implements ServletContextListener 
{
	public void contextDestroyed(ServletContextEvent event) 
	{
		// 销毁记录
	}

	public void contextInitialized(ServletContextEvent event) 
	{
		// 记录登录信息
		Date date = new Date();
		event.getServletContext().log(date.toString());
	}
}

web.xml配置文件

 

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	<display-name>xkxt</display-name>
	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>		
	</welcome-file-list>
	  <filter>
      <filter-name>Encoding</filter-name>
      <filter-class>com.software.usermanager.FilterEncoding</filter-class>
 
    <init-param>
    <param-name>encoding</param-name>
    <param-value>UTF-8</param-value>
    </init-param>
     </filter>
	   <filter-mapping>
       <filter-name>Encoding</filter-name>
       <url-pattern>/*</url-pattern>
  </filter-mapping>
	
	<listener>
		<listener-class>com.software.usermanager.Listener</listener-class>
	</listener>
	<servlet>
       <servlet-name>xkxt</servlet-name>
       <servlet-class>com.software.usermanager.XKXTServlet</servlet-class>
    </servlet>
	<servlet-mapping>
      <servlet-name>xkxt</servlet-name>
      <url-pattern>/action</url-pattern>
    </servlet-mapping>
		<servlet>
       <servlet-name>querydqm</servlet-name>
       <servlet-class>com.software.usermanager.DQMServlet</servlet-class>
    </servlet>
	<servlet-mapping>
      <servlet-name>querydqm</servlet-name>
      <url-pattern>/action</url-pattern>
    </servlet-mapping>
  
  
	
	
	
</web-app>