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程序
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)事务管理与图片的存储
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)结果集游标的上下游动
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 对象的并发模式。