衔接 初学JDBC
http://blog.csdn.net/u010542146/article/details/50782684
内容概要:
一、JDBC常用的API深入详解及存储过程的调用
二、JDBC的事务管理
三、数据库连接池 (1.dbcp / 2.c3p0)
四、JDBC的替代产品(1.hibernate / 2.mybiatis)
存储过程
1.创建存储过程(无参) 和 使用
创建
create PROCEDURE sp_select_man_nofilter()
BEGIN
select * from man ;
END
使用
CALL sp_select_man_nofilter()
2.创建存储过程(有参) 和 使用
BEGIN
IF sp_name is null or sp_name = '' THEN
select * from man ;
ELSE
IF LENGTH(sp_name) = 2 AND SUBSTRING(sp_name,1,1) = 2 then
SELECT * from man where age = sp_name ;
ELSE
SELECT * from man where name = sp_name ;
end IF ;
end IF ;
END
使用
call sp_select_man_filter("KiKi")
3.创建带输出的存储过程
CREATE PROCEDURE sp_select_man_count(out count int(10)
BEGIN
SELECT count(*) INTO count FROM man ;
END
ProcedureDao 封装了使用存储过程访问数据库的方法
package com.example.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import com.example.db.DButil;
import com.example.model.Man;
import com.mysql.jdbc.CallableStatement;
public class ProcedureDao {
//查
public List<Man> select_man_nofilter() {
List<Man> list = new ArrayList<Man>();
Man man = null;
//1.获得连接
Connection conn = DButil.getConnection();
String sql = "call sp_select_man_nofilter()";
try {
//2.获得CallableStatement
CallableStatement cs = (CallableStatement) conn.prepareCall(sql);
//3.执行存储过程
cs.execute();
//4.处理返回结果;结果集,出参
ResultSet rs = cs.getResultSet();
while (rs.next()) {
man = new Man();
man.setName(rs.getString("name"));
man.setAge(rs.getInt("age"));
list.add(man);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//带输入参数的查
public List<Man> select_man_filter(String params) {
List<Man> list = new ArrayList<Man>();
Man man = null;
Connection conn = DButil.getConnection();
String sql = "call sp_select_man_filter(?)";
try {
CallableStatement cs = (CallableStatement) conn.prepareCall(sql);
cs.setString(1, params);
cs.execute();
ResultSet rs = cs.getResultSet();
while (rs.next()) {
man = new Man();
man.setName(rs.getString("name"));
man.setAge(rs.getInt("age"));
list.add(man);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
//带输出参数的查
public int select_man_count() {
List<Man> list = new ArrayList<Man>();
int count = -1;
Man man = null;
Connection conn = DButil.getConnection();
String sql = "call sp_select_man_count(?)";
try {
CallableStatement cs = (CallableStatement) conn.prepareCall(sql);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
count = cs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
}
事务
事务的概念:事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作。
这些操作为一个整体一起向系统提交,要么都执行,要么都不执行。
事务的特点:
1.原子性(Atomicity) : 事务是一个完整的操作
2.一致性(Consistency): 当事务完成时,数据必须处于一致状态。
3.隔离性(Isolation) : 对数据进行修改的所有并发事务都是彼此隔离的。
4.永久性(Durability): 事务完成后,它对数据库的修改是永久的。
JDBC对事务管理的支持
1、我们通过提交commit()或是回退rollback()来管理事务的操作
2、事务操作时默认自动提交的
3、可以同通过调用setAutoCommit(false)来禁止自动提交
买东西例子
account_info 表
trans_info 表
架构
TransAction
package com.example.action;
import com.example.dao.AccountInfoDao;
import com.example.model.AccountInfo;
import com.example.service.TransService;
public class TransAction {
public static void main(String[] args) {
String result = trans();
System.out.println(result);
}
public static String trans() {
AccountInfoDao dao = new AccountInfoDao();
AccountInfo from = null;
AccountInfo to = null;
from = dao.get(1);
to = dao.get(2);
double amount = 20;
TransService transService = new TransService();
String result = "transFail";
result = transService.transAction(from, to, amount);
return result;
}
}
AccountInfoDao
package com.example.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.example.db.DButil;
import com.example.model.AccountInfo;
import com.mysql.jdbc.PreparedStatement;
public class AccountInfoDao {
public void updateAmount(int id, double d) {
Connection conn = DButil.getConnection();
String sql = "UPDATE account_info set amount = ? where id = ?";
try {
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setDouble(1, d);
ptmt.setInt(2, id);
ptmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public AccountInfo get(int id) {
Connection conn = DButil.getConnection();
String sql = "select * from account_info where id = ?";
AccountInfo info = null;
try {
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setInt(1, id);
ResultSet rs = ptmt.executeQuery();
if (rs.next()) {
info = new AccountInfo();
info.setId(rs.getInt("id"));
info.setAccount(rs.getString("account"));
info.setAmount(rs.getDouble("amount"));
info.setCreateAt(rs.getDate("create_at"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return info;
}
}
TransInfoDao
package com.example.dao;
import java.sql.Connection;
import java.sql.SQLException;
import com.example.db.DButil;
import com.example.model.TransInfo;
import com.mysql.jdbc.PreparedStatement;
public class TransInfoDao {
public void addTrans(TransInfo transInfo) {
Connection conn = DButil.getConnection();
String sql = "Insert into trans_info (source_id,source_account,destination_id,destination_account,amount) value (?,?,?,?,?)";
try {
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setInt(1, transInfo.getSourceId());
ptmt.setString(2, transInfo.getSourceAccount());
ptmt.setInt(3, transInfo.getDestinationId());
ptmt.setString(4, transInfo.getDestinationAccount());
ptmt.setDouble(5, transInfo.getAmount());
ptmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
TransService(核心)
package com.example.service;
import java.sql.Connection;
import java.sql.SQLException;
import com.example.dao.AccountInfoDao;
import com.example.dao.TransInfoDao;
import com.example.db.DButil;
import com.example.model.AccountInfo;
import com.example.model.TransInfo;
public class TransService {
//无事务,导致买家减钱,卖家没加钱
public String trans(AccountInfo from, AccountInfo to, double amount) {
AccountInfoDao accountInfoDao = new AccountInfoDao();
//买东西,减钱
accountInfoDao.updateAmount(from.getId(),from.getAmount()-amount);
//模拟报错,导致只减钱没加钱
String a = null;
a.split(",");
//卖东西,加钱
accountInfoDao.updateAmount(to.getId(),to.getAmount()+amount);
//记录单
TransInfo transInfo = new TransInfo();
transInfo.setSourceId(from.getId());
transInfo.setSourceAccount(from.getAccount());
transInfo.setDestinationId(to.getId());
transInfo.setDestinationAccount(to.getAccount());
transInfo.setAmount(amount);
TransInfoDao transInfoDao = new TransInfoDao();
transInfoDao.addTrans(transInfo);
return "success";
}
//有事务,保证有加减,一致性
public String transAction(AccountInfo from, AccountInfo to, double amount) {
Connection conn = DButil.getConnection();
try {
//禁止自动提交
conn.setAutoCommit(false);
AccountInfoDao accountInfoDao = new AccountInfoDao();
//买东西,减钱
accountInfoDao.updateAmount(from.getId(),from.getAmount()-amount);
//模拟报错,被catch然后rollback()无影响
// String a = null;
// a.split(",");
//卖东西,加钱
accountInfoDao.updateAmount(to.getId(),to.getAmount()+amount);
//记录单
TransInfo transInfo = new TransInfo();
transInfo.setSourceId(from.getId());
transInfo.setSourceAccount(from.getAccount());
transInfo.setDestinationId(to.getId());
transInfo.setDestinationAccount(to.getAccount());
transInfo.setAmount(amount);
TransInfoDao transInfoDao = new TransInfoDao();
transInfoDao.addTrans(transInfo);
//手动提交
conn.commit();
return "success";
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
return "fail";
}
}
}
连接池
连接池的作用是当访问量巨大时,会让新的用户等待,当连接可用时,再把这个连接给等待的用户。这样可以减小服务器的压力。
dbcp连接池配置
common-dbcp.jar
common-pool.jar
common-logging.jar
mysql-connector-java.jar
配置文件 dbcp.properties -> 放置在src目录下
dbcp.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.191.1:3306/jdbctest?useUnicode=true&characterEncoding=utf-8
username=root
password=root
maxActive=30
maxIdle=10
maxWait=1000
initialSize=5
minIdle=1
DBCPUtil
package com.example.db;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.omg.CORBA_2_3.portable.InputStream;
public class DBCPUtil {
//数据源
private static DataSource DS;
private static final String configFile = "/dbcp.properties";
public DBCPUtil() {
initDbcp();
}
private void initDbcp() {
Properties pops = new Properties();
try {
pops.load(Object.class.getResourceAsStream(configFile));
DS = (DataSource) BasicDataSourceFactory.createDataSource(pops);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection() {
Connection conn = null;
if (DS != null) {
try {
conn = (Connection) DS.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
return conn;
}
}
ManDao
package com.example.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.example.db.DBCPUtil;
import com.example.model.Man;
public class ManDao {
//增
public void AddMan(Man man) {
//获得连接
DBCPUtil dbcpUtil = new DBCPUtil();
Connection conn = dbcpUtil.getConnection();
try {
String sql = "INSERT INTO man (name,age) values (?,?)";
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1, man.getName());
ptmt.setInt(2, man.getAge());
ptmt.execute();
//手动提交
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
使用(和直连一样)
ManDao dao = new ManDao();
Man man = new Man();
man.setName("Ni");
man.setAge(12);
dao.AddMan(man);
c3p0连接池配置
c3p0是一个开源的JDBC连接池,它实现了数据源和JNDI绑定,支持JDBC3和JDBC2的标准拓展
1.导入相关jar
c3p0-0.9.2-pre4.jar
mchange-commons-java-0.2.2.jar
2.在项目根目录下增加配置文件
c3p0.properties -> 放置src目录下
c3p0.properties
c3p0.driverClass=com.mysql.jdbc.Driver
c3p0.jdbcUrl=jdbc:mysql://192.168.191.1:3306/jdbctest?useUnicode=true&characterEncoding=utf-8
c3p0.user=root
c3po.password=root
C3P0Util
package com.example.db;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Util {
private static ComboPooledDataSource ds = new ComboPooledDataSource();
public static Connection getConnection() {
try {
return (Connection) ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
使用
得到连接的方式
Connection conn = C3P0Util.getConnection();
dbcp 和 c3p0比较
相同点:
不同点
转载于:http://www.imooc.com/learn/421
javaee中jar放置