主要是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 block
e.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 block
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection con = null;
try {
loadDriver();
con = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.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();
}
@Test
public 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 block
e.printStackTrace();
} finally {
JdbcUtil.release(con, pst, rs);
}
}
@Test
public 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.Driver
url=jdbc:mysql://localhost:3306/itheima
username=root
password=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 block
e.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 block
e.printStackTrace();
}
}
}
dbcpConfig.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/itheima
username=root
password=root
#<!-- \u521D\u59CB\u5316\u8FDE\u63A5 -->
initialSize=10
#\u6700\u5927\u8FDE\u63A5\u6570\u91CF
maxActive=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 block
e.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 block
e.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 block
e.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 block
e.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 block
e.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;
}
}