09.连接池
1、连接池
为什么要使用连接池?
以往我们每次与数据库交互都回去创建链接,用完之后需要销毁链接,会占用大量的系统资源,效率低下,而且任务量巨大的时候会导致数据库链接超出最大上限,程序直接崩溃。
什么是连接池?
一种高效的创建和管理数据库链接的技术。
2、连接池分类:
c3p0:主要的学习目标
Druid
DBCP
3、c3p0的使用
1、导入Jar包
mysql-connector-java-5.1.49.jar:数据库链接驱动
c3p0-version.jar:核心jar包
mchange-commons-java-version:以来jar包
2、创建连接池核心对象(数据源)
ComboPooledDataSource dataSource = new ComboPooledDataSource();
案例:
案例1:
package com.zb.test;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBUtil {
public static Connection getConn() {
//创建数据源
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//设置链接的基本属性
//设置链接的驱动类
try {
dataSource.setDriverClass("com.mysql.jdbc.Driver");
} catch (PropertyVetoException e) {
System.out.println("加载驱动类失败");
}
//链接地址
dataSource.setJdbcUrl("jdbc:mysql:///user_db?characterEncoding=UTF-8");
//链接账号
dataSource.setUser("root");
//链接密码
dataSource.setPassword("123456");
//设置额外属性
//设置链接初始化的时候管理的链接数量
dataSource.setInitialPoolSize(10);
//设置连接最大的闲置时间
dataSource.setMaxIdleTime(1);
//设置连接池保有的连接最大数量
dataSource.setMaxPoolSize(50);
//设置链接池保有的最小链接数量
dataSource.setMinPoolSize(10);
//单个链接最大的Statement对象持有数量
dataSource.setMaxStatements(200);
//获取链接
Connection conn = null;
try {
conn = dataSource.getConnection();
} catch (SQLException e) {
System.out.println("获取链接对象失败");
}
return conn;
}
}
package com.zb.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestMain {
public static void main(String[] args) throws Exception {
//加入链接jar包
//加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//创建链接对象
Connection conn = DriverManager.getConnection("jdbc:mysql:///user_db?characterEncoding=UTF-8","root","123456");
//SQL语句执行对象
PreparedStatement pst = conn.prepareStatement(" SELECT * FROM user_info ");
//如果有参数,则需要先设置参数
//执行增删改executeUpdate
//执行查询executeQuery
ResultSet res = pst.executeQuery();
//解析结果ResultSet
while(res.next()) {
//解析
}
//关闭连接释放资源
res.close();
pst.close();
//conn.close();
}
}
package com.zb.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestMain01 {
public static void main(String[] args) throws Exception {
Connection conn = DBUtil.getConn();
//创建SQl语句执行对象
PreparedStatement pst = conn.prepareStatement(" INSERT INTO user_info VALUES(DEFAULT,?,?) ");
//设置SQL语句参数
pst.setString(1, "xiaoshuaibi@qq.com");
pst.setString(2, "123456");
//执行增删改
int rc = pst.executeUpdate();
System.out.println(rc>0?"添加成功":"添加失败");
//只需要关闭链接,这里的关闭不是真的关闭连接了,而是让连接回到连接池中以供其他程序使用
conn.close();
}
}
案例2:
验证码:
package com.zuxia.Text;
import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics;
import java.awt.Image;
import java.awt.image.BufferedImage;
import java.util.Random;
public class MileDemo {
//定义数组,将值存入
private static String[] strs= {"a","b","c","d","e","f","g","h",
"i","j","k","m","n","p","q","r","s","t","u","v",
"w","x","y","z","2","3","4","5","6","7","8","9"
};
public static void main(String[] args) {
//设置图片的高度和宽度
int h=60;int w=200;
//创建图片对象RGB,red,green,blue红绿蓝三原色
BufferedImage image = new BufferedImage(w, h, BufferedImage.TYPE_INT_ARGB);
//获取画笔对象
Graphics graphics = image.getGraphics();
//给画笔设置颜色
graphics.setColor(Color.green);
//填充矩形
graphics.fillRect(0, 0, w, h);
//随机数获取数据
Random r = new Random();
//设置画笔颜色
graphics.setColor(Color.red);
//设置字体
graphics.setFont(new Font("微软雅黑", Font.BOLD, 30));
//字符串在图片上的坐标
int x = 30;int y = 30;
//for循环输出验证码
for(int i=0;i<4;i++){
}
}
}
package com.zuxia.Text;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class MyMain {
public static void main(String[] args) throws SQLException {
//创建数据源
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//设置链接驱动
//dataSource.setDriverClass("com.mysql.jdbc.Driver");
/*
* //链接地址
* dataSource.setJdbcUrl("jdbc:mysq.l:///user_DB?characterEncoding=UTF-8"); //链接名
* dataSource.setUser("root"); //连接密码 dataSource.setPassword("123456");
*/
//创建连接对象
Connection conn = dataSource.getConnection();
//创建sql语句执行对象
PreparedStatement pre = conn.prepareStatement("select * from stu_info;");
ResultSet rs = pre.executeQuery();
while(rs.next()) {System.out.print(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
}
//执行sql语句
conn.close();
}
}
package com.zuxia.Text;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TextMain {
public static void main(String[] args) throws Exception {
//创建数据源
ComboPooledDataSource dataSource = new ComboPooledDataSource();
//设置链接驱动
dataSource.setDriverClass("com.mysql.jdbc.Driver");
//链接地址
dataSource.setJdbcUrl("jdbc:mysql:///user_DB?characterEncoding=UTF-8");
//链接名
dataSource.setUser("root");
//连接密码
dataSource.setPassword("123456");
//创建连接对象
Connection conn = dataSource.getConnection();
//创建sql语句执行对象
PreparedStatement pre = conn.prepareStatement("insert into user_info values(null,?,?)");
pre.setString(1, "张三");
pre.setString(2, "123456");
//执行sql语句
int row = pre.executeUpdate();
System.out.println(row>0?"添加成功":"添加失败");
}
}
package com.zuxia.Text;
import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.util.Random;
import javax.imageio.ImageIO;
public class YaZhengMa {
//定义数组,将值存入
private static String[] strs= {"a","b","c","d","e","f","g","h",
"i","j","k","m","n","p","q","r","s","t","u","v",
"w","x","y","z","2","3","4","5","6","7","8","9"
};
public static void main(String[] args) throws IOException {
//定义图片的宽度
int w=200;int h=60;
//创建图片对象RGB,red,green,blue红绿蓝三原色
BufferedImage image=new BufferedImage(w,h,BufferedImage.TYPE_INT_BGR);
//获取画笔对象
Graphics g=image.getGraphics();
//给画笔设置颜色
g.setColor(Color.green);
//填充矩形
g.fillRect(0,0,w,h);
//随机数获取数据
Random random=new Random();
//设置画笔颜色
g.setColor(Color.red);
g.setFont(new Font("楷体",Font.BOLD,30));
//字符串在图片上的坐标
int x=30;int y=30;
//编写for循环
for(int i=0;i<4;i++) {
//随机数获取所产生的元素的下标
int index=random.nextInt(strs.length);
//随机数数组中获取数据
String str=strs[index];
//随机生成角度-45到45
int jd=random.nextInt(91)-45;
//弧度=角度*PI*180;
double hd=jd*Math.PI/180;
//调用方法,设置字体的旋转
//g.rotate(hd, x, y);
//随机数获取一个数,把该数据画在矩形上
g.drawString(str,x,y);
//恢复坐标
//g.rotate(-hd, x, y);
//每次累加x的坐标
x+=25;
}
//设置画笔颜色
g.setColor(Color.blue);
//线条
int a,b,c,d;
for(int i=0;i<10;i++) {
a=random.nextInt(50);
b=random.nextInt(h);
c=random.nextInt(30)+120;
d=random.nextInt(h);
g.drawLine(a, b, c, d);
}
//创建文件流对象
File f=new File("c:\\img\\aaa.jpg");
if(!f.exists())
{
f.mkdirs();
}
//把内存中image对象保存到本地的磁盘上
ImageIO.write(image,"jpg",new File("c:\\img\\aaa.jpg"));
}
}
案例3:
验证码发送:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="phone" method="post">
<input type="text" name="phoneNumber"/><br/>
<input type="submit" value="发送"/>
</form>
</body>
</html>
导包
package com.zuxia.ce.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.aliyuncs.CommonRequest;
import com.aliyuncs.CommonResponse;
import com.aliyuncs.DefaultAcsClient;
import com.aliyuncs.IAcsClient;
import com.aliyuncs.exceptions.ClientException;
import com.aliyuncs.exceptions.ServerException;
import com.aliyuncs.http.MethodType;
import com.aliyuncs.profile.DefaultProfile;
@WebServlet("/phone")
public class PhoneServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String phoneNumber = req.getParameter("phoneNumber");
//ctrl=shift+o
DefaultProfile profile = DefaultProfile.getProfile("cn-hangzhou", req.getServletContext().getInitParameter("accessKeyId"), req.getServletContext().getInitParameter("accessSecret"));
IAcsClient client = new DefaultAcsClient(profile);
CommonRequest request = new CommonRequest();
request.setSysMethod(MethodType.POST);
request.setSysDomain("dysmsapi.aliyuncs.com");
request.setSysVersion("2017-05-25");
request.setSysAction("SendSms");
request.putQueryParameter("RegionId", "cn-hangzhou");
request.putQueryParameter("PhoneNumbers", phoneNumber);
request.putQueryParameter("SignName", "YK商城");
request.putQueryParameter("TemplateCode", "SMS_205434322");
request.putQueryParameter("TemplateParam", "{\"code\":\"66655\"}");
try {
CommonResponse response = client.getCommonResponse(request);
System.out.println(response.getData());
} catch (ServerException e) {
e.printStackTrace();
} catch (ClientException e) {
e.printStackTrace();
}
}
}