create database mydb character set utf8;
alert database mydb character set utf8;
1.自定义连接池为了不去经常创建连接和释放对象而占用大量资源
-----JdbcTool3 -----------获得connection(通过·)和释放资源------------
public class JdbcTool3 {
private static String drive;
private static String sql;
private static String username;
private static String passord;
static {
try {
ClassLoader classLoader = JdbcTool3.class.getClassLoader();
InputStream input = classLoader.getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(input);
drive = properties.getProperty("driver");
sql = properties.getProperty("url");
username = properties.getProperty("username");
passord = properties.getProperty("psaaword");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection connection() {
try {
Class.forName(drive);
} catch (Exception e) {
e.printStackTrace();
}
Connection con = null;
try {
con = DriverManager.getConnection(sql, username, passord);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void relese(Statement sta, Connection con, ResultSet res) {
if (res != null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (sta != null) {
try {
sta.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
---------有了conn之后便是创建几个放入连接池，在使用完后同样回收到连接池---
implements 是重写接口所以需要全部覆盖
public class MyDatasource implements DataSource{
private static LinkedList<Connection> pool=new LinkedList<>();
static{
Connection conn=null;
for (int i = 0; i < 5; i++) {
conn=JdbcTool3.connection();
pool.add(conn);
}
}
/**放回池中
* @param conn
*/
public static void backConnection(Connection conn) {
pool.add(conn);
}
@Override
public Connection getConnection() throws SQLException {
if (pool.size()==0) {
Connection conn=null;
for (int i = 0; i < 5; i++) {
conn=JdbcTool3.connection();
pool.add(conn);
}
}
return pool.remove(0);
}
- 修饰者设计模式：为了将.close修改成把链接放回连接池而不是释放
--------修改原本的功能重写一个类继承connection，为了修改.close方法--------
因为实例化所以同样需要重写prepareStatement方法
public class ColectionMackClose implements Connection {
private static LinkedList<Connection> pool;
private static Connection con;
public ColectionMackClose(Connection con, LinkedList<Connection> pool) {
this.pool = pool;
this.con = con;
}
@Override
public void close() throws SQLException {
pool.add(con);
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return con.prepareStatement(sql);
}
----------获得con之后用新的类ColectionMackClose 包装得到重写的con---------
public class MyDatasource1 implements DataSource{
private static LinkedList<Connection> pool=new LinkedList<>();
static{
Connection conn=null;
for (int i = 0; i < 5; i++) {
conn=JdbcTool3.connection();
ColectionMackClose myCollection = new ColectionMackClose(conn, pool);
pool.add(myCollection);
}
}
@Override
public Connection getConnection() throws SQLException {
if (pool.size()==0) {
Connection conn=null;
for (int i = 0; i < 5; i++) {
conn=JdbcTool3.connection();
ColectionMackClose myCollection = new ColectionMackClose(conn, pool);
pool.add(myCollection);
}
}
return pool.remove(0);
}
2.c3p0连接池，使用较多
- 导包
从SourceForge 网站下载最新的版本
http://sourceforge.net/projects/c3p0/
出现找不到或无法加载主类 cn.fb.textDatasource.C3p0Text，从项目文件夹.path文件中删除多余路径
注意添加两个文件c3p0-0.9.5.2.jar和mchange-commons-java-0.2.11.jar（有时候没mchange也行，和版本有关？）
图片.png
------c3p0-config.xml名称唯一放在scr下-----
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///web09</property>
<property name="user">root</property>
<property name="password">0616</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<named-config name="text">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///web09</property>
<property name="user">root</property>
<property name="password">0616</property>
</named-config>
</c3p0-config>
------c3p0会自动获取xml文件配置---使用set方法设置也行---
public class C3p0Utiles {
private static ComboPooledDataSource datasourse=new ComboPooledDataSource("text");如果没参数会自动加载default
public static ComboPooledDataSource getCombpdatasource() {
return datasourse;
}
public static Connection getCon() {
try {
return datasourse.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
-----------text----------------
public void textadd2() {
Connection con = null;
PreparedStatement pst = null;
try {
con =C3p0Utiles.getCon();
String sql = "insert into product values(?,?,?,null)";
pst = con.prepareStatement(sql);
pst.setString(1, "p022");
pst.setString(2, "云河2");
pst.setDouble(3, 30);
int row = pst.executeUpdate();
if (row > 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}finally {
JdbcTool3.relese(pst, con, null);
}
}
3.dbcp
图片.png
同样使用.properties文件，BasicDataSourceFactory创建
private static DataSource dataSource;
static {
try {
InputStream input = DbcpUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties pro = new Properties();
pro.load(input);
dataSource = BasicDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static DataSource getdatasource() {
return dataSource;
}
public static Connection getconnection() {
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
-------------properties----放在src下-----------
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web09?useUnicode=true&characterEncoding=utf8
username=root
psaaword=0616
4.dbutils
图片.png
MapHandler：单行处理器！把结果集转换成Map<String,Object>，其中列名为键！
MapListHandler：多行处理器！把结果集转换成List<Map<String,Object>>；
BeanHandler：单行处理器！把结果集转换成Bean，该处理器需要Class参数，即Bean的类型；
BeanListHandler：多行处理器！把结果集转换成List<Bean>；
ColumnListHandler：多行单列处理器！把结果集转换成List<Object>，使用ColumnListHandler时需要指定某一列的名称或编号，例如：new ColumListHandler(“name”)表示把name列的数据放到List中。
ScalarHandler：单行单列处理器！把结果集转换成Object。一般用于聚集查询，例如select count(*) from tab_student。
--------------------------------------------
@Test
public void fun1() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student where number=?";
Map<String,Object> map = qr.query(sql, new MapHandler()[把一行记录转换成一个Map，其中键为列名称，值为列值], "S_2000");
System.out.println(map);
}
@Test
public void fun2() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
List<Map<String,Object>> list = qr.query(sql, new MapListHandler()[把转换集转换成List<Map>，其中每个Map对应一行记录]);
for(Map<String,Object> map : list) {
System.out.println(map);
}
}
@Test
public void fun3() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student where number=?";
Student stu = qr.query(sql, new BeanHandler<Student>(Student.class)[把结果集转换成一个Bean对象，在使用BeanHandler时需要指定Class，即Bean的类型], "S_2000");
System.out.println(stu);
}
@Test
public void fun4() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
List<Student> list = qr.query(sql, new BeanListHandler<Student>(Student.class));[需要将列名化为属性把结果集转换成List<Bean>，其中每个Bean对应一行记录]
for(Student stu : list) {
System.out.println(stu);
}
}
@Test
public void fun5() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
List<Object> list = qr.query(sql, new ColumnListHandler("name")[多行单例处理器，即获取name列数据]);
for(Object s : list) {
System.out.println(s);
}
}
@Test
public void fun6() throws SQLException {
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select count(*) from tab_student";
Number number = (Number)qr.query(sql, new ScalarHandler()[单行单列处理器，一般用于聚合查询，在使用ScalarHandler时可以指定列名，如果不指定，默认为第1列。]);
int cnt = number.intValue();[对聚合函数的查询结果，有的驱动返回的是Long，有的返回的是BigInteger，所以这里我把它转换成Number，Number是Long和BigInteger的父类！然后我再调用Number的intValue()或longValue()方法就OK了。]
System.out.println(cnt);
}
@Test
public void add() {
try {
QueryRunner qr = new QueryRunner(C3p0Utiles.getCombpdatasource());
String sql = "insert into product values(?,?,?,null);";
Object[] product = { "p017", "抱抱", 2000 };
int row = qr.update(sql, product);
if (row > 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
} catch (SQLException e) {
e.printStackTrace();
}
