JDBC入门学习

作者: 须臾之北 | 来源:发表于2018-06-02 23:31 被阅读11次

JDBC学习

JAVA Database Connectivity java 数据库连接

为什么会出现JDBC

SUN公司提供的一种数据库访问规则、规范, 由于数据库种类较多，并且java语言使用比较广泛，sun公司就提供了一种规范，让其他的数据库提供商去实现底层的访问规则。我们的java程序只要使用sun公司提供的jdbc驱动即可。

JDBC的步骤

load the driver

此时的意思是向DriverManager注册驱动
```
 try {
     Class.forName(driverClass);
 }
 catch(ClassNotFoundException e) {
     e.printStackTrace();
 }
```
forName() throws ClassNotFoundException

driverClass为相应的驱动类 eg:Oracle.jdbc.driver.OracleDriver

connect to database

 try {
     conn = DriverManager.getConnection(url,user,password);
 }
 catch(SQLException e) {
     e.printStackTrace();
 }

getConnection()方法抛出SQLException

execute sql statement

 Statement stmt = null;
 
 stmt = conn.createStatement();
     
 String sql = "select * from dept";
 rs = stmt.executeQuery(sql);

retrieve the data

 int deptno = 0;
 String dname = null;
 String loc = null;
     
 while(rs.next()) {
     deptno = rs.getInt("deptno");
     dname = rs.getString("dname");
     loc = rs.getString("loc");
     System.out.println("deptno = " + deptno +"  dname = " + dname + " loc = " + loc);
 }

close the resource

 rs.close();
 stmt.close();
 conn.close();

程序整体

 public static void main(String[] args) {
     Connection connection = null;
     Statement statement = null;
     ResultSet result = null;
     
     try {
         //注册驱动
         Class.forName("com.mysql.jdbc.Driver");
         
         //建立连接
         connection = DriverManager.getConnection("jdbc:mysql://localhost/student", "root", "root");
         
         //创建语句
         statement = connection.createStatement();
         
         //执行查询，得到结果集
         String sql = "select * from t_stu";
         result = statement.executeQuery(sql);
         
         //遍历结果集
         while(result.next()) {
             int id = result.getInt("id");
             String name = result.getString("name");
             int age = result.getInt("age");
             
             System.out.println("id = " + id + " name = " + name + " age = " + age);          
         }
     }catch(ClassNotFoundException e) {
         e.printStackTrace();
     } catch(SQLException e) {
         e.printStackTrace();
     } finally {
         JDBCUtil.closeResource(connection, statement, result);
     }           
 }

JDBC工具类的创建

因为有关JDBC的操作中经常性的会用到以上步骤，所以最好是写入包装类，等到使用时直接添加即可

工具类名称:JDBCUtil

配置文件的生成--new file--jdbc.properties

需要的类有：java.util.Properities

driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost/student
user = root
password = root

配置文件的使用

static中使用

static {
//创建属性配置对象
Properties properties = new Properties();

//使用类加载器，读取src底下的资源文件
InputStream iStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
    
//导入输入流:
try {
    properties.load(iStream);
} catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

//读取属性
driverClass = properties.getProperty("driverClass");
url = properties.getProperty(url);
name = properties.getProperty(name);
password = properties.getProperty(password);

}

将 load the driver 和 Connect to database 合并为getConne()方法

    public static Connection getConn() {
        Connection conn = null;
    
        //--load the driver
        try {
            Class.forName(driverClass);
        }
        catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        
        //--connect to database;
    
        try {
            conn = DriverManager.getConnection(url,user,password);
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        
        return conn;
}

添加closeResource()方法

主程序中finally中之执行closeResource()方法，进行资源释放

public static void closeResource(ResultSet rs,Statement stmt,Connection conn){
    closeRs(rs);
    closeStmt(stmt);
    closeConn(conn);
}

public static void closeResource(PreparedStatement pstmt,Connection conn){
    closeStmt(pstmt);  //preparedStatement是statement的一个子类
    closeConn(conn);

}

static void closeRs(ResultSet rs) {
    try {
        if(rs != null)
            rs.close();         
    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    finally {
        rs = null;
    }
}

static void closeStmt(Statement stmt) {
    try {
        if(stmt != null)
            stmt.close();
    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    finally {
        stmt = null;
    }
}

static void closeConn(Connection conn) {
    try {
        if(conn != null)
            conn.close();
    }
    catch(SQLException e) {
        e.printStackTrace();
    }
    finally {
        conn = null;
    }
}

此时注意方法的重载

JDBC的DML使用

预备知识
JUnit的使用：

①添加Junit的lib

②new Junit Test Case

testQuery()
--测试查询

 public void testQuery() {
     Connection conn = JDBCUtil.getConn();       
     Statement stmt = null;
     ResultSet rs = null;
 
     
     try {
         //execute sql statement
         stmt = conn.createStatement();
         
         String sql = "select * from dept";
         rs = stmt.executeQuery(sql);

         /*
             注意:executeQuery()返回的是ResultSet
          */
         
         
         //retrieve the data             
         int deptno = 0;
         String dname = null;
         String loc = null;
         
         while(rs.next()) {
             deptno = rs.getInt("deptno");
             dname = rs.getString("dname");
             loc = rs.getString("loc");
             System.out.println("deptno = " + deptno +"  dname = " + dname + " loc = " + loc);
         }
         
     } catch (SQLException e) {
         e.printStackTrace();
     } finally {
         //close the resource
         JDBCUtil.closeResource(rs, stmt, conn);
     }       
 }

testInsert()

 public void testInsert() {
     Connection conn = JDBCUtil.getConn();       
     Statement stmt = null;

     try {
         //execute sql statement
         stmt = conn.createStatement();
         
         String sql = "insert into dept values(50,'What','Shanxi')";
         int result = stmt.executeUpdate(sql);
         
         if(result == 0) {
             System.out.println("添加失败");
         }
         else {
             System.out.println("添加成功");
         }
     }catch(SQLException e) {
         e.printStackTrace();
     }finally {
         JDBCUtil.closeResource(stmt, conn);
     }
 }

testInsert_2()--使用preparedStatement

 public void testInsert_2() {
     Connection conn = JDBCUtil.getConn();       
     PreparedStatement prestmt = null;

     try {
         //execute sql statement
         prestmt = conn.prepareStatement("insert into dept values(?,?,?)");
         prestmt.setInt(1, 55);
         prestmt.setString(2,"hahahaha");
         prestmt.setString(3, "henan");
         int result = prestmt.executeUpdate();   
         
         if(result == 0) {
             System.out.println("添加失败");
         }
         else {
             System.out.println("添加成功");
         }
         
     }catch(SQLException e) {
         e.printStackTrace();
     }finally {
         JDBCUtil.closeResource(prestmt, conn);   //此时运用到多态
     }
 }

testDelete()

public void testDelete() {
    Connection conn = JDBCUtil.getConn();       
    Statement stmt = null;

    try {
        //execute sql statement
        stmt = conn.createStatement();
        
        String sql = "delete from dept where deptno = 55";
        int result = stmt.executeUpdate(sql);
        
        if(result == 0) {
            System.out.println("删除失败");
        }
        else {
            System.out.println("删除成功");
        }
    }catch(SQLException e) {
        e.printStackTrace();
    }finally {
        JDBCUtil.closeResource(stmt, conn);
    }
}

testUpdate()

 @Test
 public void testUpdate() {
     Connection conn = JDBCUtil.getConn();       
     Statement stmt = null;

     try {
         //execute sql statement
         stmt = conn.createStatement();
         
         String sql = "update dept set dname = 'xixixixi' where deptno = 55";
         int result = stmt.executeUpdate(sql);
         
         if(result == 0) {
             System.out.println("更新失败");
         }
         else {
             System.out.println("更新成功");
         }
     }catch(SQLException e) {
         e.printStackTrace();
     }finally {
         JDBCUtil.closeResource(stmt, conn);
     }
 }

DAO模式

Data Access Object 访问数据对象

可以实现业务逻辑与数据库访问相分离

一般的DAO的封装由以下另个原则：

一个表对应一个dao接口，对应一个相应实现类。
对于DAO接口，必须由具体的类型定义。这样可以避免被错误地调用

一般步骤

创建相关表的接口--new Interface--DeptDao--此时例子是对dept表进行操作

里面封装相应对于此表的操作：eg: query,delete,update,insert
```
 package com.anna.dao;

 public interface DeptDao {
     //查询所有
     public void findAll();  
 }
```

创建接口的实现类--一般与接口在不同包中

----继承接口

----实现接口中的抽象方法

 public class DeptDaoImpl implements DeptDao{
 
     public void findAll() {

         //Connect to database
         Connection conn = JDBCUtil.getConn();
         
         //execute sql statement
         Statement stmt= null;
         ResultSet rs = null;
         
         try {
              stmt = conn.createStatement();
              
              String sql = "select * from dept";
              rs = stmt.executeQuery(sql);
              
              while(rs.next()) {
                  int deptno = rs.getInt("deptno");
                  String dname = rs.getString("dname");
                  String loc = rs.getString("loc");
                  
                  System.out.println("deptno = " + deptno + "  dname = " + dname + "  loc = " + loc + "\n");
              }
         }
         catch(SQLException e) {
             e.printStackTrace();
         }finally {
             JDBCUtil.closeResource(stmt,conn);
         }
     }
 }

利用JUnit测试相关方法

 public class TestDeptDaoImpl {
     
     @Test
     public void testFindAll() {
         DeptDao dd = new DeptDaoImpl();   //接口引用指向实现类
         dd.findAll();
     }
 }

Statement的安全问题

Statement执行，拼接sql语句的。先拼接sql语句，然后在一起执行。

---此时可能会导致错误

PreparedStatement的学习

由于statement本身存在的问题，引出了PreparedStatement

//execute sql statement
    PreparedStatement pstmt= null;      
    ResultSet rs = null;
    
    try {
         String sql = "select * from dept where deptno = ? and dname = ? and loc = ?"; 
         pstmt = conn.prepareStatement(sql);
         pstmt.setInt(1, deptno);
         pstmt.setString(2,dname);
         pstmt.setString(3,loc);
         
         rs = pstmt.executeQuery();          
         if(rs.next()) {
             System.out.println("验证成功");
         }
         else {
             System.out.println("验证失败");
         }
    }
    catch(SQLException e) {
        e.printStackTrace();
    }finally {
        JDBCUtil.closeResource(pstmt,conn);
    }

总结

JDBC的学习
JDBC工具类的创建
JDBC的DML的实现
DAO模式的了解
PreparedStatement的学习

JDBC入门学习

JDBC学习

JDBC的步骤

JDBC工具类的创建

工具类名称:JDBCUtil

主程序中finally中之执行closeResource()方法，进行资源释放

JDBC的DML使用

JUnit的使用：

①添加Junit的lib

②new Junit Test Case

DAO模式

一般步骤

Statement的安全问题

PreparedStatement的学习

总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读