jdbc初步（转）

转载

byte01 2022-08-25 09:32:01

文章标签 jdbc sql oracle java 文章分类 虚拟化云计算

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程序

[java] view plaincopyprint?

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

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)事务管理与图片的存储

[java] view plaincopyprint?

1. public class ImageLibraryService
2. {
3. public void addImage(long id, String imageName, String path) throws SQLException
4. {
5. null;
6. null;
7. null;
8. try
9. {
10. con = ConnectionFactory.getConnection();
11. false);
12. "insert into ImageLibrary(id, name, image)";
13. " VALUES(?, ?, empty_blob())";
14. ps = con.prepareStatement(sql);
15. 1, id);
16. 2, imageName);
17. ps.executeUpdate();
18. ps.close();
19. "select image from ImageLibrary WHERE id = ? for update ");
20. 1, id);
21. rs = ps.executeQuery();
22. if (rs.next())
23. {
24. 1);
25. 0);
26. new BufferedOutputStream(out);
27. 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. "image");
60. new BufferedOutputStream(new FileOutputStream(filename));
61. 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)结果集游标的上下游动

[java] view plaincopyprint?

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. "id=" + rs.getInt(1));
9. ",no=" + rs.getString(2));
10. ",owner=" + rs.getString(3));
11. ",pwd=" + rs.getString(4));
12. ",cdate=" + rs.getDate(5));
13. ",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));
}

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 对象的并发模式。