主要的实现类
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.cxp.model.QuestionInfo;
import com.cxp.util.BaseDao;
import oracle.jdbc.internal.OracleTypes;
/**
* 试题信息DAO实现类
*
* @author cxp
* @data 2016-10-15
* @modify
*
*/
public class QuestionInfoDaoImpl extends BaseDao implements QuestionInfoDao {
/**
* 列出所有试题信息
*/
public void queryAll() {
// 获得连接
Connection conn = getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
// 3.ps对象
String sql = "{call proc_queryAll(?)}";
cs = conn.prepareCall(sql);
cs.registerOutParameter(1, OracleTypes.CURSOR);
// 4.执行sql
cs.execute();
// 5.获取数据
rs = (ResultSet) cs.getObject(1);
boolean flag=false;//标识是否找到数据
while (rs.next()) {
System.out.println(rs.getInt(7) + "、" + rs.getString(1) + "\n选项A:" + rs.getString(2) + "\n选项B:"
+ rs.getString(3) + "\n选项C:" + rs.getString(4) + "\n选项D:" + rs.getString(5) + "\n答案:"
+ rs.getString(6) + "\n");
flag=true;
}
//没找到数据
if(flag==false)
System.out.println("无匹配结果!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭连接
closeAll(rs, cs, conn);
}
}
/**
* 按科目查询
*/
public void querySubject(int subject) {
// 获得连接
Connection conn = getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
// 3.ps对象
String sql = "{call proc_querySubject(?,?)}";
cs = conn.prepareCall(sql);
cs.setInt(1, subject);
cs.registerOutParameter(2, OracleTypes.CURSOR);
// 4.执行sql
cs.execute();
// 5.获取数据
rs = (ResultSet) cs.getObject(2);
boolean flag=false;//标识是否找到数据
while (rs.next()) {
System.out.println(rs.getInt(7) + "、" + rs.getString(1) + "\n选项A:" + rs.getString(2) + "\n选项B:"
+ rs.getString(3) + "\n选项C:" + rs.getString(4) + "\n选项D:" + rs.getString(5) + "\n答案:"
+ rs.getString(6) + "\n");
flag=true;//有数据
}
//没找到数据
if(flag==false)
System.out.println("无匹配结果!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭连接
closeAll(rs, cs, conn);
}
}
/**
* 按题干模糊查询
*/
public void queryQuestion(String stem) {
// 获得连接
Connection conn = getConnection();
ResultSet rs = null;
try {
// 3.创建st对象
Statement st = conn.createStatement();
// 4.执行sql语句
StringBuffer sql = new StringBuffer(
"select question,optionA,optionB,optionC,optionD,answer,questionId from question_info");
sql.append(" where question like '%" + stem + "%'");
rs = st.executeQuery(sql.toString());
while (rs.next()) {
System.out.println(rs.getInt(7) + "、" + rs.getString(1) + "\n选项A:" + rs.getString(2) + "\n选项B:"
+ rs.getString(3) + "\n选项C:" + rs.getString(4) + "\n选项D:" + rs.getString(5) + "\n答案:"
+ rs.getString(6) + "\n");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭连接
closeAll(rs, null, conn);
}
}
/**
* 按题干模糊查询(存储过程)
*/
public void queryQuestion1(String stem) {
// 获得连接
Connection conn = getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
// 3.ps对象
String sql = "{call proc_queryQuestion(?,?)}";
cs = conn.prepareCall(sql);
cs.setString(1, stem);
cs.registerOutParameter(2, OracleTypes.CURSOR);
// 4.执行sql
cs.execute();
// 5.获取数据
rs = (ResultSet) cs.getObject(2);
boolean flag=false;//标识是否找到数据
while (rs.next()) {
System.out.println(rs.getInt(7) + "、" + rs.getString(1) + "\n选项A:" + rs.getString(2) + "\n选项B:"
+ rs.getString(3) + "\n选项C:" + rs.getString(4) + "\n选项D:" + rs.getString(5) + "\n答案:"
+ rs.getString(6) + "\n");
flag=true;
}
//没找到数据
if(flag==false)
System.out.println("无匹配结果!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭连接
closeAll(rs, cs, conn);
}
}
/**
* 添加试题
*/
public void add(QuestionInfo info) {
// 1.sql语句
String sql = "insert into question_info values(seq_qid.nextval,?,?,?,?,?,?,?)";
// 2.定义参数数组
Object[] param = new Object[] { info.getQuestion(), info.getOptionA(), info.getOptionB(), info.getOptionC(), info.getOptionD(),
info.getSubject(), info.getAnswer() };
// 3.执行
int count = executeUpdateSql(sql, param);
if (count > 0) {
System.out.println("数据添加成功!");
} else {
System.out.println("数据添加失败!");
}
}
/**
* 删除试题
*/
public void del(int id) {
// 1.sql语句
String sql = "delete question_info where questionId=?";
// 2.参数数组
Object[] param = new Object[] { id };
// 3.执行
int count = executeUpdateSql(sql, param);
if (count > 0) {
System.out.println("删除用户信息成功!");
} else {
System.out.println("删除用户信息失败!");
}
}
}
封装连接和关闭数据库的类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* JDBC连接数据库 BaseDao类主要封装连接数据库的功能
*
* @author cxp
* @data 2016-09-09
* @modify
*
*/
public class BaseDao {
/**
* 1.获得连接对象
*
* @return
*/
public Connection getConnection() {
Connection conn = null;
try {
// 加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 获得连接
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ORCL", "cxp", "123");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 2.关闭连接
*
* @param rs
* @param ps
* @param conn
*/
public void closeAll(ResultSet rs, PreparedStatement ps, Connection conn) {
// rs
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// con
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 添加,更新,删除
* @param sql
* @param param
* @return
*/
public int executeUpdateSql(String sql, Object... param) {
//1.获得连接
Connection conn=getConnection();
//2.声明ps对象
PreparedStatement ps=null;
try {
//3.从conn获得ps对象
ps=conn.prepareStatement(sql);
//4.设置参数
if(param!=null){
for (int i = 0; i < param.length; i++) {
ps.setObject(i+1,param[i] );
}
}
//5.执行sql语句
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
closeAll(null, ps, conn);
}
return 0;
}
}