jdbc初步（转）-白红宇

jdbc初步（转）

阅读量：5813 次

发布时间：2019-06-18

本文共 9459 字，大约阅读时间需要 31 分钟。

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]

public class AccountDaoBad

{

public void select()

{

Connection con = null;

Statement st = null;

ResultSet rs = null;

{

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

}

}

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]

public class ImageLibraryService

{

public void addImage(long id, String imageName, String path) throws SQLException

{

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

{

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

{

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

}

}

}

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]

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

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

转载于:https://www.cnblogs.com/softidea/p/4396931.html

你可能感兴趣的文章

USNews大学排名遭美国计算机研究学会怒怼，指排名荒谬要求撤回

查看>>

struts1——静态ActionForm与动态ActionForm

查看>>