JDBC-DBUtils开源工具类库(简化查询封装数据过程)

1.封装Student类

package com.oracle.model;

import java.io.Serializable;

@SuppressWarnings("serial")
public class Student implements Serializable{
	private int id;
	private String name;
	private String sex;
	private int age;
	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 getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
}

2.创建数据库基类JDBCTemplate

package com.oracle.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCTemplate {
	private static final String className="oracle.jdbc.driver.OracleDriver";
	private static final String url="jdbc:oracle:thin:@localhost:1521:XE";
	private static final String user="system";
	private static final String password="oracle";
	private Connection conn=null;
	private PreparedStatement pst=null;
	private ResultSet rs=null;
	
	static{
			System.out.println("开始加载驱动");
			try {
				Class.forName(className);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			System.out.println("驱动已经成功加载");
	}
	//创建连接
	public Connection getConn(){
		 try {
			conn=DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return conn;
	}
	private void createPrepareStatement(String sql){
		try {
			pst=this.getConn().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 修改
	 * @param sql
	 * @param args
	 * @return
	 */
	public int update(String sql,String args[]){
		int rowcount=0;
		this.createPrepareStatement(sql);
		try {
			for(int i=0;args!=null && i<args.length;i++){
				pst.setString(i+1, args[i]);
			}
			rowcount=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.closeConn();
		}
		return rowcount;
	}
	/**
	 * 查询
	 * @param sql
	 * @param args
	 * @return
	 */
	public ResultSet Query(String sql,String[] args){
		this.createPrepareStatement(sql);
		try {
			for(int i=0;args!=null && i<args.length;i++){
				pst.setString(i+1, args[i]);
			}
			rs=pst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.closeConn();
		}
		return rs;
	}
	/**
	 * 获取首行首列的值
	 * 
	 * @param sql 
	 * @return
	 */
	public String executeScalar(String sql) {
		String res = "";
		this.createPrepareStatement(sql);
		try {
			ResultSet rs = pst.executeQuery();

			if (rs.next()) {
				res = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeConn();
		}
		return res;
	}
	/**
	 * 关闭原则：从小到大
	 */
	public void closeConn(){
		try {
			if(rs!=null){
				rs.close();
			}
			if(pst!=null){
				pst.close();
			}
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

3.导入DBUtils的jar包

4.创建数据库连接类StudentDao

package com.oracle.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.oracle.model.Student;
import com.oracle.util.JDBCTemplate;

public class StudentDao {
	private JDBCTemplate jt=new JDBCTemplate();
	/**
	 * 查询所有信息
	 * @return
	 * @throws SQLException
	 */
	public List<Student> getAllStudent() throws SQLException{
		List<Student> list=new ArrayList<Student>();
		QueryRunner q=new QueryRunner();
		list=(List<Student>) q.query(jt.getConn(),"select * from student",new BeanListHandler(Student.class));
		return list;
	}
}

测试代码

package com.oracle.test;

import java.sql.SQLException;
import java.util.List;
import com.oracle.dao.StudentDao;
import com.oracle.model.Student;

public class DBUtilsTest {
	public static void main(String[] args) {
		StudentDao studentDao=new StudentDao();
		try {
			List<Student> list=studentDao.getAllStudent();
			for(Student s:list){
				System.out.println(s.getId()+"\t"+s.getName()+"\t"+s.getSex()+"\t"+s.getAge());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

5.与之前的查询进行对比，没有使用DBUtils的查询方法代码如下：

public List<Student> prigetAllStudent() throws SQLException{
		List<Student> list=new ArrayList<Student>();
		String sql="select * from student";
		ResultSet rs=jt.Query(sql, null);
		while(rs.next()){
			Student s=new Student();
			s.setId(rs.getInt(1));
			s.setName(rs.getString(2));
			s.setSex(rs.getString(3));
			s.setAge(rs.getInt(4));
			list.add(s);
		}
		return list;
	}

测试代码

package com.oracle.test;

import java.sql.SQLException;
import java.util.List;
import com.oracle.dao.StudentDao;
import com.oracle.model.Student;

public class DBUtilsTest {
	public static void main(String[] args) {
		StudentDao studentDao=new StudentDao();
		try {
			List<Student> list=studentDao.prigetAllStudent();
			for(Student s:list){
				System.out.println(s.getId()+"\t"+s.getName()+"\t"+s.getSex()+"\t"+s.getAge());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

执行结果

对比可见，若数据库中的表的列数增加，使用DBUtils很大的减少了代码量