java连接数据库的一些方法记载

主要是jdbc，dbcp，c3p0，dbutils的笔记

都需要导入的包：mysql-connector-java-5.1.37-bin.jar

===jdbc===

JdbcUtil.java

package com.jdbcutil;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JdbcUtil {private static final String driverClass;private static final String url;private static final String username;private static final String password;static {Properties pro = null;InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("database.properties");pro = new Properties();try {pro.load(is);} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}driverClass = pro.getProperty("driverClass");url = pro.getProperty("url");username = pro.getProperty("username");password = pro.getProperty("password");}public static void loadDriver() {try {Class.forName(driverClass);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static Connection getConnection() {Connection con = null;try {loadDriver();con = DriverManager.getConnection(url, username, password);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return con;}public static void release(Connection con, Statement pst, ResultSet rs) {if (con!=null) {try {con.close();} catch (Exception e) {e.printStackTrace();}con = null;}if (pst!=null) {try {pst.close();} catch (Exception e) {e.printStackTrace();}pst = null;}if (rs!=null) {try {rs.close();} catch (Exception e) {e.printStackTrace();}rs = null;}}public static void release(Connection con, Statement pst) {if (con!=null) {try {con.close();} catch (Exception e) {e.printStackTrace();}con = null;}if (pst!=null) {try {pst.close();} catch (Exception e) {e.printStackTrace();}pst = null;}}}

JdbcTest.java

package com.jdbcutil;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import org.junit.Test;public class JdbcTest {public static void main(String[] args) {//select();update();}@Testpublic static void select() {Connection con = JdbcUtil.getConnection();PreparedStatement pst = null;String sql = "select * from zhangwu";ResultSet rs = null;try {pst = con.prepareStatement(sql);rs = pst.executeQuery();while (rs.next()) {System.out.println(rs.getString(1) + "-" + rs.getString(2));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {JdbcUtil.release(con, pst, rs);}}@Testpublic static void update() {Connection con = JdbcUtil.getConnection();PreparedStatement pst = null;String sql = "insert into zhangwu(name,money) values (?,?)";try {pst = con.prepareStatement(sql);pst.setString(1, "test");pst.setInt(2, 888);int num = pst.executeUpdate();System.out.println(num);} catch (Exception e) {e.printStackTrace();} finally {JdbcUtil.release(con, pst);}}}

database.properties

driverClass=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/itheimausername=rootpassword=root

===Dbcp===

导入包：commons-dbcp-1.4.jar ，commons-pool-1.5.6.jar

DbcpTest.java

package com.jdbcutil;import java.io.IOException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;import org.apache.commons.dbcp.BasicDataSourceFactory;public class DbcpTest {public static void main(String[] args) {//t1();t2();}public static void t1() {Connection con = null;PreparedStatement pst = null;ResultSet rs = null;BasicDataSource dataSource = new BasicDataSource();dataSource.setDriverClassName("com.mysql.jdbc.Driver");dataSource.setUrl("jdbc:mysql://localhost:3306/itheima");dataSource.setUsername("root");dataSource.setPassword("root");try {con = dataSource.getConnection();String sql = "select * from zhangwu";pst = con.prepareStatement(sql);rs = pst.executeQuery();while (rs.next()) {System.out.println(rs.getString(1) + "-" + rs.getString(2));}con.close();pst.close();rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void t2() {Connection con = null;PreparedStatement pst = null;ResultSet rs = null;Properties pro = new Properties();try {//注意，这里的配置参数是固定的值pro.load(DbcpTest.class.getClassLoader().getResourceAsStream("dbcpConfig.properties"));DataSource dataSource = BasicDataSourceFactory.createDataSource(pro);con = dataSource.getConnection();String sql = "select * from zhangwu";pst = con.prepareStatement(sql);rs = pst.executeQuery();while (rs.next()) {System.out.println(rs.getString(1) + "-" + rs.getString(2));}con.close();pst.close();rs.close();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

dbcpConfig.properties

driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/itheimausername=rootpassword=root#<!-- \u521D\u59CB\u5316\u8FDE\u63A5 -->initialSize=10#\u6700\u5927\u8FDE\u63A5\u6570\u91CFmaxActive=50#<!-- \u6700\u5927\u7A7A\u95F2\u8FDE\u63A5 -->maxIdle=20#<!-- \u6700\u5C0F\u7A7A\u95F2\u8FDE\u63A5 -->minIdle=5#<!-- \u8D85\u65F6\u7B49\u5F85\u65F6\u95F4\u4EE5\u6BEB\u79D2\u4E3A\u5355\u4F4D 6000\u6BEB\u79D2/1000\u7B49\u4E8E60\u79D2 -->maxWait=60000

===c3p0===

导入包：c3p0-0.9.1.2.jar

C3p0Test.java

package com.jdbcutil;import java.beans.PropertyVetoException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3p0Test {public static void main(String[] args) {//t1();t2();}public static void t1() {Connection con = null;PreparedStatement pst = null;ResultSet rs = null;String sql = "select * from zhangwu";ComboPooledDataSource dataSource = new ComboPooledDataSource();try {dataSource.setDriverClass("com.mysql.jdbc.Driver");dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/itheima");dataSource.setUser("root");dataSource.setPassword("root");con = dataSource.getConnection();pst = con.prepareStatement(sql);rs = pst.executeQuery();while (rs.next()) {System.out.println(rs.getString(1) + "-" + rs.getString(2));}con.close();pst.close();rs.close();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}//使用配置文件public static void t2() {Connection con = null;PreparedStatement pst = null;ResultSet rs = null;ComboPooledDataSource dataSource = new ComboPooledDataSource();//这时候使用默认的，如果制定连接：("配置的名字")String sql = "select * from zhangwu";try {con = dataSource.getConnection();pst = con.prepareStatement(sql);rs = pst.executeQuery();while (rs.next()) {System.out.println(rs.getString(1) + "-" + rs.getString(2));}con.close();pst.close();rs.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

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://localhost:3306/itheima</property><property name="user">root</property><property name="password">root</property><property name="initialPoolSize">5</property><property name="maxPoolSize">20</property>  </default-config>    <named-config name="oracle">     <property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql:///web_07</property><property name="user">root</property><property name="password">123</property>  </named-config>  </c3p0-config>

===dbutils===

导入包：commons-dbutils-1.6.jar

另外用的是c3p0的连接池，需要引入对应的包和配置文件，见上面

DbutilsTest.java

package com.jdbcutil;import java.sql.Connection;import java.sql.SQLException;import java.util.List;import java.util.Map;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DbutilsTest {public static void main(String[] args) {//t1();//t2();t3();}public static void t1() {ComboPooledDataSource dataSource = new ComboPooledDataSource();try {Connection con = dataSource.getConnection();QueryRunner qr = new QueryRunner();String sql = "select * from zhangwu where id>?";List<Map<String,Object>> list = qr.query(con, sql, new MapListHandler(),0);for (Map<String,Object> mp: list) {System.out.println(mp.get("name") + "-" + mp.get("money"));}con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void t2() {ComboPooledDataSource dataSource = new ComboPooledDataSource();QueryRunner qr = new QueryRunner(dataSource);String sql = "select * from zhangwu where id>?";List<Map<String, Object>> list;try {list = qr.query(sql, new MapListHandler(),0);for (Map<String,Object> mp: list) {System.out.println(mp.get("name") + "-" + mp.get("money"));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void t3() {ComboPooledDataSource dataSource = new ComboPooledDataSource();QueryRunner qr = new QueryRunner(dataSource);String sql = "select * from zhangwu";List<Zhangwu> list;try {list = qr.query(sql, new BeanListHandler<Zhangwu>(Zhangwu.class));for (Zhangwu zw : list) {System.out.println(zw.getId() + "-" + zw.getName() + "-" + zw.getMoney());}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

Zhangwu.java

package com.jdbcutil;public class Zhangwu {private int id;private String name;private double money;public Zhangwu() {}public Zhangwu(int id, String name, double money) {this.id = id;this.name = name;this.money = money;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public double getMoney() {return money;}public void setMoney(double money) {this.money = money;}}