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