JDBC连接数据库小示例

主要的实现类

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

}