jdbc

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);
	}
}

3. 该工具类主要用于处理分页属性
   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);
	}