************************JDBC操作事务*************************
1.开启事务
conn.setAutoCommit(false);
2.提交事务
conn.commit();
3. 回滚事务
conn.rollback();
注意:事务是默认关闭的,会自动提交..
******************************根据id查询单个商品种类、按照sql查询商品种类**************************
package com.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.dao.inter.CategoryDao;
import com.util.ConnOracle;
import com.vo.Category;
public class CategoryDaoImpl implements CategoryDao {
//1.组合Connection
private Connection conn;
public CategoryDaoImpl(){
conn = ConnOracle.getConnection();
}
@Override
public int addCategory(Category category) {
int count = 0;
//3.建立通道
String sql = "insert into category values(seq_category.nextval,?,?)";
//获得了一个预编译的通道 相当于IO通道 可以用它来发送sql语句
PreparedStatement pstmt = null;
//默认事务是关闭的 调用addCategory增加一条记录的时候 会自动提交
//开启事务
try {
//conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, category.getCname());
pstmt.setString(2, category.getCdesc());
//4.执行并返回结果集
count = pstmt.executeUpdate();//可以执行除了DQL以外所有的语句 DML 返回的是受影响的行数 DCL或DDL语句 返回值是0
//conn.commit();
if(count>=1){
System.out.println("添加商品种类成功!");
}else{
System.out.println("没有添加任何商品种类!");
}
} catch (SQLException e) {
/*try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}*/
System.out.println("建立通道或添加商品种类失败");
e.printStackTrace();
}finally{
//5.关闭
ConnOracle.closeConnection(null,pstmt, conn);
}
return count;
}
@Override
public int deleteCategory(Category category) {
int count = 0;
//3.建立通道
String sql = "delete from category where cid=?";
//获得了一个预编译的通道 相当于IO通道 可以用它来发送sql语句
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, category.getCid());
//4.执行并返回结果集
count =pstmt.executeUpdate();//可以执行除了DQL以外所有的语句 DML 返回的是受影响的行数 DCL或DDL语句 返回值是0
if(count>=1){
System.out.println("删除商品种类成功!");
}else{
System.out.println("没有删除任何商品种类!");
}
} catch (SQLException e) {
System.out.println("建立通道或删除商品种类失败");
e.printStackTrace();
}finally{
//5.关闭
ConnOracle.closeConnection(null,pstmt, conn);
}
return count;
}
@Override
public int updateCategory(Category category) {
int count = 0;
//3.建立通道
String sql = "update category set cname=?,cdesc=? where cid=?";
//获得了一个预编译的通道 相当于IO通道 可以用它来发送sql语句
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, category.getCname());
pstmt.setString(2, category.getCdesc());
pstmt.setInt(3, category.getCid());
//4.执行并返回结果集
count =pstmt.executeUpdate();//可以执行除了DQL以外所有的语句 DML 返回的是受影响的行数 DCL或DDL语句 返回值是0
if(count>=1){
System.out.println("修改商品种类成功!");
}else{
System.out.println("没有修改任何商品种类!");
}
} catch (SQLException e) {
System.out.println("建立通道或修改商品种类失败");
e.printStackTrace();
}finally{
//5.关闭
ConnOracle.closeConnection(null,pstmt, conn);
}
return count;
}
@Override
public Category getCategoryById(int id) {
Category category = new Category();
//3.建立通道
String sql = "select * from category where cid=?";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
//4.执行并返回结果集
rs =pstmt.executeQuery();
while(rs.next()){
category.setCid(rs.getInt("cid"));
category.setCname(rs.getString("cname"));
category.setCdesc(rs.getString("cdesc"));
}
} catch (SQLException e) {
System.out.println("建立通道或查询单个商品种类失败");
e.printStackTrace();
}finally{
//5.关闭
ConnOracle.closeConnection(rs,pstmt, conn);
}
return category;
}
@Override
public List<Category> getPageByQuery(String sql) {
List<Category> list = new ArrayList<Category>();
Category category = null;
ResultSet rs = null;
Statement stmt = null;
//3.建立通道
try {
stmt = conn.createStatement();
//4.执行并返回结果集
rs = stmt.executeQuery(sql);
while(rs.next()){
category = new Category();
category.setCid(rs.getInt("cid"));
category.setCname(rs.getString("cname"));
category.setCdesc(rs.getString("cdesc"));
list.add(category);
}
} catch (SQLException e) {
System.out.println("创建通道或查询结果集失败");
e.printStackTrace();
}finally{
//5.关闭
ConnOracle.closeConnection(rs, stmt, conn);
}
return list;
}
public static void main(String[] args) {
CategoryDaoImpl dao = new CategoryDaoImpl();
String sql = "select * from category";
List<Category> list = dao.getPageByQuery(sql);
for(Category category:list){
System.out.println(category);
}
}
}
