JDBC的使用

JDBC原理

   JDBC定义了一组操作数据库的接口，不同的数据库厂商提供了JDBC接口的实现，这个实现称为JDBC驱动。这样就可以通过JDBC接口来对不同数据库获得统一的操作。

 

JDBC常用类

1. java.sql.DriverManager: 管理一组 JDBC 驱动程序的基本服务。
2. java.sql.Connection: 与特定数据库的连接（会话）。
3. java.sql.Statement: 用于执行静态 SQL 语句并返回它所生成结果的对象。
4. java.sql.ResultSet: 表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。
5. java.sql.PreparedStatement

6. java.sql.CallableStatement

 

JDBC操作数据库的一般步骤

//1：注册驱动类    
    Class.forName("com.mysql.jdbc.Driver");    
//2：创建数据库连接
    Connection conn = DriverManager.getConnection(dburl, user, password);    
//3：创建执行SQL的对象
    Statement stmt = conn.createStatement();    
//4：执行SQL，并获取返回结果
    ResultSet rs = stmt.executeQuery(testsql);    
//5：处理返回结果，此处打印查询结果
    while (rs.next()) {    
         ....
    }    
//6：关闭数据库连接
    conn.close();

本人封装的简单JDBC工具类SqlBean

package can.javaBean;
import java.sql.*;

public class sqlBean {
        public    Connection conn = null;
        public    Statement stmt = null;
        public    ResultSet rs = null;
        
        public boolean connectDB(String DatabaseDriver, String url) {        
                conn = null;
                
                //打开数据库连接
                try {
                        Class.forName(DatabaseDriver);    
                        conn = DriverManager.getConnection(url);
                        return true;                        
                }catch(ClassNotFoundException ex) {
                        ex.printStackTrace();
                }catch(SQLException ex) {
                        ex.printStackTrace();
                }
                return false;
        }
        
        public boolean closeConn() {
                //关闭数据库连接
                try{
                        if(stmt != null){
                                stmt.close();
                                stmt = null;
                        }
                        
                        if(conn != null){
                                conn.close();
                                conn = null;
                        }
                        return true;
                }catch(SQLException ex) {
                        ex.printStackTrace();
                        return false;
                }
        }
        
        public ResultSet execQuery(String sql) {
                //执行查询
                rs = null;
                if (conn != null && sql != null) {
                        try{
                                stmt = conn.createStatement();
                                rs = stmt.executeQuery(sql);
                        }catch(SQLException ex)        {
                                ex.printStackTrace();
                        }
                }
                return rs;
        }
        
        public int execUpdate(String sql) {
                //执行更新或删除
                int rows = 0;
                
                if (conn != null && sql != null){
                        try{
                                stmt = conn.createStatement();
                                rows = stmt.executeUpdate(sql);
                        }catch(SQLException ex)        {
                                ex.printStackTrace();
                        }
                }
                return rows;
        }
}

web.xml相应设置

<context-param>        
         <param-name>context/driver</param-name>        
         <param-value>com.mysql.jdbc.Driver</param-value>
         <param-name>context/url</param-name>
         <param-value><![CDATA[jdbc:mysql://localhost/teachingSystem?user=canny&password=123456&useUnicode=true&characterEncoding=GB2312]]></param-value>
</context-param>

servlet使用例子

doPost()

SqlBean sqlBean = new SqlBean();

String driver = getServletContext().getInitParameter("context/driver");
String url = getServletContext().getInitParameter("context/url");

if (sqlBean.connectDB(driver, url) != true) {
        errorMsg = "数据库连接出错coon";
        writeMsg(response, errorMsg);
        return ;
}

String sql = "select * from studentInfo where stu_no='" + infoStr[0]+ "'";
ResultSet rs = sqlBean.execQuery(sql);
if (rs == null) {
        errorMsg = "数据库连接出错rs";
        writeMsg(response, errorMsg);
        return ;
}

try {//catch rs.next()的exception
        if (rs.next()){    
                //存在重复
                errorMsg = "所输入的学号已经存在了";
                writeMsg(response, errorMsg);
        }else{ //进行添加
                sql = "insert into studentInfo values('" + infoStr[0] + "','"
                     + infoStr[1] + "','" + infoStr[2] +"','" + infoStr[3]+ "','"
                     + infoStr[4] + "','" + infoStr[5] +"','123456')";
        
                int row = sqlBean.execUpdate(sql);
                if (row == 1) {
                        errorMsg = "添加信息成功!";
                        onClickStr = "inputstudent.jsp";
                }
                else
                        errorMsg = "添加信息失败";
                
                writeMsg(response, errorMsg);
        }
}catch(SQLException ex)  {
        errorMsg = "数据库连接出错rs";
        writeMsg(response,errorMsg);
}finally  {
        sqlBean.closeConn();
}