jdbc初步（转）

1. Jdbc的六个编程步骤

1. 注册一个驱动

注册驱动程序有三种方式：

方式一：Class.forName(“oracle.jdbc.driver.OracleDriver”);

JAVA 规范中明确规定：所有的驱动程序必须在静态初始化代码块中将驱动

注册到驱动程序管理器中。

方式二：Driver drv = new oracle.jdbc.driver.OracleDriver();

DriverManager.registerDriver(drv);

方式三：编译时在虚拟机中加载驱动

javac –Djdbc.drivers = oracle.jdbc.driver.OracleDriver xxx.java

java –D jdbc.drivers=驱动全名 类名

使用系统属性名，加载驱动 -D 表示为系统属性赋值

附 : mysql 的 Driver 的全名 com.mysql.jdbc.Driver

SQLServer 的 Driver 的全名 com.microsoft.jdbc.sqlserver.SQLServerDriver

2. 取得一个连接

Conn = DriverManager.getConnection(jdbcURL,userName,password);

3. 获得一个Statement声明对象

Statement st = conn.createStatement();

PreparedStatement ps = conn.prepareStatement(sql);

CallableStatemetn cs = conn.prepareCall(sql);

4. 通过Statement对象执行Sql语句

sta.execute(String sql); 如果返回一个结果集则返回true,否则返回false。

sta.executeQuery(String sql);返回一个查询结果集。

sta.executeUpdate(String sql);返回值为 int 型，表示影响记录的条数。

将 sql 语句通过连接发送到数据库中执行，以实现对数据库的操作。

5. 若有结果集ResultSet则处理结果集

ResultSetMetaDate rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

While(rs.next()){

for(int i = 1; i <= columnCount; i++)

{

if(i != 1) System.out.print(",");

String name = rsmd.getColumnName(i); //取得第i列的列名称

String value = rs.getString(i); //取得第i列的值

System.out.print(name + "=" + value);

}

}

6. 关闭资源

2. 第一个Jdbc程序

1. public class AccountDaoBad
2. {
3. public void select()
4. {
5. Connection con = null;
6. Statement st = null;
7. ResultSet rs = null;
8. try
9. {
10. Class.forName("oracle.jdbc.driver.OracleDriver");
11. /** static init block in OracleDriver*/
12. String jdbcURL = “jdbc:oracle:thin:@192.168.0.5:1521:tarena”;
13. con = DriverManager.getConnection(jdbcURL, "sd0703", "sd0703");
14. st = con.createStatement();
15. System.out.println(st.getFetchSize());
16. String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account";
17. rs = st.executeQuery(sql);
18. while(rs.next())
19. {
20. System.out.print("id=" + rs.getInt(1));
21. System.out.print(",no=" + rs.getString(2));
22. System.out.print(",owner=" + rs.getString(3));
23. System.out.print(",pwd=" + rs.getString(4));
24. System.out.print(",cdate=" + rs.getDate(5));
25. System.out.println(",balance=" + rs.getDouble(6));
26. }
27. }catch(SQLException e)
28. {
29. e.printStackTrace();
30. throw new RuntimeException(e.getMessage());
31. }catch(ClassNotFoundException e)
32. {
33. e.printStackTrace();
34. throw new RuntimeException(e.getMessege());
35. }finally
36. {
37. try{ con.close();}catch(Exception e) {e.printStatckTrace();}
38. }
39. }

public class AccountDaoBad
{
    public void select()
    {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            /** static init block in OracleDriver*/
String jdbcURL = “jdbc:oracle:thin:@192.168.0.5:1521:tarena”;
            con = DriverManager.getConnection(jdbcURL, "sd0703", "sd0703");
            st = con.createStatement();
            System.out.println(st.getFetchSize());
            String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account";
            rs = st.executeQuery(sql);
            while(rs.next())
            {
                System.out.print("id=" + rs.getInt(1));
                System.out.print(",no=" + rs.getString(2));
                System.out.print(",owner=" + rs.getString(3));
                System.out.print(",pwd=" + rs.getString(4));
                System.out.print(",cdate=" + rs.getDate(5));
                System.out.println(",balance=" + rs.getDouble(6));
            }
        }catch(SQLException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());

        }catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new RuntimeException(e.getMessege());

        }finally
        {
            try{ con.close();}catch(Exception e) {e.printStatckTrace();}
        }
    }

3. 常用类

1. Connection, Statement, ResultSet,

4. 2.0新特性

(1)事务管理与图片的存储

1. public class ImageLibraryService
2. {
3. public void addImage(long id, String imageName, String path) throws SQLException
4. {
5. Connection con = null;
6. PreparedStatement ps = null;
7. ResultSet rs = null;
8. try
9. {
10. con = ConnectionFactory.getConnection();
11. con.setAutoCommit(false);
12. String sql = "insert into ImageLibrary(id, name, image)";
13. sql += " VALUES(?, ?, empty_blob())";
14. ps = con.prepareStatement(sql);
15. ps.setLong(1, id);
16. ps.setString(2, imageName);
17. ps.executeUpdate();
18. ps.close();
19. ps = con.prepareStatement("select image from ImageLibrary WHERE id = ? for update ");
20. ps.setLong(1, id);
21. rs = ps.executeQuery();
22. if (rs.next())
23. {
24. Blob image = rs.getBlob(1);
25. OutputStream out = image.setBinaryStream(0);
26. BufferedOutputStream bufferedOut = new BufferedOutputStream(out);
27. BufferedInputStream bufferedIn = new BufferedInputStream(new FileInputStream(path));
28. int c;
29. while ((c = bufferedIn.read()) != -1)
30. {
31. bufferedOut.write(c);
32. }
33. bufferedIn.close();
34. bufferedOut.close();
35. }
36. con.commit();
37. } catch (Exception e)
38. {
39. e.printStackTrace();
40. try
41. {
42. con.rollback();
43. } catch (SQLException se) { }
44. throw new SQLException(e.getMessage());
45. } finally
46. {
47. JdbcUtil.close(rs, ps, con);
48. }
49. }
50. //以下代码没有进行细致的异常捕获
51. public void restoreImage(long id, String filename) throws Exception
52. {
53. Connection con = ConnectionFactory.getConnection();
54. Statement st = con.createStatement();
55. String sql = "SELECT image From ImageLibrary Where id = " + id;
56. ResultSet rs = st.executeQuery(sql);
57. while (rs.next())
58. {
59. Blob image = rs.getBlob("image");
60. BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(filename));
61. BufferedInputStream in = new BufferedInputStream(image.getBinaryStream());
62. int c;
63. while ((c = in.read()) != -1) out.write(c);
64. in.close();
65. out.close();
66. }
67. }
68. }

public class ImageLibraryService
{
    public void addImage(long id, String imageName, String path) throws SQLException
    {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            con = ConnectionFactory.getConnection();
            con.setAutoCommit(false);
            String sql = "insert into ImageLibrary(id, name, image)";
            sql += " VALUES(?, ?, empty_blob())";
            ps = con.prepareStatement(sql);
            ps.setLong(1, id);
            ps.setString(2, imageName);
            ps.executeUpdate();
            ps.close();
            ps = con.prepareStatement("select image from ImageLibrary WHERE id = ? for update ");
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if (rs.next())
            {
                Blob image = rs.getBlob(1);
                OutputStream out = image.setBinaryStream(0);
                BufferedOutputStream bufferedOut = new BufferedOutputStream(out);
                BufferedInputStream bufferedIn = new BufferedInputStream(new FileInputStream(path));
                int c;
                while ((c = bufferedIn.read()) != -1)
                {
                    bufferedOut.write(c);
                }
                bufferedIn.close();
                bufferedOut.close();
            }
            con.commit();
        } catch (Exception e)
        {
            e.printStackTrace();
            try
            {
                con.rollback();
            } catch (SQLException se)  {    }
            throw new SQLException(e.getMessage());
        } finally
        {
            JdbcUtil.close(rs, ps, con);
        }
    }
//以下代码没有进行细致的异常捕获
    public void restoreImage(long id, String filename) throws  Exception
    {
        Connection con = ConnectionFactory.getConnection();
        Statement  st = con.createStatement();
String sql = "SELECT image  From ImageLibrary Where id = " + id;
        ResultSet rs = st.executeQuery(sql);
        while (rs.next())
        {
           Blob image = rs.getBlob("image");
           BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(filename));
            BufferedInputStream in = new BufferedInputStream(image.getBinaryStream());
           int c;
           while ((c = in.read()) != -1)   out.write(c);
           in.close();
           out.close();
        }
    }
}

(2)结果集游标的上下游动

1. con = ConnectionFactory.getConnection();
2. st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
3. String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account";
4. rs = st.executeQuery(sql);
5. rs.afterLast(); //将光标移动到此 ResultSet 对象的末尾，正好位于最后一行之后。
6. while(rs.previous())
7. {
8. System.out.print("id=" + rs.getInt(1));
9. System.out.print(",no=" + rs.getString(2));
10. System.out.print(",owner=" + rs.getString(3));
11. System.out.print(",pwd=" + rs.getString(4));
12. System.out.print(",cdate=" + rs.getDate(5));
13. System.out.println(",balance=" + rs.getDouble(6));
14. }

con = ConnectionFactory.getConnection();
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
String sql = "select id,no,owner,pwd,cdate,balance from sd0703_account";
rs = st.executeQuery(sql);
rs.afterLast(); //将光标移动到此 ResultSet 对象的末尾，正好位于最后一行之后。
while(rs.previous())
{
     System.out.print("id=" + rs.getInt(1));
     System.out.print(",no=" + rs.getString(2));
     System.out.print(",owner=" + rs.getString(3));
     System.out.print(",pwd=" + rs.getString(4));
     System.out.print(",cdate=" + rs.getDate(5));
     System.out.println(",balance=" + rs.getDouble(6));
}

注：ResultSet静态常量字段(参见javax.sql.ResultSet)

ResultSet.TYPE_SCROLL_INSENSITIVE

该常量指示可滚动但通常不受 ResultSet 底层数据更改影响的 ResultSet 对象的类型。

ResultSet.CONCUR_READ_ONLY

该常量指示不可以更新的 ResultSet 对象的并发模式。

http://www.cnblogs.com/smileallen/archive/2013/02/16/3391573.html