JDBC记录

前提:

              |-设置工作空间的字符编码

              

              |-Junit:Java语言提供的单元测试框架。属于第三方工具，一般情况下需要导入jar包，不过，多数java开发环境已经集成了Junit作为测试工具

定义：Java Database Connectivity Java数据库连接，SUN公司提供的java访问数据库的标准规范(接口)

原理：

                  

简单实现：

①创建一个java项目

②导入jar包：mysql-connector-java-5.1.39-bin.jar    并build path

③书写代码，并执行。

   *注：导入的package都是java.sql下的

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;
public class TestJDBC {
	@Test
	public void testQueryAll(){
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://127.0.0.1:3306/exam?useUnicode=true&characterEncoding=utf8";
			String username="root";
			String password="root";
			conn=DriverManager.getConnection(url,username,password);
			stmt=conn.createStatement();
			String sql="select * from emp";
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				System.out.print(rs.getString(1)+" ");//获取第一列属性
				System.out.println(rs.getString("ename"));//根据属性名来获取内容
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

通过配置文件来实现JDBC

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/exam?useUnicode=true&characterEncoding=utf8
username=root
password=root

JDBCUtils_V3.java

获取属性文件的两种方法

1）ResourceBundle

ResourceBundle bundle = ResourceBundle.getBundle("db");
String driver = bundle.getString("jdbc.driver");
String url = bundle.getString("jdbc.url");
String user = bundle.getString("jdbc.user");
String password = bundle.getString("jdbc.password");

2）Properties，下边直接使用该方法封装了工具类。

package cn.pb.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;

/**
 * 提供获取连接和释放资源的 方法
 * 
 */
public class JDBCUtils_V3 {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;

	/**
	 * 静态代码块加载配置文件信息
	 */
	static {
		try {
			// 1.通过当前类获取类加载器
			ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
			// 2.通过类加载器的方法获得一个输入流
			InputStream is = classLoader.getResourceAsStream("db.properties");
			// 3.创建一个properties对象
			Properties props = new Properties();
			// 4.加载输入流
			props.load(is);
			// 5.获取相关参数的值
			driver = props.getProperty("driver");
			url = props.getProperty("url");
			username = props.getProperty("username");
			password = props.getProperty("password");
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 获取连接方法
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
}

TestUtils.java

package cn.pb.jdbc.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import cn.pb.jdbc.JDBCUtils_V1;
import cn.pb.jdbc.JDBCUtils_V2;
import cn.pb.jdbc.JDBCUtils_V3;

/**
 * 测试工具类
 * 
 * @author Never Say Never
 * @date 2016年7月29日
 * @version V1.0
 */
public class TestUtils {
	/**
	 * 根据id更新用户信息方法
	 */
	@Test
	public void testUpdateById() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			// 1.获取连接
			conn = JDBCUtils_V3.getConnection();
			// 2.编写sql语句
			String sql = "update tbl_user set upassword=? where uid=?";
			// 3.获取执行sql语句对象
			pstmt = conn.prepareStatement(sql);
			// 4.设置参数
			pstmt.setString(1, "999");
			pstmt.setInt(2, 3);
			// 5.执行更新操作
			int row = pstmt.executeUpdate();
			if (row > 0) {
				System.out.println("更新成功!");
			} else {
				System.out.println("更新失败!");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 6.释放资源
			JDBCUtils_V3.release(conn, pstmt, null);
		}
	}

	/**
	 * 根据id删除信息方法
	 */
	@Test
	public void testDeleteById() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			// 1.获取连接
			conn = JDBCUtils_V3.getConnection();
			// 2.编写sql语句
			String sql = "delete from tbl_user where uid=?";
			// 3.获取执行sql语句对象
			pstmt = conn.prepareStatement(sql);
			// 4.设置参数
			pstmt.setInt(1, 4);
			// 5.执行删除操作
			int row = pstmt.executeUpdate();
			if (row > 0) {
				System.out.println("删除成功!");
			} else {
				System.out.println("删除失败!");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 6.释放资源
			JDBCUtils_V3.release(conn, pstmt, null);
		}
	}

	/**
	 * 添加用户信息方法
	 */
	@Test
	public void testAdd() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			// 1.获取连接
			conn = JDBCUtils_V2.getConnection();
			// 2.编写sql语句
			String sql = "insert into tbl_user values(null,?,?)";
			// 3.获取执行sql语句对象
			pstmt = conn.prepareStatement(sql);
			// 4.设置参数
			pstmt.setString(1, "lisi");
			pstmt.setString(2, "hehe");
			// 5.执行插入操作
			int row = pstmt.executeUpdate();
			if (row > 0) {
				System.out.println("添加成功!");
			} else {
				System.out.println("添加失败!");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 6.释放资源
			JDBCUtils_V2.release(conn, pstmt, null);
		}
	}

	/**
	 * 根据id查询用户信息
	 */
	@Test
	public void testFindUserById() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			// 1.获取连接
			conn = JDBCUtils_V1.getConnection();
			// 2.编写sql语句
			String sql = "select * from tbl_user where uid=?";
			// 3.获取执行sql语句对象
			pstmt = conn.prepareStatement(sql);
			// 4.设置参数
			pstmt.setInt(1, 2);
			// 5.执行查询操作
			rs = pstmt.executeQuery();
			// 6.处理结果集
			while (rs.next()) {
				System.out.println(rs.getString(2) + "----" + rs.getString("upassword"));
			}
			// 释放资源放在此处行么？【不行滴！】
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 7.释放资源
			JDBCUtils_V1.release(conn, pstmt, rs);
		}
	}
}

JDBC连接池优化

         传统JDBC的操作,对连接的对象销毁不是特别好.每次创建和销毁连接都是需要花费时间.可以使用连接池优化的程序.

        在程序开始的时候,可以创建几个连接,将连接放入到连接池中.用户使用连接的时候,可以从连接池中进行获取.用完之后,可以将连接归还连接池.

      1）自定义连接池：SUN公司提供了一个连接池的接口.(javax.sql.DataSource).了解

package cn.pb.jdbc.DataSource;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import cn.pb.jdbc.utils.JDBCUtils_V3;

public class MyDataSource implements DataSource{
	//1.创建1个容器用于存储Connection对象
	private static LinkedList<Connection> pool = new LinkedList<Connection>();
	
	//2.创建5个连接放到容器中去
	static{
		for (int i = 0; i < 5; i++) {
			Connection conn = JDBCUtils_V3.getConnection();
			pool.add(conn);
		}
	}
	
	/**
	 * 重写获取连接的方法
	 */
	@Override
	public Connection getConnection() throws SQLException {
		Connection conn = null;
		//3.使用前先判断
		if(pool.size()==0){
			//4.池子里面没有，我们再创建一些
			for (int i = 0; i < 5; i++) {
				conn = JDBCUtils_V3.getConnection();
				pool.add(conn);
			}
		}
		//5.从池子里面获取一个连接对象Connection
		conn = pool.remove(0);
		return conn;
	}

	/**
	 * 归还连接对象到连接池中去
	 */
	public void backConnection(Connection conn){
		pool.add(conn);
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

       自定义连接池的问题

1. 获得连接之后必须手动归还
2. 直接调用close()关闭连接，连接池可能出现无连接可使用。

  解决方法：

       继承：子类继承父类，实现方法复写，实现方法增强。

           前提：必须存在父类，且存在继承关系

       装饰着模式：此模式专门用于方法增强

           前提：必须有接口

       动态代理：在运行时动态创建代理类，完成增强操作。也装饰着相似

           前提：必须存在接口

           难点：需要反射技术

       字节码增强，运行时创建目标类子类，从而进行增强

            常见的有cglib、javassist 等。

装饰着模式重写connection

package cn.pb.jdbc.DataSource;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.util.LinkedList;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

//1.实现同一个接口Connection
public class MyConnection implements Connection {
	//3.定义一个变量
	private Connection conn;
	
	private LinkedList<Connection> pool;
	
	// 2.编写一个构造方法(参数使用了面相对象的多态特性)
	public MyConnection(Connection conn,LinkedList<Connection> pool) {
		this.conn=conn;
		this.pool=pool;
	}
	
	//4.书写需要增强的方法
	@Override
	public void close() throws SQLException {
		pool.add(conn);
	}

	/**
	 * 此方法必须覆盖！否则会出现空指针异常！！！
	 */
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return conn.prepareStatement(sql);
	}
	
	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Statement createStatement() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public String nativeSQL(String sql) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean getAutoCommit() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void commit() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void rollback() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean isClosed() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public DatabaseMetaData getMetaData() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setReadOnly(boolean readOnly) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean isReadOnly() throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void setCatalog(String catalog) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public String getCatalog() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setTransactionIsolation(int level) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public int getTransactionIsolation() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public SQLWarning getWarnings() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void clearWarnings() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setHoldability(int holdability) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public int getHoldability() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Savepoint setSavepoint() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Savepoint setSavepoint(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability)
			throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency,
			int resultSetHoldability) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Clob createClob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Blob createBlob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public NClob createNClob() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SQLXML createSQLXML() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isValid(int timeout) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public void setClientInfo(String name, String value) throws SQLClientInfoException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setClientInfo(Properties properties) throws SQLClientInfoException {
		// TODO Auto-generated method stub

	}

	@Override
	public String getClientInfo(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Properties getClientInfo() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setSchema(String schema) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public String getSchema() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void abort(Executor executor) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public int getNetworkTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

}

MyDataSource1.java       --------->解决close方法问题

package cn.pb.jdbc.DataSource;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;

import javax.sql.DataSource;

import cn.pb.jdbc.utils.JDBCUtils_V3;

public class MyDataSource1 implements DataSource{
	//1.创建1个容器用于存储Connection对象
	private static LinkedList<Connection> pool = new LinkedList<Connection>();
	
	//2.创建5个连接放到容器中去
	static{
		for (int i = 0; i < 5; i++) {
			Connection conn = JDBCUtils_V3.getConnection();
			//放入池子中connection对象已经经过改造了
			MyConnection myconn = new MyConnection(conn, pool);
			pool.add(myconn);
		}
	}
	
	/**
	 * 重写获取连接的方法
	 */
	@Override
	public Connection getConnection() throws SQLException {
		Connection conn = null;
		//3.使用前先判断
		if(pool.size()==0){
			//4.池子里面没有，我们再创建一些
			for (int i = 0; i < 5; i++) {
				conn = JDBCUtils_V3.getConnection();
				//放入池子中connection对象已经经过改造了
				MyConnection myconn = new MyConnection(conn, pool);
				pool.add(myconn);
			}
		}
		//5.从池子里面获取一个连接对象Connection
		conn = pool.remove(0);
		return conn;
	}

	/**
	 * 归还连接对象到连接池中去
	 */
	public void backConnection(Connection conn){
		pool.add(conn);
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

}

C3P0连接池的使用，这个是最重要的。

    ①导入jar包。    c3p0-0.9.1.2.jar

    ②书写配置文件：c3p0-config.xml

            *注：路径 src下

            *注：文件名字：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/wutong?useSSL=false
		</property>
		<property name="user">root</property>
		<property name="password">222222</property>
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">20</property>
	</default-config>

	工具类根据此name来进行数据库的一些初始化操作
	<named-config name="itcast">
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/wutong?useSSL=false
		</property>
		<property name="user">root</property>
		<property name="password">222222</property>
		如果连接不够用了一次增加几个
		<property name="acquireIncrement">5</property>
		初始化连接数
		<property name="initialPoolSize">20</property>
		最小连接数
		<property name="minPoolSize">10</property>
		最大连接数
		<property name="maxPoolSize">40</property>
		JDBC标准参数，用于控制数据内加载的PrepareStatements数量
		<property name="maxStatements">0</property>
		连接池内单个连接所拥有的最大缓存statement数
		<property name="maxStatementsPerConnection">5</property>
	</named-config>
</c3p0-config>

 常见配置项

             

            

C3P0Utils.java

package cn.pb.jdbc.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource("pb");

	public static DataSource getDataSource() {
		return dataSource;
	}

	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}

DBCP连接池：tomcat内置的连接池

      ①导入jar包。         commons-dbcp-1.4.jar           commons-pool-1.5.6.jar

      ②写配置文件。 *.properties

            *注：建议src下

            *注：配置文件中的命名是有规定的。

            *注：properties不能编写中文，不支持在STS中修改，必须使用记事本进行修改，否则中文乱码。

            *注：使用DBCP线程池进行操作的时候，不能关闭资源，否则可以实现修改操作。但是，进行junit测试的时候会报一个资源无法绑定的错误

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web08?useUnicode=true&characterEncoding=utf8
username=root
password=root

常见配置项:http://commons.apache.org/proper/commons-dbcp/configuration.htm l

              

              

DBCPUtils.java

package cn.pb.jdbc.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class DBCPUtils {
	private static DataSource dataSource;
	static{
		try {
			//1.加载找properties文件输入流
			InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties");
			//2.加载输入流
			Properties props = new Properties();
			props.load(is);
			//3.创建数据源
			dataSource = BasicDataSourceFactory.createDataSource(props);
		} 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);
		}
	}
}

使用DBUtills实现增删改查

        ①导入jar包     commons-dbutils-1.4.jar

        ②进行测试

package cn.pb.jdbc.test;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import cn.pb.jdbc.utils.C3P0Utils;

/**
 * 测试DBUtils工具类的增删改操作
 * 
 */
public class TestDBUtils1 {

	/**
	 * 添加所有用户方法
	 */
	@Test
	public void testAddUser() {
		try {
			// 1.创建核心类QueryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写SQL语句
			String sql = "insert into tbl_user values(null,?,?)";
			// 3.为站位符设置值
			Object[] params = { "余淮", "耿耿" };
			// 4.执行添加操作
			int rows = qr.update(sql, params);
			if (rows > 0) {
				System.out.println("添加成功!");
			} else {
				System.out.println("添加失败!");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 根据id修改用户方法
	 * 
	 */
	@Test
	public void testUpdateUserById() {
		try {
			// 1.创建核心类QueryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写SQL语句
			String sql = "update tbl_user set upassword=? where uid=?";
			// 3.为站位符设置值
			Object[] params = { "xxx", 21 };
			// 4.执行添加操作
			int rows = qr.update(sql, params);
			if (rows > 0) {
				System.out.println("修改成功!");
			} else {
				System.out.println("修改失败!");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 根据id删除用户方法
	 */
	@Test
	public void testDeleteUserById() {
		try {
			// 1.创建核心类QueryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写SQL语句
			String sql = "delete from tbl_user where uid=?";
			// 3.为站位符设置值
			Object[] params = {19};
			// 4.执行添加操作
			int rows = qr.update(sql, params);
			if (rows > 0) {
				System.out.println("删除成功!");
			} else {
				System.out.println("删除失败!");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

       查询测试

package cn.pb.jdbc.test;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import cn.pb.domain.User;
import cn.pb.jdbc.utils.C3P0Utils;

/**
 * 测试DBUtils查询操作
 * 
 * @author Never Say Never
 * @date 2016年7月31日
 * @version V1.0
 */
public class TestDBUtils2 {

	/*
	 * 查询所有用户方法
	 */
	@Test
	public void testQueryAll() {
		try {
			// 1.获取核心类queryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写sql语句
			String sql = "select * from tbl_user";
			// 3.执行查询操作
			List<User> users = qr.query(sql, new BeanListHandler<User>(User.class));
			// 4.对结果集集合进行遍历
			for (User user : users) {
				System.out.println(user.getUname() + " : " + user.getUpassword());
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/*
	 * 根据id查询用户方法
	 */
	@Test
	public void testQueryUserById() {
		try {
			// 1.获取核心类queryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写sql语句
			String sql = "select * from tbl_user where uid=?";
			//3.为占位符设置值
			Object[] params = {21};
			// 4.执行查询操作
			User user = qr.query(sql, new BeanHandler<User>(User.class), params);
			System.out.println(user.getUname() + " : " + user.getUpassword());
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/*
	 * 根据所有用户的总个数
	 */
	@Test
	public void testQueryCount() {
		try {
			// 1.获取核心类queryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写sql语句
			String sql = "select count(*) from tbl_user";
			// 4.执行查询操作
			Long count = (Long) qr.query(sql, new ScalarHandler());
			System.out.println(count);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/*
	 * 查询所有用户方法
	 */
	@Test
	public void testQueryAll1() {
		try {
			// 1.获取核心类queryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写sql语句
			String sql = "select * from tbl_user";
			// 3.执行查询操作
			List<Map<String, Object>> list = qr.query(sql, new MapListHandler());
			// 4.对结果集集合进行遍历
			for (Map<String, Object> map : list) {
				System.out.println(map);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	/*
	 * 查询所有用户方法
	 */
	@Test
	public void testQueryAll2() {
		try {
			// 1.获取核心类queryRunner
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			// 2.编写sql语句
			String sql = "select * from tbl_user";
			// 3.执行查询操作
			List<Object> list = qr.query(sql, new ColumnListHandler("uname"));
			// 4.对结果集集合进行遍历
			for (Object object : list) {
				System.out.println(object);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}