完全面向对象 的方法 jdbc连接数据库

思想：拿查询举例：把查询结果放到放到学生对象中，然后再把学生对象放到一个容器中，最后冲容器中，取出想要的数据，确保数据安全性
//---------------------------------------------
//创建学生类
/**
*@author Li Jia Xuan
*@version 1.0
*@since 2012-10-29
*@time 上午10:11:11
*/
public class StudentDTO {
private int id;
private String name;
private String pwd;

public StudentDTO(int id, String name, String pwd) {
super();
this.id = id;
this.name = name;
this.pwd = pwd;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public String getPwd() {
return pwd;
}

}

//----------------------------------------------------

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

/**
*建立连接，关闭连接
*@author Li Jia Xuan
*@version 1.0
*@since 2012-10-29
*@time 上午10:12:43
*/
public class GetConnection {

static final String DRIVERNAMR = "oracle.jdbc.driver.OracleDriver";
static final String URL = "jdbc:oracle:thin:@192.168.1.254:1521:orcl";
static final String USER = "lijiaxuan";
static final String PWD = "123456";
/**
* 单子模式，只获取一个连接对象
*/

static GetConnection instance = new GetConnection();

private GetConnection() {
}

public static GetConnection getInstance() {
return instance;

}

public Connection getConnection() {
Connection conn = null;
try {
Class.forName(DRIVERNAMR);
conn = DriverManager.getConnection(URL, USER, PWD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;

}
/**
* 关闭连接，前进后出的顺序
* @param rs
* @param ps
* @param conn
*/
public void closeConnection(ResultSet rs, PreparedStatement ps,
Connection conn) {
if (rs != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 重载关闭连接
* @param ps
* @param conn
*/
public void closeConnection(PreparedStatement ps, Connection conn) {

if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}


//--------------------------------------------------------------------

import java.util.ArrayList;

/**
*
*@author Li Jia Xuan
*@version 1.0
*@since 2012-10-29
*@time 上午10:20:43
*/
public interface UserDAO {
public ArrayList<StudentDTO> getAll();
public StudentDTO getById(int id);
public boolean update(StudentDTO dto);
public boolean deleteById(int id);
public boolean insert(StudentDTO dto);

}
//------------------------------------------------------------
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
*实现接口
*@author Li Jia Xuan
*@version 1.0
*@since 2012-10-29
*@time 上午10:23:40
*/
public class UserDAOImpl implements UserDAO {

PreparedStatement ps=null;
ResultSet rs=null;
Connection conn= GetConnection.getInstance().getConnection();
@Override
public ArrayList<StudentDTO> getAll() {
ArrayList<StudentDTO> list=new ArrayList<StudentDTO>();
try {
ps=conn.prepareStatement("select * from student");
rs=ps.executeQuery();
while(rs.next()){
int id=rs.getInt("stu_id");
String name=rs.getString("username");
String pwd=rs.getString("pwd");
StudentDTO dto=new StudentDTO(id,name,pwd);
list.add(dto);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
GetConnection.getInstance().closeConnection(rs, ps, conn);
}
return list;
}

@Override
public StudentDTO getById(int id) {
StudentDTO dto=null;
try {
ps=conn.prepareStatement("select * from student where stu_id=?");
ps.setInt(1, id);
rs=ps.executeQuery();
while(rs.next()){
int id1=rs.getInt("stu_id");
String name=rs.getString("username");
String pwd=rs.getString("pwd");
dto=new StudentDTO(id1,name,pwd);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
GetConnection.getInstance().closeConnection(rs, ps, conn);
}
return dto;
}

@Override
public boolean update(StudentDTO dto) {

try {
ps=conn.prepareStatement("update student set pwd=? where username=?");
ps.setString(1, dto.getPwd());
ps.setString(2, dto.getName());
int i=ps.executeUpdate();
if(i>0){
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
GetConnection.getInstance().closeConnection( ps, conn);
}
return false;

}

@Override
public boolean deleteById(int id) {
try {
ps=conn.prepareStatement("delete from student where stu_id=?");
ps.setInt(1, id);
int i=ps.executeUpdate();
if(i>0){
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
GetConnection.getInstance().closeConnection( ps, conn);
}
return false;
}

@Override
public boolean insert(StudentDTO dto) {
try {
ps=conn.prepareStatement("insert into student values(seq_student.nextval,?,?)");
ps.setString(1, dto.getName());
ps.setString(2, dto.getPwd());
int i=ps.executeUpdate();
if(i>0){
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
GetConnection.getInstance().closeConnection( ps, conn);
}
return false;
}

}

//---------------------------------------------------------------------

import static org.junit.Assert.*;

import java.util.ArrayList;
import java.util.Iterator;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

/**
*
*@author Li Jia Xuan
*@version 1.0
*@since 2012-10-29
*@time 上午11:00:10
*/
public class UserDAOImplTest {
UserDAOImpl u=new UserDAOImpl();
@BeforeClass
public static void setUpBeforeClass() throws Exception {
}

@AfterClass
public static void tearDownAfterClass() throws Exception {
}

@Test
//测试查询所有
public void testGetAll() {
ArrayList<StudentDTO> al=u.getAll();
Iterator<StudentDTO> it=al.iterator();
while(it.hasNext()){
StudentDTO s=it.next();
System.out.println("编号"+s.getId()+"姓名"+s.getName()+"密码"+s.getPwd());
}
}

@Test
//测试通过ID查询
public void testGetById() {
StudentDTO s=u.getById(25);
System.out.println("编号:"+s.getId()+"姓名:"+s.getName()+"密码:"+s.getPwd());
}

@Test
//测试更新
public void testUpdate() {
StudentDTO dto=new StudentDTO(1,"赵六","111111");
System.out.println(u.update(dto));
}

@Test
//测试删除
public void testDeleteById() {
System.out.println(u.deleteById(1));
}

@Test
//测试插入
public void testInsert() {
StudentDTO dto=new StudentDTO(1,"赵六","111111");
System.out.println(u.insert(dto));
}

}