使用JDBC操作数据库

使用JDBC操作数据库
关键字: jdbc jdbctemplate
#1、JDBC基础
#JDBC简介:JDBC（Java Database Connectivity）是一种可以执行SQL的Java API，通过它可以用一种API操作不同的数据库。
#JDBC驱动：不同数据库间，标准的SQL语句可以移植，而数据库实际通信协议及某些数据库特征不可移植，因此，JDBC和数据库之间须还有一层，用于将JDBC调用映射成特定的数据库调用，此特殊层就是JDBC驱动程序。
常见的JDBC驱动有四种：
》JDBC-ODBC桥，是最早实现的JDBC驱动程序，目的为了快速推广JDBC，非多线程，能力有限，此驱动程序将JDBC API映射成ODBC API
》直接将JDBC API映射成数据库特定的客户端API，这种驱动程序包含特定数据库的本地代码，可用于特定数据库的客户端
》支持三层结构的JDBC访问方式，主要用于Applet阶段，通过Applet访问数据库
》纯java的，直接与数据库实例交互，智能型的，知道数据库使用的底层协议，是目前最流行的JDBC驱动

#JDBC常用接口和类简介
DriverManager：用于管理JDBC驱动的服务类，主要方法是获得Connection对象
public static synchronized Connection getConnection(String url,String user,String pass) throws SQLException
Connection:代表一个数据库连接物理会话，若放我数据库，须先获得数据库连接。常用方法
》Statement createStatement() throws SQLException 返回Statement对象
》PreparedStatement prepareStatement(Strin sql) throws SQLException 返回编译的Statement对象
》CallableStatement preparedCall(Strin sql) throws SQLException 返回的CallableStatement对象用于存储过程调度
Statement：执行DML SQL的工具接口，常用方法：
》ResultSet executeQuery(String sql) throws SQLException 执行查询，返回结果集对应的ResultSet对象
》int executeUpdate(String sql) throws SQLException 执行DML并返回受影响的行数
》boolean execute(String sql) throws SQLException 返回boolean表式执行成功与否
PreparedStatement ：是Statement的子接口，允许数据库预编译SQL，避免数据库每次重新编译，以后每次只改变SQL的参数，性能较好，常用方法
》ResultSet executeQuery() throws SQLException 执行查询，返回结果集对应的ResultSet对象
》int executeUpdate() throws SQLException 执行DML并返回受影响的行数
》boolean execute() throws SQLException 返回boolean表式执行成功与否
注：上述方法因SQL预编译，无须接手SQL字符串，只是需要接收参数，故有如下方法 void setXxx(int paramIndex,Xxx value)
ResultSet：包含访问结果集的方法，可通过列索引或列名获得列数据，常用方法
》boolean next() throws SQLException,将ResultSet定位到下一行，结果集的起始位在第一行之前
》void close()throws SQLException 释放ResultSet对象
》boolean absolute(int row)throws SQLException 将结果集移到指定行，若row是负值，则倒数移动
注：默认方法创建的ResultSet不支持absolute方法因为结果集不支持后移，若想支持，需要如此创建
：Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
：ResultSet rs=stmt.executeQuery(sql);
#传统JDBC访问数据库步骤
》通过Class.forName(String driverClass)注册数据库驱动
》通过DriverManager.getConnection(String url,String user,String password)获得数据库连接对象
》通过Connnection.createStatement()或者Connection.createPreparedStatement(String sql)创建相应的Statement对象
》通过Statement.execute(String sql)或者PreparedStatement.execute()执行相应的SQL，并返回ResultSet对象
》操作ResultSet
#2、数据库连接池
》数据库连接的建立及关闭极其耗资源，对系统性能影响尤为明显。
》传统数据库连接方式：一个数据库连接均对应一个物理连接，每次操作都要打开、关闭该物理操作，这种频繁性，会造成系统性能下降，此时，考虑数据库连接池。
》数据库连接池解决方案：当应用程序启动时，系统主动建立足够的连接（按指定的初始化数据），并将这些连接组成一个池。每次应用程序请求数据库连接时，无需重新打开连接，而是从池中取出已有的连接，使用完后，不再关闭，而是直接将该连接归还池，使用连接池，可大大提高系统运行效率。
》数据库连接池介绍
》 对于共享资源的情况，有一个通用的设计模式--资源池(Resource Pool)，用于解决资源的频繁请求、释放所造成的性能下降。为解决数据库连接的这种频繁性，JDBC2.0规范引入了数据库连接池技术，实际上，数据库连接池是Connection对象的工厂，常用参数有：
@数据库的初始连接数 @连接池的最大连接数 @连接池每次增加的连接数
》连接池的工作示意图 {JDBC标准的API并没有提供连接池的实现，仅仅提供了DataSource接口具体的实现有一些厂商提供}
》 连接池的分配与释放
@程序启动，分配初始化数目的连接，按需分配，用过归还、超时归还，当申请时无或者达到指定的最小值，按增量参数值分配新的连接
@为确保连接池中最小的连接数，通常有如下策略：
：动态--定时检查连接池，一旦发现数量小于最小连接数，则补充相应的新连接，保证连接池正常运转
：静态--空闲连接不足时，系统才检测是否达到最小连接
》连接池的实现
连接池通常包括连接池类(DBConnectionPool)和连接池管理类(DBConnectionPoolManager)：
》连接池类是某一数据库所有连接的缓冲池，主要实现功能：@从连接池获取或者创建可用连接，@使用完毕，归还给池连接，@系统关闭前，断开所有连接并释放连接占用的资源，@处理无效连接，@限制池中连接的数节目，介于最小值和最大值之间
》连接池管理类是连接池类的包装类，该类采用单态模式设计，保证系统中只有一个实例，主要用于管理多个连接池对象，主要实现以下功能：@注册数据库驱动程序，@根据配置文件，创建连接池对象，@命名、管理连接池，@跟踪连接池的使用，需要时关闭并释放资源
：数据库连接池的管理是个难点，管理不当，造成系统开销过大，将成为性能瓶颈。对于高并发的WEB应用，采用连接池技术效率和稳定性比传统的连接方式要好的多
：并发问题--数据库必须考虑此问题，对于并发，Java语言提供管理并发的支持，使用synchronized关键字可确保方法线程的安全。故，DataSource的getConnection方法必须以该该关键字修饰
public synchronized Connection getConnection(); //保证线程安全
：事务处理--JDBC的Connection本身通过设置Connection的AutoCommit属性为false，提供对事务的支持，然后，显式地调用commit或rollback方法提交或回滚事务。
连接池需要复用connection，因此，必须提供相应的事务支持机制。考虑采用每个事务独占一个连接，此法可降低事务管理复杂性。
：多数据库服务器和多用户--JDBC规范中，DataSource具备同时连接不同数据库的能力，如同时连oracle和sql server。此时，考虑使用xml配置文件来配置连接所需的相关信息。然后，提供一个Singleton模式的连接池管理类，该管理类每次启动时读取配置文件信息，创建多个连接池类的实例，每个实例对应一个数据库连接池。连接池管理类实例命名每个连接池实例，通过不同的名称管理不同的连接池。
对于多个用户访问同一个数据库的情况，也可考虑使用xml配置文件。
》常见的数据库连接池
通常，没有必要自己实现连接池。商用应用服务器都有自己的连接池实现，如WebLogic和WebSphere，其性能和稳定性绝佳，即使没有商用应用服务器，也可使用开源的连接池。目前流行的有2个：@DBCP连接池 @C3P0连接池
》DBCP连接池
Appache提供的开源实现，依赖于两个jar文件
@commons-dbcp-1.2.1.jar:连接池的实现
@commons-pool.jar:连接池实现的依赖库
Tomcat的连接池正是采用该连接池实现的。连接该连接池，既允许与应用服务器整合使用，又可由应用程序独立使用。下面的代码DBConn类通过DBCP获得数据库连接：
public class DBConn
{
private static DBConn dc;
private Connection conn = null;
private Statement stmt = null;
private DBConn()
{
}
public static DBConn instance()
{
if (dc == null)
{
dc = new DBConn();
}
return dc;
}

public Statement openStmt()
{
if (stmt == null)
{
conn = getConn();
try
{
stmt = conn.createStatement();
}
catch (Exception e)
{
System.err.println("创建Statement异常： " + e.getMessage());
}
}
return stmt;
}
public void closeStmt()
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (Exception e)
{
System.err.println("Statement关闭异常");
}
}
if (conn != null)
{
try
{
conn.close();
}
catch (Exception e)
{
System.err.println("数据库关闭异常");
}
}
}

private Connection getConn()
{
if (conn == null)
{
try
{
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUsername("root");
ds.setPassword("123456");
conn = ds.getConnection();
}
catch (Exception e)
{
e.printStackTrace();
}
}
return conn;
}
}

》C3P0连接池
Hibernate推荐使用该优秀的连接池，它实现了JDBC3.0规范的部分功能，故其性能更加突出，该池不仅可自动清理不再使用的Connection，还可以自动清理Statement和ResultSet。C3P0连接池需要jre1.3以上，推荐jre1.4
若需使用C3P0连接池，应将包c3p0-0.8.5.jar文件复制进系统。下面代码是通过C3P0连接池获得数据库连接：
public class DBConn
{
private static DBConn dc;
private Connection conn = null;
private Statement stmt = null;
private DBConn()
{
}
public static DBConn instance()
{
if (dc == null)
{
dc = new DBConn();
}
return dc;
}

public Statement openStmt()
{
if (stmt == null)
{
conn = getConn();
try
{
stmt = conn.createStatement();
}
catch (Exception e)
{
System.err.println("创建Statement异常： " + e.getMessage());
}
}
return stmt;
}
public void closeStmt()
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (Exception e)
{
System.err.println("Statement关闭异常");
}
}
if (conn != null)
{
try
{
conn.close();
}
catch (Exception e)
{
System.err.println("数据库关闭异常");
}
}
}

public Connection getConn()
{
if (conn == null)
{
try
{
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUser("root");
ds.setPassword("32147");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(2);
ds.setMaxStatements(180);
conn = ds.getConnection();
}
catch (Exception e)
{
e.printStackTrace();
}
}
return conn;
}
}

#3、Spring的JDBC体系
》Spring提供的JDBC抽象框架由core、datasource、object和support4个包组成。
core包含Spring JDBC抽象的核心类，包含各种SQLExceptionTranslator，用于将SQLException转化成spring的异常继承体系，还有DataFileMaxValueIncrementer实现，以及JdbcTemplate，持久层访问模板类。
datasource包含简化数据源连接的工具类，以及各种数据源的简单实现。通过这些实现，spring可以在j2ee之外测试jdbc代码。通过该工具类，可以从JNDI获得连接，并可关闭连接。
object包里的工具类，可将数据库的查询、更新等过程封装成类。这种方式模拟JDO的访问方式，纵横四海查询返回的“值对象”不与数据库关联。support包含了JdbcDaoSupport等工具类。
底层数据库异常被包装成org.springframework.dao中的异常，是运行时异常，故，通过JDBC抽象进行的持久操作，无需处理jdbc访问特定的异常。Spring允许将包装后的异常传播到特定的层。
》Spring的JDBC封装核心是JdbcTemplate，简化了JDBC的使用，可以处理数据库的连接和释放，故，可避免没有数据库关闭造成的连接泄露。
使用构造器JdbcTemplate(DataSource dataSource)，可通过数据源引用创建JdbcTemplate实例，实际应用中，可以通过JdbcDaoSupport取得，JdbcDaoSupport的getJdbcTemplate()方法会创建JdbcTemplate实例，所需的数据源有JdbcDaoSupport提供。
JdbcTemplate提供系列方法简化数据库访问，主要常用有：
》void execute(String sql) 主要用于执行DDL语句
》List query(String sql,Object[] args,RowMapper) 执行SQL查询，并将每条记录映射成bean实例，返回bean的实例集合
》List queryForList(String sql,Object[] args)
》Object queryForObject(String sql,RowMapper rowMapper)
》int update(String sql)
》int update(String sql,Object[] args)
》JDBC封装的回调接口
这些回调接口允许在spring的JDBC抽象体系内，使用原生JDBC查询，避免spring对JDBC封装后的灵活性不足缺点。Spring JDBC抽象体系内的回调接口有：
》CallableStatementCallback：通过该接口，可使用原生的JDBC命令调用存储过程和函数
》CallableStatementCreator：是JdbcTemplate使用的两个核心回调接口之一，通过该接口可以获得CallableStatement对象
》PreparedStatementCallback：通过该接口，可以使用原生的JDBC命令访问数据库
》PreparedStatementCreator：是JdbcTemplate使用的两个核心回调接口之一，通过该接口可以获得PreparedStatement对象
》StatementCallback：作用类似PreparedStatementCallback，只是不具备预编译功能
》Spring JDBC与传统JDBC对比
》简化连接获取方式--无须每次采用DriverManager获得连接，也不需使用JNDI查找获得连接。Spring的JDBC连接依赖IOC容器注入
》模板化操作方式--无需繁琐的getConnection，createStatement等操作
》优秀的面向对象操作方式--结果直接转化成JavaBean传出
》一致的异常继承体系--无需捕获JDBC特定的数据库异常，JDBC的checked异常被包装成了Runtime异常，不再要求强制捕捉

#4、JdbcTemplate访问数据库
》执行简单的查询 {API详见Spring API 2.0}
int queryForXxx(String sql):静态SQL命令执行查询，Xxx可以是int或long，返回一个标量值，而且只能返回一行记录
int queryForXxx(String sql,Object[] args):同上，带参数而已
Object queryForObject(String sql,Class requiredType):返回执行单行单列的查询，将返回结果转换成requiredType类型的对象
注：只支持特定类型的转换，比如，varchar只能转成String
List queryForList(String sql):
List queryForList(String sql,Object[] args):
List queryForList(String sql,Class requiredType):
List queryForList(String sql, Object[] args, Class elementType):
/*
*示例
*/
public class JdbcTemplateQuery
{

public static void main(String[] args)throws Exception
{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUser("root");
ds.setPassword("32147");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(2);
ds.setMaxStatements(180);

//创建一个JdbcTemplate
JdbcTemplate jt = new JdbcTemplate();
//为JdbcTemplate指定DataSource
jt.setDataSource(ds);
//如果只需返回一个特定值,可直接查询
int count = jt.queryForInt("select count(*) from mytable");
System.out.println(count);
//此处的转换实际非常简单：只支持Varchar->String的转换。
String nametmp = (String)jt.queryForObject("select name from mytable where name='wawa2'",String.class);
System.out.println(nametmp);


List namelist = jt.queryForList("select name from mytable");

for (Iterator it = namelist.iterator();it.hasNext(); )
{
System.out.println(it.next().getClass());
}

//返回系列值
List list = jt.queryForList("select * from mytable");

for (Iterator it = list.iterator();it.hasNext(); )
{
System.out.println(it.next().getClass());
System.out.println((Map)it.next());
}
}
}
》执行更新的
int update(String sql)
int update(String sql,Object[] args)
int update(String sql,Object[] args,int[] argTypes)：使用PreparedStatement执行更新，args用于传入参数，argTypes指定参数的SQL类型
/*
*示例
*/
public class JdbcTemplateUpdate
{

public static void main(String[] args)throws Exception
{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUser("root");
ds.setPassword("32147");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(2);
ds.setMaxStatements(180);

//创建一个JdbcTemplate
JdbcTemplate jt = new JdbcTemplate();
//为JdbcTemplate指定DataSource
jt.setDataSource(ds);

jt.update("update mytable set name = 'china' where name='aaa'");

String[] values = {"American"} ;

jt.update("update mytable set name = ? where name='china'" , values);

}
}
》执行简单DDL的
void execute(String sql)
/*
*示例
*/
public class JdbcTemplateDdl
{

public static void main(String[] args)throws Exception
{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUser("root");
ds.setPassword("123456");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(2);
ds.setMaxStatements(180);

//创建一个JdbcTemplate
JdbcTemplate jt = new JdbcTemplate();
//为JdbcTemplate指定DataSource
jt.setDataSource(ds);

jt.execute("drop table if exists wawa");
jt.execute("create table xxx(name varchar(100))");
System.out.println("正常结束");

}
}

#5、StatementCallback访问数据库
JdbcTemplate对数据库访问包装，持久化操作更简单，但降低了访问的灵活性，而通过回调接口的使用，可弥补此损失。
通过StatementCallback接口，可获得JdbcTemplate的Statement回调，使用原生的SQL命令，此时，同样可利用JdbcTemplate提供的系列优点，如一致的异常体系。
StatementCallback回调接口只有一个方法需实现：
Object doInStatement(Statement stmt)throws SQLException,DataAccessException
在该方法的执行体内，可获得Statement的引用，从而完成数据库的访问。
/*
*示例
*/
public class ExecuteStatementCallback
{

public static void main(String[] args)throws Exception
{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUser("root");
ds.setPassword("32147");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(2);
ds.setMaxStatements(180);

//创建一个JdbcTemplate
JdbcTemplate jt = new JdbcTemplate();
//为JdbcTemplate指定DataSource
jt.setDataSource(ds);

jt.execute(new StatementCallback()
{
public Object doInStatement(Statement stmt)throws SQLException
{
stmt.execute("update mytable set name = 'xx' where name ='American' ");
return null;
}
});


}
}
程序中，通常使用StatementCallback的匿名内部类创建StatementCallback实例，实现该接口，则要求实现doInStatement(Statement stmt)方法，其方法体就是实际要执行的SQL操作
#6、PreparedStatementCallback访问数据库
作用类似于StatementCallback接口，只是使用了PreparedStatement对象
/*
*示例
*/
public class ExecutePreparedStatementCallback
{

public static void main(String[] args)throws Exception
{

ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUser("root");
ds.setPassword("32147");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(2);
ds.setMaxStatements(180);

//创建一个JdbcTemplate
JdbcTemplate jt = new JdbcTemplate();
//为JdbcTemplate指定DataSource
jt.setDataSource(ds);

jt.execute("update mytable set name=? where name = 'xx'", new PreparedStatementCallback()
{
public Object doInPreparedStatement(PreparedStatement pstmt)throws SQLException
{
pstmt.setString(1,"-----");
pstmt.execute();
return null;
}
}
);
}
}
#7、连接数据库的辅助类
》DataSourceUtils工具类：通过DataSource获取连接,支持线程的绑定，如用于DataSourceTransactionManager。含有很多静态方法，主要有2个：
》static Connection getConnection(DataSource dataSource)
》static voic releaseConnection(Connection con,DataSource dataSource)
通常情况下建议使用spring的IOC容器管理DataSource。
/*
*示例
*/
public class BeanTest
{
public static void main(String[] args)throws Exception
{
//创建DataSource实例
ComboPooledDataSource ds = new ComboPooledDataSource();
//加载DataSource驱动
ds.setDriverClass("com.mysql.jdbc.Driver");
//设置连接数据库的url
ds.setJdbcUrl("jdbc:mysql://localhost:3306/j2ee");
//设置数据库用户名
ds.setUser("root");
//设置数据库密码
ds.setPassword("32147");
//设置池的最大连接数
ds.setMaxPoolSize(40);
//设置池的最小连接数
ds.setMinPoolSize(2);
ds.setMaxStatements(180);
//以下才是真正使用Spring的JDBC的事务方法
Connection conn = DataSourceUtils.getConnection(ds);
java.sql.Statement stmt = conn.createStatement();
stmt.execute("insert into mytable values('wddda2')");
}
}
通过DataSourceUtils获得连接比使用DataSource的getConnection直接获取的更智能，前者的连接在必要时自动关闭。在Spring的事务管理结合时，更具有独特的能力。
》SmartDataSource接口：继承DataSource接口，提供一些额外的功能，在恰当的时候关闭连接，更加智能化。
》SingelConnectionDataSource类：采用单态模式实现SmartDataSource接口
/*
*示例
*/
public class BeanTest
{
public static void main(String[] args)throws Exception
{
SingleConnectionDataSource ds = new SingleConnectionDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setPassword("32147");
ds.setUrl("jdbc:mysql://localhost:3306/j2ee");
ds.setUsername("root");
//ds.setSuppressClose(true);
Connection conn = DataSourceUtils.getConnection(ds);
System.out.println(conn);
//java.sql.Statement stmt = conn.createStatement();
//stmt.execute("insert into mytable values('w52x')");
//conn.close();
//conn.createStatement();
System.out.println(DataSourceUtils.getConnection(ds));
System.out.println(DataSourceUtils.getConnection(ds));

}
}
》DriverManagerDataSource类继承Spring的抽象类AbstractDataSource，实际上，DriverManagerDataSource常被作为SmartDataSource的一个实现，而不是作为JDBC2.0规范的DataSource实现。因为DriverManagerDataSource并不具备连接池的能力。
该类主要用于测试，它可以脱离j2ee容器独立运行，可以作为不同ApplicationContext的数据源bean，也可以和简易的JNDI环境一起工作。
/*
*示例
*/
public class TransactionTest
{
public static void main(String[] args)
{
final ApplicationContext ctx = new FileSystemXmlApplicationContext("bean.xml");
System.out.println("============");

PlatformTransactionManager transactionManager = (PlatformTransactionManager)ctx.getBean("transactionManager");
TransactionTemplate tt = new TransactionTemplate(transactionManager);

tt.execute(new TransactionCallbackWithoutResult()
{
protected void doInTransactionWithoutResult(TransactionStatus ts)
{
try
{
DataSource ds = (DataSource)ctx.getBean("dataSource");
Connection conn = DataSourceUtils.getConnection(ds);
java.sql.Statement stmt = conn.createStatement();
stmt.execute("insert into mytable values('china')");
stmt.execute("insert into mytable values('wawa2')");//让此句引发异常就可看出事务的管理
}
catch (SQLException sqle)
{
//这种方式无须显式提交,但需要在出现异常的时候显式回滚
ts.setRollbackOnly();
sqle.printStackTrace();
}
}
});
}
}