oraclec3p0dword60,web_day10dbcpc3p0dbutils

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 &＃61; JdbcTool3.class.getClassLoader();

InputStream input &＃61; classLoader.getResourceAsStream("db.properties");

Properties properties &＃61; new Properties();

properties.load(input);

drive &＃61; properties.getProperty("driver");

sql &＃61; properties.getProperty("url");

username &＃61; properties.getProperty("username");

passord &＃61; properties.getProperty("psaaword");

} catch (Exception e) {

e.printStackTrace();

}

}

public static Connection connection() {

try {

Class.forName(drive);

} catch (Exception e) {

e.printStackTrace();

}

Connection con &＃61; null;

try {

con &＃61; DriverManager.getConnection(sql, username, passord);

} catch (Exception e) {

e.printStackTrace();

}

return con;

}

public static void relese(Statement sta, Connection con, ResultSet res) {

if (res !&＃61; null) {

try {

res.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (con !&＃61; null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if (sta !&＃61; null) {

try {

sta.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

---------有了conn之后便是创建几个放入连接池&＃xff0c;在使用完后同样回收到连接池---

implements 是重写接口所以需要全部覆盖

public class MyDatasource implements DataSource{

private static LinkedList pool&＃61;new LinkedList<>();

static{

Connection conn&＃61;null;

for (int i &＃61; 0; i <5; i&＃43;&＃43;) {

conn&＃61;JdbcTool3.connection();

pool.add(conn);

}

}

/**放回池中

* &＃64;param conn

*/

public static void backConnection(Connection conn) {

pool.add(conn);

}

&＃64;Override

public Connection getConnection() throws SQLException {

if (pool.size()&＃61;&＃61;0) {

Connection conn&＃61;null;

for (int i &＃61; 0; i <5; i&＃43;&＃43;) {

conn&＃61;JdbcTool3.connection();

pool.add(conn);

}

}

return pool.remove(0);

}

修饰者设计模式&＃xff1a;为了将.close修改成把链接放回连接池而不是释放

--------修改原本的功能重写一个类继承connection&＃xff0c;为了修改.close方法--------

因为实例化所以同样需要重写prepareStatement方法

public class ColectionMackClose implements Connection {

private static LinkedList pool;

private static Connection con;

public ColectionMackClose(Connection con, LinkedList pool) {

this.pool &＃61; pool;

this.con &＃61; con;

}

&＃64;Override

public void close() throws SQLException {

pool.add(con);

}

&＃64;Override

public PreparedStatement prepareStatement(String sql) throws SQLException {

return con.prepareStatement(sql);

}

----------获得con之后用新的类ColectionMackClose 包装得到重写的con---------

public class MyDatasource1 implements DataSource{

private static LinkedList pool&＃61;new LinkedList<>();

static{

Connection conn&＃61;null;

for (int i &＃61; 0; i <5; i&＃43;&＃43;) {

conn&＃61;JdbcTool3.connection();

ColectionMackClose myCollection &＃61; new ColectionMackClose(conn, pool);

pool.add(myCollection);

}

}

&＃64;Override

public Connection getConnection() throws SQLException {

if (pool.size()&＃61;&＃61;0) {

Connection conn&＃61;null;

for (int i &＃61; 0; i <5; i&＃43;&＃43;) {

conn&＃61;JdbcTool3.connection();

ColectionMackClose myCollection &＃61; new ColectionMackClose(conn, pool);

pool.add(myCollection);

}

}

return pool.remove(0);

}

2.c3p0连接池&＃xff0c;使用较多

导包

从SourceForge 网站下载最新的版本

http://sourceforge.net/projects/c3p0/

出现找不到或无法加载主类 cn.fb.textDatasource.C3p0Text&＃xff0c;从项目文件夹.path文件中删除多余路径

注意添加两个文件c3p0-0.9.5.2.jar和mchange-commons-java-0.2.11.jar(有时候没mchange也行&＃xff0c;和版本有关&＃xff1f;)

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 &＃61; null;

PreparedStatement pst &＃61; null;

try {

con &＃61;C3p0Utiles.getCon();

String sql &＃61; "insert into product values(?,?,?,null)";

pst &＃61; con.prepareStatement(sql);

pst.setString(1, "p022");

pst.setString(2, "云河2");

pst.setDouble(3, 30);

int row &＃61; 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

Properties pro &＃61; new Properties();

pro.load(input);

dataSource &＃61; 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&＃61;com.mysql.jdbc.Driver

url&＃61;jdbc:mysql://localhost:3306/web09?useUnicode&＃61;true&characterEncoding&＃61;utf8

username&＃61;root

psaaword&＃61;0616

4.dbutils

Map map &＃61; qr.query(sql, new MapHandler()[把一行记录转换成一个Map&＃xff0c;其中键为列名称&＃xff0c;值为列值], "S_2000");

System.out.println(map);

}

&＃64;Test

public void fun2() throws SQLException {

DataSource ds &＃61; JdbcUtils.getDataSource();

QueryRunner qr &＃61; new QueryRunner(ds);

String sql &＃61; "select * from tab_student";

List> list &＃61; qr.query(sql, new MapListHandler()[把转换集转换成List&＃xff0c;其中每个Map对应一行记录]);

for(Map map : list) {

System.out.println(map);

}

}

&＃64;Test

public void fun3() throws SQLException {

DataSource ds &＃61; JdbcUtils.getDataSource();

QueryRunner qr &＃61; new QueryRunner(ds);

String sql &＃61; "select * from tab_student where number&＃61;?";

Student stu &＃61; qr.query(sql, new BeanHandler(Student.class)[把结果集转换成一个Bean对象&＃xff0c;在使用BeanHandler时需要指定Class&＃xff0c;即Bean的类型], "S_2000");

System.out.println(stu);

}

&＃64;Test

public void fun4() throws SQLException {

DataSource ds &＃61; JdbcUtils.getDataSource();

QueryRunner qr &＃61; new QueryRunner(ds);

String sql &＃61; "select * from tab_student";

List list &＃61; qr.query(sql, new BeanListHandler(Student.class));[需要将列名化为属性把结果集转换成List&＃xff0c;其中每个Bean对应一行记录]

for(Student stu : list) {

System.out.println(stu);

}

}

&＃64;Test

public void fun5() throws SQLException {

DataSource ds &＃61; JdbcUtils.getDataSource();

QueryRunner qr &＃61; new QueryRunner(ds);

String sql &＃61; "select * from tab_student";

List list &＃61; qr.query(sql, new ColumnListHandler("name")[多行单例处理器&＃xff0c;即获取name列数据]);

for(Object s : list) {

System.out.println(s);

}

}

&＃64;Test

public void fun6() throws SQLException {

DataSource ds &＃61; JdbcUtils.getDataSource();

QueryRunner qr &＃61; new QueryRunner(ds);

String sql &＃61; "select count(*) from tab_student";

Number number &＃61; (Number)qr.query(sql, new ScalarHandler()[单行单列处理器&＃xff0c;一般用于聚合查询&＃xff0c;在使用ScalarHandler时可以指定列名&＃xff0c;如果不指定&＃xff0c;默认为第1列。]);

int cnt &＃61; number.intValue();[对聚合函数的查询结果&＃xff0c;有的驱动返回的是Long&＃xff0c;有的返回的是BigInteger&＃xff0c;所以这里我把它转换成Number&＃xff0c;Number是Long和BigInteger的父类&＃xff01;然后我再调用Number的intValue()或longValue()方法就OK了。]

System.out.println(cnt);

}

&＃64;Test

public void add() {

try {

QueryRunner qr &＃61; new QueryRunner(C3p0Utiles.getCombpdatasource());

String sql &＃61; "insert into product values(?,?,?,null);";

Object[] product &＃61; { "p017", "抱抱", 2000 };

int row &＃61; qr.update(sql, product);

if (row > 0) {

System.out.println("添加成功");

} else {

System.out.println("添加失败");

}

} catch (SQLException e) {

e.printStackTrace();

}