javaWeb学习笔记
JDBC
JDBC常用接口和类简介
DriverManager
用于管理JDBC驱动的服务类。主要功能是获得Connetion对象
public static Connection getConnection(String url,String user,String pass)throws SQLException
Connection
代表数据库连接对象,每个Connection代表一个物理连接对象。这是个interface,数据库的驱动jar包里,
应该有这些接口的实现。
●Statement createStatement() throws SQLException
●PreparedStatement prepareStatement(String sql)throws SQLException
●CallableStatement PrepareCall(String sql) 用于调用存储过程
处理事物
- void commit()
- void rollback()
Statement
执行SQL语句的工具接口。DCL.DDL.DML
- ResultSet executeQuery(String sql)
- int executeUpdate(String sql)
- boolean execute(String sql)
PreparedStatement
- void setXXX(int parameterIndex,Xxx value);
ResultSet
- boolena previous()
- boolean next()
jdbc编程步骤
●加载数据库驱动
Class.forName(“com.mysql.jdbc.Driver”)
Class.forName(“Oracle.jdbc.driver.OracleDriver”)
●获得数据库连接
DriverManager.getConnection(String url,String user,String pass)
url:
jdbc:mysql://hostname:port/databasename
jdbc:oracle:thin:@hostname:port:databasename
●通过Connection创建Statement对象
- createStatement()
- prepareStatement(String sql)
- prepareCall(String sql)
●使用Statement执行SQL语句
- execute
- executeUpdate
- executeQuery
●操作结果集
- next previous等移动记录指针
- getXxx获取记录指针指向行
●回收数据库资源 ResultSet Statement Connection
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cars","root","lyh1988");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from user_table");
while(rs.next()){
System.out.println("user_name:"+rs.getString("user_name"));
}
if (conn != null && !conn.isClosed()){
conn.close();
}
if(stmt != null){
stmt.close();
}
if(rs != null){
rs.close();
}
}
执行SQL语句的方式
- 使用executeUpdate执行DDL和DML语句
- 前面是executeQuery执行查询语句,执行DDL返回0,执行DML返回受影响的记录条数
把数据库连接信息放在mysql.ini中使用Properties来加载
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cars","root","lyh1988");
Statement stmt = conn.createStatement();
String sql= "create table mytest_table "
+ "(my_id int auto_increment primary key,"
+ "my_name varchar(255) not null default 'xx',"
+ "my_password varchar(255) not null)";
stmt.executeUpdate(sql);
if (conn != null && !conn.isClosed()){
conn.close();
}
if(stmt != null){
stmt.close();
}
}
使用execute执行
- 只是返回ture或者false,表示是否返回了ResultSet
- 如果是 Statement 的getResult() 得到ResultSet
- 如果不是用 Statement 的getUpdateCount()得到行数
getString(int ) getString(String)
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cars","root","lyh1988");
Statement stmt = conn.createStatement();
boolean hasrs= stmt.execute("select * from user_table");
if(hasrs){
ResultSet rs = stmt.getResultSet();
ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
while(rs.next()){
for( int i = 0; i < columns; i++){
System.out.print(rs.getString(i+1)+"\t");
}
System.out.println("\n");
}
}
if (conn != null && !conn.isClosed()){
conn.close();
}
if(stmt != null){
stmt.close();
}
}
使用PreparedStatement执行SQL语句
public static void main(String[] args) {
Connection conn = null;
// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cars","root","lyh1988");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
long start = System.currentTimeMillis();
try {
Statement stmt = conn.createStatement();
for(int i = 0;i < 100; i++){
stmt.executeUpdate("insert into mytest_table values("+(i+1)+",'myname','mypass')");
}
long end = System.currentTimeMillis();
System.out.println(" statement time is :"+ (end - start));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
long start1 = System.currentTimeMillis();
PreparedStatement ps = conn.prepareStatement("insert into mytest_table values( ?,'myname2','mypass2')");
for (int i = 200; i < 300; i++){
ps.setInt(1, i);
ps.execute();
}
long end1 = System.currentTimeMillis();
System.out.println(" Preparedstatement time is :"+ (end1 - start1));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
使用CallableStatement调用存储过程
管理结果集
可滚动、可更新的结果集
absolute、previous,beforeFirst,first,last,afterLast启动指针
pstmt=conn.prepateStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE);
public static void main(String[] args) {
Connection conn = null;
// TODO Auto-generated method stub
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cars","root","lyh1988");
PreparedStatement stmt = conn.prepareStatement("select * from mytest_table",ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery();
rs.last();
int rowCount = rs.getRow();
for(int i = rowCount; i > 0; i--){
rs.absolute(i);
System.out.print(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\n");
rs.updateString(2,"学生名"+i);
rs.updateRow();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
处理Blob类型数据
binary long object
使用ResultSetMetaData分析结果集
- int getColumnCount();
- Stirng getColumnName(int column)
- int getColumnType(int column)
事物处理
事物是由一步或几步数据库操作序列组成的逻辑执行单元,这系列操作要么全部执行,要么全部放弃执行
Atomicity consistency isolation durability
jdbc连接的事物支持
conn.setAutoCommit(false);
..
conn.commit();
Savapoint setSavepoint();
rollback(Savepoint saveponit);
批量更新
DatabaseMetaData的supportBatchUpdates() 是否支持批量更新
boolean autoCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.addBatch(sql1);
stmt.addBatch(sql2)
stmt.addBatch(sql3)
...
stmt.executeBatch();
conn.commit();
conn.setAutoCommit(autoCommit );
分析数据库信息
插入(C) 查询(R)修改(U)删除(D)
使用连接池管理连接
程序中的连接 物理内存 文件
conn——-> | |\
| | \
—— ○
conn——–>| | /
/
| |/
每个conn 对应一个物理连接,也就是在内存中保存关于,文件系统中数据库的信息
数据库连接池的常用参数
数据库的初始连接数
连接池的最大连接数
连接池的最小连接数
连接池的每次增加的容量
JDBC的数据库连接池使用javax.sql.DataSource接口来表示,通常有商用服务器来实现(webLogic,webSphere)等提供实现,也有一些开源组织提供实现,如(DBCP,和C3P0)
DBCP数据源
commons-dbcp.jar:连接池的实现;
commons-pool.jar:连接池实现的依赖库
tomcat的连接池采用该连接池实现
static BaseDataSource ds = new BaseDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306:/cars");
ds.setUsername("root");
ds.setPassword("pass");
ds.setInitialSize(5);
ds.setMaxActive(20);
ds.setMinIdle(2);
Connection conn = ds.getConnection();
....
conn.close();//归还给连接池
C3P0数据源
c3p0-0.9.1.2.jar