package com.demo.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.demo.beans.Myaddr;
public class OracleDao {
private Connection conn; //连接数据库的三要素
private Statement stat;
private ResultSet re;
public OracleDao(Connection conn1){ //创建构造,为了得到连接对象。
this.conn=conn1;
}
public List findAll(){ //findAll方法,获取数据库表中的信息
List list = new ArrayList(); //实例一个list对象
String sql = "select * from myaddrbook"; //sql语句
try {
stat = conn.createStatement();
re = stat.executeQuery(sql);
Myaddr myaddr=null; //初始化Myaddr类的对象myaddr.
while(re.next()) //循环,只要还能在数据库中表中next(即没有到表的结尾).
{
myaddr=new Myaddr(); //新new一个Myaddr对象myaddr
myaddr.setId(Integer.valueOf(re.getString(1))); //以下操作是获取字段的值,传给myaddr对象
myaddr.setFirstName(re.getString(2));
myaddr.setLastName(re.getString(3));
myaddr.setJobTitle(re.getString(4));
myaddr.setDepartment(re.getString(5));
myaddr.setOffph(Integer.valueOf(re.getString(6)));
myaddr.setMobile(Integer.valueOf(re.getString(7)));
myaddr.setEmail(re.getString(8));
list.add(myaddr); //将myaddr对象所获取的信息加入到list对象中
}
return list; //返回list对象
} catch (SQLException e) { //异常
// TODO Auto-generated catch block
e.printStackTrace();
}finally //使用后要关闭各种数据库相关属性
{
try {
re.close(); //关闭结果集
stat.close(); //关闭管道
conn.close(); //关闭连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return null;
}
public boolean save(Myaddr myaddr)
{
String sql = "insert into myaddrbook(ID,firstName,lastName,jobTitle,department,offph,mobile,eMail) values(";
sql = sql +myaddr.getId()+",'"+myaddr.getFirstName()+"','"+myaddr.getLastName()+"','"+myaddr.getJobTitle()+"','"+myaddr.getDepartment()+"',"+myaddr.getOffph()+","+myaddr.getMobile()+",'"+myaddr.getEmail()+"')";
/*try {
stat.executeUpdate(sql);
System.out.println(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return true;*/
try {
stat=conn.createStatement();
int rowCount=0;
rowCount=stat.executeUpdate(sql);
if(rowCount>0){
conn.commit();
}else{
conn.rollback();
return false;
}
return true;
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
}
}finally{
try {
stat.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
return false;
}
public Myaddr update(Integer count) {
String sql = "select * from myaddrbook where id=" + count;
try {
stat = conn.createStatement();
re = stat.executeQuery(sql);
Myaddr myaddr = null;
if (re.next()) {
myaddr = new Myaddr();
myaddr.setId(count);
myaddr.setFirstName(re.getString(2));
myaddr.setLastName(re.getString(3));
myaddr.setJobTitle(re.getString(4));
myaddr.setDepartment(re.getString(5));
myaddr.setOffph(Integer.valueOf(re.getString(6)));
myaddr.setMobile(Integer.valueOf(re.getString(7)));
myaddr.setEmail(re.getString(8));
return myaddr;
} else {
return null;
}
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
return null;
} finally {
try {
stat.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
}
public boolean modify(Myaddr myaddr)
{
String sql = "update myaddrbook set firstName = '" + myaddr.getFirstName()
+ "',lastName = '" + myaddr.getLastName() + "',jobTitle='" +
myaddr.getJobTitle()+ "',department = '" + myaddr.getDepartment() + "',offph='" + myaddr.getOffph() + "',mobile = '"
+ myaddr.getMobile() + "',email = '" + myaddr.getEmail() + "' where id=" + myaddr.getId();
try {
stat=conn.createStatement();
int rowCount=0;
rowCount=stat.executeUpdate(sql);
if(rowCount>0){
conn.commit();
return true;
}else{
conn.rollback();
return false;
}
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
}
}finally{
try {
stat.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
return true;
}
public boolean delete(Integer count) {
String sql = "delete from myaddrbook where id = " + count;
try {
stat = conn.createStatement();
int rowCount = 0;
rowCount = stat.executeUpdate(sql);
if (rowCount > 0) {
conn.commit();
return true;
} else {
conn.rollback();
return false;
}
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
// TODO 自动生成 catch 块
e1.printStackTrace();
}
} finally {
try {
stat.close();
conn.close();
} catch (SQLException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
}
}
return true;
}
}