1.com.dao（查询所有queryAll）
//dao包，一般用于数据库的增删改查
//DAO(Data Access Object) 数据访问对象是一个面向对象的数据库接口
public class ShopDao {
// 连接对象
private Connection con;
// 预处理对象
private PreparedStatement ps;
// 结果集对象
private ResultSet rs;
// 查询所有记录
public List<Shop> queryAll() {
List<Shop> list = new ArrayList<Shop>();
String sql = "select * from shop";
try {
// 1.获取连接对象
con = DBUtils.getConnection();
// 2.通过连接对象获取预处理对象
ps = con.prepareStatement(sql);
// 3.通过预处理对象执行具体的sql语句
rs = ps.executeQuery(); // 主要针对查询操作，即select语法
//ps.executeUpdate(); // 主要针对增删改操作，即insert、delete、update语法
// 4.处理结果集对象，将结果集中每一行数据封装成具体的实体类，并保存到集合中
while(rs.next()) {
//string类型，要加""
String shoId = rs.getString(1);
// 通过列索引取值，这里为取出第1列的值
String shopName = rs.getString("shopname");
// 通过列名取值，列名不区分大小
String shopAddress = rs.getString("shopAddress");
// 通过列名取值，列名不区分大小
String contact = rs.getString("CONTACT");
// 通过列名取值，列名不区分大小
Shop shop = new Shop();
shop.setShopId(shoId);
shop.setShopName(shopName);
shop.setShopAddress(shopAddress);
shop.setContact(contact);
list.add(shop);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5.关闭相关资源
DBUtils.close(con, ps, rs);
}
return list;
}
}
2.com.pojo
public class Shop {
//只要不是大文本或者涉及到二进制，就设成string类型
private String shopId;
private String shopName;
private String shopAddress;
private String contact;
//封装
public String getShopId() {
return shopId;
}
public void setShopId(String shopId) {
this.shopId = shopId;
}
public String getShopName() {
return shopName;
}
public void setShopName(String shopName) {
this.shopName = shopName;
}
public String getShopAddress() {
return shopAddress;
}
public void setShopAddress(String shopAddress) {
this.shopAddress = shopAddress;
}
public String getContact() {
return contact;
}
public void setContact(String contact) {
this.contact = contact;
}
}
3.com.test(测试)
//junit--第三方服务的测试包
//ojdbc--oracle数据库驱动包
安装两个jar包
/**
* 测试方法定义规则：
* 1.访问修饰符必须是public
* 2.必须没有返回值void
* 3.方法无参
* 4.在方法上加上@Test注解
* 5.方法名约定为test加上要测试的方法名
*/
public class TestDemo {
private ShopDao dao = new ShopDao();
@Test
public void testGetConnection() throws SQLException {
Connection con = DBUtils.getConnection();
System.out.println(con.isClosed());//返回的是boolean类型
}
@Test
public void testQueryAll() {
List<Shop> list = dao.queryAll();
for(Shop shop : list) {
String shopId = shop.getShopId();
String shopName = shop.getShopName();
String shopAddress = shop.getShopAddress();
String contact = shop.getContact();
System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
}
}
}
4.com.util(工具)
//工具类主要用于相关资源的链接和关闭资源
public final class DBUtils {
private DBUtils() {}//不能被实例化，不能被继承
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
//orcl 实例名
private static final String USER = "meitao";
private static final String PWD = "123";
// 用于获取数据库连接对象
public static Connection getConnection() {
//获取数据库连接
//只能定义为静态方法，别人才能调用
try {
Class.forName(DRIVER);//加载数据库驱动
return DriverManager.getConnection(URL, USER, PWD);//获取数据库连接对象（connection对象）
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
// 用于关闭相关资源
public static void close(Connection con, PreparedStatement ps, ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
if(ps != null) {
ps.close();
}
if(con != null) {
con.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
5.（查询单个querySingle）
//查询一条记录
//Shop对象
public Shop querySingle(String shopId){
//参数与字符串拼接，最简单，但是不好，有sql注入问题
// String sql="select * from shop where shopid="+shopId;
String sql="select * from shop where shopid=？";
//获取连接对象
con=DBUtils.getConnection();
try {
//通过连接对象获取预处理对象
ps=con.prepareStatement(sql);
//参数索引parameterIndex,第几列索引和参数值x
//ps.setString(1, shopId);
ps.setString(2, "");
//""跟具体的值
//通过预处理对象执行sql语句
rs=ps.executeQuery();//主要针对查询操作
//ps.executeUpate;//增删改的分别为insert,delete,update
//处理结果集对象，将结果集中的每一行数据封装成具体的实体类，并保存到集合中
if(rs.next()){
String shopId1=rs.getString(1);
//通过列索引取值，这里是第一列所以取第一列的值
String shopName=rs.getString("shopname");
//通过列名取值，列名不区分大小写
String shopAddress=rs.getString("shopAddress");
String contact=rs.getString("contact");
Shop shop= new Shop();
shop.setShopId(shopId1);
shop.setShopAddress(shopAddress);
shop.setShopName(shopName);
shop.setContact(contact);
return shop;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
//关闭相关资源
DBUtils.close(con, ps, rs);
}
//出异常或者没有查到数据才返回null
return null;
}
@Test
public void testQuerySingle(){
Shop shop=dao.querySingle("1");
//Shop shop=dao.querySingle("50");
//如果是50，则传入的是一个空值，会报错，空引用异常
//遍历集合
String shopId= shop.getShopId();
String shopName= shop.getShopName();
String shopAddress= shop.getShopAddress();
String contact= shop.getContact();
System.out.println(shopId+ "\t" +shopName+ "\t" +shopAddress+ "\t" +contact);
}
6.统计查询（count）
//查询合计
public int queryOne(int no){
//获取数据库查询语句
String sql="select count(*) from shop";
//获取连接对象--从数据库里面获取
try {
con=DBUtils.getConnection();
ps=con.prepareStatement(sql);
//执行sql语句
rs=ps.executeQuery();
//取值，赋值，把查询语句中的每一条记录封装起来，装在集合中
if(rs.next()){
//利用索引值取值
//或者利用名字取值,不区分大小写
int no1= rs.getInt(1);
return no1;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//查询完毕之后记得关闭相关资源
DBUtils.close(con, ps, rs);
}
//返回要查询的数据
return 0;
}
@Test
public void testQueryOne(){
//要测试,先把值获取过来,用集合装着
int no1=dao.queryOne(3);
System.out.println(no1);
}
7.模糊查询（like）
// 模糊查询
public List<Shop> queryLike(String shopname1) {
//?是具体的参数
List<Shop> list = new ArrayList<Shop>();
// String sql = "select * from shop where shopname like ?";
//灵活版//ps.setString(1,shopname1);
String sql = "select * from shop where shopname like ?";
try {
// 1.获取连接对象
con = DBUtils.getConnection();
// 2.通过连接对象获取预处理对象
ps = con.prepareStatement(sql);
//parameterIndex，第一个？;x,第一个？所代表的参数值
ps.setString(1,"%"+shopname1+"%");
System.out.println(sql);
// 3.通过预处理对象执行具体的sql语句
rs = ps.executeQuery(); // 主要针对查询操作，即select语法
//ps.executeUpdate(); // 主要针对增删改操作，即insert、delete、update语法
// 4.处理结果集对象，将结果集中每一行数据封装成具体的实体类，并保存到集合中
while(rs.next()) {
//string类型，要加""
String shoId = rs.getString(1);
// 通过列索引取值，这里为取出第1列的值
String shopName = rs.getString("shopname");
// 通过列名取值，列名不区分大小
String shopAddress = rs.getString("shopAddress");
// 通过列名取值，列名不区分大小
String contact = rs.getString("CONTACT");
// 通过列名取值，列名不区分大小
Shop shop = new Shop();
shop.setShopId(shoId);
shop.setShopName(shopName);
shop.setShopAddress(shopAddress);
shop.setContact(contact);
list.add(shop);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5.关闭相关资源
DBUtils.close(con, ps, rs);
}
return list;
}
@Test
public void testQueryLike() {
//不加%,就是左右啥都没有，精确匹配
List<Shop> list = dao.queryLike("店");
for(Shop shop : list) {
String shopId = shop.getShopId();
String shopName = shop.getShopName();
String shopAddress = shop.getShopAddress();
String contact = shop.getContact();
System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
}
}
8.分页查询（queryPaging）
// 1.分页查询
public List queryPaging(Page page) {
List<Shop> list = new ArrayList<Shop>();
StringBuffer sql = new StringBuffer();
sql.append("select * from (select t.*,rownum rn from ")
.append("(select * from shop) t) where rn between ? and ?");
try {
// 1.获取连接对象
con = DBUtils.getConnection();
// 2.根据连接对象获取预处理对象
ps = con.prepareStatement(sql.toString());
ps.setInt(1, page.getStart());
ps.setInt(2, page.getEnd());
// 3.通过预处理对象执行SQL，获取结果集对象
rs = ps.executeQuery();
// 4.处理结果集，将结果集中的每一条记录提取出来，包装成对应的实体对象
while(rs.next()) {
String shopId = rs.getString(1);
// 通过列索引取值，从1开始
String shopName = rs.getString(“shopname”);
// 通过列名取值，不区分大小写
String shopAddress = rs.getString(“SHOPADDRESS”);
// 通过列名取值，不区分大小写
String contact = rs.getString(“Contact”);
// 通过列名取值，不区分大小写
Shop shop = new Shop();
shop.setShopId(shopId);
shop.setShopName(shopName);
shop.setShopAddress(shopAddress);
shop.setContact(contact);
list.add(shop);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(con, ps, rs);
}
return list;
}
2.@Test
public void testQueryPaging() {
//先查询单个
int rowCount = dao.queryCount();
Page page = new Page();
page.setPageSize(3);
page.setPageNow(1);
page.setRowCount(rowCount);
PageUtils.setPage(page);
System.out.println("总共:" + page.getPageCount() + "页，当前第" + page.getPageNow() + "页");
List<Shop> list = dao.queryPaging(page);
for(Shop shop : list) {
String shopId = shop.getShopId();
String shopName = shop.getShopName();
String shopAddress = shop.getShopAddress();
String contact = shop.getContact();
System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
}
}
-
该工具类主要用于处理分页属性
public final class PageUtils {
private PageUtils() {}public static final void setPage(Page page) {
// 每页显示记录数 int pageSize = page.getPageSize(); // 总记录数 int rowCount = page.getRowCount(); // 计算总页数 int pageCount = rowCount / pageSize; if(rowCount % pageSize != 0) { pageCount ++; } // 当前页数 int pageNow = page.getPageNow(); if(pageNow < 1) { pageNow = 1; } else { if(pageNow > pageCount) { pageNow = pageCount; } } // 计算出提取数据的起始值 int start = pageNow * pageSize - pageSize + 1; // 计算出提取数据的结束值 int end = pageNow * pageSize; // 重新给Page对象赋值 page.setPageCount(pageCount); page.setPageNow(pageNow); page.setStart(start); page.setEnd(end);}
}
4.// 分页对象
public class Page {
// 每页显示的记录数，通过指定，如每页显示3条记录
private int pageSize;
// 总记录数，通过聚合函数count获取
private int rowCount;
/*
* 总记录数，通过计算获取
* 计算原理：
* int pageCount = rowCount/pageSize;
* if(rowCount%pageSize != 0) {pageCount++;}
*/
private int pageCount;
// 当前页数，通过指定，一般从第1页开始
private int pageNow;
// 查询起始位置，通过计算获取：当前页码*每页显示记录数-每页显示记录数+1
//当前页的第一条的记录
private int start;
// 查询结束位置，通过计算获取：当前页码*每页显示记录数
//当前页的最后一条记录
private int end;
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getRowCount() {
return rowCount;
}
public void setRowCount(int rowCount) {
this.rowCount = rowCount;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getPageNow() {
return pageNow;
}
public void setPageNow(int pageNow) {
this.pageNow = pageNow;
}
public int getStart() {
return start;
}
public void setStart(int start) {
this.start = start;
}
public int getEnd() {
return end;
}
public void setEnd(int end) {
this.end = end;
}
}
9.删除（delete）
// 删除
public boolean deleteShop(String shopId) { // 优选方式
String sql = "delete from shop where shopid=?";
try {
con = DBUtils.getConnection();
ps = con.prepareStatement(sql);
ps.setString(1, shopId);
int row = ps.executeUpdate();
if(row != 0) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(con, ps, rs);
}
return false;
}
@Test
public void testDeleteShop() {
boolean flag = dao.deleteShop("13");
Assert.assertTrue(flag);
}
10.批处理删除（deleteBatch）
// 批处理删除
public boolean deleteBatch(String... shopIds) {
String sql = "delete from shop where shopid=?";
try {
con = DBUtils.getConnection();
ps = con.prepareStatement(sql);
for(String id : shopIds) {
ps.setString(1, id);
ps.addBatch();
}
ps.executeBatch();
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(con, ps, rs);
}
return false;
}
@Test
public void testDeleteBatch() {
boolean flag = dao.deleteBatch("11", "12", "13", "14");
Assert.assertTrue(flag);
}
11.新增（insert）
//新增
//多个
//数组, String[] string
//String... args
//Shop shop--优选方式--setString后面加shop.getShopId()等等
//public boolean addShop(String shopId, String shopName, String shopAddress, String contact) {
//public boolean addShop(String... args) {
public boolean addShop(Shop shop) { // 优选方式
String sql = "insert into shop values(?,?,?,?)";
try {
con = DBUtils.getConnection();
ps = con.prepareStatement(sql);
// ps.setString(1, shopId);
// ps.setString(2, shopName);
// ps.setString(3, shopAddress);
// ps.setString(4, contact);
// ps.setString(1, args[0]);
// ps.setString(2, args[1]);
// ps.setString(3, args[2]);
// ps.setString(4, args[3]);
ps.setString(1, shop.getShopId());
ps.setString(2, shop.getShopName());
ps.setString(3, shop.getShopAddress());
ps.setString(4, shop.getContact());
int row = ps.executeUpdate();
if(row != 0) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(con, ps, rs);
}
return false;
}
@Test
public void testAddShop() {
Shop shop = new Shop();
shop.setShopId("13");
shop.setShopName("ddd");
shop.setShopAddress("ddd");
shop.setContact("ddd");
boolean flag = dao.addShop(shop);
Assert.assertTrue(flag);
}
12.修改（update）
// 修改
public boolean updateShop(Shop shop) { // 优选方式
String sql = "update shop set shopname=?,shopaddress=?,contact=? where shopid=?";
try {
con = DBUtils.getConnection();
ps = con.prepareStatement(sql);
ps.setString(4, shop.getShopId());
ps.setString(1, shop.getShopName());
ps.setString(2, shop.getShopAddress());
ps.setString(3, shop.getContact());
int row = ps.executeUpdate();
if(row != 0) {
return true;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(con, ps, rs);
}
return false;
}
@Test
public void testUpdateShop() {
Shop shop = new Shop();
shop.setShopId("10");
shop.setShopName("abc");
shop.setShopAddress("abc");
shop.setContact("abc");
boolean flag = dao.updateShop(shop);
Assert.assertTrue(flag);
}
13.执行存储过程
// 执行存储过程
public String executeProc(String shopId) {
try {
con = DBUtils.getConnection();
// 通过连接对象获取执行存储过程的对象
cs = con.prepareCall("{call proc_shop(?,?)}");
cs.setString(1, shopId); // 设置输入参数
cs.registerOutParameter(2, Types.VARCHAR); // 注册输出参数
cs.execute(); // 执行存储过程
return cs.getString(2); // 提取输出参数值，根据参数索引
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtils.close(con, cs, rs);
}
return null;
}
@Test
public void testExecuteProc() {
String shopName = dao.executeProc("5");
System.out.println(shopName);
}
扫一扫
426
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
