mysql常用的操作(五)

数据库连接池：c3p0、dbcp、tomcat

1）自定义连接池

public Connection getConnection() throws SQLException {try{if(list.size()==0){Class.forName("com.mysql.jdbc.Driver");for(int i=0;i<5;i++){Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db_name","root","root");list.add(conn);}}final Connection conn = list.remove();Connection proxy = (Connection)Proxy.newProxyInstance(Connection.class.getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler(){public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {if("close".equals(method.getName())){if(conn!=null && conn instanceof Connection && !conn.isClosed()){list.add(conn);}return null;}else{return method.invoke(conn, args);}}});return proxy;}catch (Exception e) {e.printStackTrace();throw new RuntimeException();}}

2）c3p0连接池

c3p0-config.xml

<?xml version="1.0" encoding="utf-8"?><c3p0-config><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/db_name</property><property name="user">root</property><property name="password">root</property></default-config><named-config name="other"><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/db_name</property><property name="user">root</property><property name="password">root</property></named-config></c3p0-config>

try {ComboPooledDataSource source = new ComboPooledDataSource();ct = source.getConnection();ps = ct.prepareStatement("select * from tb_type where typeid = ?");ps.setString(1, "1");rs = ps.executeQuery();while (rs.next()) {String typename = rs.getString("typename");System.out.println(typename);}} catch (SQLException e) {e.printStackTrace();} finally {close(rs, ps, ct);}

3）dbcp连接池

dbcp.properties

dirver=com.mysql.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3308/db_nameusername=rootpassword=root

static {try {prop = new Properties();prop.load(DBCPDemo.class.getClassLoader().getResourceAsStream("dbcp.properties"));source = BasicDataSourceFactory.createDataSource(prop);} catch (Exception e) {e.printStackTrace();} }

4）tomcat自带的连接池dbcp

context.xml

<Context><Resource name="jdbc/EmployeeDB" auth="Container" type="javax.sql.DataSource"username="root" password="root" driverClassName="com.mysql.jdbc.Driver"url="jdbc:mysql://127.0.0.1:3308/db_name" maxActive="8" maxIdle="4" /></Context>

try {InitialContext init = new InitialContext();Context context = (Context) init.lookup("java:comp/env");DataSource source = (DataSource) context.lookup("jdbc/EmployeeDB");SourceUtils.setDataSource(source);Connection ct = source.getConnection();PreparedStatement ps = ct.prepareStatement("select * from tb_video");ResultSet rs = ps.executeQuery();while (rs.next()) {System.out.println(rs.getString("videoname") + " " + rs.getString("videourl"));}} catch (Exception e) {throw new RuntimeException(e);}