Java JDBC数据库 之 DAO 封装
http://www.verejava.com/?id=16998506642443
下载 Oracle 或者 Mysql Jar 驱动
mysql-connector-java-3.0.10-stable-bin.jar
ojdbc14.jar
package com.dao;
import java.util.List;
import com.entity.Dept;
public interface DeptDao
{
//添加部门
public boolean add(Dept item);
//获得所有的部门
public List<Dept> find();
//根据部门的id 返回 该部门
public Dept findById(int id);
//根据 部门id 修改部门数据
public boolean update(Dept item);
//根据 部门 id 删除部门
public boolean deleteById(int id);
//删除多个 "1,2,3"
public boolean deleteByIds(String ids);
}
package com.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.db.DBUtil;
import com.entity.Dept;
public class DeptDaoImpl implements DeptDao
{
private DBUtil db;
public DeptDaoImpl()
{
db=new DBUtil();
}
@Override
public boolean add(Dept item)
{
//添加到数据库
String sql="INSERT INTO dept(id,name)VALUES(?,?)";
Object[] params={item.getId(),item.getName()};
return db.executeUpdate(sql, params);
}
@Override
public List<Dept> find()
{
//实例化集合 存的是 所有的部门的数据
List<Dept> deptList=new ArrayList<Dept>();
String sql="SELECT * FROM dept";
ResultSet rs=db.executeQuery(sql, null);
try
{
while(rs.next())
{
int id=rs.getInt("id");
String name=rs.getString("name");
//存入集合
deptList.add(new Dept(id,name));
}
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
db.DBClose();
}
return deptList;
}
@Override
public Dept findById(int id)
{
Dept item=null;
String sql="SELECT * FROM dept WHERE id=?";
Object[] params={id};
ResultSet rs=db.executeQuery(sql, params);
try
{
if(rs.next())
{
int id2=rs.getInt("id");
String name=rs.getString("name");
item=new Dept(id2,name);
}
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
db.DBClose();
}
return item;
}
@Override
public boolean update(Dept item)
{
String sql="UPDATE dept SET name=? WHERE id=?";
Object[] params={item.getName(),item.getId()};
return db.executeUpdate(sql, params);
}
@Override
public boolean deleteById(int id)
{
String sql="DELETE FROM dept WHERE id=?";
Object[] params={id};
return db.executeUpdate(sql, params);
}
@Override
public boolean deleteByIds(String ids) //ids "1,2,3"
{
String sql="DELETE FROM dept WHERE id IN ("+ids+")";
return db.executeUpdate(sql);
}
}
package com.dao.test;
import com.dao.DeptDao;
import com.dao.DeptDaoImpl;
import com.entity.Dept;
public class TestAdd
{
public static void main(String[] args)
{
DeptDao deptDao=new DeptDaoImpl();
deptDao.add(new Dept(1,"财务"));
deptDao.add(new Dept(3,"人事"));
}
}
package com.dao.test;
import com.dao.DeptDao;
import com.dao.DeptDaoImpl;
public class TestDelete
{
public static void main(String[] args)
{
DeptDao deptDao=new DeptDaoImpl();
deptDao.deleteById(1);
}
}
package com.dao.test;
import com.dao.DeptDao;
import com.dao.DeptDaoImpl;
import com.entity.Dept;
public class TestUpdate
{
public static void main(String[] args)
{
DeptDao deptDao=new DeptDaoImpl();
deptDao.update(new Dept(1,"会计"));
}
}
package com.dao.test;
import java.util.List;
import com.dao.DeptDao;
import com.dao.DeptDaoImpl;
import com.entity.Dept;
public class TestFind
{
public static void main(String[] args)
{
DeptDao deptDao=new DeptDaoImpl();
List<Dept> deptList=deptDao.find();
for(Dept item:deptList)
{
System.out.println(item.getId()+","+item.getName());
}
}
}
package com.dao.test;
import com.dao.DeptDao;
import com.dao.DeptDaoImpl;
import com.entity.Dept;
public class TestFindById
{
public static void main(String[] args)
{
DeptDao deptDao=new DeptDaoImpl();
Dept item=deptDao.findById(2);
System.out.println(item.getId()+","+item.getName());
}
}
package com.dao.test;
import com.dao.DeptDao;
import com.dao.DeptDaoImpl;
public class TestDeleteByIds
{
public static void main(String[] args)
{
DeptDao deptDao=new DeptDaoImpl();
deptDao.deleteByIds("1,2,3");
}
}
package com.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil
{
private Connection conn;//数据库的链接对象
public DBUtil()
{
//加载驱动
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e)
{
e.printStackTrace();
}
}
//打开数据的链接
public Connection openConnection()
{
try
{
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "myspace", "myspace123");
} catch (SQLException e)
{
e.printStackTrace();
}
return conn;
}
//执行 INSERT DELETE UPDATE 的方法
public boolean executeUpdate(String sql)
{
//打开数据库
openConnection();
//创建执行 sql 的对象
try
{
Statement stmt=conn.createStatement();
//执行sql 语句
if(stmt.executeUpdate(sql)>0)
{
return true;
}
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
DBClose();
}
return false;
}
//执行 sql 的 查找 SELECT 的方法
public ResultSet executeQuery(String sql)
{
//打开数据库链接
openConnection();
//创建执行sql 的对象
Statement stmt=null;
ResultSet rs=null;
try
{
stmt = conn.createStatement();
rs=stmt.executeQuery(sql);
} catch (SQLException e)
{
e.printStackTrace();
}
return rs;
}
//绑定的sql INSERT DELETE UPDATE 方法
public boolean executeUpdate(String sql,Object[] params)
{
//打开数据库
openConnection();
//创建绑定对象
try
{
PreparedStatement pstmt=conn.prepareStatement(sql);
//填充 ？ 的值
for(int i=0;params!=null&&i<params.length;i++)
{
if(params[i] instanceof Integer)
{
pstmt.setInt(i+1, Integer.parseInt(params[i].toString()));
continue;
}
if(params[i] instanceof Float)
{
pstmt.setFloat(i+1, Float.parseFloat(params[i].toString()));
continue;
}
if(params[i] instanceof Double)
{
pstmt.setDouble(i+1, Double.parseDouble(params[i].toString()));
continue;
}
if(params[i] instanceof String)
{
pstmt.setString(i+1, params[i].toString());
}
//java.sql.Date, java.util.Date
if(params[i] instanceof java.util.Date)
{
//将 java.util.Date 转成 java.sql.Date
java.util.Date javaDate=(java.util.Date)params[i];
java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime());
pstmt.setDate(i+1, sqlDate);
continue;
}
if(params[i] instanceof java.util.Timestamp)
{
//将 java.util.Date 转成 java.sql.Date
java.util.Date javaDate=(java.util.Date)params[i];
java.sql.Timestamp timestamp=new java.sql.Timestamp(javaDate.getTime());
pstmt.setTimestamp(i+1, timestamp);
continue;
}
}
//执行 绑定的 sql 语句
if(pstmt.executeUpdate()>0)
{
return true;
}
} catch (SQLException e)
{
e.printStackTrace();
}
finally
{
DBClose();
}
return false;
}
//绑定的sql 查找 SELECT 方法
public ResultSet executeQuery(String sql,Object[] params)
{
//打开数据库
openConnection();
//创建绑定对象
try
{
PreparedStatement pstmt=conn.prepareStatement(sql);
//填充 ？ 的值
for(int i=0;params!=null&&i<params.length;i++)
{
if(params[i] instanceof Integer)
{
pstmt.setInt(i+1, Integer.parseInt(params[i].toString()));
continue;
}
if(params[i] instanceof Float)
{
pstmt.setFloat(i+1, Float.parseFloat(params[i].toString()));
continue;
}
if(params[i] instanceof Double)
{
pstmt.setDouble(i+1, Double.parseDouble(params[i].toString()));
continue;
}
if(params[i] instanceof String)
{
pstmt.setString(i+1, params[i].toString());
}
//java.sql.Date, java.util.Date
if(params[i] instanceof java.util.Date)
{
//将 java.util.Date 转成 java.sql.Date
java.util.Date javaDate=(java.util.Date)params[i];
java.sql.Date sqlDate=new java.sql.Date(javaDate.getTime());
pstmt.setDate(i+1, sqlDate);
continue;
}
if(params[i] instanceof java.util.Timestamp)
{
//将 java.util.Date 转成 java.sql.Date
java.util.Date javaDate=(java.util.Date)params[i];
java.sql.Timestamp timestamp=new java.sql.Timestamp(javaDate.getTime());
pstmt.setTimestamp(i+1, timestamp);
continue;
}
}
//执行 绑定的 sql 语句
return pstmt.executeQuery();
} catch (SQLException e)
{
e.printStackTrace();
}
return null;
}
//关闭数据库链接
public void DBClose()
{
if(conn!=null)
{
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
浙公网安备 33010602011771号