难难难,道德玄,不对知音不可谈,
对了知音谈几句,不对知音枉废舌尖!
一、JDBC详解之存储过程的调用
1、JDBC存储过程之调用无参存储过程
ProduceDao.java类
package com.imooc.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.imooc.db.DBUtil;
import com.mysql.jdbc.CallableStatement;
public class ProduceDao {
public static void select_nofilter() throws SQLException {
//1、获得连接
Connection conn = DBUtil.getConnection();
//2、获得callablestatement
CallableStatement cs = (CallableStatement) conn.prepareCall("call sp_select_nofilter()");
//3、执行存储过程
cs.execute();
//4、处理返回的结果:结果集,出参
ResultSet rs = cs.getResultSet();
while (rs.next()) {
System.out.println(rs.getString("user_name")+rs.getString("email")+rs.getString("mobile"));
}
}
}
JDBCTest.java测试类
package com.imooc.test;
import java.sql.SQLException;
import com.imooc.dao.ProduceDao;
public class JDBCTest {
public static void main(String[] args) throws SQLException {
ProduceDao dao = new ProduceDao();
dao.select_nofilter();
}
}
结果输出:
2、JDBC存储过程之调用带输入参数的存储过程
注意设置参数。
SQL语句
CREATE DEFINER = `root`@`localhost` PROCEDURE `imooc`.`sp_select_filter`(IN sp_name varchar(20))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
IF sp_name IS NULL OR sp_name = '' THEN
SELECT * FROM imooc_goddess;
ELSE
IF LENGTH(sp_name) = 11 AND SUBSTRING(sp_name,1,1) =1 THEN
SELECT * FROM imooc_goddess WHERE mobile = sp_name;
ELSE
SELECT * FROM imooc_goddess WHERE user_name LIKE CONCAT('%',sp_name,'%');
END if;
END if;
END
在ProduceDao.java|类中新增select_filter方法
public static List<Goddess> select_filter(String sp_name) throws Exception {
List<Goddess> result = new ArrayList<Goddess>();
// 1、获得连接
Connection conn = DBUtil.getConnection();
// 2、获得callablestatement
CallableStatement cs = (CallableStatement) conn.prepareCall("call sp_select_filter(?)");
cs.setString(1, sp_name);
//3、执行存储过程
cs.execute();
//4、处理返回的结果:结果集,出参
ResultSet rs = cs.getResultSet();
Goddess g = null;
while (rs.next()) {
g = new Goddess();
g.setId(rs.getInt("id"));
g.setUser_name(rs.getString("user_name"));
g.setAge(rs.getInt("age"));
g.setMobile(rs.getString("mobile"));
result.add(g);
}
return result;
}
JDBCTest.java类进行测试
package com.imooc.test;
import java.sql.SQLException;
import java.util.List;
import com.imooc.dao.ProduceDao;
import com.imooc.model.Goddess;
public class JDBCTest {
public static void main(String[] args) throws SQLException {
// dao.select_nofilter();
String sp_name = "刘";
List<Goddess> result = null;
try {
result = select_filter(sp_name);
showResult(result);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static List<Goddess> select_filter(String sp_name) throws Exception{
ProduceDao dao = new ProduceDao();
return dao.select_filter(sp_name);
}
public static void showResult(List<Goddess> result) {
for (int i = 0; i < result.size(); i++) {
System.out.println(
result.get(i).getId() + ":" + result.get(i).getUser_name() + ":" + result.get(i).getMobile());
}
}
}
3、JDBC存储过程之调用带输出参数的存储过程
SQL语句
mysql> CREATE DEFINER = `root`@`localhost` PROCEDURE `imooc`.`sp_select_count`(OUT count INT(10))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT count(*) INTO count FROM imooc_goddess;
END;
Query OK, 0 rows affected (0.03 秒)
mysql>
在ProduceDao.java|类中新增select_count()方法
public static Integer select_count() throws Exception{
Integer count =0;
// 1、获得连接
Connection conn = DBUtil.getConnection();
// 2、获得callablestatement
CallableStatement cs = (CallableStatement) conn.prepareCall("call sp_select_count(?)");
cs.registerOutParameter(1, Types.INTEGER);
//3、执行存储过程
cs.execute();
//4、处理返回的结果:结果集,出参
count = cs.getInt(1);
return count;
}
JDBCTest.java类进行测试
package com.imooc.test;
import java.sql.SQLException;
import java.util.List;
import com.imooc.dao.ProduceDao;
import com.imooc.model.Goddess;
public class JDBCTest {
public static void main(String[] args) throws SQLException {
// dao.select_nofilter();
String sp_name = "刘";
List<Goddess> result = null;
Integer count =0;
try {
//带参数的存储过程
// result = select_filter(sp_name);
// showResult(result);
count = select_count();
System.out.println(count);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static List<Goddess> select_filter(String sp_name) throws Exception{
ProduceDao dao = new ProduceDao();
return dao.select_filter(sp_name);
}
public static Integer select_count() throws Exception{
ProduceDao dao = new ProduceDao();
return dao.select_count();
}
public static void showResult(List<Goddess> result) {
for (int i = 0; i < result.size(); i++) {
System.out.println(
result.get(i).getId() + ":" + result.get(i).getUser_name() + ":" + result.get(i).getMobile());
}
}
}
二、JDBC详解之事务处理
1、JDBC事物的概念和特点
2、JDBC实现事务管理
3、通过代码实现事务管理
#1.创建imooc_db数据库
CREATE DATABASE imooc_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE imooc_db;//使用数据库
#2.创建account_info账号信息表
CREATE TABLE account_info
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,//账户id
account VARCHAR(20) NOT NULL,//账户
amount DOUBLE(18,2) NOT NULL DEFAULT 0,//合计
create_at DATETIME NOT NULL DEFAULT NOW()//创建时间
);
#3.创建trans_info交易信息表
CREATE TABLE trans_info
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,//id
source_id INT NOT NULL,//来源id
source_account VARCHAR(20) NOT NULL,//来源账户
destination_id INT NOT NULL,//目标id
destination_account varchar(20) NOT NULL,//目标账户
amount DOUBLE(18,2) NOT NULL DEFAULT 0,//合计
create_at DATETIME NOT NULL DEFAULT NOW()//创建时间
);
#4.插入数据
INSERT INTO account_info (account,amount) values ('market',0);
INSERT INTO account_info (account,amount) values ('a',250);
TransInfo.java类
package com.imooc.model;
import java.util.Date;
public class TransInfo {
private Integer id;
private Integer sourceId;
private String sourceAccount;
private Integer destinationId;
private String destinationAccount;
private Double amount;
private Date createAt;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getSourceId() {
return sourceId;
}
public void setSourceId(Integer sourceId) {
this.sourceId = sourceId;
}
public String getSourceAccount() {
return sourceAccount;
}
public void setSourceAccount(String sourceAccount) {
this.sourceAccount = sourceAccount;
}
public Integer getDestinationId() {
return destinationId;
}
public void setDestinationId(Integer destinationId) {
this.destinationId = destinationId;
}
public String getDestinationAccount() {
return destinationAccount;
}
public void setDestinationAccount(String destinationAccount) {
this.destinationAccount = destinationAccount;
}
public Double getAmount() {
return amount;
}
public void setAmount(Double amount) {
this.amount = amount;
}
public Date getCreateAt() {
return createAt;
}
public void setCreateAt(Date createAt) {
this.createAt = createAt;
}
}
AccountDao.java类
package com.imooc.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.imooc.db.DBUtil;
import com.imooc.model.Account;
import com.mysql.jdbc.PreparedStatement;
public class AccountDao {
// 增加
public void insert(Account account) throws Exception {
Connection conn = DBUtil.getConnection();
String sql = "" + "INSERT INTO account_info (account,amount) VALUES (?,?);";
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1, account.getAccount());
ptmt.setDouble(2, account.getAmount());
ptmt.execute();
}
// 更新
public void update(Account account) throws Exception {
Connection conn = DBUtil.getConnection();
String sql = "" + "UPDATE account_info " + " SET account=?,amount=?" + " WHERE id=? ";
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1, account.getAccount());
ptmt.setDouble(2, account.getAmount());
ptmt.setInt(3, account.getId());
ptmt.execute();
}
// 删除
public void delete(Account account) throws Exception {
Connection conn = DBUtil.getConnection();
String sql = "" + " DELETE FROM account_info " + " WHERE id=? ";
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setInt(1, account.getId());
ptmt.execute();
}
// 查找
public List<Account> query(Account account) throws SQLException {
List<Account> result = new ArrayList<Account>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM account_info ");
sb.append("WHERE account LIKE ? ");
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sb.toString());
ptmt.setString(1, "%" + account.getAccount() + "%");
ResultSet rs = ptmt.executeQuery();
Account a = null;
while (rs.next()) {
a = new Account();
a.setAccount(rs.getString("account"));
a.setAmount(rs.getDouble("amount"));
a.setCreateAt(rs.getDate("create_at"));
a.setId(rs.getInt("id"));
result.add(a);
}
return result;
}
public Account query(int id) throws SQLException {
List<Account> result = new ArrayList<Account>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM account_info");
sb.append(" WHERE id LIKE ?");
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sb.toString());
ptmt.setInt(1, id);
ResultSet rs = ptmt.executeQuery();
Account a = null;
while (rs.next()) {
a = new Account();
a.setAccount(rs.getString("account"));
a.setAmount(rs.getDouble("amount"));
a.setCreateAt(rs.getDate("create_at"));
a.setId(rs.getInt("id"));
// list.add(a);
}
return a;
}
public Account queryByDbcp(int id) throws SQLException {
DBUtil db = new DBUtil();
List<Account> list = new ArrayList<Account>();
Connection conn = DBUtil.getConnection();
StringBuilder sb = new StringBuilder();
sb.append("select * from account_info");
sb.append(" where id like ?");
PreparedStatement st = (PreparedStatement) conn.prepareStatement(sb.toString());
st.setInt(1, id);
ResultSet rs = st.executeQuery();
Account a = null;
while (rs.next()) {
a = new Account();
a.setAccount(rs.getString("account"));
a.setAmount(rs.getDouble("amount"));
a.setCreateAt(rs.getDate("create_at"));
a.setId(rs.getInt("id"));
// list.add(a);
}
return a;
}
}
TransInfoDao.java类
package com.imooc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.imooc.db.DBUtil;
import com.imooc.model.TransInfo;
public class TransInfoDao {
public void insert(TransInfo transInfo) throws SQLException {
Connection conn = DBUtil.getConnection();
PreparedStatement st = conn.prepareStatement(
"insert into trans_info(source_id,source_account,destination_id,destination_account,amount) values(?,?,?,?,?)");
st.setInt(1, transInfo.getSourceId());
st.setString(2, transInfo.getSourceAccount());
st.setInt(3, transInfo.getDestinationId());
st.setString(4, transInfo.getDestinationAccount());
st.setDouble(5, transInfo.getAmount());
st.execute();
}
public void update(TransInfo transInfo) throws SQLException {
Connection conn = DBUtil.getConnection();
PreparedStatement st = conn.prepareStatement(
"update trans_info set source_id =?,source_account =?,destination_id =?,destination_account=?,amount=? where id =?");
st.setInt(1, transInfo.getSourceId());
st.setString(2, transInfo.getSourceAccount());
st.setInt(3, transInfo.getDestinationId());
st.setString(4, transInfo.getDestinationAccount());
st.setDouble(5, transInfo.getAmount());
st.setInt(6, transInfo.getId());
st.execute();
}
public void delete(TransInfo transInfo) throws SQLException {
Connection conn = DBUtil.getConnection();
PreparedStatement st = conn.prepareStatement("delete from trans_info where id=?");
st.setInt(1, transInfo.getId());
st.execute();
}
public List<TransInfo> query(TransInfo transInfo) throws SQLException{
List<TransInfo> list =new ArrayList<TransInfo>();
Connection conn =DBUtil.getConnection();
PreparedStatement st =conn.prepareStatement("select * from trans_info where id =?");
st.setInt(1, transInfo.getId());
ResultSet rs =st.getResultSet();
TransInfo t =null;
while(rs.next()){
t.setId(rs.getInt("id"));
t.setSourceId(rs.getInt("source_id"));
t.setSourceAccount(rs.getString("source_account"));
t.setDestinationId(rs.getInt("destination_id"));
t.setDestinationAccount(rs.getString("desitination_account"));
t.setAmount(rs.getDouble("amount"));
list.add(t);
}
return list;
}
}
service层
TransService.java类
package com.imooc.service;
import java.sql.Connection;
import java.sql.SQLException;
import com.imooc.dao.AccountDao;
import com.imooc.dao.TransInfoDao;
import com.imooc.db.DBUtil;
import com.imooc.model.Account;
import com.imooc.model.TransInfo;
public class TransService {
//无事物管理的
public String trans(Account from,Account to,Double amount) throws Exception {
AccountDao accountDao = new AccountDao();
TransInfoDao transInfoDao = new TransInfoDao();
from.setAmount(from.getAmount()-amount);
accountDao.update(from);
// String a= null;
// a.split("1");
to.setAmount(to.getAmount()+amount);
accountDao.update(to);
TransInfo info =new TransInfo();
info.setSourceAccount(from.getAccount());
info.setSourceId(from.getId());
info.setDestinationAccount(to.getAccount());
info.setDestinationId(to.getId());
info.setAmount(amount);
transInfoDao.insert(info);
return "success";
}
//有事物管理的
public String transaction(Account from,Account to,Double amount) throws Exception {
Connection conn =DBUtil.getConnection();
conn.setAutoCommit(false);
try {
AccountDao accountDao=new AccountDao();
TransInfoDao transInfoDao = new TransInfoDao();
from.setAmount(from.getAmount()-amount);
accountDao.update(from);
// String a =null;
// a.split("1");
to.setAmount(to.getAmount()+amount);
accountDao.update(to);
TransInfo info = new TransInfo();
info.setSourceAccount(from.getAccount());
info.setSourceId(from.getId());
info.setDestinationAccount(to.getAccount());
info.setDestinationId(to.getId());
info.setAmount(amount);
TransInfoDao.insert(info);
conn.commit();
return "success";
} catch (Exception e) {
// TODO: handle exception
conn.rollback();
e.printStackTrace();
return "fail";
}
}
}
三、JDBC升级之连接池
1、数据库连接是一种重要的资源
2、频繁的连接数据库会增加数据库的压力
3、未解决上述问题出现了连接池技术
配置文件属性
2、C3P0
C3P0是一个开源的JDBC连接池,它实现了数据源和JNDI绑定,支持JDBC3和JDBC2的标准拓展。