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

来源：互联网发布：java 批处理框架编辑：程序博客网时间：2024/06/06 15:49

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 blocke.printStackTrace();}System.out.println("驱动已经成功加载");}//创建连接public Connection getConn(){ try {conn=DriverManager.getConnection(url, user, password);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} return conn;}private void createPrepareStatement(String sql){try {pst=this.getConn().prepareStatement(sql);} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.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 blocke.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 blocke.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 blocke.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 blocke.printStackTrace();}}}

执行结果

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

2 0