1、注册驱动
l
Class.forName
(“
com.mysql.jdbc.Driver
”);
推荐这种方式，不会对具体的驱动类产生依赖。
l
DriverManager.registerDriver
(
com.mysql.jdbc.Driver
);
会造成DriverManager中产生两个一样的驱动，并会对具体的驱动类产生依赖。
l
System.setProperty
(“
jdbc.drivers
”, “driver1:driver2”);
虽然不会对具体的驱动类产生依赖；但注册不太方便，所以很少使用。
l
驱动类型
(
四种类型
)
2、建立连接
lConnection conn = DriverManager.getConnection(url, user, password);
lurl格式： JDBC:子协议:子名称//主机名:端口/数据库名？属性名=属性值&…
lUser,password可以用“属性名=属性值”方式告诉数据库；
其他参数如：useUnicode=true&characterEncoding=GBK
3、创建执行SQL的语句(Statement)
l
Statement
Statement st = conn.createStatement();
st.executeQuery(sql);
l
PreparedStatement
String sql = “select * from table_name where col_name=?”;
PreparedStatement ps = conn.preparedStatement(sql);
ps.setString(1, “col_value”);
ps.executeQuery();
4、处理执行结果(ResultSet)
ResultSet rs = statement.executeQuery(sql);
While(rs.next()){
rs.getString(“col_name”);
rs.getInt(“col_name”);
//…
}
5、释放资源
l
释放
ResultSet
,
Statement,Connection
.
l
数据库连接（
Connection
）是非常稀有的资源，用完后必须马上释放，如果
Connection
不能及时正确的关闭将导致系统宕机。
Connection
的使用原则是尽量晚创建，尽量早的释放。
6、事务(ACID)
l
原子性
(atomicity)
：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。
l
一致性
(consistency)
：在事务处理执行前后，数据库是一致的
(
数据库数据完整性约束
)
。
l
隔离性
(
isolcation
)
：一个事务处理对另一个事务处理的影响。
l
持续性
(durability)
：事务处理的效果能够被永久保存下来 。
l
connection.setAutoCommit
(false);//
打开事务。
l
connection.commit
();//
提交事务。
l
connection.rollback
();//
回滚事务。
7、事务(SavePoint)
l
当只想撤销事务中的部分操作时可使用
SavePoint
l
SavePoint
sp =
connection.setSavepoint
();
l
connection.rollerbak
(sp);
connection.commit
();
8、事务(JTA)
l
跨越多个数据源的事务，使用
JTA
容器实现事务。
l
分成两阶段提交。
l
javax.transaction.UserTransaction
tx
= (
UserTransaction
)
ctx.lookup
(“
jndiName
");
tx.begin();
//connection1 connection2 (可能来自不同的数据库)…
tx.commit();//tx.rollback();
9、DataSource
在Spring里面配置DataSource文件：
<
bean
id
="dataSourceOracle"
class
="org.apache.commons.dbcp.BasicDataSource"
destroy-method ="close" >
< property name ="driverClassName" value ="oracle.jdbc.driver.OracleDriver" />
< property name ="url" value ="jdbc:oracle:thin:@ip:1521:ocntest" />
< property name ="username" value ="dmbeta" />
< property name ="password" value ="dmbeta" />
</ bean >
< bean id ="dataSourceMysql" class ="org.apache.commons.dbcp.BasicDataSource"
destroy-method ="close" >
< property name ="driverClassName" value ="com.mysql.jdbc.Driver" />
< property name ="url" value ="jdbc:mysql://ip:3306/stanley_test" />
< property name ="username" value ="stanley_test" />
< property name ="password" value ="1qaz@2wsx" />
</ bean >
destroy-method ="close" >
< property name ="driverClassName" value ="oracle.jdbc.driver.OracleDriver" />
< property name ="url" value ="jdbc:oracle:thin:@ip:1521:ocntest" />
< property name ="username" value ="dmbeta" />
< property name ="password" value ="dmbeta" />
</ bean >
< bean id ="dataSourceMysql" class ="org.apache.commons.dbcp.BasicDataSource"
destroy-method ="close" >
< property name ="driverClassName" value ="com.mysql.jdbc.Driver" />
< property name ="url" value ="jdbc:mysql://ip:3306/stanley_test" />
< property name ="username" value ="stanley_test" />
< property name ="password" value ="1qaz@2wsx" />
</ bean >
Java调用：
BeanFactory factory =
new ClassPathXmlApplicationContext(
"applicationContext.xml");
dataSource = (DataSource) factory.getBean( "dataSourceOracle");
dataSource = (DataSource) factory.getBean( "dataSourceOracle");
10、样例代码
static
void test()
throws SQLException, ClassNotFoundException {
// 1.注册驱动
DriverManager.registerDriver( new com.mysql.jdbc.Driver());
System.setProperty( "jdbc.drivers", "com.mysql.jdbc.Driver");
Class.forName( "com.mysql.jdbc.Driver"); // 推荐方式
// 2.建立连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "";
Connection conn = DriverManager.getConnection(url, user, password);
// 3.创建语句
Statement st = conn.createStatement();
// 4.执行语句
ResultSet rs = st.executeQuery("select * from user");
// 5.处理结果
while (rs.next()) {
System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t"
+ rs.getObject(3) + "\t" + rs.getObject(4));
}
// 6.释放资源
rs.close();
st.close();
conn.close();
}
// 1.注册驱动
DriverManager.registerDriver( new com.mysql.jdbc.Driver());
System.setProperty( "jdbc.drivers", "com.mysql.jdbc.Driver");
Class.forName( "com.mysql.jdbc.Driver"); // 推荐方式
// 2.建立连接
String url = "jdbc:mysql://localhost:3306/jdbc";
String user = "root";
String password = "";
Connection conn = DriverManager.getConnection(url, user, password);
// 3.创建语句
Statement st = conn.createStatement();
// 4.执行语句
ResultSet rs = st.executeQuery("select * from user");
// 5.处理结果
while (rs.next()) {
System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t"
+ rs.getObject(3) + "\t" + rs.getObject(4));
}
// 6.释放资源
rs.close();
st.close();
conn.close();
}
JDBC帮助类， 标准释放，标准单例模式
public
final
class JdbcUtilsSing {
private String url = "jdbc:mysql://localhost:3306/jdbc";
private String user = "root";
private String password = "";
// private static JdbcUtilsSing instance = new JdbcUtilsSing();
private static JdbcUtilsSing instance = null;
private JdbcUtilsSing() {
}
public static JdbcUtilsSing getInstance() {
if (instance == null) {
synchronized (JdbcUtilsSing.class) {
if (instance == null) {
instance = new JdbcUtilsSing();
}
}
}
return instance;
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
private String url = "jdbc:mysql://localhost:3306/jdbc";
private String user = "root";
private String password = "";
// private static JdbcUtilsSing instance = new JdbcUtilsSing();
private static JdbcUtilsSing instance = null;
private JdbcUtilsSing() {
}
public static JdbcUtilsSing getInstance() {
if (instance == null) {
synchronized (JdbcUtilsSing.class) {
if (instance == null) {
instance = new JdbcUtilsSing();
}
}
}
return instance;
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
本文转自 tianya23 51CTO博客，原文链接：http://blog.51cto.com/tianya23/270844，如需转载请自行联系原作者
