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很大的减少了代码量